SQL: Data Definition & Manipulation

Objectives of SQL

Ideally, a database language should allow a user to:

• create the database and relation structures;
• perform insertion, modification and deletion of data;
• perform simple and complex queries.

SQL is a transform-oriented language with three major components:

SQL consists of standard English words:

CREATE TABLE Staff (staffNo VARCHAR(5),
                    lName   VARCHAR(15),
                    salary  DECIMAL(7,2));

INSERT INTO Staff VALUES ('SG16', 'Brown', 8300);

SELECT staffNo, lName, salary
FROM   Staff
WHERE  salary > 10000;

DreamHome sample data

All examples use the DreamHome case study (Connolly & Begg). Expand a table to check query results yourself.

The SELECT statement

SELECT   [DISTINCT | ALL] {* | [columnExpression [AS newName]] [,...]}
FROM     TableName [alias] [, ...]
[WHERE   condition]
[GROUP BY columnList] [HAVING condition]
[ORDER BY columnList]

Basic retrieval

All columns, all rows

List full details of all staff.

SELECT staffNo, fName, lName, position, sex, DOB, salary, branchNo
FROM   Staff;

-- * is an abbreviation for 'all columns'
SELECT * FROM Staff;

Result: all 6 rows of the Staff table.

Specific columns, all rows

List salaries for all staff, showing only staff number, first and last names, and salary.

SELECT staffNo, fName, lName, salary
FROM   Staff;

Use of DISTINCT

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

SELECT propertyNo FROM Viewing;           -- PA14, PG4, PG4, PA14, PG36
SELECT DISTINCT propertyNo FROM Viewing;  -- PA14, PG4, PG36

DISTINCT eliminates duplicate rows from the result.

Calculated fields

Produce a list of monthly salaries for all staff.

SELECT staffNo, fName, lName, salary/12 AS monthlySalary
FROM   Staff;

Without AS, the column gets a system name (e.g. col4). Use the AS clause to name it.

Search conditions (WHERE)

The five basic search conditions: Comparison Range Set membership Pattern match Null

1. Comparison

List all staff with a salary greater than 10,000.

SELECT staffNo, fName, lName, position, salary
FROM   Staff
WHERE  salary > 10000;

Result: SL21 (30000), SG37 (12000), SG14 (18000), SG5 (24000).

Compound comparison — list addresses of all branch offices in London or Glasgow:

SELECT *
FROM   Branch
WHERE  city = 'London' OR city = 'Glasgow';

Result: B005 (22 Deer Rd, London), B003 (163 Main St, Glasgow), B002 (56 Clover Dr, London).

2. Range — BETWEEN

List all staff with a salary between 20,000 and 30,000.

SELECT staffNo, fName, lName, position, salary
FROM   Staff
WHERE  salary BETWEEN 20000 AND 30000;

-- equivalent:
WHERE  salary >= 20000 AND salary <= 30000;

Result: SL21 John White (30000), SG5 Susan Brand (24000).

• BETWEEN includes the endpoints of the range.
• There is a negated version, NOT BETWEEN.
• BETWEEN doesn’t add expressive power, but is useful for a range of values.

3. Set membership — IN

List all managers and supervisors.

SELECT staffNo, fName, lName, position
FROM   Staff
WHERE  position IN ('Manager', 'Supervisor');

-- equivalent:
WHERE  position = 'Manager' OR position = 'Supervisor';

Result: SL21 White (Manager), SG14 Ford (Supervisor), SG5 Brand (Manager).

• Negated version: NOT IN. IN is more efficient/readable when the set contains many values.

4. Pattern matching — LIKE

Find all owners with the string ‘Glasgow’ in their address.

SELECT ownerNo, fName, lName, address, telNo
FROM   PrivateOwner
WHERE  address LIKE '%Glasgow%';

Result: CO87 Carol Farrel, CO40 Tina Murphy, CO93 Tony Shaw.

5. NULL search condition

List details of all viewings on property PG4 where a comment has not been supplied.

