CPSC340A
Database Systems

Sales Orders Database

2. 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

2. 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)