SQL and R

SQL

Sebelum Anda memulai materi ini, Anda harus terlebih dahulu membuat company.db dari El Masri (Buku Pegangan Anda), Silahkan akses Syntax SQL pada halaman Github berikut ini : El Masri Company Database

Perintah dalam SQL

Commands	Keterangan
`SELECT`	Memilih Data dari Database
`FROM`	Memilih table darimana data diambil
`WHERE`	Menyaring query dengan mencocokkan suatu kondisi
`AS`	Mengubah nama kolom atau tabel dengan suatu alias
`JOIN`	Menggabungkan baris dari 2 atau lebih tabel
`AND`	Kombinasi Kondisi Query. Semua Kondisi harus terpenuhi
`OR`	Kombinasi Kondisi Query. Salah satu Kondisi harus terpenuhi
`LIMIT`	Membatasi jumlah baris yang ditampilkan
`IN`	Spesifikasi beberapa nilai ketika menggunakan `WHERE`
`IS NULL`	Mengembalikan baris dengan nilai `NULL`
`LIKE`	Mencari pola tertentu di suatu kolom
`GROUP BY`	Mengelompokkan Data
`ORDER BY`	Mengurutkan Data, default ASC. Gunakan DESC untuk sebaliknya
`HAVING`	Sama dengan `WHERE` tapi untuk Groups
`COUNT`	Menghitung jumlah baris
`SUM`	Menghitung jumlah dari kolom
`AVG`	Menghitung rata-rata dari kolom
`MIN`	Menghitung nilai minimal dari kolom
`MAX`	Menghitung nilai maksimum dari kolom

Order Of Execution

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
LIMIT

Contoh

Berikut ini adalah beberapa contoh, penggunaan Query SQL dengan menggunakan Entity EMPLOYEE dari Database company.db

SELECT
  *
FROM
  EMPLOYEE;

8 records
Fname	Minit	Lname	Ssn	Bdate	Address	Sex	Salary	Super_ssn	Dno
John	B	Smith	123456789	1965-01-09	731 Fondren, Houston TX	M	30000	333445555	5
Franklin	T	Wong	333445555	1965-12-08	638 Voss, Houston TX	M	40000	888665555	5
Alicia	J	Zelaya	999887777	1968-01-19	3321 Castle, Spring TX	F	25000	987654321	4
Jennifer	S	Wallace	987654321	1941-06-20	291 Berry, Bellaire TX	F	43000	888665555	4
Ramesh	K	Narayan	666884444	1962-09-15	975 Fire Oak, Humble TX	M	38000	333445555	5
Joyce	A	English	453453453	1972-07-31	5631 Rice, Houston TX	FEMALE	25000	333445555	5
Ahmad	V	Jabbar	987987987	1969-03-29	980 Dallas, Houston TX	MALE	25000	987654321	4
James	E	Borg	888665555	1937-11-10	450 Stone, Houston TX	ANYTHING	55000	NA	1

1. Memilih Semua Kolom dengan Filter Tertentu

SELECT
  *
FROM
  EMPLOYEE
WHERE
  SALARY < 30000;

3 records
Fname	Minit	Lname	Ssn	Bdate	Address	Sex	Salary	Super_ssn	Dno
Alicia	J	Zelaya	999887777	1968-01-19	3321 Castle, Spring TX	F	25000	987654321	4
Joyce	A	English	453453453	1972-07-31	5631 Rice, Houston TX	FEMALE	25000	333445555	5
Ahmad	V	Jabbar	987987987	1969-03-29	980 Dallas, Houston TX	MALE	25000	987654321	4

2. Memilih 5 Baris Pertama dari 2 Kolom

SELECT
  Fname, Lname
FROM
  EMPLOYEE
LIMIT
  5;

5 records
Fname	Lname
John	Smith
Franklin	Wong
Alicia	Zelaya
Jennifer	Wallace
Ramesh	Narayan

3. Memilih Semua Kolom dengan beberapa Filter

SELECT
  *
FROM
  EMPLOYEE
WHERE
  SALARY > 30000
  AND
  SEX = 'M';

2 records
Fname	Minit	Lname	Ssn	Bdate	Address	Sex	Salary	Super_ssn	Dno
Franklin	T	Wong	333445555	1965-12-08	638 Voss, Houston TX	M	40000	888665555	5
Ramesh	K	Narayan	666884444	1962-09-15	975 Fire Oak, Humble TX	M	38000	333445555	5

