We talked more about set operations in SQL; after the midterm we will talk about functions and grouping.

1.  [Company SQL]

   Write SQL for the following queries, and list the output

• [10pt] List employees whose supervisor is younger than they are.
• [15pt] List managers who are the oldest employees in their department.
• [15pt] List all employees that have a dependent spouse of the same sex and a dependent child of the opposite sex. Hint: break this up into pieces you can solve first.

2. [Henry Books]  Write SQL for the following queries, and list the output

• [10pt] List authors who have published a book as both paperback and hardcover. Hint: assume two books are the same if they have the same title and author. There is no such book in the database.
• [15pt] List authors who have not coauthored a book. Hint: a coauthor need not necessarily have a sequence number of 2.

3. [Extra Credit]

    Write SQL for the following query in the company world:

• List the names of employees that work on all department 5 projects.

Marcus Schaefer
Last updated: October 13th, 2010.