Exercises 2

Sistem Basis Data

Email             : putriangelina865@gmail.com
Instagram     : https://www.instagram.com/putriangelinaw
RPubs            : https://rpubs.com/putriangelinaw/

---

1 List all orders with customer information!

SELECT OrderDate, c.*
FROM orders o
JOIN customers c
ON o.CustomerID=c.CustomerID
ORDER BY OrderDate;

2 List all orders with product names, quantities, and prices!

SELECT o.OrderDate, p.ProductName, od.Quantity, p.Price
FROM orders o
JOIN orderdetails od
ON o.OrderID=od.OrderID
JOIN products p
ON od.ProductID=p.ProductID
ORDER BY o.OrderDate;

3 This will list all customers, whether they placed any order or not!

SELECT DISTINCT CustomerID, CustomerName
FROM customers
ORDER BY CustomerID;

4 List customers that have not placed orders!

SELECT DISTINCT CustomerID, CustomerName
FROM customers
WHERE CustomerID NOT IN (SELECT CustomerID
                         FROM orders)
ORDER BY CustomerID;

5 List all contacts, i.e., suppliers and customers!

Select 'Supplier' TYPE, ContactName
FROM suppliers s
UNION
SELECT 'Customer' TYPE, ContactName
FROM customers c
ORDER BY ContactName;

6 List products with order quantities greater than 80!

SELECT p.ProductID, p.ProductName, SUM(od.Quantity) Quantity
FROM products p
JOIN orderdetails od
ON od.ProductID=p.ProductID
GROUP BY ProductID
HAVING Quantity > 80;

7 Which products were sold by the unit (i.e. quantity =1)?

SELECT p.ProductID, p.ProductName, od.Quantity
FROM products p
JOIN orderdetails od
ON p.ProductID = od.ProductID
WHERE od.Quantity = 1;

8 List customers who placed orders that are larger than the average of each customer order!

SELECT CustomerID, CustomerName, CustomerOrder
FROM (SELECT c.*, COUNT(OD.Quantity) AS 'CustomerOrder'
      FROM customers c
      JOIN orders o
      ON c.CustomerID = o.CustomerID
      JOIN orderdetails od
      ON o.OrderID = od.OrderID
      GROUP BY od.OrderID) AS co
GROUP BY co.CustomerID
HAVING CustomerOrder > AVG(CustomerOrder);

9 Find best selling products based on quantity!

SELECT ProductID, ProductName, TotalQuantity
FROM (SELECT p.*, SUM(od.Quantity) AS 'TotalQuantity'
      FROM products p
      JOIN orderdetails od
      ON p.ProductID = od.ProductID
      GROUP BY p.ProductID) AS tq
ORDER BY tq.TotalQuantity DESC
LIMIT 1;

10 Find best selling products based on revenue!

SELECT ProductID, ProductName, Revenue
FROM (SELECT p.*, SUM(od.Quantity*p.Price) AS 'Revenue'
      FROM products p
      JOIN orderdetails od
      ON p.ProductID = od.ProductID
      GROUP BY p.ProductID) AS r
ORDER BY r.Revenue DESC
LIMIT 1;

11 Find best selling products based on revenue for each country!

SELECT ProductID, ProductName, Country, Revenue
FROM (SELECT p.*, SUM(od.Quantity*p.Price) AS 'Revenue', Country
      FROM products p
      JOIN orderdetails od
      ON p.ProductID = od.ProductID
      JOIN orders o
      ON od.OrderID=o.OrderID
      JOIN customers c
      ON o.CustomerID=c.CustomerID
      GROUP BY p.ProductName, c.Country
      ORDER BY Revenue DESC) AS r
GROUP BY Country
ORDER BY Revenue DESC;

12 Find suppliers with a product price less than $50!

SELECT DISTINCT SupplierName
FROM suppliers s
JOIN products p
ON p.SupplierID=s.SupplierID
WHERE Price < 50
ORDER BY SupplierName;

13 Find top 10 best employees based on their sales quantity!

SELECT CONCAT(FirstName,' ',LastName) Name, sum(Quantity) Quantity
FROM employees e
JOIN orders o
ON o.EmployeeID=e.EmployeeID
JOIN orderdetails od
ON od.OrderID=o.OrderID
GROUP BY e.EmployeeID
ORDER BY Quantity DESC
LIMIT 10;

14 Find top 10 best supplier countries based on quantity!

SELECT Country, sum(Quantity) Quantity
FROM suppliers s
JOIN products p
ON p.SupplierID=s.SupplierID
JOIN orderdetails od
ON od.ProductID=p.ProductID
GROUP BY Country
ORDER BY Quantity DESC
LIMIT 10;

15 Find top 10 best customer countries based on quantity!

SELECT Country, sum(Quantity) Quantity
FROM customers c
JOIN orders o
ON o.CustomerID=c.CustomerID
JOIN orderdetails od
ON od.OrderID=o.OrderID
GROUP BY Country
ORDER BY Quantity DESC
LIMIT 10;

16 Find top 10 best selling products based on quantity in every year!

(SELECT ProductName, YEAR(o.OrderDate) Year, SUM(Quantity) Quantity
 FROM products p
 JOIN orderdetails od
 ON od.ProductID=p.ProductID
 JOIN orders o
 ON o.OrderID=od.OrderID
 GROUP BY Year, ProductName
 HAVING Year=1996
 ORDER BY Quantity DESC
 LIMIT 10)

UNION

(SELECT ProductName, YEAR(o.OrderDate) Year, SUM(Quantity) Quantity
 FROM products p
 JOIN orderdetails od
 ON od.ProductID=p.ProductID
 JOIN orders o
 ON o.OrderID=od.OrderID
 GROUP BY Year, ProductName
 HAVING Year=1997
 ORDER BY Quantity DESC
 LIMIT 10)