1

4.1.9 Functions of SQL advanced commands

a) DISTINCT
b) WHERE
c) AND & OR
d) ORDER BY
e) WILDCARDS
f) IN & BETWEEN
g) INTERSECT
h) GROUP BY & HAVING
 2

4.1.9 Functions of SQL advanced commands

a) DISTINCT

• to return only distinct (different) values

Syntax:
SELECT DISTINCT column1, column2, ...
FROM table_name;
 3

4.1.9 Functions of SQL advanced commands

a) DISTINCT

SELECT DISTINCT S_Address FROM Student;

S_Address
Pahang

Kedah

Melaka

NULL
 4

4.1.9 Functions of SQL advanced commands

b) WHERE

• to filter records

Syntax:
SELECT column1, column2, ...
FROM table_name
WHERE condition;
 5

4.1.9 Functions of SQL advanced commands

b) WHERE

SELECT * FROM Student

WHERE S_Address = ‘Pahang’;

S_ID S_Name S_Address S_CGPA

18DNS2001 Alia Husna Pahang 3.59
 6

4.1.9 Functions of SQL advanced commands

c) AND & OR

• the WHERE clause can be combined with

AND and OR operators
Syntax:
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;
 7

4.1.9 Functions of SQL advanced commands

c) AND & OR

• the WHERE clause can be combined with

AND and OR operators
Syntax:
SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;
 8

4.1.9 Functions of SQL advanced commands

c) AND & OR

SELECT * FROM STUDENT

WHERE S_Address = ‘Pahang’
OR S_Address = ‘Melaka’
AND S_CGPA > 3.00;

S_ID S_Name S_Address S_CGPA

18DNS2001 Alia Husna Pahang 3.59

18DNS2003 David Melaka 3.17

18DNS2004 Swee Lan Pahang 2.00

 9

4.1.9 Functions of SQL advanced commands

c) AND & OR

SELECT * FROM STUDENT

WHERE S_Address = ‘Kedah’
OR S_Address = ‘Melaka’
AND S_CGPA > 3.00;
 10

4.1.9 Functions of SQL advanced commands

c) AND & OR

SELECT * FROM STUDENT

WHERE (S_Address = ‘Pahang’
OR S_Address = ‘Melaka’)
AND S_CGPA > 3.00

S_ID S_Name S_Address S_CGPA

18DNS2001 Alia Husna Pahang 3.59

18DNS2003 David Melaka 3.17

 11

4.1.9 Functions of SQL advanced commands

d) ORDER BY

• used to sort the result-set in ascending

or descending order
• sorts the records in ascending order by
default
• to sort the records in descending order, use the DESC
keyword.
Syntax:
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
 12

4.1.9 Functions of SQL advanced commands

d) ORDER BY

SELECT * FROM STUDENT

ORDER BY S_Name; Default ASC

S_ID S_Name S_Address S_CGPA

18DNS2001 Alia Husna Pahang 3.59
18DNS2003 David Melaka 3.17

18DNS2002 Shafiq Kedah NULL

18DNS2004 Swee Lan Pahang 2.00

 13

4.1.9 Functions of SQL advanced commands

d) ORDER BY

SELECT * FROM STUDENT

ORDER BY S_Name DESC;

S_ID S_Name S_Address S_CGPA

18DNS2004 Swee Lan Pahang 2.00
18DNS2002 Shafiq Kedah NULL
18DNS2003 David Melaka 3.17
18DNS2001 Alia Husna Pahang 3.59
 14

4.1.9 Functions of SQL advanced commands

d) ORDER BY (multiple sorting)

SELECT * FROM STUDENT

ORDER BY S_Name, S_CGPA DESC;

S_ID S_Name S_Address S_CGPA

18DNS2001 Alia Husna Pahang 3.59
18DNS2003 David Melaka 3.17
18DNS2002 Shafiq Kedah NULL
18DNS2004 Swee Lan Pahang 2.00
 15

4.1.9 Functions of SQL advanced commands

e) WILDCARDS

• used to substitute any other character(s) in a string

• used with the SQL LIKE operator
• 2 wildcards used in conjunction with the LIKE operator:
i. % - The percent sign represents zero, one, or multiple characters
ii. _ - The underscore represents a single character
 16

4.1.9 Functions of SQL advanced commands

e) WILDCARDS

LIKE Operator Description

WHERE CustomerName LIKE 'a%' Finds any values that starts with "a"
WHERE CustomerName LIKE '%a' Finds any values that ends with "a"
WHERE CustomerName LIKE Finds any values that have "or" in any position
'%or%'
WHERE CustomerName LIKE '_r%' Finds any values that have "r" in the second position
WHERE CustomerName LIKE Finds any values that starts with "a" and are at least 3
'a_%_%' characters in length
WHERE ContactName LIKE 'a%o' Finds any values that starts with "a" and ends with "o"
 17

4.1.9 Functions of SQL advanced commands

e) WILDCARDS

SELECT * FROM STUDENT

WHERE S_Name LIKE ‘%d;
S_ID S_Name S_Address S_CGPA
18DNS2003 David Melaka 3.17
 18

4.1.9 Functions of SQL advanced commands

e) WILDCARDS

SELECT * FROM STUDENT

WHERE S_Name LIKE ‘ a%’;

S_ID S_Name S_Address S_CGPA

18DNS2001 Alia Husna Pahang 3.59
 19

4.1.9 Functions of SQL advanced commands

f) IN & BETWEEN

• to specify multiple values in a WHERE clause

