Search⌘ K
Home
Courses
SQL Interview Preparation—Intermediate Level

Answer: Using INTERSECT

Explore how to use the INTERSECT operator to retrieve employee names with sales in multiple categories by combining SELECT statements. Understand aliases, JOINs, filtering with WHERE, and alternative solutions using GROUP BY, DISTINCT, and subqueries to handle intermediate SQL interview questions focused on set operators.

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 ...