1. (50 points) Consider the following schema:

   department ( name, chair )
   professor ( ssn, name, employer )
   student ( SID, name, dept )
   course ( name, offered_by )
   section ( course, number, taught_by, credits )
   enrollment ( course, number, student )
   

   that has the following foreign keys:

   department.chair → professor.ssn
   professor.employer → department.name
   student.dept → department.name
   course.offered_by → department.name
   section.course → course.name
   section.taught_by → professor.ssn
   enrollment.[course,number] → section.[course,number]
   enrollment.student  → student.SID
   

   Based on this schema, express the following queries in SQL (10 points for each query).
   1. print the course names taught by professor John Smith.
   2. print the student names who are enrolled in cse3330, section 1 (that is, the course name is "cse3330" and the section number is 1).
   3. print the professor names who teach cse3330.
   4. print the course names and section numbers enrolled by the student Mary Jones.
   5. print the course names and section numbers of all sections that are taught by a professor who is not employed by the department that offers this course.

2. (50 points) Consider the following relational schema:

   supplier ( sid, name, city )
   customer ( cid, name, city )
   product ( pid, name, category )
   supplies ( sid, pid, quantity, price )
   sales ( cid, pid, date, month, year, sale )
   

   with the following foreign keys:

   supplies.sid → supplier.sid
   supplies.pid → product.pid
   sales.cid → customer.cid
   sales.pid → product.pid
   

   Based on this relational schema, express the following queries in SQL (10 points for each query).
   1. Print the names of the customers who live in Arlington.
   2. Print the names and the prices of products supplied by Arlington suppliers.
   3. Print the names of the customers who bought at least one product in 2012 that has sale price greater than 100$.
   4. Print the names of the customers who bought products in the categories of electronics or home appliances (or both) in 2012.
   5. Print the names and the prices of all products supplied by COSTCO and sold to the customer John Smith.

How to Submit the Homework

You need to submit your homework solution online using the following web form. Acceptable document formats are: plain text, Microsoft word document, PDF, and postscript. You may submit it as many times as you like, but only the most recently submitted file will be retained and evaluated. Not that, this time we will not accept any email or hardcopy submission. If you cannot login or have a problem submitting the homework using this form, ask the GTA for help.