Oracle Direct Seminar...21 データ検索時に条件を指定（WHERE句） EMPNO NAME JOB SAL...

Oracle Direct Seminar

試験対策ポイント解説「11g SQL基礎Ⅰ」

<Insert Picture Here>

日本オラクル株式会社

Copyright© 2011 Oracle. All rights reserved. 2

アジェンダ

ORACLE MASTER Oracle Database 11g 概要

「 11g SQL基礎Ⅰ」試験紹介

ポイント解説


実務エキスパートの

認定

↑

↓

製品知識の

認定

ORACLE MASTER Bronze

「Oracle技術基礎」レベル

ORACLE MASTER Silver

「データベース管理中級」レベル

ORACLE MASTER Gold

「データベース管理上級」レベル

ORACLE MASTER Platinum

「データベース管理ｴｷｽﾊﾟｰﾄ」レベル

資格体系


ORACLE MASTER

Platinum

Oracle Database 11g

ORACLE MASTER

Silver

Oracle Database 11g

ORACLE MASTER

Bronze

Oracle Database 11g

Silver DBA11g

Bronze DBA11g

11g SQL 基礎Ⅰ

実技試験

ORACLE MASTER

Gold

Oracle Database 11g Gold DBA11g

基本的な技術を理解し、より高度な技術者のもとで実践的な作業を行うことができるレベル。

基本的なコンセプトの操作方法を理解し、より高度な技術者のもとで簡単な操作ができるレベル。

▲各種プロジェクトメンバー、 ▲チームメンバー

技術要素を全般的に理解する上級技術者として、状況に応じた手法の提案や、助言が行えるレベル。

▲チームリーダー、 ▲小規模プロジェクトリーダー

資格試験

各資格に必要な試験の種類

概要

トップレベルのエキスパートとして幅広い技術力を駆使し、最適な構築、運用や、適切な障害対策が行えるレベル。

▲エキスパート技術者、技術責任者

▲プロジェクトリーダー


試験の受験方法/お申込み方法

• 受験方法

• ピアソンVUE公認テストセンター

