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


  1. (50 points) Consider the following relational database schema:
    employee ( employee_name, street, city )               key: employee_name
    works ( employee_name, company_name, salary )          key: employee_name, company_name
    company ( company_name, city )                         key: company_name
    manages ( employee_name, manager_name )                key: employee_name, manager_name
    
    Express the following queries in SQL:
    1. Print the names of all employees who work for First Bank.
    2. Print the names, street address, and cities of residence of all employess who work for First Bank and earn more than $50,000.
    3. Print the names of all employees who do not work for First Bank.
    4. Print the names of all employees who live in the same city and on the same street as do their managers.
    5. Print the names of all employees who earn more than every employee of First Bank.

  2. (50 points) Consider the following relational database schema:
    pizza ( pid, pname, size )               key: pid
    soldby ( pid, sname, price )             key: pid, sname
    store ( sname, phone, quality )          key: sname
    
    Express the following queries in SQL:
    1. Print the names and phone numbers of all excellent stores.
    2. Print the price of the 10-inch pepperoni pizza sold by Dominos.
    3. Print the names of all stores that sell pepperoni or Hawaiian pizza.
    4. Print the names of all stores that sell both pepperoni and Hawaiian pizza.
    5. Print the names of all good or excellent stores that sell pepperoni pizza under $8.
    6. Print the names of all stores that do not sell pepperoni pizza.
    7. Print the names of all stores that sell Hawaiian pizza but do not sell pepperoni pizza.

    How to Submit the Homework

    You need to submit your homework solution online using the following web form. We do not accept email or hardcopy submissions. You need a username and password to submit your homework. You should have already received an email from your instructor at your mavs.uta.edu account with your username and password. Acceptable document formats are: Microsoft word/powerpoint document, PDF, postscript, and plain text. You may submit multiple files, if you like, as long as they have different names. These files are automatically uploaded directly into your personal class account for this particular homework, so you don't have to include your name or student ID or homework number in the file name. You may submit your files as many times as you like, but only the most recently submitted files will be retained and evaluated (newly submitted files replace the old files under the same file name). After you submit the files, please double-check that your submitted files are correct by clicking on the Status link and by downloading your submitted homework. If you cannot login or have a problem submitting the homework using this form, ask the GTA for help.

    Submit Homework #1:

    Last modified: 9/15/11 by Leonidas Fegaras