CPSC340A
Database Systems

Activity 6

Union

Sales Orders Database

  1. List the customers who ordered a helmet together with the vendors who provide helmets. (Hint: This involves creating a UNION of two complex JOINs.)

    +------------------------+------------------------------+----------+
    | FullName               | ProductName                  | RowID    |
    +------------------------+------------------------------+----------+
    | Viescas, Suzanne       | Viscount Microshell Helmet   | Customer |
    | Viescas, Suzanne       | Glide-O-Matic Cycling Helmet | Customer |
    | Viescas, Suzanne       | King Cobra Helmet            | Customer |
    | Thompson, William      | Glide-O-Matic Cycling Helmet | Customer |
    | Thompson, William      | Dog Ear Helmet Mount Mirrors | Customer |
    | Thompson, William      | King Cobra Helmet            | Customer |
    | Hallmark, Gary         | Glide-O-Matic Cycling Helmet | Customer |
    | Hallmark, Gary         | King Cobra Helmet            | Customer |
    | Hallmark, Gary         | Viscount Microshell Helmet   | Customer |
    | Hallmark, Gary         | Dog Ear Helmet Mount Mirrors | Customer |
    | Brown, Robert          | Glide-O-Matic Cycling Helmet | Customer |
    | Brown, Robert          | Viscount Microshell Helmet   | Customer |
    | Brown, Robert          | King Cobra Helmet            | Customer |
    | McCrae, Dean           | Glide-O-Matic Cycling Helmet | Customer |
    | McCrae, Dean           | King Cobra Helmet            | Customer |
    | McCrae, Dean           | Viscount Microshell Helmet   | Customer |
    | McCrae, Dean           | Dog Ear Helmet Mount Mirrors | Customer |
    | Viescas, John          | King Cobra Helmet            | Customer |
    | Viescas, John          | Glide-O-Matic Cycling Helmet | Customer |
    | Sergienko, Mariya      | Dog Ear Helmet Mount Mirrors | Customer |
    | Sergienko, Mariya      | Viscount Microshell Helmet   | Customer |
    | Sergienko, Mariya      | King Cobra Helmet            | Customer |
    | Sergienko, Mariya      | Glide-O-Matic Cycling Helmet | Customer |
    | Patterson, Neil        | Viscount Microshell Helmet   | Customer |
    | Patterson, Neil        | King Cobra Helmet            | Customer |
    | Patterson, Neil        | Glide-O-Matic Cycling Helmet | Customer |
    | Patterson, Neil        | Dog Ear Helmet Mount Mirrors | Customer |
    | Cencini, Andrew        | King Cobra Helmet            | Customer |
    | Cencini, Andrew        | Glide-O-Matic Cycling Helmet | Customer |
    | Cencini, Andrew        | Dog Ear Helmet Mount Mirrors | Customer |
    .
    .
    .
    (91 rows)

Entertainment Agency Database

  1. Display a combined list of customers and entertainers. (Hint: Be careful to create an expression for one of the names so that you have the same number of columns in both SELECT statements.)

    +--------------------------+-------------+
    | Name                     | Type        |
    +--------------------------+-------------+
    | Hartwig, Doris           | Customer    |
    | Waldal, Deb              | Customer    |
    | Brehm, Peter             | Customer    |
    | McCrae, Dean             | Customer    |
    | Hallmark, Elizabeth      | Customer    |
    | Berg, Matt               | Customer    |
    | Keyser, Liz              | Customer    |
    | Gehring, Darren          | Customer    |
    | Thompson, Sarah          | Customer    |
    | Ehrlich, Zachary         | Customer    |
    | Bonnicksen, Joyce        | Customer    |
    | Patterson, Kerry         | Customer    |
    | Pundt, Estella           | Customer    |
    | Rosales, Mark            | Customer    |
    | Viescas, Carol           | Customer    |
    | Carol Peacock Trio       | Entertainer |
    | Topazz                   | Entertainer |
    | JV & the Deep Six        | Entertainer |
    | Jim Glynn                | Entertainer |
    | Jazz Persuasion          | Entertainer |
    | Modern Dance             | Entertainer |
    | Coldwater Cattle Company | Entertainer |
    | Country Feeling          | Entertainer |
    | Katherine Ehrlich        | Entertainer |
    | Saturday Revue           | Entertainer |
    | Julia Schnebly           | Entertainer |
    | Susan McLain             | Entertainer |
    | Caroline Coie Cuartet    | Entertainer |
    +--------------------------+-------------+
    (28 rows)
  2. Produce a list of customers who like contemporary music together with a list of entertainers who play contemporary music. (Hint: You need to UNION two complex JOINs to solve this one.)

    +-----------------------+-------------+
    | Name                  | Type        |
    +-----------------------+-------------+
    | Doris Hartwig         | Customer    |
    | Darren Gehring        | Customer    |
    | Kerry Patterson       | Customer    |
    | Carol Peacock Trio    | Entertainer |
    | Caroline Coie Cuartet | Entertainer |
    +-----------------------+-------------+
    (5 rows)