SELECT clientNo, viewDate
FROM   Viewing
WHERE  propertyNo = 'PG4' AND comment IS NULL;

Result: CR56, 26-May-01. There are 2 viewings for PG4 — one with and one without a comment.

Sorting results (ORDER BY)

Single column ordering

SELECT staffNo, fName, lName, salary
FROM   Staff
ORDER BY salary DESC;

Result order: SL21 (30000), SG5 (24000), SG14 (18000), SG37 (12000), SA9 (9000), SL41 (9000).

Multiple column ordering

SELECT propertyNo, type, rooms, rent
FROM   PropertyForRent
ORDER BY type;

There are four flats — with no minor sort key, the system arranges them in any order it chooses. To order by rent within type:

SELECT propertyNo, type, rooms, rent
FROM   PropertyForRent
ORDER BY type, rent DESC;

type is the major sort key; rent is the minor sort key. Default direction is ASC.

Aggregate functions

The ISO standard defines five aggregate functions:

• Each operates on a single column and returns a single value.
• Apart from COUNT(*), each function eliminates nulls first and operates only on the remaining non-null values.
• COUNT(*) counts all rows, regardless of nulls or duplicates.
• DISTINCT before the column name eliminates duplicates. It has no effect on MIN/MAX, but may change SUM/AVG.
• Aggregates can be used only in the SELECT list and the HAVING clause.

SELECT staffNo, COUNT(salary)   -- ILLEGAL
FROM   Staff;

Examples

-- How many properties cost more than £350 per month to rent?
SELECT COUNT(*) AS myCount
FROM   PropertyForRent
WHERE  rent > 350;                         -- 5

-- How many different properties were viewed in May '01?
SELECT COUNT(DISTINCT propertyNo) AS myCount
FROM   Viewing
WHERE  viewDate BETWEEN '1-May-01' AND '31-May-01';   -- 2 (PA14, PG4)

-- Find number of Managers and sum of their salaries.
SELECT COUNT(staffNo) AS myCount, SUM(salary) AS mySum
FROM   Staff
WHERE  position = 'Manager';               -- 2, 54000

-- Find minimum, maximum, and average staff salary.
SELECT MIN(salary) AS myMin, MAX(salary) AS myMax, AVG(salary) AS myAvg
FROM   Staff;                              -- 9000, 30000, 17000

Grouping (GROUP BY and HAVING)

Use GROUP BY to get sub-totals. SELECT and GROUP BY are closely integrated: each item in the SELECT list must be single-valued per group, so the SELECT clause may only contain:

• column names (that are in the GROUP BY)
• aggregate functions
• constants
• expressions combining the above

• All column names in the SELECT list must appear in the GROUP BY clause unless used only inside an aggregate.
• If WHERE is used with GROUP BY, WHERE is applied first, then groups are formed from the remaining rows.
• ISO considers two nulls to be equal for GROUP BY purposes (all nulls form one group).

Find the number of staff in each branch and their total salaries.

SELECT   branchNo, COUNT(staffNo) AS myCount, SUM(salary) AS mySum
FROM     Staff
GROUP BY branchNo
ORDER BY branchNo;

Restricted groupings — HAVING

• HAVING is designed for use with GROUP BY to restrict the groups that appear in the final result.
• Similar to WHERE, but WHERE filters individual rows, HAVING filters groups.
• Column names in HAVING must also appear in the GROUP BY list or be inside an aggregate.

For each branch with more than 1 member of staff, find the number of staff and the sum of their salaries.

SELECT   branchNo, COUNT(staffNo) AS myCount, SUM(salary) AS mySum
FROM     Staff
GROUP BY branchNo
HAVING   COUNT(staffNo) > 1
ORDER BY branchNo;

Result: B003 (3, 54000), B005 (2, 39000). B007 is removed because it has only 1 staff member.

Subqueries

