Introduction to SQL. Presented by www.texaswebdevelopers.com

Formatting the SELECT statement:

SELECT [predicate] { * | table.* | [table.]field1 [AS alias1] [, table.]field2 [AS alias2] [, ...] ] }
FROM tableexpression [, ...] [IN externaldatabase]
[WHERE... ] [NOT] [IN] (value1,[value2,[...] ] )
[GROUP BY... ]
[HAVING... ]
[ORDER BY... ] v [WITH OWNERACCESS OPTION]
[subqueryclause [subqueryclause [...] ] ]

SELECT

The SELECT statement returns information from a database as a set of records without altering the database. It searches the database, extracts the chosen columns and selects those records that meet the criteria, sorting and grouping the results into a specified order.

A SELECT statement can be nested inside of another SELECT statement which is nested inside of another SELECT and so on. When a SELECT is nested it is refered to as a subquery clause.

At its simplest, you can use an asterisk ( * ) to select all of the fields in a table (in this case the table 'Musicians' ):

SELECT * FROM Musicians;

Or you can be more selective and choose just one or a few of the fields in a table, and they will be returned in the order listed:

SELECT MusicianName, Instrument FROM Musicians;

You can also have a query display a field under a different heading than the one used in the table by establishing an "alias" with the reserved word AS:

SELECT MusicianName AS Musician, Instrument
FROM Musicians;

...and you can even have a query combine two or more fields from a table into one field in the returned list under a new heading using the ampersand character:

SELECT Name, City & ", " & Country AS Location
FROM Customers;

WHERE

You can use the WHERE clause to further focus your selection by specifying certain criteria to be met by the values. The following example returns the names of all musicians who play the flute:

SELECT MusicianName AS Musician FROM Musicians
WHERE Instrument = 'flute';

...and this example returns the names of all jigs in a 'Tunes' table:

SELECT TuneName AS Name, Source1 AS Recording FROM Tunes
WHERE TuneType = 'jig';

You can combine more than one criterion in a WHERE clause using any of the logical operators. Here the query returns a list of all items which are blue and cost less than $100:

SELECT Item, UnitPrice AS Price FROM Products
WHERE Color = 'blue' AND UnitPrice < 100;

The optional, reserved word IN can be used either as a clause or as an operator.

If you want to get fields from a database other than the one you are currently working in, you use the IN as a clause:

SELECT Name, Address
FROM PianoTuners IN USAPiano.mdb
WHERE state='TN';

