SQL exercises on retail shopping: Multiple table queries

SELECT E.LastName,
E.FirstName,
EP.JobTitle,
EP.Wage
FROM Worker.Worker E
INNER JOIN Worker.Pay EP ON E.EmployeeID = EP.EmployeeID
WHERE (EP.JobTitle="Gross sales Assist" OR EP.JobTitle="Salesman")
AND DATEDIFF (YEAR, EP.HireDate, EP.LastDate) >= 5;

Chosen Columns: The question selects LastName and FirstName from the Worker desk, and JobTitle and Wage from the Pay desk. These columns present private and job-related details about every worker.

INNER JOIN: The INNER JOIN clause matches rows from the Worker desk with rows from the Pay desk based mostly on the EmployeeID, guaranteeing that solely data with matching EmployeeID values are included within the outcomes.

Filtering with WHERE Clause: The question applies two filters:

• (EP.JobTitle="Gross sales Assist" OR EP.JobTitle="Salesman"): This situation filters the outcomes to incorporate solely workers whose job title is both ‘Gross sales Assist’ or ‘Salesman’.
• DATEDIFF(YEAR, EP.HireDate, EP.LastDate) >= 5: This situation ensures that solely workers with at the least 5 years of service (calculated by the distinction in years between the HireDate and LastDate) are included.

SELECT E.LastName,
E.FirstName,
E.Metropolis,
EP.JobTitle,
EP.Bonus
FROM Worker.Worker E
INNER JOIN Worker.Pay EP ON E.EmployeeID = EP.EmployeeID
WHERE EP.JobTitle="Salesman" OR EP.JobTitle="Gross sales Assist"
AND EP.Bonus >= 1000
ORDER BY EP.JobTitle, EP.Bonus;

Chosen Columns: The question selects LastName, FirstName, and Metropolis from the Worker desk, and JobTitle and Bonus from the Pay desk. This mixture gives private particulars and job-related monetary data.

INNER JOIN: The INNER JOIN clause combines data from the Worker and Pay tables based mostly on the EmployeeID, guaranteeing that solely workers with matching IDs in each tables are included.

Filtering with WHERE Clause: The question applies the next filters:

• (EP.JobTitle="Salesman" OR EP.JobTitle="Gross sales Assist"): This situation restricts the outcomes to workers who maintain both the ‘Salesman’ or ‘Gross sales Assist’ job titles.
• AND EP.Bonus >= 1000: This situation additional filters the outcomes to incorporate solely these workers who’ve acquired a bonus of at the least 1,000.

Ordering with ORDER BY Clause: The outcomes are ordered first by JobTitle after which by Bonus in ascending order. This sorting helps organise the info, making it simpler to check workers throughout the identical job title class based mostly on their bonus quantities.

SELECT E.LastName,
E.FirstName,
E.Metropolis,
EP.JobTitle,
EP.Wage,
DATEDIFF (YEAR, EP.HireDate, GETDATE()) AS TenureInYears
FROM Worker.Worker E
INNER JOIN Worker.Pay EP ON E.EmployeeID = EP.EmployeeID
WHERE EP.JobTitle="Salesman" or EP.JobTitle="Gross sales Assist"
ORDER BY EP.JobTitle, EP.Wage;

Chosen Columns:

• LastName, FirstName, and Metropolis are chosen from the Worker desk to offer private particulars about every worker.
• JobTitle and Wage are chosen from the Pay desk to offer job-related and monetary data.
• DATEDIFF(YEAR, EP.HireDate, GETDATE()) AS TenureInYears calculates the variety of years every worker has been with the corporate, utilizing the distinction between their rent date and the present date. This result’s labeled as TenureInYears.

INNER JOIN:

• The INNER JOIN clause combines data from the Worker and Pay tables based mostly on the EmployeeID, guaranteeing solely workers with matching IDs in each tables are included.

Filtering with WHERE Clause:

• The situation (EP.JobTitle="Salesman" OR EP.JobTitle="Gross sales Assist") restricts the outcomes to incorporate solely workers with the job titles ‘Salesman’ or ‘Gross sales Assist’.

Ordering with ORDER BY Clause:

• The outcomes are ordered first by JobTitle after which by Wage in ascending order. This sorting helps organise the info, making it simpler to analyse and examine workers throughout the identical job title class based mostly on their wage.

SELECT C.CustomerName,
C.Metropolis,
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 question retrieves buyer names, cities, order dates, product descriptions, and order portions by becoming a member of three tables: CustomerTab.CustomerTabl (aliased as C), CustomerTab.CustomerOrdersTabl (aliased as CO), and CustomerTab.CustomerProductTabl (aliased as P).

It makes use of INNER JOIN operations to mix associated data from these tables. The outcomes are then sorted by order date and order amount.

• Chosen Columns:
  • CustomerName and Metropolis are chosen from the CustomerTabl desk to offer buyer particulars.
  • OrderDate and OrderQuantity are chosen from the CustomerOrdersTabl desk to offer details about the order’s date and amount.
  • ProductDescription is chosen from the CustomerProductTabl desk to offer particulars in regards to the product ordered.
• INNER JOIN Operations:
  • The primary INNER JOIN hyperlinks the CustomerTabl and CustomerOrdersTabl tables utilizing the CustomerID to make sure that every order is matched with the proper buyer.
  • The second INNER JOIN hyperlinks the CustomerOrdersTabl and CustomerProductTabl tables utilizing the ProductID to match every order with the proper product particulars.