Some SQL statements can have a SELECT embedded within them. A subselect used in the WHERE or HAVING clause of an outer SELECT is called a subquery or nested query. Subselects may also appear in INSERT, UPDATE and DELETE statements.

Subquery with equality

List staff who work in the branch at ‘163 Main St’.

SELECT staffNo, fName, lName, position
FROM   Staff
WHERE  branchNo = (SELECT branchNo
                   FROM   Branch
                   WHERE  street = '163 Main St');

The inner SELECT finds the branch number ('B003'). The outer SELECT then becomes … WHERE branchNo = 'B003'. Result: SG37 Ann Beech, SG14 David Ford, SG5 Susan Brand.

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);

Subquery rules

1. ORDER BY may not be used in a subquery (only in the outermost SELECT).
2. The subquery SELECT list must consist of a single column name or expression, except for subqueries using EXISTS.
3. By default, column names refer to the table in the subquery’s FROM clause; you can refer to an outer table using an alias.
4. When a subquery is an operand in a comparison, it must appear on the right-hand side.
5. A subquery may not be used as an operand in an expression.

Nested subquery using IN

List properties handled by staff at ‘163 Main St’.

SELECT propertyNo, street, city, postcode, type, rooms, rent
FROM   PropertyForRent
WHERE  staffNo IN (SELECT staffNo
                   FROM   Staff
                   WHERE  branchNo = (SELECT branchNo
                                      FROM   Branch
                                      WHERE  street = '163 Main St'));

The middle query returns several staff numbers, so IN is used rather than =. Result: PG16, PG36, PG21.

Multi-table queries (joins)

• Subqueries are fine if all result columns come from the same table.
• If result columns come from more than one table, you must use a join.
• Include more than one table in the FROM clause (comma-separated) and use WHERE to specify the join column(s).
• A table alias is separated from the table name by a space and is used to qualify ambiguous column names.

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;

Equivalent to the equi-join in relational algebra: only rows with identical clientNo values in both tables are included.

Alternative JOIN constructs

FROM Client c JOIN Viewing v ON c.clientNo = v.clientNo
FROM Client JOIN Viewing USING (clientNo)
FROM Client NATURAL JOIN Viewing

In each case the new FROM replaces the original FROM and WHERE. However, the ON version produces a table with two identical clientNo columns; USING and NATURAL JOIN keep only one.

Sorting a join

For each branch, list numbers and names of staff who manage properties, and the properties they manage.

SELECT   s.branchNo, s.staffNo, fName, lName, propertyNo
FROM     Staff s, PropertyForRent p
WHERE    s.staffNo = p.staffNo
ORDER BY s.branchNo, s.staffNo, propertyNo;

Three-table join

For each branch, list staff who manage properties, including the city of the branch and the properties they manage.

SELECT   b.branchNo, b.city, s.staffNo, fName, lName, propertyNo
FROM     Branch b, Staff s, PropertyForRent p
WHERE    b.branchNo = s.branchNo AND s.staffNo = p.staffNo
ORDER BY b.branchNo, s.staffNo, propertyNo;

-- Alternative FROM/WHERE:
FROM (Branch b JOIN Staff s USING (branchNo)) AS bs
     JOIN PropertyForRent p USING (staffNo)

Result: same rows as above plus a city column (Glasgow ×3, London, Aberdeen).

Multiple grouping columns

Find the number of properties handled by each staff member.

SELECT   s.branchNo, s.staffNo, COUNT(*) AS myCount
FROM     Staff s, PropertyForRent p
WHERE    s.staffNo = p.staffNo
GROUP BY s.branchNo, s.staffNo
ORDER BY s.branchNo, s.staffNo;

Result: B003/SG14 → 1, B003/SG37 → 2, B005/SL41 → 1, B007/SA9 → 1.

Computing a join (conceptual procedure)

1. Form the Cartesian product of the tables named in the FROM clause.
2. If there is a WHERE clause, apply the search condition to each row of the product, keeping rows that satisfy it.
3. For each remaining row, determine the value of each item in the SELECT list to produce a single result row.
4. If DISTINCT was specified, eliminate duplicate rows.
5. If there is an ORDER BY, sort the result.

