I just need the SQL statements for these questions: 12. list all details about a
ID: 3811444 • Letter: I
Question
I just need the SQL statements for these questions:
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.
Here are the tables, if it helps:
E CUSTOMER ITEM ETORDE ORDER LINE E REP POSTAL CODE BALANCE CREDIT LIMIT REP NUM Click to Add $1,210.25 $7,500.00 15 900 $575.00 $10,000.00 30 90092 $2,345.75 $7,500.00 45 90097 $879.25 $7,500.00 30 90098 90085 $345.00 $5,000.00 45 $5,025.75 $5,000.00 15 90104 90125 $3,456.75 $15,000.00 45 $678.90 $7,500.00 30 90092 $4,234.60 $10,000.00 15 90097 $124.75 $7,500.00 45 90098 $2,475.99 $15,000.00 30 90104 90085 $935.75 $7,500.00 15Explanation / Answer
Please give thumbs up, If it is helpful for you. Thankyou!!
Ans12)
select * from ITEM
order by DESCRIPTION;
Ans13)
select * from ITEM
order by STOREHOUSE,ITEM_NUM;
Ans14)
select count(*)
from CUSTOMER
where BALANCE>CREDIT_LIMIT;
Ans15)
select sum(BALANCE) as total
from CUSTOMER
where REP_NUM=15 and BALANCE<CREDIT_LIMIT;
Ans16)
select ITEM_NUM, DESCRIPTION, ON_HAND
from ITEM
where ON_HAND > (select avg(ON_HAND)
from ITEM);
Ans17)
select min(PRICE)
from ITEM;
Ans18)
select ITEM_NUM,DESCRIPTION,PRICE
from ITEM
where PRICE = (select min(PRICE) from ITEM);
Ans19)
select REP_NUM,sum(BALANCE)
from CUSTOMER
group by REP_NUM;
Ans20)
select REP_NUM,sum(BALANCE)
from CUSTOMER
group by REP_NUM
having sum(BALANCE)<5000
order by REP_NUM;
Ans21)
select ITEM_NUM
from ITEM
where DESCRIPTION = 'unknown'
Ans22)
select ITEM_NUM,DESCRIPTION
from ITEM
where CATEGORY in ('PZL','TOY') and DESCRIPTION like '%Set%' ;
Ans23)
select ITEM_NUM,DESCRIPTION,(PRICE-PRICE/.10) as DISCOUNTED_PRICE
from ITEM;