Kueri bertingkat berkorelasi

Pengambilan Keputusan Berbasis Data dengan SQL

Bart Baesens

Professor Data Science and Analytics

Kueri berkorelasi

Kondisi di klausa WHERE kueri dalam.
Merujuk kolom dari tabel di kueri luar.

Contoh kueri berkorelasi

Jumlah penyewaan film lebih dari 5

SELECT *
FROM movies as m
WHERE 5 < 
  (SELECT COUNT(*)
  FROM renting as r
  WHERE r.movie_id=m.movie_id);

Evaluasi kueri dalam

SELECT COUNT(*)
FROM renting as r
WHERE r.movie_id = 1;

| count |
|-------|
| 8     |

Evaluasi kueri luar

Jumlah penyewaan film lebih dari 5

SELECT *
FROM movies as m
WHERE 5 < 
    (SELECT COUNT(*)
    FROM renting as r
    WHERE r.movie_id = m.movie_id);

| movie_id | title                 | genre  | runtime | year_of_release | renting_price |
|----------|-----------------------|--------|---------|-----------------|---------------|
| 1        | One Night at McCool's | Comedy | 93      | 2001            | 2.09          |
| 2        | Swordfish             | Drama  | 99      | 2001            | 2.19          |

Kurang dari 5 penyewaan

Pilih film dengan kurang dari 5 penyewaan.

SELECT *
FROM movies as m
WHERE 5 > 
    (SELECT COUNT(*)
    FROM renting as r
    WHERE r.movie_id = m.movie_id);

| movie_id | title           | genre              | runtime | year_of_release | renting_price |
|----------|-----------------|--------------------|---------|-----------------|---------------|
| 17       | The Human Stain | Mystery & Suspense | 106     | 2003            | 1.99          |
| 20       | Love Actually   | Comedy             | 135     | 2003            | 2.29          |

Ayo berlatih!

Pengambilan Keputusan Berbasis Data dengan SQL