SELECT Statement

SELECT Specifies which columns are to

appear in output.
FROM Specifies table(s) to be used.
WHERE Filters rows.

1
© Pearson Education Limited 1995, 2005

Example 5.1 All Columns, All Rows

List full details of all staff.

SELECT staffNo, fName, lName, address,

position, sex, DOB, salary, branchNo
FROM Staff;
 Can use * as an abbreviation for ‘all columns’:
SELECT *
FROM Staff;

2
© Pearson Education Limited 1995, 2005

1
Example 5.1 All Columns, All Rows

3
© Pearson Education Limited 1995, 2005

Example 5.2 Specific Columns, All Rows

Produce a list of salaries for all staff, showing only
staff number, first and last names, and salary.

SELECT staffNo, fName, lName, salary

FROM Staff;

4
© Pearson Education Limited 1995, 2005

2
Example 5.2 Specific Columns, All Rows

5
© Pearson Education Limited 1995, 2005

Example 5.3 Use of DISTINCT

List the property numbers of all properties that
have been viewed.

SELECT propertyNo
FROM Viewing;

6
© Pearson Education Limited 1995, 2005

3
Example 5.3 Use of DISTINCT
 Use DISTINCT to eliminate duplicates:
SELECT DISTINCT propertyNo
FROM Viewing;

7
© Pearson Education Limited 1995, 2005

Example 5.4 Calculated Fields

Produce list of monthly salaries for all staff,
showing staff number, first/last name, and salary.
SELECT staffNo, fName, lName, salary/12
FROM Staff;

© Pearson Education Limited 1995, 2005

4
Example 5.4 Calculated Fields
 To name column, use AS clause:
SELECT staffNo, fName, lName, salary/12
AS monthlySalary
FROM Staff;

9
© Pearson Education Limited 1995, 2005

Example 5.5 Comparison Search Condition

List all staff with a salary greater than 10,000.
SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary > 10000;

10
© Pearson Education Limited 1995, 2005

5
Example 5.6 Compound Comparison Search
Condition
List addresses of all branch offices in London or
Glasgow.
SELECT *
FROM Branch
WHERE city = ‘London’ OR city = ‘Glasgow’;

11
© Pearson Education Limited 1995, 2005

Subqueries
 Some SQL statements can have a SELECT
embedded within them.

12
© Pearson Education Limited 1995, 2005

6
Example 5.19 Subquery with Equality
List staff who work in branch at ‘163 Main St’.

SELECT staffNo, fName, lName, position

FROM Staff
WHERE branchNo =
(SELECT branchNo
FROM Branch
WHERE street = ‘163 Main St’);

13
© Pearson Education Limited 1995, 2005

Example 5.19 Subquery with Equality

 Inner SELECT finds branch number for branch
at ‘163 Main St’ (‘B003’).
 Outer SELECT then retrieves details of all staff
who work at this branch.
 Outer SELECT then becomes:

SELECT staffNo, fName, lName, position

FROM Staff
WHERE branchNo = ‘B003’;

14
© Pearson Education Limited 1995, 2005

7
Example 5.19 Subquery with Equality

15
© Pearson Education Limited 1995, 2005

Example 5.20 Subquery with Aggregate

List all staff whose salary is greater than the average
salary, and show by how much.

SELECT staffNo, fName, lName, position,

salary – (SELECT AVG(salary) FROM Staff) As SalDiff
FROM Staff
WHERE salary >
(SELECT AVG(salary)
FROM Staff);

16
© Pearson Education Limited 1995, 2005

8
Example 5.20 Subquery with Aggregate
 Cannot write ‘WHERE salary > AVG(salary)’
 Instead, use subquery to find average salary
(17000), and then use outer SELECT to find those
staff with salary greater than this:

SELECT staffNo, fName, lName, position,

salary – 17000 As salDiff
FROM Staff
WHERE salary > 17000;

17
© Pearson Education Limited 1995, 2005

Example 5.20 Subquery with Aggregate

18
© Pearson Education Limited 1995, 2005

9
Multi-Table Queries
 Can use subqueries provided result columns come
from same table.
 If
result columns come from more than one table
must use a join.
 Toperform join, include more than one table in
FROM clause.
 Usecomma as separator and typically include
WHERE clause to specify join column(s).

19
© Pearson Education Limited 1995, 2005

Multi-Table Queries
 Also
possible to use an alias for a table named in
FROM clause.

 Alias is separated from table name with a space.

 Alias can be used to qualify column names when

there is ambiguity.

20
© Pearson Education Limited 1995, 2005

10
Example 5.24 Simple Join
List names of all clients who have viewed a
property along with any comment supplied.

SELECT c.clientNo, fName, lName,

propertyNo, comment
FROM Client c, Viewing v
WHERE c.clientNo = v.clientNo;

21
© Pearson Education Limited 1995, 2005

Example 5.24 Simple Join

 Only those rows from both tables that have
identical values in the clientNo columns
(c.clientNo = v.clientNo) are included in result.
 Equivalent to equi-join in relational algebra.

22
© Pearson Education Limited 1995, 2005

11