Problem 1. (70 points) Consider the relational database defined below:

create table customer ( customer_ID varchar ( 1 0 ) , customer_name varchar ( 3 0 ) , ZIP_code char ( 5 ) , primary key ( customer_ID ) ) ;

create table product ( product_name varchar ( 1 0 ) , price numeric ( 1 0 , 2 ) , primary key ( product_name ) ) ;

create table purchase ( purchase_ID varchar ( 1 0 ) , customer_ID varchar ( 1 0 ) , product_name varchar ( 1 0 ) , quantity int , primary key ( purchase_ID ) , foreign key ( customer_ID ) references customer , foreign key ( product_name ) references product ) ;

Express in SQL each of the following queries:

(a) (10 points) For each ZIP code stored in the customer table, find the number of customers who have purchased product X.

(b) (10 points) For each product whose price is less than $10,000, increase the price of that product by 10 percent. For all other products, increase their prices by 5 percent.

(c) (10 points) Find the names of the products that have not been purchased by anyone whose ZIP code is 12222 (use a set operation or not in).

(d) (10 points) Find the IDs of the customers who have never purchased any product (use a left outer join and is null).

(e) (10 points) Find the names of the products that have been purchased by every customer whose ZIP code is 12222.

(f) (10 points) Find the most selling product(s). Their total purchase quantity must be no less than the total purchase quantity of any other product. 1

(g) (10 points) Write a check condition to ensure that, in the case of product X, each person whose ZIP code is 12222 can purchase only up to 10 items at a time.