This preview has intentionally blurred sections. Sign up to view the full version.View Full Document
Unformatted text preview: . . Fall 2007 CSC/CPE 365: Database Systems Alexander Dekhtyar . . Lab 4 Part II: The Return of Simple Queries Due date: Tuesday, October 23, midnight ! . Note: This is the due date for the entire Lab 4 assignment. Lab Assignment The Task In addition to the queries you wrote for part 1 of the Lab assignment, write, debug and test the SQL statements for the following information needs. The SQL statements can be simply included at the end of the SQL script files you prepared for Part 1 of the lab. STUDENT dataset 1. Find teachers of all students whose first name is ’THEO’ . Output first and last names of the teachers. Ensure that each teacher’s name does not get reported more than once. 2. For each classroom, report the grade it is used for. Sort the output by grade level, then by classroom number. BAKERY dataset 1. Find all purchases made on October 21. For each purchase output the name of the cusomter and the reciept number. 2. Find all items sold on October 3. For each item list the reciept number, the name of the item (flavor, food), and the price. Do not eliminate duplicate tuples. Output the result sorted by flavor, food. 1 3. Find all people who purchased Apple Pie between October 10 and October 20 (inclusive). Report the date of purchase, the name of the customer (first, last). Output the result sorted by date of purchase, then by last name of the customer. 4. Find all purchases (reciepts) which had at least five items in them. Report the Reciept number, date and the name (first, last) of the customer. Sort the output by date, then by last name of the customer 1 5. Find all purchases of items that cost more than $8. For each item, output the date, the item name (flavor, food) and price. Sort the output by date, then by item name (flavor, then food).output by date, then by item name (flavor, then food)....
View Full Document
- Spring '08
- Databases, Personal name, Family name, Given name