Go to Qiita Advent Calendar Top
LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@nittamatama(にった ま)

『初めてのSQL 第3版』で取り上げられているクエリを練習問題化した①

Posted at

記事概要

オライリーから出版されているSQLに関する書籍『初めてのSQL 第3版』には、SQLクエリ例が豊富に記載されています。
https://www.oreilly.co.jp/books/9784873119588/

そのクエリを少しでも自分のものにしたいと思い、完全な自己満ですが各クエリの練習問題を作ってみました。

注意点

  • 問題文は書籍を持っていない方でも解けるように作成したつもりですが、載せているのはあくまで書籍に載っているクエリのみで、そのクエリに関する解説は載せたらさすがに問題になるだろうと思ったのでほぼ載せていません
    • 参考になる解説が豊富なので、気になる場合は書籍購入しましょう
  • 書籍の各章の終わりに載っている「練習問題」は載せていません。問題化されていないクエリだけ載せてます
  • 解答はあくまで書籍に載っているクエリをベースにしてるため、別の書き方の解答もあると思いますがそこはご容赦ください
  • 書籍の内容はMySQLが提供するサンプルデータベースであるSakila Sample Databaseを使用して書かれているため、クエリは基本的にMySQLをベースにしています

Sakila Sample Database環境構築

どんな環境の作り方でも良いとは思いますが、一応以下の記事でDocker Composeを使用してMySQL8.0のSakila Sample Databaseをサクッと作る方法をまとめてますので、必要な方は参考にしていただき環境を用意してください

それでは以下より問題スタート(問題はそれぞれ各章の見出しをそのまま入れています)

第三章 クエリ

列エイリアス

languageテーブルから以下の要件を満たすクエリを作成してください。

  1. language_idの値を出力すること
  2. すべての行に対して、文字列リテラル 'COMMON' を返す列（エイリアスをlanguage_usageとすること）
  3. language_id の値に円周率の近似値 3.1415927 を乗算した結果を返す列（エイリアスを lang_pi_valueとすること）を出力すること
  4. name列の値をすべて大文字に変換した結果を返す列（エイリアスをlanguage_nameとすること）
回答 
mysql> SELECT language_id,
->   'COMMON' AS language_usage,
->   language_id * 3.1415927 AS lang_pi_value,
->   UPPER(name) language_name
-> FROM language;
+-------------+----------------+---------------+---------------+
| language_id | language_usage | lang_pi_value | language_name |
+-------------+----------------+---------------+---------------+
|           1 | COMMON         |     3.1415927 | ENGLISH       |
|           2 | COMMON         |     6.2831854 | ITALIAN       |
|           3 | COMMON         |     9.4247781 | JAPANESE      |
|           4 | COMMON         |    12.5663708 | MANDARIN      |
|           5 | COMMON         |    15.7079635 | FRENCH        |
|           6 | COMMON         |    18.8495562 | GERMAN        |
+-------------+----------------+---------------+---------------+
6 rows in set (0.00 sec)

重複削除

映画の出演記録が格納されている film_actor テーブル には、各映画に出演した俳優の ID（actor_id）が記録されていますが、一人の俳優が複数の映画に出演しているため、IDが重複して格納されています。

このテーブルに対してクエリを実行し、出演した俳優全員の ID のリストを取得してください。ただし、同じ俳優 ID が複数出力されないよう、重複を取り除いたリストとし、結果は actor_id の昇順で並べ替えること。

回答 
mysql> SELECT DISTINCT actor_id FROM film_actor ORDER BY actor_id;
+----------+
| actor_id |
+----------+
|        1 |
|        2 |
|        3 |
|        4 |
|        5 |
|        6 |
|        7 |
|        8 |
|        9 |
|       10 |
......
|      192 |
|      193 |
|      194 |
|      195 |
|      196 |
|      197 |
|      198 |
|      199 |
|      200 |
+----------+
200 rows in set (0.00 sec)

一時テーブル

以下1〜3を順に行なってください

概要
actorテーブルにある名字（last_name）が「J」で始まる俳優のデータを一時的に格納するための環境を準備し、結果を確認する一連の操作を実行したい

①: 一時テーブルの作成
actorテーブルからデータを一時的に格納するために、以下の列を持つ一時テーブル actors_j を作成してください。
• actor_id (smallint(5))
• first_name (varchar(45))
• last_name (varchar(45))

②: データの挿入
作成した一時テーブル actors_j に、永続テーブル actor からデータを挿入してください。挿入するデータは、last_name が「J」で始まる俳優の actor_id、first_name、last_name の各列の値とします。

③: データの確認
最後に、作成した一時テーブル actors_j に挿入されたすべての行を確認するクエリを実行してください。

回答 
①
mysql> CREATE TEMPORARY TABLE actors_j
->  (actor_id smallint(5),
->   first_name varchar(45),
->   last_name varchar(45)
->  );
Query OK, 0 rows affected, 1 warning (0.00 sec)

②
mysql> INSERT INTO actors_j
-> SELECT actor_id, first_name, last_name
-> FROM actor
-> WHERE last_name LIKE 'J%';
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

③
mysql> SELECT * FROM actors_j;
+----------+------------+-----------+
| actor_id | first_name | last_name |
+----------+------------+-----------+
|      119 | WARREN     | JACKMAN   |
|      131 | JANE       | JACKMAN   |
|        8 | MATTHEW    | JOHANSSON |
|       64 | RAY        | JOHANSSON |
|      146 | ALBERT     | JOHANSSON |
|       82 | WOODY      | JOLIE     |
|       43 | KIRK       | JOVOVICH  |
+----------+------------+-----------+
7 rows in set (0.00 sec)

昇順と降順

①
customer テーブルと rental テーブルを結合して、レンタル日（rental_date datetime型）が'2005-06-14'に映画をレンタルした顧客ID（customer_id）と姓名(last_name)(first_name)のリストを、レンタルした時刻が新しい順に取得するクエリを作成してください。

回答 
mysql> SELECT c.first_name, c.last_name,
    ->   time(r.rental_date) rental_time
    -> FROM customer c
    ->   INNER JOIN rental r
    ->   ON c.customer_id = r.customer_id
    -> WHERE date(r.rental_date) = '2005-06-14'
    -> ORDER BY time(r.rental_date) desc;
+------------+-----------+-------------+
| first_name | last_name | rental_time |
+------------+-----------+-------------+
| JEANETTE   | GREENE    | 23:54:46    |
| CHARLES    | KOWALSKI  | 23:54:34    |
| SONIA      | GREGORY   | 23:50:11    |
| TERRENCE   | GUNDERSON | 23:47:35    |
| AMBER      | DIXON     | 23:42:56    |
| HERMAN     | DEVORE    | 23:35:09    |
| MATTHEW    | MAHAN     | 23:25:58    |
| CATHERINE  | CAMPBELL  | 23:17:03    |
| GWENDOLYN  | MAY       | 23:16:27    |
| JOYCE      | EDWARDS   | 23:16:26    |
| TERRANCE   | ROUSH     | 23:12:46    |
| DANIEL     | CABRAL    | 23:09:38    |
| MIRIAM     | MCKINNEY  | 23:07:08    |
| MINNIE     | ROMERO    | 23:00:34    |
| ELMER      | NOE       | 22:55:13    |
| JEFFERY    | PINSON    | 22:53:33    |
+------------+-----------+-------------+
16 rows in set (0.02 sec)

