SQL exercises on retail shopping: Multiple table queries

SELECT E.LastName,
E.FirstName,
EP.JobTitle,
EP.Salary
FROM Employee.Employee E
INNER JOIN Employee.Pay EP ON E.EmployeeID = EP.EmployeeID
WHERE (EP.JobTitle = 'Sales Support' OR EP.JobTitle = 'Salesman')
AND DATEDIFF (YEAR, EP.HireDate, EP.LastDate) >= 5;

Selected Columns: The query selects LastName and FirstName from the Employee table, and JobTitle and Salary from the Pay table. These columns provide personal and job-related information about each employee.

INNER JOIN: The INNER JOIN clause matches rows from the Employee table with rows from the Pay table based on the EmployeeID, ensuring that only records with matching EmployeeID values are included in the results.

Filtering with WHERE Clause: The query applies two filters:

• (EP.JobTitle = 'Sales Support' OR EP.JobTitle = 'Salesman'): This condition filters the results to include only employees whose job title is either ‘Sales Support’ or ‘Salesman’.
• DATEDIFF(YEAR, EP.HireDate, EP.LastDate) >= 5: This condition ensures that only employees with at least 5 years of service (calculated by the difference in years between the HireDate and LastDate) are included.

SELECT E.LastName,
E.FirstName,
E.City,
EP.JobTitle,
EP.Bonus
FROM Employee.Employee E
INNER JOIN Employee.Pay EP ON E.EmployeeID = EP.EmployeeID
WHERE EP.JobTitle = 'Salesman' OR EP.JobTitle = 'Sales Support'
AND EP.Bonus >= 1000
ORDER BY EP.JobTitle, EP.Bonus;

Selected Columns: The query selects LastName, FirstName, and City from the Employee table, and JobTitle and Bonus from the Pay table. This combination provides personal details and job-related financial information.

INNER JOIN: The INNER JOIN clause combines records from the Employee and Pay tables based on the EmployeeID, ensuring that only employees with matching IDs in both tables are included.

Filtering with WHERE Clause: The query applies the following filters:

• (EP.JobTitle = 'Salesman' OR EP.JobTitle = 'Sales Support'): This condition restricts the results to employees who hold either the ‘Salesman’ or ‘Sales Support’ job titles.
• AND EP.Bonus >= 1000: This condition further filters the results to include only those employees who have received a bonus of at least 1,000.

Ordering with ORDER BY Clause: The results are ordered first by JobTitle and then by Bonus in ascending order. This sorting helps organise the data, making it easier to compare employees within the same job title category based on their bonus amounts.

SELECT E.LastName,
E.FirstName,
E.City,
EP.JobTitle,
EP.Salary,
DATEDIFF (YEAR, EP.HireDate, GETDATE()) AS TenureInYears
FROM Employee.Employee E
INNER JOIN Employee.Pay EP ON E.EmployeeID = EP.EmployeeID
WHERE EP.JobTitle = 'Salesman' or EP.JobTitle = 'Sales Support'
ORDER BY EP.JobTitle, EP.Salary;

Selected Columns:

• LastName, FirstName, and City are selected from the Employee table to provide personal details about each employee.
• JobTitle and Salary are selected from the Pay table to provide job-related and financial information.
• DATEDIFF(YEAR, EP.HireDate, GETDATE()) AS TenureInYears calculates the number of years each employee has been with the company, using the difference between their hire date and the current date. This result is labeled as TenureInYears.

INNER JOIN:

• The INNER JOIN clause combines records from the Employee and Pay tables based on the EmployeeID, ensuring only employees with matching IDs in both tables are included.

Filtering with WHERE Clause:

• The condition (EP.JobTitle = 'Salesman' OR EP.JobTitle = 'Sales Support') restricts the results to include only employees with the job titles ‘Salesman’ or ‘Sales Support’.

Ordering with ORDER BY Clause:

• The results are ordered first by JobTitle and then by Salary in ascending order. This sorting helps organise the data, making it easier to analyse and compare employees within the same job title category based on their salary.

SELECT C.CustomerName,
C.City,
CO.OrderDate,
P.ProductDescription,
CO.OrderQuantity
FROM CustomerTab.CustomerTabl C
INNER JOIN CustomerTab.CustomerOrdersTabl CO ON C.CustomerID = CO.CustomerID
INNER JOIN CustomerTab.CustomerProductTabl P ON CO.ProductID = P.ProductID
ORDER BY CO.OrderDate, CO.OrderQuantity;

This SQL query retrieves customer names, cities, order dates, product descriptions, and order quantities by joining three tables: CustomerTab.CustomerTabl (aliased as C), CustomerTab.CustomerOrdersTabl (aliased as CO), and CustomerTab.CustomerProductTabl (aliased as P).

It uses INNER JOIN operations to combine related information from these tables. The results are then sorted by order date and order quantity.

• Selected Columns:
  • CustomerName and City are selected from the CustomerTabl table to provide customer details.
  • OrderDate and OrderQuantity are selected from the CustomerOrdersTabl table to provide information about the order’s date and quantity.
  • ProductDescription is selected from the CustomerProductTabl table to provide details about the product ordered.
• INNER JOIN Operations:
  • The first INNER JOIN links the CustomerTabl and CustomerOrdersTabl tables using the CustomerID to ensure that each order is matched with the correct customer.
  • The second INNER JOIN links the CustomerOrdersTabl and CustomerProductTabl tables using the ProductID to match each order with the correct product details.
• Ordering with ORDER BY Clause:
  • The results are ordered first by OrderDate and then by OrderQuantity in ascending order. This sorting helps in organising the data chronologically and by the number of products ordered, making it easier to analyse order trends and customer purchasing behaviour.

SELECT C.CustomerName,
C.City,
CO.OrderDate,
P.ProductDescription,
CO.OrderQuantity
FROM CustomerTab.CustomerTabl C
LEFT JOIN CustomerTab.CustomerOrdersTabl CO ON C.CustomerID = CO.CustomerID
LEFT JOIN CustomerTab.CustomerProductTabl P ON CO.ProductID = P.ProductID
ORDER BY C.CustomerName, CO.OrderDate, P.ProductDescription;

