【Oracle Silver SQL】試験対策用備忘録メモ

ORACLE MASTER Silver SQL（1Z0-071-JPN）の試験対策用の個人的な備忘録として、暗記事項をまとめました。

なお、記事の構成は公式¹の出題範囲に依拠しました。

第1章:リレーショナル・データベースの概要

DBMSの利点

• DBMS（データベース管理システム）を使うと以下の様なメリットを得られる

  • 大量のデータを複数のユーザで利用でき、データを高速で参照・変更ができる

  • データ型・トランザクション機能・整合性制約により、データの高い整合性を維持できる

  • データが破損した場合速やかに復旧できる

  • ユーザ単位でアクセスと操作権限の制御ができる

SQLを実行するツール

• Oracleの実行ツールには、SQL*PlusとOracle SQL Developerがある

• SQL*Plusはコマンドラインベースのツール

  • ターミナル・コマンドプロンプト上で実行する

  • SQLに加えて専用のコマンドを実行できる

• Oracle SQL DeveloperはGUIベースのツール

オブジェクト

• オブジェクトとは表などのデータを管理するものをオブジェクトと呼ぶ

• Oracleでは主に以下のオブジェクトが使われる

セグメント・表領域

• セグメントとは記憶域のこと

• 表などのデータを持つオブジェクトには、セグメントが1対1で対応している

• セグメントは表領域に格納さ

  • 1つの表領域は複数のセグメントを格納できる

スキーマ

• スキーマとはユーザと1対1で対応するもので、オブジェクトが格納されている

• スキーマには同名のユーザが所有しているオブジェクトが入っている

初期化パラメータ

• 初期化パラメータはメモリサイズや各機能のオンオフ等の設定値

• ALTER SESSION SET文で初期化パラメータをセッションレベルで変更できる

1ALTER SESSION SET <パラメータ名> = <設定値>;

第2章:SQL SELECT 文を使用したデータの取得

列別名

• 列名の後に列別名を指定できる

• 列別名の前のASは省略できる

• 列別名を"（2重引用符）で囲む必要があるのは列別名が以下の場合

  • 空白を含む

  • $・_・#以外の記号を含む

  • 大文字・小文字を区別する

  • 最初の文字が数字・記号を含む

  • Oracleの予約語

データ型

• 列にはデータ型が指定され、データ型に対応するデータしか格納できない

• 主なデータ型は以下

DESCRIBEコマンド

• DESCRIBEコマンドで表の構造を確認できる

• コマンドはDESCと短縮可能

• コマンドで確認できる要素は以下

文字リテラル

• '（1重引用符）で文字列を囲むと文字リテラルとなり、文字列そのものを指定できる

• 文字リテラル内で1重引用符を使用したい場合は1重引用符を2つ続けて記述するか、代替引用メカニズムを使う

  • 代替引用メカニズムは「q'と任意の文字」「任意の文字と'」で囲む

NULL

• 列に値を設定せずに追加・更新するとNULLが設定される

• 空文字はNULLとして扱われる

• NULLを含む計算式はNULLとして扱われる

• NULLと「指定した文字列」を連結すると指定した文字列が返される

DISTINCT

• SELECTの直後にDISTINCTを記述すると重複データを除外できる

  • それ以外の場所には指定できない

第3章:データの制限とソート

条件式

• 条件式には列別名を指定できない

条件の評価順序

• 条件の評価順序は以下になる（上にあるものが優先される）

  1. =・!=・<・>・<=・>=

  2. IS NULL・LIKE・BETWEEN・IN・EXISTS

  3. NOT

  4. AND

  5. OR

• ()で囲むと順序が優先される

置換変数

• 置換変数を使うことでSQL文の一部を変えたものを繰り返して実行できる

  • &<変数>を使うと毎回変数の値入力が必要

  • &&<変数>を使うと変数の値が保存され繰り返し使える

• DEFINEコマンドでSQL実行前に置換変数を値を設定できる

• 置換変数の処理前後を非表示にするには変数VERIFYをOFFにする

第4章:単一行関数を使用した出力のカスタマイズ

文字列を受け取るファンクション（引数:文字列）

UPPER・LOWER・INITCAPファンクション

• アルファベットの大文字・小文字を変更するファンクションはUPPER・LOWER・INITCAPがある

  • INITCAPファンクションは先頭文字を大文字、それ以降を小文字にする

SUBSTRファンクション

• 文字列から文字を抜き出す

1--STRの先頭N文字目からL文字を抜き出す
2--lを省略したら最後まで抜き出す
3SUBSTR(<文字列STR>,<開始位置N>,<長さL>) 
4
5SUBSTR('ABCDE',2)   --BCDE
6SUBSTR('ABCDE',-2)  --DE マイナスは開始位置を後ろから数える
7SUBSTR('ABCDE',2,3) --BCD