②
また、この問題のORDER BYに指定されている値をプレースホルダに置き換えるとどういった書き方になりますか？

回答

あんま推奨されない書き方っぽい


mysql> SELECT c.first_name, c.last_name,
    ->   time(r.rental_date) rental_time
    -> FROM customer c
    ->   INNER JOIN rental r
    ->   ON c.customer_id = r.customer_id
    -> WHERE date(r.rental_date) = '2005-06-14'
    -> ORDER BY 3 desc;

第四章 フィルタリング

不等号条件

レンタル顧客のcustomerテーブルと、レンタル記録を格納するrentalテーブルを結合して、2005-06-14以外の日付に発生したすべてのレンタル日時(rental_date)に対応する顧客のメールアドレス(email)を取得するクエリを作成してください。

回答 
-- <>ではなく!=でもOK
mysql> SELECT c.email
-> FROM customer c
->   INNER JOIN rental r
->   ON c.customer_id = r.customer_id
-> WHERE date(r.rental_date) <> '2005-06-14';
+------------------------------------------+
| email                                    |
+------------------------------------------+
| MARY.SMITH@sakilacustomer.org            |
| MARY.SMITH@sakilacustomer.org            |
| MARY.SMITH@sakilacustomer.org            |
| MARY.SMITH@sakilacustomer.org            |
| MARY.SMITH@sakilacustomer.org            |
| MARY.SMITH@sakilacustomer.org            |
| MARY.SMITH@sakilacustomer.org            |
| MARY.SMITH@sakilacustomer.org            |
| MARY.SMITH@sakilacustomer.org            |
......
| AUSTIN.CINTRON@sakilacustomer.org        |
| AUSTIN.CINTRON@sakilacustomer.org        |
| AUSTIN.CINTRON@sakilacustomer.org        |
| AUSTIN.CINTRON@sakilacustomer.org        |
| AUSTIN.CINTRON@sakilacustomer.org        |
| AUSTIN.CINTRON@sakilacustomer.org        |
| AUSTIN.CINTRON@sakilacustomer.org        |
| AUSTIN.CINTRON@sakilacustomer.org        |
| AUSTIN.CINTRON@sakilacustomer.org        |
+------------------------------------------+
16028 rows in set (0.02 sec)

範囲条件

①
rental テーブルから、以下の日付範囲内で行われたレンタルの顧客ID（customer_id）とレンタル日時（rental_date）を取得するクエリを作成してください。

要件:
フィルタリング条件として、rental_date が 2005-6-14 00:00:00から2005-6-15 23:59:59までの間に含まれる行のみを抽出します。

解答 
mysql> SELECT customer_id, rental_date
-> FROM rental
-> WHERE rental_date <= '2005-06-16'
->   AND rental_date >= '2005-06-14';
+-------------+---------------------+
| customer_id | rental_date         |
+-------------+---------------------+
|         416 | 2005-06-14 22:53:33 |
|         516 | 2005-06-14 22:55:13 |
|         239 | 2005-06-14 23:00:34 |
|         285 | 2005-06-14 23:07:08 |
|         310 | 2005-06-14 23:09:38 |
|         592 | 2005-06-14 23:12:46 |
......
|         148 | 2005-06-15 23:20:26 |
|         237 | 2005-06-15 23:36:37 |
|         155 | 2005-06-15 23:55:27 |
|         341 | 2005-06-15 23:57:20 |
|         149 | 2005-06-15 23:58:53 |
+-------------+---------------------+
364 rows in set (0.00 sec)

-- もしくはBETWEEN
mysql> SELECT customer_id, rental_date
    -> FROM rental
    -> WHERE rental_date BETWEEN '2005-06-14' AND '2005-06-16';

②
customer テーブルに格納されている顧客情報から、特定のアルファベットの範囲内に名字（last_name）が含まれる顧客のリストを取得するクエリを作成してください。

要件:

  1. 取得対象の列は苗字（last_name）と名前（first_name）とします。
  2. フィルタリング条件として、last_nameが文字列'FA' から'FRB'までの範囲に含まれる顧客を抽出します。
解答 
mysql> SELECT last_name, first_name
-> FROM customer
-> WHERE last_name BETWEEN 'FA' AND 'FRB';
+------------+------------+
| last_name  | first_name |
+------------+------------+
| FARNSWORTH | JOHN       |
| FENNELL    | ALEXANDER  |
| FERGUSON   | BERTHA     |
| FERNANDEZ  | MELINDA    |
| FIELDS     | VICKI      |
| FISHER     | CINDY      |
| FLEMING    | MYRTLE     |
| FLETCHER   | MAE        |
| FLORES     | JULIA      |
| FORD       | CRYSTAL    |
| FORMAN     | MICHEAL    |
| FORSYTHE   | ENRIQUE    |
| FORTIER    | RAUL       |
| FORTNER    | HOWARD     |
| FOSTER     | PHYLLIS    |
| FOUST      | JACK       |
| FOWLER     | JO         |
| FOX        | HOLLY      |
| FRALEY     | JUAN       |
| FRANCISCO  | JOEL       |
| FRANKLIN   | BETH       |
| FRAZIER    | GLENDA     |
+------------+------------+
22 rows in set (0.00 sec)

余裕があったら、文字セット内順序を把握するために照合順序を調べましょう

メンバーシップ条件

①
映画情報が格納されているfilmテーブルから、以下のいずれかのレーティング(rating)条件を満たす映画のタイトル(title)とレーティング(rating)を取得するクエリを作成してください。

要件: 映画のレーティング（rating）が 'G' または 'PG' である

解答 
mysql> SELECT title, rating
-> FROM film
-> WHERE rating = 'G' OR rating = 'PG';
+---------------------------+--------+
| title                     | rating |
+---------------------------+--------+
| ACADEMY DINOSAUR          | PG     |
| ACE GOLDFINGER            | G      |
| AFFAIR PREJUDICE          | G      |
| AFRICAN EGG               | G      |
| AGENT TRUMAN              | PG     |
| ALAMO VIDEOTAPE           | G      |
| ALASKA PHANTOM            | PG     |
| ALI FOREVER               | PG     |
| AMADEUS HOLY              | PG     |
......
| WEDDING APOLLO            | PG     |
| WEREWOLF LOLA             | G      |
| WEST LION                 | G      |
| WIZARD COLDBLOODED        | PG     |
| WON DARES                 | PG     |
| WONDERLAND CHRISTMAS      | PG     |
| WORDS HUNTER              | PG     |
| WORST BANGER              | PG     |
| YOUNG LANGUAGE            | G      |
+---------------------------+--------+
372 rows in set (0.01 sec)

-- `IN`を使っても勿論OK
SELECT title, rating
FROM film
WHERE rating IN ('G','PG');

