CPSC340A
Database Systems

Activity 9

Update

Sales Orders Database

  1. Set the retail price of accessories (category = 1) to the wholesale price of the highest-priced vendor plus 35 percent.

    +-------------+--------------+
    | RetailPrice | UpdatedPrice |
    +-------------+--------------+
    |       75.00 |           77 |
    |        7.45 |            8 |
    |       50.00 |           53 |
    |       15.00 |           16 |
    |       55.00 |           56 |
    |       82.00 |           86 |
    |      139.00 |          142 |
    |       45.00 |           48 |
    |       22.00 |           23 |
    |       27.00 |           32 |
    |      166.00 |          185 |
    +-------------+--------------+
    (11 rows changed)

Entertainment Agency Database

  1. Add 0.5 percent to the commission rate of agents who have sold more than $20,000 in engagements. (Hint: Use an aggregate subquery to find those agents HAVING total bookings greater than $20,000.)

    +----------------+---------------------+
    | CommissionRate | NewCommissionRate   |
    +----------------+---------------------+
    |           0.05 | 0.05500000074505806 |
    |          0.045 | 0.05000000178813934 |
    |           0.06 |  0.0649999986588955 |
    +----------------+---------------------+
    (3 rows changed)

School Scheduling Database

  1. Increase the salary of full-time tenured staff by 5 percent. (Hint: Use a subquery in the WHERE clause to find matching staff IDs in the faculty table that have a status of full time and a tenured field value of true, that is, 1 or –1, depending on your database system.)

    +----------+-----------+
    | Salary   | NewSalary |
    +----------+-----------+
    | 44000.00 |     46200 |
    | 53000.00 |     55650 |
    | 45000.00 |     47250 |
    | 49000.00 |     51450 |
    | 44000.00 |     46200 |
    | 60000.00 |     63000 |
    | 45000.00 |     47250 |
    | 45000.00 |     47250 |
    | 50000.00 |     52500 |
    | 57000.00 |     59850 |
    | 48000.00 |     50400 |
    | 60000.00 |     63000 |
    | 50000.00 |     52500 |
    | 52000.00 |     54600 |
    | 60000.00 |     63000 |
    | 60000.00 |     63000 |
    | 45000.00 |     47250 |
    | 60000.00 |     63000 |
    | 48000.00 |     50400 |
    | 45000.00 |     47250 |
    | 56000.00 |     58800 |
    +----------+-----------+
    (21 rows changed)
  2. For all staff in ZIP Codes 98115 and 98125, change the area code to 360.

    +-------------+-------------+
    | StfAreaCode | NewAreaCode |
    +-------------+-------------+
    | 206         | 360         |
    | 206         | 360         |
    | 206         | 360         |
    +-------------+-------------+
    (3 rows changed)

Bowling League Database

  1. Change the name of the‘ Huckleberrys’ bowling team to ‘MantaRays.’

    +--------------+-------------+
    | TeamName     | NewTeamName |
    +--------------+-------------+
    | Huckleberrys | Manta Rays  |
    +--------------+-------------+
    (1 row changed)