Fall Semester 2002

Homework Assignment Three: Solutions

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

```SELECT RepNum, LastName, FirstName
FROM Rep;```

2. List the cmplete Customer table.

```SELECT *
FROM Customer```

3. List the number and name of every customer represented by sales rep 20.

```SELECT CustomerNum, CustomerName
FROM Customer
WHERE RepNum = '20';```

4. 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;```

5. 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;```

6. 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;```

7. List the number and name of all customers represented by Valerie Kaiser.

```SELECT CustomerNum, CustomerName
FROM Customer, Rep
WHERE Customer.RepNum = Rep.RepNum
AND LastName = 'Kaiser'
AND FirstName = 'Valerie';```

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

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

9. Find the total of the balances for all customers represented by sales rep 65.

```SELECT SUM(Balance)
FROM Customer
WHERE RepNum = '65';```

10. Give the part number, description, and on-hand value
OnHand * Price
for each part in item class AP.

```SELECT PartNum, Description, OnHand * Price
FROM Part
WHERE Class = 'AP'; ```

11. List all columns and all rows in the Part table. Sort the results by part description.

```SELECT *
FROM Part
ORDER BY Description;```

12. List all columns and all rows in the Part table. Sort the results by part number within item class.

```SELECT *
FROM Part
ORDER BY Class, PartNum;```

13. List the item class and the sum of the value of parts on hand. Group the results by item class.

```SELECT Class, SUM(OnHand)
FROM Part
GROUP BY Class;```

14. Create a new table named Appliance to contain the columns PartNum, Description, OnHand, Warehouse, and Price for all rows in which the item class is AP.

```SELECT PartNum, Description, OnHand, Warehouse, Price
INTO Appliance
FROM Part
WHERE Class='AP';```

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

```UPDATE Appliance
SET Description='Electric Dryer'
WHERE PartNum='KL62';```

16. In the Applicance table, delete every row in which the warehouse number is 3.

```DELETE
FROM Appliance
WHERE Warehouse='3'```
Here's 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 complete Publisher table (all rows and all columns).

```SELECT *
FROM Publisher;
```
3. list the name of every publisher located in New York.

```SELECT PublisherName
FROM Publisher
WHERE City='New York';```
4. List the name of every publisher not located in New York.

```SELECT PublisherName
FROM Publisher
WHERE NOT City='New York';```
5. List the name of every branch that has at least 10 employees.

```SELECT BranchName
FROM Branch
WHERE NumEmployees >= 10;```
6. ```SELECT BookCode, Title
FROM Book
WHERE Type='HOR';```
7. List the book code and title of every book that has the type HOR and that has the publisher code PE.

```SELECT BookCode, Title
FROM Book
WHERE Type='HOR'
AND Paperback;```
8. List the book code and title of every book that has the type HOR or that has the publisher code PE.

```SELECT BookCode, Title
FROM Book
WHERE Type='HOR'
OR PublisherCode='PE';```
9. 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;```
10. List the book code and title of every book that has the type MYS and a price of less than \$20.

```SELECT BookCode, Title
FROM Book
WHERE Type='MYS'
AND Price<20;```
11. Customer that are part of a special program get a 15% discount off regular book prices. To determine the discounted prices, list the book code, title, and discounted price of every book. (Your calculated column should calculate 85% of the current price, which is 100% less a 15% discount).
```SELECT BookCode, Title, .85 * Price AS DiscountedPrice
FROM Book;```
12. 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');```
13. List the book code, title, and publisher code for all books. Sort the results by title within publisher code.

```SELECT BookCode, Title, PublisherCode
FROM Book
ORDER BY PublisherCode, Title;```
14. How many books have the type MYS?

```SELECT COUNT(*)
FROM BOOK
WHERE Type='MYS';```
15. Calculate the average price for each type of book.

```SELECT AVG(Price), Type
FROM Book
GROUP BY Type;```
16. For every book, list the book code, book title, publisher code, and publisher name.

```SELECT BookCode, Title, Book.PublisherCode, PublisherName
FROM Book, Publisher
WHERE Book.PublisherCode=Publisher.PublisherCode;```
17. 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';```
18. List the book title and book code for every book published by Scribner that has a book price greater than \$10.

```SELECT Title, BookCode
FROM Book, Publisher
WHERE Book.PublisherCode=Publisher.PublisherCode
AND PublisherName='Scribner'
AND Price>10;```
19. Create a new table named Fiction using the data in the BookCode, Title, PublisherCode, and Price columns in the Book table for those books that have the type FIC.

```SELECT BookCode, Title, PublisherCode, Price
INTO Fiction
FROM Book
WHERE Type='FIC';```
20. Use an update query to change the price of any book in the Fiction table with a current price of 8.00 to 8.50.

```UPDATE Fiction
SET Price=8.50
WHERE Price=8.00;```
21. Use a delete query to delete all books in the Fiction table that have the publisher code PE.

```DELETE
FROM Fiction
WHERE PublisherCode='PE';```

Last updated: Sep 23 , 2002 by Adrian German for A201