Explain correlated nested queries with examples
Answer:-
Some queries require fetching existing values from the database and then using them in a comparison condition. Such queries can be formulated using nested queries, which are complete SELECT-FROM-WHERE blocks within the WHERE clause of another query. This other query is called the outer query.
Example 1: List the project numbers of projects that have an employee with the last name ‘Smith’ as manager.
SELECT DISTINCT Pnumber
FROM PROJECT
WHERE Pnumber IN
(SELECT Pnumber
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE Dnum=Dnumber AND Mgr_ssn=Ssn AND Lname='Smith');
Example 2: List the project numbers of projects that have an employee with the last name ‘Smith’ as either manager or worker.
SELECT DISTINCT Pnumber
FROM PROJECT
WHERE Pnumber IN
(SELECT Pnumber
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE Dnum=Dnumber AND Mgr_ssn=Ssn AND Lname='Smith')
OR
Pnumber IN
(SELECT Pno
FROM WORKS_ON, EMPLOYEE
WHERE Essn=Ssn AND Lname='Smith');
In these examples, the nested queries select project numbers based on certain conditions (e.g., having an employee with a specific last name as a manager or worker). The outer query then uses these results to filter the project numbers. The IN operator is used to compare a value with a set of values and evaluates to true if the value is in the set.