Advanced SQL Query

Theories come and go, but fundamental data always remain the same.
- Mary Leakey

Alfa Nugraha Pradana

Prodi Statistika dan Sains Data IPB University

Kueri SQL Tingkat Lanjut

Operasi pada nilai NULL
Kueri bersarang (Kueri di dalam kueri)
Kueri bersarang yang berkorelasi
Fungsi EXISTS dan NOT EXISTS
EXPLICIT SETS
Mengubah nama atribut
Joined relations
- CROSS JOIN, MULTIWAY JOIN, NATURAL JOIN, LEFT JOIN, RIGHT JOIN, dan sebagainya
Fungsi agregat
Pengelompokan
Klausa HAVING

Operasi pada nilai NULL

Menampilkan nama, alamat dan umur dari tabel pasien dimana umur bernilai NULL

SELECT nama, alamat, umur
  FROM pasien
  WHERE umur IS NULL;

Menjalankan kueri di atas pada R dapat menggunakan sintaks berikut:

# memuat package dan set driver DBMS
library(RPostgreSQL)
driver <- dbDriver('PostgreSQL')

# bangun koneksi ke DBMS
DB <- dbConnect(
  driver, dbname="hospital", host="localhost",port=5433,user="postgres", password="root"
)
# definisikan kueri yang ingin dieksekusi
q <- "SELECT nama, alamat, umur FROM pasien WHERE umur IS NULL;"
# request kueri ke DBMS
rs <- dbGetQuery(DB, q)

Kueri bersarang (kueri di dalam kueri)

Kueri ini terdiri dari kueri outer dan kueri inner

Menampilkan kamar yang ditempati oleh pasien yang berobat di poli saraf

SELECT id_kamar
  FROM kamar_untuk_pasien
  WHERE id_pasien IN (
    SELECT id_pasien 
    FROM tindakan
    WHERE id_dokter IN (
      SELECT id_dokter 
      FROM dokter
      WHERE spesialis='Saraf'
    )
  );

Menampilkan nama_dokter, spesialis, dan upah dengan kondisi umur di atas 40 tahun dan upah > 6500000

SELECT nama_dokter, spesialis, upah
  FROM dokter
  WHERE upah IN (SELECT upah
    FROM dokter
    WHERE umur_dokter > 40
      AND upah > 6500000);

Kueri bersarang yang berkorelasi

Kondisi dimana klausa WHERE pada kueri bersarang berpadanan/mewakili beberapa atribut dari relasi yang dideklarasikan pada kueri terluarnya (outer) sehingga dua kueri ini dikatakan berkorelasi

Menampilkan id_pasien berikut namanya yang berobat ke poli kardiovaskular

SELECT p.id_pasien, p.nama
  FROM tindakan AS t, dokter AS d, pasien AS p
  WHERE t.id_dokter   = d.id_dokter 
    AND p.id_pasien   = t.id_pasien
    AND d.spesialis = 'Kardiovaskular';

Fungsi EXISTS dan NOT EXISTS

Menampilkan id_pasien dan namanya yang masuk ke instalasi rawat inap dengan kelas kamar=VVIP

SELECT p.id_pasien, p.nama
  FROM pasien AS p
  WHERE EXISTS (
    SELECT * FROM kamar_untuk_pasien AS kp
      WHERE P.id_pasien=kp.id_pasien AND EXISTS (
        SELECT * FROM kamar AS k WHERE kp.id_kamar=k.id_kamar AND k.kelas='VVIP'
      )
  );

Ruangan yang kosong (belum diisi oleh pasien) yang disajikan dengan id_kamar dan kelas

SELECT k.id_kamar, k.kelas
FROM kamar AS k
WHERE NOT EXISTS (
  SELECT * FROM kamar_untuk_pasien AS kp
    WHERE kp.id_kamar=k.id_kamar
  );

EXPLICIT SETS

Menampilkan daftar pasien yang ditangani oleh dokter spesialis saraf dan kardiovaskular

