In the following exercises, you will use the data in the TAL Distributors database shown in Figure 2-1. (If you use a computer to complete these exercises, use a copy of the TAL Distributors 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 complete the task. Check with your instructor if you are uncertain about which approach to take.
1. List the number and name of all customers.
2. List the complete Item table.
3. List the number and name of all customers represented by rep 15.
4. List the number and name of all customers that are represented by rep 15 and that have a credit limit of $10,000.
5. List the number and name of all customers that are represented by rep 15 or that have a credit limit of $10,000.
6. For each order, list the order number, order date, number of the customer that placed the order, and name of the customer that placed the order.
7. List the number and name of all customers represented by Megan Gradey.
8. How many customers have a credit limit of $15,000?
9. Find the total of the balances for all customers represented by rep 30.
10. Give the item number, description, and on-hand value (OnHand * Price) for each item in category GME.
11. List all columns and all records in the Item table. Sort the results by item description.
12. List all columns and all records in the Item table. Sort the results by item number within category.
13. List the category and the sum of the items on hand. Group the results by category.

14. Create a new table named Toy to contain the columns ItemNum, Description, OnHand, and Price for all rows on which the category is TOY.
15. In the Toy table, change the description of item DL51 to €œClassic Train Set.€
16. In the Toy table, delete every row on which the price is greater than $120.
17. There are two ways to create the query in Exercise 11. What are they? Which one did you use?
18. An employee of TAL Distributors created the query shown in Figure 2-45. She wants to list each order and the number of items in the order. Will the query results be correct? If not, how should she modify the query to achieve this result?

Figure 2-1

Figure 2-45

Transcribed Image Text:

Rep State PostalCode Commission Rate RepNum LastName FirstName Street Campos Gradey Tian City Rafael 724 Vinca Dr. Grove CA 90092 $23,457.50 $41,317.00 $27,789.25 $0.00 15 0.06 Megan Hui 632 Liatris St. Fullton CA 30 90085 0.08 1785 Tyler Ave. 267 Oakley St. Northfield Congaree CA 45 90098 0.06 Sefton Janet CA 0.06 60 90097 Customer State PostalCode Balance $1,210.25 Creditlimit RepNum $7,500.00 15 CustomerNum CustomerName Street City Fullton Toys Galore 28 Laketon St. CA 126 90085 Brookings Direct 452 Columbus Dr. Grove CA $575.00 $10,000.00 30 260 90092 342 Mage St. CA 90097 $2,345.75 $7,500.00 45 The Everything Shop Johnson's Department Store Congaree 334 124 Main St. Narthfield CA 90098 $879.25 $7,500.00 30 386 CA Grove 3456 Central Ave. Fullton 90085 $345.00 $5,000.00 45 440 Historical Museum Store CA Cards and More 167 Hale St. Mesa $5,025.75 $5,000.00 15 502 90104 Almondton General Store 3345 Devon Ave. Almondton CA 90125 $3,456.75 $15,000.00 45 586 Cricket Gift Shop 372 Oxford St. Grove CA 90092 665 $678.90 $4,234.60 $7,500.00 30 $10,000.00 15 12 Rising Sun Ave. Congaree Cress Store CA 713 90097 Unique Gifts 786 Passmore St. Northfield CA $124.75 $7,500.00 45 796 90098 945 Gilham St. CA Kline's Mesa $2,475.99 $15,000.00 30 824 90104 All Season Gifts 382 Wildwood Ave. Fullton CA $935.75 $7,500.00 15 893 90085 Orders OrderLine OrderNum ItemNum NumOrdered QuotedPrice OrderNum OrderDate CustomerNum 51608 10/12/2015 CD33 $86.99 126 51608 10/12/2015 $10.95 334 KL78 51610 51610 25 $13.99 10/13/2015 386 51610 TR40 51613 10 DLS1 $104.95 10/13/2015 260 51614 51613 FD11 $124.95 10/15/2015 51617 586 51614 $115.99 10/15/2015 NL89 51619 126 51617 $116.95 10/15/2015 TW35 51623 586 51617 FD11 $121.95 10/16/2015 51625 796 51619 $29.95 DR67 51623 $36.95 FH24 51623 12 $13.10 KD34 51623 10 $45.79 MT03 51625 Item ItemNum Description OnHand Category Storehouse Price 9 GME 21 GME AH74 Patience $22.99 3. BR23 Skittles $29.99 Wood Block Set (48 piece) CD33 36 TOY $89.49 DL51 Classic Railway Set 12 TOY $107.95 DR67 Giant Star Brain Teaser 24 PZL $31.95 Mancala DW23 40 GME $50.00 Rocking Horse Puzzle Gift Set FD11 8 TOY $124.95 FH24 65 PZL $38.95 Cribbage Set Pentominoes Brain Teaser 56 GME $75.00 KA12 KD34 $14.95 60 PZL KL78 Pick Up Sticks $10.95 110 GME МТоз Zauberkasten Brain Teaser 45 PZL. $45.79 Wood Block Set (62 piece) Tic Tac Toe Fire Engine NL89 32 ΤΟΥ $119.75 TR40 75 GME $13.99 TW35 30 ΤΟΥ $118.95 Count query Orderline Orders 9 Ordert kum 9 Ordertkum 9mentum OrdeDate NumOriterad Customentium Quatedhrice Field Ordertum Table Orders Sort: Show temtdum Ordertine Criteria or