• Ordering with ORDER BY Clause:
  • The outcomes are ordered first by OrderDate after which by OrderQuantity in ascending order. This sorting helps in organising the info chronologically and by the variety of merchandise ordered, making it simpler to analyse order traits and buyer buying behaviour.

SELECT C.CustomerName,
C.Metropolis,
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 question retrieves buyer names, cities, order dates, product descriptions, and order portions by becoming a member of three tables: CustomerTab.CustomerTabl (aliased as C), CustomerTab.CustomerOrdersTabl (aliased as CO), and CustomerTab.CustomerProductTabl (aliased as P).

It makes use of LEFT JOIN operations to incorporate all clients, even when they haven’t any corresponding orders or merchandise.

The outcomes are then sorted by buyer title, order date, and product description.

• Chosen Columns:
  • CustomerName and Metropolis are chosen from the CustomerTabl desk to offer buyer particulars.
  • OrderDate and OrderQuantity are chosen from the CustomerOrdersTabl desk to offer details about the date of the order and the amount ordered.
  • ProductDescription is chosen from the CustomerProductTabl desk to offer particulars in regards to the product ordered.
• LEFT JOIN Operations:
  • The primary LEFT JOIN hyperlinks the CustomerTabl and CustomerOrdersTabl tables utilizing the CustomerID. This be part of contains all clients, even those that haven’t positioned any orders.
  • The second LEFT JOIN hyperlinks the CustomerOrdersTabl and CustomerProductTabl tables utilizing the ProductID. This be part of contains all orders, even when they don’t seem to be related to a product within the product desk.
• Ordering with ORDER BY Clause:
  • The outcomes are ordered first by CustomerName, then by OrderDate, and eventually by ProductDescription in ascending order. This sorting gives a transparent, organised view of the info, making it simpler to see buyer order patterns and the merchandise they’re enthusiastic about.

SELECT C.CustomerName,
C.Metropolis,
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 question retrieves buyer names, cities, order dates, product descriptions, and order portions by combining data from three tables: CustomerTab.CustomerOrdersTabl (aliased as CO), CustomerTab.CustomerTabl (aliased as C), and CustomerTab.CustomerProductTabl (aliased as P).

It makes use of RIGHT JOIN and LEFT JOIN operations to make sure all clients are included, even when they haven’t any orders or product particulars.

The outcomes are then sorted by buyer title, order date, and product description.

• Chosen Columns:
  • CustomerName and Metropolis are chosen from the CustomerTabl desk to offer buyer particulars.
  • OrderDate and OrderQuantity are chosen from the CustomerOrdersTabl desk to offer details about when the order was positioned and what number of gadgets have been ordered.
  • ProductDescription is chosen from the CustomerProductTabl desk to offer particulars in regards to the product ordered.
• RIGHT JOIN and LEFT JOIN Operations:
  • The RIGHT JOIN between CustomerOrdersTabl (CO) and CustomerTabl (C) ensures that every one clients are included, even those that haven’t positioned any orders. It hyperlinks the orders to clients based mostly on CustomerID.
  • The LEFT JOIN between CustomerOrdersTabl (CO) and CustomerProductTabl (P) contains all orders, even when they don’t seem to be related to a product, linking orders to merchandise utilizing ProductID.
• Ordering with ORDER BY Clause:
  • The outcomes are ordered first by CustomerName, then by OrderDate, and eventually by ProductDescription in ascending order. This sorting gives a transparent, organised view of the info, making it simpler to know buyer order patterns and the merchandise they’re enthusiastic about.

SELECT C.CustomerName,
C.Handle,
C.Metropolis,
C.ZipCode,
C.Cellphone,
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 question retrieves buyer particulars and their corresponding order data through the use of a FULL JOIN between the CustomerTab.CustomerTabl (aliased as C) and CustomerTab.CustomerOrdersTabl (aliased as CO) tables.

The question contains all clients and all orders, even when they don’t have matching data in each tables.

The outcomes are then sorted by buyer title and order date.

• Chosen Columns:
  • CustomerName, Handle, Metropolis, ZipCode, and Cellphone are chosen from the CustomerTabl desk to offer complete buyer particulars.
  • OrderID, OrderQuantity, and OrderDate are chosen from the CustomerOrdersTabl desk to offer details about the client’s orders, together with order identifiers, portions, and dates.
• FULL JOIN Operation:
  • The FULL JOIN between CustomerTabl (C) and CustomerOrdersTabl (CO) ensures that every one clients and all orders are included within the outcome set, no matter whether or not there’s a corresponding match within the different desk. If a buyer has no orders, the order particulars shall be NULL. Equally, if an order doesn’t have a corresponding buyer report, the client particulars shall be NULL.
• Ordering with ORDER BY Clause:
  • The outcomes are ordered first by CustomerName after which by OrderDate in ascending order. This sorting gives a transparent, organised view of the info, making it simpler to trace buyer actions and order histories.

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

This SQL question retrieves pairs of workers who dwell in the identical metropolis, offering particulars about every worker within the pair.

It makes use of a self-join on the Worker.Worker desk to search out workers with a typical metropolis, guaranteeing that every pair is listed solely as soon as.

The outcomes embrace the worker IDs, final names, first names, and telephone numbers for each workers, in addition to town they’ve in widespread.

• Chosen Columns:
  • EmployeeID, LastName, FirstName, and Cellphone from E1 (aliased as Employee1ID, Employee1LastName, Employee1FirstName, Employee1Phone) are chosen to offer particulars in regards to the first worker in every pair.
  • EmployeeID, LastName, FirstName, and Cellphone from E2 (aliased as Employee2ID, Employee2LastName, Employee2FirstName, Employee2Phone) are chosen to offer particulars in regards to the second worker in every pair.
  • Metropolis from E1 (aliased as CommonCity) is chosen to indicate town that each workers have in widespread.
