【体感型セミナー】をクイズ感覚で学ぶ！ SQL - Oracle · Oracle Direct Seminar...

<Insert Picture Here>

Oracle Direct Seminar

【体感型セミナー】SQLをクイズ感覚で学ぶ！

SQLクイズ日本オラクル株式会社

Copyright© 2011, Oracle. ALL rights reserved. 2

はじめに

• 本セミナーは、翔泳社「SQLクイズ」をもとに、実際にSQLを考えていただく、受講者参加型のセミナーです。

• 宿題、やってありますか？

• 抜き打ちテスト対策、大丈夫ですか？


アジェンダ

• SQL実行環境のご紹介

• できた？宿題の解説

• チャレンジ！SQLクイズ


SQL実行環境のご紹介

• SQL実行環境「Oracle Application Express」

http://apex.oracle.com/pls/apex/

• Oracle Application Expressの利用方法に関しては、以下のURLをご確認ください。http://blogs.oracle.com/oracle4engineer/column/seminar/025292.html

http://apex.oracle.com/pls/apex/

http://blogs.oracle.com/oracle4engineer/column/seminar/025292.html


セミナー中に利用する表社員表（EMPLOYEES表）

部門表（DEPARTMENTS表）職種表（JOBS表）


＜できた？宿題の解説第１問＞

問題1.

人事部では組織見直しのため、職種の統計を調べています。それぞれの職種ごとの平均給与を給与額が高い順に表示してください。（列名を「職種」と「平均給与」として表示してください。）



問題を解くためのポイント①• それぞれの職種ごとの給与平均を出す

• GROUP BY句の利用

構文：GROUP BY group_by_expression

構文：AVG（列名）平均値

ポイント•group_by_expression：グループ化の基準となる列を指定•列別名の使用は不可•SELCTで指定された列はグループ関数列を除いて、全ての列をGROUP BY句に含める必要があります。

• AVG関数の利用関数意味

COUNT 行の数

MAX 最大値の算出

MIN 最小値の算出

SUM 合計値の算出

表：その他のグループ関数



問題を解くためのポイント②

• 給与の高い順に行をソートする

• ORDER BY句の利用

構文：ORDER BY ｛列名、列別名、列位置｝[ASC | DESC]

ポイントASC：行を昇順(A→Z)にソート

DESC：行を降順（Z→A）にソート※ASCもしくはDESCを省略した場合は、昇順（ASC）で

ソートされます。

NULLS FIRSTまたはNULLS LASTを使用することで、NULL値が含まれる戻された行が順序の最初にくるか、最後にくるかを指定することができます。

※ORDER BY句は最後の句にする必要があります。



解答例

SELECT job_id “職種”, AVG(salary) “平均給与”FROM employeesGROUP BY job_idORDER BY AVG(salary) DESC;

・表内の行をグループ分けするGROUP BY group_by_expression

GROUP BY句を使用して、行をグループ化することで、それぞれのグループのサマリー情報を戻すことが可能

Point


＜できた？宿題の解説第２問＞

問題2.人事部では社員のデフォルトのパスワードを管理しています。パスワードは従業員の名前（first_name）の最初の3文字（大文字を利用）と（入社日）hire_dateの日付（MMDD）を組み合せたものです。パスワードを自動生成するには、どのようなSELECT文を使えばよいでしょうか。例：名前（first_name）：Wendy入社日（hire_date）：11-04-01(YY-MM-DD) の場合パスワード：WEN0401



問題を解くためのポイント①

• first_name列から最初の1文字を取り出す

• SUBSTR関数の使用

• 入社日をMMDD形式の文字列にする

• TO_CHAR関数の利用

構文：SUBSTR（対象の文字列,開始位置,取得文字数）

構文：TO_CHAR(date, ‘format_model’)

実行例：

SELECT last_name,SUBSTR(last_name,1,1)FROM employees;

実行例：

SELECT TO_CHAR(hire_date,'MM/DD') FROM employees;




• first_nameの最初の3文字と、hire_dateの日付を連結する

• 連結演算子の使用

• 結果を大文字で表示する

• UPPER関数の使用

構文：要素1 || 要素2 || 要素3

実行例：

SELECT last_name "名字", first_name "名前",last_name||first_name "氏名" FROM employees;

構文：UPPER（列名）



解答例

SELECT first_name "名前", last_name "名字",hire_date "入社日",UPPER(SUBSTR(first_name,1,3))||TO_CHAR(hire_date, 'mmdd') "パスワード" FROM employees;