4. Seperti Poin 3, gaji diurutkan dari yang terbesar

SELECT
  FNAME, LNAME, SALARY
FROM
  EMPLOYEE
WHERE
  SALARY > 30000
  AND
  SEX = 'M'
ORDER BY
  SALARY DESC;

2 records
Fname	Lname	Salary
Franklin	Wong	40000
Ramesh	Narayan	38000

Anda, juga bisa menuliskan Syntax di atas, sebagai berikut

SELECT
  FNAME, LNAME, SALARY
FROM
  EMPLOYEE
WHERE
  SALARY > 30000
  AND
  SEX = 'M'
ORDER BY
  3 DESC;

2 records
Fname	Lname	Salary
Franklin	Wong	40000
Ramesh	Narayan	38000

5. Menghitung Jumlah Baris

SELECT
  COUNT(*)
FROM
  EMPLOYEE;

1 records
COUNT(*)
8

6. Menghitung Total Gaji

SELECT
  SUM(SALARY)
FROM
  EMPLOYEE;

1 records
SUM(SALARY)
281000

7. Menampilkan Nama dan Gaji, dari Employee dengan Gaji Terbesar

SELECT
  FNAME, LNAME, MAX(SALARY)
FROM
  EMPLOYEE;

1 records
Fname	Lname	MAX(SALARY)
James	Borg	55000

8. Menghitung Gaji rata-rata dari masing-masing Jenis Kelamin

SELECT
  SEX, AVG(SALARY)
FROM
  EMPLOYEE
GROUP BY
  SEX;

5 records
Sex	AVG(SALARY)
ANYTHING	55000
F	34000
FEMALE	25000
M	36000
MALE	25000

Advanced SQL Query

Untuk Materi ini, akan dibuat suatu Database bernama Hospital.db dengan Informasi Berikut ini.

Hospital

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

Hospital Database

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

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

Database

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;

Displaying records 1 - 10
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

Tabel ROOM

SELECT * FROM ROOM;

Displaying records 1 - 10
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

Tabel TREATS

SELECT * FROM TREATS;

Displaying records 1 - 10
ID_DOCTOR	PATIENT_ID
DOT76896766	ID0989096
DKV1509876	ID7689009
DKV1544569	ID9870034
DOT76896766	ID0966789
DA76884560	ID1324564
DS34335980	ID1267864
DA76884560	ID1254789
DKV1544569	ID8764456
DOG5655123	ID0987679
DU453323565	ID0456989

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

Query Database dengan R

Terdapat banyak cara yang bisa digunakan untuk melakukan akses atas suatu database dengan R, berikut ini adalah 3 cara yang umum digunakan.

library(dplyr)
library(dbplyr)

Secara umum, sebuah Koneksi ke database membutuhkan 2 hal, yaitu :
1. Database Driver
2. Data Source Name

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

Pada contoh di atas,
1. Database Drivernya adalah RSQLite::SQLite().
2. Data Source Name adalah C:/sqlite/company.db

Using DBI

df <- DBI::dbGetQuery(con,'
                      SELECT "fname", "lname"
                      FROM "Employee"
                      '
                )

df

##      Fname   Lname
## 1     John   Smith
## 2 Franklin    Wong
## 3   Alicia  Zelaya
## 4 Jennifer Wallace
## 5   Ramesh Narayan
## 6    Joyce English
## 7    Ahmad  Jabbar
## 8    James    Borg

class(df)

## [1] "data.frame"

Ada juga yang menulis sintax di atas sebagai berikut

df <- DBI::dbGetQuery(con, statement = paste("SELECT fname, lname FROM employee"))

Using dplyr syntax

df <- tbl(con, "employee") %>%
  select(Fname, Lname)

Salah satu hal yang perlu diingat adalah, karena menggunakan dplyr, maka berlaku aturan umum R, yaitu CASE SENSITIVE. Jika menggunakan DBI, Anda tidak perlu memperhatikan hal tersebut.

df

