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

Size: px

Start display at page:

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

いおりかたづ
4 years ago
Views:

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

はじめに本セミナーは翔泳社 SQL クイズをもとに実際に SQL を考えていただく受講者参加型のセミナーです宿題

SQL 実行環境のご紹介 SQL 実行環境 Oracle Application

com/pls/apex/ Oracle Application Express

それぞれの職種ごとの給与平均を出す GROUP BY 句の利用構文 :GROUP

8 < できた? 宿題の解説第 1 問 > 問題を解くためのポイント 1 それぞれの職種ごとの給与平均を出す 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

(ASC) でソートされます NULLS FIRST または NULLS LAST を使用することで NULL

9 < できた? 宿題の解説第 1 問 > 問題を解くためのポイント 2 給与の高い順に行をソートする 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

10 < できた? 宿題の解説第 1 問 > 解答例 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

11 < できた? 宿題の解説第 2 問 > 問題 2. 人事部では社員のデフォルトのパスワードを管理していますパスワードは従業員の名前 (first_name) の最初の 3 文字 ( 大文字を利用 ) と ( 入社日 ) hire_date の日付 (MMDD) を組み合せたものですパスワードを自動生成するにはどのような SELECT 文を使えばよいでしょうか例 : 名前 (first_name):wendy 入社日 (hire_date): (yy-mm-dd) の場合パスワード :WEN0401 Copyright 2011, Oracle. ALL rights reserved. 11

対象の文字列, 開始位置, 取得文字数 ) 実行例 : SELECT last_name, SUBSTR(last_name,1,1) FROM employees;

12 < できた? 宿題の解説第 2 問 > 問題を解くためのポイント 1 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

13 < できた? 宿題の解説第 2 問 > 問題を解くためのポイント 2 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

14 < できた? 宿題の解説第 2 問 > 解答例 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

last_name " 名前 ", job_id " 職種 FROM employees WHERE job_id <> 'IT_PROG': 演算子意味演算子意味 =

16 < できた? 宿題の解説第 3 問 > 問題を解くためのポイント 1 IT プログラマでない職種を探す比較演算子を利用構文 : WHERE 値演算子値実行例 : SELECT last_name " 名前 ", job_id " 職種 FROM employees WHERE job_id <> 'IT_PROG': 演算子意味演算子意味 = 等しい > 大きい >= 以上 < 小さい <= 以下 <>,!=,^= 等しくない 2つの BETWEEN AND IN(set) LIKE IS NULL 2 つの値の間 ( その値自体を含む ) 値のリストのいずれかに一致文字のパターンに一致 NULL 値である Copyright 2011, Oracle. ALL rights reserved. 16

