SQL Exercises

Follow these directions to export the Northwinds database to MySQL.          

Use the database to answer these queries:

1. Get supplier names and order ID for all orders.
   

   SELECT Suppliers.CompanyName, Orders.OrderID
     FROM Suppliers, Products, Order_Details, Orders
       WHERE Suppliers.SupplierID=Products.SupplierID
         AND Products.ProductID=Order_Details.ProductID
         AND Order_Details.OrderID=Orders.OrderID;

   ...or...

   SELECT Suppliers.CompanyName, Orders.OrderID
     FROM Suppliers
     INNER JOIN Products USING (SupplierID)
     INNER JOIN Order_Details USING (ProductID)
     INNER JOIN Orders USING (OrderID);

2. Get supplier name, order ID and product name for all orders supplied by "Tokyo Traders."

   SELECT Suppliers.CompanyName, Order_Details.OrderID, Products.ProductName
     FROM Suppliers, Products, Order_Details
       WHERE Suppliers.SupplierID = Products.SupplierID
         AND Products.ProductID = Order_Details.ProductID
         AND Suppliers.CompanyName = "Tokyo Traders";

   ...or...

   SELECT Suppliers.CompanyName, Order_Details.OrderID, Products.ProductName
     FROM Suppliers
     INNER JOIN Products USING (SupplierID)
     INNER JOIN Order_Details USING (ProductID)
     WHERE Suppliers.CompanyName = "Tokyo Traders";

3. Get supplier name, order ID, product name, and dollar value of the amount of that product ordered for each order supplied by "Tokyo Traders."

   SELECT Suppliers.CompanyName, Order_Details.OrderID, Products.ProductName,
     ((Order_Details.UnitPrice*Order_Details.Quantity) * (1-Order_Details.Discount)) as "$Value"
       FROM Suppliers, Products, Order_Details
         WHERE Suppliers.SupplierID = Products.SupplierID
           AND Products.ProductID = Order_Details.ProductID
           AND Suppliers.CompanyName = "Tokyo Traders";

   ...or...

   SELECT Suppliers.CompanyName, Order_Details.OrderID, Products.ProductName,
     ((Order_Details.UnitPrice*Order_Details.Quantity) * (1-Order_Details.Discount)) as "$Value"
       FROM Suppliers
       INNER JOIN Products USING (SupplierID)
       INNER JOIN Order_Details USING (ProductID)
       WHERE suppliers.CompanyName = "Tokyo Traders";

4. Get the shipper name and supplier name for all orders shipped by "Speedy Express."

   SELECT Shippers.CompanyName, Suppliers.CompanyName
     FROM Shippers, Orders, Order_Details, Products, Suppliers
       WHERE Shippers.ShipperID = Orders.ShipVia
         AND Orders.OrderID = Order_Details.OrderID
         AND Order_Details.ProductID = Products.ProductID
         AND Products.SupplierID = Suppliers.SupplierID
         AND Shippers.CompanyName = "Speedy Express";

   ...or...

   SELECT Shippers.CompanyName, Suppliers.CompanyName
     FROM Shippers
     INNER JOIN Orders ON Shippers.ShipperID = Orders.ShipVia
     INNER JOIN Order_Details USING (OrderID)
     INNER JOIN Products USING (ProductID)
     INNER JOIN Suppliers USING (SupplierID)
     WHERE Shippers.CompanyName = "Speedy Express";

5. List the shipper name and the number of orders shipped by each shipper.

   SELECT Shippers.CompanyName, COUNT(Orders.OrderID) as "Number of Orders"
     FROM Shippers, Orders
       WHERE Shippers.ShipperID = Orders.ShipVia
         GROUP BY Shippers.CompanyName;

   ...or...

   SELECT Shippers.CompanyName, COUNT(Orders.OrderID) as "Number of Orders"
     FROM Shippers
     INNER JOIN Orders ON Shippers.ShipperID = Orders.ShipVia
     GROUP BY Shippers.CompanyName;

6. Display the quantity ordered of each product.

   SELECT ProductName, Sum(Quantity) AS "Quantity Ordered"
     FROM Products, Order_Details
       WHERE Products.ProductID = Order_Details.ProductID
         GROUP BY ProductName;

   ...or...

   SELECT Products.ProductName, Sum(Quantity) AS "Quantity Ordered"
     FROM Products
     INNER JOIN Order_Details USING (ProductID)
     GROUP BY ProductName;