School Scheduling Database

  1. Create a mailing list for students and staff, sorted by ZIP Code.

    | Name               | StudStreetAddress       | StudCity     | StudState | StudZipCode |
    +--------------------+-------------------------+--------------+-----------+-------------+
    | Robert Brown       | 672 Lamont Ave          | Houston      | TX        | 77201       |
    | Kerry Patterson    | 9877 Hacienda Drive     | San Antonio  | TX        | 78284       |
    | Kirk DeGrasse      | 455 West Palm Ave       | San Antonio  | TX        | 78284       |
    | Sarah Thompson     | 2222 Springer Road      | Lubbock      | TX        | 79402       |
    | Steve Pundt        | 2500 Rosales Lane       | Dallas       | TX        | 79915       |
    | Maria Patterson    | 3445 Cheyenne Road      | El Paso      | TX        | 79993       |
    | Caleb Viescas      | 4501 Wetland Road       | Long Beach   | CA        | 90809       |
    | Brannon Jones      | 777 Fenexet Blvd        | Long Beach   | CA        | 90809       |
    | Betsy Stadick      | 611 Alpine Drive        | Palm Springs | CA        | 92263       |
    | Sam Abolrous       | 611 Alpine Drive        | Palm Springs | CA        | 92263       |
    | Jeffrey Smith      | 30301 - 166th Ave. N.E. | Fremont      | CA        | 94538       |
    | David Smith        | 311 20th Ave. N.E.      | Fremont      | CA        | 94538       |
    | Luke Patterson     | 877 145th Ave SE        | Portland     | OR        | 97208       |
    | Mariya Sergienko   | 901 Pine Avenue         | Portland     | OR        | 97208       |
    | John Kennedy       | 16679 NE 41st Court     | Portland     | OR        | 97208       |
    | Sara Sheskey       | 16679 NE 41st Court     | Portland     | OR        | 97208       |
    .
    .
    .
    (45 rows)

Bowling League Database

  1. Find the bowlers who had a raw score of 165 or better at Thunderbird Lanes combined with bowlers who had a raw score of 150 or better at Bolero Lanes. (Hint: This can also be solved with a UNION or a single SELECT statement and a complex WHERE clause.)

    +-------------------+----------------+-----------------+----------+
    | TourneyLocation   | BowlerLastName | BowlerFirstName | RawScore |
    +-------------------+----------------+-----------------+----------+
    | Thunderbird Lanes | Kennedy        | John            |      181 |
    | Thunderbird Lanes | Cunningham     | David           |      167 |
    | Thunderbird Lanes | Kennedy        | John            |      175 |
    | Thunderbird Lanes | Cunningham     | David           |      178 |
    | Thunderbird Lanes | Patterson      | Neil            |      179 |
    | Thunderbird Lanes | Viescas        | David           |      181 |
    | Thunderbird Lanes | Patterson      | Kathryn         |      180 |
    | Thunderbird Lanes | Viescas        | David           |      183 |
    | Thunderbird Lanes | Patterson      | Kathryn         |      177 |
    | Thunderbird Lanes | Viescas        | David           |      178 |
    | Thunderbird Lanes | Viescas        | John            |      189 |
    | Thunderbird Lanes | Thompson       | Mary            |      180 |
    | Thunderbird Lanes | Thompson       | William         |      167 |
    | Thunderbird Lanes | Viescas        | John            |      176 |
    | Thunderbird Lanes | Thompson       | Mary            |      175 |
    | Thunderbird Lanes | Thompson       | William         |      178 |
    | Thunderbird Lanes | Thompson       | Sarah           |      168 |
    | Thunderbird Lanes | Patterson      | Rachel          |      179 |
    | Thunderbird Lanes | Viescas        | Caleb           |      193 |
    | Thunderbird Lanes | Thompson       | Sarah           |      178 |
    .
    .
    .
    (129 rows)

Recipes Database

  1. Display a list of all ingredients and their default measurement amounts together with ingredients used in recipes and the measurement amount for each recipe. (Hint: You need one simple JOIN and one complex JOIN to solve this.)

    +-------------------------------+------------------------+------------+
    | IngredientName                | MeasurementDescription | Type       |
    +-------------------------------+------------------------+------------+
    | Beef                          | Ounce                  | Ingredient |
    | Water                         | Ounce                  | Ingredient |
    | Guinness Beer                 | Ounce                  | Ingredient |
    | Vinegar                       | Ounce                  | Ingredient |
    | Olive Oil                     | Ounce                  | Ingredient |
    | Fettuccini Pasta              | Ounce                  | Ingredient |
    | Heavy Cream                   | Ounce                  | Ingredient |
    | Spaghetti                     | Ounce                  | Ingredient |
    | Mustard, Regular              | Ounce                  | Ingredient |
    | Mustard, Dijon                | Ounce                  | Ingredient |
    | Ketchup                       | Ounce                  | Ingredient |
    | Salsa                         | Ounce                  | Ingredient |
    | Parmesan Cheese               | Ounce                  | Ingredient |
    | Blue Cheese                   | Ounce                  | Ingredient |
    | White Pepper (ground)         | Ounce                  | Ingredient |
    | Cayenne Pepper, Ground        | Ounce                  | Ingredient |
    | Balsamic Vinaigrette Dressing | Ounce                  | Ingredient |
    | Red Snapper                   | Ounce                  | Ingredient |
    | Potato                        | Cup                    | Ingredient |
    | Carrot                        | Cup                    | Ingredient |
    | Jalapeno                      | Cup                    | Ingredient |
    .
    .
    .
    (144 rows)