②
タイトル(title)にPETという文字列を持つ映画のratingに絞って出力してください(ヒントはサブクエリ)

解答 
mysql> SELECT title, rating
    -> FROM film
    -> WHERE rating IN (SELECT rating FROM film WHERE title LIKE '%PET%');

+---------------------------+--------+
| title                     | rating |
+---------------------------+--------+
| ACADEMY DINOSAUR          | PG     |
| ACE GOLDFINGER            | G      |
| AFFAIR PREJUDICE          | G      |
| AFRICAN EGG               | G      |
| AGENT TRUMAN              | PG     |
| ALAMO VIDEOTAPE           | G      |
| ALASKA PHANTOM            | PG     |
| ALI FOREVER               | PG     |
| AMADEUS HOLY              | PG     |
......
| WEDDING APOLLO            | PG     |
| WEREWOLF LOLA             | G      |
| WEST LION                 | G      |
| WIZARD COLDBLOODED        | PG     |
| WON DARES                 | PG     |
| WONDERLAND CHRISTMAS      | PG     |
| WORDS HUNTER              | PG     |
| WORST BANGER              | PG     |
| YOUNG LANGUAGE            | G      |
+---------------------------+--------+
372 rows in set (0.01 sec)

マッチング条件

customer テーブルに格納されている顧客情報から、以下の要件を満たす顧客の苗字（last_name）と名前（first_name）を取得するクエリを作成してください。
要件:

  • フィルタリング条件として、last_name が 'Q' で始まる行、または last_name が'Y' で始まる行を抽出してください
  • ワイルドカードか、もしくは正規表現を使って取得してください
解答 
mysql> SELECT last_name, first_name
    -> FROM customer
    -> WHERE last_name LIKE 'Q%' OR last_name LIKE 'Y%';
+-------------+------------+
| last_name   | first_name |
+-------------+------------+
| QUALLS      | STEPHEN    |
| QUIGLEY     | TROY       |
| QUINTANILLA | ROGER      |
| YANEZ       | LUIS       |
| YEE         | MARVIN     |
| YOUNG       | CYNTHIA    |
+-------------+------------+
6 rows in set (0.00 sec)

もしくは正規表現

mysql> SELECT last_name, first_name
    -> FROM customer
    -> WHERE last_name REGEXP '^[QY]';

第五章 複数のテーブルからデータを取得する

内部結合

①
顧客の個人情報が格納されているcustomerテーブルと、顧客住所情報が格納されているaddressテーブルから各顧客の名前(first_name, last_name)と対応する住所(address)を結合して一覧化するクエリを作成してください。

解答 
mysql> SELECT c.first_name, c.last_name, a.address
-> FROM customer AS c INNER JOIN address AS a
->   ON c.address_id = a.address_id;
+-------------+--------------+----------------------------------------+
| first_name  | last_name    | address                                |
+-------------+--------------+----------------------------------------+
| MARY        | SMITH        | 1913 Hanoi Way                         |
| PATRICIA    | JOHNSON      | 1121 Loja Avenue                       |
| LINDA       | WILLIAMS     | 692 Joliet Street                      |
| BARBARA     | JONES        | 1566 Inegl Manor                       |
| ELIZABETH   | BROWN        | 53 Idfu Parkway                        |
| JENNIFER    | DAVIS        | 1795 Santiago de Compostela Way        |
| MARIA       | MILLER       | 900 Santiago de Compostela Parkway     |
| SUSAN       | WILSON       | 478 Joliet Way                         |
| MARGARET    | MOORE        | 613 Korolev Drive                      |
......
| TERRANCE    | ROUSH        | 42 Fontana Avenue                      |
| RENE        | MCALISTER    | 1895 Zhezqazghan Drive                 |
| EDUARDO     | HIATT        | 1837 Kaduna Parkway                    |
| TERRENCE    | GUNDERSON    | 844 Bucuresti Place                    |
| ENRIQUE     | FORSYTHE     | 1101 Bucuresti Boulevard               |
| FREDDIE     | DUGGAN       | 1103 Quilmes Boulevard                 |
| WADE        | DELVALLE     | 1331 Usak Boulevard                    |
| AUSTIN      | CINTRON      | 1325 Fukuyama Street                   |
+-------------+--------------+----------------------------------------+
599 rows in set (0.00 sec)

②
上記はANSI SQL規格のSQL92バージョンの結合が正答だが、古い結合構文で上記を作成してください

解答 
SELECT c.first_name, c.last_name, a.address
    -> FROM customer c, address a
    -> WHERE c.address_id = a.address_id;

多少短くは書けるが、結合条件とフィルタ条件の区別がつきにくいなどのデメリットがある
ただ、だいたいのRDBMSで動く

3つ以上のテーブルを結合する

customerテーブルにある顧客の名前(first_name, last_name)、およびcityテーブルにある顧客が居住している都市名(city)を取得するクエリを記述してください

解答 
mysql> SELECT c.first_name, c.last_name, ct.city
-> FROM customer c
->   INNER JOIN address a
->   ON c.address_id = a.address_id
->   INNER JOIN city ct
->   ON a.city_id = ct.city_id;
+-------------+--------------+----------------------------+
| first_name  | last_name    | city                       |
+-------------+--------------+----------------------------+
| MARY        | SMITH        | Sasebo                     |
| PATRICIA    | JOHNSON      | San Bernardino             |
| LINDA       | WILLIAMS     | Athenai                    |
| BARBARA     | JONES        | Myingyan                   |
| ELIZABETH   | BROWN        | Nantou                     |
| JENNIFER    | DAVIS        | Laredo                     |
......
| SETH        | HANNON       | al-Manama                  |
| KENT        | ARSENAULT    | Juiz de Fora               |
| TERRANCE    | ROUSH        | Szkesfehrvr                |
| RENE        | MCALISTER    | Garden Grove               |
| EDUARDO     | HIATT        | Jining                     |
| TERRENCE    | GUNDERSON    | Jinzhou                    |
| ENRIQUE     | FORSYTHE     | Patras                     |
| FREDDIE     | DUGGAN       | Sullana                    |
| WADE        | DELVALLE     | Lausanne                   |
| AUSTIN      | CINTRON      | Tieli                      |
+-------------+--------------+----------------------------+
599 rows in set (0.00 sec)

小ネタ問題
上記をオプティマイザの実行計画で結合順序（どのテーブルを先に読み込むか）を決めるのではなく、記述した順番にテーブル結合させる関数で書き換えてください

解答 
SELECT STRAIGHT_JOIN c.first_name, c.last_name, ct.city
FROM city ct
  INNER JOIN address a
  ON a.city_id = ct.city_id
  INNER JOIN customer c
  ON c.address_id = a.address_id;

サブクエリをテーブルとして使う

addressテーブルの地区(district)の値が'California'である顧客の氏名(first_name, last_name) 住所(address) 都市名(city)を取得してください

制約: 派生テーブル（サブクエリ）を使用して結合を行い上記を取得してください

