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

Last updated at April 16, 2024 by

This question asked in CBSE Outside Delhi 2013

Write SQL queries for (a) to (d) on the basis of tables given below:

Question 5 Long Answer 5 - image 2.jpg

Table: PRODUCTS

PID PNAME QTY PRICE COMPANY SUPCODE
101 DIGITAL CAMERA  14X 120 12000 RENBIX S01
102 DIGITAL 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

Question 5 Long Answer 5 - image 1.jpg

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 Long Answer 5 - image 3.jpg

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(ii) Long Answer 5 - image 3.jpg

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(iii) Long Answer 5 - image 3.jpg

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(iv) Long Answer 5 - image 1.jpg

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.

Question 5(v) Long Answer 5 - image 1.jpg

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.

Ask a doubt
Davneet Singh's photo - Co-founder, Teachoo

Made by

Davneet Singh

Davneet Singh has done his B.Tech from Indian Institute of Technology, Kanpur. He has been teaching from the past 14 years. He provides courses for Maths, Science, Social Science, Physics, Chemistry, Computer Science at Teachoo.