Name :- Yash Uday Ringe

Roll No :- 52

Title :- A general hospital consists of a number of specialized wards(such as

Pediatrics, Oncology etc.). Each ward hosts a number of patients who were
admitted on the recommendation of their own doctor and confirmed by a consultant
employed by the hospital. On admission the personal details of every patient are
recorded. A separate register is to be held to store the information of the tests
undertaken and the results of the prescribed treatment. A number of tests may be
conducted for each patient. Each patient is assigned to one leading consultant but
may be examined by another doctor, if required. Doctors are specialists in some
branch of medicine and may be leading consultant for a number of patients, not
necessarily from the same ward
----------------------------------------------------------------------------------------------------

mysql> create table doctor(doctor_name varchar(20),specialization varchar(20),phn_no

int(10),salary int,consultant varchar(20));
Query OK, 0 rows affected, 1 warning (1.11 sec)

mysql> select * from doctor;

+-------------+-----------------+------------+--------+-------------+
| doctor_name | specialization | phn_no | salary | consultant |
+-------------+-----------------+------------+--------+-------------+
| Dr.Onkar | Neurologist | 1234567895 | 200000 | Dr.Kulkarni |
| Dr.Ajinkya | Radiologist | 2222222 | 100000 | Dr.Pawar |
| Dr.Anurag | Radiologist | 33333333 | 80000 | Dr.Pawar |
| Dr.Durgesh | Cardiologist | 444444 | 90000 | Dr.Bhadane |
| Dr.Pravin | Cardiologist | 444444 | 50000 | Dr.Jain |
| Dr.Ram | Psychiatrist | 888888 | 70000 | Dr.shukla |
| Dr.Laxman | Radiologist | 66666 | 60000 | Dr.shukla |
| Dr.Sita | cardiologist | 444555 | 90000 | Dr.verma |
+-------------+-----------------+------------+--------+-------------+
8 rows in set (0.00 sec)
mysql> create table patient(pid int,pname varchar(20),DOB date,phn_no int(10),address
varchar(20),admit_date date,discharge_date date,doctor_name varchar(20),consultant
varchar(20),suffering_from varchar(20),ward varchar(20));
Query OK, 0 rows affected, 1 warning (1.65 sec)

mysql> select * from patient;

+------+-----------------+------------+----------+------------+------------+----------------+-------------+---
----------+----------------+---------------+
| pid | pname | DOB | phn_no | address | admit_date | discharge_date | doctor_name | consultant |
suffering_from | ward |
+------+-----------------+------------+----------+------------+------------+----------------+-------------+---
----------+----------------+---------------+
| 1 | Bruce Banner | 1972-02-11 | 222222 | New York | 2017-05-08 | 2017-05-22 |
Dr.Onkar | Dr.Kulkarni | Braintumor | SpecialWard |

| 2 | Chris Ewans | 2000-01-11 | 33333 | Manchester | 2018-07-04 | 2018-07-22 |

Dr.Pravin | Dr.jain | Corona | SpecialWard |

| 2 | Chris Hemsworth | 2001-02-12 | 44444 | New Jersy | 2017-08-03 | 2017-06-23 |

Dr.Richard | Dr.Stark | Heart Attack | SpecialWard |

| 4 | Charlie Puth | 2003-04-12 | 777777 | Columbus | 2018-05-02 | 2018-05-30 | Dr.Evin |

Dr.Root | spinal Injury | SpecialWard |

| 5 | Kane Williamson | 2007-05-12 | 99999 | Paris | 2017-04-03 | 2017-04-25 | Dr.Evin |

Dr.Root | Braintumor | SpecialWard |

| 6 | Adam levine | 2001-07-12 | 20202020 | Canada | 2017-04-02 | 2017-05-25 | Dr.Evin |

Dr.Root | Braintumor | SpecialWard |

| 7 | Adam Gillchrist | 2007-01-12 | 6666666 | England | 2014-04-02 | 2014-08-25 |

