Kueri bersarang

Pengambilan Keputusan Berbasis Data dengan SQL

Irene Ortner

Data Scientist at Applied Statistics

Kueri bersarang

Blok SELECT di klausa WHERE atau HAVING
Kueri dalam mengembalikan satu atau banyak nilai
Gunakan hasil kueri dalam untuk memilih baris tertentu di kueri lain

Kueri dalam

Langkah 1: Kueri dalam

SELECT DISTINCT customer_id
FROM renting
WHERE rating <= 3

| customer_id |
|-------------|
| 28          |
| 41          |
| 86          |
| 120         |

Hasil di klausa WHERE

SELECT name
FROM customers
WHERE customer_id IN (28, 41, 86, 120);

Kueri luar

Langkah 2: Kueri luar

SELECT name
FROM customers
WHERE customer_id IN
    (SELECT DISTINCT customer_id
    FROM renting
    WHERE rating <= 3);

| name            |
|-----------------|
| Sidney Généreux |
| Zara Mitchell   |

Kueri bersarang di klausa HAVING

Langkah 1: Kueri dalam

SELECT MIN(date_account_start)
FROM customers
WHERE country = 'Austria';

| min        |
|------------|
| 2017-11-22 |

Kueri bersarang di klausa HAVING

Langkah 2: Kueri luar

SELECT country, MIN(date_account_start)
FROM customers
GROUP BY country
HAVING MIN(date_account_start) <
    (SELECT MIN(date_account_start)
    FROM customers
    WHERE country = 'Austria');

| country       | min        |
|---------------|------------|
| Spain         | 2017-02-14 |
| Great Britain | 2017-03-31 |

Siapa aktor dalam film Ray?

SELECT name
FROM actors
WHERE actor_id IN
    (SELECT actor_id 
     FROM actsin
     WHERE movie_id =
        (SELECT movie_id 
         FROM movies
         WHERE title='Ray'));

| name             |
|------------------|
| Jamie Foxx       |
| Kerry Washington |
| Regina King      |

Ayo berlatih!

Pengambilan Keputusan Berbasis Data dengan SQL