Level a task

***Level A tasks****
List of all customers:


SELECT * FROM Customers;

List of all customers where company name ending in N:


SELECT * FROM Customers
WHERE CompanyName LIKE '%N';

List of all customers who live in Berlin or London:


SELECT * FROM Customers
WHERE City IN ('Berlin', 'London');

List of all customers who live in UK or USA:

SELECT * FROM Customers
WHERE Country IN ('UK', 'USA');

List of all products sorted by product name:

SELECT * FROM Products
ORDER BY ProductName;

List of all products where product name starts with an A:

SELECT * FROM Products
WHERE ProductName LIKE 'A%';

List of customers who ever placed an order:

SELECT DISTINCT Customers.*
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

List of Customers who live in London and have bought chai:

SELECT DISTINCT Customers.*
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID
JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
JOIN Products ON OrderDetails.ProductID = Products.ProductID
WHERE Customers.City = 'London' AND Products.ProductName = 'Chai';

List of customers who never placed an order:

SELECT * FROM Customers
WHERE CustomerID NOT IN (SELECT CustomerID FROM Orders);

List of customers who ordered Tofu:

SELECT DISTINCT Customers.*
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID
JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
JOIN Products ON OrderDetails.ProductID = Products.ProductID
WHERE Products.ProductName = 'Tofu';

Details of first order of the system:

SELECT * FROM Orders
ORDER BY OrderDate
LIMIT 1;

Find the details of most expensive order date:

SELECT Orders.*, SUM(OrderDetails.Quantity * OrderDetails.UnitPrice) AS Total
FROM Orders
JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
GROUP BY Orders.OrderID
ORDER BY Total DESC
LIMIT 1;

For each order get the OrderID and Average quantity of items in that order:


SELECT OrderID, AVG(Quantity) AS AvgQuantity
FROM OrderDetails
GROUP BY OrderID;

For each order get the OrderID, minimum quantity and maximum quantity for that order:

SELECT OrderID, MIN(Quantity) AS MinQuantity, MAX(Quantity) AS MaxQuantity
FROM OrderDetails
GROUP BY OrderID;

Get a list of all managers and total number of employees who report to them:

SELECT ManagerID, COUNT(EmployeeID) AS NumberOfEmployees
FROM Employees
GROUP BY ManagerID;

Get the OrderID and the total quantity for each order that has a total quantity of greater than 300:

SELECT OrderID, SUM(Quantity) AS TotalQuantity
FROM OrderDetails
GROUP BY OrderID
HAVING TotalQuantity > 300;

List of all orders placed on or after 1996/12/31:

SELECT * FROM Orders
WHERE OrderDate >= '1996-12-31';

List of all orders shipped to Canada:


SELECT * FROM Orders
WHERE ShipCountry = 'Canada';

List of all orders with order total > 200:


SELECT Orders.*, SUM(OrderDetails.Quantity * OrderDetails.UnitPrice) AS OrderTotal
FROM Orders
JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
GROUP BY Orders.OrderID
HAVING OrderTotal > 200;

List of countries and sales made in each country:

SELECT ShipCountry, SUM(OrderDetails.Quantity * OrderDetails.UnitPrice) AS TotalSales
FROM Orders
JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
GROUP BY ShipCountry;

List of Customer ContactName and number of orders they placed:

SELECT Customers.ContactName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.ContactName;

List of customer contact names who have placed more than 3 orders:

SELECT Customers.ContactName
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.ContactName
HAVING COUNT(Orders.OrderID) > 3;

List of discontinued products which were ordered between 1/1/1997 and 1/1/1998:

SELECT DISTINCT Products.*
FROM Products
JOIN OrderDetails ON Products.ProductID = OrderDetails.ProductID
JOIN Orders ON OrderDetails.OrderID = Orders.OrderID
WHERE Products.Discontinued = 1
AND Orders.OrderDate BETWEEN '1997-01-01' AND '1998-01-01';

List of employee first name, last name, supervisor first name, last name:

SELECT e.FirstName, e.LastName, m.FirstName AS SupervisorFirstName, m.LastName AS SupervisorLastName
FROM Employees e
LEFT JOIN Employees m ON e.ManagerID = m.EmployeeID;

List of employee IDs and total sales conducted by employee:

