Problem 1. (80 points) Consider the relational database defined below: create table professor ( professor_ID varchar ( 1 0 ) , professor_name varchar ( 3 0 ) , ZIP_code char ( 5 ) , salary numeric (10 , 2 ) , primary key ( professor_ID ) ) ; create table student ( student_ID varchar ( 1 0 ) , student_name varchar ( 3 0 ) , ZIP_code char ( 5 ) , primary key ( student_ID ) ) ; create table advisement ( professor_ID varchar ( 1 0 ) , student_ID varchar ( 1 0 ) , start_date date , primary key ( professor_ID , student_ID ) , foreign key ( professor_ID ) references professor , foreign key ( student_ID ) references student ) ;

Express in SQL each of the following queries:

(a) (10 points) Update the salary of each professor as follows. If the current salary is higher than $100,000, increase the salary by 3 percent. Otherwise, increase the salary by 4 percent.

(b) (10 points) Find the ID of every student who is advised by both professors P001 and P002 (i.e., both of the professors whose IDs are P001 and P002, respectively). Use a set operation in your SQL statement.

(c) (10 points) Find every ZIP code with more than 100 students (for each of these ZIP codes, there must be more than 100 students having that ZIP code).

(d) (10 points) Find the IDs of the professors not advising any student (use not in).