Homework 4 (due 10/6)
We talked more about SQL, including functions, logic and set operations. You might want to review some Boolean logic and set theory.
1. [Company SQL] Write SQL queries to answer the following questions. Run the queries, and submit the queries as well as their output.
- [5pt] List all employees who were born in January.
- [5pt] List all employees (name, ssn) and their salaries after a 4% salary increase.
- [10pt] List managers who became managers of their department before the age of 45. Hint: don't hardcode 2010-45 = 1965. Use DateDiff.
2. [Henry Books] Write SQL queries to answer the following questions. Run the queries, and submit the queries as well as their output.
- [10pt] List all books (title only) and the cost with taxes (7%) and shipping ($3.99).
- [10pt] List all books (title only) in the mystery and horror genres.
- [10pt] List all books by Peter Straub, unless they are in the horror or mystery genre.
- [10pt] List publishers for which we carry both mystery and science-fiction novels by those publishers. Hint: depending on how you do this, you'll need a set operation not supported in Access. In that case, write out the query and determine the output by hand.
Last updated: September 30th, 2010.