Consider the following table ‘Furniture’. Write SQL commands for the statements (i) to (viii) and write output for SQL queries (ix) and (x).
Table: Furniture
| FCODE | NAME | PRICE | MANUFDATE | WCODE |
|---|---|---|---|---|
| 10023 | Coffee table | 4000 | 19-DEC-2016 | W03 |
| 10001 | Dining table | 20500 | 12-JAN-2017 | W01 |
| 10012 | Sofa | 35000 | 06-JUN-2016 | W02 |
| 10024 | Chair | 2500 | 07-APR-2017 | W03 |
| 10090 | Cabinet | 18000 | 31-MAR-2015 | W02 |
- To display FCODE, NAME, and PRICE of items that have Price less than 5,000.
- To display NAMES and PRICE of those Furniture Items that have ‘table’ anywhere in their names.
- To display WCode of Furniture Items. There should be no duplicate values.
- To display the NAMES and PRICE increased by 500.00 of all the furniture items.
(Price should only be displayed as increased; there should be no increase in the data in the table) - To display FCODE and NAME of each Furniture Item in descending order of FCODE.
- To display the details of all the Furniture Items which have Manufacturing date (MANUFDATE) between 01-JAN-2016 and 15-JUN-2017 (inclusive of both the dates).
- To display the average PRICE of all the Furniture Items, which are made of Wood with WCODE as W02.
- To display WCODE wise, WCODE and the highest price of Furniture Items.
SQL Queries:
- (ix) SELECT SUM(PRICE) FROM Furniture WHERE WCODE=’W03’;
- (x) SELECT COUNT(DISTINCT PRICE) FROM Furniture;