• 団体受験 - 所属の企業・団体指定の会場(オンサイト試験）

• 試験のお申込み方法

• http://www.oracle.com/jp/education/certification/examinfo-172594-ja.html

• 試験料 ¥22,260（税込み）

http://www.oracle.com/jp/education/certification/examinfo-172594-ja.html






アジェンダ



ポイント解説


11g SQL基礎Ⅰ試験概要

• SQLを使用してデータの更新、追加、削除および複雑な条件に基づくデータ検索ができる

• Oracleを使用したシステムを設計/開発/運用する場合に必要な表や索引、ビューなどデータベース･オブジェクトを適切に作成できる

• 試験概要（2011年9月現在） • 試験時間： 120分

• 出題数： 55問

• 合格ライン： 60%


11g SQL基礎Ⅰ試験範囲

• 基本的なSQL Select文の作成

• データの制限およびソート

• 単一行関数

• グループ関数を使用したデータの集計

• 複数の表からのデータの表示

• 副問合せを使用した問合せの解決

• データ操作

• オブジェクトの作成および管理


アジェンダ



ポイント解説


ポイント解説範囲（1）



• 単一行関数




• データ操作



SQLとは

• Structured Query Languageの略

• 構造化問合わせ言語である

• 米国規格協会(ANSI)、国際標準化機構(ISO)のいずれも、リレーショナル・データベース用標準言語としてSQLを認定

表

結果

データベースユーザー SQL文


EMPNO NAME JOB SAL DEPTNO

100 武井社長 6000 10

200 山崎経理 3000 20

300 桜井営業 2000 20

400 市川技術 3500 30

500 杉田 1000 30

EMP（社員表）

表とは

• データベース内に格納されているデータベース・オブジェクトである

• 表には、関連するデータが格納されている

• 表には、名前がついている

• 表は、縦（列）と横（行）の2次元から構成されている

列

行

列


NULL（ヌル）値とは


100 武井社長 6000 10

200 山崎経理 3000 20

300 桜井営業 2000 20

400 市川技術 3500 30

500 杉田 1000 30

EMP（社員表）

NULL値

• 行の特定の列に対するデータ値が欠落している場合、その値はNULLである、またはNULLを含むといいます

• NULLはゼロまたは空白とは異なる

• ゼロは数値で、空白は文字


NULL値の計算


100 武井社長 6000 10

200 山崎経理 3000 20

300 桜井営業 2000 20

400 市川技術 3500 30

500 杉田技術 30

EMP（社員表）

NULL値

• NULLに対して、計算を行うことはできない

• NULLは、ゼロとは異なる

2100

6100

3100

3600

SAL+100

NULL値

SAL列値に

100を加算

• NULLに対して計算を行うには、どうすればよい？


NVL関数の使用


100 武井社長 6000 10

200 山崎経理 3000 20

300 桜井営業 2000 20

400 市川技術 3500 30

500 杉田技術 30

EMP（社員表）

NULL値

• NULLに対して計算を行う際には、 NVL関数を使用

• NVL関数は、NULLを別の値に置換する関数

• NULL関数の構文：NVL（列名、値）

• NULL関数の例：NVL（SAL、0）

2100

6100

3100

3600

100

NVL（SAL, 0）+100

0 + 100

SAL列値に

100を加算


問題1 ある会社の社員表（EMPLOYEES表）は、ID列とEMAIL列から構成されています。

ID列には各社員の従業員番号（数値）が格納されており、 EMAIL列にはE-mailアドレス（文字）が格納されています。

各列にNULL値が格納されている場合のNULL値の扱われ方について、正しいものを1つ選択してください。

a. 数値を格納するID列にNULL値が格納されている場合のみ、0として扱われる

b. 文字データを格納するEMAIL列にNULL値が格納されている場合のみ、空白として扱われる

c. 文字データを格納するEMAIL列にNULL値が格納されている場合は、空白として扱われ、数値を格納するID列にNULL値が格納されている場合は0として扱われる

d. いずれの選択肢も該当しない


ある会社の社員表（EMPLOYEES表）は、ID列とEMAIL列から構成されています。ID列には各社員の従業員番号（数値）が格納されており、 EMAIL列にはE-mailアドレス（文字）が格納されています。

各列にNULL値が格納されている場合のNULL値の扱われ方について、正しいものを1つ選択してください。

a. 数値を格納するID列にNULL値が格納されている場合のみ、0として扱われる

b. 文字データを格納するEMAIL列にNULL値が格納されている場合のみ、空白として扱われる

c. 文字データを格納するEMAIL列にNULL値が格納されている場合は、空白として扱われ、数値を格納するID列にNULL値が格納されている場合は0として扱われる

d. いずれの選択肢も該当しない

解答1


問題2 ある会社の社員表（EMPLOYEES表）は、SAL列とCOMMISSION列から構成されています。SAL列には給与額（数値）が格納されており、COMMISSION列には歩合給の割合（数値）が格納されています。

SAL列の値が3,000で、COMMISSION列値がNULLの場合、12*SAL*COMMISSIONの値はいくつになりますか。

正しいものを1つ選択してください。

a. 3,000

b. 36,000

c. 0

d. NULL

e. SQL文の実行が失敗し、エラーとなる


解答2 ある会社の社員表（EMPLOYEES表）は、SAL列とCOMMISSION列から構成されています。SAL列には給与額（数値）が格納されており、COMMISSION列には歩合給の割合（数値）が格納されています。

SAL列の値が3,000で、COMMISSION列値がNULLの場合、12*SAL*COMMISSIONの値はいくつになりますか。

正しいものを1つ選択してください。

a. 3,000

b. 36,000

c. 0

d. NULL

e. SQL文の実行が失敗し、エラーとなる


表データの検索（SELECT 文）


100 武井社長 6000 10

200 山崎経理 3000 20

300 桜井営業 2000 20

400 市川技術 3500 30

500 杉田 1000 30

EMP表

• SELECT文の構文 SELECT 列名, 列名, … FROM 表名；

例１）EMP表から全社員の名前（NAME）を検索 SELECT name

FROM emp ；

SELECT *

FROM emp ；

例2）EMP表から全情報を検索 SELECT empno, name, job, sal, deptno

FROM emp ；

※SQL文では、大文字・小文字は

区別されません。


データ検索時に条件を指定（WHERE句）


100 武井社長 6000 10

200 山崎経理 3000 20

300 桜井営業 2000 20

400 市川技術 3500 30

500 杉田 1000 30

EMP（社員表）

• SELECT文の構文

SELECT

FROM

WHERE ;

条件式

列名

表名

例3）EMP表から部門番号（DEPTNO）が２０である社員の

名前（NAME）と給与（SAL）を検索 SELECT name, sal

FROM emp

WHERE deptno = 20;

3000 山崎

2000 桜井

SAL NAME

検索結果


• ORDER BY句を使用して検索結果を並び替える

• ORDER BY句はSELECT文の最後に記述する


500 杉田技術 1000 30

300 桜井営業 2000 20

200 山崎営業部長 3000 20

400 市川技術部長 3500 30

100 武井社長 5000 10

SELECT name, sal

FROM emp

ORDER BY sal;

SAL列で昇順に並び替え

検索結果の並び替え（ORDER BY句）

SELECT name, sal

FROM emp

ORDER BY sal ASC; ＝

昇順昇順

SELECT name, sal

FROM emp

ORDER BY sal DESC;

降順

EMP（社員表）


並び替えのルール（昇順の場合）

• 数値は、小さい値が先に表示される

• 日付は、古い日付から先に表示される

• 英字は、アルファベット順に表示される

• NULLは、最後に表示される


SELECT文に ORDER BY ASC を指定してデータを検索した場合の動作として、正しいものを2つ選択してください。

a. 数値は、小さい値が先に表示される

b. 日付は、新しい日付から先に表示される

c. 英字は、アルファベット順に表示される

d. NULLは、最初に表示される

問題3


SELECT文に ORDER BY ASC を指定してデータを検索した場合の動作として、正しいものを2つ選択してください。

a. 数値は、小さい値が先に表示される

b. 日付は、新しい日付から先に表示される

c. 英字は、アルファベット順に表示される

d. NULLは、最初に表示される

解答3


WHERE句に指定できる条件

条件式例

=、<=、>=、<、> 、 < > 、！= WHERE sal < > 1000

等しい等しくない給与が$1000以外

BETWEEN 最小値 AND 最大値 WHERE sal BETWEEN 1000 AND 3000

給与が$1000以上、$3000以下

IN （値1、値2、値3、… ） WHERE job IN (‘営業’,’技術’)

職種が営業または技術

LIKE 文字列 WHERE name LIKE ‘A%’

名前がAではじまる

IS NULL ⇔ IS NOT NULL WHERE email IS NULL

メールアドレスがNULL

※1 文字データを指定する際には、シングル・クォーテーションで囲む

※2 シングル・クォーテーションで囲まれた文字については、大文字・小文字を区別する


問題4 EMP（社員）表の情報を表示する際に、表に含まれる各列（EMPNO（社員番号）、

NAME（名前）、SAL（給与）に次のような条件を指定しようと思います。

1. EMPNOが300より大きく500より小さいデータのみ表示

2. NAMEがCではじまるデータのみ表示

3. SALがNULLではないもののみ表示

次のSQL文を実行した場合、上記3つの条件のうちいくつ満たされますか。

SELECT empno, job, sal

FROM emp

WHERE empno BETWEEN 300 AND 500

AND name = ‘C%’

AND sal ！= NULL

a. １つの条件のみ満たされる

b. ２つの条件が満たされる

c. ３つすべての条件が満たされる

d. どの条件も満たされない


解答4 EMP（社員）表の情報を表示する際に、表に含まれる各列（EMPNO（社員番号）、

NAME（名前）、SAL（給与）に次のような条件を指定しようと思います。

1. EMPNOが300より大きく500より小さいデータのみ表示

2. NAMEがCではじまるデータのみ表示

3. SALがNULLではないもののみ表示

次のSQL文を実行した場合、上記3つの条件のうちいくつ満たされますか。

SELECT empno, job, sal

FROM emp

WHERE empno BETWEEN 300 AND 500

↑ EMPNO > 300 AND EMPNO < 500

AND name = ‘C%’ ← LIKE ‘C%’

AND sal ！= NULL ← IS NOT NULL

a. １つの条件のみ満たされる

b. ２つの条件が満たされる

c. ３つすべての条件が満たされる

d. どの条件も満たされない


関数例結果

UPPER UPPER(‘oracle’) ORACLE

大文字に変換小文字のoracleを大文字に変換

SUBSTR SUBSTR(‘oracle’, 2, 3) rac

文字列を抽出 oracleの2文字目から3文字分を抽出

ROUND ROUND(45.923, 2) 45.92

四捨五入 45.923を小数点以下2桁になるよう四捨五入

TRUNC TRUNC(3.14) 3

切捨て 3.14を小数点以下0桁（整数）になるよう切捨て

NVL NVL( comm, 0 ) 0

NULLを実値に変換 comm列値がNULLの場合、0に置き換える

関数


次のSQL文を実行しました。

SQL> SELECT ROUND(45.923, 0), TRUNC(45.923)

2 FROM dual;

表示される値はどれですか。1つ選択してください。

a. 46 と 45.9

b. 46 と 45

c. 46 と 40

d. 50 と 45.9

e. 50 と 46

f. 50 と 40

問題5


次のSQL文を実行しました。

SQL> SELECT ROUND(45.923, 0), TRUNC(45.923)

2 FROM dual;

表示される値はどれですか。1つ選択してください。

a. 46 と 45.9

b. 46 と 45 ← 45.923 （9を四捨五入＆切捨て）

c. 46 と 40

d. 50 と 45.9

e. 50 と 46

f. 50 と 40

解答5


製品表には、次の列が含まれています。

次の文の実行結果を選択肢の中から1つ選んで下さい。

SELECT NVL（数量, 0), コスト

