We are using, Oracle Database 11g Express Edition.

use SQL and the TAL Distrutors database (see figure 1-2 in chapter 1) to complete the following exercises. if directed to do so by your instructor, use the information provided with the chapter 3 exercises to print your output or save it to a document.

1. For each order, list the order number and order date along with the number and name of the customer that placed the order.

2. For each order placed on October 15, 2015, list the order number along with the number

and name of the customer that placed the order.

3. For each order, list the order number, order date, item number, number of units ordered, and

quoted price for each order line that makes up the order.

4. Use the IN operator to find the number and name of each customer that placed an order

on October 15, 2015.

5. Repeat Exercise 4, but this time use the EXISTS operator in your answer.

6. Find the number and name of each customer that did not place an order on

October 15, 2015.

7. For each order, list the order number, order date, item number, part description, and

category for each item that makes up the order.

8. Repeat Exercise 7, but this time order the rows by category and then by order number.

9. Use a subquery to find the rep number, last name, and first name of each sales rep who represents

at least one customer with a credit limit of $10,000. List each sales rep only once

in the results

10. Repeat Exercise 9, but this time do not use a subquery.

11. Find the number and name of each customer that currently has an order on file for a

Rocking Horse.

12. List the item number, description, and category for each pair of items that are in the same category (For example, one such pair would be item CD33 and part DL51, because the category for both items is TOY.)

13. List the item number and order date for each order placed by the customer named

Johnson's Department Store. (Hint: To enter an apostrophe (single quotation mark) within

a string of characters, type two single quotation marks.)

14. List the order number and order date for each order that contains an order line for a Fire Engine

15. List the order number and order date for each order that either was placed by Almondton General Store or that contains an order line for a Fire Engine

16. List the order number and order date for each order that was placed by Almondton General Store and that contains an order line for a Fire Engine

17. List the order number and order date for each order that was placed by Almondton General Store but that does not contain an order line for a Fire Engine

18. List the item number, description, unit price, and category for each item that has a unit price greater than the unit price of every item in category GME. Use either the ALL or ANY operator in your query. (Hint: Make sure you select the correct operator.)

19. For each item, list the item number, description, units on hand, order number, and number of units ordered. All items should be included in the results. For those items are are currently not on order, the order number and number of units ordered should be left blank. Order the results by item number.

20. If you used ALL in Exercise 18, repeat the exercise using ANY. If you used ANY, repeat the exercise using ALL, and then run a new command. What question does the new command answer?

51623 FH24 51623 KD34 51625 MTO3 ITEM ITEM NUM DESCRIPTION ON HAND CATEGORY STORE HOUSE PRICE Patience 9 GME $22.99 Skittles BR23 $29.99 1 CD33 Wood Block Set (48 piece) 36 TOY Classic Railway Set DL51 12 TOY $107.95 s3195 DR67 Giant Star Brain Teaser 24 PZL DW23 Mancala. 40 GME 3 sso 00 FDI Rocking Horse 8 TOY $124.95 L1 3895 FI124 65 PZL Puzzle Gift Set KA12 Cribbage Set 60 PZL Pentomino es Brain Teaser $14.95 110 GME K178 $10.95 Pick Up Sticks MT03 45 PZL Zauber kasten Brain Teaser $119.75 NLA% Wood Block set (62 piece) 32 TOY 75 GME TW3S 30 TOY FIGURE 1-2 Sample data for TAL Distributors (continued)