使用するテーブル
• customer テーブル
• address テーブル
• city テーブル

解答 
mysql> SELECT c.first_name, c.last_name, addr.address, addr.city
-> FROM customer c
->   INNER JOIN
->    (SELECT a.address_id, a.address, ct.city
->     FROM address a
->       INNER JOIN city ct
->       ON a.city_id = ct.city_id
->     WHERE a.district = 'California'
->    ) addr
->   ON c.address_id = addr.address_id;
+------------+-----------+------------------------+----------------+
| first_name | last_name | address                | city           |
+------------+-----------+------------------------+----------------+
| PATRICIA   | JOHNSON   | 1121 Loja Avenue       | San Bernardino |
| BETTY      | WHITE     | 770 Bydgoszcz Avenue   | Citrus Heights |
| ALICE      | STEWART   | 1135 Izumisano Parkway | Fontana        |
| ROSA       | REYNOLDS  | 793 Cam Ranh Avenue    | Lancaster      |
| RENEE      | LANE      | 533 al-Ayn Boulevard   | Compton        |
| KRISTIN    | JOHNSTON  | 226 Brest Manor        | Sunnyvale      |
| CASSANDRA  | WALTERS   | 920 Kumbakonam Loop    | Salinas        |
| JACOB      | LANCE     | 1866 al-Qatif Avenue   | El Monte       |
| RENE       | MCALISTER | 1895 Zhezqazghan Drive | Garden Grove   |
+------------+-----------+------------------------+----------------+
9 rows in set (0.00 sec)

同じテーブルを2回使う

①
特定の俳優2名（CATE MCQUEENとCUBA BIRCH）が出演している全ての映画のタイトル(title)を、映画情報が入ったfilmテーブルから取得するクエリを記述してください。
2人の名前はactorテーブルにfirst_nameとlast_nameで入っています。

使用するテーブル

  • film
  • film_actor
    • 出演している映画と俳優を紐づけている中間テーブル
  • actor
解答 
mysql> SELECT f.title
-> FROM film f
->   INNER JOIN film_actor fa
->   ON f.film_id = fa.film_id
->   INNER JOIN actor a
->   ON fa.actor_id = a.actor_id
-> WHERE ((a.first_name = 'CATE' AND a.last_name = 'MCQUEEN')
->   OR (a.first_name = 'CUBA' AND a.last_name = 'BIRCH'));
+----------------------+
| title                |
+----------------------+
| ATLANTIS CAUSE       |
| BLOOD ARGONAUTS      |
| COMMANDMENTS EXPRESS |
| DYNAMITE TARZAN      |
| EDGE KISSING         |
......
| TOWERS HURRICANE     |
| TROJAN TOMORROW      |
| VIRGIN DAISY         |
| VOLCANO TEXAS        |
| WATERSHIP FRONTIER   |
+----------------------+
54 rows in set (0.01 sec)

②
次に、この俳優2名（CATE MCQUEENとCUBA BIRCH）が共演している映画のタイトル(title)を取得するクエリを記述してください。

解答 
mysql> SELECT f.title
-> FROM film f
->   INNER JOIN film_actor fa1
->   ON f.film_id = fa1.film_id
->   INNER JOIN actor a1
->   ON fa1.actor_id = a1.actor_id
->   INNER JOIN film_actor fa2
->   ON f.film_id = fa2.film_id
->   INNER JOIN actor a2
->   ON fa2.actor_id = a2.actor_id
-> WHERE (a1.first_name = 'CATE' AND a1.last_name = 'MCQUEEN')
->   AND (a2.first_name = 'CUBA' AND a2.last_name = 'BIRCH');
+------------------+
| title            |
+------------------+
| BLOOD ARGONAUTS  |
| TOWERS HURRICANE |
+------------------+
2 rows in set (0.00 sec)

6章 集合

UNION演算子

以下の条件を満たす人物（顧客または俳優）の氏名のリストを取得するクエリを記述してください。

要件

  1. ファーストネーム(first_name)が「J」で始まる。
  2. ラストネーム(last_name)が「D」で始まる。

上記の条件を満たす人物を、顧客 (customer) テーブルと 俳優 (actor) テーブルの両方から検索し、重複を取り除いた単一の結果セットとして表示してください

解答 
mysql> SELECT c.first_name, c.last_name
-> FROM customer c
-> WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%'
-> UNION
-> SELECT a.first_name, a.last_name
-> FROM actor a
-> WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| JENNIFER   | DAVIS     |
| JUDY       | DEAN      |
| JODIE      | DEGENERES |
| JULIANNE   | DENCH     |
+------------+-----------+
4 rows in set (0.00 sec)

INTERSECT演算子(積集合)

customerテーブルとactorテーブルを使用し、以下のすべての条件を満たす行を取得するクエリを記述してください。

要件:

  1. 対象の人物のfirst_nameが文字'J'で始まる。
  2. 対象の人物のlast_nameが文字'D' で始まる。
  3. 上記の条件を満たす結果セットを比較し、両方のテーブルに共通する行のみを抽出して出力してください(俳優としても顧客としても登録されている人を探す)
解答 
SELECT c.first_name, c.last_name
FROM customer c
WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%'
INTERSECT
SELECT a.first_name, a.last_name
FROM actor a
WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| JENNIFER   | DAVIS     |
+------------+-----------+
1 row in set (0.00 sec)

EXCEPT演算子(差集合)

customerテーブルとactorテーブルを使用し、以下のすべての条件を満たす行を取得するクエリを記述してください。

要件:
俳優(actor)として登録されているが、顧客(customer)としては登録されていない人物のリストを抽出すること

フィルタ条件:

  1. 対象の人物のfirst_nameが文字'J'で始まる。
  2. 対象の人物のlast_nameが文字'D' で始まる。
解答 
SELECT a.first_name, a.last_name
FROM actor a
WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%'
EXCEPT
SELECT c.first_name, c.last_name
FROM customer c
WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%';
Set Operators | 109
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| JUDY       | DEAN      |
| JODIE      | DEGENERES |
| JULIANNE   | DENCH     |
+------------+-----------+
3 rows in set (0.00 sec)

集合演算の優先順位

以下の2つの複合クエリは、()ありなしで結果が異なっている
この理由を答えよ

ysql> SELECT a.first_name, a.last_name
    -> FROM actor a
    -> WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%'
    -> UNION
    -> (SELECT a.first_name, a.last_name
    ->  FROM actor a
    ->  WHERE a.first_name LIKE 'M%' AND a.last_name LIKE 'T%'
    ->  UNION ALL
    ->  SELECT c.first_name, c.last_name
    ->  FROM customer c
    ->  WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%'
    -> );
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| JENNIFER   | DAVIS     |
| JUDY       | DEAN      |
| JODIE      | DEGENERES |
| JULIANNE   | DENCH     |
| MARY       | TANDY     |
| MENA       | TEMPLE    |
+------------+-----------+
6 rows in set (0.02 sec)

