Sample Question No. 6 for IP - Informatics Practices ( AISSCE 2014 ) _ B
[2+2+6]
Answer the following questions.
(a) Write an SQL query to creat the table
“Items” with the following structure -
|
Field
|
Type
|
Constraint
|
|
Item_id
|
Varchar(5)
|
Primary Key
|
|
Item_Name
|
Varchar(25)
|
|
|
Manufacturer
|
Varchar(15)
|
|
|
Color
|
Varchar(15)
|
|
|
Price
|
Integer
|
Must between 399 and 4999
|
|
Quantity
|
Integer
|
Not null
|
(b) in a database there are two tables
‘Dress’ and ‘Material’ shown below-
Table : Dress
|
Dcode
|
Description
|
Price
|
MCode
|
LaunchDate
|
|
1002
|
Trouser
|
999
|
M002
|
15-12-2010
|
|
2006
|
Coat
|
1599
|
M001
|
27-09-2009
|
|
1036
|
Sweater
|
540
|
M001
|
11-01-2008
|
|
1789
|
Frock
|
1950
|
M003
|
31-12-2010
|
Table : Material
|
MCode
|
Type
|
|
M001
|
Woolen
|
|
M002
|
Cotton
|
|
M003
|
Polyester
|
|
M004
|
Silk
|
|
M005
|
Denim
|
(i) Name the columns which can be made
‘Foreign Key’ in both the tables.
(ii) Write SQL statement to display the
dress details which are made up of woolen?
(c) Consider the tables give below.
Table
: Books
|
Book_Id
|
Book_Name
|
Author_Name
|
Publisher
|
Price
|
Type
|
Quantity
|
|
B0001
|
Harry Potter
|
J.K. Rolling
|
BPB
|
525
|
Fiction
|
15
|
|
B0013
|
Vision 2020
|
A.P.J.A. Kalam
|
TMH
|
333
|
Informative
|
10
|
|
B0152
|
Let Us C++
|
Y.K.
|
BPB
|
625
|
Text
|
50
|
|
B1102
|
Applied Physics
|
H. C. Verma
|
|
745
|
Text
|
44
|
|
B2314
|
Godan
|
Premchand
|
|
125
|
Novel
|
50
|
Table
: Issued
|
Book_Id
|
DateofIssue
|
Member_ID
|
|
B0152
|
15-12-2010
|
M008
|
|
B2314
|
25-10-2010
|
M112
|
|
B0001
|
31-12-2010
|
M110
|
With reference to these tables, write commands
in SQL for (i) and (ii) and output for (iii) below-
(i) To display the name of the book issued
to member M112.
(ii) To display the type wise no. of books
and total quantity of all type of book.
(iii) SELECT Book_Name, Quantity FROM Books
WHERE publisher IN(‘TMH’,NULL);