employees WHERE salary > ALL ( SELECT salary FROM employees 副問合せ WHERE job_id = 'IT_PROG ) ; 主問合せ構文 : SELECT 列名副問合せ

17 < できた? 宿題の解説第 3 問 > 問題を解くためのポイント 2 どの 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 意味リスト内のいずれかのメンバーと等しい直前に =!= > < <= >= が必要 1 つの値をリスト内の値または問い合わせで戻されるそれぞれの値と比較直前に =!= > < <= >= が必要 1 つの値をリスト内の値または問い合わせで戻される全ての値と比較 Copyright 2011, Oracle. ALL rights reserved. 17

18 < できた? 宿題の解説第 3 問 > 解答例 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 比較演算子 1つの式を別の値または式と比較する条件で使用副問合せある結果を元にして他の処理を行う構文副問合せは括弧 ( ) で囲んで主問合せの中に記述する Copyright 2011, Oracle. ALL rights reserved. 18

20 < できた? 宿題の解説第 4 問 > 問題を解くためのポイント 1 SELECT last_name " 名前 ",salary " 給与 ", (CASE WHEN salary > THEN ' 対象者 ' ELSE ' 対象外 ' END) " ステータス " FROM employees; SELECT SYSDATE, SYSDATE-TO_YMINTERVAL('5-0') "5 years_ago", SYSDATE-TO_YMINTERVAL('10-0') "10 years_ago" FROM employees; 条件によって処理を変える CASE 式の使用構文 : CASE WHEN 条件 1 THEN 結果 1 WHEN 条件 2 THEN 結果 2 ELSE END 何年を表す例外処理 TO_YMINTERVAL 関数を使用構文 :TO_YMINTERVAL('01-02') 1 年 2 ヶ月 Copyright 2011, Oracle. ALL rights reserved. 20

21 < できた? 宿題の解説第 4 問 > 解答例 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

22 < できた? 宿題の解説第 4 問 > みなさんの解答例 1 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

23 < できた? 宿題の解説第 4 問 > みなさんの解答例 2 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

24 < できた? 宿題の解説第 5 問 > 問題部門ごとの社員の人数を調べるため以下のようなレポートを出力する必要があります全部門の部門名とその部門の社員数各部門の給与合計および社員名を表示してください社員がいない部門は表示する必要はありませんが部門に所属していない社員のデータは未配属として表示してください EMPLOYEES 表 DEPARTMENTS 表 Copyright 2011, Oracle. ALL rights reserved. 24

25 < できた? 宿題の解説第 5 問 > 問題を解くためのポイント 1 部門名とその部門の社員数各部門の給与合計を求める社員表と部門表を結合する実行例 : 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

部門名 ", COUNT(*) 社員数, SUM(salary) " 合計給与 " FROM employees e LEFT OUTER JOIN departments d ON e.

26 < できた? 宿題の解説第 5 問 > 問題を解くためのポイント 2 部門に配属されていない社員も未配属として表示する実行例 : 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

27 < できた? 宿題の解説第 5 問 > 問題を解くためのポイント 3 部門に所属する社員名を表示する 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

28 < できた? 宿題の解説第 5 問 > 問題を解くためのポイント 4 2 と 3 の処理をまとめて一つにして表示する複数の SELECT 文の結果をまとめて表示する連結演算子 (UNION) を使用 NULL NULL NULL NULL 構文 : SELECT 列名 FROM 表名 UNION SELECT 列名 FROM 表名 NULL NULL NULL NULL NULL NULL NULL NULL 列をそろえる必要があるため足りない列の位置に NULL やリテラル文字 ( 配属社員 ) を指定実行例 : SELECT NVL(department_name, ' 未配属 ') " 部門名 ", COUNT(*) " 社員数 ", SUM(salary) " 合計給与 ", ' 配属社員 ' " 社員名 " UNION SELECT department_name, NULL " 社員名 ", NULL " 合計給与 ", last_name Copyright 2011, Oracle. ALL rights reserved. 28

29 < できた? 宿題の解説第 5 問 > 解答例 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

30 < できた? 宿題の解説第 5 問 > みなさんの解答例 1 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

31 < できた? 宿題の解説第 5 問 > みなさんの解答例 1 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

33 OTN ダイセミでスキルアップ!! 一般的な技術問題解決方法などを知りたい! セミナ資料など技術コンテンツがほしい! Oracle Technology Network(OTN) をご活用下さい一般的技術問題解決には OTN 掲示版のデータベース一般をご活用ください OTN 掲示版は基本的に Oracle ユーザー有志からの回答となるため 100% 回答があるとは限りませんただ過去の履歴を見ると質問の大多数に関してなんらかの回答が書き込まれております過去のセミナ資料動画コンテンツは OTN の OTN セミナーオンデマンドコンテンツへダイセミ事務局にダイセミ資料を請求頂いてもお受けできない可能性がございますので予めご了承くださいダイセミ資料は OTN コンテンツオンデマンドかセミナ実施時間内にダウンロード頂くようお願い致します Copyright 2011, Oracle. ALL rights reserved. 33

OTN セミナーオンデマンドコンテンツダイセミで実施された技術コンテンツを動画で配信中!! ダイセミのライブ感はそのままにお好きな時間で受講頂けます最新情報つぶやき中 oracletechnetjp 人気コンテンツは? お勧め情報公開予告など OTN トップページ http://www.oracle.com/technetwork/jp/index.

34 OTN セミナーオンデマンドコンテンツダイセミで実施された技術コンテンツを動画で配信中!! ダイセミのライブ感はそのままにお好きな時間で受講頂けます最新情報つぶやき中 oracletechnetjp 人気コンテンツは? お勧め情報公開予告など OTN トップページページ左基本リンク > OTN セミナーオンデマンド掲載のコンテンツ内容は予告なく変更になる可能性があります期間限定での配信コンテンツも含まれておりますお早めにダウンロード頂くことをお勧めいたします Copyright 2011, Oracle. ALL rights reserved. 34

Oracle エンジニアのための技術情報サイトオラクルエンジニア通信 http://blogs.oracle.

カテゴリ別製品機能別コラムオラクル製品に関する技術コラムを毎週お届けします決してニッチではなく誰もが明日から使える技術のあそうだったんだ!

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

36 Oracle Database の価格ご存知ですか? 1 問題 : Oracle Database の最小構成はいくらでしょうか? ヒント : Oracle Standard Edition One を 5Named User Plus( 指名ユーザ ) というのが最小構成です 2 問題 : Real Applications Clusters(RAC) Option はいくらでしょうか? ヒント : RAC は Oracle Database Enterprise Edition の Option です答えはこちらログイン不要の簡単見積もりライセンス見積もりヘルプ検索 Copyright 2011, Oracle. ALL rights reserved. 36

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

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

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