Example (Simple/Non-correlated)
Nested Query
- Query 1:
- Find fname, lname
of employees
in the "Research" department
|
- Solution:
|
Example (Simple/Non-correlated)
Nested Query
- Query 1:
- Find fname, lname
of employees
in the "Research" department
|
- Solution using
a
NON-nested query
(uses a join):
SELECT fname, lname
FROM employee, department
WHERE dno = dnumber
AND dname = 'Research'
|
|
Example (Simple/Non-correlated)
Nested Query
- Query 1:
- Find fname, lname
of employees
in the "Research" department
|
- Suppose we know that
the
'Research'
department has the
department number
5:
SELECT fname, lname
FROM employee
WHERE dno IN ( 5 )
|
|
Example (Simple/Non-correlated)
Nested Query
- Query 1:
- Find fname, lname
of employees
in the "Research" department
|
- We can use a
subquery to
find
the department number of
the 'Research' department:
SELECT fname, lname
FROM employee
WHERE dno IN ( 5 )
Subquery to find dnumber of 'Research' dept
SELECT dnumber FROM department WHERE dname='Research'
|
|
Example (Simple/Non-correlated)
Nested Query
- Query 1:
- Find fname, lname
of employees
in the "Research" department
|
- Solution:
SELECT fname, lname
FROM employee
WHERE dno IN ( SELECT dnumber
FROM department
WHERE dname='Research'
)
Subquery to find dnumber of 'Research' dept
SELECT dnumber FROM department WHERE dname='Research'
|
|
Example (Simple/Non-correlated)
Nested Query
- Query 1:
- Find fname, lname
of employees
in the "Research" department
|
- Note: because the
subquery
returns a
set
with a
single value,
we can also use:
SELECT fname, lname
FROM employee
WHERE dno = ( SELECT dnumber
FROM department
WHERE dname='Research'
)
Subquery to find dnumber of 'Research' dept
SELECT dnumber FROM department WHERE dname='Research'
|
|
Example 2: (Simple/Non-correlated)
Nested Query
- Query 2:
- Find fname, lname of
employees that
do not have any
dependent
|
- Solution:
|
Example 2: (Simple/Non-correlated)
Nested Query
- Query 2:
- Find fname, lname of
employees that
do not have any
dependent
|
- Conceptual solution:
SELECT fname, lname
FROM employee
WHERE ssn IN { ssn of employees without dependent }
|
|
Example 2: (Simple/Non-correlated)
Nested Query
- Query 2:
- Find fname, lname of
employees that
do not have any
dependent
|
- Which is equivalent to:
SELECT fname, lname
FROM employee
WHERE ssn IN { ssn of employees without dependent }
SELECT fname, lname
FROM employee
WHERE ssn NOT IN { ssn of employees with (one or more) dependent }
|
|
Example 2: (Simple/Non-correlated)
Nested Query
- Query 2:
- Find fname, lname of
employees that
do not have any
dependent
|
- Solution: with
subquery
written in SQL
SELECT fname, lname
FROM employee
WHERE ssn IN { ssn of employees without dependent }
SELECT fname, lname
FROM employee
WHERE ssn NOT IN { ssn of employees with (one or more) dependent }
SELECT fname, lname
FROM employee
WHERE ssn NOT IN (SELECT essn
FROM dependent)
|
|
Example 3: (Simple/Non-correlated)
Nested Query
- Query 3:
- Find fname, lname of employees
who earn the
highest salary in the
company
|
- Solution:
|
Example 3: (Simple/Non-correlated)
Nested Query
- Query 3:
- Find fname, lname of employees
who earn the
highest salary in the
company
|
- Conceptual solution:
SELECT fname, lname
FROM employee
WHERE salary = MAX salary of all employees
|
|
Example 3: (Simple/Non-correlated)
Nested Query
- Query 3:
- Find fname, lname of employees
who earn the
highest salary in the
company
|
- Re-formulate the
condition
using
>= ALL:
SELECT fname, lname
FROM employee
WHERE salary = MAX salary of all employees
SELECT fname, lname
FROM employee
WHERE salary >= ALL ( set of salary of all employees )
|
|
Example 3: (Simple/Non-correlated)
Nested Query
- Query 3:
- Find fname, lname of employees
who earn the
highest salary in the
company
|
- Write the
inner query
in SQL:
SELECT fname, lname
FROM employee
WHERE salary = MAX salary of all employees
SELECT fname, lname
FROM employee
WHERE salary >= ALL ( set of salary of all employees )
SELECT fname, lname
FROM employee
WHERE salary >= ALL ( SELECT salary
FROM employee )
|
|
❮
❯