Alternative Answers in MySQL sytnax to the Intermediate and Advanced Problems of SQL Practice Problems
If your want the explanations or details of the answers, please feel free to contact me by email
25. High freight charges
SELECT shipcountry,
Avg(freight) AS AverageFreight
FROM northwind.orders
GROUP BY shipcountry
ORDER BY averagefreight DESC
LIMIT 3;
26. High freight charges - 2015
SELECT shipcountry,
Avg(freight) AS AverageFreight
FROM northwind.orders
WHERE Year(orderdate) = 2015
GROUP BY shipcountry
ORDER BY averagefreight DESC
LIMIT 3;
28. High freight charges - last year
SELECT shipcountry,
Avg(freight) AS AverageFreight
FROM northwind.orders
WHERE orderdate BETWEEN (SELECT Date_sub(Max(orderdate), INTERVAL 12 month)
FROM orders) AND (SELECT Max(orderdate)
FROM orders)
GROUP BY shipcountry
ORDER BY averagefreight DESC
LIMIT 3;
32. High-value customers
SELECT O.customerid,
C.companyname,
O.orderid,
Sum(OD.unitprice * OD.quantity) AS TotalOrderAmount
FROM northwind.orders AS O,
northwind.orderdetails AS OD,
northwind.customers AS C
WHERE Year(O.orderdate) = 2016
AND O.orderid = OD.orderid
AND O.customerid = C.customerid
GROUP BY O.customerid,
O.orderid
HAVING Sum(OD.unitprice * OD.quantity) >= 10000
ORDER BY totalorderamount DESC;
33. High-value customers - total orders
SELECT O.customerid,
C.companyname,
Sum(OD.unitprice * OD.quantity) AS TotalOrderAmount
FROM northwind.orders AS O,
northwind.orderdetails AS OD,
northwind.customers AS C
WHERE Year(O.orderdate) = 2016
AND O.orderid = OD.orderid
AND O.customerid = C.customerid
GROUP BY O.customerid
HAVING Sum(OD.unitprice * OD.quantity) >= 15000
ORDER BY totalorderamount DESC;
34. High-value customers - with discount
SELECT O.customerid,
C.companyname,
Sum(OD.unitprice * OD.quantity) AS
TotalsWithoutDiscount,
Sum(OD.unitprice * ( 1 - OD.discount ) * OD.quantity) AS
TotalsWithDiscount
FROM northwind.orders AS O,
northwind.orderdetails AS OD,
northwind.customers AS C
WHERE Year(O.orderdate) = 2016
AND O.orderid = OD.orderid
AND O.customerid = C.customerid
GROUP BY O.customerid
HAVING Sum(OD.unitprice * ( 1 - OD.discount ) * OD.quantity) >= 10000
ORDER BY totalswithdiscount DESC;
35. Month-end orders
SELECT employeeid,
orderid,
orderdate
FROM northwind.orders
WHERE Date(orderdate) IN (SELECT DISTINCT ( Last_day(Date(orderdate)) )
FROM northwind.orders)
ORDER BY employeeid,
orderid;
36. Orders with many line items
SELECT orderid,
Count(*) AS TotalOrderDetails
FROM northwind.orderdetails
GROUP BY orderid
ORDER BY totalorderdetails DESC
LIMIT 10;
37. Orders - random assortment
SET @a:= (SELECT round(count(distinct OrderID)*0.02) FROM northwind.orderdetails);
PREPARE STMT FROM 'SELECT OrderID FROM northwind.orderdetails ORDER BY RAND() LIMIT ?';
EXECUTE STMT USING @a;
38. Orders - accidental double-entry
SELECT orderid
FROM northwind.orderdetails
WHERE quantity >= 60
GROUP BY orderid,
quantity
HAVING Count(*) > 1;
39. Orders - accidental double-entry details
SELECT *
FROM northwind.orderdetails
WHERE orderid IN (SELECT DISTINCT orderid
FROM northwind.orderdetails
WHERE quantity >= 60
GROUP BY orderid,
quantity
HAVING Count(*) > 1);
40. Orders - accidental double-entry details, derived table
```Some OrderID are duplicated, use DISTINCT to select the unique OrderID```
SELECT orderdetails.orderid,
productid,
unitprice,
quantity,
discount
FROM orderdetails
JOIN (SELECT DISTINCT orderid
FROM orderdetails
WHERE quantity >= 60
GROUP BY orderid,
quantity
HAVING Count(*) > 1) PotentialProblemOrders
ON PotentialProblemOrders.orderid = orderdetails.orderid
ORDER BY orderid, productid;
41. Late orders
SELECT orderid,
Date(orderdate) AS OrderDate,
Date(requireddate) AS RequiredDate,
Date(shippeddate) AS ShippedDate
FROM northwind.orders
WHERE Date(shippeddate) >= Date(requireddate);
42. Late orders - which employees?
SELECT employees.employeeid,
employees.lastname,
Count(*) AS TotalLateOrders
FROM northwind.orders
JOIN employees
ON employees.employeeid = orders.employeeid
WHERE Date(shippeddate) >= Date(requireddate)
GROUP BY employees.employeeid,
employees.lastname
ORDER BY totallateorders DESC;
43. Late orders vs. total orders
SELECT A.employeeid,
lastname,
allorders,
lateorders
FROM (SELECT employeeid,
Count(*) AS AllOrders
FROM northwind.orders
GROUP BY employeeid) A,
(SELECT employees.employeeid,
employees.lastname,
Count(*) AS LateOrders
FROM northwind.orders
JOIN employees
ON employees.employeeid = orders.employeeid
WHERE Date(shippeddate) >= Date(requireddate)
GROUP BY employees.employeeid,
employees.lastname) B
WHERE A.employeeid = B.employeeid;
44. Late orders vs. total orders - missing employee
SELECT A.employeeid,
A.lastname,
allorders,
lateorders
FROM (SELECT employees.employeeid,
lastname,
Count(*) AS AllOrders
FROM northwind.orders
JOIN employees
ON employees.employeeid = orders.employeeid
GROUP BY employees.employeeid,
lastname) A
LEFT JOIN (SELECT employeeid,
Count(*) AS LateOrders
FROM northwind.orders
WHERE Date(shippeddate) >= Date(requireddate)
GROUP BY employeeid) B
ON A.employeeid = B.employeeid
ORDER BY employeeid;
45. Late orders vs. total orders - fix null
SELECT A.employeeid,
A.lastname,
allorders,
IF(lateorders IS NULL, 0, lateorders) AS LateOrders
FROM (SELECT employees.employeeid,
lastname,
Count(*) AS AllOrders
FROM northwind.orders
JOIN employees
ON employees.employeeid = orders.employeeid
GROUP BY employees.employeeid,
lastname) A
LEFT JOIN (SELECT employeeid,
Count(*) AS LateOrders
FROM northwind.orders
WHERE Date(shippeddate) >= Date(requireddate)
GROUP BY employeeid) B
ON A.employeeid = B.employeeid
ORDER BY employeeid;
46. Late orders vs. total orders - percentage
SELECT A.employeeid,
A.lastname,
allorders,
IF(lateorders IS NULL, 0, lateorders) AS LateOrders,
IF(lateorders IS NULL, 0, lateorders / allorders) AS PercentLateOrders
FROM (SELECT employees.employeeid,
lastname,
Count(*) AS AllOrders
FROM northwind.orders
JOIN employees
ON employees.employeeid = orders.employeeid
GROUP BY employees.employeeid,
lastname) A
LEFT JOIN (SELECT employeeid,
Count(*) AS LateOrders
FROM northwind.orders
WHERE Date(shippeddate) >= Date(requireddate)
GROUP BY employeeid) B
ON A.employeeid = B.employeeid
ORDER BY employeeid;
47. Late orders vs. total orders - fix decimal
SELECT A.employeeid,
A.lastname,
allorders,
IF(lateorders IS NULL, 0, lateorders) AS LateOrders
,
IF(lateorders IS NULL, 0, Round(lateorders / allorders, 2)) AS
PercentLateOrders
FROM (SELECT employees.employeeid,
lastname,
Count(*) AS AllOrders
FROM northwind.orders
JOIN employees
ON employees.employeeid = orders.employeeid
GROUP BY employees.employeeid,
lastname) A
LEFT JOIN (SELECT employeeid,
Count(*) AS LateOrders
FROM northwind.orders
WHERE Date(shippeddate) >= Date(requireddate)
GROUP BY employeeid) B
ON A.employeeid = B.employeeid
ORDER BY employeeid;
48. Customer grouping
SELECT orders.customerid,
companyname,
Round(Sum(quantity * unitprice), 2) AS TotalOrderAmount,
CASE
WHEN Sum(quantity * unitprice) BETWEEN 0 AND 1000 THEN "low"
WHEN Sum(quantity * unitprice) BETWEEN 1001 AND 5000 THEN "medium"
WHEN Sum(quantity * unitprice) BETWEEN 5001 AND 10000 THEN "high"
WHEN Sum(quantity * unitprice) > 10000 THEN "very high"
end AS CustomerGroup
FROM orders,
orderdetails,
customers
WHERE orders.orderid = orderdetails.orderid
AND orders.customerid = customers.customerid
AND Year(orderdate) = 2016
GROUP BY customerid
ORDER BY customerid;
49. Customer grouping - fix null
SELECT orders.customerid,
companyname,
Round(Sum(quantity * unitprice), 2) AS TotalOrderAmount,
CASE
WHEN Sum(quantity * unitprice) >= 0
AND Sum(quantity * unitprice) < 1000 THEN "low"
WHEN Sum(quantity * unitprice) >= 1000
AND Sum(quantity * unitprice) < 5000 THEN "medium"
WHEN Sum(quantity * unitprice) >= 5000
AND Sum(quantity * unitprice) < 10000 THEN "high"
WHEN Sum(quantity * unitprice) >= 10000 THEN "very high"
end AS CustomerGroup
FROM orders,
orderdetails,
customers
WHERE orders.orderid = orderdetails.orderid
AND orders.customerid = customers.customerid
AND Year(orderdate) = 2016
GROUP BY customerid
ORDER BY customerid;
50. Customer grouping with percentage
SELECT customergroup,
Count(customergroup) AS TotalInGroup,
Count(customergroup) / (SELECT Count(DISTINCT customerid)
FROM orders
WHERE Year(orderdate) = 2016) AS PercentageInGroup
FROM (SELECT orders.customerid,
CASE
WHEN Sum(quantity * unitprice) BETWEEN 0 AND 1000 THEN "low"
WHEN Sum(quantity * unitprice) BETWEEN 1000 AND 5000 THEN "medium"
WHEN Sum(quantity * unitprice) BETWEEN 5000 AND 10000 THEN "high"
WHEN Sum(quantity * unitprice) > 10000 THEN "very high"
end AS CustomerGroup
FROM orders,
orderdetails
WHERE orders.orderid = orderdetails.orderid
AND Year(orderdate) = 2016
GROUP BY customerid) T
GROUP BY customergroup
ORDER BY totalingroup DESC;
51. Customer grouping - flexible
52. Countries with suppliers or customers
SELECT DISTINCT country
FROM suppliers
UNION
SELECT DISTINCT country
FROM customers
ORDER BY country;
53. Countries with suppliers or customers, version 2
SELECT DISTINCT country AS SupplierCountry,
customercountry
FROM suppliers S
LEFT JOIN (SELECT DISTINCT country AS CustomerCountry
FROM customers) C
ON S.country = C.customercountry
UNION
SELECT DISTINCT country AS SupplierCountry,
customercountry
FROM suppliers S
RIGHT JOIN (SELECT DISTINCT country AS CustomerCountry
FROM customers) C
ON S.country = C.customercountry;
54. Countries with suppliers or customers - version 3
SELECT T1.country,
totalsuppliers,
totalcustomers
FROM (SELECT T.country,
Count(S.country) AS TotalSuppliers
FROM suppliers S
RIGHT JOIN (SELECT DISTINCT country
FROM suppliers
UNION
SELECT DISTINCT country
FROM customers) T
ON S.country = T.country
GROUP BY T.country) T1,
(SELECT T.country,
Count(C.country) AS TotalCustomers
FROM customers C
RIGHT JOIN (SELECT DISTINCT country
FROM suppliers
UNION
SELECT DISTINCT country
FROM customers) T
ON C.country = T.country
GROUP BY T.country) T2
WHERE T1.country = T2.country
ORDER BY T1.country;
55. First order in each country
56. Customers with multiple orders in 5 day period
57. Customers with multiple orders in 5 day period, version 2