1.

To see all of the columns/fields from all of the rows/records in the projects table:

SELECT *
FROM projects;

Note that the line breakdown is for form and ease of reading, the statement could also be written as:

SELECT * FROM projects;

Note also that the instruction itself is not case sensitive and could be written as:

select * from projects;

or as

SELECT * FROM PROJECTS;

The format of the select is the verb SELECT followed by a list of the columns/fields that the user wants displayed followed by the clause FROM and the name of the table/file that contains the columns/fields. The use of the * means that all columns/fields are to be displayed.

2.

To see the columns/fields projectid and budget from the projects table:

SELECT projectid, budget
FROM projects;

3.

To see the columns/fields projectid and budget from the projects table for all rows/records where the budget is greater than 12000:

SELECT projectid, budget
FROM projects
WHERE budget > 12000;

The WHERE clause can be added to the basic SELECT statement to select rows/records that meet a specified criteria. The WHERE clause can contain complex conditions using AND and OR.

4.

To see a specific record, the user can use the WHERE clause to test for the key equal to a particualr value:

SELECT *
FROM projects
WHERE projectid = "Graphic";

Note that in testing the value there is case sensitivity. Graphic has to be written exactly as the data was entered on the file. Also be aware that some implementations of SQL allow
the quote to be either " or ' while others require a " or '.

5.

Using a compound AND:

SELECT *
FROM budget
WHERE projectid="Graphic" AND budget > 50000;

6.

Using a compound OR:

SELECT *
FROM staff
WHERE title="Programmer" OR hourlyrate > 50;

7.

Using a complex AND and OR:

SELECT *
FROM tasks

Where hours >20 AND (projectid="Graphic" OR projectid="Program");
Notice that SQL follows the traditional AND gets resolved before OR so ( ) must be used to group the OR and have it resolved first.
 

8.

Using the BETWEEN option instead of AND:

SELECT *
FROM staff
WHERE hourlyrate > 49 AND hourlyrate < 61;

SELECT *
FROM staff
WHERE hourlyrate BETWEEN 50 AND 60;

These result in the same records being retrieved. BETWEEN is inclusive so both 50 and 60 will be included in the results.

9.

To see a column/field that is not on the database but is the result of a calculation, the user can implement a computed column. This can mean subtracting one column/field from another, adding them together etc. In this example, 10% of the budget will be displayed. Note that the parenthesis around the computed column are optional.

SELECT projectid, (budget * .1), year
FROM budget;
SELECT projectid, budget * .1, year
FROM budget;

10.

Some versions of SQL allow the user to scan a field looking for a match within the field with the LIKE clause. To do this, the % wildcard is used. The wildcard means it doesn't matter what precedes or follows the string. For example, to locate the word Programmer in the title field of the staff table, the following could be used.

SELECT *
FROM staff
WHERE title LIKE "%Programmer%";

Obviously, if you knew there would never be anything after Programmer, you could use "%Programmer".
Some versions of SQL will use the * instead of the %.
There is also a ? wildcard that replaces a single unknown character. In this example I am looking for a p in the third character. I don't care what the first two characters are and there can be any number of characters after the p.

SELECT *
FROM projects
WHERE name = "??p%";

Note: The % means any number of unknown characters, while the ? wildcard means a single character is unknown.

11.

The following example asks that any names that begin with a letter in the range D through G should be displayed. The letters in the range are bracketed and the % means that any number of characters can follow.

SELECT name
FROM staff
Where NAME = "[D-G]%";

To select a field that contains blanks, two quotes next to each other are used (in some versions, they must be specifically single or double quotes).

SELECT name, title, manager
FROM staff
WHERE manager = ' ';

12.

SQL allows the use of the clause IN to set up a group of values so that the user can look for a match to one of the values in the group.

SELECT *
FROM staff
WHERE hourlyrate in (35.00, 40.00, 45.00, 50.00);

13.

The DISTINCT clause allows the user to select only one occurrence of a name instead of all. For example to look at the staff table and ask for a list of managers the same names would appear multiple times. To see each managers name on the list only once, the DISTINCT clause could be used.

SELECT DISTINCT manager
FROM staff;

14.

With SQL, the user can alter the order of the displayed table using the ORDER BY clause. Some versions require the use of the word DISTINCT when an order by is used, others do not.

SELECT name, title, manager
FROM staff
ORDER BY manager;

The default will be in ascending order, to get descending order the clause DESC should be used. The sort can also be done on multiple fields. In this example, the staff table will be sorted on title and hourlyrate withing title. The hourlyrate is in descending order.

SELECT name, title, hourlyrate
FROM staff
ORDER BY title, hourlyrate DESC;

Other examples:

SELECT name, title, manager
FROM staff
ORDER BY title;

SELECT name, title, manager, hourlyrate
FROM staff
ORDER BY TITLE, hourlyrate DESC;

15.

SQL has built in functions to allow the user to COUNT, SUM, determine the AVG, MAX and MIN.

SELECT COUNT(*)
FROM staff;

SELECT COUNT(*)
FROM staff
WHERE title = "Programmer";

Some versions of SQL do not support the (*) and the implementation would be:

