We start with the Premiere Products Exercises.
 List the number and name of all sales reps.
SELECT RepNum, LastName, FirstName
FROM Rep;
 List the cmplete Customer table.
SELECT *
FROM Customer
 List the number and name of every customer
represented by sales rep 20.
SELECT CustomerNum, CustomerName
FROM Customer
WHERE RepNum = '20';
 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;
 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;
 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;
 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';
 How many customers have a credit limit of $7,500?
SELECT COUNT(*)
FROM Customer
WHERE CreditLimit = 7500;
 Find the total of the balances for all customers
represented by sales rep 65.
SELECT SUM(Balance)
FROM Customer
WHERE RepNum = '65';
 Give the part number, description, and onhand value
OnHand * Price
for each part in item class AP.
SELECT PartNum, Description, OnHand * Price
FROM Part
WHERE Class = 'AP';
 List all columns and all rows in the
Part table. Sort the results by part description.
SELECT *
FROM Part
ORDER BY Description;
 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;
 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;
 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';
 In the Appliance table, change the description
of part KL62 to "Electric Dryer".
UPDATE Appliance
SET Description='Electric Dryer'
WHERE PartNum='KL62';
 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.
 List the book code and book title for every book.
SELECT BookCode, Title
FROM Book;
 List the complete Publisher table (all rows and all columns).
SELECT *
FROM Publisher;
 list the name of every publisher located in New York.
SELECT PublisherName
FROM Publisher
WHERE City='New York';
 List the name of every publisher not located in New York.
SELECT PublisherName
FROM Publisher
WHERE NOT City='New York';
 List the name of every branch that has at least 10 employees.
SELECT BranchName
FROM Branch
WHERE NumEmployees >= 10;

SELECT BookCode, Title
FROM Book
WHERE Type='HOR';
 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;
 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';
 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;
 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;
 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;
 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');
 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;
 How many books have the type MYS?
SELECT COUNT(*)
FROM BOOK
WHERE Type='MYS';
 Calculate the average price for each type of book.
SELECT AVG(Price), Type
FROM Book
GROUP BY Type;
 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;
 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';
 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;
 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';
 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;
 Use a delete query to delete all books in the Fiction table that have the
publisher code PE.
DELETE
FROM Fiction
WHERE PublisherCode='PE';