Advanced SQL Query - Hospital Management System

Pendahuluan

Berikut ini adalah salah satu Tugas yang dikerjakan dalam Perkuliah STA562-Manajemen Data Statistika yang pada Semester Genap TA 20/21.

Entity Relationship Diagram

Diketahui suatu Rumah Sakit memiliki ER Diagram (Conceptual Model) sebagai berikut :

Hospital Entity Relationship Diagram

Schema Diagram

ER Diagram di atas, diterjemahkan menjadi Schema Diagram sebagai berikut :

Hospital Schema Diagram

Relational Database Schema

Kemudian, diterjemahkan lebih lanjut menjadi Relational Database Schema yang berisi List Entities dan Atributes berikut ini.

Referential integrity constraints pada Hospital relational database schema

SQL Databases

Berikut ini dibuat Sample Databases Hospital

Create Tables

DOCTOR

CREATE TABLE DOCTOR
(   DOCTOR_ID VARCHAR (20) NOT NULL,
    NAME TEXT NOT NULL,
    AGE NUMERIC NOT NULL,
    QUALIFICATION VARCHAR (20) NOT NULL,
    SALARY NUMERIC NOT NULL,
    PRIMARY KEY (DOCTOR_ID));

PATIENTS

CREATE TABLE PATIENTS
(   PATIENT_ID VARCHAR (20) NOT NULL,
    NAME TEXT NOT NULL,
    ADDRESS TEXT NOT NULL,
    AGE NUMERIC,
    APPEARANCE TEXT NOT NULL,
    MARITAL_STATUS CHAR (15) NOT NULL,
    HEIGHT INT NOT NULL,
    WEIGHT INT NOT NULL,
    BLOOD_PRESSURE INT NOT NULL,
    PRIMARY KEY (PATIENT_ID));

ROOM

CREATE TABLE ROOM
(   ROOM_ID VARCHAR (10) NOT NULL,
    RENT NUMERIC NOT NULL,
    CLASS CHAR (20) NOT NULL,
    AVAILABILITY CHAR (20) NOT NULL,
    PRIMARY KEY (ROOM_ID));

TREATS

CREATE TABLE TREATS
(   ID_DOCTOR VARCHAR (20) NOT NULL,
    PATIENT_ID VARCHAR (20) NOT NULL,
    PRIMARY KEY (ID_DOCTOR, PATIENT_ID),
    FOREIGN KEY (PATIENT_ID) REFERENCES PATIENTS (PATIENT_ID),
    FOREIGN KEY (ID_DOCTOR) REFERENCES DOCTOR (DOCTOR_ID));

ROOM FOR PATIENT

CREATE TABLE ROOM_FOR_PATIENT
(   ID_PATIENT VARCHAR (20) NOT NULL,
    ROOM_ID VARCHAR (10) NOT NULL,
    PRIMARY KEY (ID_PATIENT),
    FOREIGN KEY (ID_PATIENT) REFERENCES PATIENTS (PATIENT_ID),
    FOREIGN KEY (ROOM_ID) REFERENCES ROOM (ROOM_ID));

Insert Tuple

DOCTOR

INSERT INTO DOCTOR
VALUES  ('DKV1509876','dr. Indah Risma Sp.BTKV ',45,'Kardiovaskular',6500000),
        ('DOT76896766','dr. Chairuddin Lubis Sp.OT',39,'Ortopedi',5650000),
        ('DOG5655123','dr. Amelia Rinta Sp.OG',38,'Kandungan',5500000),
        ('DKV1544569','dr. Wahyu Kusuma Sp.BTKV',49,'Kardiovaskular',7000000),
        ('DA76884560','dr. Muti Mulia Sp.A',36,'Anak',5300000),
        ('DU455434456','dr. Dyah Agustina',42,'Umum',5000000),
        ('DU453323565','dr. Mifthah Farhan',36,'Umum',4800000),
        ('DS34335980','dr. Ferdy Saragih Sp.S',50,'Saraf',7400000),
        ('DP00981287','dr. Joko Ahmad Sp.P',54,'Paru-paru',7400000);

PATIENTS