mysql> SELECT a.first_name, a.last_name
    -> FROM actor a
    -> WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%'
    -> UNION
    -> SELECT a.first_name, a.last_name
    -> FROM actor a
    -> WHERE a.first_name LIKE 'M%' AND a.last_name LIKE 'T%'
    -> UNION ALL
    -> SELECT c.first_name, c.last_name
    -> FROM customer c
    -> WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| JENNIFER   | DAVIS     |
| JUDY       | DEAN      |
| JODIE      | DEGENERES |
| JULIANNE   | DENCH     |
| MARY       | TANDY     |
| MENA       | TEMPLE    |
| JENNIFER   | DAVIS     |
+------------+-----------+
7 rows in set (0.02 sec)
解答 結果の違いが生じた理由は、丸括弧の使用により、UNION（重複排除）の適用範囲が制御されたためです。 • 括弧あり (6行): 最後のUNIONがクエリ全体の結果セットに対して適用され、すべての重複が排除されました。 • 括弧なし (7行): UNION ALLによって統合された結果セットには重複排除が適用されず、JENNIFER DAVISの重複行（俳優と顧客）がそのまま残り、行数が1つ増えました。

デフォルトの挙動は、隣り合ったクエリを丸かっこで囲むことで 複合クエリの「上から順に処理する」 という設定になっている

7章

タイムゾーンに対処する

①
MySQLサーバーのグローバルおよびセッションのタイムゾーン設定を確認してください

details 
mysql> select @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+
1 row in set (0.00 sec)
  • systemは、データベースがインストールされているサーバのタイムゾーン設定を使用するということ

②
rentalテーブルのrental_idが99999のレコードのreturn_dateを2019-09-17 15:30:00に変更してください

解答 
UPDATE rental
SET return_date = '2019-09-17 15:30:00'
WHERE rental_id = 99999;
  • datetime型カラムに対して文字列で値を渡しても、MySQLが自動的に変換を試みている

文字列から日付への変換

テーブルを指定せず、'2019-09-17 15:30:00'という文字列を日付型に変換して表示してください

解答 
SELECT CAST('2019-09-17 15:30:00' AS datetime);

日付を生成するための関数

①
rentalテーブルのrental_idが99999のレコードのreturn_dateを、'September 17, 2019'という文字列からdatetime型に変換して上書きしてください

解答 
UPDATE rental
SET return_date = STR_TO_DATE('September 17, 2019', '%M %d, %Y')
WHERE rental_id = 99999;
  • STR_TO_DATEってOracleでも使えるらしい

②
rentalテーブルのrental_idが99999のレコードのreturn_dateが、実際は3時間27分11秒後ということが判明しました　正しい時間にアップデートしてください

解答 
UPDATE rental
SET return_date = DATE_ADD(return_date, INTERVAL '3:27:11' HOUR_SECOND)
WHERE rental_id = 99999;

変換関数

'999ABC111'という文字列をCASTを使用して数値に変換できる箇所まで変換してください
また文字列が完全に変換できないため、MySQLから出力される警告文を確認してください

解答 
mysql> SELECT CAST('999ABC111' AS UNSIGNED INTEGER);
+---------------------------------------+
| CAST('999ABC111' AS UNSIGNED INTEGER) |
+---------------------------------------+
|                                   999 |
+---------------------------------------+
1 row in set, 1 warning (0.01 sec)

mysql> SHOW warnings;
+---------+------+------------------------------------------------+
| Level   | Code | Message                                        |
+---------+------+------------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: '999ABC111' |
+---------+------+------------------------------------------------+
1 row in set (0.00 sec)

mysql>

8章 グループ化と集計

グループ化

①
映画のレンタルに関する情報が入っているrentalテーブルを使用し、最もレンタル回数が多い顧客ID(customer_id)を特定するためのクエリを記述してください(レンタル回数が多い順に並べる)
rentalテーブルには、1回のレンタル記録ごとにレコードが記録されています

解答 
mysql> SELECT customer_id, count(*)
-> FROM rental
-> GROUP BY customer_id
-> ORDER BY 2 DESC;
+-------------+----------+
| customer_id | count() |
+-------------+----------+
|         148 |       46 |
|         526 |       45 |
|         144 |       42 |
|         236 |       42 |
|          75 |       41 |
......
|         136 |       15 |
|         248 |       15 |
|          61 |       14 |
|         110 |       14 |
|         281 |       14 |
|         318 |       12 |
+-------------+----------+
599 rows in set (0.00 sec)

②
rentalテーブルを使用し、レンタル回数が40回以上の顧客ID(customer_id)を取得するクエリを記述してください。

解答 
mysql> SELECT customer_id, count()
-> FROM rental
-> GROUP BY customer_id
-> HAVING count() >= 40;
+-------------+----------+
| customer_id | count(*) |
+-------------+----------+
|          75 |       41 |
|         144 |       42 |
|         148 |       46 |
|         197 |       40 |
|         236 |       42 |
|         469 |       40 |
|         526 |       45 |
+-------------+----------+
7 rows in set (0.00 sec)

式を使う(集計関数)

rentalテーブルを使用し、このテーブルに記録されている全レコードの中から、レンタル期間(貸出日rental_date 〜 返却日return_date)が最も長い日数を特定するクエリを記述してください。

解答 
mysql> SELECT MAX(datediff(return_date,rental_date))
    -> FROM rental;
+----------------------------------------+
| MAX(datediff(return_date,rental_date)) |
+----------------------------------------+
|                                     10 |
+----------------------------------------+
1 row in set (0.01 sec)

複数の列によるグループ化

film_actor(actorとfilmの中間) テーブルとfilm(映画)テーブルを使用し、各俳優ID(actor_id)が映画のレーティング(rating)毎にいくつの映画に出演しているかを集計して出力してください
また、クエリの結果は俳優ID (actor_id)とレーティング (rating) の順に並べ替えられているようにしてください

解答 
mysql> SELECT fa.actor_id, f.rating, count()
-> FROM film_actor fa
->   INNER JOIN film f
->   ON fa.film_id = f.film_id
-> GROUP BY fa.actor_id, f.rating
-> ORDER BY 1,2;
+----------+--------+----------+
| actor_id | rating | count() |
+----------+--------+----------+
|        1 | G      |        4 |
|        1 | PG     |        6 |
|        1 | PG-13  |        1 |
|        1 | R      |        3 |
|        1 | NC-17  |        5 |
|        2 | G      |        7 |
|        2 | PG     |        6 |
|        2 | PG-13  |        2 |
|        2 | R      |        2 |
|        2 | NC-17  |        8 |
......
|      199 | G      |        3 |
|      199 | PG     |        4 |
|      199 | PG-13  |        4 |
|      199 | R      |        2 |
|      199 | NC-17  |        2 |
|      200 | G      |        5 |
|      200 | PG     |        3 |
|      200 | PG-13  |        2 |
|      200 | R      |        6 |
|      200 | NC-17  |        4 |
+----------+--------+----------+
996 rows in set (0.01 sec)

式によるグループ化

rentalテーブルを使用し、年ごとのレンタル回数を集計するクエリを記述してください
年はrental_dateの中から取得してください

