Shoeisha Technology Media

資格Zine(しかくジン)

記事種別から探す

副問合せの基本確認と難問の克服

  • LINEで送る
  • このエントリーをはてなブックマークに追加
林 優子[著]
2016/10/13 14:00

 SQL入門者にとって結合と並んで「難しい」と感じるのは副問合せのようです。副問合せは、自分がやりたいことをSQLに書き起こしてみれば自然と記述できるようになるのですが、この「自分がやりたいことをSQLにする」ということ自体が難しいようですね。「習うより慣れろ」という言葉もありますので、いろんな副問合せのパターンに慣れましょう。

どんなときに副問合せが必要か

さっそくですが、実力試しに次の問題に挑戦してください。

問1

CUSTOMERS表の構造を資料で確認してください。

名前                        NULL?    型
--------------------------- -------- --------------------
CUST_ID                     NOT NULL NUMBER
CUST_FIRST_NAME             NOT NULL VARCHAR2(20)
CUST_LAST_NAME              NOT NULL VARCHAR2(40)
CUST_GENDER                 NOT NULL CHAR(1)
CUST_YEAR_OF_BIRTH          NOT NULL NUMBER(4)
CUST_MAIN_PHONE_NUMBER      NOT NULL VARCHAR2(25)
CUST_EMAIL   

単一の文で実行する場合に、結合または副問合せが必要になるのはどのタスクですか。

  • a. 登録顧客の平均年齢より上の顧客数
  • b. 40歳以上の女性顧客の人数
  • c. EMAILが登録されている男性顧客の人数
  • d. 女性顧客の割合

12cSQLが難しい理由の1つは、この設問のように、暗記では解けない出題があることだと思います。こういう問題は頭で解こうとせずに、実際にSQLを記述してみましょう。試験はコンピュータベースですが、受験時には紙(みたいなもの)とペンを渡してくれるので、その紙に選択肢の内容を得るためのSQLを書いてみればよいのです。

受験というのは、平常心でいるつもりでも、どこか緊張したり焦ったりしているものです。SQLに自信のある人も、頭の中でSQLを考えるのではなく、紙に書いてください。このとき、完璧なSQLを書こうとする必要はありません。思いつくところから書いてみればいいのです。

選択肢a「登録顧客の平均年齢より上の顧客数」

では、選択肢aから始めます。「登録顧客の平均年齢」は、次で求めることができます。

SQL> SELECT AVG(TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')- cust_year_of_birth))
  2  FROM customers;

AVG(TO_NUMBER(TO_CHAR(SYSDATE,
------------------------------
                    58.5960541

それより上の(年齢の)顧客の人数は、

SELECT COUNT(*) FROM customers 
WHERE TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')- cust_year_of_birth) > (登録顧客の平均年齢);

ですから、副問合せを使わなければなりません。したがって、選択肢aは正解です。

選択肢b「40歳以上の女性顧客の人数」

選択肢aは、副問合せから考えたので、選択肢bは主問合せから考えてみます。要求しているのは女性顧客の人数ですから、

SQL> SELECT COUNT(*)
  2  FROM customers
  3  WHERE cust_gender = 'F';

  COUNT(*)
----------
     18325

なおかつ、40歳以上ですから、WHERE句の条件に次を追加すればいいですよね。

AND TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')- cust_year_of_birth) >= 40

ということは、副問合せを使う必要はないので、選択肢bは不正解です。

選択肢c「EMAILが登録されている男性顧客の人数」

これも主問合せから考えましょう。EMAILが登録されているということは、cust_email列の値がNULLではないと考えればいいので、

SQL> SELECT COUNT(*) FROM customers
  2  WHERE cust_email IS NOT NULL
  3  AND cust_gender = 'M';

  COUNT(*)
----------
     37175

副問合せを使う必要はないので、選択肢cは不正解です。

選択肢d「女性顧客の割合」

割合ということは女性の人数を全体の人数で割ればいいので、

SQL> -- 女性の人数
SQL> SELECT COUNT(*) FROM customers
  2  WHERE cust_gender = 'F';

  COUNT(*)
----------
     18325

SQL> -- 全体の人数
SQL> SELECT COUNT(*) FROM customers;

  COUNT(*)
----------
     55500

というふうにそれぞれ求めて……と考えてしまいますが、DECODEという関数がありましたよね。これを使って、女性だったら「1」、それ以外なら「NULL」を返して数えれば、女性顧客の人数は求まります。

SQL> SELECT COUNT(DECODE(cust_gender,'F',1,NULL))
  2  FROM customers;

COUNT(DECODE(CUST_GENDER,'F',1
------------------------------
                         18325

だったら、次のSQLで女性顧客の割合は求められるので、副問合せは不要だということです(選択肢dは不正解)。

SQL> SELECT (COUNT(DECODE(cust_gender,'F',1,NULL)) / COUNT(*) ) * 100
  2  FROM customers;

(COUNT(DECODE(CUST_GENDER,'F',1,NULL))/COUNT(*))*100
----------------------------------------------------
                                           33.018018

検索条件になる値が静的な値(例えば、性別が男性 →cust_gender = 'M')であれば、副問合せは必要ではなく、動的な値(例えば、平均年齢→レコード件数が増えれば平均年齢は違ってくる)なら必要というふうに考えても構いません。しかし、SQLも1つのプログラミング言語であり、プログラミングは頭の中だけで組み立てられるようになるのは、訓練(経験)が必要です。前述したとおり、受験時にはSQLを紙に書き出してみるのが正解への近道だと思います。


  • LINEで送る
  • このエントリーをはてなブックマークに追加

著者プロフィール

  • 林 優子(ハヤシ ユウコ)

    日本オラクル株式会社の教育ビジネスのスタートアップを全面的に支援し、バージョン5の頃からOracleに携わるベテラン講師として知る人も多い。Oracle認定講師を表彰するExcellent Instructorを連続受賞。1ランク上のITスペシャリスト育成を目標に、データベース分野にとどまらず「プレゼンテーション」、「ロジカルシンキング」などのトレーニングも手がけている。著書に『オラクルマスター教科書』シリーズ(翔泳社)、『プロとしてのデータモデリング入門』(SBクリエイティブ)など。その他、雑誌執筆、著書・メディア出演も多数。

バックナンバー

連載:めざせオラクルマスターホルダー! Oracle DB 12c SQL基礎 弱点克服スクール

もっと読む

この記事もオススメ

All contents copyright © 2015-2017 Shoeisha Co., Ltd. All rights reserved. ver.1.0