FROM 製品表

WHERE 製造番号 LIKE ‘NF%’ ;

製品番号詳細製造番号数量コスト

1 A1 NF10032 500 3

2 A2 OT456 2000

3 D2 OT456 1

4 C2 OT456 200 0.7

5 A1 OT456 900 3

6 A1 NF10032 4

7 A2 NF10032 2500

8 C2 OT456 80 1

a. 、

b. 、

c. 、

d. 、

数量コスト

500 3

0 4

2500

数量コスト

500 3

4

2500

数量コスト

500 3

4

2500 0

数量コスト

500 3

0 4

2500 0

問題6


製品表には、次の列が含まれています。

次の文の実行結果を選択肢の中から1つ選んで下さい。

SELECT NVL（数量, 0), コスト

FROM 製品表

WHERE 製造番号 LIKE ‘NF%’ ;

製品番号詳細製造番号数量コスト

1 A1 NF10032 500 3

2 A2 OT456 2000

3 D2 OT456 1

4 C2 OT456 200 0.7

5 A1 OT456 900 3

6 A1 NF10032 4

7 A2 NF10032 2500

8 C2 OT456 80 1

a. 、

b. 、

c. 、

d. 、

数量コスト

500 3

0 4

2500

数量コスト

500 3

4

2500

数量コスト

