CSE3330 Homework #1
Due on Tuesday September 25, before midnight
Worth 4% of the final grade


  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.

Submit Homework #1:

Last modified: 09/13/12 by Leonidas Fegaras