解答 
mysql> SELECT extract(YEAR FROM rental_date) year,
->   COUNT(*) how_many
-> FROM rental
-> GROUP BY extract(YEAR FROM rental_date);
+------+----------+
| year | how_many |
+------+----------+
| 2005 |    15862 |
| 2006 |      182 |
+------+----------+
2 rows in set (0.01 sec)

小計と総計を生成する

film_actorテーブルとfilmテーブルを使用し、小計と総計を含む集計リストを作成するクエリを記述してください。

要件
以下の階層的な情報を含む単一のレポートを作成します。

  1. 各俳優ID (actor_id) が、各レーティング (rating) 毎に出演した回数(ratingごとの小計)
  2. 各俳優ID (actor_id) が、レーティングに関係なく出演した総数(各actor_idのratingごとの小計)
  3. 全体で登録されている映画と俳優の関連付けの総数(各actor_idのratingごとの総計 == film_actorテーブルの行数)
  4. クエリの結果は俳優ID (actor_id) および レーティング (rating) の昇順で並べ替えられている必要があります
解答 
mysql> SELECT fa.actor_id, f.rating, count()
-> FROM film_actor fa
->   INNER JOIN film f
->   ON fa.film_id = f.film_id
-> GROUP BY fa.actor_id, f.rating WITH ROLLUP
-> ORDER BY 1,2;
+----------+--------+----------+
| actor_id | rating | count() |
+----------+--------+----------+
|     NULL | NULL   |     5462 |
|        1 | NULL   |       19 |
|        1 | G      |        4 |
|        1 | NC-17  |        5 |
|        1 | PG     |        6 |
|        1 | PG-13  |        1 |
|        1 | R      |        3 |
|        2 | NULL   |       25 |
|        2 | G      |        7 |
|        2 | NC-17  |        8 |
|        2 | PG     |        6 |
|        2 | PG-13  |        2 |
|        2 | R      |        2 |
......
|      199 | NULL   |       15 |
|      199 | G      |        3 |
|      199 | NC-17  |        2 |
|      199 | PG     |        4 |
|      199 | PG-13  |        4 |
|      199 | R      |        2 |
|      200 | NULL   |       20 |
|      200 | G      |        5 |
|      200 | NC-17  |        4 |
|      200 | PG     |        3 |
|      200 | PG-13  |        2 |
|      200 | R      |        6 |
+----------+--------+----------+
1197 rows in set (0.01 sec)
  • 結果セットの先頭(5462)がfilm_actorテーブルの総計
  • 各actor_idの先頭にあるratingがNULLの行が各俳優ID (actor_id) が、レーティングに関係なく出演した総数
  • 上記以外の行が各レーティング (rating) 毎に出演した回数
    WITH ROLLUPに関して
    https://gihyo.jp/article/2023/08/mysql-rcn0204

グループ化のフィルタ条件

film_actorテーブルとfilmテーブルを使用し、以下のすべての条件を満たす俳優の出演データを抽出するクエリを記述してください。

目的
特定のレーティング(rating)において、出演回数が一定数を超える俳優ID(actor_id)を特定したい

フィルタ条件:

  1. 対象とする映画は、レーティング(rating)が 'G' または 'PG' のいずれかに限定されていること
  2. 各actor_idのratingごとに出演回数を集計し、その組み合わせにおける出演回数 (count(*)) が 10回以上である俳優IDのみを結果として出力する
  3. 出力は、以下の3つの列を含み、俳優ID (actor_id) および レーティング (rating) の昇順で並べ替えられている必要がある
解答 
mysql> SELECT fa.actor_id, f.rating, count()
-> FROM film_actor fa
->   INNER JOIN film f
->   ON fa.film_id = f.film_id
-> WHERE f.rating IN ('G','PG')
-> GROUP BY fa.actor_id, f.rating
-> HAVING count() > 9;
+----------+--------+----------+
| actor_id | rating | count(*) |
+----------+--------+----------+
|      137 | PG     |       10 |
|       37 | PG     |       12 |
|      180 | PG     |       12 |
|        7 | G      |       10 |
|       83 | G      |       14 |
|      129 | G      |       12 |
|      111 | PG     |       15 |
|       44 | PG     |       12 |
|       26 | PG     |       11 |
|       92 | PG     |       12 |
|       17 | G      |       12 |
|      158 | PG     |       10 |
|      147 | PG     |       10 |
|       14 | G      |       10 |
|      102 | PG     |       11 |
|      133 | PG     |       10 |
+----------+--------+----------+
16 rows in set (0.00 sec)

9章 サブクエリ

非相関サブクエリ

都市情報を格納するcityテーブルと、国情報を格納するcountryテーブルが存在します。
以下の条件を満たすデータを取得するクエリをスカラサブクエリを使用して出力してください

  • 対象とする都市(city)は国名(country)が'India'ではないものとする。
  • 上記に該当するcity_idとcityを出力してください
解答 
mysql> SELECT city_id, city
-> FROM city
-> WHERE country_id <>
->   (SELECT country_id FROM country WHERE country = 'India');
+---------+----------------------------+
| city_id | city                       |
+---------+----------------------------+
|       1 | A Corua (La Corua)         |
|       2 | Abha                       |
|       3 | Abu Dhabi                  |
|       4 | Acua                       |
|       5 | Adana                      |
|       6 | Addis Abeba                |
......
|     595 | Zapopan                    |
|     596 | Zaria                      |
|     597 | Zeleznogorsk               |
|     598 | Zhezqazghan                |
|     599 | Zhoushan                   |
|     600 | Ziguinchor                 |
+---------+----------------------------+
540 rows in set (0.00 sec)

複数行/単一行のサブクエリ

都市情報を格納するcityテーブルと、国情報を格納するcountryテーブルが存在します。
以下の条件を満たすデータを取得するクエリをサブクエリを使って記述しなさい

  • 対象とする都市(city)は、国名(country)が 'Canada'または'Mexico'のいずれかに属するものとする
  • 結果として、該当するすべての都市の city テーブルから、city_id (都市ID) と city (都市名) を取得すること。
解答 
mysql> SELECT city_id, city
-> FROM city
-> WHERE country_id IN
->   (SELECT country_id
->    FROM country
->    WHERE country IN ('Canada','Mexico'));
+---------+----------------------------+
| city_id | city                       |
+---------+----------------------------+
|     179 | Gatineau                   |
|     196 | Halifax                    |
|     300 | Lethbridge                 |
|     313 | London                     |
|     383 | Oshawa                     |
|     430 | Richmond Hill              |
|     565 | Vancouver                  |
......
|     452 | San Juan Bautista Tuxtepec |
|     541 | Torren                     |
|     556 | Uruapan                    |
|     563 | Valle de Santiago          |
|     595 | Zapopan                    |
+---------+----------------------------+
37 rows in set (0.00 sec)

all演算子

①
顧客情報を格納するcustomerテーブルと、支払い情報を格納するpaymentテーブルが存在します。
これまでにレンタル料金(amount)がタダになったことがない(0ドルで借りたことがない)顧客のfirst_nameと last_name をサブクエリを使って取得すること

