SQL Tutorial - The Basics - Part III
This 3rd aticle introducing SQL will look at the various ways of specifying your criteria for returning results. There are many occaisions when you will now the exact ID or name you're looking for but what about those occaisions when you just know the first initial? or when you want to return all the results in a give date range? The article will look at the various SQL commands that can be used.
The following SQL commands will be covered in this article, clicking an item below will skip directly to that section:
All of the examples in this artilce will continue to use data from the following 'Employees' table:
| FirstName |
Surname |
JobTitle |
Age |
StartDate |
Salary |
| Andrew |
Jones |
Sales Executive |
28 |
10/11/2003 |
49816 |
| John |
Sullivan |
CEO |
44 |
12/10/1997 |
102000 |
| Keith |
Angler |
VP Development |
34 |
12/10/2001 |
72540 |
| Sarah |
Growler |
VP Development |
41 |
11/12/2006 |
81235 |
| Elizabeth |
Armitage |
Admin Assitant |
22 |
12/12/2008 |
29400 |
The SQL IN keyword is used in conjunction with the WHERE statement to allow you to select multiple value for a particular columm. In the example below IN is used to specify 3 different values for the Age field which, if found, will return results:
SELECT
FirstName, Surname, JobTitle, Age, Salary
FROM
Employees
WHERE
Age IN (28, 34, 62)
This would return the following results:
FirstName Surname JobTitle Age Salary
--------- ------- -------- --- ------
Keith Angler VP Development 34 72540
Andrew Jones Sales Executive 28 49816
You could acheive the same results by specifying multiple OR statements but this is much quicker, easier and more manageable.
The BETWEEN keyword allows you specify a start and end value for a range which all values falling between will be returned, i.e.
SELECT
FirstName, Surname, JobTitle, Age, Salary
FROM
Employees
WHERE
Age BEWTEEN 21 AND 35
The following results are returned:
FirstName Surname JobTitle Age Salary
--------- ------- -------- --- ------
Keith Angler VP Development 34 72540
Elizabeth Armitage Admin Assistant 22 29400
Andrew Jones Sales Executive 28 49816
The SQL LIKE keyword allows you to search on a pattern rather that an exact value, this is used when part of a field in known. You can combine LIKE with a number of wildcard characters to enhance your searches.
The wildcards include:
- % (Percent) - Represents one or more unknown characters
- _ (Underscore) - Represents exactly one unknown character
- [x-y] (Square Parenthesis or Brackets) - Represents an unknown character within the range x to y
Using the % wilcard you can create patterns including any letters you know for your criteria, the examples below show; 1. Surnames beginning with a, 2. All surnames containg the letter n anywhere within them and 3. Any surnames which end with the letter n.
SELECT
FirstName, Surname, JobTitle, Age, Salary
FROM
Employees
WHERE
Surname LIKE 'a%'
SELECT
FirstName, Surname, JobTitle, Age, Salary
FROM
Employees
WHERE
Surname LIKE '%n%'
SELECT
FirstName, Surname, JobTitle, Age, Salary
FROM
Employees
WHERE
Surname LIKE '%n'
Returns the following results:
FirstName Surname JobTitle Age Salary
--------- ------- -------- --- ------
Keith Angler VP Development 34 72540
Elizabeth Armitage Admin Assistant 22 29400
FirstName Surname JobTitle Age Salary
--------- ------- -------- --- ------
Keith Angler VP Development 34 72540
Andrew Jones Sales Executive 28 49816
John Sullivan CEO 44 102000
FirstName Surname JobTitle Age Salary
--------- ------- -------- --- ------
John Sullivan CEO 44 102000
Like the % wildcard the _ (underscore) is also used to represent any unknown character, the difference is that the _ applies a little more control as it indicates there is only 1 character unknown. The _ can be used to indicate a character missing before, after or both, a simple example is:
SELECT
FirstName, Surname, JobTitle, Age, Salary
FROM
Employees
WHERE
Surname LIKE '_n%'
will produce the results:
FirstName Surname JobTitle Age Salary
--------- ------- -------- --- ------
Keith Angler VP Development 34 72540
Although Jones and Sullivan also contain the letter n, only Angler has only 1 letter preceding the n.
The final wildcard we are going to look at is []. The square parenthesis (or brackets) are used to store a range of values for our unknown characters. For example [3-7] would indicate that the value must be between 3 and 7, inclusive. A simple example to get all of the employees whose surnames are before h in the alphabet would be:
SELECT
FirstName, Surname, JobTitle, Age, Salary
FROM
Employees
WHERE
Surname LIKE '[a-h]%'
will produce the results:
FirstName Surname JobTitle Age Salary
--------- ------- -------- --- ------
Keith Angler VP Development 34 72540
Elizabeth Armitage Admin Assistant 22 29400
Sarah Growler VP Development 41 81235
The HAVING command allows you to use one of the aggregate statements (AVG, COUNT or SUM) in the WHERE clause of your query. Ordinarily you may try and use something like the query below to return all of the jobs which cost the company over $100k:
SELECT
JobTitle, SUM(Salary) AS 'Total Expenditure'
FROM
Employees
WHERE
Sum(Salary) > 100000
GROUP BY
JobTitle
You might expect to see a resultset containing CEO and VP Developement. Instead the query will throw an error. The way round this is to use a similar statement but using the HAVING keyword, i.e.:
SELECT
JobTitle, SUM(Salary) AS 'Total Expenditure'
FROM
Employees
GROUP BY
JobTitle
HAVING
Sum(Salary) > 100000
This will return the result set:
JobTitle Total Expenditure
-------- -----------------
VP Development 153775
CEO 102000