Database Concepts and Design: Complex SQL Queries

What is the outer query in a nested query?

What operator is used in nested queries to compare a value with a set of values?

What is the purpose of using nested queries?

What can be used to retrieve the names of those students who have more CGPA than that of the maximum of BCS students?

What is the benefit of using nested queries in SQL retrieval?

What is the primary purpose of a subquery in a nested query?

What is the main difference between the IN operator and the ANY or SOME operator?

What is the purpose of the OR clause in the following query: SELECT DISTINCT Pnumber FROM PROJECT WHERE Pnumber IN (...) OR Pnumber IN (...)?

What is the result of the following query: SELECT Fname, Lname, Address FROM Employee WHERE Dno IN (SELECT Dnumber FROM Department WHERE Dname=“RESEARCH”)?

What is the primary advantage of using nested queries instead of joins?

Complex SQL Retrieval Queries

Additional features in SQL allow users to specify more complex retrievals from a database.

Nested Queries

A query within a query, useful when a condition has to be applied against an unknown value.
Example: Retrieve the names of students who have more CGPA than the maximum CGPA of BCS students.
The inner query is executed once before the outer query.
Output is a temporary dataset used by the outer query.

Example 1: Retrieve Employee Information

Retrieve the name and address of all employees who work for the 'Research' department.
SELECT Fname, Lname, Address FROM Employee WHERE Dno IN (SELECT Dnumber FROM Department WHERE Dname="RESEARCH")

Example 2: Retrieve Project Information

Retrieve project numbers of 'Smith' where he worked as a manager or worker.
SELECT DISTINCT Pnumber FROM PROJECT WHERE Pnumber IN (SELECT Pnumber FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE Dnum=Dnumber AND mgrssn=Ssn AND Lname='Smith') OR Pnumber IN (SELECT Pno FROM WORKS_ON, EMPLOYEE WHERE Essn=Ssn AND Lname='Smith');

Comparison Operators in Nested Queries

ANY or SOME operator returns TRUE if the value v is equal to some value in the set V, equivalent to IN.
Other operators that can be combined with ANY (or SOME) include >, >=, <, <=.

Summary of SQL Queries

SQL queries can be used to retrieve complex data from a database.

Test your understanding of complex SQL retrieval queries, including nested queries, joins, aggregate functions, and grouping. Learn how to apply conditions against unknown values and retrieve data from databases with this quiz.