CPSC340A
Database Systems

Activity 5

Outer Joins

Sales Orders Database

  1. Show me customers who have never ordered a helmet. (Hint: This is another request where you must first build an INNER JOIN to find all orders containing helmets and then do an OUTER JOIN with Customers.)

    +------------+---------------+--------------+
    | CustomerID | CustFirstName | CustLastName |
    +------------+---------------+--------------+
    |       1011 | Alaina        | Hallmark     |
    |       1023 | Julia         | Schnebly     |
    |       1028 | Jeffrey       | Tirekicker   |
    +------------+---------------+--------------+
    (3 rows)
  2. Display customers who have no sales rep (employees) in the same ZIP Code.

    +------------+---------------+--------------+-------------+
    | CustomerID | CustFirstName | CustLastName | CustZipCode |
    +------------+---------------+--------------+-------------+
    |       1004 | Robert        | Brown        | 77201       |
    |       1007 | Mariya        | Sergienko    | 97208       |
    |       1008 | Neil          | Patterson    | 92199       |
    |       1010 | Angel         | Kennedy      | 78710       |
    |       1011 | Alaina        | Hallmark     | 98072       |
    |       1013 | Rachel        | Patterson    | 92199       |
    |       1014 | Sam           | Abolrous     | 92263       |
    |       1015 | Darren        | Gehring      | 95926       |
    |       1016 | Jim           | Wilson       | 97301       |
    |       1017 | Manuela       | Seidel       | 97501       |
    |       1018 | David         | Smith        | 94538       |
    |       1019 | Zachary       | Ehrlich      | 91209       |
    |       1021 | Estella       | Pundt        | 75260       |
    |       1022 | Caleb         | Viescas      | 90809       |
    |       1023 | Julia         | Schnebly     | 99837       |
    |       1024 | Mark          | Rosales      | 79915       |
    |       1025 | Maria         | Patterson    | 79915       |
    |       1027 | Luke          | Patterson    | 97208       |
    +------------+---------------+--------------+-------------+
    (18 rows)
  3. List all products and the dates for any orders.

    +---------------+---------------------------------------+------------+
    | ProductNumber | ProductName                           | OrderDate  |
    +---------------+---------------------------------------+------------+
    |             1 | Trek 9000 Mountain Bike               | 2012-09-01 |
    |             1 | Trek 9000 Mountain Bike               | 2012-09-02 |
    |             1 | Trek 9000 Mountain Bike               | 2012-09-03 |
    |             1 | Trek 9000 Mountain Bike               | 2012-09-04 |
    |             1 | Trek 9000 Mountain Bike               | 2012-09-05 |
    |             1 | Trek 9000 Mountain Bike               | 2012-09-06 |
    |             1 | Trek 9000 Mountain Bike               | 2012-09-07 |
    |             1 | Trek 9000 Mountain Bike               | 2012-09-08 |
    |             1 | Trek 9000 Mountain Bike               | 2012-09-09 |
    |             1 | Trek 9000 Mountain Bike               | 2012-09-10 |
    |             1 | Trek 9000 Mountain Bike               | 2012-09-12 |
    |             1 | Trek 9000 Mountain Bike               | 2012-09-13 |
    |             1 | Trek 9000 Mountain Bike               | 2012-09-14 |
    |             1 | Trek 9000 Mountain Bike               | 2012-09-15 |
    |             1 | Trek 9000 Mountain Bike               | 2012-09-18 |
    |             1 | Trek 9000 Mountain Bike               | 2012-09-19 |
    |             1 | Trek 9000 Mountain Bike               | 2012-09-20 |
    |             1 | Trek 9000 Mountain Bike               | 2012-09-21 |
    |             1 | Trek 9000 Mountain Bike               | 2012-09-22 |
    |             1 | Trek 9000 Mountain Bike               | 2012-09-23 |
    |             1 | Trek 9000 Mountain Bike               | 2012-09-24 |
    |             1 | Trek 9000 Mountain Bike               | 2012-09-25 |
    |             1 | Trek 9000 Mountain Bike               | 2012-09-26 |
    |             1 | Trek 9000 Mountain Bike               | 2012-09-27 |
    |             1 | Trek 9000 Mountain Bike               | 2012-09-29 |
    |             1 | Trek 9000 Mountain Bike               | 2012-09-30 |
    |             1 | Trek 9000 Mountain Bike               | 2012-10-01 |
    |             1 | Trek 9000 Mountain Bike               | 2012-10-02 |
    .
    .
    .
    (2681 rows)

Bowling League Database

  1. Display matches with no game data.

    +---------+-----------+-------------+--------------+
    | MatchID | TourneyID | OddLaneTeam | EvenLaneTeam |
    +---------+-----------+-------------+--------------+
    |      57 |        11 | MintJuleps  | Huckleberrys |
    +---------+-----------+-------------+--------------+
    (1 row)
  2. Display all tournaments and any matches that have been played.

    +-----------+-------------+--------------------+---------+------------+------------+
    | TourneyID | TourneyDate | TourneyLocation    | MatchID | GameNumber | Winner     |
    +-----------+-------------+--------------------+---------+------------+------------+
    |         1 | 2012-09-04  | Red Rooster Lanes  |       1 |          2 | Sharks     |
    |         1 | 2012-09-04  | Red Rooster Lanes  |       3 |          1 | Dolphins   |
    |         1 | 2012-09-04  | Red Rooster Lanes  |       4 |          3 | Manatees   |
    |         1 | 2012-09-04  | Red Rooster Lanes  |       2 |          1 | Terrapins  |
    |         1 | 2012-09-04  | Red Rooster Lanes  |       3 |          3 | Dolphins   |
    |         1 | 2012-09-04  | Red Rooster Lanes  |       4 |          2 | Swordfish  |
    |         1 | 2012-09-04  | Red Rooster Lanes  |       1 |          1 | Marlins    |
    |         1 | 2012-09-04  | Red Rooster Lanes  |       2 |          3 | Terrapins  |
    |         1 | 2012-09-04  | Red Rooster Lanes  |       3 |          2 | Orcas      |
    .
    .
    .
    (174 rows)