CITS1402 Relational Database Management Systems
Week 3—SQL: Data Manipulation
Dr Mehwish Nasim/ CSSE
Sem1/2024
cits1402-sem1-pmc@uwa.edu.au
Objectives
How to retrieve data from database using SELECT and:
Use compound WHERE conditions.
Use aggregate functions.
Sort query results using ORDER BY.
Group data using GROUP BY and HAVING.
Use subqueries.
Join tables together.
Perform set operations (UNION, INTERSECT, EXCEPT).
Reference book: Database Systems by Thomas Connolly
Objectives
Soon…
How to update database using INSERT, UPDATE, and DELETE.
3
Objectives
How to retrieve data from database using SELECT and:
Use compound WHERE conditions.
Use aggregate functions.
Sort query results using ORDER BY.
Group data using GROUP BY and HAVING.
Use subqueries.
Join tables together.
Perform set operations (UNION, INTERSECT, EXCEPT).
4
SELECT Statement
SELECT [DISTINCT | ALL]
{* | [columnExpression [AS newName]] [,...] }
FROM TableName [alias] [, ...]
[WHERE condition]
[GROUP BY columnList]
[HAVING condition]
[ORDER BY columnList]
5
SELECT Statement
SELECT Specifies which columns are to
appear in output.
FROM Specifies table(s) to be used.
WHERE Filters rows.
GROUP BY Forms groups of rows with same
column value.
HAVING Filters groups subject to some
condition.
ORDER BY Specifies the order of the output.
6
SELECT Statement
Order of the clauses cannot be changed.
Only SELECT and FROM are mandatory.
SELECT is akin to PROJECTION (∏) in RA
WHERE is akin to SELECTION (σ) in RA } More on this on 14th March
7
Before we formally start SQL statements
Know thy SCHEMA
8
DreamHome Database
Branch (branchNo, street, city, postcode)
Staff (staffNo, fName, lName, position, sex, DOB, salary,
branchNo)
PropertyForRent (propertyNo, street, city, postcode, type, rooms,
rent, ownerNo, staffNo, branchNo)
Client (clientNo, fName, lName, telNo, prefType,
maxRent, email)
PrivateOwner (ownerNo, fName, lName, address, telNo, email,
password)
Viewing (clientNo, propertyNo, viewDate, comment)
Registration (clientNo, branchNo, staffNo, dateJoined)
9
Example All Columns, All Rows
10
Example All Columns, All Rows
List full details of all staff.
SELECT staffNo, fName, lName,
position, sex, DOB, salary, branchNo
FROM Staff;
Can use * as an abbreviation for ‘all columns’:
SELECT *
FROM Staff;
11
Example All Columns, All Rows
12
Example Specific Columns, All Rows
13
Example 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;
14
Example Use of DISTINCT
List the property numbers of all properties that have been viewed.
SELECT propertyNo
FROM Viewing;
15
Example Use of DISTINCT
Use DISTINCT to eliminate duplicates:
SELECT DISTINCT propertyNo
FROM Viewing;
16
Example 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;
17
Example Calculated Fields AS Clause
Not very informative
To name column, use AS clause:
SELECT staffNo, fName, lName,
salary/12 AS monthlySalary
FROM Staff;
18
Row Selection (WHERE clause)
Comparison
Range
Set Membership
Pattern Match
Null
19
Example 6.5 Comparison Search Condition
WHERE
List all staff with a salary greater than 10,000.
20
Example 6.5 Comparison Search Condition
WHERE
List all staff with a salary greater than 10,000.
SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary > 10000;
21
Example Comparison Search Condition WHERE
In SQL the following simple comparison operators are available:
= equals
<> is not equal to (maybe also !=)
< is less than
<= is less than or equal to
> is greater than
>= is greater than or equal to
22
Example Comparison Search Condition WHERE
More complex predicates can be created using AND, OR, and NOT
Evaluation Order
An expression is evaluated left to right
Subexpression in brackets are evaluated first
NOTs are evaluated before ANDs and ORs
ANDs are evaluated before ORs
Make life easier => USE BRACKETS!
23
Example Compound Comparison Search Condition: AND and
OR List addresses of all branch offices in London or Glasgow.
SELECT *
FROM Branch
WHERE city = ‘London’ OR city = ‘Glasgow’;
24