SELECT COUNT(name)
FROM STAFF
WHERE title = "Programmer";

Multiple functions can be done with one select statement:

SELECT COUNT(name) AVG(hourlyrate)
FROM STAFF
WHERE title = "Programmer";

16.

The GROUP BY option allows the user to essentially develop stand alone or group printed minor total lines - grouped on a certain column/field. For example, in the second of these two examples the sum of hours is gathered and displayed for each projectid.

SELECT projectid
FROM tasks
GROUP BY projectid;

SELECT projectid, sum(hours)
FROM tasks
GROUP BY projectid;

SELECT title, count(name)
FROM staff
WHERE hourlyrate > 40
GROUP BY title;

17.

The HAVING clause can be used to test a group (the where clause is used to run tests on individual rows/records and the having clause is used to test a grouping).

SELECT projectid, totalhours=sum(hours)
FROM tasks
GROUP BY projectid
HAVING SUM(hours) > 200;

In this variation, the order of the list is going to be by the totalhours (the sum of the hours). Note the use of distinct with order by - this is a requirement in some implementations of SQL and not in others.

SELECT DISTINCT projectid, totalhours=sum(hours)
FROM tasks
GROUP BY projectid
HAVING SUM(hours) > 200
ORDER BY totalhours;

This example figures the average hourly rate for analysts, programmers and operators who make at least 40 per hour.

SELECT title, avgrate = avg(hourlyrate)
FROM staff
WHERE hourlyrate >= 40
GROUP BY title
HAVING title IN ("Analyst", "Programmer", "Operator");
 

18.

In the next example, there is an embedded SELECT. When embedded selects occur, the inner select is evaluated or resolved first and the result is set up as a temporary table. Then the outer select is evaluated against this result.

SELECT name, hourlyrate
FROM staff
WHERE hourlyrate IN
(SELECT MAX(hourlyrate)
FROM staff
WHERE title = "Programmer");

In this example, the inner select is resolved first so the MAX(hourlyrate) for Programmer is selected from staff. Once this figure has been established, the outer select is executed and the name and hourlyrate are displayed for all who match the selected hourlyrate.
The next example will first get the max hours from the tasks table for all records with the projectid for Expense and then bring all tasks that match that selection.

SELECT projectid, tasks, hours
FROM tasks
WHERE hours in
(SELECT MAX(hours)
FROM tasks
WHERE projectid = "Expense");

The next set of examples uses SQL to query multiple tables. This is illustrated two ways: the first method involves joinging two tables togeter and the second involves the use of the clause IN. When using two tables, the columns/fields listed in the SELECT verb must specify what table/file they are from, the FROM clause must contain the names of all tables/files being used and the WHERE clause is used to establish the relationship between the tables/files (the relationship specifies how the tables/files are joined).

19

In this example, the information is printed both from the budget table and the projects table. The two tables are joined together or related by the match in projectid.

SELECT budget.projectid, projects.description, budget.budget, budget.year
FROM budget, projects
WHERE budget.projectid = projects.projectid;

Since we are using two tables, in the select statement we are specifying which table contains the field.
In this example, the only change is the addition of a condition in the WHERE clause specifying that the budget in the budget file must be greater than 15000.

SELECT budget.projectid, projects.description, budget.budget, budget.year
FROM budget, projects
WHERE budget.projectid = projects.projectid AND budget.budget > 15000;

In this example an alias is used to keep from having to specify the whole name of the table/file with each column/field. The alias is specified in the FROM clause directly after the table name.

SELECT b.projectid, p.description, b.budget, b.year
FROM budget b, projects p
WHERE b.projectid = p.projectid and b.budget > 15000;

20.

In this example, the IN clause is ued to gather criteria from one table, however, the data that is displayed all comess from the projects table.

SELECT projectid, description, duedate
FROM projects
WHERE projectid in
(SELECT projectid
FROM budget
WHERE budget > 15000);

The inner select is executed first and the projectid for all rows/records where the budget is > 15000 are collected as a temporary table. The next step is to display the information from the projects table where the projectid in the projects table matches a projectid in the temporary table.
This example uses three selects - remember, the inner select gets resolved first.

SELECT DISTINCT manager
FROM staff
WHERE name IN
(SELECT name
FROM pojects
WHERE projectid IN
(SELECT projectid
FROM budget
WHERE year = 1996));

First the year of 1996 is located and a temporary list of projectid that meet this criteria is established. Then the search is made in the task table for records with a projectid that matches this temporary table. A temporary table of the name from those records is established. Last the staff table is used to display recorders where the manaager matches the name in the second temporary table. The distinct clause assures that each managers name will appear only once.

21.

The UNION clause temporarily joins two or more select statements and the result gets displayed as a single table. In this example we want a joined list of people from two tables that meet different criteria.

SELECT name
FROM staff
WHERE manager = "Doe, John"
UNION
SELECT name
FROM tasks
WHERE projectid = "Graphics";

22.

The INNER JOIN is another way of selecting records from two tables.

SELECT budget.projectid, projects.description, budget.budget, budget.year
FROM budget INNER JOIN projects
ON budget.projectid = projects.projectid;