Example 1 on Correlated Nested Queries in SQL

For Complete YouTube Video: Click Here

In this class, we will try to understand Example 1 on Correlated Nested Queries in SQL.

We have already discussed the concepts of Correlated Nested Queries in our previous classes.

The query is as shown below.

Write a query to find the employees who work on at least 2 projects.

To execute this we need employee and works_on tables of the company database as shown below.

The query is shown below.

SELECT outer.fname, outer.lname FROM employee outer WHERE (SELECT count(essn) FROM works_on inner WHERE outer.ssn = inner.essn) >= 2;

The output of the query is shown below.

A clear explanation of the query is made in the above youtube link.