Please type in data so I can copy/ paste them into word document Use SQl and the
ID: 3574217 • Letter: P
Question
Please type in data so I can copy/ paste them into word document
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.
4. list the order number for each placed by customer number 126 on 10/15/2015.
5. list the number and name of each customer represented by sales rep 30 or sales rep 45.
6. list the item number and description of each item that is not in category PZL.
7. List the item number, description, and number of units on hand for each item that has between 20 and 40 units on hand, including both 20 and 40. do this two ways.
8. list the item number, description, and on-hand value (units on hand * until price) of each item in category TOY. (on-hand value is really units on hand * cost, but there is no COST column in the ITEM table.) Assign the name ON_HAND_VALUE to the computed column.
9. list the item number, description, and on-hand value for each item whose on-hand value is at least $1,500. Assign the name ON_HAND_VALUE to the computed column.
10. use the IN operator to list the item number and description of each item in category GME or PZL.
11. Find the number and name of each customer whose name begins with the letter "C".
12. list all details about all items. order output by description.
13. list all details about all items. order the output by item number within storehouse. (that is, order the output by storehouse and then by item number.)
14. how many customers have balances that are more than their credit limits?
15. find the total of the balances for all customers represented by sales rep 15 with balances that are less than their credit limits.
16. list the item number, description, and on-hand value of each item whose number of units on hand is more than the average number of units on hand for all items.
17. what is the price of the least expensive item in the database?
18. what is the item number, description, and price of the least expensive item in the database?
19. list the sum of the balances of all customers for each sales rep. order and group the results by sales rep number.
20. list the sum of the balances of all customers for each sales rep, but restrict the output to those sales reps for which the sum is more than $5,000 . order the results by sales rep number.
21. list the item number of any item with an unknown description.
22. list the item number and description of all items that are in the PZL or TOY category and contain the word "Set" in the description.
23. TAL Distrutors is considering discounting the price of all items by 10 percent. list the item number, description, and discounted price for all items. use DISCOUNTED_PRICE as the name for the computed column. 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.
4. list the order number for each placed by customer number 126 on 10/15/2015.
5. list the number and name of each customer represented by sales rep 30 or sales rep 45.
6. list the item number and description of each item that is not in category PZL.
7. List the item number, description, and number of units on hand for each item that has between 20 and 40 units on hand, including both 20 and 40. do this two ways.
8. list the item number, description, and on-hand value (units on hand * until price) of each item in category TOY. (on-hand value is really units on hand * cost, but there is no COST column in the ITEM table.) Assign the name ON_HAND_VALUE to the computed column.
9. list the item number, description, and on-hand value for each item whose on-hand value is at least $1,500. Assign the name ON_HAND_VALUE to the computed column.
10. use the IN operator to list the item number and description of each item in category GME or PZL.
11. Find the number and name of each customer whose name begins with the letter "C".
12. list all details about all items. order output by description.
13. list all details about all items. order the output by item number within storehouse. (that is, order the output by storehouse and then by item number.)
14. how many customers have balances that are more than their credit limits?
15. find the total of the balances for all customers represented by sales rep 15 with balances that are less than their credit limits.
16. list the item number, description, and on-hand value of each item whose number of units on hand is more than the average number of units on hand for all items.
17. what is the price of the least expensive item in the database?
18. what is the item number, description, and price of the least expensive item in the database?
19. list the sum of the balances of all customers for each sales rep. order and group the results by sales rep number.
20. list the sum of the balances of all customers for each sales rep, but restrict the output to those sales reps for which the sum is more than $5,000 . order the results by sales rep number.
21. list the item number of any item with an unknown description.
22. list the item number and description of all items that are in the PZL or TOY category and contain the word "Set" in the description.
23. TAL Distrutors is considering discounting the price of all items by 10 percent. list the item number, description, and discounted price for all items. use DISCOUNTED_PRICE as the name for the computed column. Exercises TAL Distributors Use SQL and the TAL Distributors database (see Figure 1-2 in Chapter 1) to complete the following exercises. lf 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. 4. List the order number for each order placed by customer number 126 on 10/15/2015. (Hint If you need help, use the discussion of the DATE data type in Figure 316 in Chapter 3) 5. List the number and name of each customer represented by sales rep 30 or sales rep 45. 6. List the item number and description of each item that is not in category PZL. 7. List the item number, description, and number of units on hand for each item that has between 20 and 40 units on hand, including both 20 and 40. Do this two ways. 8. List the item number, description, and on-hand value (units on hand unit price) of each item in category TOY. (On-hand value is really units on hand cost, bpt there is no coST column in the ITEM table.) Assign the name ON HAND VALUE to the computed column. 9. List the item number, description, and on-hand value for each item whose on-hand value is at least $1,500. Assign the name ON HAND VALUE to the computed column. 10. Use the IN operator to list the item number and description of each item in category GME or PZL. 129 A Guide to SQL MacBook Air
Explanation / Answer
Hi,
there are so many queries. I am giving you answers for top11 questions. For other post the question again.
1. list the item number, description, and price for all items.
SELECT ITEM_NUM, DESCRIPTION,PRICE
FROM ITEM;
2. list all rows and columns for the complete ORDERS table.
SELECT * FROM ORDERS;
3. list the names of customers with credit limits of $10,000 or more.
SELECT CUSTOMER_NAME
FROM CUSTOMERS
WHERE CREDIT_LIMIT>=10000;
4. list the order number for each placed by customer number 126 on 10/15/2015.
SELECT ORDER_NUM
FROM ORDERS
WHERE CUSTOMER_NUM=126 AND ORDER_DATE='10/15/2015';
5. list the number and name of each customer represented by sales rep 30 or sales rep 45.
SELECT CUSTOMER_NUM,CUSTOMER_NAME
FROM CUSTOMERS
WHERE REP_NUM IN (30,40);
6. list the item number and description of each item that is not in category PZL.
SELECT ITEM_NUM, DESCRIPTION
FROM ITEM
WHERE CATEGORY != 'PLZ';
7. List the item number, description, and number of units on hand for each item that has between 20 and 40 units on hand, including both 20 and 40. do this two ways.
SELECT ITEM_NUM, DESCRIPTION, ON_HAND
FROM ITEM
WHERE ON_HAND BETWEEN 20 AND 40;
8. list the item number, description, and on-hand value (units on hand * until price) of each item in category TOY. (on-hand value is really units on hand * cost, but there is no COST column in the ITEM table.) Assign the name ON_HAND_VALUE to the computed column.
SELECT ITEM_NUM, DESCRIPTION,(ON_HAND*PRICE) AS ON_HAND_VALUE
FROM ITEM
WHERE CATEGORY = 'TOY';
9.list the item number, description, and on-hand value for each item whose on-hand value is at least $1,500. Assign the name ON_HAND_VALUE to the computed column.
SELECT ITEM_NUM, DESCRIPTION, PRICE AS ON_HAND_VALUE
FROM ITEM
WHERE PRICE>=1500;
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','PLZ');
11. Find the number and name of each customer whose name begins with the letter "C".
SELECT CUSTOMER_NUM,CUSTOMER_NAME
FROM CUSTOMERS
WHERE CUSTOMER_NAME IS LIKE 'C%';