Seatwork (2010-02-01)

from Quiz (February 1, 2010)

1. Show each Antiques order and the last and first names of the person who ordered the item.

    SELECT BuyerID, OwnerLastName, OwnerFirstName
    FROM Antique
    WHERE BuyerID =  (
        SELECT OwnerID
        FROM AntiqueOwners
    );

2. Show each column in the EmployeeStatisticsTable in alphabetical order by Position, then by EmployeeIDNo.

    SELECT *
    FROM EmployeeStatisticsTable
    ORDER BY Position ASC, EmployeeIDNo ASC;

3. Show the annual budget for Benefits from the EmployeeStatisticsTable.

    SELECT SUM(Benefits) as AnnualBudget
    FROM EmployeeStatisticsTable;

4. Using the IN Operator, show the names of the owners of Chairs.

    SELECT CONCAT(OwnerLastName, ', ', OwnerFirstName) as FullName
    FROM AntiqueOwners
    WHERE OwnerID IN (
        SELECT BuyerID
        FROM Antiques
        WHERE Item = 'Chair'
    );

5. Show the names of all Antiques Owners who have do not have an order placed.

    SELECT CONCAT(OwnerLastName, ', ', OwnerFirstName) as FullName
    FROM AntiqueOwners
    WHERE OwnerID !=  (
        SELECT OwnerID
        FROM Orders
    );

6. Show the names of those who have placed Antique orders, with no duplicates (Hint: consider the order of tables in the From clause).

    SELECT DISTINCT CONCAT(OwnerLastName, ', ', OwnerFirstName) as FullName
    FROM AntiqueOwners
    WHERE OwnerID =  (
        SELECT OwnerID
        FROM Orders
    );

7. Show all of Bob Smith's Antique orders (Hint: Bob's ID Number is 02).

    SELECT Item
    FROM Antique
    WHERE BuyerID = 02

or

    SELECT Item
    FROM Antique
    WHERE BuyerID =  (
        SELECT OwnerID
        FROM AntiqueOwners
        WHERE (OwnerLastName = 'Smith'
        AND OwnerFirstName = 'Bob')
    );

8. Show an/all Antique order/s for a Rocking Chair for Jane Akins (Hint: Jane's ID Number is 21).

9. (Challenger) Show the annual budget for Salary by each position from the EmployeeStatisticsTable

    SELECT SUM(Salary) as AnnualBudget
    FROM EmployeeStatisticsTable
    GROUP BY POSITION;