SQLクイズ - Oracle

Oracle Direct Seminar
<Insert Picture Here>
【体感型セミナー】SQLをクイズ感覚で学ぶ！
SQLクイズ
日本オラクル株式会社
はじめに
• 本セミナーは、翔泳社「SQLクイズ」をもとに、実際にSQLを
考えていただく、受講者参加型のセミナーです。
• 宿題、やってありますか？
• 抜き打ちテスト対策、大丈夫ですか？
Copyright© 2011, Oracle. ALL rights reserved.
2
アジェンダ
•
•
•
SQL実行環境のご紹介
できた？宿題の解説
チャレンジ！SQLクイズ
Copyright© 2011, Oracle. ALL rights reserved.
3
SQL実行環境のご紹介
• SQL実行環境 「Oracle Application Express」
http://apex.oracle.com/pls/apex/
• Oracle Application Expressの利用方法に関しては、以下のURLをご確認ください。
http://blogs.oracle.com/oracle4engineer/column/seminar/025292.html
Copyright© 2011, Oracle. ALL rights reserved.
4
セミナー中に利用する表
社員表（EMPLOYEES表）
部門表（DEPARTMENTS表）
職種表（JOBS表）
Copyright© 2011, Oracle. ALL rights reserved.
5
Copyright© 2011, Oracle. ALL rights reserved.
6
＜できた？宿題の解説 第１問＞
問題1.
人事部では組織見直しのため、職種の統計を調べています。
それぞれの職種ごとの平均給与を給与額が高い順に表示してください。
（列名を「職種」と「平均給与」として表示してください。）
Copyright© 2011, Oracle. ALL rights reserved.
7
＜できた？宿題の解説 第１問＞
問題を解くためのポイント①
• それぞれの職種ごとの給与平均を出す
• GROUP BY句の利用
構文：GROUP BY group_by_expression
ポイント
•group_by_expression：グループ化の基準となる列を指定
•列別名の使用は不可
•SELCTで指定された列はグループ関数列を除いて、
全ての列をGROUP BY句に含める必要があります。
• AVG関数の利用
構文：AVG（列名） 平均値
関数
意味
COUNT
行の数
MAX
最大値の算出
MIN
最小値の算出
SUM
合計値の算出
表：その他のグループ関数
Copyright© 2011, Oracle. ALL rights reserved.
8
＜できた？宿題の解説 第１問＞
問題を解くためのポイント②
• 給与の高い順に行をソートする
• ORDER BY句の利用
構文：ORDER BY ｛列名、列別名、列位置｝
[ASC | DESC]
ポイント
ASC： 行を昇順(A→Z)にソート
DESC： 行を降順（Z→A）にソート
※ASCもしくはDESCを省略した場合は、昇順（ASC）で
ソートされます。
NULLS FIRSTまたはNULLS LASTを使用することで、
NULL値が含まれる戻された行が順序の最初にくるか、
最後にくるかを指定することができます。
※ORDER BY句は最後の句にする必要があります。
Copyright© 2011, Oracle. ALL rights reserved.
9
＜できた？宿題の解説 第１問＞
解答例
SELECT job_id “職種”, AVG(salary) “平均給与”
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC;
Point
・表内の行をグループ分けする
GROUP BY group_by_expression
GROUP BY句を使用して、行をグループ化することで、
それぞれのグループのサマリー情報を戻すことが可能
Copyright© 2011, Oracle. ALL rights reserved.
10
＜できた？宿題の解説 第２問＞
問題2.
人事部では社員のデフォルトのパスワードを管理しています。
パスワードは従業員の名前（first_name）の最初の3文字（大文字を利用）と（入社日）
hire_dateの日付（MMDD）を組み合せたものです。
パスワードを自動生成するには、どのようなSELECT文を使えばよいでしょうか。
例： 名前（first_name）：Wendy
入社日（hire_date）：11-04-01(YY-MM-DD) の場合
パスワード：WEN0401
Copyright© 2011, Oracle. ALL rights reserved.
11
＜できた？宿題の解説 第２問＞
問題を解くためのポイント①
• first_name列から最初の1文字を取り出す
• SUBSTR関数の使用
構文：SUBSTR（対象の文字列,開始位置,取得文字数）
実行例：
SELECT last_name,
SUBSTR(last_name,1,1)
FROM employees;
• 入社日をMMDD形式の文字列にする
• TO_CHAR関数の利用
構文：TO_CHAR(date, ‘format_model’)
実行例：
SELECT TO_CHAR(hire_date,'MM/DD')
FROM employees;
Copyright© 2011, Oracle. ALL rights reserved.
12
＜できた？宿題の解説 第２問＞
問題を解くためのポイント②
• first_nameの最初の3文字と、hire_dateの日付を連結する
• 連結演算子の使用
構文：要素1 || 要素2 || 要素3
実行例：
SELECT last_name "名字", first_name "名前",
last_name||first_name "氏名"
FROM employees;
• 結果を大文字で表示する
• UPPER関数の使用
構文：UPPER（列名）
Copyright© 2011, Oracle. ALL rights reserved.
13
＜できた？宿題の解説 第２問＞
解答例
SELECT first_name "名前", last_name "名字",hire_date "入社日",
UPPER(SUBSTR(first_name,1,3))
||TO_CHAR(hire_date, 'mmdd') "パスワード"
FROM employees;
• Point
・指定した位置の文字列を取り出す
SUBSTR（first_name,開始位置,取得文字数）
「開始位置」から「取得文字数」分の文字を取り出す
・複数の要素を連結する
要素1 || 要素2 || 要素3
複数の要素（列やリテラル文字など）を連結して表示する
・日付型のデータを文字列にする
TO_CHAR(date, ‘format_model’)
‘format_model’で指定された書式で、日付型を文字列の値に変換します
Copyright© 2011, Oracle. ALL rights reserved.
14
＜できた？宿題の解説 第３問＞
問題3.
人事部では給与の見直しを行うため、給与の調査をしています。
職種(job_id)がITプログラマ（IT_PROG）ではなく、給与（salary）が
どのITプログラマ（IT_PROG）よりも多い人を表示してください。
Copyright© 2011, Oracle. ALL rights reserved.
15
＜できた？宿題の解説 第３問＞
問題を解くためのポイント①
• ITプログラマでない職種を探す
• 比較演算子を利用
構文：・・・・・ WHERE 値 演算子 値
実行例：
SELECT last_name "名前", job_id "職種“
FROM employees WHERE job_id <> 'IT_PROG':
演算子
意味
演算子
意味
=
等しい
>
大きい
BETWEEN・・・
AND・・・
2つの値の間（その値自
体を含む）
>=
以上
<
小さい
IN（set）
値のリストのいずれかに
一致
<=
以下
LIKE
文字のパターンに一致
<>,!=,^=
等しくない
IS NULL
NULL値である
２つの
Copyright© 2011, Oracle. ALL rights reserved.
16
＜できた？宿題の解説 第３問＞
問題を解くためのポイント②
• どのITプログラマより給与が高い社員を求める
• 複数行副問合せの使用
• 複数行比較演算子を使用します
SELECT last_name, salary, job_id FROM employees
WHERE salary > ALL
( SELECT salary FROM employees
副問合せ
WHERE job_id = 'IT_PROG‘ ) ;
主問合せ
構文：
SELECT 列名
副問合せ
FROM
表名
WHERE 列名 比較条件 （SELECT 列名
FROM
表名
・・・・）
演算子
意味
IN
リスト内のいずれかのメンバーと等しい
ANY
直前に=、!=、>、<、<=、>=が必要。１つの値をリスト内の値、
または問い合わせで戻されるそれぞれの値と比較
ALL
直前に=、!=、>、<、<=、>=が必要。１つの値をリスト内の値、
または問い合わせで戻される全ての値と比較
Copyright© 2011, Oracle. ALL rights reserved.
17
＜できた？宿題の解説 第３問＞
解答例
SELECT employee_id "社員番号", last_name "名字", job_id "職種", salary "給与“
FROM employees
WHERE salary > ALL
(SELECT salary FROM employees WHERE job_id = ‘IT_PROG’)
AND job_id <> 'IT_PROG'
ORDER BY 1;
Point
・比較演算子
１つの式を別の値または式と比較する条件で使用
・副問合せ
ある結果を元にして、他の処理を行う構文
副問合せは括弧（ ）で囲んで、主問合せの中に
記述する
Copyright© 2011, Oracle. ALL rights reserved.
18
＜できた？宿題の解説 第４問＞
問題4.
人事部では社員の勤続年数を調べています。
各社員が入社してからの勤続年数によって「15年以上」「10年以上」
「5年以上」「5年未満」と表示してください。
Copyright© 2011, Oracle. ALL rights reserved.
19
＜できた？宿題の解説 第４問＞
問題を解くためのポイント①
SELECT last_name "名前",salary "給与",
(CASE
WHEN salary > 10000
THEN '対象者'
ELSE '対象外'
END) "ステータス"
FROM employees;
• 条件によって処理を変える
• CASE式の使用
構文：
CASE
WHEN 条件1 THEN 結果1
WHEN 条件2 THEN 結果2
ELSE 例外処理
END
• 「何年」を表す
SELECT SYSDATE ,
SYSDATE-TO_YMINTERVAL('5-0') "5 years_ago",
SYSDATE-TO_YMINTERVAL('10-0') "10 years_ago"
FROM employees;
• TO_YMINTERVAL関数を使用
構文：TO_YMINTERVAL（'01-02'） 「1年2ヶ月」
Copyright© 2011, Oracle. ALL rights reserved.
20
＜できた？宿題の解説 第４問＞
解答例
SELECT last_name "名前",
(CASE WHEN (SYSDATE-TO_YMINTERVAL('15-0'))>=hire_date
THEN '15年以上'
WHEN (SYSDATE-TO_YMINTERVAL('10-0'))>=hire_date
THEN '10年以上'
WHEN (SYSDATE-TO_YMINTERVAL('5-0'))>=hire_date
THEN '5年以上'
ELSE '5年未満'
END) "勤続年数"
FROM employees;
Point
・CASE式
条件によって処理を変える
「WHEN」句で条件を記述し、「THEN」句に表示する結果を記述
Copyright© 2011, Oracle. ALL rights reserved.
21
＜できた？宿題の解説 第４問＞
みなさんの解答例①
SELECT last_name 名前 ,
case
WHEN trunc(months_between(SYSDATE,hire_date)/12) < 5 THEN '5年未満'
WHEN trunc(months_between(SYSDATE,hire_date)/12) <10 THEN '5年以上'
WHEN trunc(months_between(SYSDATE,hire_date)/12) <15 THEN '10年以上'
ELSE ‘15年以上’
end 勤続年数
FROM employees ;
SELECT LAST_NAME AS 名前,
CASE
WHEN MONTHS_BETWEEN(SYSDATE,hire_date) >= 15*12 THEN '15年以上'
WHEN MONTHS_BETWEEN(SYSDATE,hire_date) >= 10*12 THEN '10年以上'
WHEN MONTHS_BETWEEN(SYSDATE,hire_date) >= 5*12 THEN '5年以上'
ELSE '5年未満'
END AS 勤続年数
FROM employees ;
「15年」の計算の仕方にも
いろいろありますね。
Copyright© 2011, Oracle. ALL rights reserved.
22
＜できた？宿題の解説 第４問＞
みなさんの解答例②
SELECT last_name,
CASE (CEIL(MONTHS_BETWEEN(SYSDATE, hire_date) / 12 / 5))
WHEN 1 THEN ‘5年未満’
WHEN 2 THEN ‘5年以上’
WHEN 3 THEN ‘10年以上’
ELSE ‘15年以上’
END AS 勤続年数
FROM employees;
SELECT LAST_NAME AS 名前,
CASE
WHEN HIRE < 5 THEN '5年未満'
WHEN HIRE >= 15 THEN '15年以上'
WHEN HIRE >= 10 THEN '10年以上'
ELSE ‘5年以上’
END AS 勤続年数
FROM (SELECT LAST_NAME,
months_between(SYSDATE, hire_date) / 12 AS HIRE
FROM employees);
WHEN句が長くならないようにすると
シンプルで分かりやすいですね！
Copyright© 2011, Oracle. ALL rights reserved.
23
＜できた？宿題の解説 第５問＞
問題
部門ごとの社員の人数を調べるため、以下のようなレポートを出力する
必要があります。
全部門の部門名と、その部門の社員数、各部門の給与合計および社員名を
表示してください。社員がいない部門は表示する必要はありませんが、
部門に所属していない社員のデータは「未配属」として表示してください。
EMPLOYEES表
DEPARTMENTS表
Copyright© 2011, Oracle. ALL rights reserved.
24
＜できた？宿題の解説 第５問＞
問題を解くためのポイント①
• 部門名と、その部門の社員数、各部門の
給与合計を求める
• 社員表と部門表を結合する
実行例：
SELECT department_name “部門名”,
COUNT(*) "社員数",
SUM(salary) "合計給与"
FROM employees e JOIN departments d
ON e.department_id = d.department_id
GROUP BY department_name;
EMPLOYEES表
DEPARTMENTS表
Copyright© 2011, Oracle. ALL rights reserved.
25
＜できた？宿題の解説 第５問＞
問題を解くためのポイント②
• 部門に配属されていない社員も「未配属」として表示する
実行例：
SELECT NVL(department_name,'未配属') "部門名",
COUNT(*) 社員数, SUM(salary) "合計給与"
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id = d.department_id
GROUP BY department_name;
※外部結合
対応する値がない行データも含めて
表示する結合方法
•NVL関数の利用の利用
構文：NVL (expr1,expr2)
expr1がNULLならexpr2の値を戻す
Copyright© 2011, Oracle. ALL rights reserved.
26
＜できた？宿題の解説 第５問＞
問題を解くためのポイント③
• 部門に所属する社員名を表示する
SELECT NVL(department_name,'未配属') “部門名”,
last_name "社員名"
FROM employees e LEFT OUTER JOIN departments d
ON
e.department_id = d.department_id;
※外部結合
対応する値がない行データも含めて
表示する結合方法
EMPLOYEES表
DEPARTMENTS表
Copyright© 2011, Oracle. ALL rights reserved.
27
＜できた？宿題の解説 第５問＞
問題を解くためのポイント④
• ②と③の処理をまとめて一つにして表示する
• 複数のSELECT文の結果をまとめて表示する
連結演算子（UNION）を使用
構文：
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
SELECT 列名 FROM
UNION
SELECT 列名 FROM
表名
表名
• 列をそろえる必要があるため、足りない列の位置に
NULLやリテラル文字（「配属社員」）を指定
実行例：
SELECT NVL(department_name, '未配属') "部門名" ,
COUNT(*) "社員数", SUM(salary) "合計給与",
'配属社員' "社員名" ・・・
UNION
SELECT department_name, NULL "社員名" , NULL "合計給与",
last_name ・・・
Copyright© 2011, Oracle. ALL rights reserved.
28
＜できた？宿題の解説 第５問＞
解答例
SELECT NVL(department_name,'未配属') "部門名" , COUNT(*) "社員数",
SUM(salary) "合計給与", '配属社員' "社員名"
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id = d.department_id
GROUP BY department_name
UNION
SELECT NVL(department_name,'未配属') ,null,null,last_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id = d.department_id;
Point
・外部結合
対応する値がない行データも含めて表示する結合方法
・UNION集合演算子
複数のSELECT文の結果をまとめて表示する
Copyright© 2011, Oracle. ALL rights reserved.
29
＜できた？宿題の解説 第５問＞
みなさんの解答例①
SELECT nvl(d.DEPARTMENT_NAME, '未配属') as 部門名,社員数, 合計給与, 社員名
FROM (SELECT DEPARTMENT_ID, count(*) as 社員数, sum(salary) as 合計給与,
'配属社員' as 社員名, 0 as No
FROM employees
GROUP BY DEPARTMENT_ID
union
SELECT DEPARTMENT_ID, null, null, LAST_NAME, 1 FROM employees) e
left outer join departments d
on e.DEPARTMENT_ID = d.DEPARTMENT_ID
ORDER BY 部門名, e.No, e.社員名;
SELECT nvl(b.DEPARTMENT_NAME,'未配属') as "部門名",
decode(grouping(a.EMPLOYEE_ID),1,count(*)) as "社員数",
decode(grouping(a.EMPLOYEE_ID),1,sum(a.salary)) as "合計給与",
decode(grouping(a.EMPLOYEE_ID),1,'配属社員',a.LAST_NAME) as "社員名"
FROM employees a Left Join departments b
on a.DEPARTMENT_ID = b.DEPARTMENT_ID
GROUP BY a.DEPARTMENT_ID,b.DEPARTMENT_NAME,
rollup((a.EMPLOYEE_ID,a.LAST_NAME))
ORDER BY b.DEPARTMENT_NAME,a.LAST_NAME nulls first;
Copyright© 2011, Oracle. ALL rights reserved.
30
＜できた？宿題の解説 第５問＞
みなさんの解答例①
SELECT DEPARTMENT_NAME 部門名, EMP_NUM 社員数, salary 合計給与,
CASE EMP_NAME WHEN '1' THEN '配属社員'
ELSE EMP_NAME
END 社員名
FROM (SELECT NVL(DEP.DEPARTMENT_NAME, '未配属') DEPARTMENT_NAME,
COUNT(*) EMP_NUM , SUM(EMP.salary) salary ,
'1' EMP_NAME
FROM employees EMP LEFT JOIN departments DEP
ON EMP.DEPARTMENT_ID = DEP.DEPARTMENT_ID
GROUP BY DEP.DEPARTMENT_NAME
UNION ALL
SELECT NVL(DEP.DEPARTMENT_NAME, '未配属') DEPARTMENT_NAME,
NULL EMP_NUM , NULL salary , EMP.LAST_NAME EMP_NAME
FROM employees EMP LEFT JOIN departments DEP
ON EMP.DEPARTMENT_ID = DEP.DEPARTMENT_ID)
ORDER BY DEPARTMENT_NAME, EMP_NAME
CASE式や副問合せを組み合わせた
力作がたくさんありました！
Copyright© 2011, Oracle. ALL rights reserved.
31
まとめ
• SQL実行環境のご紹介
• できた？宿題の解説
• チャレンジ！SQLクイズ
できましたか？
様々な関数、構文を取り入れ、
シンプル＆効率的なSQL文を
書いてくださいね！
Copyright© 2011, Oracle. ALL rights reserved.
32
OTN×ダイセミ でスキルアップ!!
・一般的な技術問題解決方法などを知りたい！
・セミナ資料など技術コンテンツがほしい！
Oracle Technology Network(OTN)をご活用下さい。
http://otn.oracle.co.jp/forum/index.jspa?categoryID=2
一般的技術問題解決にはOTN掲示版の
「データベース一般」をご活用ください
※OTN掲示版は、基本的にOracleユーザー有志からの回答となるため100%回答があるとは限りません。
ただ、過去の履歴を見ると、質問の大多数に関してなんらかの回答が書き込まれております。
http://www.oracle.com/technology/global/jp/ondemand/otn-seminar/index.html
過去のセミナ資料、動画コンテンツはOTNの
「OTNセミナー オンデマンド コンテンツ」へ
※ダイセミ事務局にダイセミ資料を請求頂いても、お受けできない可能性がございますので予めご了承ください。
ダイセミ資料はOTNコンテンツ オン デマンドか、セミナ実施時間内にダウンロード頂くようお願い致します。
Copyright© 2011, Oracle. ALL rights reserved.
33
OTNセミナー オンデマンド コンテンツ
ダイセミで実施された技術コンテンツを動画で配信中!!
ダイセミのライブ感はそのままに、お好きな時間で受講頂けます。
最新情報つぶやき中
oracletechnetjp
・人気コンテンツは？
・お勧め情報
・公開予告 など
OTN トップページ http://www.oracle.com/technetwork/jp/index.html
ページ左「基本リンク」＞「OTN セミナー オンデマンド」
※掲載のコンテンツ内容は予告なく変更になる可能性があります。
期間限定での配信コンテンツも含まれております。お早めにダウンロード頂くことをお勧めいたします。
Copyright© 2011, Oracle. ALL rights reserved.
34
Oracle エンジニアのための技術情報サイト
オラクルエンジニア通信
http://blogs.oracle.com/oracle4engineer/
最新情報つぶやき中
oracletechnetjp
• 技術資料
• ダイセミの過去資料や製品ホワイト
ペーパー、スキルアップ資料などを
多様な方法で検索できます
• キーワード検索、レベル別、カテゴ
リ別、製品・機能別
• コラム
• オラクル製品に関する技術コラムを
毎週お届けします
• 決してニッチではなく、誰もが明日
から使える技術の「あ、そうだった
んだ！」をお届けします
オラクルエンジニア通信
こんな資料が人気です
 6か月ぶりに資料ダウンロードランキングの首位が交代！
新王者はOracle Database構築資料でした。
 データベースの性能管理手法について、Statspack派も
Enterprise Manager派も目からウロコの技術特集公開中
Copyright© 2011, Oracle. ALL rights reserved.
35
Oracle Databaseの価格ご存知ですか？
①
②
問題：
Oracle Databaseの最小構成はいくらでしょうか？
ヒント：
Oracle Standard Edition Oneを
5Named User Plus(指名ユーザ) というのが最小構成です。
問題：
Real Applications Clusters(RAC) Optionはいくらでしょうか？
ヒント：
RACはOracle Database Enterprise EditionのOptionです。
答えはこちら↓ ログイン不要の簡単見積もり
ライセンス見積もりヘルプ
検索
Copyright© 2011, Oracle. ALL rights reserved.
36
ITプロジェクト全般に渡る無償支援サービス
Copyright© 2011, Oracle. ALL rights reserved.
37
Copyright© 2011, Oracle. ALL rights reserved.
38
Copyright© 2011, Oracle. ALL rights reserved.
39
以上の事項は、弊社の一般的な製品の方向性に関する概要を説明するものです。
また、情報提供を唯一の目的とするものであり、いかなる契約にも組み込むことは
できません。以下の事項は、マテリアルやコード、機能を提供することをコミットメン
ト（確約）するものではないため、購買決定を行う際の判断材料になさらないで下さ
い。オラクル製品に関して記載されている機能の開発、リリースおよび時期につい
ては、弊社の裁量により決定されます。
Oracle、PeopleSoft、JD Edwards、及びSiebelは、米国オラクル・コーポレーション及びその子会社、関連会社の登
録商標です。その他の名称はそれぞれの会社の商標の可能性があります。
Copyright© 2011, Oracle. ALL rights reserved.
40