CPSC340A
Database Systems

Activity 1

Select

Sales Orders Database

  1. Use the source command to load the Sales Orders Database.

  2. Show me all the information on our employees.

    +------------+--------------+-------------+-------------------------+-------------+----------+------------+-------------+----------------+--------------+
    | EmployeeID | EmpFirstName | EmpLastName | EmpStreetAddress        | EmpCity     | EmpState | EmpZipCode | EmpAreaCode | EmpPhoneNumber | EmpBirthDate |
    +------------+--------------+-------------+-------------------------+-------------+----------+------------+-------------+----------------+--------------+
    |        701 | Ann          | Patterson   | 16 Maple Lane           | Auburn      | WA       | 98002      |         253 | 555-2591       | 1958-03-18   |
    |        702 | Mary         | Thompson    | 122 Spring River Drive  | Duvall      | WA       | 98019      |         425 | 555-2516       | 1990-06-21   |
    |        703 | Matt         | Berg        | 908 W. Capital Way      | Tacoma      | WA       | 98413      |         253 | 555-2581       | 1964-11-02   |
    |        704 | Carol        | Viescas     | 722 Moss Bay Blvd.      | Kirkland    | WA       | 98033      |         425 | 555-2576       | 1987-12-23   |
    |        705 | Kirk         | DeGrasse    | 455 West Palm Ave       | San Antonio | TX       | 78284      |         210 | 5552311        | 1985-10-05   |
    |        706 | David        | Viescas     | 16679 NE 42nd Court     | Redmond     | WA       | 98052      |         425 | 555-2661       | 1968-01-01   |
    |        707 | Kathryn      | Patterson   | 554 E. Wilshire Apt. 2A | Seattle     | WA       | 98105      |         206 | 555-2697       | 1982-12-12   |
    |        708 | Susan        | McLain      | 511 Lenora Ave          | Bellevue    | WA       | 98006      |         425 | 555-2301       | 1967-10-15   |
    +------------+--------------+-------------+-------------------------+-------------+----------+------------+-------------+----------------+--------------+
    (8 rows)
  3. Show me a list of cities, in alphabetical order, where our vendors are located, and include the names of the vendors we work with in each city.

    +--------------+------------------------+
    | VendCity     | VendName               |
    +--------------+------------------------+
    | Albany       | ProFormance            |
    | Anchorage    | Big Sky Mountain Bikes |
    | Ballard      | Nikoma of America      |
    | Bellevue     | Shinoman, Incorporated |
    | Dallas       | Armadillo Brand        |
    | El Paso      | Lone Star Bike Supply  |
    | New York     | Dog Ear                |
    | Redmond      | Kona, Incorporated     |
    | Santa Monica | Sun Sports Suppliers   |
    | St. Louis    | Viscount               |
    +--------------+------------------------+
    (10 rows)
  4. Write a SQL script that drops all of the tables in the Sales Orders database, before moving on to the activities for the next database.

Bowling League Database

  1. List all of the teams in alphabetical order.

    +--------------+
    | TeamName     |
    +--------------+
    | Barracudas   |
    | Dolphins     |
    | Huckleberrys |
    | Manatees     |
    | Marlins      |
    | MintJuleps   |
    | Orcas        |
    | Sharks       |
    | Swordfish    |
    | Terrapins    |
    +--------------+
    (10 rows)
  2. Show me all the bowling score information for each of our members.

    +---------+------------+----------+----------+---------------+---------+
    | MatchID | GameNumber | BowlerID | RawScore | HandiCapScore | WonGame |
    +---------+------------+----------+----------+---------------+---------+
    |       1 |          1 |        1 |      146 |           192 |         |
    |       1 |          1 |        2 |      166 |           205 |        |
    |       1 |          1 |        3 |      140 |           171 |         |
    |       1 |          1 |        4 |      146 |           198 |         |
    |       1 |          1 |        5 |      157 |           203 |        |
    |       1 |          1 |        6 |      160 |           198 |         |
    |       1 |          1 |        7 |      170 |           199 |        |
    |       1 |          1 |        8 |      150 |           202 |        |
    |       1 |          2 |        1 |      146 |           192 |         |
    |       1 |          2 |        2 |      135 |           174 |         |
    |       1 |          2 |        3 |      156 |           187 |        |
    |       1 |          2 |        4 |      143 |           195 |        |
    |       1 |          2 |        5 |      149 |           195 |        |
    |       1 |          2 |        6 |      152 |           190 |        |
    |       1 |          2 |        7 |      158 |           187 |        |
    |       1 |          2 |        8 |      136 |           188 |         |
    |       1 |          3 |        1 |      153 |           199 |        |
    |       1 |          3 |        2 |      177 |           216 |        |
    |       1 |          3 |        3 |      191 |           222 |        |
    |       1 |          3 |        4 |      148 |           200 |        |
    |       1 |          3 |        5 |      139 |           185 |         |
    |       1 |          3 |        6 |      142 |           180 |         |
    |       1 |          3 |        7 |      192 |           221 |         |
    .
    .
    .
    (1344)
  3. Show me a list of bowlers and their addresses, and sort it in alphabeti-cal order.

    +----------------+-----------------+-------------------------+-------------+-------------+-----------+
    | BowlerLastName | BowlerFirstName | BowlerAddress           | BowlerCity  | BowlerState | BowlerZip |
    +----------------+-----------------+-------------------------+-------------+-------------+-----------+
    | Black          | Alastair        | 4726 - 11th Ave. N.E.   | Seattle     | WA          | 98105     |
    | Cunningham     | David           | 4110 Old Redmond Rd.    | Redmond     | WA          | 98052     |
    | Ehrlich        | Zachary         | 507 - 20th Ave. E.      | Seattle     | WA          | 98122     |
    | Fournier       | Barbara         | 67 Willow Drive         | Bothell     | WA          | 98123     |
    | Fournier       | David           | 67 Willow Drive         | Bothell     | WA          | 98123     |
    | Hallmark       | Alaina          | Route 2, Box 203B       | Woodinville | WA          | 98072     |
    | Hallmark       | Bailey          | Route 2, Box 203B       | Woodinville | WA          | 98072     |
    | Hallmark       | Elizabeth       | Route 2, Box 203B       | Woodinville | WA          | 98072     |
    | Hallmark       | Gary            | Route 2, Box 203B       | Woodinville | WA          | 98072     |
    | Hernandez      | Kendra          | 47 Harvard Drive        | Kirkland    | WA          | 98033     |
    | Hernandez      | Michael         | 47 Harvard Drive        | Kirkland    | WA          | 98033     |
    | Kennedy        | Angel           | 2957 W 33rd             | Ballard     | WA          | 98099     |
    | Kennedy        | John            | 2957 W 33rd             | Ballard     | WA          | 98099     |
    | Patterson      | Ann             | 16 Maple Lane           | Auburn      | WA          | 98002     |
    | Patterson      | Kathryn         | 16 Maple Lane           | Auburn      | WA          | 98002     |
    | Patterson      | Megan           | 16 Maple Lane           | Auburn      | WA          | 98002     |
    .
    .
    .
    (32 rows)