INSERT INTO PATIENTS
VALUES  ('ID0989096','Merlin Anindita','Jl. Majapahit No. 9',NULL,'Ideal','SINGLE',156,53,112),
        ('ID7689009','Ilham Mubarok','Jl. Soekarno Hatta No. 21',32,'Gemuk','MARRIED',168,70,120),
        ('ID9870034','Kislam','Jl. Merpati No.9',50,'Langsing','MARRIED',165,63,110),
        ('ID0966789','Nur Hakim','Jl. Kaliurang km 5',20,'Gemuk','SINGLE',153,65,130),
        ('ID1324564','Habibur Rifqi','Jl. Bunda no. 13',6,'Putih','SINGLE',135,35,90),
        ('ID1267864','Yulisman','Ujung Pandang',52,'Gemuk','MARRIED',163,70,135),
        ('ID1254789','Ibrahim Mika','Jl. Ginting no. 6',4,'Ideal','SINGLE',126,25,95),
        ('ID8764456','Agus Rahman','Jl. Merpati No.28',34,'Ideal','MARRIED',157,60,114),
        ('ID0987679','Putri Mayani','Jl. Anggur no. 7',28,'Ideal','MARRIED',156,55,115),
        ('ID0456989','Nur Kartika','Padang Raya No. 6',18,'Gemuk','SINGLE',150,60,110),
        ('ID0656554','Auliya Fikri','Jl. Lintas Angkasa No. 2',23,'Tinggi','SINGLE',170,75,123);

ROOM

INSERT INTO ROOM
VALUES  ('R101','RENT','GENERAL','AVAILABLE'),
        ('R102','RENT','GENERAL','AVAILABLE'),
        ('R103','RENT','GENERAL','AVAILABLE'),
        ('R104','RENT','GENERAL','AVAILABLE'),
        ('R201','RENT','SEMI SPECIAL','AVAILABLE'),
        ('R202','RENT','SEMI SPECIAL','AVAILABLE'),
        ('R203','RENT','SEMI SPECIAL','AVAILABLE'),
        ('R204','RENT','SEMI SPECIAL','AVAILABLE'),
        ('R301','RENT','SPECIAL','AVAILABLE'),
        ('R302','RENT','SPECIAL','AVAILABLE'),
        ('R303','RENT','SPECIAL','AVAILABLE'),
        ('R304','RENT','SPECIAL','AVAILABLE');

TREATS

INSERT INTO TREATS
VALUES  ('DOT76896766','ID0989096'),
        ('DKV1509876','ID7689009'),
        ('DKV1544569','ID9870034'),
        ('DOT76896766','ID0966789'),
        ('DA76884560','ID1324564'),
        ('DS34335980','ID1267864'),
        ('DA76884560','ID1254789'),
        ('DKV1544569','ID8764456'),
        ('DOG5655123','ID0987679'),
        ('DU453323565','ID0456989'),
        ('DU455434456','ID0656554');

ROOM FOR PATIENT

INSERT INTO ROOM_FOR_PATIENT
VALUES  ('ID0989096','R102'),
        ('ID7689009','R201'),
        ('ID0966789','R203'),
        ('ID1254789','R104'),
        ('ID8764456','R301'),
        ('ID0987679','R304'),
        ('ID1267864','R302');

Hospital Manajemen System

hospital<-DBI::dbConnect(RSQLite::SQLite(), "C:/sqlite/hospital.db")

Tabel DOCTOR

SELECT * FROM DOCTOR;

9 records
DOCTOR_ID	NAME	AGE	QUALIFICATION	SALARY
DKV1509876	dr. Indah Risma Sp.BTKV	45	Kardiovaskular	6500000
DOT76896766	dr. Chairuddin Lubis Sp.OT	39	Ortopedi	5650000
DOG5655123	dr. Amelia Rinta Sp.OG	38	Kandungan	5500000
DKV1544569	dr. Wahyu Kusuma Sp.BTKV	49	Kardiovaskular	7000000
DA76884560	dr. Muti Mulia Sp.A	36	Anak	5300000
DU455434456	dr. Dyah Agustina	42	Umum	5000000
DU453323565	dr. Mifthah Farhan	36	Umum	4800000
DS34335980	dr. Ferdy Saragih Sp.S	50	Saraf	7400000
DP00981287	dr. Joko Ahmad Sp.P	54	Paru-paru	7400000

Tabel PATIENTS

SELECT * FROM PATIENTS;