REPLACEファンクション

• 文字列を置換する

1REPLACE(<文字列STR>,<開始位置N>,<長さL>) 

LPAD・RPADファンクション

• 文字列に文字を埋め込む（0埋めなど）

1--STRの左側に、長さがLになるようにPADを埋め込む
2--PADを省略したら空白が埋め込まれる
3LPAD(<文字列STR>,<長さL>,<文字列PAD>) 
4
5LPAD('AB',5,'0') --000AB
6LPAD('AB',5)     --   AB

1--STRの右側に、長さがLになるようにPADを埋め込む
2--PADを省略したら空白が埋め込まれる
3RPAD(<文字列STR>,<長さL>,<文字列PAD>) 
4
5RPAD('AB',5,'0') --AB000

TRIMファンクション

• 文字列の前後から指定の文字を削除する

• 指定する文字は1つのみ（複数はエラー）

1--STRの先頭と末尾から連続した文字CHARを削除
2--<文字CHAR FROM>を省略した場合は前後の空白文字を削除
3TRIM(<文字CHAR FROM> <文字列STR>) 
4
5TRIM('  AB  ')        --AB
6TRIM('A' FROM  'ABA') --B

1--LEADINGを指定した場合STRの先頭の文字CHARを削除
2--TRAILINGを指定した場合STRの末尾の文字CHARを削除
3--BOTHを指定した場合STRの先頭・末尾の文字CHARを削除
4--<文字CHAR FROM>を省略した場合は前後の空白文字を削除
5TRIM(<LEADING | TRAILING | BOTH> <文字CHAR FROM> <文字列STR>) 
6
7TRIM(LEADING '*' FROM '**AB**')  --AB**
8TRIM(TRAILING '*' FROM '**AB**') --**AB
9TRIM(BOTH '*' FROM '**AB**')     --AB

数値を受け取るファンクション

INSTRファンクション

• 指定した文字列の出現した位置を返す

1--文字列STRのPOS文字目から文字列SEARCHを検索、N回目に出現する位置を返す
2--POSとNを省略した場合は1となる
3--SEARCHが無い場合0が戻る
4INSTR(<文字列STR>, <文字列SEARCH>, <場所POS>, <回数N>) 
5
6INSTR('ABCDE', 'C')            --3
7INSTR('ABCDEABCDE', 'C', 6, 1) --8（6文字目から数えて1回目に出現する位置）

ROUNDファンクション

• 数値を四捨五入する

1--数値Nを小数点以下INT桁で四捨五入
2--INTが負の時は小数点左側INT桁で四捨五入
3--INTを省略した時は0扱いになり整数で四捨五入
4ROUND(<数値N>, <桁INT>)      
5
6ROUND(15.555)     --16
7ROUND(15.555, 1)  --15.6
8ROUND(15.555, -1) --20

TRUNCファンクション

• 数値を切り捨てる

日時の計算・日時ファンクション

日時データの計算

• 日時データの計算の挙動は以下の表の通りになる

  • 日時+日時の計算はエラーになる

NEXT_DAYファンクション

• 指定した日付の後に来る、指定した曜日の日付を返す

1--日付DATEの後に来るSTRに指定した曜日で最初の日付を返す
2NEXT_DAY(<日付DATE>, <曜日STR>)
3
4--日付DATEの後に来るNUMに指定した曜日で最初の日付を返す
5--数値NUMは、1:日、2:月、3:火…のように定義される
6NEXT_DAY(<日付DATE>, <曜日NUM>) 

第5章:変換関数と条件式の使用

データ型の変換

• データ型の変換については明示的な変換（ファンクションを使用して型を変換を指定する）と暗黙的な変換（Oracleが自動的に型を変換する）がある

  • 暗黙的な変換は意図しない挙動になることがあるため明示的な変換が推奨される

NULLに関するファンクション

NVLファンクション

• NULLを指定した値に置き換える

1--INがNULL以外の場合INをそのまま戻す
2--INがNULLの場合OUTを戻す（INとOUTの型が異なる場合は暗黙的な変換が行われる）
3NVL(<入力IN>, <出力OUT>)

NVL2ファンクション

• 入力がNULLかNULL以外かで指定した値に置き換える

1--INがNULL以外の場合OUT1を戻す
2--INがNULLの場合OUT2を戻す（INとOUTの型が異なる場合は暗黙的な変換が行われる）
3NVL2(<入力IN>, <出力OUT1>, <出力OUT2>)

NULLIFファンクション

• 2つの引数の値が等しい時にNULLを戻す

  • 等しくない時は1つ目の引数の値をそのまま戻す

  • 2つの引数のデータ型は一致している必要がある

COALESCEファンクション

• 複数の値から最初にあるNULLでない値を返す

  • 引数のデータ型は全て一致している必要がある