This SQL query retrieves customer names, cities, order dates, product descriptions, and order quantities by joining three tables: CustomerTab.CustomerTabl (aliased as C), CustomerTab.CustomerOrdersTabl (aliased as CO), and CustomerTab.CustomerProductTabl (aliased as P).

It uses LEFT JOIN operations to include all customers, even if they have no corresponding orders or products.

The results are then sorted by customer name, order date, and product description.

• Selected Columns:
  • CustomerName and City are selected from the CustomerTabl table to provide customer details.
  • OrderDate and OrderQuantity are selected from the CustomerOrdersTabl table to provide information about the date of the order and the quantity ordered.
  • ProductDescription is selected from the CustomerProductTabl table to provide details about the product ordered.
• LEFT JOIN Operations:
  • The first LEFT JOIN links the CustomerTabl and CustomerOrdersTabl tables using the CustomerID. This join includes all customers, even those who have not placed any orders.
  • The second LEFT JOIN links the CustomerOrdersTabl and CustomerProductTabl tables using the ProductID. This join includes all orders, even if they are not associated with a product in the product table.
• Ordering with ORDER BY Clause:
  • The results are ordered first by CustomerName, then by OrderDate, and finally by ProductDescription in ascending order. This sorting provides a clear, organised view of the data, making it easier to see customer order patterns and the products they are interested in.

SELECT C.CustomerName,
C.City,
CO.OrderDate,
P.ProductDescription,
CO.OrderQuantity
FROM CustomerTab.CustomerOrdersTabl CO
RIGHT JOIN CustomerTab.CustomerTabl C ON CO.CustomerID = C.CustomerID
LEFT JOIN CustomerTab.CustomerProductTabl P ON CO.ProductID = P.ProductID
ORDER BY C.CustomerName, CO.OrderDate, P.ProductDescription;

This SQL query retrieves customer names, cities, order dates, product descriptions, and order quantities by combining information from three tables: CustomerTab.CustomerOrdersTabl (aliased as CO), CustomerTab.CustomerTabl (aliased as C), and CustomerTab.CustomerProductTabl (aliased as P).

It uses RIGHT JOIN and LEFT JOIN operations to ensure all customers are included, even if they have no orders or product details.

The results are then sorted by customer name, order date, and product description.

• Selected Columns:
  • CustomerName and City are selected from the CustomerTabl table to provide customer details.
  • OrderDate and OrderQuantity are selected from the CustomerOrdersTabl table to provide information about when the order was placed and how many items were ordered.
  • ProductDescription is selected from the CustomerProductTabl table to provide details about the product ordered.
• RIGHT JOIN and LEFT JOIN Operations:
  • The RIGHT JOIN between CustomerOrdersTabl (CO) and CustomerTabl (C) ensures that all customers are included, even those who have not placed any orders. It links the orders to customers based on CustomerID.
  • The LEFT JOIN between CustomerOrdersTabl (CO) and CustomerProductTabl (P) includes all orders, even if they are not associated with a product, linking orders to products using ProductID.
• Ordering with ORDER BY Clause:
  • The results are ordered first by CustomerName, then by OrderDate, and finally by ProductDescription in ascending order. This sorting provides a clear, organised view of the data, making it easier to understand customer order patterns and the products they are interested in.

SELECT C.CustomerName,
C.Address,
C.City,
C.ZipCode,
C.Phone,
CO.OrderID,
CO.OrderQuantity,
CO.OrderDate
FROM CustomerTab.CustomerTabl C
FULL JOIN CustomerTab.CustomerOrdersTabl CO ON C.CustomerID = CO.CustomerID
ORDER BY C.CustomerName, CO.OrderDate;

This SQL query retrieves customer details and their corresponding order information by using a FULL JOIN between the CustomerTab.CustomerTabl (aliased as C) and CustomerTab.CustomerOrdersTabl (aliased as CO) tables.

The query includes all customers and all orders, even if they do not have matching records in both tables.

The results are then sorted by customer name and order date.

• Selected Columns:
  • CustomerName, Address, City, ZipCode, and Phone are selected from the CustomerTabl table to provide comprehensive customer details.
  • OrderID, OrderQuantity, and OrderDate are selected from the CustomerOrdersTabl table to provide information about the customer’s orders, including order identifiers, quantities, and dates.
• FULL JOIN Operation:
  • The FULL JOIN between CustomerTabl (C) and CustomerOrdersTabl (CO) ensures that all customers and all orders are included in the result set, regardless of whether there is a corresponding match in the other table. If a customer has no orders, the order details will be NULL. Similarly, if an order does not have a corresponding customer record, the customer details will be NULL.
• Ordering with ORDER BY Clause:
  • The results are ordered first by CustomerName and then by OrderDate in ascending order. This sorting provides a clear, organised view of the data, making it easier to track customer activities and order histories.

SELECT E1. EmployeeID AS Employee1ID,
E1. LastName AS Employee1LastName,
E1. FirstName AS Employee1FirstName,
E1. Phone AS Employee1Phone,
E2. EmployeeID AS Employee2ID,
E2. LastName AS Employee2LastName,
E2. FirstName AS Employee2FirstName,
E2. Phone AS Employee2Phone,
E1. City AS CommonCity
FROM Employee.Employee E1
INNER JOIN Employee.Employee E2 ON E1. City = E2. City AND E1. EmployeeID < E2.
EmployeeID;

This SQL query retrieves pairs of employees who live in the same city, providing details about each employee in the pair.

It uses a self-join on the Employee.Employee table to find employees with a common city, ensuring that each pair is listed only once.

The results include the employee IDs, last names, first names, and phone numbers for both employees, as well as the city they have in common.

• Selected Columns:
  • EmployeeID, LastName, FirstName, and Phone from E1 (aliased as Employee1ID, Employee1LastName, Employee1FirstName, Employee1Phone) are selected to provide details about the first employee in each pair.
  • EmployeeID, LastName, FirstName, and Phone from E2 (aliased as Employee2ID, Employee2LastName, Employee2FirstName, Employee2Phone) are selected to provide details about the second employee in each pair.
  • City from E1 (aliased as CommonCity) is selected to show the city that both employees have in common.