SELECT DISTINCT p.id_pasien, p.nama, d.nama_dokter, d.spesialis
  FROM tindakan AS t, dokter AS d, pasien AS p
  WHERE t.id_dokter   = d.id_dokter 
    AND p.id_pasien   = t.id_pasien
    AND d.spesialis IN ('Kardiovaskular', 'Saraf');

Mengubah nama atribut

SELECT d.nama_dokter AS "nama praktisi", spesialis AS "spesialisasi kedokteran" 
  FROM dokter AS d
  WHERE umur_dokter = 45;

Joined relations

Mencari pasien dan dokter yang memiliki usia sama.

SELECT * FROM dokter AS d CROSS JOIN pasien AS P
  WHERE d.umur_dokter = p.umur;

Menampilkan nama dan usia pasien yang dirawat di kamar VVIP dan berusia > 17.

SELECT p.nama, p.umur, k.kelas
  FROM pasien as p, kamar_untuk_pasien as kp, kamar as k
  WHERE p.id_pasien=kp.id_pasien 
    AND kp.id_kamar=k.id_kamar 
    AND k.kelas = 'VVIP' 
    AND p.umur>17;

MULTIWAY JOIN

SELECT p.nama, p.umur
  FROM (
    (pasien as p JOIN kamar_untuk_pasien as kp ON p.id_pasien=kp.id_pasien) JOIN kamar as k ON kp.id_kamar=k.id_kamar
  )
  WHERE k.kelas = 'VVIP' AND p.umur>17;

NATURAL JOIN

Menampilkan nama dan usia dari pasien anak-anak dibawah 18 tahun dan id_dokter yang merawatnya

SELECT nama, umur, id_dokter 
  FROM pasien AS p
  NATURAL JOIN tindakan WHERE umur < 18;

Fungsi agregat

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

SELECT COUNT(*), 
       SUM (upah) AS total_upah,
       MAX (upah) AS upah_tertinggi, 
       MIN (upah) AS upah_terendah,
       AVG (upah) AS rataan_upah
  FROM dokter;

Menampilkan upah tertinggi, upah terendah, dan rataan upah dari dokter yang jumlah spesialisasinya lebih dari satu

SELECT spesialis, 
    MAX (upah) AS upah_tertinggi, 
    MIN (upah) AS upah_terendah, 
    AVG (upah) AS rataan_upah 
  FROM dokter as d
  GROUP BY d.spesialis
  HAVING COUNT(*) > 1;

Mencari dokter yang menangani dua atau lebih pasien.

SELECT nama_dokter
  FROM dokter
  WHERE (SELECT COUNT(*) FROM tindakan WHERE tindakan.id_dokter=dokter.id_dokter) >= 2;

Pengelompokan

Menghitung total dan rata-rata upah setiap dokter spesialis dan mengurutkannya secara menurun

SELECT spesialis, COUNT(*), AVG(upah)
  FROM dokter
  GROUP BY spesialis
  ORDER BY AVG(upah) DESC;

Menghitung total pasien dari pada masing-masing poli

SELECT d.spesialis, COUNT(*)
  FROM dokter as d, tindakan as t
  WHERE d.id_dokter = t.id_dokter
  GROUP BY d.spesialis;

Klausa `HAVING`

Mencari dokter spesialis yang menangani lebih dari satu pasien dan diurutkan berdasarkan jumlah pasien secara menurun

SELECT d.spesialis, COUNT(*)
  FROM dokter as d, tindakan as t
  WHERE d.id_dokter = t.id_dokter
  GROUP BY d.spesialis
  HAVING COUNT(*)>1
  ORDER BY COUNT(*) DESC;

Mencari jumlah pasien berdasarkan poli dimana pasien belum memiliki keluarga dan mengurutkannya dari yang paling banyak ke paling sedikit.

SELECT d.spesialis, COUNT(*)
  FROM dokter as d, pasien as p, tindakan as t
  WHERE d.id_dokter = t.id_dokter
  AND p.id_pasien = t.id_pasien
  AND p.status = 'SINGLE'
  GROUP BY d.spesialis
  HAVING COUNT(*)>1
  ORDER BY count(*) DESC;