GRADE 12 - LAB 15

Write query commands for the following based on tables doctor and doc_dept .

DOCTOR

+------+------------------+--------+-----+------------+----------+

| d_id | dname            | gender | age | mobile     | salary   |

+------+------------------+--------+-----+------------+----------+

| d123 | swati garg       | f      |  35 | 9873214560 | 85000.00 |

| d234 | anirudh          | m      |  40 | 9874563210 | 91000.00 |

| d334 | Deepak           | m      |  45 | 9988774455 | 85400.00 |

| d456 | rupinder kaur    | f      |  32 | 9632587410 | 95850.00 |

| d656 | shailender gupta | m      |  42 | 9102365478 | 98750.00 |

| d734 | yashika lamba    | f      |  39 | 9899552223 | 75300.00 |

+------+------------------+--------+-----+------------+----------+

DOC_DEPT

+------+-------------+---------+----------+

| d_id | department  | charges | opd_days |

+------+-------------+---------+----------+

| d123 | gynaecology |  700.00 | mwf      |

| d234 | cardiology  |  850.00 | mwf      |

| d456 | gynaecology |  700.00 | tts      |

| d656 | cardiology  |  850.00 | mwf      |

| d734 | ent         |  900.00 | tts      |

| d334 | neurology   |  950.00 | tts      |

+------+-------------+---------+----------+

create table doctor(

d_id char(4) primary key,

dname varchar(25),

gender char(1) check(gender in ("M","F")),

age int,

mobile decimal(10),

salary decimal(7,2));

insert into doctor values

("d123","Swati Garg","F",35,9873214560,85000.00),

("d234","Anirudh","M",40,9874563210,91000.00),

("d334","Deepal","M",45,9988774455,85400.00),

("d456","Rupinder Kaur","F",32,9632587910,95850.00),

("d656","Shailendar Gupta","M",42,9102365478,98750.00),

("d734","Yashika Lamba","F",39,9899552223,75300.00);

create table doc_dept(

d_id char(4) primary key references doctor(d_id),

department varchar(20),

charges decimal(10,2),

opd_days varchar(7));

insert into doc_dept values

("d123","Gynaecology",700.00,"mwf"),

("d234","Cardiology",850.00,"mwf"),

("d456","Gynaecology",700.00,"tts"),

("d656","Cardiology",850.00,"mwf"),

("d734","ENT",900.00,"tts"),

("d334","Neurology",950.00,"tts");

i)Display id, name, department, mobile number, and charges from the above table.

ii) Display dname, department name, charges of opd_days mwf

iii)Display the total salary of the doctors department wise

iv)Increase the charges of the neurology, ent department to 20%

v)Show the details of the male doctors whose age is between 40 to 50  and have opd_days on MWF

vi)Display the average age of those departments which are less than 40