• Self-Join Operation:
  • The INNER JOIN operation is performed on the Employee.Employee table, with E1 and E2 being aliases representing different instances of the same table.
  • The join condition E1.City = E2.City finds pairs of employees who live in the same city.
  • The condition E1.EmployeeID < E2.EmployeeID ensures that each pair is listed only once, avoiding duplicate pairs in reverse order.

SELECT P.ProductDescription, O.OrderQuantity
FROM CustomerTab.CustomerProductTabl P
LEFT OUTER JOIN CustomerTab.CustomerOrdersTabl O
ON P.ProductID = O.ProductID;

This SQL query retrieves product descriptions and their corresponding order quantities by joining the CustomerTab.CustomerProductTabl table (aliased as P) with the CustomerTab.CustomerOrdersTabl table (aliased as O).

It uses a LEFT OUTER JOIN to ensure that all products are included in the results, even if they have not been ordered.

• Selected Columns:
  • ProductDescription is selected from the CustomerProductTabl table to provide details about the products.
  • OrderQuantity is selected from the CustomerOrdersTabl table to show the quantity of each product ordered.
• LEFT OUTER JOIN Operation:
  • The LEFT OUTER JOIN between CustomerProductTabl (P) and CustomerOrdersTabl (O) ensures that all products are listed in the result set, including those that have not been ordered. If a product has no corresponding order in the CustomerOrdersTabl table, the OrderQuantity will be NULL.
  • The join condition P.ProductID = O.ProductID links products with their corresponding orders based on the ProductID.

SELECT E.EmployeeID,
E.LastName,
E.FirstName,
(EP.Salary + EP.Bonus) AS TotalCompensation
FROM Employee.Employee E
CROSS JOIN Employee.Pay EP
ORDER BY E.EmployeeID;

This SQL query retrieves a combination of employee details and total compensation by performing a CROSS JOIN between the Employee.Employee table (aliased as E) and the Employee.Pay table (aliased as EP).

It calculates the total compensation for each combination of employees and pay records by adding the salary and bonus.

The results are then sorted by employee ID.

• Selected Columns:
  • EmployeeID, LastName, and FirstName are selected from the Employee table to provide identification and personal details for each employee.
  • (EP.Salary + EP.Bonus) AS TotalCompensation calculates the total compensation by summing the Salary and Bonus from the Pay table. This result is given the alias TotalCompensation.
• CROSS JOIN Operation:
  • The CROSS JOIN produces a Cartesian product of the Employee and Pay tables, meaning that each row from the Employee table is combined with every row from the Pay table. This results in every possible combination of employees and pay records, regardless of whether there is a logical match.
  • This type of join is typically used when there is no relationship between the two tables, or when generating all possible combinations is required for analysis or reporting.
• Ordering with ORDER BY Clause:
  • The results are ordered by EmployeeID in ascending order. This sorting helps organise the data, making it easier to locate information based on employee ID.

SELECT
E.LastName,
E.FirstName,
EP.Bonus
FROM Employee.Employee E
INNER JOIN Employee.Pay EP ON E.EmployeeID = EP.EmployeeID
WHERE EP.Bonus IN (
SELECT TOP 5 Bonus
FROM Employee.Pay
)
ORDER BY EP.Bonus DESC;

This SQL query retrieves the last names, first names, and bonus amounts of employees by joining the Employee.Employee table (aliased as E) with the Employee.Pay table (aliased as EP).

The query focuses on identifying employees who have received one of the top 5 highest bonuses.

It uses a subquery to find these top bonuses and sorts the results in descending order of the bonus amount.

• Selected Columns:
  • LastName and FirstName are selected from the Employee table to provide the personal details of each employee.
  • Bonus is selected from the Pay table to show the bonus amount each employee received.
• INNER JOIN Operation:
  • The INNER JOIN links the Employee and Pay tables using the EmployeeID, ensuring that only employees with matching IDs in both tables are included in the results.
• Filtering with WHERE Clause and Subquery:
  • WHERE EP.Bonus IN (...): This condition filters the results to include only those employees whose bonus is among the top 5 highest bonuses.
  • SELECT TOP 5 Bonus FROM Employee.Pay: This subquery selects the top 5 highest bonus amounts from the Pay table. The TOP 5 clause ensures that only the highest 5 bonuses are considered.
• Ordering with ORDER BY Clause:
  • ORDER BY EP.Bonus DESC: The results are sorted in descending order by the bonus amount, so the highest bonuses appear first.

SELECT CustomerName
FROM CustomerTab.CustomerTabl
WHERE CustomerID IN (
SELECT CustomerID
FROM CustomerTab.CustomerOrdersTabl
GROUP BY CustomerID
HAVING SUM(OrderQuantity) > 25
);

This SQL query retrieves the names of customers who have placed orders with a total quantity exceeding 25 items.

It uses a subquery to identify customers who meet this criterion based on their order records, and then selects the customer names from the CustomerTab.CustomerTabl table.

• Selected Column:
  • CustomerName is selected from the CustomerTab.CustomerTabl table, providing the names of customers who meet the specified order quantity condition.
• Filtering with WHERE Clause and Subquery:
  • WHERE CustomerID IN (...): This condition filters the results to include only customers whose IDs match those returned by the subquery.
  • The subquery SELECT CustomerID FROM CustomerTab.CustomerOrdersTabl GROUP BY CustomerID HAVING SUM(OrderQuantity) > 25 identifies customers from the CustomerOrdersTabl table who have placed orders totalling more than 25 items.
    • GROUP BY CustomerID: Groups the orders by customer.
    • HAVING SUM(OrderQuantity) > 25: Ensures that only customers with a total order quantity greater than 25 are included.

SELECT
CustomerID,
SUM(order_total) AS total_spent
FROM (
SELECT
o.CustomerID,
p.Cost * o.OrderQuantity AS order_total
FROM
CustomerTab.CustomerOrdersTabl o
JOIN
CustomerTab.CustomerProductTabl p ON o.ProductID = p.ProductID
) AS customer_orders
GROUP BY
CustomerID
ORDER BY
total_spent DESC;

This SQL query calculates the total amount spent by each customer by combining information from the CustomerOrdersTabl and CustomerProductTabl tables.