7. Write a query that shows how many orders are associated with each customer ID. Note that the customerID field is replaced with the Customer’s name. Why is that?

   SELECT Orders.CustomerID, COUNT(Orders.OrderID) as "Number of Orders"
     FROM Orders
       GROUP BY Orders.CustomerID;

8. Total inventory (dollar value) on hand by category.

   SELECT CategoryID, SUM(UnitPrice * UnitsInStock) AS "Inventory Value"
     FROM Products
       GROUP BY CategoryID;

9. Show the company names of all customers that have ordered the product "Aniseed Syrup."

   SELECT Customers.CompanyName
     FROM Customers, Orders, Order_Details, Products
       WHERE Customers.CustomerID = Orders.CustomerID
         AND Orders.OrderID = Order_Details.OrderID
         AND Order_Details.ProductID = Products.ProductID
         AND Products.ProductName = 'Aniseed Syrup';

   ...or...

   SELECT Customers.CompanyName
     FROM Customers
     INNER JOIN Orders USING (CustomerID)
     INNER JOIN Order_Details USING (OrderID)
     INNER JOIN Products USING (ProductID)
     WHERE Products.ProductName = 'Aniseed Syrup';

10. Show the company names of all suppliers who have discontinued products.

    SELECT DISTINCT Suppliers.CompanyName
      FROM Suppliers, Products
        WHERE Suppliers.SupplierID = Products.SupplierID
          AND Products.Discontinued;

    ...or...

    SELECT DISTINCT Suppliers.CompanyName
      FROM Suppliers
      INNER JOIN Products USING (SupplierID)
      WHERE Products.Discontinued;

11. Show the total dollar value of all the orders we have for each customer (one dollar amount for each customer). Order the list in descending order.

    SELECT Orders.CustomerID,
      CONCAT('$', FORMAT (SUM((Order_Details.UnitPrice * Order_Details.Quantity) * (1 - Order_Details.Discount)),2)) AS Total
        FROM Orders, Order_Details
          WHERE Orders.OrderID = Order_Details.OrderID
            GROUP BY Orders.CustomerID
              ORDER BY SUM((Order_Details.UnitPrice *
                Order_Details.Quantity) * (1 - Order_Details.Discount)) DESC;

    ...or...

    SELECT Orders.CustomerID,
        CONCAT('$', FORMAT (SUM((Order_Details.UnitPrice * Order_Details.Quantity) *
        (1 - Order_Details.Discount)),2)) AS Total
      FROM Orders
      INNER JOIN Order_Details USING (OrderID)
      GROUP BY Orders.CustomerID
      ORDER BY SUM((Order_Details.UnitPrice *
      Order_Details.Quantity) * (1 - Order_Details.Discount)) DESC;

12. Show all products that have UnitsInStock that are below the ReorderLevel.

    SELECT ProductName
      FROM Products
        WHERE UnitsInStock < Reorderlevel;

13. What are the last names of all employees born before Jan 1, 1960?

    SELECT LastName
      FROM Employees
        WHERE BirthDate < "1960-1-1";

14. List the supplier name and city for all suppliers based in the United States.

    SELECT CompanyName, City
      FROM Suppliers
        WHERE Country = "USA";

15. List the supplier name and URL for all suppliers that have a home page.

    SELECT CompanyName, HomePage
      FROM Suppliers
        WHERE HomePage IS NOT NULL;

16. Using one of the special operators, list the supplier name and country for all suppliers based in an English-speaking county.

    SELECT CompanyName, Country
      FROM Suppliers
        WHERE Country in ("USA", "UK", "Australia", "Canada");

17. Using one of the special operators, list the product name and price for all products that sell for as little as $10 and as much as $20.

    SELECT ProductName, UnitPrice
      FROM Products
        WHERE UnitPrice BETWEEN 10.00 AND 20.00;

18. List the company name for all customers whose company name falls between 'L' and 'O', inclusive.

    SELECT CompanyName
      FROM Customers
        WHERE CompanyName Between "L*" AND "P*";

    MySQL is inclusive at beginning but not end.

19. List the last name and title for all employees who work in sales.

    SELECT LastName, Title
      FROM Employees
        WHERE Title LIKE "Sales%";