•

・指定した位置の文字列を取り出すSUBSTR（first_name,開始位置,取得文字数）「開始位置」から「取得文字数」分の文字を取り出す

・複数の要素を連結する要素1 || 要素2 || 要素3複数の要素（列やリテラル文字など）を連結して表示する

・日付型のデータを文字列にするTO_CHAR(date, ‘format_model’)

‘format_model’で指定された書式で、日付型を文字列の値に変換します

Point


＜できた？宿題の解説第３問＞

問題3.

人事部では給与の見直しを行うため、給与の調査をしています。

職種(job_id)がITプログラマ（IT_PROG）ではなく、給与（salary）が

どのITプログラマ（IT_PROG）よりも多い人を表示してください。



問題を解くためのポイント①• ITプログラマでない職種を探す

• 比較演算子を利用

構文：・・・・・ WHERE 値演算子値

実行例：

SELECT last_name "名前", job_id "職種“FROM employees WHERE job_id <> 'IT_PROG':

演算子意味

= 等しい

> 大きい

>= 以上

< 小さい

<= 以下

<>,!=,^= 等しくない

２つの

演算子意味

BETWEEN・・・AND・・・

2つの値の間（その値自体を含む）

IN（set）値のリストのいずれかに一致

LIKE 文字のパターンに一致

IS NULL NULL値である



問題を解くためのポイント②• どのITプログラマより給与が高い社員を求める

• 複数行副問合せの使用

• 複数行比較演算子を使用します

構文：SELECT 列名FROM 表名WHERE 列名比較条件（SELECT 列名

FROM 表名・・・・）

副問合せ

主問合せ

演算子意味

IN リスト内のいずれかのメンバーと等しい

ANY 直前に=、!=、>、<、<=、>=が必要。１つの値をリスト内の値、または問い合わせで戻されるそれぞれの値と比較

ALL 直前に=、!=、>、<、<=、>=が必要。１つの値をリスト内の値、または問い合わせで戻される全ての値と比較

