Page 57 - IP

PREBOARD
EXAMINATION

5.(a) How can you remove the column of a table? Explain with example. 2

(b) What are TCL commands? Explain the role of Commit and Rollback with example. 2

(c)Consider the table Doctor given below, write command in SQL for (1) to (4) and output for (5) to (8).
6
Table :
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 CARDIOLO M 9 500
GY

(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(ConstFee) FROM Doctor WHERE NOT Gender=’F’;
(6) SELECT Count(Experience) FROM Doctor;
(7) SELECT Name, Experience FROM Doctor WHERE id BETWEEN 100 AND200;
(8)SELECT SUM(ConstFee),MAX( Experience) FROM Doctor;

6.(a) Write MySql Command to create the Table “Hotel” with the following structure: 2

Table: Hotel

Field Name Field Type Constraint

Room_No Integer(3) Primary Key

Type Varchar(20)

Fare Integer(4) Not
Null
Floor Varchar(10)

(b) In a database, there are two table “Company” and “Model” as shown below:

Company