Question 5 - Past Year - 5 Mark Questions - Chapter 9 - Structured Query Language

Transcript

This question asked in CBSE Outside Delhi 2013 Write SQL queries for (a) to (d) on the basis of tables given below: Table: PRODUCTS PID PNAME QTY PRICE COMPANY SUPCODE 101 DIGI CAMERA 14X 120 12000 RENBIX S01 102 DIGI PAD 11i 100 22000 DIGI POP S02 104 PEN DRIVE 16 GB 500 1100 STOREKING S01 106 LED SCREEN 32 70 28000 DISPEXPERTS S02 105 CAR GPS SYSTEM 60 12000 MOVEON S03 Table: SUPPLIERS SUPCODE SNAME CITY S01 GET ALL INC KOLKATA S03 EASY MARKET CORP DELHI S02 DIGI BUSY GROUP CHENNAI Question 5 (i) To display the details of all the products in ascending order of product names (i.e., PNAME). Answer: To display the details of all the products in ascending order of product names (i.e., PNAME), the ORDER BY clause followed by column name ie., PNAME should be used in the query. QUERY: SELECT * FROM PRODUCTS ORDER BY PNAME; Question 5 (ii) To display product name and price of all those products, whose price is in the range of 10000 and 15000 (both values inclusive). Answer: To display product name and price of all those products, whose price is in the range of 10000 and 15000 (both values inclusive), the BETWEEN operator followed by the range ie., 10000 AND 15000 should be used in the query. QUERY: SELECT PNAME,PRICE FROM PRODUCTS WHERE PRICE BETWEEN 10000 AND 15000; Question 5 (iii) To display the number of products, which are supplied by each supplier. i.e., the expected output should be; S01 2 S02 2 S03 1 Answer: To display the number of products, which are supplied by each supplier: COUNT( ) function is used to get the number of products. The GROUP BY clause is used to group the products from the same supplier. QUERY: SELECT SUPCODE,COUNT(PID) FROM PRODUCTS GROUP BY SUPCODE; Question 5 (iv) Obtain the outputs of the following SQL queries based on the data given in tables PRODUCTS and SUPPLIERS above. Answer: The query will display the distinct values in column SUPNO ie., no duplicate values will be displayed. Question 5 (v) Obtain the outputs of the following SQL queries based on the data given in tables PRODUCTS and SUPPLIERS above. (d1) SELECT DISTINCT SUPCODE FROM PRODUCTS; (d2) SELECT MAX (PRICE), MIN (PRICE) FROM PRODUCTS; Answer: The query will display the largest and the smallest value in the column PRICE of table PRODUCTS.