SQL Server

Exceptional SQL

SQL's union operators can make queries easy to write and intuitive to read
and understand. One of these is the EXCEPT operator that "subtracts" one
set of rows from another. 



Read the full post at www.gennick.com/database.

Exceptional SQL

Fifth in a series of posts in response to Tim Ford's #EntryLevel Challenge.


SQL implements a number of so-called union operators that under the right circumstances can make queries easy to write and intuitive to read and understand. One of these is the EXCEPT operator that "subtracts" one set of rows from another. 

Say for example that you're doing some work on data quality and want to investigate products that your firm has sold without ever having first purchased. What have you sold but never bought? You can answer that question easily by executing the following EXCEPT query:

SQL Joinery

Fourth in a series of posts in response to Tim Ford's #EntryLevel Challenge.


SQL supports three types of join operation. Most developers learn the inner join first. But there are two other join operations you should know about. These are the outer join, and the full outer join. These additional join types allow you to write in essence could be termed as optional joins

Inner Joins

The so-called inner-join is the default. It's the happy path from a theory perspective, and it's the join type most SQL developers learn first. Use it to combine related rows from two or more tables. 

For example, perhaps you want to report on all the customers in the AdventureWorks database. You might begin working that business problem by writing the following query:

SQL Joinery

Joins are fundamental in SQL, and are used in most every production query.
There are three types in particular that every developer should fully
understand.



Read the full post at www.gennick.com/database.

ANSI Join Syntax in SQL Server

Writing your database queries using explicit join syntax helps toward
clarity and readability while reducing the opportunity for error and wrong
results.



Read the full post at www.gennick.com/database.

ANSI Join Syntax in SQL Server

Another in a series of posts in response to Tim Ford's #EntryLevel Challenge.


Anyone new to SQL Server will sooner or later, and probably sooner, encounter exhortations to write joins in "ANSI join syntax". While the term is misleading and in fact incorrect, the practice of using the so-called "ANSI join syntax" contributes toward queries that are easier to understand and maintain. 

Clear Intentions

Following are two queries that produce the same result -- all possible combinations of product and subcategory names from the two tables listed in the FROM clause. (Such a result is termed a Cartesian product). Notice how I've written the joins in the respective FROM clauses.

Sliced Bread in SQL

There is sliced bread in SQL. You'll find it in the from of window
functions. They are so very important and useful that it's irresponsible
not be understand them and use them. 



Read the full post at www.gennick.com/database.

Sliced Bread in SQL

My second in a series of posts in response to Tim Ford's #EntryLevel Challenge.


A comparison to sliced bread is a rhetorical device evoking the wonder of some new invention or feature in comparison to what went before. Sliced bread revolutionized the making of lunches and toast for breakfast. It meant so much to harried families that a World War II attempt to ban the slicing of bread to save on resources was opposed by New York City Mayor LaGuardia and lasted a mere 49 days from January 18, 1943 until being rescinded on March 8 that same year. 

There is sliced bread in SQL.

Video: Amazon Web Services (AWS) : Relational Database Services (RDS) for SQL Server

Here’s another video on my YouTube channel. This one is a quick run through of RDS for SQL Server, a DBaaS offering from Amazon Web Services.

The video was based on this article.

The cameo for this video is Garth Harbach, a former colleague of mine. :)

Installing Adventure Works

Welcome to my first in a series of posts in response to Tim Ford's #EntryLevel Challenge, which I learned about indirectly from reading a post by Steve Hood. SQL is what I'm good at, so I will be focusing on SQL and T-SQL in this series. 


To learn or practice with SQL requires two things: A database engine, and some example data. Microsoft SQL Server Express is a freely available and easy-to-install engine. Microsoft's Adventure Works example database provides a good set of tables with data designed to show off all that SQL is capable of doing. Put SQL Server Express together with Adventure Works, and you have a nice platform on which to learn and practice the SQL language.