ORACLE MASTER Silver SQL1Z0-071試験の正解率を上げるヒント

この記事が役に立つかもしれない人

• 1Z0-071: ORACLE MASTER Silver SQL試験のために勉強していて、
  範囲は一通り勉強はしたが、うる覚えが多い方。
• Oracle SQLを触り始めたが、細かいエラーに辟易している方

ご自身でSQLを叩いたり公式ドキュメントを確認してください。
(PingTでは　CLOB BLOBは最大4Gと記載されていたが、黒本では　二つとも最大128TBと記載されていたり食い違うこともありうる)

参考文献かつ勉強に使った教材

Ping-t Oracle Master 12c Bronze SQL基礎(1Z0-061)

オラクルマスター教科書 Silver SQL Oracle Database SQL

1Z0-071: ORACLE MASTER Silver SQL模擬試験問題集(6回分277問)

昨日この試験に合格し(86点)、今この記事を書いております。
理解するときや問題を解く時に少しでも楽になればと思ってこの記事を書きました。
参考書には無いまとめ方で乱文ですのでわからないものはすぐに飛ばしてください。一つでも役にたてば幸いです。

ややこしいもの　シリーズ

参照キーは、「参照されるキー」のこと。　

能動的に参照「する」のは外部キー。
親表→参照キー
子表→外部キー

LOCALTIMESTAMP関数はTIMESTAMP型を戻す

　（'LOCAL'と付いてるが、TIMESTAMP WITH LOCAL TAIMEZONE型を戻すわけではない)

因みにタイムゾーンを含む結果を表示する関数はCURRENT_TIMESTAMPのみ。(CURRENT_TIMESTAMP関数はTIMESTAMP WITH TAIMEZONE型を返す)
(因みに_(アンダーバー)のあるものとない関数があるが覚え方は→CURRENTがつくファンクションはアンダーバーあり)

ビューのSELECT文の変更は、「変更」というがALTERは使用できない。

CREATE OR REPLACEで変更できる(上書き)。
(因みにREPLACEされた際、ビューへのオブジェクト権限の付与状態は維持されるのでユーザやロールに再付与する必要はない。)

Oracleでは空文字(長さ０の文字列)をNULLとして扱う。

また、NULLを検索するにはNULL条件を使用する。

因みにSQL*PlusではNULLは空白で表示されるが、これはコマンドで変更可能。
SET NULL ’(NULLとして表示したい文字列)’ というSQL＊Plusコマンドで任意の文字列に変更できる。

削除するSQL ALTERとDROPとTRUNCATE　と　DELET

ALTER TABLE emp DROP COLUMN 列名

• 列を削除できる
• その削除したい列が参照される親表の列だったらオプションCASCADE CONSTRAINTSをつける
• 削除した列は戻せない
• 時間がかかる場合のために SET UNUSED　がある

DROP TABLE

• 表を削除する
• 削除された表はゴミ箱に保管される。
• FLASHBACK TABLE TO BEFORE DROPコマンドで削除したデータを戻せる可能性がある
• データと定義は削除される
• 表に定義されているトリガーもオブジェクト権限も削除される
• ビューとシノニムは無効になる

(←UDEMYの模試では[DROP TABLEするとシノニムは無効になる」は不正解)

TRUNCATE TABLE

• 全ての行を削除するが、DDLに分類される
• FLASHBACK機能は対象外。
• DDLなのでロールバックできない。なのでUNDO(変更前のデータの保管)もない。なのでUNDOがあるDELETEより高速。
• 表に格納された全ての行を削除すると、索引のデータも併せて削除されるが、索引定義は保持される。（表の定義も残る)なので、ユーザに付与されているその表のオブジェクト権限も保持される。
• その実表を参照しているビューは無効化されない。ビューに対してSELECT文を実行できる。
• 諸々が保持されるので、TRUNCATEを使用したら諸々を再生成しなくて済むので便利。
• TRUNCATEが一時表に発行された場合、セッション固有のデータのみが切り捨てられる。他のセッションのデータには何の影響もない。

DELETE FROM TABLE

• 行を削除（全ての行も削除できる）
• ロールバック可能
• UNDO(変更前のデータの保管)あり。なのでTRUNCATEより遅い。

(私の)凡ミスシリーズ

• 「シーケンスは重複しない」という選択肢は❌（CYCLEオプションを指定した場合、重複もありうるため)

• 複数の列の組み合わせに制約を定義する場合は、表レベルでまとめて定義　
  (ズラっと英語が並んだ選択肢を見て、一つのSQL文に、例えば複数のUNIQUE制約が見つかればばその選択肢は不正解❌)

• SELECT '1234'+5　FROM dual;　は 1239 と表示される
  (暗黙的データ変換で、文字型　'12345'になるのではなく、加算されて数値の1239になる)　