It first calculates the total for each order, and then sums these totals by customer. The results are sorted in descending order of the total amount spent.

• Main Query:
  • CustomerID is selected to identify each customer.
  • SUM(order_total) AS total_spent calculates the total amount spent by each customer by summing up the values of order_total. The result is labelled as total_spent.
• Subquery (customer_orders):
  • The subquery calculates the total cost of each order by multiplying the Cost of the product from the CustomerProductTabl table with the OrderQuantity from the CustomerOrdersTabl table.
  • JOIN clause: This join links each order to its corresponding product using the ProductID.
    • o.CustomerID: Refers to the customer who placed the order.
    • p.Cost * o.OrderQuantity AS order_total: Calculates the total cost of each order by multiplying the product’s cost by the quantity ordered.
• GROUP BY Clause:
  • GROUP BY CustomerID: Groups the results by customer, allowing the query to calculate the total amount spent by each individual customer.
• Ordering with ORDER BY Clause:
  • ORDER BY total_spent DESC: The results are sorted in descending order by the total amount spent, so the customers who have spent the most appear first.

SELECT
p.ProductID,
p.ProductDescription
FROM
CustomerTab.CustomerProductTabl p
LEFT JOIN
(
SELECT DISTINCT
ProductID
FROM
CustomerTab.CustomerOrdersTabl
) AS ordered_products
ON
p.ProductID = ordered_products.ProductID
WHERE
ordered_products.ProductID IS NULL;

This SQL query retrieves a list of products that have not been ordered by any customers.

It uses a LEFT JOIN to combine the CustomerProductTabl table (aliased as p) with a subquery that identifies all products that have been ordered, ensuring that only products without orders are included in the result.

• Selected Columns:
  • ProductID and ProductDescription are selected from the CustomerProductTabl table to provide the ID and description of each product.
• Subquery (ordered_products):
  • The subquery selects distinct ProductID values from the CustomerOrdersTabl table, identifying products that have been ordered at least once.
  • SELECT DISTINCT ProductID FROM CustomerTab.CustomerOrdersTabl: This ensures that each product ID appears only once, regardless of how many times it has been ordered.
• LEFT JOIN Operation:
  • The LEFT JOIN is used to join the CustomerProductTabl table with the ordered_products subquery. This join includes all products from the CustomerProductTabl table, even if they do not have a matching entry in the ordered_products subquery.
  • ON p.ProductID = ordered_products.ProductID: This condition matches products from the product table with the ordered products subquery based on ProductID.
• Filtering with WHERE Clause:
  • WHERE ordered_products.ProductID IS NULL: This condition filters the results to include only those products that do not have a corresponding ProductID in the ordered_products subquery. These are products that have not been ordered.

SELECT
CustomerID,
AVG(order_total) AS average_order_cost
FROM (
SELECT
o.CustomerID,
p.Cost * o.OrderQuantity AS order_total
FROM
CustomerTab.CustomerOrdersTabl o
JOIN
CustomerTab.CustomerProductTabl p ON o.ProductID = p.ProductID
) AS customer_orders
GROUP BY
CustomerID
HAVING
AVG(order_total) > 50;

This SQL query calculates the average order cost for each customer by joining the CustomerOrdersTabl table (aliased as o) with the CustomerProductTabl table (aliased as p).

It only includes customers whose average order cost exceeds £50. The query uses a subquery to compute the total cost of each order before calculating the average order cost per customer.

• Main Query:
  • CustomerID is selected to identify each customer.
  • AVG(order_total) AS average_order_cost calculates the average total cost of orders placed by each customer. The result is labelled as average_order_cost.
• Subquery (customer_orders):
  • The subquery calculates the total cost of each order by multiplying the Cost of the product from the CustomerProductTabl table by the OrderQuantity from the CustomerOrdersTabl table.
  • JOIN clause: This join links each order to its corresponding product using the ProductID.
    • o.CustomerID: Refers to the customer who placed the order.
    • p.Cost * o.OrderQuantity AS order_total: Calculates the total cost of each order by multiplying the product’s cost by the quantity ordered.
• GROUP BY Clause:
  • GROUP BY CustomerID: Groups the results by customer, allowing the query to calculate the average order cost for each individual customer.
• Filtering with HAVING Clause:
  • HAVING AVG(order_total) > 50: This condition filters the results to include only those customers whose average order cost is greater than £50.

SELECT AVG(EP.Salary) AS AverageSalary, COUNT(*) AS EmployeeCount
FROM Employee.Pay AS EP
WHERE YEAR(EP.HireDate) = (
SELECT YEAR(HireDate)
FROM Employee.Pay
WHERE Salary = (
SELECT MAX(Salary)
FROM Employee.Pay
)
);

This SQL query calculates the average salary and counts the number of employees hired in the same year as the employee with the highest salary.

It involves multiple levels of subqueries to determine the relevant year and then uses this information to filter the employees accordingly.

• Main Query:
  • AVG(EP.Salary) AS AverageSalary: Calculates the average salary of employees hired in a specific year, labelling the result as AverageSalary.
  • COUNT(*) AS EmployeeCount: Counts the total number of employees hired in that same year, labelling the result as EmployeeCount.
• Filtering with WHERE Clause:
  • WHERE YEAR(EP.HireDate) = (...): This condition filters the main query to include only those employees hired in a particular year, which is determined by a subquery.
• Subquery Structure:
  • The first subquery SELECT YEAR(HireDate) FROM Employee.Pay WHERE Salary = (...) identifies the year in which the employee with the highest salary was hired.
    • The innermost subquery SELECT MAX(Salary) FROM Employee.Pay finds the maximum salary in the Employee.Pay table.
    • The outer subquery then finds the HireDate of the employee with this maximum salary and extracts the year.

SELECT E.EmployeeID, E.FirstName, E.LastName, EP.JobTitle, EP.Salary
FROM Employee.Employee AS E
INNER JOIN Employee.Pay AS EP ON E.EmployeeID = EP.EmployeeID
WHERE EP.HireDate <= DATEADD(YEAR, -5, GETDATE())
AND EP.Salary < (
SELECT AVG (EP2.Salary)
FROM Employee.Pay AS EP2
WHERE EP2.JobTitle = EP.JobTitle
);

