Check sibling questions

This question asked in CBSE Delhi, 2010

Consider the following tables STORE and SUPPLIERS and answer (a) and (b) parts of this question:

Question 10 Long Answer 5 - image 1.jpg

Table: STORE

ItemNo Item Scode Qty Rate LastBuy
2005 Sharpener Classic 23 60 8 31-Jun-09
2003 Ball Pen 0.25 22 50 25 01-Feb-10
2002 Gel Pen Premium 21 150 12 24-Feb-10
2006 Gel Pen Classic 21 250 20 11-Mar-09
2001 Eraser Small 22 220 6 19-Jan-09
2004 Eraser Big 22 110 8 02-Dec-09
2003 Ball Pen 0.5 21 180 18 03-Nov-09

Question 10 Long Answer 5 - image 2.jpg

Table: SUPPLIERS

Scode Sname
21 Premium Stationers
23 Soft Plastics
22 Tetra Supply

Question 10 (a)

Write SQL commands for the following statements:

(i) To display details of all the items in the STORE table in ascending order of LastBuy.

Answer:

To display details of all the items in the STORE table in ascending order of LastBuy, the ORDER BY clause followed by the column name ie., LASTBUY should be used in the query.

QUERY: SELECT * FROM STORE ORDER BY LASTBUY;

Question 10 (i) Long Answer 5 - image 2.jpg

(ii) To display ItemNo and Item name of those items from STORE table whose Rate is more than 15 Rupees.

Answer:

To display ItemNo and Item name of those items from STORE table whose Rate is more than 15  Rupees, the WHERE clause followed by the condition ie., RATE>15 should be used in the query.

QUERY: SELECT ITEMNO,ITEM FROM STORE WHERE RATE>15;

Question 10 (ii) Long Answer 5 - image 1.jpg

(iii)To display the details of those items whose supplier code (Scode) is 22 or Quantity in Store (Qty) is more than 110 from the table Store.

Answer:

To display the details of those items whose supplier code (Scode) is 22 or Quantity in Store (Qty) is  more than 110 from the table Store, the WHERE clause followed by condition ie., SCODE=22 OR QTY>110 should be used in the query.

QUERY: SELECT * FROM STORE WHERE SCODE=22 OR QTY>110;

Question 10 (iii) Long Answer 5 - image 1.jpg

Question 10 (b)

Give the output of the following SQL queries:

(i) SELECT COUNT(DISTINCT Scode) FROM STORE;

Answer:

The query will display the number of distinct values in the column SCODE of table STORE.

Question 10 (iv) Long Answer 5 - image 1.jpg

(ii) SELECT Rate* Qty FROM STORE WHERE ItemNo=2004;

Answer:

The query will display the value that is obtained when the values in columns RATE and QTY are multiplied for the record with value 2004 in the column ITEMNO.

Question 10 (vv) Long Answer 5 - image 1.jpg

549 people joined Teachoo Black. What are you waiting for?


Transcript

This question asked in CBSE Delhi, 2010 Consider the following tables STORE and SUPPLIERS and answer (a) and (b) parts of this question: Table: STORE ItemNo Item Scode Qty Rate LastBuy 2005 Sharpener Classic 23 60 8 31-Jun-09 2003 Ball Pen 0.25 22 50 25 01-Feb-10 2002 Gel Pen Premium 21 150 12 24-Feb-10 2006 Gel Pen Classic 21 250 20 11-Mar-09 2001 Eraser Small 22 220 6 19-Jan-09 2004 Eraser Big 22 110 8 02-Dec-09 2003 Ball Pen 0.5 21 180 18 03-Nov-09 Table: SUPPLIERS Scode Sname 21 Premium Stationers 23 Soft Plastics 22 Tetra Supply Question 10 (a) Write SQL commands for the following statements: (i) To display details of all the items in the STORE table in ascending order of LastBuy. Answer: To display details of all the items in the STORE table in ascending order of LastBuy, the ORDER BY clause followed by the column name ie., LASTBUY should be used in the query. QUERY: SELECT * FROM STORE ORDER BY LASTBUY; (ii) To display ItemNo and Item name of those items from STORE table whose Rate is more than 15 Rupees. Answer: To display ItemNo and Item name of those items from STORE table whose Rate is more than 15 Rupees, the WHERE clause followed by the condition ie., RATE>15 should be used in the query. QUERY: SELECT ITEMNO,ITEM FROM STORE WHERE RATE>15; (iii)To display the details of those items whose supplier code (Scode) is 22 or Quantity in Store (Qty) is more than 110 from the table Store. Answer: To display the details of those items whose supplier code (Scode) is 22 or Quantity in Store (Qty) is more than 110 from the table Store, the WHERE clause followed by condition ie., SCODE=22 OR QTY>110 should be used in the query. QUERY: SELECT * FROM STORE WHERE SCODE=22 OR QTY>110; Question 10 (b) Give the output of the following SQL queries: (i) SELECT COUNT(DISTINCT Scode) FROM STORE; Answer: The query will display the number of distinct values in the column SCODE of table STORE. (ii) SELECT Rate* Qty FROM STORE WHERE ItemNo=2004; Answer: The query will display the value that is obtained when the values in columns RATE and QTY are multiplied for the record with value 2004 in the column ITEMNO.

Davneet Singh's photo - Teacher, Engineer, Marketer

Made by

Davneet Singh

Davneet Singh is a graduate from Indian Institute of Technology, Kanpur. He has been teaching from the past 12 years. He provides courses for Maths and Science at Teachoo.