11 records
PATIENT_ID	NAME	ADDRESS	AGE	APPEARANCE	MARITAL_STATUS	HEIGHT	WEIGHT	BLOOD_PRESSURE
ID0989096	Merlin Anindita	Jl. Majapahit No. 9	NA	Ideal	SINGLE	156	53	112
ID7689009	Ilham Mubarok	Jl. Soekarno Hatta No. 21	32	Gemuk	MARRIED	168	70	120
ID9870034	Kislam	Jl. Merpati No.9	50	Langsing	MARRIED	165	63	110
ID0966789	Nur Hakim	Jl. Kaliurang km 5	20	Gemuk	SINGLE	153	65	130
ID1324564	Habibur Rifqi	Jl. Bunda no. 13	6	Putih	SINGLE	135	35	90
ID1267864	Yulisman	Ujung Pandang	52	Gemuk	MARRIED	163	70	135
ID1254789	Ibrahim Mika	Jl. Ginting no. 6	4	Ideal	SINGLE	126	25	95
ID8764456	Agus Rahman	Jl. Merpati No.28	34	Ideal	MARRIED	157	60	114
ID0987679	Putri Mayani	Jl. Anggur no. 7	28	Ideal	MARRIED	156	55	115
ID0456989	Nur Kartika	Padang Raya No. 6	18	Gemuk	SINGLE	150	60	110
ID0656554	Auliya Fikri	Jl. Lintas Angkasa No. 2	23	Tinggi	SINGLE	170	75	123

Tabel ROOM

SELECT * FROM ROOM;

12 records
ROOM_ID	RENT	CLASS	AVAILABILITY
R101	RENT	GENERAL	AVAILABLE
R102	RENT	GENERAL	AVAILABLE
R103	RENT	GENERAL	AVAILABLE
R104	RENT	GENERAL	AVAILABLE
R201	RENT	SEMI SPECIAL	AVAILABLE
R202	RENT	SEMI SPECIAL	AVAILABLE
R203	RENT	SEMI SPECIAL	AVAILABLE
R204	RENT	SEMI SPECIAL	AVAILABLE
R301	RENT	SPECIAL	AVAILABLE
R302	RENT	SPECIAL	AVAILABLE
R303	RENT	SPECIAL	AVAILABLE
R304	RENT	SPECIAL	AVAILABLE

Tabel TREATS

SELECT * FROM TREATS;

11 records
ID_DOCTOR	PATIENT_ID
DOT76896766	ID0989096
DKV1509876	ID7689009
DKV1544569	ID9870034
DOT76896766	ID0966789
DA76884560	ID1324564
DS34335980	ID1267864
DA76884560	ID1254789
DKV1544569	ID8764456
DOG5655123	ID0987679
DU453323565	ID0456989
DU455434456	ID0656554

Tabel ROOM_FOR_PATIENT

SELECT * FROM ROOM_FOR_PATIENT;

7 records
ID_PATIENT	ROOM_ID
ID0989096	R102
ID7689009	R201
ID0966789	R203
ID1254789	R104
ID8764456	R301
ID0987679	R304
ID1267864	R302

Advanced SQL Query

Operations on NULL values

Menampilkan NAME, ADDRESS dan AGE dari Tabel PATIENTS dimana AGE adalah NULL

SELECT NAME, ADDRESS, AGE
    FROM PATIENTS
    WHERE AGE IS NULL;

1 records
NAME	ADDRESS	AGE
Merlin Anindita	Jl. Majapahit No. 9	NA

Nested Queries

Menampilkan Ruangan yang ditempati oleh pasien yang berobat di bagian saraf

SELECT ROOM_ID
FROM ROOM_FOR_PATIENT
WHERE ID_PATIENT IN (SELECT PATIENT_ID 
            FROM TREATS
            WHERE ID_DOCTOR IN (SELECT DOCTOR_ID 
                        FROM DOCTOR
                        WHERE QUALIFICATION='Saraf'));

1 records
ROOM_ID
R302

Menampilkan Nama Dokter, Kualifikasi dan Gajinya dengan umur > 40 tahun yang memiliki gaji > 6500000

SELECT NAME, QUALIFICATION, SALARY
FROM DOCTOR
WHERE SALARY IN (SELECT SALARY
        FROM DOCTOR
        WHERE AGE>40
        AND SALARY > 6500000);

3 records
NAME	QUALIFICATION	SALARY
dr. Wahyu Kusuma Sp.BTKV	Kardiovaskular	7000000
dr. Ferdy Saragih Sp.S	Saraf	7400000
dr. Joko Ahmad Sp.P	Paru-paru	7400000

Correlated Nested Queries

