Subquery

1. Scalar Subquery

contoh 1
- tampilkan film_id, title dan rental_rate untuk film yang rental_ratenya diatas rata-rata
- proses query
  - menghitung terlebih dahulu rata-ratanya dengan AVG
  - ini yang disebut subquery.
  - nilainya diambil dan digunakan di query utama
```
SELECT film_id, title, rental_rate
FROM film
WHERE rental_rate > 
 (SELECT AVG (rental_rate) FROM film);
```
Contoh 2
- tampilkan nama film dan length nya (lama film dalam menit), dimana length dari film itu diatas nilai rata-rata
  - petunjuk:
  - hitung dulu rata-rata length dengan subquery
  - hanya menggunakan 1 tabel saja yaitu film
```
  select avg(film.length) from film;

  select film.title, film.length
  from film
  where film.length >
    (select avg(film.length) from film);
```

2. One column Subquery

Contoh 3

tampilkan film yang tidak pernah disewa
penjelasan

    select film.film_id
    from film
    inner join inventory ON film.film_id = inventory.film_id
    inner join rental    ON inventory.inventory_id = rental.inventory_id
    order by film.film_id;

langkah pertama adalah menghubungkan 3 tabel dari film, inventory, rental
bila tanpa di urutkan (order) akan tampak id secara acak
karena itu ditambahkan order sehingga bila film id 1 disewa 10x akan muncul angka 1 sebanyak 10x
karena itu maka digunakan distinct agar bila angka 1 sudah ditampilkan JANGAN ditampilkan lagi

  select distinct film.film_id
  from film
  inner join inventory ON film.film_id = inventory.film_id
  inner join rental    ON inventory.inventory_id = rental.inventory_id
  order by film.film_id;

untuk selanjutnya kita buat query utama yang menampilkan data film

  select film.film_id, film.title
  from film
  where film.film_id NOT IN 
    (select distinct film.film_id
     from film
     inner join inventory ON film.film_id = inventory.film_id
     inner join rental    ON inventory.inventory_id = rental.inventory_id
     order by film.film_id);

pada query diatas subquery akan menghasil set [1,5,8,..] yaitu film_id yang pernah disewa
dengan menggunakan NOT IN berarti bila film tersebut idnya tidak ada dalam set
maka berarti tidak pernah disewa, dan selanjutnya kan ditampilkan titlenya

contoh 4:

menampilkan film_id dan title untuk film-film yang disewa hanya 5x saja

select film.film_id, film.title
from film
where film.film_id IN (
  select film.film_id
  from rental
  inner join inventory on rental.inventory_id = inventory.inventory_id
  inner join film      on inventory.film_id   = film.film_id
  group by film.film_id
  having count(rental.rental_id) = 5);

3. Multiple columns subquery

tampilkan customer first_name dan last_name serta jumlah yang
dibayar dari masing-masing customer
petunjuk:
- menggunakan tabel customer dan payment

    SELECT cu.first_name, cu.last_name, tabel_baru.jumlah_bayar
    FROM customer cu
    INNER JOIN (
      SELECT customer_id, SUM(amount) as jumlah_bayar
      FROM payment
      GROUP BY customer_id
    ) AS tabel_baru ON tabel_baru.customer_id = cu.customer_id

penjelasan proses
- subquery 1: menghasilkan customer_id dan jumlah_bayar
  - karena hasil dari subquery dalah tabel dengan banyak kolom
  - maka disebut multiple column subquery.
- subquery 2: hasil dari subquery 1 adalah tabel_baru
  - di lakukan join dengan tabel customer

Soal

Tampilkan customer yang tidak pernah menyewa
Tampilkan nama film yang paling banyak disewa
Tampilkan nama customer yang membayar paling banyak
Tampilkan category film yang paling sering disewa
Tampilkan actor yang tidak pernah bermain film
Tampilkan actor yang paling sering bermain film
Tampilkan 10 actor yang bermain film dengan category 'Horror'
Tampilkan nama nama actor yang filmnya paling sering dipinjam orang (rental)