1--入力の中から最初の非NULL値を返す
2--全てNULLの時はNULLを返す
3COALESCE(<入力IN1>, <入力IN2>, ....)

第6章:グループ関数を使用した集計データのレポート

集計ファンクション

• 複数のデータを集計するときは集計ファンクションを使用する

• 集計ファンクションは返り値のデータは1件になる

• NULLは集計の対象外になる

  • COUNT(*)ではNULLもカウントの対象になる

  • COUNT(<列名>)の時は列がNULLの時は集計の対象外になる

• DISTINCTを集計ファンクションに指定すると、重複データを排除してから集計処理を行う

  • 例:AVG(DISTINCT <列名>)

GROUP BY句とHAVING句

• データをグループに分けてからそれぞれ集計したい時はGROUP BY句にグループ分けしたい列を指定する

  • WHERE句で絞ってからグループ分けできる（グループ分けの前に絞り込みされる）

  • GROUP BY句に複数の列を指定した場合列の組み合わせでグループ分けされる

  • 集計ファンクションをネストすることで、グループごとに集計したデータをさらに集計できる

• HAVING句を使うとグループ分けし集計した後にデータの絞り込みができる

第7章:複数の表からのデータの表示

内部結合

• 複数の表からデータを結合（JOIN）することができる

• 内部結合では結合条件（ON句）を満たすデータのみ表示する

外部結合

• 外部結合では結合条件を満たさないデータも表示される

USING句

• 2つの表を同じ名前で結合する時はUSING句が使用できる

NATURAL JOIN構文

• 列の名前とデータ型が同じ場合NATURAL JOINを使用できる

自己結合

• 同じ表を結合することもでき、自己結合と呼ばれる

クロス結合

• クロス結合（デカルト積）では行の全ての組み合わせを返す

  • 表名を指定する時にCROSS JOINか,を指定する

非等価結合

• 結合に=以外の条件を使用した結合は非等価結合と呼ばれる

• USINGとNATURAL JOINを用いた結合では非等価結合はできない

第8章:副問合せを使用した問合せの解決

副問合せ

• SQL文の中にSELECT文を埋めることができ、そのSELECT文を副問合せ（サブクエリ）と呼ぶ

  • 副問合せを埋め込んだSQL文を主問合せと呼ぶ

  • 1つの主問合せに埋め込める副問合せの数に制限は無く、副問合せの中に副問合せを埋め込むことも可能

スカラー副問合せ

• 「1つの行の1つの列の値」を戻す副問合せをスカラー副問合せと呼ぶ

• スカラー副問合せはSELECT文のWHERE句だけではなく、FROM句やHAVING句などの単独の値を指定できる所ならどこでも使える

• スカラー副問合せでは複数の行・列の値を返す場合はエラーになる

• スカラー副問合せのWHERE条件を満たす行がない時はNULLを返す

非スカラー副問合せ

• 「1つの行の1つの列の値以外」を戻す副問合せを非スカラー副問合せと呼ぶ

• IN条件を指定すると複数の値のいずれかに等しい時条件を満たす

• ANY条件を指定すると複数の値のいずれかが指定した演算子を満たす場合に条件を満たす

  • =・!=・>・>=・<・<=・<>を指定できる

• ALL条件を指定すると複数の値の全てが指定した演算子を満たす場合に条件を満たす

  • 指定できる演算子はANY条件と同じ

相関副問合せ

• 副問合せの中で「主問合せで参照している表の列」を参照している問合せを相関副問合せと呼ぶ

  • 相関副問合せでは主問合せと副問合せが関連付けられているので、副問合せのSQLを単独で実行はできない

• 相関副問合せの内、主問合せと副問合せで同じ表を参照するものを自己相関副問合せと呼ぶ

第9章:集合演算子の使用

集合演算

• SQLでは問合せによって戻されるデータを1つの集合として扱うことで集合演算が可能

• 集合演算子は以下の4つ

集合演算の注意事項

• SELECTの列数・データ型を一致させる

• SELECTの列名は異なっても良い

• ORDER BY句は文の最後に指定する

• UNION ALL以外の集合演算では戻るデータがソートされる（最初の列の昇順）

第10章:DML 文を使用した表の管理

DML

• DML（表のデータを操作するSQL）にはINSERT文・UPDATE文・DELETE文・MERGE文などがある

マルチテーブルINSERT

• マルチテーブルINSERTでは副問合せが戻す複数行を複数表の行へ挿入できる

MERGE文

• MERGE文はある表から取得した行を元に、別の表の追加・更新を行う

1MERGE INTO <ターゲット表>
2  USING <ソース表>
3  ON (結合条件)
4  WHEN MATCHED THEN
5    UPDATE ...
6  WHEN NOT MATCHED THEN
7    INSERT ...

トランザクション