This SQL query retrieves details of employees who have been employed for at least 5 years and earn a salary below the average for their specific job title.

It combines data from the Employee.Employee table (aliased as E) and the Employee.Pay table (aliased as EP) using an INNER JOIN.

The query uses subqueries to compare each employee’s salary against the average salary for their job title.

• Selected Columns:
  • EmployeeID, FirstName, and LastName are selected from the Employee table to provide identification and personal details for each employee.
  • JobTitle and Salary are selected from the Pay table to provide job-related details and salary information.
• INNER JOIN Operation:
  • The INNER JOIN links the Employee and Pay tables using the EmployeeID, ensuring that only employees with matching IDs in both tables are included in the results.
• Filtering with WHERE Clause:
  • EP.HireDate <= DATEADD(YEAR, -5, GETDATE()): This condition filters the results to include only employees who were hired at least 5 years ago. The DATEADD(YEAR, -5, GETDATE()) function calculates the date 5 years before the current date.
  • AND EP.Salary < (...): This condition filters the results to include only those employees whose salary is below the average salary for their job title.
    • The subquery SELECT AVG(EP2.Salary) FROM Employee.Pay AS EP2 WHERE EP2.JobTitle = EP.JobTitle calculates the average salary for each job title. It compares the employee’s salary against this average, filtering out those who earn less than the average.

SELECT
c.CustomerName,
c.Address
FROM
CustomerTab.CustomerTabl c
WHERE
c.CustomerID IN (
SELECT TOP 10
o.CustomerID
FROM
CustomerTab.CustomerOrdersTabl o
GROUP BY
o.CustomerID
ORDER BY
SUM(o.OrderQuantity) DESC
);

This SQL query retrieves the names and addresses of the top 10 customers who have placed the largest total order quantities.

It uses a subquery to identify these top customers based on their order quantities and filters the main query to include only those customers.

• Selected Columns:
  • CustomerName and Address are selected from the CustomerTabl table to provide the names and addresses of the top customers.
• Filtering with WHERE Clause:
  • WHERE c.CustomerID IN (...): This condition filters the main query to include only those customers whose IDs are in the list returned by the subquery.
• Subquery Structure:
  • The subquery SELECT TOP 10 o.CustomerID FROM CustomerTab.CustomerOrdersTabl o GROUP BY o.CustomerID ORDER BY SUM(o.OrderQuantity) DESC identifies the top 10 customers based on the total quantity of items they have ordered.
    • GROUP BY o.CustomerID: Groups the orders by customer, allowing the query to aggregate order quantities.
    • ORDER BY SUM(o.OrderQuantity) DESC: Orders the results by the total order quantity in descending order, so customers with the highest order quantities appear first.
    • SELECT TOP 10: Limits the result to the top 10 customers.

SELECT
ProductID,
ProductDescription,
Cost,
'Top-Selling' AS SalesCategory
FROM
CustomerTab.CustomerProductTabl
WHERE
ProductID IN (
SELECT TOP 5
ProductID
FROM
CustomerTab.CustomerProductTabl
ORDER BY
Cost DESC
)
UNION
-- Query to retrieve least-selling products
SELECT
ProductID,
ProductDescription,
Cost,
'Least-Selling' AS SalesCategory
FROM
CustomerTab.CustomerProductTabl
WHERE
ProductID IN (
SELECT TOP 5
ProductID
FROM
CustomerTab.CustomerProductTabl
ORDER BY
Cost ASC
);

This SQL query retrieves the top 5 most expensive and top 5 least expensive products from the CustomerTab.CustomerProductTabl table and categorises them as either ‘Top-Selling’ or ‘Least-Selling’ based on their cost.

The query uses two separate subqueries to identify these products and combines the results using a UNION.

• Selected Columns:
  • ProductID, ProductDescription, and Cost are selected from the CustomerProductTabl table to provide details about each product.
  • 'Top-Selling' AS SalesCategory and 'Least-Selling' AS SalesCategory are hardcoded string values used to label the products as ‘Top-Selling’ or ‘Least-Selling’ based on the subquery they are part of.
• Subquery for Top-Selling Products:
  • SELECT TOP 5 ProductID FROM CustomerTab.CustomerProductTabl ORDER BY Cost DESC: This subquery selects the top 5 most expensive products.
    • ORDER BY Cost DESC: Orders the products by cost in descending order to identify the most expensive products.
• Subquery for Least-Selling Products:
  • SELECT TOP 5 ProductID FROM CustomerTab.CustomerProductTabl ORDER BY Cost ASC: This subquery selects the top 5 least expensive products.
    • ORDER BY Cost ASC: Orders the products by cost in ascending order to identify the least expensive products.
• UNION Operator:
  • The UNION operator combines the results of the two queries into a single result set. It ensures that the combined results do not include duplicate rows, but since each product is assigned a unique SalesCategory, duplicates are unlikely.

SELECT
ProductID,
ProductDescription AS Name,
NULL AS Address
FROM
CustomerTab.CustomerProductTabl
UNION
-- Query to retrieve customers from the specified city
SELECT
CustomerID,
CustomerName AS Name,
City AS Location
FROM
CustomerTab.CustomerTabl

This SQL query combines information about products and customers into a single result set using the UNION operator.

It selects product details from the CustomerProductTabl table and customer details from the CustomerTabl table, ensuring the results share the same structure by using aliases and placeholder values.

• First Query (Product Information):
  • ProductID is selected to identify each product uniquely.
  • ProductDescription AS Name renames the ProductDescription column to Name to match the structure of the customer query.
  • NULL AS Address: This column is included to match the structure of the second query. Since products do not have an address, NULL is used as a placeholder.
• Second Query (Customer Information):
  • CustomerID is selected to identify each customer uniquely.
  • CustomerName AS Name renames the CustomerName column to Name to match the structure of the product query.
  • City AS Location: This column provides the location of each customer, serving a similar purpose to the NULL placeholder in the first query, but here it includes actual data.
• UNION Operator:
  • The UNION operator combines the results of the two queries into a single result set. It removes duplicate rows, if any, ensuring that each unique row is displayed only once.