解答 
mysql> SELECT first_name, last_name
-> FROM customer
-> WHERE customer_id <> ALL
->   (SELECT customer_id
->    FROM payment
->    WHERE amount = 0);
+-------------+--------------+
| first_name  | last_name    |
+-------------+--------------+
| MARY        | SMITH        |
| PATRICIA    | JOHNSON      |
| LINDA       | WILLIAMS     |
| BARBARA     | JONES        |
......
| EDUARDO     | HIATT        |
| TERRENCE    | GUNDERSON    |
| ENRIQUE     | FORSYTHE     |
| FREDDIE     | DUGGAN       |
| WADE        | DELVALLE     |
| AUSTIN      | CINTRON      |
+-------------+--------------+
576 rows in set (0.02 sec)
  • ALLではなくNOT INでもOK(つかこっちのが一般的)
    • WHERE customer_id NOT IN ...

②
データベースには、以下のレンタル記録が入ったrentalテーブルと、以下顧客関連のテーブルが存在します。

  • customer（顧客情報）
  • address（住所情報）
  • city（都市情報）
  • country（国情報）

以下の条件を同時に満たす顧客ID(customer_id)と、その顧客の総レンタル回数 (count(*)) を取得するクエリを記述しなさい。

  • countryが'United States'、'Mexico'、'Canada'のいずれかに住む顧客のなかで、一番多くのレンタルを行っている顧客ID(customer_id)とその回数(count)を、ALL演算子を使って取得してください
解答 
mysql> SELECT customer_id, count()
-> FROM rental
-> GROUP BY customer_id
-> HAVING count() > ALL
->   (SELECT count()
->    FROM rental r
->      INNER JOIN customer c
->      ON r.customer_id = c.customer_id
->      INNER JOIN address a
->      ON c.address_id = a.address_id
->      INNER JOIN city ct
->      ON a.city_id = ct.city_id
->      INNER JOIN country co
->      ON ct.country_id = co.country_id
->    WHERE co.country IN ('United States','Mexico','Canada')
->    GROUP BY r.customer_id
->   );
+-------------+----------+
| customer_id | count() |
+-------------+----------+
|         148 |       46 |
+-------------+----------+
1 row in set (0.01 sec)

any演算子

支払い記録のpaymentテーブル、顧客情報のcustomerテーブル、住所情報のaddress、都市情報のcity、国情報のcountryの各テーブルが存在します。
レンタル料金(amount)の支払い総額(sum(amount))がボリビア('Bolivia')の顧客全員、パラグアイ('Paraguay')の顧客全員、またはチリ('Chile')の顧客全員の支払い総額よりも、少なくともいずれか一国の支払い総額を上回る顧客ID(customer_id)を見つけ出してください

解答 
mysql> SELECT customer_id, sum(amount)
-> FROM payment
-> GROUP BY customer_id
-> HAVING sum(amount) > ANY
->   (SELECT sum(p.amount)
->    FROM payment p
->      INNER JOIN customer c
->      ON p.customer_id = c.customer_id
->      INNER JOIN address a
->      ON c.address_id = a.address_id
->      INNER JOIN city ct
->      ON a.city_id = ct.city_id
->      INNER JOIN country co
->      ON ct.country_id = co.country_id
->    WHERE co.country IN ('Bolivia','Paraguay','Chile')
->    GROUP BY co.country
->   );
+-------------+-------------+
| customer_id | sum(amount) |
+-------------+-------------+
|         137 |      194.61 |
|         144 |      195.58 |
|         148 |      216.54 |
|         178 |      194.61 |
|         459 |      186.62 |
|         526 |      221.55 |
+-------------+-------------+
6 rows in set (0.02 sec)

複数列のサブクエリ

以下のテーブルが存在します。
• actor（俳優情報）
• film（映画情報）
• film_actor（俳優と映画の関連付け）

'MONROE'という俳優が出演した映画のレーティングがPGのケースを全て洗い出したい
以下のすべての条件を満たす俳優ID(actor_id)と映画ID(film_id)をfilm_actorテーブルから取得するクエリを、サブクエリを使って取得しなさい

  • 俳優の姓 (last_name) が 'MONROE'であること
  • 映画のレーティング (rating) が'PG'であること
解答 
mysql> SELECT actor_id, film_id
-> FROM film_actor
-> WHERE (actor_id, film_id) IN
->   (SELECT a.actor_id, f.film_id
->    FROM actor a
->      CROSS JOIN film f
->    WHERE a.last_name = 'MONROE' AND f.rating = 'PG'
->   );
+----------+---------+
| actor_id | film_id |
+----------+---------+
|      120 |      63 |
|      120 |     144 |
|      120 |     414 |
|      120 |     590 |
|      120 |     715 |
|      120 |     894 |
|      178 |     164 |
|      178 |     194 |
|      178 |     273 |
|      178 |     311 |
|      178 |     983 |
+----------+---------+
11 rows in set (0.00 sec)

相関サブクエリ

データベースには、顧客情報を格納するcustomerテーブルと、レンタル記録を格納するrentalテーブルが存在します。
総レンタル回数(rentalテーブルの行数)がちょうど20回の顧客のfirst_nameとlast_nameを取得するクエリを記述しなさい。

解答 
mysql> SELECT c.first_name, c.last_name
-> FROM customer c
-> WHERE 20 =
->   (SELECT count(*)
->    FROM rental r
->    WHERE r.customer_id = c.customer_id);
+------------+-------------+
| first_name | last_name   |
+------------+-------------+
| LAUREN     | HUDSON      |
| JEANETTE   | GREENE      |
| TARA       | RYAN        |
| WILMA      | RICHARDS    |
| JO         | FOWLER      |
| KAY        | CALDWELL    |
| DANIEL     | CABRAL      |
| ANTHONY    | SCHWAB      |
| TERRY      | GRISSOM     |
| LUIS       | YANEZ       |
| HERBERT    | KRUGER      |
| OSCAR      | AQUINO      |
| RAUL       | FORTIER     |
| NELSON     | CHRISTENSON |
| ALFREDO    | MCADAMS     |
+------------+-------------+
15 rows in set (0.01 sec)

EXISTS演算子

①
データベースには、顧客情報を格納するcustomerテーブルと、レンタル記録を格納するrentalテーブルが存在します。
2005年5月25日よりも前に、少なくとも1回のレンタル記録が存在する(rental_dateが存在する)顧客のfirst_nameとlast_nameを取得するクエリを記述しなさい。

解答 
mysql> SELECT c.first_name, c.last_name
-> FROM customer c
-> WHERE EXISTS
->   (SELECT 1 FROM rental r
->    WHERE r.customer_id = c.customer_id
->      AND date(r.rental_date) < '2005-05-25');
+------------+-------------+
| first_name | last_name   |
+------------+-------------+
| CHARLOTTE  | HUNTER      |
| DELORES    | HANSEN      |
| MINNIE     | ROMERO      |
| CASSANDRA  | WALTERS     |
| ANDREW     | PURDY       |
| MANUEL     | MURRELL     |
| TOMMY      | COLLAZO     |
| NELSON     | CHRISTENSON |
+------------+-------------+
8 rows in set (0.01 sec)
  • EXISTSを使う場合は、1か*を使うのが慣例

