SQL Interview Preparation—Intermediate Level/

...

/

Answer: Using INTERSECT

Answer: Using INTERSECT

Find a detailed explanation of using INTERSECT in SQL queries.

We'll cover the following...

Solution

The solution is given below:

MySQL
/* The query to find employees who have sales in the 
month of January for both Cosmetics and Footwear */
SELECT E.EName AS 'Employee Name' FROM Employees E
JOIN   Sales S ON E.EID = S.EID
JOIN   ProductCategories PC ON S.CategoryID = PC.CategoryID
WHERE  S.Month = 'January' AND PC.CategoryName = 'Cosmetics'
INTERSECT
SELECT E.EName AS 'Employee Name' FROM Employees E
JOIN   Sales S ON E.EID = S.EID
JOIN   ProductCategories PC ON S.CategoryID = PC.CategoryID
WHERE  S.Month = 'January' AND PC.CategoryName = 'Footwear';

Code explanation

The explanation of the solution code is given below:

  • Line 3: The SELECT statement selects the columns EName. The data is retrieved from the Employees table. We use AS to set an alias for the columns and tables.

  • Line 4: The JOIN is applied with Sales on columns EID in both the tables.

  • Line 5: Another JOIN is applied to connect Sales and ProductCategories on columns CategoryID in both the tables.

  • Line 6: The WHERE clause specifies the condition on which we want to retrieve the data from the table. We get the data for the month of January and Cosmetics category.

  • Line 8: The INTERSECT keyword is used to intersect the results of two queries.

  • Line 10: The SELECT statement selects the columns EName. The data is retrieved from ...