Q No - 5
a. What
is the purpose of ALTER TABLE command in MYSQL? How is it different from UPDATE
command?
b.
Table Traveldetails has 15 records and table Passenger has 5 records in it. Ms.
Nidhi wants to display all information stored in both of these related tables
. She forget to specify the equi-join condition in the query. How many rows
will get displayed on execution of this query?
c.
Consider the Relation Doctor
given below, write command in SQL for (i) to (iv) and output for (v) to (viii)
Relation: Doctor
ID
|
Name
|
Dept
|
Gender
|
Experience
|
ConstFee
|
201
|
R.K. Nath
|
ENT
|
M
|
12
|
300
|
457
|
Mahavir Singh
|
SKIN
|
M
|
500
|
|
365
|
M. Asthana
|
MEDICINE
|
F
|
9
|
250
|
221
|
V. S. Nag
|
ENT
|
M
|
3
|
150
|
122
|
S. P. Sinha
|
NEPHRO
|
F
|
200
|
|
110
|
J. P. Pandey
|
CARDIOLOGY
|
M
|
9
|
500
|
i. To display name of all doctors who are in
Medicine having more than 10 years of experience.
ii. To
display the count of the different departments.
iii. To
display minimum consultation fee of female doctors
iv. To
display name and department of male doctors who has no experience.
v. SELECT AVG(ConstFee)
FROM Doctor WHERE NOT Gender=’F’;
vi. SELECT Count(Experience)
FROM Doctor;
vii. SELECT Name,
Experience FROM Doctor WHERE id BETWEEN
100 AND 200;
viii. SELECT
SUM(ConstFee),MAX( Experience) FROM Doctor;
Q No - 6
Answer
the following question.
a. Write
an SQL query to create the table “peripheral“
with the following structure -
Field
|
Type
|
Constraint
|
P_Id
|
Varchar(5)
|
Primary
Key
|
P_Name
|
Varchar(25)
|
|
Manufacturer
|
Varchar(15)
|
|
Price
|
Integer
|
Must
between 399 and 4999
|
Quantity
|
Integer
|
Not
Null
|
b. In
a database there are two Relations ‘Dress’ and ‘Material’ shown below -
Table/Relation :
Dress
Dcode
|
Description
|
Price
|
MCode
|
LaunchDate
|
1002
|
Trouser
|
999
|
M002
|
|
2006
|
Coat
|
1599
|
M001
|
|
1036
|
Sweater
|
540
|
M001
|
|
1789
|
Frock
|
1950
|
M003
|
Table / Relation:
Material
MCode
|
Type
|
M001
|
Woolen
|
M002
|
Cotton
|
M003
|
Polyester
|
M004
|
Silk
|
M005
|
Denim
|
i) Name the column
which can be made ‘Foreign Key’ from both the tables
ii) To
display the dress details which are made up of woolen?
c.
Consider the tables given below:
Table : STOCK
Itcode
|
Itname
|
Dcode
|
Qty
|
unitpr
|
Stkdate
|
444
|
Drawing Copy
|
101
|
10
|
21
|
|
445
|
Sharpener Camlin
|
102
|
25
|
13
|
|
450
|
Eraser Natraj
|
101
|
40
|
6
|
|
452
|
Gel Pen Montex
|
103
|
80
|
10
|
|
457
|
Geometry Box
|
101
|
65
|
65
|
|
467
|
Parker Premium
|
102
|
40
|
109
|
|
469
|
Office File
|
103
|
27
|
34
|
Table : DEALERS
Dcode
|
Dname
|
Location
|
101
|
Vikash Stationers
|
Lanka Varanasi
|
102
|
Bharat Drawing Emporium
|
Luxa Varanasi
|
103
|
Banaras Books Corporation
|
Bansphatak Varanasi
|
With reference to these tables, write commands in SQL
for (i) and (ii) and output for (iii) below-
(i) To display the amount that has been spent to
purchase Office file & Sharpener camlin.
(ii) To display all the items of Vikash Stationers.
(iii) SELECT DCODE,COUNT(QTY),AVG(UNITPR) FROM STOCK
GROUP BY DCODE;
Q No - 7
a. Name any two web portals involved in
e-Business. And how is it benefited to the company.
b. How
has e-governance benefited the common man?
Name any two sites.
c. Neha works for a Hotel. Sky warts to create controls on a form for the following
function. Choose
appropriate controls from TextField, Label , RadioButton , CheckBox , List ,
ComboBox , Button and write in the third column.
S.No.
|
Controls use to
|
Control
|
1
|
Select room type
|
|
2
|
Enter customers name
|
|
3
|
Enter arrival date
|
|
4
|
To book room
|