500 3

4

2500 0

数量コスト

500 3

0 4

2500 0

解答6





• 単一行関数




• データ操作



データをグループ化し集計する（GROUP BY）

• GROUP BY句を使用してデータをグループ化できる

• グループ関数を使用して平均（AVG関数）、合計（SUM関数）などのデータの集計ができる

SELECT deptno, sum(sal)

FROM emp

GROUP BY deptno;

EMPNO NAME SAL DEPTNO

100 武井 6000 10

200 山崎 3000 20

300 桜井 2000 20

400 市川 3500 30

500 杉田 1000 30

DEPTNO SUM(SAL)

10 6000

20 5000

30 4500

EMP（従業員表）


集計値をもとにデータを絞り込む（HAVING）

• HAVING句を使用して、集計値を条件に指定してデータを絞り込むことができる

• WHERE句でグループ関数を指定することはできない


100 武井 6000 10

200 山崎 3000 20

300 桜井 2000 20

400 市川 3500 30

500 杉田 1000 30


DEPTNO SUM(SAL)

10 6000

20 5000

合計値は5000未満のため表示されない

SELECT deptno, sum(sal)

FROM emp

GROUP BY deptno

HAVING sum(sal) >= 5000;


問題7 EMP表の部門（DEPTNO)別、職種（JOB_ID）の合計給与が3000より多い部門の部門番号（DEPTNO)と職種（JOB_ID）と合計給与SUM（sal）を検索しようと思います。

検索結果を合計給与の少ないものから順に表示する場合、次のSELECT文の（）に該当する選択肢を1つ選んでください。

SQL> SELECT deptno, job_id, SUM(sal)

2 FROM emp

3 GROUP BY deptno, job_id

4 HAVING SUM(sal) > 3000

5 ( ) ;

a. ORDER BY SUM(sal) DESC

b. ORDER BY job, SUM(saｌ)

c. ORDER BY SUM(sal)

d. ORDER BY sal


解答7 EMP表の部門（DEPTNO)別、職種（JOB_ID）の合計給与が3000より多い部門の部門番号（DEPTNO)と職種（JOB_ID）と合計給与SUM（sal）を検索しようと思います。

検索結果を合計給与の少ないものから順に表示する場合、次のSELECT文の（）に該当する選択肢を1つ選んでください。

SQL> SELECT deptno, job_id, SUM(sal)

2 FROM emp

