Database Reference
In-Depth Information
Premiere Products Exercises: Relational Algebra
In the following exercises, you will use the data in the Premiere Products database shown in Figure 2-1. In each
step, indicate how to use relational algebra to obtain the desired results.
69
1. List the number and name of all sales reps.
2. List all information from the Part table for part FD21.
3. List the order number, order date, customer number, and customer name for each order.
4. List the order number, order date, customer number, and customer name for each order placed by any cus-
tomer represented by the sales rep whose last name is Kaiser.
5. List the number and date of all orders that were placed on 10/20/2013 or that were placed by a customer whose
rep number is 20.
6. List the number and date of all orders that were placed on 10/20/2013 by a customer whose rep number is 20.
7. List the number and date of all orders that were placed on 10/20/2013 but not by a customer whose rep number
is 20.
Henry Books Case
Ray Henry knows that being able to run queries is one of the most important benefits of using a DBMS. Now that he
has created his database, he is eager to obtain answers to a variety of questions. In the following exercises, you will
use the data in the Henry Books database shown in Figures 1-15 through 1-18 in Chapter 1. (If you use a computer
to complete these exercises, use a copy of the Henry Books database so you will still have the original data when
you complete Chapter 3.) In each step, use QBE to obtain the desired results. You can use the query feature in a
DBMS to complete the exercises using a computer, or you can simply write a description of how you would com-
plete the task. Check with your instructor if you are uncertain about which approach to take.
1. List the name of each publisher that's not located in New York.
2. List the title of each book published by Penguin USA.
3. List the title of each book that has the type MYS.
4. List the title of each book that has the type SFI and that is in paperback.
5. List the title of each book that has the type PSY or whose publisher code is JP.
6. List the title of each book that has the type CMP, HIS, or SCI.
7. How many books have a publisher code of ST or VB?
8. List the title of each book written by Dick Francis.
9. List the title of each book that has the type FIC and that was written by John Steinbeck.
10. For each book with coauthors, list the title, publisher code, type, and author names (in the order listed on the
cover).
11. How many book copies have a price that is greater than $20 but less than $25?
12. List the branch number, copy number, quality, and price for each copy of The Stranger.
13. List the branch name, copy number, quality, and price for each copy of Electric Light.
14. For each book copy with a price greater than $25, list the topic's title, quality, and price.
15. For each book copy available at the Henry on the Hill branch whose quality is excellent, list the book's title and
author names (in the order listed on the cover).
16. Create a new table named FictionCopies using the data in the BookCode, Title, BranchNum, CopyNum, Quality,
and Price columns for those books that have the type FIC.
17. Ray Henry is considering increasing the price of all copies of fiction books whose quality is excellent by 10%.
To determine the new prices, list the book code, title, and increased price of every book in the FictionCopies
table. (Your computed column should determine 110% of the current price, which is 100% plus a 10% increase.)
