Solutions -- SQL Exercise 1


Query 1

Query 2

Query 3

Query 4

Query 5

Query 6

Query 7

Query 8

Query 9

Query 10

Query 11

Query 12

Query 13

Query 14

Query 15

Query 16

Query 17

Query 18

Query 19

 

 

 

 

 

 

 

 

 

 

1. Show all products that have fewer units in stock than the reorder level specifies.

SELECT ProductName
    FROM Products
        WHERE UnitsInStock < Reorderlevel;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

SELECT LastName
    FROM Employees
        WHERE BirthDate < #1/1/60#;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

SELECT CompanyName, HomePage
    FROM Suppliers
          WHERE HomePage IS NOT NULL;  
(EXISTS does not work in Access)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

5. 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");

 

 

 

 

 

 

 

 

 

 

 

 

 

 

6. 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;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

7. 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*"; 
(Access is inclusive at beginning but not end)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

SELECT LastName, Title
    FROM Employee
        WHERE Title LIKE "*Sales*";

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

SELECT Suppliers.CompanyName, Orders.OrderID
    FROM Orders INNER JOIN (
        (Suppliers INNER JOIN Products ON Suppliers.SupplierID = Products.SupplierID) 
        INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID) 
        ON [Order Details].OrderID = Orders.OrderID;

or

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;

 

 

 

 

 

 

 

 

 

 

10. 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 INNER JOIN Products ON Suppliers.SupplierID = Products.SupplierID)
            INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID
                  Where Suppliers.CompanyName = "Tokyo Traders";

or

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";

 

 

 

 

 

 

 

 

 

 

 

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

SELECT Suppliers.CompanyName, [Order Details].OrderID, Products.ProductName,
    (([Order Details].UnitPrice*[Order Details].Quantity) * (1-[Order Details].Discount)) as [Dollar Value]
          FROM (Suppliers INNER JOIN Products ON Suppliers.SupplierID = Products.SupplierID)
                INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID
                      WHERE suppliers.CompanyName = "Tokyo Traders";

or

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

 

 

 

 

 

 

 

 

 

12. Get the shipper name and supplier name for all orders shipped by speedy express.

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

or

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";

 

 

 

 

 

 

 

 

13. 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 INNER JOIN Orders ON Shippers.ShipperID = Orders.ShipVia
            GROUP BY Shippers.CompanyName;

or

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

14. Display the quantity ordered of each product.

SELECT Products.ProductName, Sum([Quantity]) AS [Quantity Ordered]
    FROM Products INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID
        GROUP BY ProductName;

or

SELECT ProductName, Sum(Quantity) AS [Quantity Ordered]
    FROM Products, [Order Details]
        WHERE Products.ProductID = [Order Details].ProductID
            GROUP BY ProductName
;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

15. 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;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

17. 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');

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

19. 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,
    FORMAT (SUM(([Order Details].UnitPrice * [Order Details].Quantity) * 
    (1 - [Order Details].Discount)), "$###,###.00") 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;

More on the Format function here.