SELECT
c.CustomerID,
c.CustomerName,
c.Address,
c.City
FROM
CustomerTab.CustomerTabl c
EXCEPT
-- Query to retrieve customers who have placed orders
SELECT
c.CustomerID,
c.CustomerName,
c.Address,
c.City
FROM
CustomerTab.CustomerTabl c
JOIN
CustomerTab.CustomerOrdersTabl o
ON
c.CustomerID = o.CustomerID;

This SQL query retrieves a list of customers who have not placed any orders.

It uses the EXCEPT operator to find the difference between two result sets: the first set containing all customers, and the second set containing only those customers who have placed orders.

The EXCEPT operator ensures that only customers without orders are returned.

• First Query (All Customers):
  • CustomerID, CustomerName, Address, and City are selected from the CustomerTabl table to provide details about all customers.
• Second Query (Customers Who Have Placed Orders):
  • CustomerID, CustomerName, Address, and City are selected from the CustomerTabl table joined with the CustomerOrdersTabl table.
  • JOIN CustomerTab.CustomerOrdersTabl o ON c.CustomerID = o.CustomerID: This JOIN links the CustomerTabl table to the CustomerOrdersTabl table using the CustomerID. This ensures that only customers who have placed orders are included in this result set.
• EXCEPT Operator:
  • The EXCEPT operator returns only the rows from the first query that are not present in the second query. This effectively filters out customers who have placed orders, leaving only those who have not.

SELECT
p.ProductID,
p.ProductDescription,
p.Cost
FROM
CustomerTab.CustomerProductTabl p
EXCEPT
-- Query to retrieve products that have been ordered
SELECT
p.ProductID,
p.ProductDescription,
p.Cost
FROM
CustomerTab.CustomerProductTabl p
JOIN
CustomerTab.CustomerOrdersTabl o
ON
p.ProductID = o.ProductID;

This SQL query retrieves a list of products that have never been ordered.

It uses the EXCEPT operator to find the difference between two result sets: the first set containing all products, and the second set containing only those products that have been ordered.

The EXCEPT operator ensures that only products without any orders are returned.

• First Query (All Products):
  • ProductID, ProductDescription, and Cost are selected from the CustomerProductTabl table to provide details about all products available.
• Second Query (Products That Have Been Ordered):
  • ProductID, ProductDescription, and Cost are selected from the CustomerProductTabl table joined with the CustomerOrdersTabl table.
  • JOIN CustomerTab.CustomerOrdersTabl o ON p.ProductID = o.ProductID: This JOIN links the CustomerProductTabl table to the CustomerOrdersTabl table using the ProductID. This ensures that only products that have been ordered are included in this result set.
• EXCEPT Operator:
  • The EXCEPT operator returns only the rows from the first query that are not present in the second query. This effectively filters out products that have been ordered, leaving only those that have not.

SELECT
JobTitle,
EmployeeID,
SUM(Salary) as TotalSalary
FROM
Employee.Pay
GROUP BY ROLLUP (JobTitle, EmployeeID );

This SQL query calculates the total salary for each employee and for each job title, using the ROLLUP feature to provide subtotals and a grand total.

The GROUP BY ROLLUP clause is used to group the results by JobTitle and EmployeeID, generating summary rows at different levels of aggregation.

• Selected Columns:
  • JobTitle is selected to group the results by the job titles of employees.
  • EmployeeID is selected to provide specific salary details for each employee within a job title.
  • SUM(Salary) AS TotalSalary calculates the total salary for each group, whether it’s per employee, per job title, or the grand total. The result is labelled as TotalSalary.
• GROUP BY ROLLUP Clause:
  • The ROLLUP (JobTitle, EmployeeID) feature is used to generate grouped results with subtotals and a grand total:
    • Per Employee: It first groups the results by each EmployeeID within each JobTitle.
    • Per Job Title: It then provides a subtotal for each JobTitle, showing the total salary of all employees with that title.
    • Grand Total: Finally, it provides a grand total of salaries for all employees, regardless of their job titles.

SELECT CustomerID, ProductID,
COUNT(*) AS TotalOrders,
SUM(OrderQuantity) AS TotalQuantity
FROM CustomerTab.CustomerOrdersTabl
GROUP BY CustomerID, ProductID
WITH ROLLUP;

This SQL query calculates the total number of orders and the total quantity of products ordered by each customer, using the ROLLUP feature to provide subtotals for each customer and a grand total.

The GROUP BY ROLLUP clause is used to group the results by CustomerID and ProductID, generating summary rows at different levels of aggregation.

• Selected Columns:
  • CustomerID is selected to group the results by each customer, identifying which customer placed the orders.
  • ProductID is selected to group the results by each product, identifying which product was ordered.
  • COUNT(*) AS TotalOrders calculates the total number of orders placed by each customer for each product. The result is labelled as TotalOrders.
  • SUM(OrderQuantity) AS TotalQuantity calculates the total quantity of products ordered by each customer for each product. The result is labelled as TotalQuantity.
• GROUP BY ROLLUP Clause:
  • The ROLLUP (CustomerID, ProductID) feature is used to generate grouped results with subtotals and a grand total:
    • Per Customer and Product: It first groups the results by each ProductID within each CustomerID.
    • Per Customer: It then provides a subtotal for each CustomerID, showing the total orders and quantity for all products ordered by that customer.
    • Grand Total: Finally, it provides a grand total for all customers and all products, showing the overall total orders and quantities.

SELECT CustomerID, ProductID, MONTH(OrderDate) AS OrderMonth,
COUNT(*) AS TotalOrders,
SUM(OrderQuantity) AS TotalQuantity
FROM CustomerTab.CustomerOrdersTabl
GROUP BY CustomerID, ProductID, MONTH(OrderDate)
WITH ROLLUP;

This SQL query calculates the total number of orders and the total quantity of products ordered by each customer for each product, broken down by the month in which the order was placed.

The query uses the ROLLUP feature to provide subtotals by customer, product, and month, as well as a grand total.

The GROUP BY ROLLUP clause is used to generate grouped results at different levels of aggregation.