ID Pasien dan Nama Pasien yang berobat ke bagian kardiovaskular

SELECT P.PATIENT_ID,P.NAME
FROM TREATS AS T, DOCTOR AS D, PATIENTS AS P
WHERE T.ID_DOCTOR=D.DOCTOR_ID 
  AND P.PATIENT_ID=T.PATIENT_ID
  AND D.QUALIFICATION='Kardiovaskular';

3 records
PATIENT_ID	NAME
ID7689009	Ilham Mubarok
ID9870034	Kislam
ID8764456	Agus Rahman

EXISTS and NOT EXISTS Functions

ID Pasien dan Nama Pasien yang rawat inap di ruangan kelas SPECIAL

SELECT P.PATIENT_ID, P.NAME
FROM PATIENTS AS P
WHERE EXISTS (SELECT * FROM ROOM_FOR_PATIENT AS RP
        WHERE P.PATIENT_ID=RP.ID_PATIENT AND EXISTS (SELECT * FROM ROOM AS R
        WHERE RP.ROOM_ID=R.ROOM_ID AND R.CLASS='SPECIAL'));

3 records
PATIENT_ID	NAME
ID1267864	Yulisman
ID8764456	Agus Rahman
ID0987679	Putri Mayani

Ruangan yang kosong (tidak ditempati pasien) (ID ROOM dan Kelas)

SELECT R.ROOM_ID,R.CLASS
FROM ROOM AS R
WHERE NOT EXISTS (SELECT * FROM ROOM_FOR_PATIENT AS RP
            WHERE RP.ROOM_ID=R.ROOM_ID);

5 records
ROOM_ID	CLASS
R101	GENERAL
R103	GENERAL
R202	SEMI SPECIAL
R204	SEMI SPECIAL
R303	SPECIAL

EXPLICIT SETS

SELECT  DISTINCT PATIENT_ID, APPEARANCE
  FROM  PATIENTS
  WHERE     APPEARANCE IN ('Gemuk','Langsing');

5 records
PATIENT_ID	APPEARANCE
ID7689009	Gemuk
ID9870034	Langsing
ID0966789	Gemuk
ID1267864	Gemuk
ID0456989	Gemuk

RENAMING Attribute

SELECT D.NAME AS NAMA, QUALIFICATION AS KUALIFIKASI
    FROM DOCTOR AS D
    WHERE AGE = 45;

1 records
NAMA	KUALIFIKASI
dr. Indah Risma Sp.BTKV	Kardiovaskular

Joined Relations

SELECT * FROM DOCTOR AS D CROSS JOIN PATIENTS AS P
WHERE D.AGE = P.AGE

1 records
DOCTOR_ID	NAME	AGE	QUALIFICATION	SALARY	PATIENT_ID	NAME	ADDRESS	AGE	APPEARANCE	MARITAL_STATUS	HEIGHT	WEIGHT	BLOOD_PRESSURE
DS34335980	dr. Ferdy Saragih Sp.S	50	Saraf	7400000	ID9870034	Kislam	Jl. Merpati No.9	50	Langsing	MARRIED	165	63	110

Mencari Pasien dan Dokter yang usianya sama.

SELECT * FROM DOCTOR AS D JOIN PATIENTS AS P ON 
D.AGE = P.AGE

1 records
DOCTOR_ID	NAME	AGE	QUALIFICATION	SALARY	PATIENT_ID	NAME	ADDRESS	AGE	APPEARANCE	MARITAL_STATUS	HEIGHT	WEIGHT	BLOOD_PRESSURE
DS34335980	dr. Ferdy Saragih Sp.S	50	Saraf	7400000	ID9870034	Kislam	Jl. Merpati No.9	50	Langsing	MARRIED	165	63	110

JOIN Menampilkan Nama dan Usia pasien yang dirawat di kamar SPECIAL dan USIA > 17.

SELECT p.name, p.age, r.class
FROM patients as p, Room_For_Patient as rp, room as r
WHERE p.patient_id=rp.id_patient AND rp.room_id=r.room_id AND r.class = 'SPECIAL' AND p.age>17;

3 records
NAME	AGE	CLASS
Yulisman	52	SPECIAL
Agus Rahman	34	SPECIAL
Putri Mayani	28	SPECIAL

MULTIWAY JOIN

SELECT p.name, p.age
FROM ((patients as p JOIN Room_For_Patient as rp ON p.patient_id=rp.id_patient) JOIN room as r ON rp.room_id=r.room_id)
WHERE r.class = 'SPECIAL' AND p.age>17;

