Past Year - 5 Mark Questions

Computer Science - Class 12
Chapter 9 - Structured Query Language

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

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

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.

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.

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;

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

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;

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.

Learn in your speed, with individual attention - Teachoo Maths 1-on-1 Class