Fewpal
0 votes
236 views
in Information Technology by (47.6k points)

Consider the following table Furniture. Write SQL commands for the statements (i) to (viii) and write output for SQL queries (ix) and (x).

FCODENAMEPRICEMANUFDATEWCODE
10023Coffee table400019-DEC-2016W03
10001Dining table2050012-JAN-2017W01
10012Sofa3500006-JUN-2016W02
10024Chair250007-APR-2017W03
10090Cabinet1800031-MAR-2015W02

(i) To display FCODE, NAME and PRICE of items that have Price less than ? 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 ;

Please log in or register to answer this question.

1 Answer

+1 vote
by (52.3k points)

SQL commands for the given statements (i) to (viii) ; 

1. SELECT FCODE, NAME. PRICE FROM Furniture WHERE PRICE < 5000; 

2. SELECT NAME. PRICE FROM Furniture 

3. 

4. WHERE NAME LIKE %table% ; 

5. SELECT DISTINCT WCODE FROM Furniture ; 

6. SELECT NAME. PRICE +500 FROM Furniture ; 

7. SELECT FCODE. NAME FROM Furniture ORDER BY FCODE DESC ; 

8. SELECT * FROM Furniture 

9. 

10. WHERE MANUFDATE BETWEEN ‘01 - JAN - 2016' AND ‘15 - JUN - 2017' ; 

11. SELECT AVG (PRICE) FROM Furniture 

12. 

13. WHERE WCODE = 'W02' ; 

14. SELECT WCODE AS wise, WCODE, MAX (PRICE) FROM Furniture ; 

15. 

16. Output for SQL queries (ix) and (x)

(ix) 

SUM(PRICE)
6500

(x)

COUNT(DISTINCT PRICE)
5

Welcome to Sarthaks eConnect: A unique platform where students can interact with teachers/experts/students to get solutions to their queries. Students (upto class 10+2) preparing for All Government Exams, CBSE Board Exam, ICSE Board Exam, State Board Exam, JEE (Mains+Advance) and NEET can ask questions from any subject and get quick answers by subject teachers/ experts/mentors/students.

Categories

...