3 records
NAME	AGE
Yulisman	52
Agus Rahman	34
Putri Mayani	28

NATURAL JOIN Menampilkan nama dan usia dari pasien anak-anak dibawah 18 tahun dan kode dokter yang merawatnya

SELECT name, age, ID_doctor FROM patients AS p NATURAL JOIN treats WHERE age<18;

2 records
NAME	AGE	ID_DOCTOR
Habibur Rifqi	6	DA76884560
Ibrahim Mika	4	DA76884560

Aggregate Function

Menampilkan jumlah dokter, total gaji, gaji tertinggi, gaji terendah, dan rata-rata gaji dari dokter

SELECT COUNT(*), SUM (Salary) AS Total_Sal, MAX (Salary) AS Highest_Sal, MIN (Salary) AS Lowest_Sal, AVG (Salary) AS Average_Sal
FROM doctor;

1 records
COUNT(*)	Total_Sal	Highest_Sal	Lowest_Sal	Average_Sal
9	54550000	7400000	4800000	6061111

Menampilkan gaji tertinggi, gaji terendah, dan rata-rata gaji dari dokter yang jumlah berdasarkan kualifikasinya > 1

SELECT qualification, MAX (Salary) AS Highest_Sal, MIN (Salary) AS Lowest_Sal, AVG (Salary) AS Average_Sal FROM doctor as d
GROUP BY d.qualification
HAVING COUNT(*)>1;

2 records
QUALIFICATION	Highest_Sal	Lowest_Sal	Average_Sal
Kardiovaskular	7e+06	6500000	6750000
Umum	5e+06	4800000	4900000

Mencari Dokter yang memiliki 2 atau lebih Pasien.

SELECT name
FROM doctor
WHERE (SELECT COUNT(*)
    FROM treats
    WHERE id_doctor=doctor_id) >=2;

3 records
NAME
dr. Chairuddin Lubis Sp.OT
dr. Wahyu Kusuma Sp.BTKV
dr. Muti Mulia Sp.A

Grouping

Menghitung Jumlah dan Rata-rata Gaji setiap Kualifikasi Dokter dan mengurutkannya dari rata-rata salary tertinggi ke terendah

SELECT qualification, COUNT(*), AVG(salary)
FROM doctor
GROUP BY qualification
ORDER BY AVG(salary) DESC;

7 records
QUALIFICATION	COUNT(*)	AVG(salary)
Saraf	1	7400000
Paru-paru	1	7400000
Kardiovaskular	2	6750000
Ortopedi	1	5650000
Kandungan	1	5500000
Anak	1	5300000
Umum	2	4900000

Menghitung jumlah pasien dari tiap kualifikasi dokter

SELECT d.qualification, COUNT(*)
FROM doctor as d, treats as t
WHERE d.doctor_id = t.id_doctor
GROUP BY d.qualification;

6 records
QUALIFICATION	COUNT(*)
Anak	2
Kandungan	1
Kardiovaskular	3
Ortopedi	2
Saraf	1
Umum	2

HAVING clause

Mencari kualifikasi dokter yang memiliki > 1 patient dan diurutkan berdasarkan jumlah pasien dari yang paling banyak ke paling sedikit

SELECT d.qualification, COUNT(*)
FROM doctor as d, treats as t
WHERE d.doctor_id = t.id_doctor
GROUP BY d.qualification
HAVING COUNT(*)>1
ORDER BY COUNT(*) DESC;

4 records
QUALIFICATION	COUNT(*)
Kardiovaskular	3
Umum	2
Ortopedi	2
Anak	2

Mencari jumlah pasien berdasarkan kualifikasi dokter yang mana pasien memiliki tekanan darah > 100 dan mengurutkannya dari paling banyak ke paling sedikit.

SELECT d.qualification, count(*)
FROM doctor as d, patients as p, treats as t
WHERE d.doctor_id = t.id_doctor AND p.patient_id = t.patient_id AND p.blood_pressure > 100
GROUP BY d.qualification
ORDER BY count(*) DESC;

5 records
QUALIFICATION	count(*)
Kardiovaskular	3
Umum	2
Ortopedi	2
Saraf	1
Kandungan	1

IPB University, abdul.aziz@big.go.id︎↩︎
IPB University, -↩︎
IPB University, -↩︎
IPB University, -↩︎