②
データベースには、俳優情報を格納するactorテーブル、映画情報を格納するfilmテーブル、および俳優と映画を紐づけるfilm_actorテーブルが存在します。
映画のレーティング(rating)が'R'の映画に一度も出演したことがない俳優の
first_nameとlast_nameを取得するクエリを記述しなさい。

解答 
mysql> SELECT a.first_name, a.last_name
-> FROM actor a
-> WHERE NOT EXISTS
->   (SELECT 1
->    FROM film_actor fa
->      INNER JOIN film f ON f.film_id = fa.film_id
->    WHERE fa.actor_id = a.actor_id AND f.rating = 'R');
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| JANE       | JACKMAN   |
+------------+-----------+
1 row in set (0.00 sec)

データソースとしてのサブクエリ

顧客情報が格納されたcustomerテーブルと、支払い情報が格納されたpaymentテーブルが存在します。
顧客ID(customer_id)ごとに、その顧客のレンタル回数と支払い額(amount)の総額と顧客の氏名first_name``last_nameを、サブクエリを用いて出力してください

解答 
mysql> SELECT c.first_name, c.last_name, pymnt.num_rentals, pymnt.tot_payments
-> FROM customer c
->   INNER JOIN
->     (SELECT customer_id, count(*) num_rentals, sum(amount) tot_payments
->      FROM payment
->      GROUP BY customer_id
->     ) pymnt
->   ON c.customer_id = pymnt.customer_id;
+-------------+--------------+-------------+--------------+
| first_name  | last_name    | num_rentals | tot_payments |
+-------------+--------------+-------------+--------------+
| MARY        | SMITH        |          32 |       118.68 |
| PATRICIA    | JOHNSON      |          27 |       128.73 |
| LINDA       | WILLIAMS     |          26 |       135.74 |
| BARBARA     | JONES        |          22 |        81.78 |
| ELIZABETH   | BROWN        |          38 |       144.62 |
......
| TERRENCE    | GUNDERSON    |          30 |       117.70 |
| ENRIQUE     | FORSYTHE     |          28 |        96.72 |
| FREDDIE     | DUGGAN       |          25 |        99.75 |
| WADE        | DELVALLE     |          22 |        83.78 |
| AUSTIN      | CINTRON      |          19 |        83.81 |
+-------------+--------------+-------------+--------------+
599 rows in set (0.01 sec)

データを人工的に生成する

顧客の支払い記録を格納するpaymentテーブルが存在します。
顧客の支払い総額(amountの合計)を基にSmall Fry``Average Joes``Heavy Hittersという3つのグループに分類し、各グループに属する顧客の人数をカウントしなさい。
各グループの定義は以下のようになる

支払い総額の範囲
Small Fry: $0.00 〜 $74.99
Average Joes: $75.00 〜 $149.99
Heavy Hitters: $150.00 以上

上記の総額の範囲は、DB上には定義されていないとする

解答 
mysql> SELECT pymnt_grps.name, count() num_customers
-> FROM
->   (SELECT customer_id, count() num_rentals, sum(amount) tot_payments
->    FROM payment
->    GROUP BY customer_id
->   ) pymnt
->   INNER JOIN
->   (SELECT 'Small Fry' name, 0 low_limit, 74.99 high_limit
->    UNION ALL
->    SELECT 'Average Joes' name, 75 low_limit, 149.99 high_limit
->    UNION ALL
->    SELECT 'Heavy Hitters' name, 150 low_limit, 9999999.99 high_limit
->   ) pymnt_grps
->   ON pymnt.tot_payments
->     BETWEEN pymnt_grps.low_limit AND pymnt_grps.high_limit
-> GROUP BY pymnt_grps.name;
+---------------+---------------+
| name          | num_customers |
+---------------+---------------+
| Average Joes  |           515 |
| Heavy Hitters |            46 |
| Small Fry     |            38 |
+---------------+---------------+
3 rows in set (0.01 sec)

共通テーブル式

データベースには、以下の映画関連情報テーブルが存在します。

  • actor: 俳優情報
  • film : 映画情報
  • film_actor : 映画と俳優の関連付け
  • inventory : 在庫情報
  • rental : レンタル記録
  • payment : 支払い記録

with句を使って、レーティング(rating)が'PG'の映画のうち、'S'で始まるlast_nameの俳優が出演している映画のレンタル収益(amount)の総額を計算し、収益の高い順に並べてください

解答 
mysql> WITH actors_s AS
->   (SELECT actor_id, first_name, last_name
->    FROM actor
->    WHERE last_name LIKE 'S%'
->   ),
->   actors_s_pg AS
->   (SELECT s.actor_id, s.first_name, s.last_name, f.film_id, f.title
->    FROM actors_s s
->      INNER JOIN film_actor fa
->      ON s.actor_id = fa.actor_id
->      INNER JOIN film f
->      ON f.film_id = fa.film_id
->    WHERE f.rating = 'PG'
->   ),
->   actors_s_pg_revenue AS
->   (SELECT spg.first_name, spg.last_name, p.amount
->    FROM actors_s_pg spg
->      INNER JOIN inventory i
->      ON i.film_id = spg.film_id
->      INNER JOIN rental r
->      ON i.inventory_id = r.inventory_id
->      INNER JOIN payment p
->      ON r.rental_id = p.rental_id
->   ) -- with句の終わり
-> SELECT spg_rev.first_name, spg_rev.last_name,
->        sum(spg_rev.amount) tot_revenue
-> FROM actors_s_pg_revenue spg_rev
-> GROUP BY spg_rev.first_name, spg_rev.last_name
-> ORDER BY 3 desc;
+------------+-------------+-------------+
| first_name | last_name   | tot_revenue |
+------------+-------------+-------------+
| NICK       | STALLONE    |      692.21 |
| JEFF       | SILVERSTONE |      652.35 |
| DAN        | STREEP      |      509.02 |
| GROUCHO    | SINATRA     |      457.97 |
| SISSY      | SOBIESKI    |      379.03 |
| JAYNE      | SILVERSTONE |      372.18 |
| CAMERON    | STREEP      |      361.00 |
| JOHN       | SUVARI      |      296.36 |
| JOE        | SWANK       |      177.52 |
+------------+-------------+-------------+
9 rows in set (0.02 sec)

式ジェネレーターとしてのサブクエリ

俳優テーブル (actor) から俳優ID(actor_id)と名前(first_name, last_name)を取得し、それぞれの俳優が出演している映画の数が多い順に結果を並べ替えてください
条件として、相関スカラサブクエリを使用して取得してください

解答 
SELECT a.actor_id, a.first_name, a.last_name
FROM actor a
ORDER BY
  (SELECT count(*) FROM film_actor fa
   WHERE fa.actor_id = a.actor_id) DESC;
  • スカラサブクエリはどこでも使うことができる

10章以降は別Qiita

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?