ASSIGNMENT 06.1: Query a Database with SQL

con <- dbConnect(RSQLite::SQLite(), "OrdersDB.sqlitedb.db")

Q1: What are the names and phone numbers of all shippers, sorted by name?

SELECT ShipperName, Phone 
FROM Shippers 
ORDER BY ShipperName 
LIMIT 10 

Q2: What are the number of customers for each country? List the country and the number of customers renamed to ‘NumCust’.

SELECT Country, COUNT(CUSTOMERID) AS NumCust 
FROM Customers 
GROUP BY Country 
LIMIT 10

Q3: List the number of products by supplier ID and name, ordered from most to least, restricted to those suppliers who sell at least five products.

SELECT s.SupplierID, s.SupplierName, COUNT(p.Productname) AS NumProducts 
FROM Suppliers s 
JOIN Products p ON (s.SupplierID = p.SupplierID) 
GROUP BY s.SupplierID,s.SupplierName 
HAVING COUNT(p.Productname) >= 5 
ORDER BY NumProducts DESC

Q4: Who placed the most recent order (by order date)? List the customer’s ID, customer name, contact name, and date.

SELECT c.CustomerID, c.CustomerName, c.ContactName, o.orderDate
FROM Customers c
JOIN Orders o ON (c.CustomerID = o.CustomerID)
ORDER BY o.OrderDate DESC
LIMIT 1

Q5: What are the total number of unique orders delivered by each shipper, ordered alphabetically by shipper name?

SELECT s.ShipperName, COUNT(DISTINCT o.OrderID) AS NumOrders
FROM Shippers s
JOIN Orders o ON (s.ShipperID = o.ShipperID)
GROUP BY s.ShipperName 
ORDER BY s.ShipperName 
LIMIT 10

Q6: How many employees never worked with a single customer, i.e., they appear in the Employees table but not in the Orders table?

SELECT COUNT(e.EmployeeID) AS NumEmployees
FROM Employees e
LEFT JOIN Orders o ON e.EmployeeID = o.EmployeeID
WHERE o.EmployeeID IS NULL

Q7: How many products contain “Louisiana” at the beginning of the name of the product?

SELECT COUNT(ProductID) as NumProducts
FROM Products
WHERE Productname LIKE "Louisiana%"

Q8: What is the total number of distinct countries to which at least one order was shipped?

SELECT COUNT(DISTINCT c.Country) AS NumCountries
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID

Q9: What is the total amount (in terms of revenue) as well as the total number of orders sold by each employee? List the employee name, the total amount sold, and the total number of orders.

SELECT e.FirstName || ' ' || e.LastName AS EmployeeName,
       SUM(p.Price * od.Quantity) AS TotalAmount,
       COUNT(DISTINCT o.OrderID) AS TotalOrders
FROM Employees e
JOIN Orders o ON e.EmployeeID = o.EmployeeID
JOIN OrderDetails od ON o.OrderID = od.OrderID
JOIN Products p ON od.ProductID = p.ProductID
GROUP BY e.EmployeeID, e.LastName, e.FirstName
LIMIT 10

Q10: Which supplier sells the least number of different products but supplies at least one product?

SELECT s.SupplierID, s.SupplierName, COUNT(DISTINCT p.ProductID) AS NumProducts
FROM Suppliers s
JOIN Products p ON s.SupplierID = p.SupplierID
GROUP BY s.SupplierID, s.SupplierName
HAVING NumProducts = (
    SELECT MIN(cnt) 
    FROM (
        SELECT COUNT(DISTINCT ProductID) AS cnt 
        FROM Products 
        GROUP BY SupplierID
    )
)

Q11: Which product was ordered most often in terms of quantity ordered? List the product name and ID.

SELECT p.ProductID, p.ProductName, SUM(o.Quantity) AS TotalQuantity
FROM OrderDetails o
JOIN Products p ON o.ProductID = p.ProductID
GROUP BY p.ProductID, p.ProductName
ORDER BY TotalQuantity DESC
LIMIT 1

Q12: Which product generated the most revenue? List the product ID and name.

SELECT p.ProductID, p.Productname, SUM(o.Quantity * p.Price)  AS Revenue
FROM Products p
JOIN OrderDetails o ON (p.ProductID = o.ProductID)
GROUP BY p.ProductID, p.Productname
ORDER BY Revenue DESC
LIMIT 1

Q13: What is the total amount spent by all customers who do live in either Brazil, Mexico, or Canada?

SELECT SUM(od.Quantity * p.Price) AS TotalAmount
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
JOIN OrderDetails od ON o.OrderID = od.OrderID
JOIN Products p ON od.ProductID = p.ProductID
WHERE c.Country IN ('Brazil', 'Mexico', 'Canada')

Q14: What is the difference in spending between the country to which the most was sold versus the country to which the least was sold?

SELECT MAX(TotalAmount) - MIN(TotalAmount) AS DIFFERENCE
FROM (
      SELECT c.Country, SUM(od.Quantity * p.Price) AS TotalAmount
      FROM Customers c
      JOIN Orders o ON c.CustomerID = o.CustomerID
      JOIN OrderDetails od ON o.OrderID = od.OrderID
      JOIN Products p ON od.ProductID = p.ProductID
      GROUP BY c.country
)

Q15: Which country has the least number of customers?

SELECT Country, COUNT(CustomerID) AS NumCustomers
FROM Customers
GROUP BY Country
ORDER BY NumCustomers ASC
LIMIT 1

Q16: Which employee generated the most revenue (in terms of “dollar amount” sold)?

SELECT e.EmployeeID, e.LastName, e.FirstName, SUM(p.Price * od.Quantity) AS DollarAmount
FROM Employees e
JOIN Orders o ON e.EmployeeID = o.EmployeeID
JOIN OrderDetails od ON o.OrderID = od.OrderID
JOIN Products p ON od.ProductID = p.ProductID
GROUP BY e.EmployeeID, e.LastName, e.FirstName
ORDER BY DollarAmount DESC
LIMIT 1

Q17: Which customers (name and ID) have never bought anything?

SELECT c.CustomerID, c.CustomerName
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE o.OrderID IS NULL 
LIMIT 10

Q18: What is the average order total per country? List the country and the average order total.

SELECT c.Country, AVG(OrderTotal) AS AvgOrderTotal
FROM (
    SELECT o.OrderID, o.CustomerID, SUM(p.Price * od.Quantity) AS OrderTotal
    FROM Orders o
    JOIN OrderDetails od ON o.OrderID = od.OrderID
    JOIN Products p ON od.ProductID = p.ProductID
    GROUP BY o.OrderID, o.CustomerID
) sub
JOIN Customers c ON sub.CustomerID = c.CustomerID
GROUP BY c.Country
LIMIT 10

Q19: To which customer was the second most recent order sent? List the Order ID, customer name, and country to which the order was shipped.

SELECT o.OrderID, c.CustomerName, c.Country, o.OrderDate
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
ORDER BY o.OrderDate DESC
LIMIT 1
OFFSET 1

Q20: From how many different (unique) suppliers do the products come from that have been ordered at least five times?

SELECT COUNT(DISTINCT p.SupplierID) AS NumSuppliers
FROM Products p
WHERE p.ProductID IN (
    SELECT od.ProductID
    FROM OrderDetails od
    GROUP BY od.ProductID
    HAVING SUM(od.Quantity) >= 5
)

dbDisconnect(con)