SELECT last_name, salary, job_id FROM employees WHERE salary > ALL( SELECT salary FROM employees WHERE job_id = 'IT_PROG‘ ) ;

副問合せ



解答例

SELECT employee_id "社員番号", last_name "名字", job_id "職種", salary "給与“FROM employeesWHERE salary > ALL(SELECT salary FROM employees WHERE job_id = ‘IT_PROG’) AND job_id <> 'IT_PROG' ORDER BY 1;

・比較演算子１つの式を別の値または式と比較する条件で使用

・副問合せある結果を元にして、他の処理を行う構文副問合せは括弧（）で囲んで、主問合せの中に

記述する

Point


＜できた？宿題の解説第４問＞

問題4.

人事部では社員の勤続年数を調べています。各社員が入社してからの勤続年数によって「15年以上」「10年以上」「5年以上」「5年未満」と表示してください。




• 条件によって処理を変える

• CASE式の使用

構文：CASE

WHEN 条件1 THEN 結果1

WHEN 条件2 THEN 結果2

ELSE 例外処理

END

構文：TO_YMINTERVAL（'01-02'）「1年2ヶ月」

SELECT SYSDATE , SYSDATE-TO_YMINTERVAL('5-0') "5 years_ago",SYSDATE-TO_YMINTERVAL('10-0') "10 years_ago" FROM employees;

SELECT last_name "名前",salary "給与",(CASE

WHEN salary > 10000THEN '対象者'

ELSE '対象外'END) "ステータス" FROM employees;

• 「何年」を表す

• TO_YMINTERVAL関数を使用



解答例

SELECT last_name "名前", (CASE WHEN (SYSDATE-TO_YMINTERVAL('15-0'))>=hire_date

THEN '15年以上'WHEN (SYSDATE-TO_YMINTERVAL('10-0'))>=hire_dateTHEN '10年以上'WHEN (SYSDATE-TO_YMINTERVAL('5-0'))>=hire_date

THEN '5年以上'ELSE '5年未満'

END) "勤続年数"FROM employees;

・CASE式条件によって処理を変える「WHEN」句で条件を記述し、「THEN」句に表示する結果を記述

Point



みなさんの解答例①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年」の計算の仕方にもいろいろありますね。



みなさんの解答例②

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句が長くならないようにするとシンプルで分かりやすいですね！


＜できた？宿題の解説第５問＞

問題

部門ごとの社員の人数を調べるため、以下のようなレポートを出力する必要があります。全部門の部門名と、その部門の社員数、各部門の給与合計および社員名を表示してください。社員がいない部門は表示する必要はありませんが、部門に所属していない社員のデータは「未配属」として表示してください。

EMPLOYEES表

DEPARTMENTS表




• 部門名と、その部門の社員数、各部門の給与合計を求める

• 社員表と部門表を結合する

実行例：SELECT department_name “部門名”,COUNT(*) "社員数", SUM(salary) "合計給与"FROM employees e JOIN departments dON e.department_id = d.department_idGROUP BY department_name;

EMPLOYEES表 DEPARTMENTS表




• 部門に配属されていない社員も「未配属」として表示する

実行例：SELECT NVL(department_name,'未配属') "部門名",COUNT(*) 社員数, SUM(salary) "合計給与"FROM employees e LEFT OUTER JOIN departments dON e.department_id = d.department_idGROUP BY department_name;

構文：NVL (expr1,expr2) expr1がNULLならexpr2の値を戻す

•NVL関数の利用の利用

※外部結合対応する値がない行データも含めて表示する結合方法



問題を解くためのポイント③

• 部門に所属する社員名を表示する

EMPLOYEES表 DEPARTMENTS表

SELECT NVL(department_name,'未配属') “部門名”, last_name "社員名"FROM employees e LEFT OUTER JOIN departments dON e.department_id = d.department_id;

※外部結合対応する値がない行データも含めて表示する結合方法



問題を解くためのポイント④

• ②と③の処理をまとめて一つにして表示する

• 複数のSELECT文の結果をまとめて表示する連結演算子（UNION）を使用

• 列をそろえる必要があるため、足りない列の位置にNULLやリテラル文字（「配属社員」）を指定

構文： SELECT 列名 FROM 表名

UNION

SELECT 列名 FROM 表名

NULL NULL

NULL NULL

NULL NULL

NULL NULL

NULL NULL

NULL NULL実行例：SELECT NVL(department_name, '未配属') "部門名" ,

COUNT(*) "社員数", SUM(salary) "合計給与", '配属社員' "社員名" ・・・

UNIONSELECT department_name, NULL "社員名" , NULL "合計給与",

last_name ・・・



解答例

SELECT NVL(department_name,'未配属') "部門名" , COUNT(*) "社員数",SUM(salary) "合計給与", '配属社員' "社員名" FROM employees e LEFT OUTER JOIN departments dON e.department_id = d.department_idGROUP BY department_nameUNIONSELECT NVL(department_name,'未配属') ,null,null,last_nameFROM employees e LEFT OUTER JOIN departments dON e.department_id = d.department_id;

・外部結合対応する値がない行データも含めて表示する結合方法

・UNION集合演算子複数のSELECT文の結果をまとめて表示する

Point



みなさんの解答例①

SELECT nvl(d.DEPARTMENT_NAME, '未配属') as 部門名,社員数, 合計給与, 社員名FROM (SELECT DEPARTMENT_ID, count(*) as 社員数, sum(salary) as 合計給与,

'配属社員' as 社員名, 0 as NoFROM 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_IDORDER 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_IDGROUP BY a.DEPARTMENT_ID,b.DEPARTMENT_NAME,

rollup((a.EMPLOYEE_ID,a.LAST_NAME))ORDER BY b.DEPARTMENT_NAME,a.LAST_NAME nulls first;



みなさんの解答例①

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_IDGROUP BY DEP.DEPARTMENT_NAME UNION ALL SELECT NVL(DEP.DEPARTMENT_NAME, '未配属') DEPARTMENT_NAME,

NULL EMP_NUM , NULL salary , EMP.LAST_NAME EMP_NAMEFROM employees EMP LEFT JOIN departments DEP ON EMP.DEPARTMENT_ID = DEP.DEPARTMENT_ID)

ORDER BY DEPARTMENT_NAME, EMP_NAME

CASE式や副問合せを組み合わせた力作がたくさんありました！


まとめ

• SQL実行環境のご紹介

• できた？宿題の解説

• チャレンジ！SQLクイズ

できましたか？様々な関数、構文を取り入れ、シンプル＆効率的なSQL文を

書いてくださいね！


OTN×ダイセミでスキルアップ!!

※OTN掲示版は、基本的にOracleユーザー有志からの回答となるため100%回答があるとは限りません。ただ、過去の履歴を見ると、質問の大多数に関してなんらかの回答が書き込まれております。

Oracle Technology Network(OTN)をご活用下さい。

・一般的な技術問題解決方法などを知りたい！・セミナ資料など技術コンテンツがほしい！

一般的技術問題解決にはOTN掲示版の

「データベース一般」をご活用ください

http://otn.oracle.co.jp/forum/index.jspa?categoryID=2

過去のセミナ資料、動画コンテンツはOTNの

「OTNセミナーオンデマンドコンテンツ」へ

http://www.oracle.com/technology/global/jp/ondemand/otn-seminar/index.html

※ダイセミ事務局にダイセミ資料を請求頂いても、お受けできない可能性がございますので予めご了承ください。ダイセミ資料はOTNコンテンツオンデマンドか、セミナ実施時間内にダウンロード頂くようお願い致します。

http://otn.oracle.co.jp/forum/index.jspa?categoryID=2


OTNセミナーオンデマンドコンテンツダイセミで実施された技術コンテンツを動画で配信中!!

ダイセミのライブ感はそのままに、お好きな時間で受講頂けます。

最新情報つぶやき中oracletechnetjp

・人気コンテンツは？・お勧め情報・公開予告など

※掲載のコンテンツ内容は予告なく変更になる可能性があります。期間限定での配信コンテンツも含まれております。お早めにダウンロード頂くことをお勧めいたします。

OTN トップページ http://www.oracle.com/technetwork/jp/index.htmlページ左「基本リンク」＞「OTN セミナーオンデマンド」

http://www.oracle.com/technetwork/jp/index.html


Oracle エンジニアのための技術情報サイト

オラクルエンジニア通信http://blogs.oracle.com/oracle4engineer/

• 技術資料

• ダイセミの過去資料や製品ホワイトペーパー、スキルアップ資料などを

多様な方法で検索できます• キーワード検索、レベル別、カテゴ

リ別、製品・機能別• コラム

• オラクル製品に関する技術コラムを毎週お届けします

• 決してニッチではなく、誰もが明日から使える技術の「あ、そうだった

んだ！」をお届けしますこんな資料が人気です

6か月ぶりに資料ダウンロードランキングの首位が交代！新王者はOracle Database構築資料でした。

データベースの性能管理手法について、Statspack派もEnterprise Manager派も目からウロコの技術特集公開中

オラクルエンジニア通信

最新情報つぶやき中oracletechnetjp

http://blogs.oracle.com/oracle4engineer/

Copyright© 2011, Oracle. ALL rights reserved.

Oracle Databaseの価格ご存知ですか？

36

問題：Oracle Databaseの最小構成はいくらでしょうか？

ヒント：Oracle Standard Edition Oneを5Named User Plus(指名ユーザ) というのが最小構成です。

問題：Real Applications Clusters(RAC) Optionはいくらでしょうか？

ヒント：RACはOracle Database Enterprise EditionのOptionです。

①

②

答えはこちら↓ ログイン不要の簡単見積もり

ライセンス見積もりヘルプ検索


ITプロジェクト全般に渡る無償支援サービス

Copyright© 2011, Oracle. ALL rights reserved.

以上の事項は、弊社の一般的な製品の方向性に関する概要を説明するものです。また、情報提供を唯一の目的とするものであり、いかなる契約にも組み込むことはできません。以下の事項は、マテリアルやコード、機能を提供することをコミットメント（確約）するものではないため、購買決定を行う際の判断材料になさらないで下さい。オラクル製品に関して記載されている機能の開発、リリースおよび時期については、弊社の裁量により決定されます。

Oracle、PeopleSoft、JD Edwards、及びSiebelは、米国オラクル・コーポレーション及びその子会社、関連会社の登録商標です。その他の名称はそれぞれの会社の商標の可能性があります。

40

【体感型セミナー】をクイズ感覚で学ぶ！ SQL - Oracle · Oracle Direct Seminar...

Documents

Transcript of 【体感型セミナー】をクイズ感覚で学ぶ！ SQL - Oracle · Oracle Direct Seminar...

【体感型セミナー】 をクイズ感覚で学ぶ！ SQL - Oracle · Oracle Direct Seminar...

Documents

Transcript of 【体感型セミナー】 をクイズ感覚で学ぶ！ SQL - Oracle · Oracle Direct Seminar...

【体感型セミナー】をクイズ感覚で学ぶ！ SQL - Oracle · Oracle Direct Seminar...

Transcript of 【体感型セミナー】をクイズ感覚で学ぶ！ SQL - Oracle · Oracle Direct Seminar...