Tables Questions based on those tables SQL 1. Report the sale date for the sale
ID: 3816729 • Letter: T
Question
Tables
Questions based on those tables SQL
1. Report the sale date for the sale made on January 15, 1995
2. Report the item names, item color and sale date of the Khaki items that got sold.
3. Report the item names and color of the Khaki items that got sold.
4. Write a SQl statement to find how many Khakiitems were sold? Answer should be 4
5. Use EXISTS statement to display the item name and item color of the Khaki color items are sold.
6. Display the item name and color of the Khaki color items that NOT sold
sale *saleno saledate sale text 12oo3-01-15Scruffy Australian-called himself Bruce. 2 2oo3-01-15 Man. Rather fond of hats. 3 20o3-01-15Woman. Planning to row Atlantic-lengthwise! 4, 20o3-01-15Man. Trip to New York-thinks NY is a jungle! 52oo3-01-16 Expedition leader for African safari. item lineitem itemno itemname itemtype itemcolor lineno lineqty lineprice *saleno temno 1 Pocket knife-Nile E Brown 4.5 2 Pocket knife-Avon E Brown 1 2 6 Compass 16 20 4 Geopositioning systemN 3 25 19 Map measure Hat-Polar Explorer C Red 60 White Hat-Polar Explorer C 75 8 Boots-snake proof C Green 10 4 9 Boots-snake proof C Black 10 Safari chair 36 1 10 11 Hammock Khaki 50 40.5 12 Tent-8 person 183 s 12 13 Tent person 60 14 Safari cooking kit E s 1 s 2 15 Pith helmet 16 Pith helmet White 17 Map case Brown 18 Sextant Black 19 Stetson 20 Stetson BrownExplanation / Answer
1. select saledate from sale where saledate='1995-01-15';
2. select i.itemname,i.itemcolor,s.saledate from item as i join lineitem as li on i.itemno=li.itemno
join sale as s on s.saleno=li.saleno where i.itemcolor='Khaki';
3. select i.itemname,i.itemcolor from item as i join lineitem as li on i.itemno=li.itemno join sale as s on s.saleno=li.saleno where i.itemcolor='Khaki';
4.select count(i.itemname) from item as i join lineitem as li on i.itemno=li.itemno join sale as s on s.saleno=li.saleno where i.itemcolor='Khaki';
5.select i.itemname,i.itemcolor from item as i where exists (select li.itemno from lineitem as li where li.itemno=i.itemno And i.itemcolor='Khaki');
6.select i.itemname,i.itemcolor from item as i where i.itemcolor='Khaki' AND i.itemno NOT IN( select li.itemno from lineitem as li);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.