.PNG)
Problem Statement:- Query performs on a Hospital Management Database system
Consider the following employee database:-
i)Physician(Phregno,Phname,Phadd,Phtelno)
ii)Patient(Ptname,Ptadd)
iii)Visits(Phregno,Ptname,Date_of_visit,Feescharged)
Given Queries:-
1.Calculate the total fees obtained by the physicians.Also print the registration number,name and
address of the physician.
2.Find the name of the physicians and there telephone numbers who have visited at least 1 patient.
3.Find the name of the physicians with their telephone numbers whom has been visited by only
patient.
4.Find the names of the patients and their address who have visited to more than one physician.
5.Find the registration number of the physician who has been visited by more than one patient in
the month of November.
6.Find the name and registration number of the physician who has not been visited by any of the
patients.
Commands for table creations of the schemas in:-
i) SQL> create table Physician
2 ( phregno varchar2(5),
3 phname varchar2(15) NOT NULL,
4 phadd varchar2(20) NOT NULL,
5 phtelno number(9),
6 PRIMARY KEY(phreg no));
Table Created.
Name Null? Type
----------------------------------------- -------- ------------
PHREGNO NOT NULL VARCHAR2(5)
PHNAME NOT NULL VARCHAR2(15)
PHADD NOT NULL VARCHAR2(20)
PHTELNO NUMBER(9)
SQL> INSERT INTO Physician VALUES (‘P1001’ , ‘Asim Mitra’ , ‘Baranagar’ , 23438976);
1 row created.
SQL> INSERT INTO Physician VALUES (‘P1002’ , ‘Vikram Singh’ , ‘Park Circus’, 21234567);
1 row created.
SQL> INSERT INTO Physician VALUES (‘P1003’ , ‘S.K.Gupta’ , ‘Kankurgachi’ , 27651290);
1 row created.
SQL> INSERT INTO Physician VALUES (‘P1004’ , ‘Aniruddha Roy’ , ‘Salt lake’ , 23376721);
1 row created.
SQL> INSERT INTO Physician VALUES (‘P1005’ , ‘Devi Shetty’ , ‘Alipur’ , 26753400);
1 row created.
SQL> INSERT INTO Physician VALUES (‘P1006’ , ‘Sougata Basu’ , ‘Sealdah’ , 25670911);
PHREG PHNAME PHADD PHTELNO
----- --------------- -------------------- ----------
P1001 Asim Mitra Baranagar 23438976
P1002 Vikram Singh Park Circus 21234567
P1003 S.K.Gupta Kankurgachi 27651290
P1004 Aniruddha Roy Salt lake 23376721
P1005 Devi Shetty Alipur 26753400
P1006 Sougata Basu Sealdah 25670911
1 row created.
ii) SQL> create table Patient
2 ( ptname varchar2(15),
3 ptadd varchar2(20) NOT NULL,
4 PRIMARY KEY(ptname) );
Table Created.
Name Null? Type
----------------------------------------- -------- ------------
PTNAME NOT NULL VARCHAR2(15)
PTADD NOT NULL VARCHAR2(20)
SQL> INSERT INTO Patient VALUES (‘Vikash’ , ‘Salt lake’);
1 row created.
SQL> INSERT INTO Patient VALUES (‘Salim’ , ‘Moulali’);
1 row created.
SQL> INSERT INTO Patient VALUES (‘Abhirup’ , ‘Garia’);
1 row created.
SQL> INSERT INTO Patient VALUES (‘Akash’ , ‘Hedua’);
1 row created.
SQL> INSERT INTO Patient VALUES (‘Shyam’ , ‘Gariahat’);
1 row created.
SQL> INSERT INTO Patient VALUES (‘Jasbir’ , ‘Ultadanga’);
1 row created.
PTNAME PTADD
--------------- ---------
Vikash Salt lake
Salim Moulali
Abhirup Garia
Akash Hedua
Shyam Gariahat
iii) SQL> create table Visits
2 ( phregno varchar2(5),
3 ptname varchar2(15),
4 date_of_visit date,
5 feescharged number(5) NOT NULL,
6 PRIMARY KEY(phregno,ptname,date_of_visit),
7 FOREIGN KEY (phregno) references Physician,
8 FOREIGN KEY (ptname) references Patient );
Table Created.
Name Null? Type
----------------------------------------- -------- -------------
PHY_NO NOT NULL NUMBER(5)
P_NAME NOT NULL VARCHAR2(20)
DATE_OF_VISIT NOT NULL VARCHAR2(12)
FEES NUMBER(4)
SQL> INSERT INTO Visits VALUES (‘P1003’ , ‘Jasbir’ , ’23-Mar-09’ , 450);
1 row created.
SQL> INSERT INTO Visits VALUES (‘P1001’ , ‘Shyam’ , ’29-Jul-09’ , 700);
1 row created.
SQL> INSERT INTO Visits VALUES (‘P1001’ , ‘Akash’ , ’05-May-09’ , 300);
1 row created.
SQL> INSERT INTO Visits VALUES (‘P1002’ , ‘Vikash’ , ’02-Apr-09’ , 650);
1 row created.
SQL> INSERT INTO Visits VALUES (‘P1002’ , ‘Salim’ , ’01-Jan-09’ , 700);
1 row created.
SQL> INSERT INTO Visits VALUES (‘P1005’ , ‘Jasbir’ , ’20-Nov-09’ , 800);
1 row created.
SQL> INSERT INTO Visits VALUES (‘P1005’ , ‘Vikash’ , ’10-Nov-09’ , 800);
1 row created.
SQL> INSERT INTO Visits VALUES (‘P1005’ , ‘Akash’ , ’25-Dec-09’ , 650);
1 row created.
SQL> INSERT INTO Visits VALUES (‘P1004’ , ‘Salim’ , ’17-Oct-09’ , 500);
1 row created.
SQL> INSERT INTO Visits VALUES (‘P1004’ , ‘Salim’ , ’17-Jun-09’ , 800);
1 row created.
PHREG PTNAME DATE_OF_V FEESCHARGED
----- --------------- --------- -----------
P1001 Shyam 29-JUL-09 700
P1001 Akash 05-MAY-09 300
P1002 Vikash 02-APR-09 650
P1002 Salim 01-JAN-09 700
P1005 Vikash 10-NOV-09 800
P1004 Salim 17-OCT-09 500
P1004 Salim 17-JUN-09 800
Solution of Queries:-
Query Number 1 :
Calculate the total fees obtained by the physicians.Also print the registration number,name and
address of the physician.
SQL Statement
SQL> select v.phregno “REGIS”, phname “NAME”, phadd “ADDRESS”, sum(feescharged) “TOTAL
2 FEES”
3 from Physician p,Visits v
4 where p.phregno=v.phregno
5 group by v.phregno,phname,phadd;
Output to Query number 1
REGIS NAME ADDRESS TOTAL FEES
-------- -------------------------- ------------------------- ----------------
P1001 Asim Mitra Baranagar 1000
P1002 Vikram Singh Park Circus 1350
P1003 S.K.Gupta Kankurgachi 450
P1004 Aniruddha Roy Salt lake 1300
P1005 Devi Shetty Alipur 2250
Query Number 2 :
Find the name of the physicians and there telephone numbers who have visited at least 1 patient.
SQL Statement
SQL> select phname,phtelno
2 from Physician
3 where phregno in (select phregno
4 from Visits
5 group by phregno
6 having count(ptname)>=(select count(ptname)
7 from Visits
8 group by(phregno)));
Output to Query number 2
PHNAME PHTELNO
-------------------------- --------------
Asim Mitra 23438967
Vikram Singh 21234567
S.K.Gupta 27651290
Aniruddha Roy 23376721
Devi Shetty 26753400
Query Number 3 :
Find the name of the physicians with their telephone numbers whom has been visited by only patient.
SQL Statement
SQL> select phname,phtelno,count(v.phregno)
2 from Physician p, Visits v
3 where p.phregno=v.phregno
4 group by phname,phtelno
5 having count(v.phregno)=1;
Output to Query number 3
PHNAME PHTELNO COUNT(V.PHREGNO)
-------------------- -------------- -----------------------------
S.K.Gupta 27651290 1
Query Number 4 :
Find the names of the patients and their address who have visited to more than one physician.
SQL Statement
SQL> select t.ptname,t.ptadd,count(v.phregno)
2 from Patient t, Visits v
3 where t.ptname=v.ptname
4 group by t.ptname,t.ptadd
5 having count(v.phregno) > 1;
Output to Query number 4
PTNAME PTADD COUNT(V.PHREGNO)
----------------------------- ------------------------ ----------------------------
Akash Hedua 2
Jasbir Ultadanga 2
Salim Moulali 3
Vikash Salt lake 2
Query Number 5 :
Find the registration number of the physician who has been visited by more than one patient in
the month of November.
SQL Statement
SQL> select v.phregno,count(v.ptname)
2 from Visits v, Physician p
3 where p.phregno=v.phregno and
4 to_char(v.date_of_visit, ’MON’)=’NOV’
5 group by v.phregno
6 having count(v.ptname) > 1;
Output to Query number 5
PHREG COUNT(V.PTNAME)
--------- --------------------------
P1001 2
Query Number 6 :
Find the suppliers who live in the same city as parts.
SQL Statement
SQL> select p.phregno, p.phnname
2 from Physician p
3 where p.phregno not in (select v.phregno
4 from Visits v);
Output to Query number 6
PHREG PHNAME
--------- -------------------------
P1006 Sougata Basu
Discussion-
The above database created is used to find out different information about a hospital’s physicians, patients and visits.
We have solved some given queries using this database, some of which have alternative methods.
Example:-
Query no. 2 as written above to find the name of the physicians and there telephone numbers who have visited at least 1 patient can also be done by selecting physicians except physicians who have record in physician table but not in visits table( on assumption that physicians who did not visited any patient will not get entered in visits table).
We can write this as follows:-
“select phname,phtel_no from physician where phregno not in(select phregno from physician minus select phregno from visits);”
This would also give us the same output we desire.
Back to main directory: Software Practical | Structured Query Language | DBMS
