CS457 Syllabus & Progress

Sub-queries

Prelude to subquery

Recall:

Many of the tuple condition clauses uses:

( set of values )

Example:

attr IN ( set of values ) attr >= ANY ( set of values ) attr >= ALL ( set of values ) EXISTS ( set of values ) ....

We will now learn a method to form (= compute) a set of values:

Subquery

Syntax: subquery
Meaning of a subquery:

Example 1:

The dependent relation: essn name sex bdate relationship --------- ---------- --- ---------- ------------ 333445555 Alice F 05-APR-76 DAUGHTER 333445555 Theodore M 25-OCT-73 SON 333445555 Joy F 03-MAY-48 SPOUSE 987654321 Abner M 29-FEB-32 SPOUSE 123456789 Micheal M 01-JAN-78 SON 123456789 Alice F 31-DEC-78 DAUGHTER 123456789 Elizabeth F 05-MAY-57 SPOUSE
Then, the subquery: ( SELECT name FROM dependent WHERE essn='123456789' ) will compute the following output set: (Micheal, Alice, Elizabeth)

Example 2:

The department relation: dname dnumber mgrssn mgrstartdate --------------- ----------- --------- ------------ Research 5 333445555 22-MAY-78 <------- **** Administration 4 987654321 01-JAN-85 Headquarters 1 888665555 19-JUN-71
Then, the subquery: ( SELECT dnumber FROM department WHERE dname='Research' ) will compute the following output set: ( 5 )

Subqueries that returns 1 single value

Very important fact:

A sub-query that returns 1 single value is considered as an atomic value

I.e.:

( SELECT dnumber FROM department WHERE dname='Research' ) returns the set of value ( 5 )
In SQL, we can use: ( SELECT dnumber FROM department WHERE dname='Research' ) as the number 5

Example:

Find fname and lname of employees with dno = 5:

select fname, lname from employee where dno = 5 // 5 is a number

We can replace the number 5 by a query that returns one value 5:

select fname, lname from employee where dno = (select dnumber from department where dname='Research')

Where can you use subqueries in the SELECT command

Where in the SELECT command can you put a sub-query:

A sub-query can be use inside the relation-list in the FROM clause:

select attribute-list FROM .... (subquery) .... WHERE ....

A sub-query can be use inside the WHERE clause:

select attribute-list FROM .... WHERE .... (subquery) ....

(1) using a sub-query inside the FROM clause:
(We not discuss this feature in CS554 )

(2) using a subquery inside the WHERE clause:

The sub-query will compute the SET of value in following the "set-testing" operators:

WHERE atomic-value ReationalOperator atomic-value
WHERE atomic-value IN (SET of values)
WHERE atomic-value NOT IN (SET of values)
WHERE atomic-value ReationalOperator ANY (SET of values)
WHERE atomic-value ReationalOperator ALL (SET of values)
WHERE EXISTS (SET of values)
WHERE atomic-value IS NULL
WHERE atomic-value LIKE 'wildcard-expression'

Note:

You can use a subquery in any place where you see: (SET of values)
Example:
You can also use a subquery that return 1 value in any place you see: atomic-value