3 GROUP BY deptno, job_id

4 HAVING SUM(sal) > 3000

5 ( ) ;

a. ORDER BY SUM(sal) DESC

b. ORDER BY job, SUM(sal)

c. ORDER BY SUM(sal)

d. ORDER BY sal





• 単一行関数




• データ操作



• データを操作するSQL文（DML文)

• 新しい行を追加する（INSERT文)

• 行を変更する（UPDATE文)

• 行を削除する（DELETE文)

データの操作


データの追加（INSERT文）

DEPT表

• INSERT文の構文

例）DEPT表に、新しい部署情報を追加

INSERT INTO dept (deptno, dname, location) VALUES (40, ‘総務’, ‘外苑前’);

INSERT INTO dept VALUES (40, ‘総務’, ‘外苑前’);

※1 文字や日付を入力する場合は、

シングル・クォーテーションで囲む

※2 列名の個数と、値の個数は同じにする

INSERT INTO 表名（列名, 列名, ･･･） VALUES （値, 値,･･･）；

DEPTNO DNAME LOCATION

10 CEO 東京

20 営業部赤坂

30 技術部渋谷

40 総務外苑前

すべての列に値を入力する場合は、列リストを省略することができる


• WHERE句を指定して、変更対象行を特定する

• WHERE句を省略するとすべての行が更新される

UPDATE文の構文

UPDATE 表名 SET 列名=値, 列名=値, ･･･ WHERE 条件式

UPDATE emp

SET sal = 1500

WHERE empno = 500;


100 武井 6000 10

200 山崎 3000 20

300 桜井 2000 20

400 市川 3500 30

500 杉田 1000 30


1500

データを更新する（UPDATE文)


• WHERE句を指定して、削除対象行を特定する

• WHERE句を省略するとすべての行が削除される

DELETE文の構文

DELETE FROM 表名 WHERE 条件式

DELETE FROM emp

WHERE empno = 500;


100 武井 6000 10

200 山崎 3000 20

300 桜井 2000 20

400 市川 3500 30

500 杉田 1000 30


データを削除する（DELETE文)


データ操作言語（DML）の文は、（）。

（）に該当する選択肢を1つ選んでください。

a. 表の構造は変更するが、表データは変更しない。

b. 表データおよび表の構造を変更する

c. 表データは変更するが、表の構造は変更しない。

問題8

20 2000 営業桜井 300

10 6000 社長武井 100

20 3000 経理山崎 200

30 3500 技術市川 400

30 1000 杉田 500

DEPTNO SAL JOB NAME EMPNO


データ操作言語（DML）の文は、（）。

（）に該当する選択肢を1つ選んでください。

a. 表の構造は変更するが、表データは変更しない。

b. 表データおよび表の構造を変更する

c. 表データは変更するが、表の構造は変更しない。

解答8

20 2000 営業桜井 300

10 6000 社長武井 100

20 3000 経理山崎 200

30 3500 技術市川 400

30 1000 杉田 500

DEPTNO SAL JOB NAME EMPNO


SQL Developer 11g

SQL Developerのメニュー・オプションを使用した

データ定義言語（DDL）操作の実行

SQL Developerのメニュー・オプションを使用した

データ操作言語（DML）操作の実行


http://www.oracle.com/jp/education/certification/index.html

チェックリスト


https://secure.oracle.co.jp/direct/inquiry-form.php

Oracle Direct 検索

あなたにいちばん近いオラクル

Oracle Direct まずはお問合せください

Web問い合わせフォームフリーダイヤル

専用お問い合わせフォームにてご相談内容を承ります。 ※こちらから詳細確認のお電話を差し上げる場合がありますので、ご登録されている連絡先が最新のものになっているか、ご確認下さい。

0120－155－096 ※月曜~金曜 9:00~12:00、13:00~18:00

（祝日および年末年始除く）

システムの検討・構築から運用まで、ITプロジェクト全般の相談窓口としてご支援いたします。

システム構成やライセンス/購入方法などお気軽にお問い合わせ下さい。


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

OracleとJavaは、Oracle Corporation 及びその子会社、関連会社の米国及びその他の国における登録商標です。文中の社名、商品名等は各社の商標または登録商標である場合があります。

Oracle Direct Seminar...21 データ検索時に条件を指定（WHERE句） EMPNO NAME JOB SAL...

Documents

Transcript of Oracle Direct Seminar...21 データ検索時に条件を指定（WHERE句） EMPNO NAME JOB SAL...