Lab 5: Subqueries

CISB224 02A, 02BSemester I, 2009/2010

College of Information Technology, Universiti Tenaga Nasional

1

Introduction to Subqueries

College of Information Technology, Universiti Tenaga Nasional

2

Suppose that you: Want to know all the other employees that

work in the same department as Zarina Majid

Don’t know Zarina Majid’s department.

You need to solve the sub-problem before you can solve the main problem. There are

two ways to do this.

Let’s call this part the main problem.Let’s call this part the main problem.

And this part, the sub-problem.And this part, the sub-problem.

Introduction to Subqueries – cont.

College of Information Technology, Universiti Tenaga Nasional

3

Solution 1

1.Find out the department by executing:

SELECT DeptFROM empWHERE Name = ‘Zarina Majid’

Admin

Admin

Introduction to Subqueries – cont.

College of Information Technology, Universiti Tenaga Nasional

4

Solution 1 – cont.

2.Then, use the Deparment name obtained to display the other employees:

SELECT Name, TitleFROM empWHERE Dept =

Admin

Admin

This method requires you to execute two queries: One query to solve the sub-problem Another query to solve the main problem

Instead, you could solve the sub-problem and the main problem in just one query.

This method requires you to execute two queries: One query to solve the sub-problem Another query to solve the main problem

Instead, you could solve the sub-problem and the main problem in just one query.

Introduction to Subqueries – cont.

College of Information Technology, Universiti Tenaga Nasional

5

Solution 2

1.Type out the query that solves the main problem.

SELECT Name, TitleFROM empWHERE Dept = ‘Admin’

Introduction to Subqueries – cont.

College of Information Technology, Universiti Tenaga Nasional

6

Solution 2 – cont.

2.At the search condition, delete the Deparment value and type in a pair of parentheses.

SELECT Name, TitleFROM empWHERE Dept = ()()

Introduction to Subqueries – cont.

College of Information Technology, Universiti Tenaga Nasional

7

Solution 2 – cont.

3.Within the parentheses, type in the query that solves the sub-problem.

Introduction to Subqueries – cont.

College of Information Technology, Universiti Tenaga Nasional

8

Solution 2 – cont.

SELECT Name, TitleFROM empWHERE Dept =(SELECT Dept(SELECT Dept

FROM empFROM empWHERE Name = ‘Zarina Majid’WHERE Name = ‘Zarina Majid’

))

Admin

Where You Can Use Subqueries

College of Information Technology, Universiti Tenaga Nasional

9

In the WHERE clause In the HAVING clause In the FROM clause (later)

Example of Subquery in WHERE

College of Information Technology, Universiti Tenaga Nasional

10

Display employees who have the same title as Nordin.

SELECT Name, Title, DeptFROM empWHERE Title = ( SELECT Title

FROM empWHERE Name like ‘Nordin%’)

Example of Subquery in HAVING

College of Information Technology, Universiti Tenaga Nasional

11

Display all the departments that have an average salary bill greater than the average of Admin deparment

Example of Subquery in HAVING – cont.

College of Information Technology, Universiti Tenaga Nasional

12

SELECT Dept, AVG(salary)FROM empGROUP BY DeptHAVING AVG(salary) <

(SELECT AVG(salary) FROM emp WHERE Dept=‘Admin’)

Important!!!

College of Information Technology, Universiti Tenaga Nasional

13

You may not have an ORDER BY clause in the subquery

If the subquery returns more than one value i.e. a list of values, than you must use the IN operator in the main query’s search condition

More Operators

College of Information Technology, Universiti Tenaga Nasional

14

OperatorOperator

IS NULLIS NULL Example: ReportsTo IS NULLExample: ReportsTo IS NULL

IS NOT NULLIS NOT NULL

The negation of the The negation of the operators that you have operators that you have learntlearnt

NOT BETWEENNOT BETWEEN

NOT INNOT IN

NOT LIKENOT LIKE

Using More Than One Search Conditions with AND or OR

College of Information Technology, Universiti Tenaga Nasional

15

Display the customers who are located in the KL, Perak and Johor

SELECT CompName, StateFROM custWHERE State IN (‘KL’, ‘Perak, ‘Johor’State IN (‘KL’, ‘Perak, ‘Johor’State = ‘KL’ OR

State = ‘Perak’ ORState = ‘Johor’

State = ‘KL’ ORState = ‘Perak’ ORState = ‘Johor’