Detailed Instructions How To Write a Select Statement in SQL Server (Transact SQL(T-SQL))

Basic Select Statement
A Select statement returns a set of records filtered by the criteria provided.  If not criteria clauses are specified, then all records in the table is returned.

Example returning all records from a table called Employees.

Select * From Employees.

The Asterisk
The asterisk specifies that all fields in the table will be returned.

Specifying Fields
To return only specific fields omit the asterisk and list the field names separated by commas.

Example Example returning all records from a table call Employees, but only the FirstName and LastName fields.

Select FirstName, LastName  From Employees

The Where Clause
The Where clause specifies criteria for which records will be returned.  Only records that meet the Where criteria will be returned.

Example of a Select statement with a Where clause that specifies to only return records with the first name of Samuel.

Select *

From Employees

Where FirstName = ‘Samuel’

Note that strings are enclosed in single quotes, not double quotes as in some programming languages.

The Like Operator and Wildcards
The Like operator and wildcards are used to match parts of the data in a field.  One would use Like in the place of an equal sign.  Previously we used the equal sign to return all records with a FirstName field equal to the string ‘Samuel’.  If we wanted to find any records with a FirstName field that start with the string ‘Sam’ we would do the following.
Select *

From Employees

Where FirstName Like ‘Sam%’

The character % matches any string of any length.  It also matches zero length strings.
The _ matches a single character.

The Order By Clause
The Order By clause sorts the records that are returned by a Select statement.  One may specify more than one field to be ordered on separated by commas.  The first field will be the one that is sorted first and the second field will be sorted within the first.  The third will be sorted within the second within the first and so forth for as many fields as one has chosen to order on.

Example of an Order By clause sorting records by LastName and FirstName.
Select *

From Employees

Order By LastName, FirstName

One may use Asc or Desc after the field name, but before the comma to specify a descending or ascending sort on a specific field.  If neither is specified ascending(Asc) is the default.

The Group By Clause
The Group By Clause tells SQL Server how to group the data together when using aggregate functions.  Aggregate functions perform calculations on each value in a field in a database table and returns a single value.  A single value is returned for each grouping.  For example, if the Group By clause says to group on a person’s name that every record that has that person’s name on it will be rolled into one value.  Each person’s name will return one value for the field being aggregated and only one record representing all records with that person’s name.
The following sections will describe some of the aggregate functions that one can use with the Group By clause.

One may specify more than one field to be grouped on separated by commas.  The first field will be the one that is grouped first and the second field will be grouped within the first.  The third will be group within the second within the first and so forth for as many fields as one has chosen to group on.

Note that all fields in the Select to be returned from the query which are not aggregate functions must also be in the Group By clause.

Aggregate Functions
Sum()
Sum is used to add all values together in a field in all records that meet the criteria of a Group By clause.

Example of a Select statement with a Sum aggregate function summing the field HourlyWage and a Group By clause which groups on the LastName field of the Employees table.
Select LastName, Sum(HourlyWage)

From Employees

Group By LastName

Avg()
Ave Sums all values in a field in all records that meet the criteria of a Group By clause and divides them by the number of records in the grouping.

Example of a Select statement with a Avg aggregate function Averaging the field HourlyWage and a Group By clause which groups on the LastName field of the Employees table.
Select LastName, Avg(HourlyWage)

From Employees

Group By LastName

Count()
Returns the number of records in the grouping specified by the Group By clause.

Example of a Select statement with a Count aggregate function Counting the number of records in a table.  Note that no Group By is given, so all records are counted and in sub groupings.
Select Count(LastName) From Employees

Inner Join, Outer Join, Huh?
Inner Join
An Inner Join joins two tables together on a common field and only returns those records where the common fields match.

An Outer Join joins two tables together on a common field and returns those records that match and also records that do not match.  To determine which table’s fields are returned when there is no match is determined by the modifier Left or Right or Full.

Left Join.  Right Join.  Go Left.  Go Right.  What is Going On?
The Left, Right, and Full modifiers are only available with the Outer Join.

A Left Outer Join returns all records from the table on the left side of the equals sign.  The records that match the sort criteria will have the values from the fields from the other table as well.  When they do not match, the values for those fields in the right table will be null.

Example of a Select statement that joins two tables and all records from the table on the left side of the equals sign being returned.  All records will be returned from the Employees table and only the records from the Address table where the AddressId matched in the Employees table.
Select *

From Employees

Left Outer Join Address On Employees.AddressId = Address.AddressId

A Right Outer Join returns all records from the table on the right side of the equals sign.  The records that match the sort criteria will have fields from the other table as well.  When they do not match those fields will be null.

Example of a Select statement that joins two tables and all records from the table on the right side of the equals sign being returned.  All records will be returned from the Address table and only the records from the Employees table where the AddressId in the Address table matches the AddressId in  the Employees table.
Select *

From Employees

Right Outer Join Address On Employees.AddressId = Address.AddressId

A Full Outer Join returns all records from both tables.  The records that match the sort criteria will have fields from both tables.  When they do not match fields from the table that could not match will be null.

Example of a Select statement that joins two tables and all records from both tables being returned.  Nulls will be returned for records that do not match the join field.
Select *

From Employees

Full Outer Join Address On Employees.AddressId = Address.AddressId

The Having Clause
The Having clause is similar to the Where clause, but is for use with the Group By clause and aggregate functions.

Example of a Select statement that filters on the Count of employees that have the same last name.  It only returns last names that appear on more than one record.

SELECT LastName, Count(LastName)
FROM Employee
GROUP BY LastName
HAVING Count(LastName) > 1
ORDER BY LastName;

Subqueries
One can also create subqueries within SQL queries.

Example of a subquery that finds the date that an employee’s address was change last.

Select Employees.FirstName, Employee.LastName,
       (Select Max(Address.ChangeDate)
        From Address
        Where Address Employees.AddressId = Address.AddressId) As MostRecentAddressChange
From Employees

Aliasing Tables and Fields
One can change the name that they reference a table as in the query or the name of the field that is returned from the query by using aliases.

Example of using Aliases for tables and field names.

Select e.FirstName As FName, e.LastName As LName, a.ZipCode As Zip

From Employees As e

Full Outer Join Address As a On e.AddressId = a.AddressId

Notice that when I reference the tables in the query I only need to use their aliases, which has the potential to make it shorter and more readable.  In my case I just use a single letter.  Using a little longer Alias might be more readable, without having to type out the entire table name.