Dr.Evin | Dr.Root | Braintumor | SpecialWard |

| 8 | Linkin Park | 2007-01-12 | 6666666 | New York | 2014-04-02 | 2014-08-25 | Dr.Evin |

Dr.Root | Braintumor | pediatricward |
+------+-----------------+------------+----------+------------+------------+----------------+-------------+---
----------+----------------+---------------+
8 rows in set (0.00 sec)
mysql> create table register(pid int,test_name varchar(20),result varchar(20));

Query OK, 0 rows affected (1.26 sec)

mysql> select * from register;

+------+-----------+---------------+
| pid | test_name | result |
+------+-----------+---------------+
| 1 | CT SCAN | BLOODCLOT |
| 2 | MRI | BLOCKAGE |
| 3 | MRI | CANCER |
| 4 | ECG | BLOCKAGE |
| 5 | MRI | BLOCKAGE |
| 6 | ECG | HEART PROBLEM |
+------+-----------+---------------+
6 rows in set (0.00 sec)

1. Display all name of doctors :

mysql> select doctor_name from doctor;

+-------------+
| doctor_name |
+-------------+
| Dr.Onkar |
| Dr.Ajinkya |
| Dr.Anurag |
| Dr.Durgesh |
| Dr.Pravin |
| Dr.Ram |
| Dr.Laxman |
| Dr.Sita |
+-------------+
8 rows in set (0.00 sec)

2. Display all doctors name starting with P

mysql> select doctor_name from doctor where doctor_name like"___P%";

+-------------+
| doctor_name |
+-------------+
| Dr.Pravin |
+-------------+
1 row in set (0.00 sec)
 3. Display all patients in general ward

mysql> select * from patient where ward="SpecialWard";

+------+-----------------+------------+--------+------------+------------+----------------+-------------+-----
--------+----------------+-------------+
| pid | pname | DOB | phn_no | address | admit_date | discharge_date | doctor_name | consultant |
suffering_from | ward |
+------+-----------------+------------+--------+------------+------------+----------------+-------------+-----
--------+----------------+-------------+
| 1 | Bruce Banner | 1972-02-11 | 222222 | New York | 2017-05-08 | 2017-05-22 |
Dr.Onkar | Dr.Kulkarni | Braintumor | SpecialWard |

| 2 | Chris Ewans | 2000-01-11 | 33333 | Manchester | 2018-07-04 | 2018-07-22 | Dr.Pravin |

Dr.jain | Corona | SpecialWard |

| 2 | Chris Hemsworth | 2001-02-12 | 44444 | New Jersey | 2017-08-03 | 2017-06-23 |

Dr.Richard | Dr.Stark | Heart Attack | SpecialWard |

| 3 | Tony Stark | 2002-03-11 | 55555 | Texas | 2014-07-02 | 2014-07-30 | Dr.James | Dr.Pollard |

spinal Injury | SpecialWard |

| 4 | Charlie Puth | 2003-04-12 | 777777 | Columbus | 2018-05-02 | 2018-05-30 | Dr.Evin |

Dr.Root | spinal Injury | SpecialWard |

| 5 | Kane Williamson | 2007-05-12 | 99999 | Paris | 2017-04-03 | 2017-04-25 | Dr.Evin |

Dr.Root | Braintumor | SpecialWard |
+------+-----------------+------------+--------+------------+------------+----------------+-------------+-----
--------+----------------+-------------+
6 rows in set (0.00 sec)
 4. Display all patients admitted between may to august 2018

mysql> select * from patient where admit_date between "2017-05-22" and "2018-07-30";

+------+-----------------+------------+--------+------------+------------+----------------+-------------+-----
-------+----------------+-------------+
| pid | pname | DOB | phn_no | address | admit_date | discharge_date | doctor_name | consultant |
suffering_from | ward |
+------+-----------------+------------+--------+------------+------------+----------------+-------------+-----
-------+----------------+-------------+
| 2 | Chris Ewans | 2000-01-11 | 33333 | Manchester | 2018-07-04 | 2018-07-22 | Dr.Pravin |
Dr.jain | Corona | SpecialWard |

