SQL Interview Preparation—Intermediate Level/

...

/

Answer: Using CROSS JOIN

Answer: Using CROSS JOIN

Find a detailed explanation of using CROSS JOIN in SQL.

We'll cover the following...

Solution

The solution is given below:

Press + to interact
-- The query to find employees with sales in each category
SELECT     E.EName AS 'Employee Name',
           PC.CategoryName AS 'Category Name',
           COUNT(S.SalesID) AS 'Sales Count'
FROM       Employees E
CROSS JOIN ProductCategories PC
LEFT JOIN  Sales S ON E.EID = S.EID AND S.CategoryID = PC.CategoryID
GROUP BY   E.EName, PC.CategoryName;

Code explanation

The explanation of the solution code is given below:

  • Lines 2–4: The SELECT statement selects the columns EName, CategoryName, and count of sales made using the aggregate function with the SalesID column. We use AS to set an alias for the columns and tables.

  • Line 5: The data is retrieved from the Employees table.

  • Line 6: The CROSS JOIN is performed between Employees and ProductCategories.

  • Line 7: The LEFT JOIN is performed with Sales, Employees, and ProductCategories on the columns EID and CategoryID.

  • Line 8: The GROUP BY specifies the EName and CategoryName columns to group the data. ...

Recalling

Access this course and 1400+ top-rated courses and projects.