• TRIMは一文字（ずつ）　文字列は不可 TRIM(BOTH '*' FROM '**A**'）　　　　　 →表示結果は A
TRUNCは数値か日時を切り捨てる TRUNC(18.6)→表示結果は18 TRUNC(18.6,-1)→表示結果は10

• 自然結合
  二つの表に同名で異なるデータがある場合エラー。
  結合列に表接頭辞をセットしている選択肢は不正解　

自然結合では表名しかSQLに指定しないので、結合列は自分で問題文から探す必要がある

因みに、問題文に「二つの表に共通するMANEGER列は除きたい」という主旨の注意がある場合は、自然結合は不正解。明示的に結合列を指定できる(つまり結合列にしたくない列を書かないという方法で除外できる)USING句かON句が正解。

• （）が必要な文　
  ALTER TABLE emp ADD birthdate DATE ←間違い
  ALTER TABLE emp ADD ( birthdate DATE)←正解
  ALTER TABLE emp MODIFY ( birthdate TIMESTAMP )←正解
  ALTER TABLE emp DROP COLUMN sal; ←単一列の削除　括弧いらない
  ALTER TABEL emp DROP (ename,sal);←複数　括弧いる
  LISTAGG(ename, '|') WITHIN GROUP (ORDER BY )←括弧いる

〇〇だけシリーズ

• グループ関数では　COUNT(*)だけNULLを含む　あとはNULLを無視

• CHAR型だけ固定長　
  ROW LONGROW LONG VARCHAR2 可変長

• VARCHAR2だけ(n)の指定が必須

• 集合演算子　複合問い合わせのUNION ALLだけ
  ソートされない。全てのNULLを表示。

• 表にのみマルチインサートできる。
  ビューやリモート表には不可。

ただし、SQL＊Loaderを使用して外部表にマルチインサートできる。

• NOT NULL制約だけ
  制約の追加のSQLは　ALTER TABLE CONSTRAINT id_pk PRIMARY KEY (id)
  だが、NOT NULL制約だけMODIFY句で追加

• 暗黙のデータ変換　文字型⇆数値型、文字型⇆日付型は変換可能だが、　数値型⇆日付型は変換できない。

文字列連結するCONCAT関数の引数に　数値型と日付型が入っていても、
CONCAT(salary, hiredate)
エラーにならずどちらも文字列型に変換して連結するため正常に表示される。

• 日付書式のMM'22-05-31'がMONに対応していないので明示的な型変換が必要
  他の書式、例えばMON(MAR)は長い月名（MARCH）に対応しているし、RRは4桁の年指定に対応しているのに。。。

因みに
sysdate-TO_DATE('2022-Apr-01,'YYYY-Mon-DD')の計算もできる(Monでも可能)。

• CURRENT_TIMESTAMPのみ
  タイムゾーンを含む結果を表示する関数はCURRENT_TIMESTAMPのみ。(※下記４つ含む５つの関数の中で)
  sysdate
systimestamp
current_date
localtimestamp はタイムゾーンを含まない表示結果

• DELETEだけ
  ビューのWITH CHECK OPTIONにおいて、
  ビューの定義問い合わせに含まれない行が生成されるようなINSERTとUPDATEは禁止(というかエラーになる)だが、
  DMLの内　DELETEだけ禁止されない。なぜならWITH CHECK OPTIONにおいて、変更後のデータがビュー定義のWHERE条件を満たしているかどうかチェックされるから。
  DELETE文にはその「変更後」のデータが無いため、DELETEは実行できる。

また、

列レベルで付与できるオブジェクト権限は、INSERT、UPDATE、REFERENCESオブジェクト権限。
DML三つのうちでDELETEの権限は列レベルで付与できない。

• 外部表だけ
  表、ビュー、一時表、外部表の中で外部表だけ索引を作成できない。

• NOT NULL制約だけ
  5つの制約のうちNOT NULL制約だけ列レベルで指定しなければならない(表レベルで指定できない)
  また、

emp表をコピーして新しいemp１表を作成するとき、他のプライマリーキーなど他の制約は引き継がないが、NOT NULL制約は引き継ぐ

• 通常のロールだけ
  通常のロールだけGRANTオプションを指定してオブジェクト権限を付与できない。ユーザやPUBLECロールには付与できる。
  GRANT SELECT ON oe.emp TO ユーザ WITH GRANT OPTION;　　　　　　　　　　　　　　←成功
  GRANT SELECT ON oe.emp TO PUBLIC WITH GRANT OPTION;　　　　　　　　　　　　←成功
  GRANT SELECT ON oe.emp TO 通常のロール WITH GRANT OPTION;　　　　　←失敗

因みに

システム権限は複数(英単語二つ以上　EXCUTE ANY PROCEDUREシステム権限)で、
オブジェクト権限は一単語(SELECTなど)

暗記を助ける理解シリーズ

読み取り「専用」と言いながら色々できる読み取り専用モード

例　ALTTER TABLE emp READ ONLY　→emp表が読み取り専用になった。
すると、DMLによる変更やSELECT FOR UPDATEは禁止される。
だが読み取り以外のことも色々できたりする。

• DROP TABLE emp PURGE;はできる。読み取り専用であろうが、表そのものを削除することはできないと困る。(DELETE FROM empは不可)
• ALTER TABLE emp READ WRITE;はできる。　読み取り専用モードの切り替え文はできて当然。
• ALTER TABLE emp DROP UNUSED COLUMNS；　はできる。すでに未使用になっている列は実は削除できる。(読み取る事がそもそもできないので読み取り専用モードと関係がないという理解)
• また、制約の追加も可能。

どっちが親表(参照キー)でどっちが子表(外部キー)かはっきりしないならアヒルの親子で理解する

子表の列は複数(重複)がありえる。
子供の方が多い。現代人のイメージではなく、アヒルの親子など動物のイメージ。
逆に親は画像の通り一匹(参照キーは必ず一意か主キー制約が必要)
ちなみに参照キーは、「参照されるキー」のこと。　能動的に参照「する」のは外部キー。画像の通り親の後ろから子供たちが見ている(参照している)イメージ。
子供がいる(参照されている)親(表のの行)は削除(DELETEもDROOP TABLEもTRUNCATE TABLEも)できない。エラーになる。
ただ、道連れになります設定(ON DELETE CASCADE)が子供にされていたら削除できる。
親子関係をNULLにする設定(ON DELETE SET NULL)をしていたら、親の値(その行の外部キーの値)はNULLになる。

因みにこれらのON DELETEオプションは予め設定されている必要があり、DELETE文には付けられない。

ビューに含まれているとそのビューにDMLを実行できなくなるもの５つ

• 集合演算子　
• DISTINCT演算子　
• 集計ファンクション　
• GROUP BY句　
• ORDER BY句
  例えば
  SELECT AVG(salary) FROM emp GROUP BY dept;
  から作成されたビューを変更しようとしても、その部署ごとの平均値はAVG関数の結果であり、実表に無いのでDMLできないことが理解できる。

理由がわからないものシリーズ

• プライベートシノニムの作成文
  CREATE SYNONYM emp FOR emp_t;←正解
  CREATE PRIVATE SYNONYM emp FOR emp_t;←不正解　これも正常に実行されたらいいのに。と思ったがこちらがデフォルトだと理解。
  因みにパブリックシノニムの作成文は
  CREATE PUBLIC SYNONYM emp FOR emp_t;←正解

• 集合演算子で
  列別名を使用した列をorder by句で指定する際に元の名前を使用したらエラー。列別名を使用しなければならない。

（※因みに通常のSELECT文では元の名前でも列別名でもエラーは起こらず表示される)

• MAERGE文において、
  WHEN MATCHED　THENや　WHEN NOT MATCHED THEN句を省略できる、、、。
  省略した場合、UPDATE処理やINSERT処理は実行されない。MERGE文を使う意味。。

• 英単語としては月も曜日も固有名詞なので大文字から始まるのに　 TO_CHAR(SYSDATE, 'month/day')は　june/mondayと出力される必要がある事。

• 単純CASE式ではNULLの評価ができない　CASE 〇〇　WHEN NULL THEN '進行中'←※エラーにはならない。
  検索CASE式で　　　CASE WHEN 〇〇　IS NULL THEN '進行中' というふうに　IS NULL演算子を使うとNULLの評価ができる
  DECODE関数でも可　DECODE(〇〇, NULL,'進行中'

単純CASE式の　CASE 〇〇　WHEN NULL THEN '進行中'はエラーにはならない。

テスト前にだけ確認するシリーズ

• recyclebin（リサイクルビン）ーゴミ箱のこと　(DROP TABLE後の表の移動先)

• メタデータ　- 表の定義のこと

• トランザクションのACID
  A-Atomicity 原子性
  C-Sonsistency 一貫性
  I-Isolation 隔離性
  D-Durability 持続性または永続性または耐久性

• 英語環境のデフォルト値は「DD-MON-RR」←月の表示がMON。これは数字ではなく、「JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC」
  因みにDYでの火曜日はTUE,木曜日はTHU

• 数値書式でのC
  TO_CHAR(12345.67,'C999G999D99') →表示結果は　JPY12,345.67(ユーロ圏内だとEUR12,345.67)
  TO_CHAR(-123456,'999999PR')は <123456> と表示される。
  TO_CHAR(-123456,'999999MI')は 123456- と表示される。

• 選択肢に出てくる1440という数字は24時間を分で表した数字
  SELECT TO_DATE('2022/08/01 00:00:00') + 2/1440 FROM dual;は
  2022/08/01 00:02:00 と表示される

• CEIL関数(天井)とFLOOR関数(床)
  SLEECT CEIL(39.9),FLOOR(39.9) FROM dual;
  表示結果　CEILの方は40, FLOORの方は39