• Selected Columns:
  • CustomerID is selected to identify each customer.
  • ProductID is selected to identify each product ordered.
  • MONTH(OrderDate) AS OrderMonth extracts the month from the OrderDate to show when the orders were placed. The result is labelled as OrderMonth.
  • COUNT(*) AS TotalOrders calculates the total number of orders placed by each customer for each product in each month. The result is labelled as TotalOrders.
  • SUM(OrderQuantity) AS TotalQuantity calculates the total quantity of products ordered by each customer for each product in each month. The result is labelled as TotalQuantity.
• GROUP BY ROLLUP Clause:
  • The ROLLUP (CustomerID, ProductID, MONTH(OrderDate)) feature generates grouped results with subtotals and a grand total:
    • Per Customer, Product, and Month: It first groups the results by each ProductID and OrderMonth within each CustomerID.
    • Per Customer and Product: It provides subtotals for each CustomerID and ProductID, showing total orders and quantity for all months.
    • Per Customer: It provides subtotals for each CustomerID, showing total orders and quantity for all products and months.
    • Grand Total: Finally, it provides a grand total for all customers, products, and months, showing overall total orders and quantities.

SELECT JobTitle, SUM(Salary) AS TotalSalary
FROM Employee.Pay
GROUP BY JobTitle
WITH ROLLUP;

This SQL query calculates the total salary paid to employees for each job title and provides a grand total for all job titles using the ROLLUP feature.

The GROUP BY ROLLUP clause groups the results by JobTitle, generating summary rows at different levels of aggregation.

• Selected Columns:
  • JobTitle is selected to identify each job title within the organisation.
  • SUM(Salary) AS TotalSalary calculates the total salary paid to all employees with a particular job title. The result is labelled as TotalSalary.
• GROUP BY ROLLUP Clause:
  • The ROLLUP (JobTitle) feature generates grouped results with subtotals and a grand total:
    • Per Job Title: It groups the results by each JobTitle, providing the total salary for each job title.
    • Grand Total: It provides a grand total of salaries for all job titles combined. This grand total row will have a NULL value in the JobTitle column, representing the overall sum of all salaries.

SELECT
JobTitle AS Job_Title,
City AS City,
YEAR(HireDate) AS Hire_Year,
SUM(Salary) AS Total_Compensation_Expense
FROM
Employee.Pay
JOIN
Employee.Employee ON Employee.Pay.EmployeeID = Employee.Employee.EmployeeID
GROUP BY
CUBE (JobTitle, City, YEAR(HireDate))
ORDER BY
Job_Title, City, Hire_Year;

This SQL query calculates the total salary expenses by job title, city, and hire year, using the CUBE feature to provide multiple levels of aggregation.

The query joins the Employee.Pay table with the Employee.Employee table to combine salary data with job title and location information.

The CUBE operator generates subtotals for every combination of job title, city, and hire year, including higher-level summaries.

• Selected Columns:
  • JobTitle AS Job_Title renames the JobTitle column to Job_Title to provide a clear and consistent format.
  • City AS City selects the city where each employee works.
  • YEAR(HireDate) AS Hire_Year extracts the year from the HireDate to show when each employee was hired.
  • SUM(Salary) AS Total_Compensation_Expense calculates the total salary expense for each combination of job title, city, and hire year. The result is labelled as Total_Compensation_Expense.
• JOIN Operation:
  • JOIN Employee.Employee ON Employee.Pay.EmployeeID = Employee.Employee.EmployeeID: This join combines the Pay and Employee tables using the EmployeeID, ensuring that salary data is matched with the corresponding job title, city, and hire date.
• GROUP BY CUBE Clause:
  • The CUBE (JobTitle, City, YEAR(HireDate)) feature generates grouped results with subtotals and a grand total:
    • Per Job Title, City, and Hire Year: It groups the results by every combination of JobTitle, City, and Hire_Year.
    • Subtotals: It provides subtotals for each JobTitle across all cities and years, each City across all job titles and years, and each Hire_Year across all job titles and cities.
    • Grand Total: It provides a grand total for all job titles, cities, and hire years combined.
• Ordering with ORDER BY Clause:
  • The results are ordered by Job_Title, City, and Hire_Year, providing a structured view of the salary expenses.

SELECT
p.ProductID AS Product,
c.City AS City,
YEAR(o.OrderDate) AS Order_Year,
SUM(o.OrderQuantity * p.Cost) AS "Revenue"
FROM
CustomerTab.CustomerOrdersTabl o
JOIN
CustomerTab.CustomerTabl c ON o.CustomerID = c.CustomerID
JOIN
CustomerTab.CustomerProductTabl p ON o.ProductID = p.ProductID
GROUP BY
CUBE (p.ProductID, c.City, YEAR(o.OrderDate))
ORDER BY
Product, City, Order_Year;

This SQL query calculates the total revenue generated by each product, in each city, and for each year, using the CUBE feature to provide various levels of aggregation.

The query joins the CustomerOrdersTabl, CustomerTabl, and CustomerProductTabl tables to combine order data with customer and product details.

The CUBE operator generates subtotals for every combination of product, city, and year, as well as higher-level summaries.

• Selected Columns:
  • p.ProductID AS Product renames the ProductID column to Product to clearly indicate that this field represents the product being analysed.
  • c.City AS City selects the city where the customer placed the order.
  • YEAR(o.OrderDate) AS Order_Year extracts the year from the OrderDate to show when the order was placed.
  • SUM(o.OrderQuantity * p.Cost) AS "Revenue" calculates the total revenue by multiplying the order quantity by the product cost. The result is labelled as "Revenue".
• JOIN Operations:
  • JOIN CustomerTab.CustomerTabl c ON o.CustomerID = c.CustomerID: This join links the orders with customer details using the CustomerID, ensuring that each order is associated with the correct customer.
  • JOIN CustomerTab.CustomerProductTabl p ON o.ProductID = p.ProductID: This join links the orders with product details using the ProductID, ensuring that each order is associated with the correct product.
