Sample Question No. 5 for IP - Informatics Practices ( AISSCE 2014 ) _ A
[2+2+6]
(a) How can you remove the table from
database? Explain with example.
(b) What are TCL commands? Explain the role
of Commit and Rollback with example.
(c) Consider the table Doctor given below, write command in SQL for (1) to (4) and output
for (5) to (8).
Table : DOCTOR
|
ID
|
Name
|
Dept
|
Gender
|
Experience
|
ConsFee
|
|
201
|
Hemant
|
ENT
|
M
|
12
|
300
|
|
457
|
Vikash
|
SKIN
|
M
|
|
500
|
|
365
|
Shashi
|
MEDICINE
|
F
|
9
|
250
|
|
221
|
Shivam
|
ENT
|
M
|
3
|
150
|
|
122
|
Bhawna
|
NEPHRO
|
F
|
|
200
|
|
110
|
N Khan
|
CARDIOLOGY
|
M
|
9
|
500
|
(1)
To display name of all doctors
who are in Medicine having more than 10 years of experience.
(2)
To display the different
departments.
(3)
To display minimum consultation
fee of female doctors.
(4)
To display name and department
of male doctors who has no experience.
(5)
SELECT AVG(ConsFEE) FROM Doctor
WHERE NOT Gender = ‘F’;
(6)
SELECT Count(Experience) FROM
Doctor;
(7)
SELECT Name, Experience FROM
Doctor WHERE id BETWEEN 100 and 250;
(8)
SELECT SUM(ConstFee),
MAX(Experience) FROM Doctor;