CPSC340A
Database Systems

Activity 3

Filtering

Sales Orders Database

  1. Give me the names of all vendors based in Ballard, Bellevue, and Redmond.

    +------------------------+----------+
| VendName               | VendCity |
+------------------------+----------+
| Kona, Incorporated     | Redmond  |
| Nikoma of America      | Ballard  |
| Shinoman, Incorporated | Bellevue |
+------------------------+----------+
(3 rows)

  2. Show me an alphabetized list of products with a retail price of $125.00 or more.

    +----------------------------------+-------------+
| ProductName                      | RetailPrice |
+----------------------------------+-------------+
| AeroFlo ATB Wheels               |      189.00 |
| Cosmic Elite Road Warrior Wheels |      165.00 |
| Cycle-Doc Pro Repair Stand       |      166.00 |
| Eagle FS-3 Mountain Bike         |     1800.00 |
| Eagle SA-120 Clipless Pedals     |      139.95 |
| Glide-O-Matic Cycling Helmet     |      125.00 |
| GT RTS-2 Mountain Bike           |     1650.00 |
| King Cobra Helmet                |      139.00 |
| Road Warrior Hitch Pack          |      175.00 |
| Trek 9000 Mountain Bike          |     1200.00 |
| Ultimate Export 2G Car Rack      |      180.00 |
| Viscount CardioSport Sport Watch |      179.00 |
| Viscount Mountain Bike           |      635.00 |
+----------------------------------+-------------+
(13 rows)

  3. Which vendors do we work with that don’t have a Web site?

    +------------------------+-------------+
| VendName               | VendWebPage |
+------------------------+-------------+
| Big Sky Mountain Bikes | NULL        |
| Dog Ear                | NULL        |
| Lone Star Bike Supply  | NULL        |
| Sun Sports Suppliers   | NULL        |
+------------------------+-------------+
(4 rows)

Bowling League Database

  1. Give me a list of the tournaments held during September 2012.

    +-------------+-------------------+
| TourneyDate | TourneyLocation   |
+-------------+-------------------+
| 2012-09-04  | Red Rooster Lanes |
| 2012-09-11  | Thunderbird Lanes |
| 2012-09-18  | Bolero Lanes      |
| 2012-09-25  | Imperial Lanes    |
+-------------+-------------------+
(4 rows)

  2. What are the tournament schedules for Bolero, Red Rooster, and Thunderbird Lanes?

    +-------------------+-------------+
| TourneyLocation   | TourneyDate |
+-------------------+-------------+
| Bolero Lanes      | 2012-09-18  |
| Bolero Lanes      | 2012-11-06  |
| Bolero Lanes      | 2013-07-26  |
| Red Rooster Lanes | 2012-09-04  |
| Red Rooster Lanes | 2012-10-23  |
| Red Rooster Lanes | 2013-07-12  |
| Thunderbird Lanes | 2012-09-11  |
| Thunderbird Lanes | 2012-10-30  |
| Thunderbird Lanes | 2013-07-19  |
+-------------------+-------------+
(9 rows)

  3. List the bowlers who live on the Eastside (you know—Bellevue, Bothell, Duvall, Redmond, and Woodinville) and who are on teams 5, 6, 7, or 8. (Hint: Use IN for the city list and BETWEEN for the team numbers.)

    +-----------------+----------------+-------------+--------+
| BowlerFirstName | BowlerLastName | BowlerCity  | TeamID |
+-----------------+----------------+-------------+--------+
| Sarah           | Thompson       | Duvall      |      6 |
| Mary            | Thompson       | Duvall      |      7 |
| William         | Thompson       | Duvall      |      7 |
| John            | Viescas        | Redmond     |      5 |
| Suzanne         | Viescas        | Redmond     |      5 |
| Caleb           | Viescas        | Redmond     |      6 |
| Michael         | Viescas        | Redmond     |      7 |
| Alaina          | Hallmark       | Woodinville |      6 |
| Bailey          | Hallmark       | Woodinville |      8 |
+-----------------+----------------+-------------+--------+
(9 rows)