• GROUP BY CUBE Clause:
  • The CUBE (p.ProductID, c.City, YEAR(o.OrderDate)) feature generates grouped results with subtotals and a grand total:
    • Per Product, City, and Year: It groups the results by every combination of ProductID, City, and Order_Year.
    • Subtotals: It provides subtotals for each ProductID across all cities and years, each City across all products and years, and each Order_Year across all products and cities.
    • Grand Total: It provides a grand total for all products, cities, and years combined.
• Ordering with ORDER BY Clause:
  • The results are ordered by Product, City, and Order_Year, providing a structured and organised view of the revenue data.

SELECT
CustomerID,
CASE
WHEN SUM(OrderQuantity * Cost) > 500 THEN 'Loyal Customer'
ELSE 'Regular Customer'
END AS CustomerStatus,
SUM(OrderQuantity * Cost) AS TotalAmountSpent
FROM CustomerTab.CustomerOrdersTabl
JOIN CustomerTab.CustomerProductTabl ON CustomerTab.CustomerOrdersTabl.ProductID =
CustomerTab.CustomerProductTabl.ProductID
GROUP BY CustomerID
HAVING SUM(OrderQuantity * Cost) > 200;

This SQL query categorises customers based on the total amount they have spent and calculates the total amount spent by each customer.

Customers are labelled as either ‘Loyal Customer’ or ‘Regular Customer’ depending on their total spending.

The query uses a JOIN to combine order and product data, then groups the results by CustomerID and filters out customers who have spent less than £200 using the HAVING clause.

• Selected Columns:
  • CustomerID is selected to identify each customer uniquely.
  • CASE WHEN ... THEN ... ELSE ... END AS CustomerStatus: This CASE statement categorises customers based on their spending:
    • If the total amount spent (SUM(OrderQuantity * Cost)) is greater than £500, the customer is labelled as ‘Loyal Customer’.
    • Otherwise, the customer is labelled as ‘Regular Customer’.
  • SUM(OrderQuantity * Cost) AS TotalAmountSpent calculates the total amount spent by each customer by multiplying the order quantity by the product cost. The result is labelled as TotalAmountSpent.
• JOIN Operation:
  • JOIN CustomerTab.CustomerProductTabl ON CustomerTab.CustomerOrdersTabl.ProductID = CustomerTab.CustomerProductTabl.ProductID: This join links the orders to the product details using the ProductID, ensuring that the correct cost is applied to each order.
• GROUP BY Clause:
  • GROUP BY CustomerID: Groups the results by customer, allowing the query to calculate the total amount spent for each customer.
• Filtering with HAVING Clause:
  • HAVING SUM(OrderQuantity * Cost) > 200: This condition filters the results to include only those customers who have spent more than £200 in total.

SELECT
ProductDescription,
Cost,
SUM(OrderQuantity * Cost) AS TotalRevenue,
SUM(OrderQuantity) AS TotalQuantitySold,
((SUM(OrderQuantity * Cost) - COUNT(OrderID) * Cost) / SUM(OrderQuantity *
Cost)) * 100 AS ProfitMargin,
CASE
WHEN SUM(OrderQuantity * Cost) > 500 THEN 'High Sales'
ELSE 'Low Sales'
END AS SalesCategory,
CASE
WHEN SUM(OrderQuantity) > 100 THEN 'High Demand'
ELSE 'Low Demand'
END AS DemandCategory,
CASE
WHEN ((SUM(OrderQuantity * Cost) - COUNT(OrderID) * Cost) / SUM
(OrderQuantity * Cost)) * 100 >= 30 THEN 'High Profit Margin'
ELSE 'Low Profit Margin'
END AS ProfitMarginCategory,
CASE
WHEN SUM(OrderQuantity) = 0 THEN 'Not Sold'
ELSE 'Sold'
END AS SoldStatus
FROM CustomerTab.CustomerOrdersTabl
JOIN CustomerTab.CustomerProductTabl ON CustomerTab.CustomerOrdersTabl.ProductID =
CustomerTab.CustomerProductTabl.ProductID
GROUP BY ProductDescription, Cost
HAVING SUM(OrderQuantity * Cost) > 500 OR SUM(OrderQuantity) > 100 OR ((SUM
(OrderQuantity * Cost) - COUNT(OrderID) * Cost) / SUM(OrderQuantity * Cost)) *
100 >= 30 OR SUM(OrderQuantity) = 0;

This SQL query provides detailed analytics about products based on their sales, demand, and profit margin. It categorises products using several CASE statements and calculates various metrics to give insights into product performance.

The query uses a JOIN to combine order data with product details and applies a GROUP BY clause to aggregate results by product description and cost.

• Selected Columns and Calculations:
  • ProductDescription: Describes the product.
  • Cost: The unit cost of the product.
  • SUM(OrderQuantity * Cost) AS TotalRevenue: Calculates total revenue by multiplying order quantity by the cost.
  • SUM(OrderQuantity) AS TotalQuantitySold: Calculates the total quantity of the product sold.
  • ((SUM(OrderQuantity * Cost) - COUNT(OrderID) * Cost) / SUM(OrderQuantity * Cost)) * 100 AS ProfitMargin: Calculates the profit margin as a percentage.
  • CASE statements:
    • SalesCategory: Categorises products as ‘High Sales’ if total revenue is over £500, otherwise ‘Low Sales’.
    • DemandCategory: Categorises products as ‘High Demand’ if more than 100 units are sold, otherwise ‘Low Demand’.
    • ProfitMarginCategory: Categorises products as ‘High Profit Margin’ if the profit margin is 30% or higher, otherwise ‘Low Profit Margin’.
    • SoldStatus: Categorises products as ‘Not Sold’ if no units are sold, otherwise ‘Sold’.
• JOIN Operation:
  • JOIN CustomerTab.CustomerProductTabl ON CustomerTab.CustomerOrdersTabl.ProductID = CustomerTab.CustomerProductTabl.ProductID: This join links the orders to the corresponding product details using the ProductID.
• GROUP BY Clause:
  • GROUP BY ProductDescription, Cost: Groups the results by product description and cost, allowing the query to calculate aggregated metrics for each product.
• Filtering with HAVING Clause:
  • The HAVING clause filters products based on several conditions to highlight significant products:
    • Total revenue over £500.
    • More than 100 units sold.
    • Profit margin 30% or higher.
    • Products that have not been sold.