jawaban

  --1--
  SELECT C.CUSTOMER_ID, C.FIRST_NAME
  FROM CUSTOMER C
  WHERE C.CUSTOMER_ID NOT IN (
    SELECT CUSTOMER_ID
    FROM RENTAL
  );

  --2--
  SELECT F.TITLE, COUNT(R.RENTAL_ID)
  FROM FILM F
  INNER JOIN INVENTORY I ON I.FILM_ID = F.FILM_ID
  INNER JOIN RENTAL R ON R.INVENTORY_ID = I.INVENTORY_ID
  GROUP BY F.TITLE
  ORDER BY 2 DESC
  LIMIT 1;

  --3--
  SELECT C.CUSTOMER_ID, C.FIRST_NAME, SUM(P.AMOUNT)
  FROM CUSTOMER C
  INNER JOIN RENTAL R ON R.CUSTOMER_ID = C.CUSTOMER_ID
  INNER JOIN PAYMENT P ON P.RENTAL_ID = R.RENTAL_ID
  GROUP BY C.CUSTOMER_ID, C.FIRST_NAME
  ORDER BY 3 DESC
  LIMIT 1;

  --4--
  SELECT CA.NAME, COUNT(R.RENTAL_ID)
  FROM CATEGORY CA
  INNER JOIN FILM_CATEGORY FC ON FC.CATEGORY_ID = CA.CATEGORY_ID
  INNER JOIN FILM F ON F.FILM_ID = FC.FILM_ID
  INNER JOIN INVENTORY I ON I.FILM_ID = F.FILM_ID
  INNER JOIN RENTAL R ON R.INVENTORY_ID = I.INVENTORY_ID
  GROUP BY CA.NAME
  ORDER BY 2 DESC
  LIMIT 1;

  --5--
  SELECT A.ACTOR_ID, A.FIRST_NAME
  FROM ACTOR A
  INNER JOIN FILM_ACTOR FA ON FA.ACTOR_ID = A.ACTOR_ID
  WHERE A.ACTOR_ID NOT IN(
    SELECT DISTINCT ACTOR_ID
    FROM FILM_ACTOR
    ORDER BY actor_id
  );

  --6--
  SELECT A.ACTOR_ID, A.FIRST_NAME, COUNT(FA.ACTOR_ID)
  FROM ACTOR A
  INNER JOIN FILM_ACTOR FA ON FA.ACTOR_ID = A.ACTOR_ID
  WHERE A.ACTOR_ID IN(
    SELECT ACTOR_ID
    FROM FILM_ACTOR
  )
  GROUP BY A.ACTOR_ID, A.FIRST_NAME
  ORDER BY 3 DESC
  LIMIT 1;

  --7-- E
  SELECT FA.ACTOR_ID, A.FIRST_NAME
  FROM ACTOR A
  INNER JOIN FILM_ACTOR FA ON FA.ACTOR_ID = A.ACTOR_ID
  INNER JOIN FILM F ON F.FILM_ID = FA.FILM_ID
  INNER JOIN FILM_CATEGORY FC ON FC.FILM_ID = F.FILM_ID
  INNER JOIN CATEGORY C ON C.CATEGORY_ID = FC.CATEGORY_ID
  WHERE C.NAME LIKE 'Horror'
  LIMIT 10;

  --8--
  SELECT FA.ACTOR_ID, A.FIRST_NAME, COUNT (R.RENTAL_ID)
  FROM ACTOR A
  INNER JOIN FILM_ACTOR FA ON FA.ACTOR_ID = A.ACTOR_ID
  INNER JOIN FILM F ON F.FILM_ID = FA.FILM_ID
  INNER JOIN INVENTORY I ON I.FILM_ID = F.FILM_ID
  INNER JOIN RENTAL R ON R.INVENTORY_ID = I.INVENTORY_ID
  GROUP BY FA.ACTOR_ID, A.FIRST_NAME
  ORDER BY 3 DESC;