| 2 | Chris Hemsworth | 2001-02-12 | 44444 | New Jersey | 2017-08-03 | 2017-06-23 |

Dr.Richard | Dr.Stark | Heart Attack | SpecialWard |

| 4 | Charlie Puth | 2003-04-12 | 777777 | Columbus | 2018-05-02 | 2018-05-30 | Dr.Evin |

Dr.Root | spinal Injury | SpecialWard |
+------+-----------------+------------+--------+------------+------------+----------------+-------------+-----
-------+----------------+-------------+
3 rows in set (0.00 sec)

5. Display all patients admitted for more than one month

mysql> select * from patient where datediff(discharge_date,admit_date)>30;

+------+-----------------+------------+----------+---------+------------+----------------+-------------+------
------+----------------+-------------+
| pid | pname | DOB | phn_no | address | admit_date | discharge_date | doctor_name | consultant |
suffering_from | ward |
+------+-----------------+------------+----------+---------+------------+----------------+-------------+------
------+----------------+-------------+
| 6 | Adam levine | 2001-07-12 | 20202020 | Canada | 2017-04-02 | 2017-05-25 | Dr.Evin |
Dr.Root | Braintumor | SpecialWard |
| 7 | Adam Gillchrist | 2007-01-12 | 6666666 | England | 2014-04-02 | 2014-08-25 | Dr.Evin |
Dr.Root | Braintumor | SpecialWard |
+------+-----------------+------------+----------+---------+------------+----------------+-------------+------
------+----------------+-------------+
2 rows in set (0.00 sec)

6. Display all patients treated by "Dr. Evin"

mysql> select * from patient where doctor_name="Dr.Evin";

+------+-----------------+------------+----------+----------+------------+----------------+-------------+-----
-------+----------------+-------------+
| pid | pname | DOB | phn_no | address | admit_date | discharge_date | doctor_name | consultant |
suffering_from | ward |
+------+-----------------+------------+----------+----------+------------+----------------+-------------+-----
-------+----------------+-------------+
| 4 | Charlie Puth | 2003-04-12 | 777777 | Columbus | 2018-05-02 | 2018-05-30 | Dr.Evin |
Dr.Root | spinal Injury | SpecialWard |

| 5 | Kane Williamson | 2007-05-12 | 99999 | Paris | 2017-04-03 | 2017-04-25 | Dr.Evin |

Dr.Root | Braintumor | SpecialWard |
| 6 | Adam levine | 2001-07-12 | 20202020 | Canada | 2017-04-02 | 2017-05-25 | Dr.Evin |
Dr.Root | Braintumor | SpecialWard |

| 7 | Adam Gillchrist | 2007-01-12 | 6666666 | England | 2014-04-02 | 2014-08-25 | Dr.Evin |

Dr.Root | Braintumor | SpecialWard |
+------+-----------------+------------+----------+----------+------------+----------------+-------------+-----
-------+----------------+-------------+
4 rows in set (0.00 sec)

7. Display how many patients admitted in special ward

mysql> select count(*) from patient where ward="SpecialWard";

+----------+
| count(*) |
+----------+
|8|
+----------+
1 row in set (0.01 sec)
 8. Display the name of all cardiologists with their phone no.

mysql> select doctor_name,phn_no from doctor where specialization="cardiologist";

+-------------+--------+
| doctor_name | phn_no |
+-------------+--------+
| Dr.Durgesh | 444444 |
| Dr.Pravin | 444444 |
| Dr.Sita | 444555 |
+-------------+--------+
3 rows in set (0.00 sec)

9.Display name of neurologist with their patient name

mysql> select doctor.doctor_name,
-> patient.pname
-> from doctor INNER JOIN patient ON
-> doctor.doctor_name=patient.doctor_name and doctor.specialization="neurologist";

