Zenn
Open11

sakilaデータベース用の問題集【MySQL】

MySQL
DB
sakila
penpenpenpen

sakilaとは？

MySQLが用意しているサンプルデータベースです。

中身は「レンタルDVDショップ」をあらわしたものとなっています。

SQLの練習に最適なので「これを使って遊ぼう！」をやります。

penpenpenpen

いろいろな人に新しい問題を投稿してもらったり、SQLに強い人に「こう書いたほうが良い」などの指摘をもらったりしたいので、記事ではなくスクラップにしました！

気軽にコメントください！

penpenpenpen

俳優のフルネーム

actorテーブルから、first_nameとlast_nameをつなげてフルネームつくってください。

  • フルネームはすべて小文字で表示してください。
  • first_nameとlast_nameの間は半角スペース1個分を空けてください。

期待する答え👆期待する結果

答え
select LOWER(CONCAT(`first_name`," ", `last_name`)) from `actor`;
penpenpenpen

エジプトに住んでいる客のフルネーム

👆期待する結果

答え
SELECT
  concat (first_name, " ", last_name) fullName,
  country
FROM
  customer
  INNER JOIN address ON address.address_id = customer.address_id
  INNER JOIN city ON address.city_id = city.city_id
  INNER JOIN country ON country.country_id = city.country_id
  AND country.country = "Egypt";
penpenpenpen

出演している俳優の数が多い映画ランキング

映画のタイトルと、その映画に出演している俳優の数を抽出して、出演している俳優の数が多い順に並び替え、上位10個だけ抽出してください。
👆期待する結果

答え
SELECT
  film.title,
  COUNT(film.title) film_actor_count
FROM
  film
  INNER JOIN film_actor ON film.film_id = film_actor.film_id
GROUP BY
  film.title
ORDER BY
  film_actor_count desc
LIMIT
  10;
penpenpenpen

各顧客が支払った合計

合計200ドル以上支払った顧客のlast_nameを抽出してください。
👆期待する結果

答え
SELECT
  customer.last_name,
  SUM(payment.amount) total_payment
FROM
  customer
  INNER JOIN payment ON customer.customer_id = payment.customer_id
GROUP BY
  customer.customer_id
HAVING
  total_payment > 200
penpenpenpen

売上トップ5のカテゴリ

すべての期間で、売上トップ5のカテゴリを抽出してください。
👆期待する結果

答え
SELECT
  category.name,
  SUM(payment.amount)
FROM
  payment
  INNER JOIN rental ON rental.rental_id = payment.rental_id
  INNER JOIN inventory ON inventory.inventory_id = rental.inventory_id
  INNER JOIN film_category ON film_category.film_id = inventory.film_id
  INNER JOIN category ON category.category_id = film_category.category_id
GROUP BY
  category.name
ORDER BY
  SUM(payment.amount) desc
LIMIT
  5
penpenpenpen

年別で売上順のカテゴリ

年別で売上順に全カテゴリを抽出してください。
👆期待する結果

答え
SELECT
  category.name,
  YEAR(rental.rental_date) as rental_year,
  SUM(payment.amount) as total_amount
FROM
  payment
  INNER JOIN rental ON rental.rental_id = payment.rental_id
  INNER JOIN inventory ON inventory.inventory_id = rental.inventory_id
  INNER JOIN film_category ON film_category.film_id = inventory.film_id
  INNER JOIN category ON category.category_id = film_category.category_id
GROUP BY
    category.name,
    YEAR(rental.rental_date);

GROUP BYで2列指定するだけです。

penpenpenpen

年別で売上トップ5のカテゴリ

年別で売上トップ5順のカテゴリを抽出してください。

👆期待する結果

答え
SELECT
  name,
  rental_year,
  total_amount,
  ranking
FROM
  (SELECT
    category.name,
    YEAR(rental.rental_date) as rental_year,
    SUM(payment.amount) as total_amount,
    ROW_NUMBER() OVER (PARTITION BY YEAR(rental.rental_date) ORDER BY SUM(payment.amount) DESC) ranking
  FROM
    payment
    INNER JOIN rental ON rental.rental_id = payment.rental_id
    INNER JOIN inventory ON inventory.inventory_id = rental.inventory_id
    INNER JOIN film_category ON film_category.film_id = inventory.film_id
    INNER JOIN category ON category.category_id = film_category.category_id
  GROUP BY
    category.name,
    YEAR(rental.rental_date)) as ranking_table
WHERE
  ranking_table.ranking <= 5
ORDER BY
  rental_year,
  total_amount DESC;

おそらく、Window関数＋サブクエリを組み合わせないと無理かも？

他に良いやり方があったらどなたか教えて下さい🙇‍♂