• a shorthand for multiple OR conditions

Syntax:
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);
 20

4.1.9 Functions of SQL advanced commands

f) IN & BETWEEN

SELECT * FROM STUDENT

WHERE S_Address IN (‘Pahang’ , ‘Melaka’);

S_ID S_Name S_Address S_CGPA

18DNS2001 Alia Husna Pahang 3.59
18DNS2003 David Melaka 3.17
18DNS2004 Swee Lan Pahang 2.00
 21

4.1.9 Functions of SQL advanced commands

f) IN & BETWEEN

• selects values within a given range

• the values can be numbers, text, or dates
• the BETWEEN operator is inclusive: begin and end values
are included

Syntax:
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
 22

4.1.9 Functions of SQL advanced commands

f) IN & BETWEEN

SELECT * FROM STUDENT

WHERE S_CGPA BETWEEN 2.00 AND 4.00;

S_ID S_Name S_Address S_CGPA

18DNS2001 Alia Husna Pahang 3.59
18DNS2003 David Melaka 3.17
18DNS2004 Swee Lan Pahang 2.00
 23

4.1.9 Functions of SQL advanced commands

g) INTERSECT

• used to return the results of 2 or more

SELECT statements
• However, it only returns the rows selected by
all queries or data
sets
• If a record exists in one query and not in the other, it will be
omitted from the INTERSECT results.
 24

4.1.9 Functions of SQL advanced commands

g) INTERSECT (syntax)

SELECT expression1, expression2, ... expression_n

FROM tables
[WHERE conditions]
INTERSECT
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];
 25

4.1.9 Functions of SQL advanced commands

g) INTERSECT (with single expression)

SELECT supplier_id
FROM suppliers
INTERSECT
SELECT supplier_id
FROM orders;
 26

4.1.9 Functions of SQL advanced commands

g) INTERSECT (with WHERE condition)

SELECT supplier_id
FROM suppliers
WHERE
supplier_id > 78
INTERSECT
SELECT supplier_id
FROM orders
WHERE quantity
<> 0;
 27

4.1.9 Functions of SQL advanced commands

g) INTERSECT (with multiple expression)

SELECT contact_id, last_name, first_name
FROM contacts
WHERE last_name <> 'Anderson'
INTERSECT
SELECT customer_id, last_name, first_name
FROM customers
WHERE customer_id < 50;
 28

4.1.9 Functions of SQL advanced commands

h) GROUP BY & HAVING

• often used with aggregate functions (COUNT, MAX,
MIN, SUM, AVG) to group the result-set by one or
more columns

Syntax:
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
 29

4.1.9 Functions of SQL advanced commands

h) GROUP BY & HAVING

SELECT S_Name, S_Address
FROM STUDENT
WHERE S_Address =
‘PAHANG’
GROUP BY S_Name ORDER
BY S_Name;
S_Name S_Address
Alia Husna Pahang
Swee Lan Pahang
 30

4.1.9 Functions of SQL advanced commands

h) GROUP BY & HAVING

• The HAVING clause was added to SQL because the
WHERE keyword could not be used with
aggregate functions.
Syntax:
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY
 31

4.1.9 Functions of SQL advanced commands

h) GROUP BY & HAVING

SELECT COUNT(S_ID), S_Address
FROM Student
GROUP BY S_Address
HAVING
COUNT(S_ID) > 1;
S_ID S_Address
18DNS2001 Pahang
18DNS2004 Pahang

* The following SQL statement lists the number of student in each state.
Only include state with more than 1 student
 32

4.1.10 Functions of SQL aggregate functions

a) MIN
b) MAX
c) AVG
d) COUNT
e) SUM
 33

4.1.10 Functions of SQL aggregate functions

a) MIN
• returns the smallest value of the selected column

Syntax:
SELECT MIN(column_name)
FROM table_name
WHERE condition;
 34

4.1.10 Functions of SQL aggregate functions

b) MAX
• returns the largest value of the selected column

Syntax:
SELECT MAX(column_name)
FROM table_name
WHERE condition;
 35

4.1.10 Functions of SQL aggregate functions

c) AVG
• returns the average value of a numeric column.

Syntax:
SELECT AVG(column_name)
FROM table_name
WHERE condition;
 36

4.1.10 Functions of SQL aggregate functions

d) COUNT
• returns the number of rows that matches a specified
criteria

Syntax:
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
 37

4.1.10 Functions of SQL aggregate functions

e) SUM
• returns the total sum of a numeric column.

Syntax:
SELECT SUM(column_name)
FROM table_name
WHERE condition;
 38

4.1.10 Functions of SQL aggregate functions

Example
SELECT MIN(S_SCGPA), MAX(S_SCGPA),
AVG(S_SCGPA), COUNT (S_SCGPA), SUM (S_SCGPA)
FROM Student;

MIN(S_CGPA) MAX(S_CGPA) AVG(S_CGPA) COUNT(S_CGPA SUM(S_CGPA)

)
1.67 3.59 2.61 4 10.43
 39

4.1.10 Functions of SQL aggregate functions

Example
SELECT MIN(S_SCGPA), MAX(S_SCGPA),
AVG(S_SCGPA), COUNT (S_SCGPA), SUM (S_SCGPA)
FROM Student
WHERE S_Address = ‘Pahang’;

MIN(S_CGPA) MAX(S_CGPA) AVG(S_CGPA) COUNT(S_CGPA SUM(S_CGPA)

)
2.00 3.59 2.80 2 5.59