CPSC340A
Database Systems

Activity 2

Expressions

Sales Orders Database

  1. What if we adjusted each product price by reducing it 5 percent?

    +---------------+----------------+-----------+
    | ProductNumber | WholesalePrice | NewPrice  |
    +---------------+----------------+-----------+
    |             1 |         804.00 |  763.8000 |
    |             1 |         854.22 |  811.5090 |
    |             2 |        1269.00 | 1205.5500 |
    |             2 |        1477.81 | 1403.9195 |
    |             3 |          54.19 |   51.4805 |
    |             3 |          57.27 |   54.4065 |
    |             4 |          44.22 |   42.0090 |
    |             4 |          41.62 |   39.5390 |
    |             5 |           5.38 |    5.1110 |
    |             5 |           5.87 |    5.5765 |
    |             6 |         403.22 |  383.0590 |
    |             6 |         448.73 |  426.2935 |
    |             7 |          31.12 |   29.5640 |
    |             8 |          39.32 |   37.3540 |
    |             8 |          37.88 |   35.9860 |
    |             9 |          21.53 |   20.4535 |
    |            10 |          22.86 |   21.7170 |
    |            11 |        1076.62 | 1022.7890 |
    |            11 |        1178.65 | 1119.7175 |
    |            12 |          14.51 |   13.7845 |
    .
    .
    .
    (90 rows)
  2. Show me a list of orders made by each customer in descending date order. (Hint: You might need to order by more than one column for the information to display properly.)

    +------------+------------+-------------+
    | CustomerID | OrderDate  | OrderNumber |
    +------------+------------+-------------+
    |       1001 | 2013-02-27 |         931 |
    |       1001 | 2013-02-21 |         898 |
    |       1001 | 2013-02-20 |         887 |
    |       1001 | 2013-02-14 |         855 |
    |       1001 | 2013-02-13 |         851 |
    |       1001 | 2013-02-12 |         840 |
    |       1001 | 2013-02-08 |         817 |
    |       1001 | 2013-01-23 |         732 |
    |       1001 | 2013-01-21 |         723 |
    |       1001 | 2013-01-17 |         701 |
    |       1001 | 2013-01-10 |         657 |
    |       1001 | 2013-01-09 |         649 |
    |       1001 | 2013-01-07 |         632 |
    |       1001 | 2013-01-02 |         608 |
    |       1001 | 2012-12-31 |         596 |
    |       1001 | 2012-12-29 |         587 |
    |       1001 | 2012-12-25 |         567 |
    |       1001 | 2012-12-24 |         564 |
    |       1001 | 2012-12-11 |         501 |
    |       1001 | 2012-12-06 |         475 |
    |       1001 | 2012-11-30 |         452 |
    |       1001 | 2012-11-29 |         443 |
    |       1001 | 2012-11-29 |         448 |
    .
    .
    .
    (944 rows)
  3. Compile a complete list of vendor names and addresses in vendor name order.

    +------------------------+---------------------------------------------+-----------------+
    | VendName               | VendCompleteAddress                         | VendPhoneNumber |
    +------------------------+---------------------------------------------+-----------------+
    | Armadillo Brand        | 12330 Side Road Lane, Dallas  TX  75137     | (214) 444-9876  |
    | Big Sky Mountain Bikes | Glacier Bay South, Anchorage  AK  99209     | (907) 222-1234  |
    | Dog Ear                | 575 Madison Ave., New York  NY  10003       | (212) 888-9876  |
    | Kona, Incorporated     | PO Box 10429, Redmond  WA  98052            | (425) 333-1234  |
    | Lone Star Bike Supply  | 7402 Kingman Drive, El Paso  TX  79915      | (915) 666-9876  |
    | Nikoma of America      | 88 Old North Road Ave, Ballard  WA  91324   | (206) 666-1234  |
    | ProFormance            | 29 N. Quail St., Albany  NY  12012          | (518) 444-1234  |
    | Shinoman, Incorporated | 3042 19th Avenue South, Bellevue  WA  98001 | (425) 888-1234  |
    | Sun Sports Suppliers   | PO Box 8082, Santa Monica  CA  91003        | (310) 777-9876  |
    | Viscount               | 1911 Commerce Way, St. Louis  MO  63127     | (314) 777-1234  |
    +------------------------+---------------------------------------------+-----------------+
    (10 rows)

