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