• Self-Be part of Operation:
  • The INNER JOIN operation is carried out on the Worker.Worker desk, with E1 and E2 being aliases representing totally different cases of the identical desk.
  • The be part of situation E1.Metropolis = E2.Metropolis finds pairs of workers who dwell in the identical metropolis.
  • The situation E1.EmployeeID < E2.EmployeeID ensures that every pair is listed solely as soon as, 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 question retrieves product descriptions and their corresponding order portions by becoming a member of the CustomerTab.CustomerProductTabl desk (aliased as P) with the CustomerTab.CustomerOrdersTabl desk (aliased as O).

It makes use of a LEFT OUTER JOIN to make sure that all merchandise are included within the outcomes, even when they haven’t been ordered.

• Chosen Columns:
  • ProductDescription is chosen from the CustomerProductTabl desk to offer particulars in regards to the merchandise.
  • OrderQuantity is chosen from the CustomerOrdersTabl desk to indicate the amount of every product ordered.
• LEFT OUTER JOIN Operation:
  • The LEFT OUTER JOIN between CustomerProductTabl (P) and CustomerOrdersTabl (O) ensures that every one merchandise are listed within the outcome set, together with people who haven’t been ordered. If a product has no corresponding order within the CustomerOrdersTabl desk, the OrderQuantity shall be NULL.
  • The be part of situation P.ProductID = O.ProductID hyperlinks merchandise with their corresponding orders based mostly on the ProductID.

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

This SQL question retrieves a mix of worker particulars and whole compensation by performing a CROSS JOIN between the Worker.Worker desk (aliased as E) and the Worker.Pay desk (aliased as EP).

It calculates the entire compensation for every mixture of workers and pay data by including the wage and bonus.

The outcomes are then sorted by worker ID.

• Chosen Columns:
  • EmployeeID, LastName, and FirstName are chosen from the Worker desk to offer identification and private particulars for every worker.
  • (EP.Wage + EP.Bonus) AS TotalCompensation calculates the entire compensation by summing the Wage and Bonus from the Pay desk. This result’s given the alias TotalCompensation.
• CROSS JOIN Operation:
  • The CROSS JOIN produces a Cartesian product of the Worker and Pay tables, that means that every row from the Worker desk is mixed with each row from the Pay desk. This leads to each doable mixture of workers and pay data, no matter whether or not there’s a logical match.
  • One of these be part of is usually used when there isn’t a relationship between the 2 tables, or when producing all doable mixtures is required for evaluation or reporting.
• Ordering with ORDER BY Clause:
  • The outcomes are ordered by EmployeeID in ascending order. This sorting helps organise the info, making it simpler to find data based mostly on worker ID.

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

This SQL question retrieves the final names, first names, and bonus quantities of workers by becoming a member of the Worker.Worker desk (aliased as E) with the Worker.Pay desk (aliased as EP).

The question focuses on figuring out workers who’ve acquired one of many high 5 highest bonuses.

It makes use of a subquery to search out these high bonuses and kinds the leads to descending order of the bonus quantity.

• Chosen Columns:
  • LastName and FirstName are chosen from the Worker desk to offer the non-public particulars of every worker.
  • Bonus is chosen from the Pay desk to indicate the bonus quantity every worker acquired.
• INNER JOIN Operation:
  • The INNER JOIN hyperlinks the Worker and Pay tables utilizing the EmployeeID, guaranteeing that solely workers with matching IDs in each tables are included within the outcomes.
• Filtering with WHERE Clause and Subquery:
  • WHERE EP.Bonus IN (...): This situation filters the outcomes to incorporate solely these workers whose bonus is among the many high 5 highest bonuses.
  • SELECT TOP 5 Bonus FROM Worker.Pay: This subquery selects the highest 5 highest bonus quantities from the Pay desk. The TOP 5 clause ensures that solely the best 5 bonuses are thought-about.
• Ordering with ORDER BY Clause:
  • ORDER BY EP.Bonus DESC: The outcomes are sorted in descending order by the bonus quantity, so the best bonuses seem first.

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

This SQL question retrieves the names of consumers who’ve positioned orders with a complete amount exceeding 25 gadgets.

It makes use of a subquery to determine clients who meet this criterion based mostly on their order data, after which selects the client names from the CustomerTab.CustomerTabl desk.

• Chosen Column:
  • CustomerName is chosen from the CustomerTab.CustomerTabl desk, offering the names of consumers who meet the required order amount situation.
• Filtering with WHERE Clause and Subquery:
  • WHERE CustomerID IN (...): This situation filters the outcomes to incorporate solely clients whose IDs match these returned by the subquery.
  • The subquery SELECT CustomerID FROM CustomerTab.CustomerOrdersTabl GROUP BY CustomerID HAVING SUM(OrderQuantity) > 25 identifies clients from the CustomerOrdersTabl desk who’ve positioned orders totalling greater than 25 gadgets.
    • GROUP BY CustomerID: Teams the orders by buyer.
    • HAVING SUM(OrderQuantity) > 25: Ensures that solely clients with a complete order amount larger than 25 are included.

