Fall Semester 2002

We start with the Premiere Products Exercises.

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'

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';