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_'.
|