+-------------+--------------+
| doctor_name | pname |
+-------------+--------------+
| Dr.Onkar | Bruce Banner |
+-------------+--------------+
1 row in set (0.00 sec)

10. Display name of patient along with their consultants

mysql> select pname,consultant from patient;

+-----------------+-------------+
| pname | consultant |
+-----------------+-------------+
| Bruce Banner | Dr.Kulkarni |
| Chris Ewans | Dr.jain |
| Chris Hemsworth | Dr.Stark |
| Tony Stark | Dr.Pollard |
| Charlie Puth | Dr.Root |
| Kane Williamson | Dr.Root |
| Adam levine | Dr.Root |
| Adam Gillchrist | Dr.Root |
+-----------------+-------------+
8 rows in set (0.00 sec)

11. Display test result of patient 'Bruce Banner’'

mysql> select * from register where pid=(select pid from patient where pname="Bruce Banner");

+------+-----------+-----------+
| pid | test_name | result |
+------+-----------+-----------+
| 1 | CT SCAN | BLOOD CLOT |
+------+-----------+-----------+
1 row in set (0.01 sec)

12. Display patient who were discharged on 5th May 2018

mysql> select * from patient where discharge_date="2018-05-30";

+------+--------------+------------+--------+----------+------------+----------------+-------------+----------
--+----------------+-------------+
| pid | pname | DOB | phn_no | address | admit_date | discharge_date | doctor_name |
consultant | suffering_from | ward |
+------+--------------+------------+--------+----------+------------+----------------+-------------+----------
--+----------------+-------------+
| 4 | Charlie Puth | 2003-04-12 | 777777 | Columbus | 2018-05-02 | 2018-05-30 | Dr.Evin |
Dr.Root | spinal Injury | SpecialWard |
+------+--------------+------------+--------+----------+------------+----------------+-------------+----------
--+----------------+-------------+
1 row in set (0.00 sec)
 13. Count no. of patient in pediatric war

mysql> select count(*) from patient where ward="pediatricward";

+----------+
| count(*) |
+----------+
|1|
+----------+
1 row in set (0.00 sec)

14.Create view hospital data on patient table that will contain pid, name
referred by ward

mysql> create view pview as select pid,pname,ward from patient;

Query OK, 0 rows affected (0.03 sec)

15. Display view

mysql> delete from pview where pid=3;

Query OK, 1 row affected (0.01 sec)

mysql> select * from pview;

+------+-----------------+---------------+
| pid | pname | ward |
+------+-----------------+---------------+
| 1 | Bruce Banner | SpecialWard |
| 2 | Chris Ewans | SpecialWard |
| 2 | Chris Hemsworth | SpecialWard |
| 4 | Charlie Puth | SpecialWard |
| 5 | Kane Williamson | SpecialWard |
| 6 | Adam levine | SpecialWard | | 7 |
Adam Gillchrist | SpecialWard | | 8 |
Linkin Park | pediatricward |
+------+-----------------+---------------+
8 rows in set (0.00 sec)

16. Delete two tuples from view

mysql> delete from pview where pid=3;

Query OK, 1 row affected (0.01 sec)

mysql> select * from pview;

+------+-----------------+---------------+
| pid | pname | ward |
+------+-----------------+---------------+ |
1 | Bruce Banner | SpecialWard | | 2 |
Chris Ewans | SpecialWard |
| 2 | Chris Hemsworth | SpecialWard | | 4
| Charlie Puth | SpecialWard | | 5 | Kane
Williamson | SpecialWard | | 6 | Adam
levine | SpecialWard | | 7 | Adam
Gillchrist | SpecialWard | | 8 | Linkin
Park | pediatricward |
+------+-----------------+---------------+ 8
rows in set (0.00 sec)

17. Create index on doctor table

mysql> create index I1 on doctor(doctor_name);

Query OK, 0 rows affected (0.08 sec)

Records: 0 Duplicates: 0 Warnings: 0