If the database is a non-Microsoft Jet database, you must append a semicolon ( ; ) to the database file name and enclose it within a pair of single ( ' ) or double quotes ( " ).

SELECT Name, Address
FROM PianoTuners IN "InstrumentTuners.xls;"
WHERE state='TN';

You can also specify a path and designate the type of file. Note the position of the ( ; ):

SELECT Name, Address
FROM PianoTuners IN "C:\Music\InstrumentTuners.xls" "Excel 5.0;"
WHERE state='TN';

When used as an operator, IN can determine if the values of a specified expression matches any values in a specified list. This example determines if any piano tuners live in 'Knoxville', 'Nashville', or 'Memphis'. A pair of single quotes must enclose each value and commas must separate each value:

SELECT * FROM TennPianoTuners
WHERE City IN ( 'Knoxville', 'Nashville', 'Memphis' );

You can also add a NOT. This causes the query to select all values other than those listed:

SELECT * FROM TennPianoTuners
WHERE City NOT IN ( 'Knoxville', 'Nashville', 'Memphis' );

The SELECT statement can optionally be followed by one of these four predicates: ALL, DISTINCT, DISTINCTROW, TOP. These limit the number of records returned.

The ALL predicate is the default, but it is rarely used. Note that the following two code examples yield the exact same results:

SELECT *
FROM RockAndRoll
WHERE Artist = 'Elvis';

SELECT ALL *
FROM RockAndRoll
WHERE Artist = 'Elvis';

The DISTINCT predicate is used to omit duplicate values just in a field. Consider a table of names, where you have the last name, "Jones", repeated numerous times. This code returns only one "Jones":

SELECT DISTINCT LastName
FROM SongWriters;

The DISTINCTROW predicate is used to omit duplicate values in an entire record of fields. This can be very useful when you use a INNER JOIN to join two tables together and you do not want any duplication. This code create a table that does not repeat any of the last names:

SELECT DISTINCTROW LastName
FROM SongWriters INNER JOIN Performers
ORDER BY LastName;

The TOP predicate returns the specified number of records from the top of the specified table. The following example returns the first 3 records:

SELECT TOP 3 MusicianName AS Musician, Instrument
FROM Musicians;

You can also carry out calculations on fields containing numeric values using the aggregate functions:
AVG - average
COUNT - count how many items
MAX - maximum value
MIN - minimum value
STDEV - sample standard deviation
STDEVP - standard deviation
SUM - add the values
VAR - sample variance
VARP - variance

This next example uses the COUNT function to count the number of items that have an entry in the SalePrice field (i.e. they are on sale) and returns that number under the heading 'ReducedCount':

SELECT COUNT(SalePrice) AS ReducedCount
FROM Products;

...and this next one returns current prices along with what the prices would be after a 10% increase:

SELECT Item, UnitPrice AS CurrentPrice, UnitPrice * 1.1 AS IncreasedPrice
FROM Products;

...and this one lists all items that are reduced along with the price and the amount of the reduction:

SELECT Item, SalePrice AS Price, UnitPrice - SalePrice AS Reduction
FROM Products
WHERE SalePrice <> Null;

Of course, you may want to select fields from more than one table, and you can do that as well. In this case it is best to precede a field name with the name of the table from which it comes, followed by the dot operator ( . ). You must do this for fields of the same name, but from different tables that are used in the SELECT statement. The following example uses two tables, Task and Assignment, and returns the names of all Tasks belonging to Assignments that are incomplete:

SELECT Task.Name, Task.TaskID
FROM Task INNER JOIN Assignment
ON Task.TaskID = Assignment.TaskID
WHERE Assignment.CompletionDate Is Null;

As an alternative to using the explicit INNER JOIN syntax, columns from multiple tables can be combined in a single query by specifying the appropriate table list, and applying the filter condition in the WHERE clause. This is illustrated in the following query, which returns the same recordset as the previous example:

SELECT Task.Name, Task.TaskID
FROM Task, Assignment
WHERE Task.TaskID = Assignment.TaskID
AND Assignment.CompletionDate Is Null;

GROUP BY

The optional GROUP BY clause groups into a single record all records that have identical values in a particular field or combination of fields. The following example returns a list of the different products in the Product field of Suppliers.

SELECT Product FROM Suppliers GROUP BY Product;

HAVING

The HAVING clause is optional and qualifies a GROUP BY clause. It is similar to the WHERE clause but determines which records are displayed after they have been grouped. The following example displays a list of different items, along with their count, but only where there are more than one.

SELECT Item, Count(Item) AS Tally FROM Products
GROUP BY Item HAVING Count(Item) > 1;

ORDER BY

The ORDER BY clause can be used to dictate the order of the records returned. The following example returns records listed primarily in order of tune type (jigs then reels), and then for each type the relevant names are also listed in alphabetical order.

SELECT TuneType AS Type, Name FROM Tunes WHERE TuneType = 'jig' OR TuneType = 'reel'
ORDER BY TuneType, Name;

WITH OWNERACCESS OPTION

In a multi-user environment utilizing secure workgroups, the WITH OWNERACCESS OPTION declaration allows the query to be executed with the same permissions as the owner of the query.

LIKE

The LIKE condition allows you to use wildcards in the where clause of an SQL statement. This allows you to perform pattern matching. The LIKE condition can be used in any valid SQL statement - select, insert, update, or delete.

The patterns that you can choose from are:

% allows you to match any string of any length (including zero length)

_ allows you to match on a single character

Here are a few patterns:

'A_Z': All string that starts with 'A', another character, and end with 'Z'. For example, 'ABZ' and 'A2Z' would both satisfy the condition, while 'AKKZ' would not (because there are two characters between A and Z instead of one).

'ABC%': All strings that start with 'ABC'. For example, 'ABCD' and 'ABCABC' would both satisfy the condition.

'%XYZ': All strings that end with 'XYZ'. For example, 'WXYZ' and 'ZZXYZ' would both satisfy the condition.

'%AN%': All string that contain the pattern 'AN' anywhere. For example, 'LOS ANGELES' and 'SAN FRANCISCO' would both satisfy the condition.

Examples using % wildcard
The first example that we'll take a look at involves using % in the where clause of a select statement. We are going to try to find all of the suppliers whose name begins with 'Hew'.

SELECT * FROM supplier
WHERE supplier_name like 'Hew%';

You can also using the wildcard multiple times within the same string. For example,

SELECT * FROM supplier
WHERE supplier_name like '%bob%';

In this example, we are looking for all suppliers whose name contains the characters 'bob'.

You could also use the LIKE condition to find suppliers whose name does not start with 'T'. For example,

SELECT * FROM supplier
WHERE supplier_name not like 'T%';

By placing the not keyword in front of the LIKE condition, you are able to retrieve all suppliers whose name does not start with 'T'.
Examples using _ wildcard
Next, let's explain how the _ wildcard works. Remember that the _ is looking for only one character.

For example,

SELECT * FROM supplier
WHERE supplier_name like 'Sm_th';

This SQL statement would return all suppliers whose name is 5 characters long, where the first two characters is 'Sm' and the last two characters is 'th'. For example, it could return suppliers whose name is 'Smith', 'Smyth', 'Smath', 'Smeth', etc.

Older versions of Microsoft Access use an * instead of a %, and a ? for it's _.

Here is another example,

SELECT * FROM supplier
WHERE account_number like '12317_';

You might find that you are looking for an account number, but you only have 5 of the 6 digits. The example above, would retrieve potentially 10 records back (where the missing value could equal anything from 0 to 9). For example, it could return suppliers whose account numbers are:

123170
123171
123172
123173
123174
123175
123176
123177
123178
123179

Examples using Escape Characters
Next, in Oracle, let's say you wanted to search for a % or a _ character in a LIKE condition. You can do this using an Escape character.

Please note that you can define an escape character as a single character (length of 1) ONLY.

For example,

SELECT * FROM supplier
WHERE supplier_name LIKE '!%' escape '!';

This SQL statement identifies the ! character as an escape character. This statement will return all suppliers whose name is %.

Here is another more complicated example:

SELECT * FROM supplier
WHERE supplier_name LIKE 'H%!%' escape '!';

This example returns all suppliers whose name starts with H and ends in %. For example, it would return a value such as 'Hello%'.

You can also use the Escape character with the _ character. For example,

SELECT * FROM supplier
WHERE supplier_name LIKE 'H%!_' escape '!';

This example returns all suppliers whose name starts with H and ends in _. For example, it would return a value such as 'Hello_'.