1.

Consider the following table 'Furniture'. Write SQL commands for the statements (i) to (viii) and write output for SQL queries (ix) and (x).Table: FurnitureFCODENAMEPRICEMANUFDATEWCODE10023Coffee table400019-DEC-2016W0310001Dining table2050012-JAN-2017W0110012Sofa3500006-JUN-2016W0210024Chair250007-APR-2017W0310090Cabinet1800031-MAR-2015W02(i) To display FCODE, NAME and PRICE of items that have Price less than Rs 5,000.(ii) To display NAMES and PRICE of those Furniture Items that have 'table' anywhere in their names.(iii) To display WCode of Furniture Items. There should be no duplicate values.(iv) 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).(v) To display FCODE and NAME of each Furniture Item in descending order of FCODE.(vi) 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).(vii) To display the average PRICE of all the Furniture Items, which are made of Wood with WCODE as W02.(viii) To display WCODE wise, WCODE and the highest price of Furniture Items.(ix) SELECT SUM (PRICE) FROM Furniture WHERE WCODE = 'W03';(x) SELECT COUNT (DISTINCT PRICE) FROM Furniture;

Answer»

(i) SELECT FCODE, NAME, PRICE FROM Furniture WHERE PRICE < 5000;

(ii) SELECT NAME, PRICE FROM Furniture WHERE NAME LIKE '%table%;

(iii) SELECT DISTINCT (WCODE) FROM Furniture;

(iv) SELECT NAME, PRICE + 500 FROM Furniture;

(v) SELECT FCODE, NAME FROM Furniture ORDER BY FCODE DESC;

(vi) SELECT . FROM Furniture WHERE MANUFDATE BETWEEN '01-JAN.2016' AND '15-lUN-2017';

(vii)SELECT AVG (PRICE) FROM Furniture WHERE WCODE = 'W02';

(viii)SELECT WCODE, MAX (PRICE) FROM Furniture GROUP BY WCODE;

(ix) Output 

SUM (PRICE)

6500

(x) Output

COUNT (DISTINCT PRICE)

5



Discussion

No Comment Found

Related InterviewSolutions