Entertainment Agency Database

  1. Give me the names of all our customers by city. (Hint: You’ll have to use an ORDER BY clause on one of the columns.)

    +----------+---------------------+
    | City     | Customer            |
    +----------+---------------------+
    | Auburn   | Hallmark, Elizabeth |
    | Bellevue | Bonnicksen, Joyce   |
    | Bellevue | Keyser, Liz         |
    | Bellevue | Pundt, Estella      |
    | Bellevue | Rosales, Mark       |
    | Bellevue | Thompson, Sarah     |
    | Kirkland | Brehm, Peter        |
    | Kirkland | Ehrlich, Zachary    |
    | Kirkland | Gehring, Darren     |
    | Redmond  | McCrae, Dean        |
    | Redmond  | Patterson, Kerry    |
    | Seattle  | Hartwig, Doris      |
    | Seattle  | Viescas, Carol      |
    | Tacoma   | Berg, Matt          |
    | Tacoma   | Waldal, Deb         |
    +----------+---------------------+
    (15 rows)
  2. List all entertainers and their Web sites.

    +--------------------------+---------------------------------+
    | Entertainer              | Drop_By                         |
    +--------------------------+---------------------------------+
    | Carol Peacock Trio       | Web site: www.cptrio.com        |
    | Topazz                   | Web site: www.topazz.com        |
    | JV & the Deep Six        | Web site: www.jvd6.com          |
    | Jim Glynn                | NULL                            |
    | Jazz Persuasion          | Web site: www.jazzper.com       |
    | Modern Dance             | Web site: www.moderndance.com   |
    | Coldwater Cattle Company | Web site: www.coldwatercows.com |
    | Country Feeling          | NULL                            |
    | Katherine Ehrlich        | NULL                            |
    | Saturday Revue           | Web site: www.satrevue.com      |
    | Julia Schnebly           | NULL                            |
    | Susan McLain             | Web site: www.greensleeves.com  |
    | Caroline Coie Cuartet    | NULL                            |
    +--------------------------+---------------------------------+
    (13 rows)
  3. Show the date of each agent’s first six-month performance review. (Hint: You’ll need to use date arithmetic to answer this request.)

    +-------------------+------------+-------------+
    | Agent             | DateHired  | FirstReview |
    +-------------------+------------+-------------+
    | Bishop, Scott     | 1998-02-05 | 1998-08-04  |
    | Dumbwit, Daffy    | 2000-02-05 | 2000-08-03  |
    | Kennedy, John     | 1997-05-15 | 1997-11-11  |
    | Patterson, Maria  | 1997-09-03 | 1998-03-02  |
    | Smith, Karen      | 1998-03-05 | 1998-09-01  |
    | Thompson, William | 1997-05-15 | 1997-11-11  |
    | Viescas, Caleb    | 1998-02-16 | 1998-08-15  |
    | Viescas, Carol    | 1997-11-19 | 1998-05-18  |
    | Wier, Marianne    | 1998-02-02 | 1998-08-01  |
    +-------------------+------------+-------------+
    (9 rows)

