Hospital Management System

A. MEMBUAT TABEL

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));

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));

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));

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));

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));

B. MEMASUKKAN DATA/TUPLE KE TABEL

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);

INSERT INTO PATIENTS
VALUES  ('ID0989096','Merlin Anindita','Jl. Majapahit No. 9',23,'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);

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');

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');

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

C. AKSES KE DATABASE

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

C.1 Menampikan semua entiti dari semua tabel

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

SELECT * FROM PATIENTS;

11 records
PATIENT_ID	NAME	ADDRESS	AGE	APPEARANCE	MARITAL_STATUS	HEIGHT	WEIGHT	BLOOD_PRESSURE
ID0989096	Merlin Anindita	Jl. Majapahit No. 9	23	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

SELECT * FROM ROOM;

12 records
ROOM_ID	RENT	CLASS	AVAILABILITY
R101	1e+05	GENERAL	AVAILABLE
R102	1e+05	GENERAL	AVAILABLE
R103	1e+05	GENERAL	AVAILABLE
R104	1e+05	GENERAL	AVAILABLE
R201	2e+05	SEMI SPECIAL	AVAILABLE
R202	2e+05	SEMI SPECIAL	AVAILABLE
R203	2e+05	SEMI SPECIAL	AVAILABLE
R204	2e+05	SEMI SPECIAL	AVAILABLE
R301	3e+05	SPECIAL	AVAILABLE
R302	3e+05	SPECIAL	AVAILABLE
R303	3e+05	SPECIAL	AVAILABLE
R304	3e+05	SPECIAL	AVAILABLE

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

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

C.2 Dokter dengan penghasilan > 6000000

SELECT NAME, SALARY FROM DOCTOR
WHERE SALARY > 6000000;

4 records
NAME	SALARY
dr. Indah Risma Sp.BTKV	6500000
dr. Wahyu Kusuma Sp.BTKV	7000000
dr. Ferdy Saragih Sp.S	7400000
dr. Joko Ahmad Sp.P	7400000

D. SYNTAX NURUL

D.1 Ruangan yang ditempatkan 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

D.2 Dokter dengan umur > 40 tahun yang memiliki gaji > 6500000 (Nama dokter dan kualifikasinya)

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

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

D.3 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

D.4 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

D.5 Ruangan yang kosong (tidak ditempatkan 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

E. SYNTAX SETIA NINGSIH

E.1 Penggunaan fungsi EXISTS dan NOT EXIST List nama dokter yang tidak memiliki pasien dirawat

SELECT  NAME
FROM    DOCTOR
WHERE   EXISTS  ( SELECT   *
                    FROM    PATIENTS
                    WHERE   DOCTOR_ID = PATIENT_ID )
    AND
    EXISTS  ( SELECT   *
                    FROM     ROOM
                    WHERE   DOCTOR_ID = ROOM_ID );

0 records
NAME

E.2 Penggunaan Fungsi EXISTS and NOT EXISTS [and EXCEPT]

SELECT NAME
FROM DOCTOR
WHERE NOT EXISTS ( SELECT WEIGHT
                  FROM PATIENTS
                  WHERE   APPEARANCE = Gemuk
                  EXCEPT  SELECT    ROOM
                  FROM   PATIENTS WHERE   DOCTOR_ID = PATIENT_ID);

E.3 penggunaan EXPLICIT SETS list nama pasien dengan kategori gemuk atau langsing

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

E.4 RENAMING ATTRIBUTE

SELECT NAME
    FROM DOCTOR AS D
    WHERE NAME = DOCTOR_ID;

0 records
NAME

E.5 Joined Relations dengan CROSS JOIN

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

E.6 Joined Relations dengan JOIN : 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

F. Syntax Reza Felix

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

SELECT p.name, p.age
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
Yulisman	52
Agus Rahman	34
Putri Mayani	28

F.2 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

F.3 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

F,4 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

F.5 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

G. Syntax Abdul Aziz

G.1 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

G.2 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

G.3 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

G.4 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

G.5 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, -↩︎