Homework 3 (due 9/29)
We began talking about SQL (chapter 3, skipping, for the time being, the DDL part; we will get back to it). Page 70 of the book describes one way of getting into the SQL view in Access, here is another: once you have opened the database, select the Create tab on top, click on Query Design, close the Show Table dialog that pops up, right-click on the query window and select SQL view. You can enter SQL into that window. You run it by right-clicking on the window and switching to datasheet view.
Submission: you can submit the homework by hardcopy in class or by sending it to me as an email. For queries, hand in the queries as well as their output. You can copy paste graphical output from Access; you can also use screenshots through CTRL/ALT/PRINT SCREEN. For example, if you were asked to list the names of all departments in the company database, you could submit:
List names of all departments:
Make sure you number your answers.
1. [Henry Book Database - simple queries, 15pt] Write SQL queries to answer the following questions. Run the queries, and submit the queries as well as their output.
- [7pt] List the names (last and first) of all authors.
- [8pt] List the title, type and price of all books.
2. [Henry Book Database, 30pt] Write SQL queries to answer the following questions. Run the queries, and submit the queries as well as their output.
- [5pt] List the names of branches that have more than 10 employees.
- [5pt] List all books (title, price) and the name of the publisher (full name, not the abbreviated form).
- [10pt]List all paperback books available for less than $14.
- [10pt] List all publishers that have published paperback books by Stephen King.
3. [Company Database, 20pt] Write SQL queries to answer the following questions. Run the queries, and submit the queries as well as their output.
- [10pt] List all managers (lname, fname, ssn) that became managers after 1/1/1986. Hint: to specify a date, enclose it in # symbols; e.g. September 22, 2010 is written #9/22/2010# in Access. You can use <, >, <=, >= to compare dates.
- [10pt] Which employees work on projects controlled by the research department? Hint: the department controlling a project is encoded as dnum in the project table.
Last updated: September 22nd, 2010.