• トランザクションはDMLを実行すると自動で開始され、COMMIT文を実行すると終了する（変更が確定）

  • COMMIT文を実行する前にROLLBACK文を実行すれば変更を取り消せる

• トランザクションの開始・終了の条件は以下

• トランザクション中にセーブポイントを指定すると、トランザクション中にセーブポイントまで戻ることができる

第11章:DDL による表とその関係の管理

オブジェクト

• オブジェクトにはスキーマオブジェクトと非スキーマオブジェクトがある

  • スキーマオブジェクトは表・索引・ビュー・シーケンス・シノニムなど特定のユーザに所有される

  • 非スキーマオブジェクトはユーザ・ロール・表領域など特定のユーザに所有されない

DDL

• オブジェクトの定義に関するSQLはDDLと呼ばれる

第12章:ビューの管理

ビュー

• ビューは表に対するSELECT文より定義されるオブジェクト

• ビューを作成するにはCREATE VIEW文を使用する

  • OR REPLACEを指定すると同じビューがあったときに上書きされる

  • WITH READ ONLYを指定するとビューは読み取り専用になる

  • WITH CHECK OPTIONを指定するとビューのデータを変更した時、変更後のデータがビュー定義のWHERE条件を満たすかチェックする（満たさない場合エラー）

第13章:索引とシノニムとシーケンスの管理

索引

• 表からのデータの読み取りを高速化するオブジェクトを索引（インデックス）と呼ぶ

  • 使用することで読み取るデータ量を削減して処理を高速にする

1CREATE INDEX <索引名> ON <表名> (<列名> ... ) --索引の作成
2
3DROP INDEX --索引の削除

• 索引をUNUSABLE（使用不可）にすると索引のセグメントが削除される（索引の定義は削除されない）

• 索引をINVISIBLE（不可視化）にすることで、索引を削除しても影響が無いか確認ができる（索引が見えなくなるが実体は残る）

シーケンス

• シーケンスを使うことでデータベース全体で一意である連番を振る処理を行う

1CREATE SEQUENCE <シーケンス名> --シーケンスの作成
2
3<シーケンス名>.NEXTVAL         --シーケンスから新たに連番を振り出す
4
5<シーケンス名>.CURVAL          --振り出された連番の確認

シノニム

• シノニムは表・ビューなどのオブジェクトに対して付ける別名

• プライベートシノニムとパブリックシノニムがある

  • プライベートシノニムは所有ユーザとシノニムが差すオブジェクトにアクセスできるユーザがアクセスできる

  • パブリックシノニムはどのユーザでもアクセスできる

第14章:ユーザー・アクセスの制御

ユーザ

• DBでデータを管理するには属性・権限を付与したユーザを作成する

• ユーザは所有するオブジェクトに対してどの表領域からどれだけストレージ領域を割り合てるか設定できる

スキーマ

• ユーザには同名のスキーマが存在し、ユーザが所有するオブジェクトはユーザのスキーマに格納される

権限

• Oracleの権限はシステム権限とオブジェクト権限の2種類ある

• システム権限はデータベースへの操作が実行可能かを制御する権限

• オブジェクト権限は他のユーザが所有する表・ビューなどのオブジェクトに対する操作が実行可能かを制御する権限

• 権限はGRANT文で付与しREVOKE文で取り消す

• ADMINオプションを指定してシステム権限を付与されたユーザは他のユーザにそのシステム権限を付与できる

• GRANTオプションを指定してオブジェクト権限を付与されたユーザは他のユーザにそのオブジェクト権限を付与できる

ロール

• 複数の権限をまとめたものをロールと呼ぶ

第15章:データ・ディクショナリ・ビューを使用したオブジェクトの管理

• データディクショナリと呼ばれるOracle内部の管理情報を格納している特殊な表から作られるビューをデータディクショナリビューと呼ぶ

第16章:異なるタイムゾーンでのデータの管理

タイムゾーン

• 各国・地域で使われる日時の基準を標準時と呼び、その標準時を使う国・地域をタイムゾーンと呼ぶ

• タイムゾーンはタイムゾーンリージョン名かタイムゾーンオフセットで指定する

  • タイムゾーンリージョン名は日本の場合Japan・Asia/Tokyo

  • タイムゾーンオフセットは日本の場合+9:00

• Oracleにはタイムゾーンに対応している日時データ型はTIMESTAMP ＷITH TIME ZONE型とTIMESTAMP ＷITH LOCAL TIME ZONE型がある

期間データ型

• 期間データ型にはINTERVAL YEAR TO MONTH型とINTERVAL DAY TO SECOND型がある

参考文献

• ORACLE MASTER Silver SQL 1Z0-071-JPN受験準備ガイド | Oracle

• Oracle Database SQL言語リファレンス, 12cリリース1 (12.1) | Oracle