InterviewSolution
Saved Bookmarks
| 1. |
Consider the Table "Infant" shown below :Table: InfantItemCodeItemDatePurchaseUnitPriceDiscount101Frock2015-01-2370010102Cot2015-09-23500025103Soft Toy2016-05-1780010104Baby Socks2014-10-161007105Baby Suit2015-09-205005Note : Discount column stores discount %.Write the commands in SQL for (i) to (viii) and output for (ix) and (x).(i) To display the details about the Cot.(ii) To list the names of items and their unit price that have unit price less than 800 and discount more than 5%.(iii) To list the names of items and their date of purchase that were purchased after 31st December 2015.(iv) To display the number of items that have more than 10% as discount.(v) To display item code and unit price in decreasing order of unit price.(vi) To increase the unit price of each item by 10% of their unit price.(vii) To display the highest unit price of items.(viii) To display the names of items that have 'Baby' anywhere in their item names.(ix) SELECT MID (Item, 1,2) FROM Infant;(x) SELECT AVG(UnitPrice) FROM Infant WHERE DATEPURCHASE > '2015-01-01; |
|
Answer» (i) SELECT* FROM Infant WHERE Item = "Cot"; (ii) SELECT Item FROM Infant WHERE UnitPrice<800 AND Discount>5 ; (iii) SELECT Item,DatePurchase FROM Infant WHERE Datepurchase>'31-10-2015'; (iv) SELECT COUNT(*) FROM Infant WHERE Discount>10; (v) SELECT ItemCode,UnitPrice FROM Infant ORDER BY UnitPrice DESC; (vi) UPDATE Infant SET Unit Price= UnitPrice+ ( (UnitPrice*10)/100) ; (vii) SELECT MAX(UnitPrice) FROM Infant; (viii) SELECT Item FROM Infant WHERE Item LIKE "%Baby%" (ix) Fr Co So Ba Ba (x) 1750 |
|