Fall Semester 2004

Due date
Wednesday September 22 (IN LAB)
Late policy
Try to turn everything on time, although late submissions (or re-submissions) will still be accepted.

Work policy
Working in groups is encouraged but please turn in your own version of the assignment.

Also, please make sure you read, understand, and comply with

before turning in your assignment.

Task
Solve the Premiere Products and Henry Books Case exercises (pp. 105-107) in the yellow book.

Your SQL programs should be turned in clearly written on a piece of paper, in lab, to your instructor.

Grading
Feedback will be provided within a week, grades will be posted on-line.

Help Getting Started

Examples from the Premiere Products Exercises.

  1. List the number and name of all sales reps. 

    SELECT RepNum, LastName, FirstName
FROM Rep;

  2. List the number and name of all customers that are represented by sales rep 20 and have credit limits of $7,500. 

    SELECT CustomerNum, CustomerName
FROM Customer
WHERE RepNum = '20'
  AND CreditLimit = 7500;

  3. List the number and name of all customers that are represented by sales rep 20 or have credit limits of $7,500. 

    SELECT CustomerNum, CustomerName
FROM Customer
WHERE RepNum = '20' 
   OR CreditLimit = 7500;

  4. For each order, list the order number, order date, the number of the customer that placed the order, and the name of the customer that placed the order. 

    SELECT OrderNum, OrderDate, Orders.CustomerNum, CustomerName
FROM Orders, Customer
WHERE Orders.CustomerNum = Customer.CustomerNum;

  5. How many customers have a credit limit of $7,500?

    SELECT COUNT(*) 
FROM Customer 
WHERE CreditLimit = 7500;

  6. In the Appliance table, change the description of part KL62 to "Electric Dryer". 

    UPDATE Appliance
SET Description='Electric Dryer'
WHERE PartNum='KL62';
Here are some examples from the second problem, the Henry Books Case.

  1. List the book code and book title for every book. 

    SELECT BookCode, Title
FROM Book;
  2. List the book code, title, and price for each book with a price that is greater than $10 but less than $20. 

    SELECT BookCode, Title, Price
FROM Book
WHERE Price BETWEEN 10 and 20;
  3. List the book code and title of every book that has the type FIC, MYS or ART. 

    SELECT BookCode, Title
FROM Book
WHERE Type IN ('FIC', 'MYS', 'ART');
  4. Calculate the average price for each type of book. 

    SELECT AVG(Price), Type
FROM Book
GROUP BY Type;
  5. For every book published by Lb Books, list the book title and book price. 

    SELECT Title, Price
FROM Book, Publisher
WHERE Book.PublisherCode=Publisher.PublisherCode
AND PublisherName='Lb Books';