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:

 This technique is called a subquery

• Subquery

• Syntax: subquery

 ``` ( SELECT-command ) ```

• Meaning of a subquery:

 The result of a subquery is a relation (= set) of tuples selected by the SQL SELECT command in the 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

• Fact:

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

• Where can you use subqueries in the SELECT command

• The SELECT comamnd:

 ``` select attribute-list FROM .... (subqueries can be used here) .... WHERE .... (subqueries can be used here) .... ```

• Use of subquery:

• Inside the WHERE clause

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

Use a subquery that return 1 value anywhere you see: atomic-value

Use a subquery (of any kind) anywhere you see: (SET of values)

• Inside the FROM clause

 The result of a subquery will be a temporal relation