CH/S6CS/May 2005
Structured Query Language (SQL)
¨ It is a set-oriented database language used to manipulate and retrieve data from relational database.
¨ SQL has two main components:
i. Data Definition Language (DDL) for describing the details of the database for housing data.
ii. Data Manipulation Language (DML) for retrieving and updating instances of data in the database.
DDL and DML
¨ Statements that are fundamental to DDL include:
i. CREATE, for creating table definition
ii. DROP, for removing tables from a database.
¨ Statements that are fundamental to DML include:
i. SELECT, for retrieving data from tables
ii. INSERT, for adding new rows (records) to a table
iii. UPDATE, for modifying rows (records) in a table
iv. DELETE, for removing tows (records) from a table
CREATE
¨ The general form of the CREATE statement is as follows
CREATE TABLE <table> (<column_definition_list>)
¨ Each column must be allocated an appropriate data type.
¨ Types can be used:
i. char: character string (alphabet or number, with the number of characters specified in blacket)
ii. integer: integer (maximum 32767)
iii. long: long integer (maximum 2147483647)
iv. single: real number
v. double: long real number
vi. date: date (with month and day validation)
vii. logical: true or false
¨ E.g.
CREATE TABLE STUDENT2 (SID char(4), NAME char(30), CLASS char(2),
CNum integer, PHONE char(8), SEX char(1), DOB date, DIND logical)
¨ The above sample SQL command creates a new empty table with the following fields:
Field name / Field type / Field lengthSID / Char / 4
NAME / Char / 30
CLASS / Char / 2
CNum / Integer
PHONE / Char / 8
SEX / Char / 1
DOB / Date
DIND / Logical
¨ Here are some sample records using the above structure:
SID / NAME / CLASS / CNUM / PHONE / SEX / DOB / DIND0301 / CHAN TAI MAN / 1A / 3 / 23451234 / M / 02/11/90 / F
0302 / WONG SIU KEUNG / 1B / 11 / 23421223 / M / 23/08/91 / F
0303 / CHU SIN LAI / 1A / 6 / 25678977 / F / 14/04/90 / F
0304 / YIP FONG WAH / 1B / 12 / 23532122 / F / 21/07/91 / T
0305 / SZETO CHI YAN / 1A / 11 / 23189000 / M / 30/05/91 / F
0306 / TSE MEI PING / 1B / 10 / 20989334 / F / 01/03/91 / F
0307 / CHAN FUNG YEE / 1A / 2 / 21859193 / F / 03/11/90 / F
0308 / TSANG WAI PING / 1B / 7 / 21426015 / F / 22/06/90 / F
0309 / LAW TAI YAN / 1A / 9 / 20992837 / M / 22/08/91 / F
0310 / MA KA MAN / 1B / 6 / 22559659 / M / 06/08/91 / F
DROP
¨ The general form of the DROP statement is as follows
DROP TABLE <table>
¨ E.g.
DROP TABLE STUDENT2
SELECT
¨ The simplified general form of the SELECT statement is as follows
SELECT [DISTINCT] <column(s)> [AS <cname>]
FROM <table>
[WHERE <condition>]
[ORDER BY <cname> [DESC] [, <cname> [DESC], ...]
¨ The simplest form of the SELECT statement is as follows
SELECT <column(s)> FROM <table>
¨ E.g. If the above table is queried by executing the following SELECT commands,
SELECT CLASS, NAME FROM STUDENT2
SELECT CLASS FROM STUDENT2
the resulting table retrieved will be as follows respectively,
CLASS / NAME / CLASS1A / CHAN TAI MAN / 1A
1B / WONG SIU KEUNG / 1B
1A / CHU SIN LAI / 1A
1B / YIP FONG WAH / 1B
1A / SZETO CHI YAN / 1A
1B / TSE MEI PING / 1B
1A / CHAN FUNG YEE / 1A
1B / TSANG WAI PING / 1B
1A / LAW TAI YAN / 1A
1B / MA KA MAN / 1B
¨ The keyword DISTINCT
i. We may include the keyword DISTINCT TO remove the duplicates in the output.
ii. E.g. If the table on page 2 is queried by executing the following SELECT command,
SELECT DISTINCT CLASS FROM STUDENT2
the resulting table will be as follows,
CLASS1A
1B
¨ The WHERE clause
i. It specifies a condition to filter out only those rows satisfy the condition.
ii. E.g. If the table on page 2 is queried by executing the following SELECT command,
SELECT NAME FROM STUDENT2 WHERE CLASS='1A'
the resulting table will be as follows,
NAMECHAN TAI MAN
CHU SIN LAI
SZETO CHI YAN
CHAN FUNG YEE
LAW TAI YAN
¨ The ORDER BY clause
i. It specifies how to order the rows in the result.
ii. E.g. If the table on page 2 is queried by executing the following SELECT commands,
SELECT NAME FROM STUDENT2 WHERE CLASS='1A' ORDER BY NAME
the resulting table will be as follows,
NAMECHAN FUNG YEE
CHAN TAI MAN
CHU SIN LAI
LAW TAI YAN
SZETO CHI YAN
¨ The wildcard "*"
i. You can use the wildcard symbol “*” in the field list to select all field
ii. E.g. If the table on page 2 is queried by executing the following SELECT commands,
SELECT * FROM STUDENT2 ORDER BY CLASS, DOB DESC
the resulting table will be as follows,
SID / NAME / CLASS / CNUM / PHONE / SEX / DOB / DIND0309 / LAW TAI YAN / 1A / 9 / 20992837 / M / 22/08/91 / F
0305 / SZETO CHI YAN / 1A / 11 / 23189000 / M / 30/05/91 / F
0307 / CHAN FUNG YEE / 1A / 2 / 21859193 / F / 03/11/90 / F
0301 / CHAN TAI MAN / 1A / 3 / 23451234 / M / 02/11/90 / F
0303 / CHU SIN LAI / 1A / 6 / 25678977 / F / 14/04/90 / F
0302 / WONG SIU KEUNG / 1B / 11 / 23421223 / M / 23/08/91 / F
0310 / MA KA MAN / 1B / 6 / 22559659 / M / 06/08/91 / F
0304 / YIP FONG WAH / 1B / 12 / 23532122 / F / 21/07/91 / T
0306 / TSE MEI PING / 1B / 10 / 20989334 / F / 01/03/91 / F
0308 / TSANG WAI PING / 1B / 7 / 21426015 / F / 22/06/90 / F
¨ Adding functions
i. All database functions can be used in SQL.
ii. E.g. If the table on page 2 is queried by executing the following SELECT command,
SELECT NAME, MONTH(DOB) FROM STUDENT2 WHERE CLASS='1A'
the resulting table will be as follows,
NAME / Expr1001CHAN TAI MAN / 11
CHU SIN LAI / 4
SZETO CHI YAN / 5
CHAN FUNG YEE / 11
LAW TAI YAN / 8
iii. E.g. If the table on page 2 is queried by executing the following SELECT commands,
SELECT NAME, DOB FROM STUDENT2 WHERE MONTH(DOB)>6
the resulting table will be as follows,
NAME / DOBCHAN TAI MAN / 02/11/90
WONG SIU KEUNG / 23/08/91
YIP FONG WAH / 21/07/91
CHAN FUNG YEE / 03/11/90
LAW TAI YAN / 22/08/91
MA KA MAN / 06/08/91
iv. E.g. If the table on page 2 is queried by executing the following SELECT commands,
SELECT NAME, DOB FROM STUDENT2 WHERE MONTH(DOB)>6 ORDER BY MONTH(DOB)
the resulting table will be as follows,
NAME / DOBYIP FONG WAH / 21/07/91
WONG SIU KEUNG / 23/08/91
LAW TAI YAN / 22/08/91
MA KA MAN / 06/08/91
CHAN TAI MAN / 02/11/90
CHAN FUNG YEE / 03/11/90
¨ Using Alias
i. You can apply alias name to make column names more readable.
ii. E.g. If the table on page 2 is queried by executing the following SELECT commands,
SELECT YEAR(DOB) AS BIRTHYEAR, PHONE FROM STUDENT2
the resulting table will be as follows,
BIRTHYEAR / PHONE1990 / 23451234
1991 / 23421223
1990 / 25678977
1991 / 23532122
1991 / 23189000
1991 / 20989334
1990 / 21859193
1990 / 21426015
1991 / 20992837
1991 / 22559659
INSERT
¨ The general form of the INSERT statement is as follows
INSERT INTO <table>
[(<column(s))] VALUES (<constant(s)>)
¨ E.g. If the table on page 2 is modified by executing the following INSERT command,
INSERT INTO STUDENT2
VALUES ('0401', 'INSERT EXAMPLE ONE', '1E',1,
'12345678','M',#01/04/1993#,False)
the following record will be inserted,
SID / NAME / CLASS / CNUM / PHONE / SEX / DOB / DIND0401 / INSERT EXAMPLE ONE / 1E / 1 / 12345678 / M / 01/04/1993 / False
UPDATE
¨ The general form of the UPDATE statement is as follows
UPDATE <table>
SET (<column_assignment_list>)
WHERE <condition>
¨ E.g. If the table on page 2 is modified by executing the following UPDATE command,
UPDATE STUDENT2 SET DIND = True
the DIND of all record will become True.
¨ E.g. If the table on page 2 is modified by executing the following UPDATE command,
UPDATE STUDENT2 SET CLASS = '1E' WHERE YEAR(DOB)=1990U
the resulting table will be as follows,
SID / NAME / CLASS / CNUM / PHONE / SEX / DOB / DIND0301 / CHAN TAI MAN / 1E / 3 / 23451234 / M / 02/11/90 / F
0302 / WONG SIU KEUNG / 1B / 11 / 23421223 / M / 23/08/91 / F
0303 / CHU SIN LAI / 1E / 6 / 25678977 / F / 14/04/90 / F
0304 / YIP FONG WAH / 1B / 12 / 23532122 / F / 21/07/91 / T
0305 / SZETO CHI YAN / 1A / 11 / 23189000 / M / 30/05/91 / F
0306 / TSE MEI PING / 1B / 10 / 20989334 / F / 01/03/91 / F
0307 / CHAN FUNG YEE / 1E / 2 / 21859193 / F / 03/11/90 / F
0308 / TSANG WAI PING / 1E / 7 / 21426015 / F / 22/06/90 / F
0309 / LAW TAI YAN / 1A / 9 / 20992837 / M / 22/08/91 / F
0310 / MA KA MAN / 1B / 6 / 22559659 / M / 06/08/91 / F
DELETE
¨ The general form of the DELETE statement is as follows
DELETE FROM <table> WHERE <condition>
¨ E.g. If the table on page 2 is modified by executing the following DELETE commands,
DELETE FROM STUDENT2 WHERE CNUM > 9
the resulting table will be as follows,
SID / NAME / CLASS / CNUM / PHONE / SEX / DOB / DIND0301 / CHAN TAI MAN / 1A / 3 / 23451234 / M / 02/11/90 / F
0303 / CHU SIN LAI / 1A / 6 / 25678977 / F / 14/04/90 / F
0307 / CHAN FUNG YEE / 1A / 2 / 21859193 / F / 03/11/90 / F
0308 / TSANG WAI PING / 1B / 7 / 21426015 / F / 22/06/90 / F
0309 / LAW TAI YAN / 1A / 9 / 20992837 / M / 22/08/91 / F
0310 / MA KA MAN / 1B / 6 / 22559659 / M / 06/08/91 / F
Forming Groups
¨ There are many cases that more than one rows need to be considered tat the same time to obtain the result.
¨ One example is to find the average mark of all students of each class. Then we need to group the rows according to class and the find the average.
¨ SQL provides a number of special functions, called aggregate functions, which can be included in the SELECT clause:
i. AVG – average
ii. MAX – maximum
iii. MIN – minimum
iv. SUM – sum
v. COUNT – counting non-blank values
¨ E.g. If the table on page 2 is modified by executing the following UPDATE command,
SELECT AVG(CNUM), MAX(CNUM), MIN(CNUM) FROM STUDENT2 WHERE SEX = 'F'
the result will be as follows,
Expr1001 / Expr1002 / Expr10036.25 / 12 / 2
¨ The GROUP BY clause
i. With this clause, we can divide the records into groups of same value in some column(s).
ii. E.g. If the table on page 2 is queried by executing the following SELECT command,
SELECT CLASS, MAX(CNUM), MIN(CNUM) FROM STUDENT2 GROUP BY CLASS
the resulting table will be as follows,
CLASS / Expr1001 / Expr10021A / 11 / 2
1B / 12 / 6
¨ The HAVING clause
i. It tests aggregate result(s) of groups and filters groups, instead of individual row, not fulfuling the condition
ii. E.g. If the table on page 2 is queried by executing the following SELECT command,
SELECT CLASS, MAX(CNUM), MIN(CNUM) FROM STUDENT2
GROUP BY CLASS HAVING MAX(CNUM) > 11
the result will be as follows,
CLASS / Expr1001 / Expr10021B / 12 / 6
More on Conditions
¨ Different operators can be used on set more complicated conditions under the WHERE or HAVING clause.
i. Arithmetic operators: +, –, *, /, MOD
ii. Relational operators: =, >, <, >, >=, <=, IN, LIKE, BETWEEN
iii. Logical operators: AND, OR, NOT
¨ E.g. If the table on page 2 is queried by executing the following SELECT command,
SELECT CNUM, PHONE FROM STUDENT2
WHERE CNUM mod 3 > 0 AND CNUM mod 5 > 0
the resulting table will be as follows (records from which the class number is indivisible by 3 and 5.
CNUM / NAME11 / WONG SIU KEUNG
11 / SZETO CHI YAN
2 / CHAN FUNG YEE
7 / TSANG WAI PING
Arithmetic operators
¨ Simple calculations like addition, subtraction, multiplication, division, exponentiation could be carried out by the operators could be done using +, –, * and /.
¨ All calculations follow default order of precedence, i.e. ^ first, * and / next; + and – last.
¨ The operator MOD returns the remainder of a division, e.g. 52 MOD 7 returns 3.
Relational operators
¨ These operators return a true or false from a relation, e.g. 5 + 7 > 13 returns a false value.
¨ They includes:
Operator / Meaningx = y / x is equal to y
x > y / x is greater than y
x < y / x is smaller than y
x >= y / x is greater than or equal to y
x <= y / x is smaller than or equal to y
x > y / x is not equal to y
x IN (a, b, c, d, …) / x is one of the value in the bracket
x BETWEEN y AND z / x is exclusively in-between y and z
x LIKE “_*” / X contain a string of character “_” at the specified position.
¨ E.g. If the table on page 2 is queried by executing the following SELECT commands,
1. SELECT CNUM, NAME FROM STUDENT2
WHERE NAME LIKE "*AN"
2. SELECT CNUM, NAME FROM STUDENT2