## # Source:   lazy query [?? x 2]
## # Database: sqlite 3.36.0 [C:\sqlite\company.db]
##   Fname    Lname  
##   <chr>    <chr>  
## 1 John     Smith  
## 2 Franklin Wong   
## 3 Alicia   Zelaya 
## 4 Jennifer Wallace
## 5 Ramesh   Narayan
## 6 Joyce    English
## 7 Ahmad    Jabbar 
## 8 James    Borg

class(df)

## [1] "tbl_SQLiteConnection" "tbl_dbi"              "tbl_sql"             
## [4] "tbl_lazy"             "tbl"

Dengan Perintah show_query, Anda bisa menerjemahkan sintax dplyr di atas, menjadi SQL.

show_query(df)

## <SQL>
## SELECT `Fname`, `Lname`
## FROM `employee`

Using R Notebooks/Rmarkdown

Ini adalah cara yang digunakan dalam menuliskan SQL Query di atas, dimana dalam penulisan R Code Chunk ditambahkan beberapa opsi.

Pertama, buat objek koneksi, di sini dinamakan con

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

Berikut ini adalah contoh

```{r, connection=con, engine="sql", output.var = "mydataframe"}
SELECT
  *
FROM
  EMPLOYEE; 
```

Kode di atas, akan muncul sebagai berikut :

SELECT
  *
FROM
  EMPLOYEE;

Selain itu, akan didapat suatu dataframe dengan nama mydataframe.

mydataframe

##      Fname Minit   Lname       Ssn      Bdate                 Address      Sex
## 1     John     B   Smith 123456789 1965-01-09 731 Fondren, Houston TX        M
## 2 Franklin     T    Wong 333445555 1965-12-08    638 Voss, Houston TX        M
## 3   Alicia     J  Zelaya 999887777 1968-01-19  3321 Castle, Spring TX        F
## 4 Jennifer     S Wallace 987654321 1941-06-20  291 Berry, Bellaire TX        F
## 5   Ramesh     K Narayan 666884444 1962-09-15 975 Fire Oak, Humble TX        M
## 6    Joyce     A English 453453453 1972-07-31   5631 Rice, Houston TX   FEMALE
## 7    Ahmad     V  Jabbar 987987987 1969-03-29  980 Dallas, Houston TX     MALE
## 8    James     E    Borg 888665555 1937-11-10   450 Stone, Houston TX ANYTHING
##   Salary Super_ssn Dno
## 1  30000 333445555   5
## 2  40000 888665555   5
## 3  25000 987654321   4
## 4  43000 888665555   4
## 5  38000 333445555   5
## 6  25000 333445555   5
## 7  25000 987654321   4
## 8  55000      <NA>   1

class(mydataframe)

## [1] "data.frame"

DBI::dbDisconnect(con)

Tugas UTS : Twitter BOT

Membuat sebuah Twitter Bot yang berjalan otomatis, yang dipublish dengan rvest dan dijalankan dengan Github Action.

Bahan : (Tidak mutlak, Jika Anda punya Opsi lebih baik, dipersilahkan)
1. Akun Twitter Developer dengan Elevated Access (sebaiknya Anda buat akun baru).
2. Akun ElephantSQL (Untuk Project ini, Anda cukup membuat 1).
3. Akun Github (sebaiknya Anda punya masing-masing).

Proses yang diharapkan :
1. Anda bisa menyimpan suatu data ke database SQL Online (ElephantSQL).
2. Anda bisa mengakses data dari database SQL Online tersebut, untuk kemudian dipublikasikan.
3. Semua proses di atas dilakukan secara Otomatis.

Penilaian
1. Kerjasama Tim, yang dapat dilihat dari history di Repository.
2. Bot Twitter berfungsi dengan baik.

Twitter Developer Account with Elevated Access

Langkah-Langkah mendapat Akun tersebut :

Membuat Akun Twitter.
Mendaftar sebagai Developer Account dengan membuka link berikut ini : link

Ketika Anda mendaftar, Anda mendapatkan Essential Access.

Mengajukan Akses Tambahan ke Elevated.

Akun Elephant SQL

Anda bisa menggunakan versi FREE dari ElephantSQL.

Catatan : Max Connection dalam satu waktu, hanya 5.

Akun Github

Anda bisa melakukan Fork pada Github berikut ini : https://github.com/Nr5D/ManajemenData

Contoh Bot yang sudah berjalan : https://twitter.com/ManajemenData