SELECT
CustomerID,
SUM(order_total) AS total_spent
FROM (
SELECT
o.CustomerID,
p.Price * 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 question calculates the entire quantity spent by every buyer by combining data from the CustomerOrdersTabl and CustomerProductTabl tables.

It first calculates the entire for every order, after which sums these totals by buyer. The outcomes are sorted in descending order of the entire quantity spent.

• Important Question:
  • CustomerID is chosen to determine every buyer.
  • SUM(order_total) AS total_spent calculates the entire quantity spent by every buyer by summing up the values of order_total. The result’s labelled as total_spent.
• Subquery (customer_orders):
  • The subquery calculates the entire price of every order by multiplying the Price of the product from the CustomerProductTabl desk with the OrderQuantity from the CustomerOrdersTabl desk.
  • JOIN clause: This be part of hyperlinks every order to its corresponding product utilizing the ProductID.
    • o.CustomerID: Refers back to the buyer who positioned the order.
    • p.Price * o.OrderQuantity AS order_total: Calculates the entire price of every order by multiplying the product’s price by the amount ordered.
• GROUP BY Clause:
  • GROUP BY CustomerID: Teams the outcomes by buyer, permitting the question to calculate the entire quantity spent by every particular person buyer.
• Ordering with ORDER BY Clause:
  • ORDER BY total_spent DESC: The outcomes are sorted in descending order by the entire quantity spent, so the shoppers who’ve spent probably the most seem 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 question retrieves an inventory of merchandise that haven’t been ordered by any clients.

It makes use of a LEFT JOIN to mix the CustomerProductTabl desk (aliased as p) with a subquery that identifies all merchandise which have been ordered, guaranteeing that solely merchandise with out orders are included within the outcome.

• Chosen Columns:
  • ProductID and ProductDescription are chosen from the CustomerProductTabl desk to offer the ID and outline of every product.
• Subquery (ordered_products):
  • The subquery selects distinct ProductID values from the CustomerOrdersTabl desk, figuring out merchandise which have been ordered at the least as soon as.
  • SELECT DISTINCT ProductID FROM CustomerTab.CustomerOrdersTabl: This ensures that every product ID seems solely as soon as, no matter what number of instances it has been ordered.
• LEFT JOIN Operation:
  • The LEFT JOIN is used to affix the CustomerProductTabl desk with the ordered_products subquery. This be part of contains all merchandise from the CustomerProductTabl desk, even when they don’t have an identical entry within the ordered_products subquery.
  • ON p.ProductID = ordered_products.ProductID: This situation matches merchandise from the product desk with the ordered merchandise subquery based mostly on ProductID.
• Filtering with WHERE Clause:
  • WHERE ordered_products.ProductID IS NULL: This situation filters the outcomes to incorporate solely these merchandise that would not have a corresponding ProductID within the ordered_products subquery. These are merchandise that haven’t been ordered.

SELECT
CustomerID,
AVG(order_total) AS average_order_cost
FROM (
SELECT
o.CustomerID,
p.Price * 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 question calculates the typical order price for every buyer by becoming a member of the CustomerOrdersTabl desk (aliased as o) with the CustomerProductTabl desk (aliased as p).

It solely contains clients whose common order price exceeds £50. The question makes use of a subquery to compute the entire price of every order earlier than calculating the typical order price per buyer.

• Important Question:
  • CustomerID is chosen to determine every buyer.
  • AVG(order_total) AS average_order_cost calculates the typical whole price of orders positioned by every buyer. The result’s labelled as average_order_cost.
• Subquery (customer_orders):
  • The subquery calculates the entire price of every order by multiplying the Price of the product from the CustomerProductTabl desk by the OrderQuantity from the CustomerOrdersTabl desk.
  • JOIN clause: This be part of hyperlinks every order to its corresponding product utilizing the ProductID.
    • o.CustomerID: Refers back to the buyer who positioned the order.
    • p.Price * o.OrderQuantity AS order_total: Calculates the entire price of every order by multiplying the product’s price by the amount ordered.
• GROUP BY Clause:
  • GROUP BY CustomerID: Teams the outcomes by buyer, permitting the question to calculate the typical order price for every particular person buyer.
• Filtering with HAVING Clause:
  • HAVING AVG(order_total) > 50: This situation filters the outcomes to incorporate solely these clients whose common order price is larger than £50.

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

This SQL question calculates the typical wage and counts the variety of workers employed in the identical 12 months as the worker with the best wage.

It includes a number of ranges of subqueries to find out the related 12 months after which makes use of this data to filter the workers accordingly.

• Important Question:
  • AVG(EP.Wage) AS AverageSalary: Calculates the typical wage of workers employed in a particular 12 months, labelling the outcome as AverageSalary.
  • COUNT(*) AS EmployeeCount: Counts the entire variety of workers employed in that very same 12 months, labelling the outcome as EmployeeCount.
• Filtering with WHERE Clause:
  • WHERE YEAR(EP.HireDate) = (...): This situation filters the principle question to incorporate solely these workers employed in a selected 12 months, which is set by a subquery.
• Subquery Construction:
  • The primary subquery SELECT YEAR(HireDate) FROM Worker.Pay WHERE Wage = (...) identifies the 12 months by which the worker with the best wage was employed.
    • The innermost subquery SELECT MAX(Wage) FROM Worker.Pay finds the utmost wage within the Worker.Pay desk.
    • The outer subquery then finds the HireDate of the worker with this most wage and extracts the 12 months.

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

This SQL question retrieves particulars of workers who’ve been employed for at the least 5 years and earn a wage under the typical for his or her particular job title.

It combines information from the Worker.Worker desk (aliased as E) and the Worker.Pay desk (aliased as EP) utilizing an INNER JOIN.

The question makes use of subqueries to check every worker’s wage in opposition to the typical wage for his or her job title.

• Chosen Columns:
  • EmployeeID, FirstName, and LastName are chosen from the Worker desk to offer identification and private particulars for every worker.
  • JobTitle and Wage are chosen from the Pay desk to offer job-related particulars and wage data.
• INNER JOIN Operation:
  • The INNER JOIN hyperlinks the Worker and Pay tables utilizing the EmployeeID, guaranteeing that solely workers with matching IDs in each tables are included within the outcomes.
• Filtering with WHERE Clause:
  • EP.HireDate <= DATEADD(YEAR, -5, GETDATE()): This situation filters the outcomes to incorporate solely workers who have been employed at the least 5 years in the past. The DATEADD(YEAR, -5, GETDATE()) perform calculates the date 5 years earlier than the present date.
  • AND EP.Wage < (...): This situation filters the outcomes to incorporate solely these workers whose wage is under the typical wage for his or her job title.
    • The subquery SELECT AVG(EP2.Wage) FROM Worker.Pay AS EP2 WHERE EP2.JobTitle = EP.JobTitle calculates the typical wage for every job title. It compares the worker’s wage in opposition to this common, filtering out those that earn lower than the typical.

SELECT
c.CustomerName,
c.Handle
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 question retrieves the names and addresses of the highest 10 clients who’ve positioned the biggest whole order portions.

It makes use of a subquery to determine these high clients based mostly on their order portions and filters the principle question to incorporate solely these clients.

• Chosen Columns:
  • CustomerName and Handle are chosen from the CustomerTabl desk to offer the names and addresses of the highest clients.
• Filtering with WHERE Clause:
  • WHERE c.CustomerID IN (...): This situation filters the principle question to incorporate solely these clients whose IDs are within the listing returned by the subquery.
• Subquery Construction:
  • The subquery SELECT TOP 10 o.CustomerID FROM CustomerTab.CustomerOrdersTabl o GROUP BY o.CustomerID ORDER BY SUM(o.OrderQuantity) DESC identifies the highest 10 clients based mostly on the entire amount of things they’ve ordered.
    • GROUP BY o.CustomerID: Teams the orders by buyer, permitting the question to combination order portions.
    • ORDER BY SUM(o.OrderQuantity) DESC: Orders the outcomes by the entire order amount in descending order, so clients with the best order portions seem first.
    • SELECT TOP 10: Limits the outcome to the highest 10 clients.

SELECT
ProductID,
ProductDescription,
Price,
'High-Promoting' AS SalesCategory
FROM
CustomerTab.CustomerProductTabl
WHERE
ProductID IN (
SELECT TOP 5
ProductID
FROM
CustomerTab.CustomerProductTabl
ORDER BY
Price DESC
)
UNION
-- Question to retrieve least-selling merchandise
SELECT
ProductID,
ProductDescription,
Price,
'Least-Promoting' AS SalesCategory
FROM
CustomerTab.CustomerProductTabl
WHERE
ProductID IN (
SELECT TOP 5
ProductID
FROM
CustomerTab.CustomerProductTabl
ORDER BY
Price ASC
);

This SQL question retrieves the highest 5 most costly and high 5 least costly merchandise from the CustomerTab.CustomerProductTabl desk and categorises them as both ‘High-Promoting’ or ‘Least-Promoting’ based mostly on their price.

The question makes use of two separate subqueries to determine these merchandise and combines the outcomes utilizing a UNION.

• Chosen Columns:
  • ProductID, ProductDescription, and Price are chosen from the CustomerProductTabl desk to offer particulars about every product.
  • 'High-Promoting' AS SalesCategory and 'Least-Promoting' AS SalesCategory are hardcoded string values used to label the merchandise as ‘High-Promoting’ or ‘Least-Promoting’ based mostly on the subquery they’re a part of.
• Subquery for High-Promoting Merchandise:
  • SELECT TOP 5 ProductID FROM CustomerTab.CustomerProductTabl ORDER BY Price DESC: This subquery selects the highest 5 most costly merchandise.
    • ORDER BY Price DESC: Orders the merchandise by price in descending order to determine the costliest merchandise.
• Subquery for Least-Promoting Merchandise:
  • SELECT TOP 5 ProductID FROM CustomerTab.CustomerProductTabl ORDER BY Price ASC: This subquery selects the highest 5 least costly merchandise.
    • ORDER BY Price ASC: Orders the merchandise by price in ascending order to determine the least costly merchandise.
• UNION Operator:
  • The UNION operator combines the outcomes of the 2 queries right into a single outcome set. It ensures that the mixed outcomes don’t embrace duplicate rows, however since every product is assigned a novel SalesCategory, duplicates are unlikely.

SELECT
ProductID,
ProductDescription AS Identify,
NULL AS Handle
FROM
CustomerTab.CustomerProductTabl
UNION
-- Question to retrieve clients from the required metropolis
SELECT
CustomerID,
CustomerName AS Identify,
Metropolis AS Location
FROM
CustomerTab.CustomerTabl

This SQL question combines details about merchandise and clients right into a single outcome set utilizing the UNION operator.

It selects product particulars from the CustomerProductTabl desk and buyer particulars from the CustomerTabl desk, guaranteeing the outcomes share the identical construction through the use of aliases and placeholder values.

• First Question (Product Data):
  • ProductID is chosen to determine every product uniquely.
  • ProductDescription AS Identify renames the ProductDescription column to Identify to match the construction of the client question.
  • NULL AS Handle: This column is included to match the construction of the second question. Since merchandise would not have an tackle, NULL is used as a placeholder.
• Second Question (Buyer Data):
  • CustomerID is chosen to determine every buyer uniquely.
  • CustomerName AS Identify renames the CustomerName column to Identify to match the construction of the product question.
  • Metropolis AS Location: This column gives the placement of every buyer, serving the same function to the NULL placeholder within the first question, however right here it contains precise information.
• UNION Operator:
  • The UNION operator combines the outcomes of the 2 queries right into a single outcome set. It removes duplicate rows, if any, guaranteeing that every distinctive row is displayed solely as soon as.

SELECT
c.CustomerID,
c.CustomerName,
c.Handle,
c.Metropolis
FROM
CustomerTab.CustomerTabl c
EXCEPT
-- Question to retrieve clients who've positioned orders
SELECT
c.CustomerID,
c.CustomerName,
c.Handle,
c.Metropolis
FROM
CustomerTab.CustomerTabl c
JOIN
CustomerTab.CustomerOrdersTabl o
ON
c.CustomerID = o.CustomerID;

This SQL question retrieves an inventory of consumers who haven’t positioned any orders.

It makes use of the EXCEPT operator to search out the distinction between two outcome units: the primary set containing all clients, and the second set containing solely these clients who’ve positioned orders.

The EXCEPT operator ensures that solely clients with out orders are returned.

• First Question (All Clients):
  • CustomerID, CustomerName, Handle, and Metropolis are chosen from the CustomerTabl desk to offer particulars about all clients.
• Second Question (Clients Who Have Positioned Orders):
  • CustomerID, CustomerName, Handle, and Metropolis are chosen from the CustomerTabl desk joined with the CustomerOrdersTabl desk.
  • JOIN CustomerTab.CustomerOrdersTabl o ON c.CustomerID = o.CustomerID: This JOIN hyperlinks the CustomerTabl desk to the CustomerOrdersTabl desk utilizing the CustomerID. This ensures that solely clients who’ve positioned orders are included on this outcome set.
• EXCEPT Operator:
  • The EXCEPT operator returns solely the rows from the primary question that aren’t current within the second question. This successfully filters out clients who’ve positioned orders, leaving solely those that haven’t.

SELECT
p.ProductID,
p.ProductDescription,
p.Price
FROM
CustomerTab.CustomerProductTabl p
EXCEPT
-- Question to retrieve merchandise which have been ordered
SELECT
p.ProductID,
p.ProductDescription,
p.Price
FROM
CustomerTab.CustomerProductTabl p
JOIN
CustomerTab.CustomerOrdersTabl o
ON
p.ProductID = o.ProductID;

This SQL question retrieves an inventory of merchandise which have by no means been ordered.

It makes use of the EXCEPT operator to search out the distinction between two outcome units: the primary set containing all merchandise, and the second set containing solely these merchandise which have been ordered.

The EXCEPT operator ensures that solely merchandise with none orders are returned.

• First Question (All Merchandise):
  • ProductID, ProductDescription, and Price are chosen from the CustomerProductTabl desk to offer particulars about all merchandise obtainable.
• Second Question (Merchandise That Have Been Ordered):
  • ProductID, ProductDescription, and Price are chosen from the CustomerProductTabl desk joined with the CustomerOrdersTabl desk.
  • JOIN CustomerTab.CustomerOrdersTabl o ON p.ProductID = o.ProductID: This JOIN hyperlinks the CustomerProductTabl desk to the CustomerOrdersTabl desk utilizing the ProductID. This ensures that solely merchandise which have been ordered are included on this outcome set.
• EXCEPT Operator:
  • The EXCEPT operator returns solely the rows from the primary question that aren’t current within the second question. This successfully filters out merchandise which have been ordered, leaving solely people who haven’t.

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

This SQL question calculates the entire wage for every worker and for every job title, utilizing the ROLLUP characteristic to offer subtotals and a grand whole.

The GROUP BY ROLLUP clause is used to group the outcomes by JobTitle and EmployeeID, producing abstract rows at totally different ranges of aggregation.

• Chosen Columns:
  • JobTitle is chosen to group the outcomes by the job titles of workers.
  • EmployeeID is chosen to offer particular wage particulars for every worker inside a job title.
  • SUM(Wage) AS TotalSalary calculates the entire wage for every group, whether or not it’s per worker, per job title, or the grand whole. The result’s labelled as TotalSalary.
• GROUP BY ROLLUP Clause:
  • The ROLLUP (JobTitle, EmployeeID) characteristic is used to generate grouped outcomes with subtotals and a grand whole:
    • Per Worker: It first teams the outcomes by every EmployeeID inside every JobTitle.
    • Per Job Title: It then gives a subtotal for every JobTitle, exhibiting the entire wage of all workers with that title.
    • Grand Whole: Lastly, it gives a grand whole of salaries for all workers, no matter their job titles.

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

This SQL question calculates the entire variety of orders and the entire amount of merchandise ordered by every buyer, utilizing the ROLLUP characteristic to offer subtotals for every buyer and a grand whole.

The GROUP BY ROLLUP clause is used to group the outcomes by CustomerID and ProductID, producing abstract rows at totally different ranges of aggregation.

• Chosen Columns:
  • CustomerID is chosen to group the outcomes by every buyer, figuring out which buyer positioned the orders.
  • ProductID is chosen to group the outcomes by every product, figuring out which product was ordered.
  • COUNT(*) AS TotalOrders calculates the entire variety of orders positioned by every buyer for every product. The result’s labelled as TotalOrders.
  • SUM(OrderQuantity) AS TotalQuantity calculates the entire amount of merchandise ordered by every buyer for every product. The result’s labelled as TotalQuantity.
• GROUP BY ROLLUP Clause:
  • The ROLLUP (CustomerID, ProductID) characteristic is used to generate grouped outcomes with subtotals and a grand whole:
    • Per Buyer and Product: It first teams the outcomes by every ProductID inside every CustomerID.
    • Per Buyer: It then gives a subtotal for every CustomerID, exhibiting the entire orders and amount for all merchandise ordered by that buyer.
    • Grand Whole: Lastly, it gives a grand whole for all clients and all merchandise, exhibiting the general whole orders and portions.

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 question calculates the entire variety of orders and the entire amount of merchandise ordered by every buyer for every product, damaged down by the month by which the order was positioned.

The question makes use of the ROLLUP characteristic to offer subtotals by buyer, product, and month, in addition to a grand whole.

The GROUP BY ROLLUP clause is used to generate grouped outcomes at totally different ranges of aggregation.

• Chosen Columns:
  • CustomerID is chosen to determine every buyer.
  • ProductID is chosen to determine every product ordered.
  • MONTH(OrderDate) AS OrderMonth extracts the month from the OrderDate to indicate when the orders have been positioned. The result’s labelled as OrderMonth.
  • COUNT(*) AS TotalOrders calculates the entire variety of orders positioned by every buyer for every product in every month. The result’s labelled as TotalOrders.
  • SUM(OrderQuantity) AS TotalQuantity calculates the entire amount of merchandise ordered by every buyer for every product in every month. The result’s labelled as TotalQuantity.
• GROUP BY ROLLUP Clause:
  • The ROLLUP (CustomerID, ProductID, MONTH(OrderDate)) characteristic generates grouped outcomes with subtotals and a grand whole:
    • Per Buyer, Product, and Month: It first teams the outcomes by every ProductID and OrderMonth inside every CustomerID.
    • Per Buyer and Product: It gives subtotals for every CustomerID and ProductID, exhibiting whole orders and amount for all months.
    • Per Buyer: It gives subtotals for every CustomerID, exhibiting whole orders and amount for all merchandise and months.
    • Grand Whole: Lastly, it gives a grand whole for all clients, merchandise, and months, exhibiting general whole orders and portions.

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

This SQL question calculates the entire wage paid to workers for every job title and gives a grand whole for all job titles utilizing the ROLLUP characteristic.

The GROUP BY ROLLUP clause teams the outcomes by JobTitle, producing abstract rows at totally different ranges of aggregation.

• Chosen Columns:
  • JobTitle is chosen to determine every job title throughout the organisation.
  • SUM(Wage) AS TotalSalary calculates the entire wage paid to all workers with a selected job title. The result’s labelled as TotalSalary.
• GROUP BY ROLLUP Clause:
  • The ROLLUP (JobTitle) characteristic generates grouped outcomes with subtotals and a grand whole:
    • Per Job Title: It teams the outcomes by every JobTitle, offering the entire wage for every job title.
    • Grand Whole: It gives a grand whole of salaries for all job titles mixed. This grand whole row could have a NULL worth within the JobTitle column, representing the general sum of all salaries.

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

This SQL question calculates the entire wage bills by job title, metropolis, and rent 12 months, utilizing the CUBE characteristic to offer a number of ranges of aggregation.

The question joins the Worker.Pay desk with the Worker.Worker desk to mix wage information with job title and site data.

The CUBE operator generates subtotals for each mixture of job title, metropolis, and rent 12 months, together with higher-level summaries.

• Chosen Columns:
  • JobTitle AS Job_Title renames the JobTitle column to Job_Title to offer a transparent and constant format.
  • Metropolis AS Metropolis selects town the place every worker works.
  • YEAR(HireDate) AS Hire_Year extracts the 12 months from the HireDate to indicate when every worker was employed.
  • SUM(Wage) AS Total_Compensation_Expense calculates the entire wage expense for every mixture of job title, metropolis, and rent 12 months. The result’s labelled as Total_Compensation_Expense.
• JOIN Operation:
  • JOIN Worker.Worker ON Worker.Pay.EmployeeID = Worker.Worker.EmployeeID: This be part of combines the Pay and Worker tables utilizing the EmployeeID, guaranteeing that wage information is matched with the corresponding job title, metropolis, and rent date.
• GROUP BY CUBE Clause:
  • The CUBE (JobTitle, Metropolis, YEAR(HireDate)) characteristic generates grouped outcomes with subtotals and a grand whole:
    • Per Job Title, Metropolis, and Rent Yr: It teams the outcomes by each mixture of JobTitle, Metropolis, and Hire_Year.
    • Subtotals: It gives subtotals for every JobTitle throughout all cities and years, every Metropolis throughout all job titles and years, and every Hire_Year throughout all job titles and cities.
    • Grand Whole: It gives a grand whole for all job titles, cities, and rent years mixed.
• Ordering with ORDER BY Clause:
  • The outcomes are ordered by Job_Title, Metropolis, and Hire_Year, offering a structured view of the wage bills.

SELECT
p.ProductID AS Product,
c.Metropolis AS Metropolis,
YEAR(o.OrderDate) AS Order_Year,
SUM(o.OrderQuantity * p.Price) AS "Income"
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.Metropolis, YEAR(o.OrderDate))
ORDER BY
Product, Metropolis, Order_Year;

This SQL question calculates the entire income generated by every product, in every metropolis, and for every year, utilizing the CUBE characteristic to offer varied ranges of aggregation.

The question joins the CustomerOrdersTabl, CustomerTabl, and CustomerProductTabl tables to mix order information with buyer and product particulars.

The CUBE operator generates subtotals for each mixture of product, metropolis, and 12 months, in addition to higher-level summaries.

• Chosen Columns:
  • p.ProductID AS Product renames the ProductID column to Product to obviously point out that this subject represents the product being analysed.
  • c.Metropolis AS Metropolis selects town the place the client positioned the order.
  • YEAR(o.OrderDate) AS Order_Year extracts the 12 months from the OrderDate to indicate when the order was positioned.
  • SUM(o.OrderQuantity * p.Price) AS "Income" calculates the entire income by multiplying the order amount by the product price. The result’s labelled as "Income".
• JOIN Operations:
  • JOIN CustomerTab.CustomerTabl c ON o.CustomerID = c.CustomerID: This be part of hyperlinks the orders with buyer particulars utilizing the CustomerID, guaranteeing that every order is related to the proper buyer.
  • JOIN CustomerTab.CustomerProductTabl p ON o.ProductID = p.ProductID: This be part of hyperlinks the orders with product particulars utilizing the ProductID, guaranteeing that every order is related to the proper product.
• GROUP BY CUBE Clause:
  • The CUBE (p.ProductID, c.Metropolis, YEAR(o.OrderDate)) characteristic generates grouped outcomes with subtotals and a grand whole:
    • Per Product, Metropolis, and Yr: It teams the outcomes by each mixture of ProductID, Metropolis, and Order_Year.
    • Subtotals: It gives subtotals for every ProductID throughout all cities and years, every Metropolis throughout all merchandise and years, and every Order_Year throughout all merchandise and cities.
    • Grand Whole: It gives a grand whole for all merchandise, cities, and years mixed.
• Ordering with ORDER BY Clause:
  • The outcomes are ordered by Product, Metropolis, and Order_Year, offering a structured and organised view of the income information.

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

This SQL question categorises clients based mostly on the entire quantity they’ve spent and calculates the entire quantity spent by every buyer.

Clients are labelled as both ‘Loyal Buyer’ or ‘Common Buyer’ relying on their whole spending.

The question makes use of a JOIN to mix order and product information, then teams the outcomes by CustomerID and filters out clients who’ve spent lower than £200 utilizing the HAVING clause.

• Chosen Columns:
  • CustomerID is chosen to determine every buyer uniquely.
  • CASE WHEN ... THEN ... ELSE ... END AS CustomerStatus: This CASE assertion categorises clients based mostly on their spending:
    • If the entire quantity spent (SUM(OrderQuantity * Price)) is larger than £500, the client is labelled as ‘Loyal Buyer’.
    • In any other case, the client is labelled as ‘Common Buyer’.
  • SUM(OrderQuantity * Price) AS TotalAmountSpent calculates the entire quantity spent by every buyer by multiplying the order amount by the product price. The result’s labelled as TotalAmountSpent.
• JOIN Operation:
  • JOIN CustomerTab.CustomerProductTabl ON CustomerTab.CustomerOrdersTabl.ProductID = CustomerTab.CustomerProductTabl.ProductID: This be part of hyperlinks the orders to the product particulars utilizing the ProductID, guaranteeing that the proper price is utilized to every order.
• GROUP BY Clause:
  • GROUP BY CustomerID: Teams the outcomes by buyer, permitting the question to calculate the entire quantity spent for every buyer.
• Filtering with HAVING Clause:
  • HAVING SUM(OrderQuantity * Price) > 200: This situation filters the outcomes to incorporate solely these clients who’ve spent greater than £200 in whole.

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

This SQL question gives detailed analytics about merchandise based mostly on their gross sales, demand, and revenue margin. It categorises merchandise utilizing a number of CASE statements and calculates varied metrics to present insights into product efficiency.

The question makes use of a JOIN to mix order information with product particulars and applies a GROUP BY clause to combination outcomes by product description and price.

• Chosen Columns and Calculations:
  • ProductDescription: Describes the product.
  • Price: The unit price of the product.
  • SUM(OrderQuantity * Price) AS TotalRevenue: Calculates whole income by multiplying order amount by the associated fee.
  • SUM(OrderQuantity) AS TotalQuantitySold: Calculates the entire amount of the product bought.
  • ((SUM(OrderQuantity * Price) - COUNT(OrderID) * Price) / SUM(OrderQuantity * Price)) * 100 AS ProfitMargin: Calculates the revenue margin as a proportion.
  • CASE statements:
    • SalesCategory: Categorises merchandise as ‘Excessive Gross sales’ if whole income is over £500, in any other case ‘Low Gross sales’.
    • DemandCategory: Categorises merchandise as ‘Excessive Demand’ if greater than 100 models are bought, in any other case ‘Low Demand’.
    • ProfitMarginCategory: Categorises merchandise as ‘Excessive Revenue Margin’ if the revenue margin is 30% or greater, in any other case ‘Low Revenue Margin’.
    • SoldStatus: Categorises merchandise as ‘Not Bought’ if no models are bought, in any other case ‘Bought’.
• JOIN Operation:
  • JOIN CustomerTab.CustomerProductTabl ON CustomerTab.CustomerOrdersTabl.ProductID = CustomerTab.CustomerProductTabl.ProductID: This be part of hyperlinks the orders to the corresponding product particulars utilizing the ProductID.
• GROUP BY Clause:
  • GROUP BY ProductDescription, Price: Teams the outcomes by product description and price, permitting the question to calculate aggregated metrics for every product.
• Filtering with HAVING Clause:
  • The HAVING clause filters merchandise based mostly on a number of situations to spotlight vital merchandise:
    • Whole income over £500.
    • Greater than 100 models bought.
    • Revenue margin 30% or greater.
    • Merchandise that haven’t been bought.