THIS IS GOING INTO ACCESS SQL ANSWERS MUST BE IN COMMANDS PL

THIS IS GOING INTO ACCESS SQL, ANSWERS MUST BE IN COMMANDS, PLEASE CHECK AND MAKE SURE YOUR COMMANDS WORK BEFORE SUBMITTING.

Use SQl and the TAL Distributor database(see Figure 1-2 in chapter 1) to complete 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. list the item number, description, and price for all items. 2. list all rows and columns for the complete ORDERS table.

3. list the names of customers with credit limits of $10,000 or more.

6. list the item number and description of each item that is not in category PZL.

10. use the IN operator to list the item number and description of each item in category GME or PZL.

$13.10 KD34. 51623 $45.79 51625 MT03 ITEM ITEM NUM DESCRIPTION ON HAND CATEGORY STOREHOUSE PRICE $22.99 Patience AH74 $29.99 skittles BR23 36 TOY $89.49 Wood Block Set (48 piece) CD33 12 TOY $107.95 Classic Railway Set DL51 $31.95 24 PZL Giant Star Brain Teaser DR67 $50.00 40 GME Mancala $124.95 Rocking Horse S TOY FD11 $38.95 Puzzle Gift Set $75.00 56 GME Cribbage Set KA12 $14.95 60 PZL Pentominoes Brain Teaser KD34 $10.95 110 GME Pick Up Sticks KL 78 $45.79 45 PZL Zauberkasten Brain Teaser $119.75 32 TOY Wood Block Set (62 piece) NL89 $13.99 75 GME Tic Tac Toe TR40 $118.95 300 TOY Fire Engine TW35 FIGURE 1-2 Sample data for TAL Distributors (continued) In the REP table, you see that there are four reps, whose numbers are 15, 30, 45

Solution

1. list the item number, description, and price for all items
here item is the table name.

SELECT item_num, description, price FROM item;

sample output
item_num   description                   price
AH74       patience                   $22.99
BR23       skittles                  $29.99
CD33       wood block set(48 piece)   $89.49
...
and so on

2.list all rows and columns for the complete ORDERS table.
consider if you need to list all rows and colums for \'item\' table with order of item_num,
SELECT * FROM item ORDER BY item_num DESC;

3. list the names of customers with credit limits of $10,000 or more.
here i couldn\'t see any customers table, but you can use below query based on your table name.
consider table name as customers and customer_name as field name which you neeed to fetch

SELECT customer_name, credit FROM customers WHERE credit >= 10000;

sample output:
customer_name   credit
test user1       10000
test user2       10001
test user3       10500
... so on

6. list the item number and description of each item that is not in category PZL.
SELECT item_num, description FROM item WHERE category != \'PZL\'; it returns category which is not equal to PZL.

sample output:
item_num   description                   //category
AH74       patience                            //GME
BR23       skittles                      //GME
CD33       wood block set(48 piece)   //TOY
DL51       classic railway set           //TOY
DW23       Mancala                       //GME
... so on

10. use the IN operator to list the item number and description of each item in category GME or PZL.
SELECT item_num, description FROM item WHERE category IN(\'GME\',\'PZL\'); it returns category which were having category as GME and PZL

sample output:
item_num   description                 //category
AH74       patience                             //GME
BR23       skittles                   //GME
DR67       Giant star brain teaser       //PZL
DW23       Mancala                       //GME
FH24       Puzzle gift set               //PZL
... so on