Oracle Direct Seminar <Insert Picture Here> 体感型セミナー SQL をクイズ感覚で学ぶ! SQL クイズ日本オラクル株式会社

(1)

<Insert Picture Here>

Oracle

Direct Seminar

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

(2)

はじめに

• 本セミナーは、翔泳社「SQLクイズ」をもとに、実際にSQLを

考えていただく、受講者参加型のセミナーです。

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

(3)

アジェンダ

• SQL実行環境のご紹介

• できた？宿題の解説

(4)

SQL実行環境のご紹介

• SQL実行環境「Oracle Application Express」

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

• Oracle Application Expressの利用方法に関しては、以下のURLをご確認ください。

(5)

セミナー中に利用する表

社員表（EMPLOYEES表）

(6)

(7)

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

問題1.

人事部では組織見直しのため、職種の統計を調べています。

それぞれの職種ごとの平均給与を給与額が高い順に表示してください。（列名を「職種」と「平均給与」として表示してください。）

(8)

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

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

• それぞれの職種ごとの給与平均を出す

• GROUP BY句の利用構文：GROUP BY group_by_expression 構文：AVG（列名）平均値ポイント •group_by_expression：グループ化の基準となる列を指定 •列別名の使用は不可 •SELCTで指定された列はグループ関数列を除いて、全ての列をGROUP BY句に含める必要があります。 • AVG関数の利用関数意味 COUNT 行の数 MAX 最大値の算出 MIN 最小値の算出 SUM 合計値の算出表：その他のグループ関数

(9)

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

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

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

• ORDER BY句の利用

構文：ORDER BY ｛列名、列別名、列位置｝ [ASC | DESC] ポイント ASC：行を昇順(A→Z)にソート DESC：行を降順（Z→A）にソート ※ASCもしくはDESCを省略した場合は、昇順（ASC）でソートされます。

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

(10)

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

解答例

SELECT job_id “職種”, AVG(salary) “平均給与” FROM employees

GROUP BY job_id

ORDER BY AVG(salary) DESC;

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

GROUP BY group_by_expression

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

(11)

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

問題2.

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

(12)

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

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

• 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;

(13)

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

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

• first_nameの最初の3文字と、hire_dateの日付を連結する • 連結演算子の使用 • 結果を大文字で表示する • UPPER関数の使用構文：要素1 || 要素2 || 要素3 実行例：

SELECT last_name "名字", first_name "名前", last_name||first_name "氏名"

FROM employees;

(14)

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

解答例

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

(15)

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

問題3.

人事部では給与の見直しを行うため、給与の調査をしています。職種(job_id)がITプログラマ（IT_PROG）ではなく、給与（salary）がどのITプログラマ（IT_PROG）よりも多い人を表示してください。

(16)

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

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

• ITプログラマでない職種を探す • 比較演算子を利用構文：・・・・・ WHERE 値演算子値実行例：

SELECT last_name "名前", job_id "職種“

FROM employees WHERE job_id <> 'IT_PROG':

演算子意味 = 等しい > 大きい >= 以上 < 小さい <= 以下 <>,!=,^= 等しくない２つの演算子意味 BETWEEN・・・ AND・・・ 2つの値の間（その値自体を含む） IN（set）値のリストのいずれかに一致 LIKE 文字のパターンに一致 IS NULL NULL値である

(17)

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

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

• どのITプログラマより給与が高い社員を求める • 複数行副問合せの使用 • 複数行比較演算子を使用します構文： SELECT 列名 FROM 表名 WHERE 列名比較条件（SELECT 列名 FROM 表名・・・・）副問合せ主問合せ演算子意味 IN リスト内のいずれかのメンバーと等しい ANY 直前に=、!=、>、<、<=、>=が必要。１つの値をリスト内の値、または問い合わせで戻されるそれぞれの値と比較 ALL 直前に=、!=、>、<、<=、>=が必要。１つの値をリスト内の値、

SELECT last_name, salary, job_id FROM employees WHERE salary > ALL

( SELECT salary FROM employees

(18)

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

解答例

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

(19)

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

問題4.

人事部では社員の勤続年数を調べています。

各社員が入社してからの勤続年数によって「15年以上」「10年以上」「5年以上」「5年未満」と表示してください。

(20)

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

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

• 条件によって処理を変える • 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 > 10000 THEN '対象者' ELSE '対象外' END) "ステータス" FROM employees; • 「何年」を表す • TO_YMINTERVAL関数を使用

(21)

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

解答例

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; ・CASE式条件によって処理を変える「WHEN」句で条件を記述し、「THEN」句に表示する結果を記述 Point

(22)

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

みなさんの解答例①

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年」の計算の仕方にもいろいろありますね。

(23)

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

みなさんの解答例②

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

(24)

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

問題

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

(25)

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

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

• 部門名と、その部門の社員数、各部門の給与合計を求める • 社員表と部門表を結合する実行例： SELECT department_name “部門名”, COUNT(*) "社員数", SUM(salary) "合計給与"

FROM employees e JOIN departments d

