Assignment 1

The queries you write should be able to run on MySQL - we will test them. You do not need to provide the output.

To turn in: Simply email your answers to cwilson@cs.uoregon.edu. Plain text preferred - no pdfs.

Write SQL for the queries below against the stores7 database.

1. List all customers who live on a "Street", as opposed to an Avenue, Court, Drive, etc. Thus, the address1 field should end with the substring "Street", or any reasonable abbreviation of that word.
2. Give the names of the cities to which an order was sent in the second half (after the 15th) of the month of May 1998. Use the functions month(attribute), day(attribute), etc.
3. Give the catalog description of everything ordered by "George Watson". (Note the two attribute foreign key.)
4. For each state, give the state name and the name (first and last) of each customer living in that state. If no one lives in that state, it should still be listed but with a blank (null) next to the state name. (Hint: outer join.)

   Write the queries below against the company database.

5. Retrieve the names of all employees in department 5 who work at least 10 hours per week on the 'ProductX' project.
6. Show all pairs of siblings (with the older one listed first).
7. How many projects are run by the Administration department?