School Scheduling Database

  1. Give me a list of staff members, and show them in descending order of salary.

    +----------+--------------------+
    | Salary   | StaffMember        |
    +----------+--------------------+
    | 60000.00 | Abolrous, Sam      |
    | 60000.00 | Black, Alastair    |
    | 60000.00 | Bonnicksen, Joyce  |
    | 60000.00 | Brehm, Peter       |
    | 60000.00 | Hernandez, Michael |
    | 57000.00 | Hallmark, Alaina   |
    | 56000.00 | Patterson, Luke    |
    | 53000.00 | Hallmark, Gary     |
    | 52000.00 | Coie, Caroline     |
    | 52000.00 | Smith, David       |
    | 52000.00 | Smith, Jeffrey     |
    | 50000.00 | Viescas, Carol     |
    | 50000.00 | Wilson, Jim        |
    | 49000.00 | Brown, Robert      |
    | 48000.00 | Keyser, Liz        |
    | 48000.00 | Patterson, Maria   |
    | 45000.00 | DeGrasse, Kirk     |
    | 45000.00 | Ehrlich, Katherine |
    | 45000.00 | Glynn, Jim         |
    | 45000.00 | Patterson, Ann     |
    | 45000.00 | Sergienko, Mariya  |
    | 45000.00 | Viescas, Caleb     |
    | 44000.00 | Viescas, Suzanne   |
    | 44000.00 | Waldal, Deb        |
    | 40000.00 | Smith, Tim         |
    | 35000.00 | Rosales III, Joe   |
    | 25000.00 | Patterson, Kathryn |
    +----------+--------------------+
    (27 rows)
  2. Can you give me a staff member phone list?

    +--------------------+----------------+
    | StaffMember        | Phone          |
    +--------------------+----------------+
    | Abolrous, Sam      | (760) 555-2611 |
    | Black, Alastair    | (206) 555-0039 |
    | Bonnicksen, Joyce  | (425) 555-2726 |
    | Brehm, Peter       | (425) 555-2501 |
    | Brown, Robert      | (713) 555-2491 |
    | Coie, Caroline     | (206) 555-2306 |
    | DeGrasse, Kirk     | (210) 555-2311 |
    | Ehrlich, Katherine | (425) 555-0399 |
    | Glynn, Jim         | (425) 555-2531 |
    | Hallmark, Alaina   | (425) 555-2631 |
    | Hallmark, Gary     | (253) 555-2676 |
    | Hernandez, Michael | (253) 555-2711 |
    | Keyser, Liz        | (425) 555-2556 |
    | Patterson, Ann     | (253) 555-2591 |
    | Patterson, Kathryn | (206) 555-2651 |
    | Patterson, Luke    | (503) 555-2316 |
    | Patterson, Maria   | (915) 555-2291 |
    | Rosales III, Joe   | (253) 555-2281 |
    | Sergienko, Mariya  | (503) 555-2526 |
    | Smith, David       | (510) 555-2646 |
    | Smith, Jeffrey     | (510) 555-2596 |
    | Smith, Tim         | (206) 555-2536 |
    | Viescas, Caleb     | (562) 555-0037 |
    | Viescas, Carol     | (425) 555-2576 |
    | Viescas, Suzanne   | (425) 555-2686 |
    | Waldal, Deb        | (253) 555-2496 |
    | Wilson, Jim        | (503) 555-2636 |
    +--------------------+----------------+
    (27 rows)
  3. List the names of all our students, and order them by the cities they live in.

    +--------------+---------------------+
    | StudCity     | Student             |
    +--------------+---------------------+
    | Auburn       | Hallmark, Elizabeth |
    | Bremerton    | Chavez, George      |
    | Dallas       | Pundt, Steve        |
    | Eugene       | Smith, Karen        |
    | Long Beach   | Jones, Brannon      |
    | Lubbock      | Thompson, Sarah     |
    | Medford      | Bishop, Scott       |
    | Palm Springs | Stadick, Betsy      |
    | Portland     | Kennedy, John       |
    | Portland     | Sheskey, Sara       |
    | Redmond      | Galvin, Janice      |
    | Redmond      | Viescas, Michael    |
    | San Antonio  | Patterson, Kerry    |
    | Seattle      | Bonnicksen, Kendra  |
    | Seattle      | Hartwig, Doris      |
    | Seattle      | Lum, Richard        |
    | Tacoma       | Hamilton, David     |
    | Tacoma       | Wier, Marianne      |
    +--------------+---------------------+
    (18 rows)

