Submit your your queries to the following prompts as a .sql file, with comments, on the course Inquire site.
Entertainment Agency Database
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
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)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)