Bright Tutorials Bright Tutorials
CBSE Class 11 Informatics Practices Question 61 of 87

Structured Query Language (SQL) — Question 22

Back to all questions
22
Question

Question 16

Consider the following tables STORE and SUPPLIERS. Write SQL commands for the statements (i) to (iii) and give the output for SQL query (iv).

Table: STORE

ItemNoItemScodeQtyRateLastBuy
2005Sharpener Classic236082009-06-31
2003Ball Pen 0.252250252010-02-01
2002Gel Pen Premium21150122010-02-24
2006Gel Pen Classic21250202009-03-11
2001Eraser Small2222062009-01-19
2004Eraser Big2211082009-12-02
2009Ball Pen 0.521180182009-11-03

Table: SUPPLIERS

ScodeSname
21Premium Stationery
23Soft Plastics
22Tetra Supply

(i) To display details of all the items in the Store table.

(ii) To display ItemNo and item name of those items from store table whose rate is more than 15.

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

(iv) SELECT Rate*Qty FROM STORE WHERE Itemno = 2004;

Answer

(i)

SELECT * FROM STORE;
Output
+--------+-------------------+-------+-----+------+------------+
| ItemNo | Item              | Scode | Qty | Rate | LastBuy    |
+--------+-------------------+-------+-----+------+------------+
|   2001 | Eraser Small      |    22 | 220 |    6 | 2009-01-19 |
|   2002 | Gel Pen Premium   |    21 | 150 |   12 | 2010-02-24 |
|   2003 | Ball Pen 0.25     |    22 |  50 |   25 | 2010-02-01 |
|   2004 | Eraser Big        |    22 | 110 |    8 | 2009-12-02 |
|   2005 | Sharpener Classic |    23 |  60 |    8 | 2009-06-30 |
|   2006 | Gel Pen Classic   |    21 | 250 |   20 | 2009-03-11 |
|   2009 | Ball Pen 0.5      |    21 | 180 |   18 | 2009-11-03 |
+--------+-------------------+-------+-----+------+------------+

(ii)

SELECT ItemNo, Item FROM STORE WHERE Rate > 15;
Output
+--------+-----------------+
| ItemNo | Item            |
+--------+-----------------+
|   2003 | Ball Pen 0.25   |
|   2006 | Gel Pen Classic |
|   2009 | Ball Pen 0.5    |
+--------+-----------------+

(iii)

SELECT * FROM STORE WHERE Scode = 22 OR Qty > 110;
Output
+--------+-----------------+-------+-----+------+------------+
| ItemNo | Item            | Scode | Qty | Rate | LastBuy    |
+--------+-----------------+-------+-----+------+------------+
|   2001 | Eraser Small    |    22 | 220 |    6 | 2009-01-19 |
|   2002 | Gel Pen Premium |    21 | 150 |   12 | 2010-02-24 |
|   2003 | Ball Pen 0.25   |    22 |  50 |   25 | 2010-02-01 |
|   2004 | Eraser Big      |    22 | 110 |    8 | 2009-12-02 |
|   2006 | Gel Pen Classic |    21 | 250 |   20 | 2009-03-11 |
|   2009 | Ball Pen 0.5    |    21 | 180 |   18 | 2009-11-03 |
+--------+-----------------+-------+-----+------+------------+

(iv) SELECT Rate*Qty FROM STORE WHERE Itemno = 2004;

Output
+------------+
| Rate * Qty |
+------------+
|        880 |
+------------+