SELECT Orders.EmployeeID, SUM(OrderDetails.Quantity * OrderDetails.UnitPrice) AS TotalSales
FROM Orders
JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
GROUP BY Orders.EmployeeID;

List of employees whose FirstName contains character 'a':

SELECT * FROM Employees
WHERE FirstName LIKE '%a%';

List of managers who have more than four people reporting to them:

SELECT ManagerID
FROM Employees
GROUP BY ManagerID
HAVING COUNT(EmployeeID) > 4;

List of Orders and ProductNames:

SELECT Orders.OrderID, Products.ProductName
FROM Orders
JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
JOIN Products ON OrderDetails.ProductID = Products.ProductID;
List of orders placed by the best customer:

WITH BestCustomer AS (
    SELECT CustomerID, COUNT(OrderID) AS NumberOfOrders
    FROM Orders
    GROUP BY CustomerID
    ORDER BY NumberOfOrders DESC
    LIMIT 1
)
SELECT Orders.*
FROM Orders
JOIN BestCustomer ON Orders.CustomerID = BestCustomer.CustomerID;

List of orders placed by customers who do not have a Fax number:

SELECT Orders.*
FROM Orders
JOIN Customers ON Orders.CustomerID = Customers.CustomerID
WHERE Customers.Fax IS NULL;

List of postal codes where the product Tofu was shipped:

SELECT DISTINCT Orders.ShipPostalCode
FROM Orders
JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
JOIN Products ON OrderDetails.ProductID = Products.ProductID
WHERE Products.ProductName = 'Tofu';

List of product names that were shipped to France:

SELECT DISTINCT Products.ProductName
FROM Orders
JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
JOIN Products ON OrderDetails.ProductID = Products.ProductID
WHERE Orders.ShipCountry = 'France';

List of ProductNames and Categories for the supplier 'Specialty Biscuits, Ltd.':

SELECT Products.ProductName, Categories.CategoryName
FROM Products
JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID
JOIN Categories ON Products.CategoryID = Categories.CategoryID
WHERE Suppliers.CompanyName = 'Specialty Biscuits, Ltd.';

List of products that were never ordered:

SELECT * FROM Products
WHERE ProductID NOT IN (SELECT ProductID FROM OrderDetails);

List of products where units in stock is less than 10 and units on order are 0:

SELECT * FROM Products
WHERE UnitsInStock < 10 AND UnitsOnOrder = 0;

List of top 10 countries by sales:

SELECT ShipCountry, SUM(OrderDetails.Quantity * OrderDetails.UnitPrice) AS TotalSales
FROM Orders
JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
GROUP BY ShipCountry
ORDER BY TotalSales DESC
LIMIT 10;

Number of orders each employee has taken for customers with CustomerIDs between A and AO:

SELECT EmployeeID, COUNT(OrderID) AS NumberOfOrders
FROM Orders
WHERE CustomerID BETWEEN 'A' AND 'AO'
GROUP BY EmployeeID;

Order date of most expensive order:

SELECT OrderDate
FROM Orders
JOIN (
    SELECT OrderID, SUM(Quantity * UnitPrice) AS Total
    FROM OrderDetails
    GROUP BY OrderID
    ORDER BY Total DESC
    LIMIT 1
) AS MostExpensiveOrder ON Orders.OrderID = MostExpensiveOrder.OrderID;

Product name and total revenue from that product:

SELECT Products.ProductName, SUM(OrderDetails.Quantity * OrderDetails.UnitPrice) AS TotalRevenue
FROM OrderDetails
JOIN Products ON OrderDetails.ProductID = Products.ProductID
GROUP BY Products.ProductName;

SupplierID and number of products offered:

SELECT SupplierID, COUNT(ProductID) AS NumberOfProducts
FROM Products
GROUP BY SupplierID;

Top ten customers based on their business:

SELECT Customers.CustomerID, Customers.ContactName, SUM(OrderDetails.Quantity * OrderDetails.UnitPrice) AS TotalBusiness
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID
JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
GROUP BY Customers.CustomerID, Customers.ContactName
ORDER BY TotalBusiness DESC
LIMIT 10;

What is the total revenue of the company:

SELECT SUM(OrderDetails.Quantity * OrderDetails.UnitPrice) AS TotalRevenue
FROM OrderDetails;