Bowling League Database

  1. Show next year’s tournament date for each tournament location.

    +--------------------+-------------+---------------------+
    | TourneyLocation    | TourneyDate | NextYearTourneyDate |
    +--------------------+-------------+---------------------+
    | Acapulco Lanes     | 2012-12-04  | 2013-12-03          |
    | Acapulco Lanes     | 2012-10-16  | 2013-10-15          |
    | Bolero Lanes       | 2012-11-06  | 2013-11-05          |
    | Bolero Lanes       | 2012-09-18  | 2013-09-17          |
    | Bolero Lanes       | 2013-07-26  | 2014-07-25          |
    | Imperial Lanes     | 2012-11-13  | 2013-11-12          |
    | Imperial Lanes     | 2012-09-25  | 2013-09-24          |
    | Imperial Lanes     | 2013-08-09  | 2014-08-08          |
    | Red Rooster Lanes  | 2012-09-04  | 2013-09-03          |
    | Red Rooster Lanes  | 2013-07-12  | 2014-07-11          |
    | Red Rooster Lanes  | 2012-10-23  | 2013-10-22          |
    | Sports World Lanes | 2012-11-20  | 2013-11-19          |
    | Sports World Lanes | 2013-08-02  | 2014-08-01          |
    | Sports World Lanes | 2012-10-02  | 2013-10-01          |
    | Thunderbird Lanes  | 2012-10-30  | 2013-10-29          |
    | Thunderbird Lanes  | 2012-09-11  | 2013-09-10          |
    | Thunderbird Lanes  | 2013-07-19  | 2014-07-18          |
    | Totem Lanes        | 2012-10-09  | 2013-10-08          |
    | Totem Lanes        | 2012-11-27  | 2013-11-26          |
    | Totem Lanes        | 2013-08-16  | 2014-08-15          |
    +--------------------+-------------+---------------------+
    (20 rows)
  2. List the name and phone number for each member of the league.

    +---------------------+----------------+
    | Bowler              | Phone          |
    +---------------------+----------------+
    | Black, Alastair     | (206) 555-1189 |
    | Cunningham, David   | (206) 555-8122 |
    | Ehrlich, Zachary    | (206) 555-9857 |
    | Fournier, Barbara   | (206) 555-9876 |
    | Fournier, David     | (206) 555-9876 |
    | Hallmark, Alaina    | (206) 555-8990 |
    | Hallmark, Bailey    | (206) 555-8990 |
    | Hallmark, Elizabeth | (206) 555-8990 |
    | Hallmark, Gary      | (206) 555-8990 |
    | Hernandez, Kendra   | (206) 889-9191 |
    | Hernandez, Michael  | (206) 889-9191 |
    | Kennedy, Angel      | (206) 555-7854 |
    | Kennedy, John       | (206) 555-7854 |
    | Patterson, Ann      | (206) 555-3487 |
    | Patterson, Kathryn  | (206) 555-3487 |
    | Patterson, Megan    | (206) 555-3487 |
    | Patterson, Neil     | (206) 555-3487 |
    | Patterson, Rachel   | (206) 555-3487 |
    | Pundt, Steve        | (206) 555-3412 |
    | Rosales, Joe        | (206) 555-9482 |
    | Sheskey, Richard    | (206) 555-9893 |
    | Sheskey, Sara       | (206) 555-9893 |
    | Thompson, Mary      | (206) 555-8989 |
    | Thompson, Sarah     | (206) 555-8989 |
    | Thompson, William   | (206) 555-8989 |
    | Viescas, Caleb      | (206) 882-8878 |
    | Viescas, Carol      | (206) 555-7295 |
    | Viescas, David      | (206) 882-8878 |
    | Viescas, John       | (206) 881-5596 |
    | Viescas, Michael    | (206) 881-5596 |
    | Viescas, Stephanie  | (206) 882-8878 |
    | Viescas, Suzanne    | (206) 881-5596 |
    +---------------------+----------------+
    (32 rows)
  3. Give me a listing of each team’s lineup. (Hint: Base this query on the Bowlers table.)

    +--------+---------------------+
    | TeamID | Bowler              |
    +--------+---------------------+
    |      1 | Fournier, Barbara   |
    |      1 | Fournier, David     |
    |      1 | Kennedy, John       |
    |      1 | Sheskey, Sara       |
    |      2 | Patterson, Ann      |
    |      2 | Patterson, Neil     |
    |      2 | Viescas, David      |
    |      2 | Viescas, Stephanie  |
    |      3 | Black, Alastair     |
    |      3 | Cunningham, David   |
    |      3 | Kennedy, Angel      |
    |      3 | Viescas, Carol      |
    |      4 | Hallmark, Elizabeth |
    |      4 | Hallmark, Gary      |
    |      4 | Patterson, Kathryn  |
    |      4 | Sheskey, Richard    |
    |      5 | Hernandez, Kendra   |
    |      5 | Hernandez, Michael  |
    |      5 | Viescas, John       |
    |      5 | Viescas, Suzanne    |
    |      6 | Ehrlich, Zachary    |
    |      6 | Hallmark, Alaina    |
    |      6 | Thompson, Sarah     |
    |      6 | Viescas, Caleb      |
    |      7 | Patterson, Megan    |
    |      7 | Thompson, Mary      |
    |      7 | Thompson, William   |
    |      7 | Viescas, Michael    |
    |      8 | Hallmark, Bailey    |
    |      8 | Patterson, Rachel   |
    |      8 | Pundt, Steve        |
    |      8 | Rosales, Joe        |
    +--------+---------------------+
    (32 rows)