ON e.department_id = d.department_id GROUP BY department_name;

(26)

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

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

• 部門に配属されていない社員も「未配属」として表示する実行例： 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 (expr1,expr2)

expr1がNULLならexpr2の値を戻す

•NVL関数の利用の利用

※外部結合

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

(27)

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

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

• 部門に所属する社員名を表示する EMPLOYEES表 DEPARTMENTS表 SELECT NVL(department_name,'未配属') “部門名”, last_name "社員名"

FROM employees e LEFT OUTER JOIN departments d ON e.department_id = d.department_id;

※外部結合

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

(28)

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

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

• ②と③の処理をまとめて一つにして表示する • 複数の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) "合計給与", '配属社員' "社員名" ・・・

UNION

SELECT department_name, NULL "社員名" , NULL "合計給与", last_name ・・・

(29)

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

解答例

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; ・外部結合対応する値がない行データも含めて表示する結合方法・UNION集合演算子複数のSELECT文の結果をまとめて表示する Point

(30)

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

みなさんの解答例①

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))

(31)

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

みなさんの解答例①

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

(32)

まとめ

• SQL実行環境のご紹介

• できた？宿題の解説

• チャレンジ！SQLクイズ

できましたか？様々な関数、構文を取り入れ、シンプル＆効率的なSQL文を書いてくださいね！

(33)

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

(34)

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

ダイセミで実施された技術コンテンツを動画で配信中!! ダイセミのライブ感はそのままに、お好きな時間で受講頂けます。最新情報つぶやき中 oracletechnetjp ・人気コンテンツは？・お勧め情報・公開予告など ※掲載のコンテンツ内容は予告なく変更になる可能性があります。期間限定での配信コンテンツも含まれております。お早めにダウンロード頂くことをお勧めいたします。 OTN トップページ http://www.oracle.com/technetwork/jp/index.html ページ左「基本リンク」＞「OTN セミナーオンデマンド」

(35)

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

オラクルエンジニア通信

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

• 技術資料 • ダイセミの過去資料や製品ホワイトペーパー、スキルアップ資料などを多様な方法で検索できます • キーワード検索、レベル別、カテゴリ別、製品・機能別 • コラム • オラクル製品に関する技術コラムを毎週お届けします • 決してニッチではなく、誰もが明日から使える技術の「あ、そうだったんだ！」をお届けしますこんな資料が人気です  6か月ぶりに資料ダウンロードランキングの首位が交代！ 新王者はOracle Database構築資料でした。  データベースの性能管理手法について、Statspack派も Enterprise Manager派も目からウロコの技術特集公開中

オラクルエンジニア通信

最新情報つぶやき中 oracletechnetjp

(36)

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

問題：

Oracle Databaseの最小構成はいくらでしょうか？ ヒント：

Oracle Standard Edition Oneを

5Named User Plus(指名ユーザ) というのが最小構成です。

問題：

Real Applications Clusters(RAC) Optionはいくらでしょうか？ ヒント：

RACはOracle Database Enterprise EditionのOptionです。

①

②

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

(37)

(38)

(39)

(40)

以上の事項は、弊社の一般的な製品の方向性に関する概要を説明するものです。また、情報提供を唯一の目的とするものであり、いかなる契約にも組み込むことはできません。以下の事項は、マテリアルやコード、機能を提供することをコミットメント（確約）するものではないため、購買決定を行う際の判断材料になさらないで下さい。オラクル製品に関して記載されている機能の開発、リリースおよび時期については、弊社の裁量により決定されます。 Oracle、PeopleSoft、JD Edwards、及びSiebelは、米国オラクル・コーポレーション及びその子会社、関連会社の登録商標です。その他の名称はそれぞれの会社の商標の可能性があります。

Oracle Direct Seminar <Insert Picture Here> 体感型セミナー SQL をクイズ感覚で学ぶ! SQL クイズ 日本オラクル株式会社

Oracle

Direct Seminar

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

はじめに

•

本セミナーは、翔泳社「SQLクイズ」をもとに、実際にSQLを

考えていただく、受講者参加型のセミナーです。

•

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

アジェンダ

•

SQL実行環境のご紹介

•

できた？宿題の解説

SQL実行環境のご紹介

•

SQL実行環境 「Oracle Application Express」

セミナー中に利用する表

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

問題1.

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

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

•

それぞれの職種ごとの給与平均を出す

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

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

•

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

•

ORDER BY句の利用

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

解答例

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

問題2.

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

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

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

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

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

解答例

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

問題3.

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

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

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

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

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

解答例

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

問題4.

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

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

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

解答例

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

みなさんの解答例①

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

みなさんの解答例②

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

問題

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

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

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

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

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

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

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

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

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

解答例

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

みなさんの解答例①

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

みなさんの解答例①

まとめ

•

SQL実行環境のご紹介

•

できた？宿題の解説

Oracle Direct Seminar <Insert Picture Here> 体感型セミナー SQL をクイズ感覚で学ぶ! SQL クイズ日本オラクル株式会社

SQL実行環境「Oracle Application Express」

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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