In the following exercises, you will use the data in the TAL Distributors database shown in Figure 2-1 in Chapter 2. (If you use a computer to complete these exercises, use a copy of the original TAL Distributors database so you will still have the original data when you complete Chapter 4.) In each step, use SQL to obtain the desired results. You can use a DBMS to complete the exercises using a computer, or you can simply write the SQL command to complete each step. 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 every customer represented by rep 15.

4. List the number and name of all customers that are represented by rep 15 and that have credit limits of $10,000.

5. List the number and name of all customers that are represented by rep 15 or that have credit limits 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 rows in the Item table. Sort the results by Item description.

12. List all columns and all rows in the Item table. Sort the results by item number within category.
13. List the category and the sum of the number of items on hand. Group the results by category, and order the records 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 Step 12. Write the SQL command that you used and then write the alternate command that also would obtain the correct result.
18. How would you modify the query in Step 6 to limit retrieval to only orders that were placed on 10/15/2015?

Figure 2-1

Rep City RepNum LastName FirstName Street Campos Gradey Tian State PostalCode Commission Rate $23,457.50 $41,317.00 $27,789.25 $0.00 Rafael 724 Vinca Dr. 632 Liatris St. Grove CA CA 15 90092 90085 0.06 Megan Hui 30 Fullton 0.08 1785 Tyler Ave. Northfield CA | 267 Oakley St. 45 90098 0,06 60 Sefton Janet Congaree CA 90097 0.06 Customer CustomerNum CustomerName Street City State PostalCode Balance CA CreditLimit RepNum 28 Laketon St. Toys Galore Brookings Direct 452 Columbus Dr. Grove 126 Fullton 90085 $1,210.25 S7,500.00 15 260 CA 90092 $575.00 $10,000,00 30 Congaree CA The Everything 342 Magee St. Shop Johnson's Department 334 90097 $2,345.75 S7,500.00 45 386 124 Main St. Northficld CA 90098 $879.25 $7,500.00 30 Store 440 Grove 3456 Central Ave. Fullton CA 90085 $345.00 $5,000.00 45 Historical Museum Store 502 Cards and More 167 Hale St. Mesa CA 90104 $5,025.75 $5,000.00 15 Almondton 586 3345 Devon Ave. Almondton CA 90125 $3,456.75 $15,000.00 45 General Store Cricket Gift Shop 372 Oxford St. Cress Store 665 Grove CA 90092 $678.90 $7,500.00 30 12 Rising Sun Ave. Congaree 713 CA 90097 $4,234.60 S10,000.00 15 796 Unique Gifts 786 Passmore St. Northfield CA 90098 $124.75 S7,500.00 45 824 Kline's 945 Gilham St. Mesa CA 90104 $2,475.99 $15,000.00 30 893 All Season Gifts 382 Wildwood Ave. Fullton CA 90085 $935.75 S7,500.00 15 Orders OrderNum OrderDate CustomerNum 10/12/2015 OrderLine OrderNum ItemNum NumOrdered QuotedPrice 51608 126 51608 CD33 $86.99 51610 10/12/2015 334 51610 KL78 25 $10.95 10/13/2015 10/13/2015 51613 386 51610 TR40 10 $13.99 51614 260 51613 DL51 $104.95 $124.95 10/15/2015 10/15/2015 51617 586 51614 FD11 1 51619 126 51617 NL89 $115.99 51623 10/15/2015 586 51617 TW35 3. $116.95 51625 10/16/2015 796 $121.95 FD11 DR67 FH24 51619 2. 51623 in $29.95 51623 12 $36.95 51623 KD34 10 $13,10 51625 MT03 8 $45.79 Item ItemNum Description OnHand Category Storehouse Price AH74 Patience 9 GME $22.99 BR23 Skittles 21 GME $29.99 CD33 Wood Block Set (48 piece) Classic Railway Set Giant Star Brain Teaser 36 TOY S89.49 DL51 12 TOY $107.95 DR67 24 PZI. $31.95 DW23 Mancala 40 GME $50.00 FD11 Rocking Horse 8 TOY $124.95 FH24 Puzzle Gift Set 65 PZL. $38.95 KA12 Cribbage Set Pentominoes Brain Teaser 56 GME $75.00 KD34 60 PZL. 2 S14.95 KL78 Pick Up Sticks Zauberkasten Brain 'Teaser Wood Block Set (62 piece) Tic Tac Toc Fire Engine 110 GME S10.95 MT03 45 PZL. $45.79 NI 89 32 TOY 3. $119.75 TR40 75 GME 2 S13.99 TW35 30 TOY 2 $118.95 FIGURE 2-1 Sample data for TAL Distributors