SQL has a special format for the Cartesian product:

SELECT [DISTINCT | ALL] {* | columnList}
FROM   Table1 CROSS JOIN Table2;

EXISTS and NOT EXISTS

• For use only with subqueries; they produce a simple true/false result.
• EXISTS is true if and only if the subquery returns at least one row; false if it returns an empty table. NOT EXISTS is the opposite.
• Since they only check existence, the subquery can contain any number of columns — it is common to write (SELECT * …).

Find all staff who work in a London branch.

SELECT staffNo, fName, lName, position
FROM   Staff s
WHERE  EXISTS (SELECT *
               FROM   Branch b
               WHERE  s.branchNo = b.branchNo AND city = 'London');

Result: SL21 John White (Manager), SL41 Julie Lee (Assistant).

The same query written as a join:

SELECT staffNo, fName, lName, position
FROM   Staff s, Branch b
WHERE  s.branchNo = b.branchNo AND city = 'London';

INSERT

INSERT INTO TableName [(columnList)]
VALUES (dataValueList);

The dataValueList must match the columnList:

• the number of items in each list must be the same;
• there must be direct correspondence in position;
• the data type of each value must be compatible with its column.

INSERT … VALUES (all columns)

INSERT INTO Staff
VALUES ('SG16', 'Alan', 'Brown', 'Assistant', 'M', DATE '1957-05-25', 8300, 'B003');

INSERT using defaults (mandatory columns only)

INSERT INTO Staff (staffNo, fName, lName, position, salary, branchNo)
VALUES ('SG44', 'Anne', 'Jones', 'Assistant', 8100, 'B003');

-- or, listing every column and using NULL:
INSERT INTO Staff
VALUES ('SG44', 'Anne', 'Jones', 'Assistant', NULL, NULL, 8100, 'B003');

INSERT … SELECT

A second form of INSERT copies multiple rows from one or more tables into another:

INSERT INTO TableName [(columnList)]
SELECT ...

Populate StaffPropCount(staffNo, fName, lName, propCnt) using Staff and PropertyForRent.

INSERT INTO StaffPropCount
  (SELECT s.staffNo, fName, lName, COUNT(*)
   FROM   Staff s, PropertyForRent p
   WHERE  s.staffNo = p.staffNo
   GROUP BY s.staffNo, fName, lName)
UNION
  (SELECT staffNo, fName, lName, 0
   FROM   Staff
   WHERE  staffNo NOT IN (SELECT DISTINCT staffNo
                          FROM   PropertyForRent));

If the second part of the UNION is omitted, staff who currently manage no properties (SL21, SG5) are excluded.

UPDATE

UPDATE TableName
SET    columnName1 = dataValue1 [, columnName2 = dataValue2 ...]
[WHERE searchCondition];

• TableName can be a base table or an updatable view.
• SET names one or more columns to update.
• WHERE is optional: if omitted, the named columns are updated for all rows; if specified, only rows satisfying the condition are updated.
• New values must be compatible with the column data types.

-- Give all staff a 3% pay increase.
UPDATE Staff SET salary = salary * 1.03;

-- Give all Managers a 5% pay increase.
UPDATE Staff SET salary = salary * 1.05
WHERE  position = 'Manager';

-- Promote David Ford (SG14) to Manager and change his salary to £18,000.
UPDATE Staff SET position = 'Manager', salary = 18000
WHERE  staffNo = 'SG14';

DELETE

DELETE FROM TableName
[WHERE searchCondition];

• TableName can be a base table or an updatable view.
• If the search condition is omitted, all rows are deleted — but the table itself is not deleted (that would be DROP TABLE).

-- Delete all viewings that relate to property PG4.
DELETE FROM Viewing WHERE propertyNo = 'PG4';

-- Delete all records from the Viewing table.
DELETE FROM Viewing;

Quick review questions