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



# この記事が役に立つかもしれない人
+ 1Z0-071: ORACLE MASTER Silver SQL試験のために勉強していて、
範囲は一通り勉強はしたが、うる覚えが多い方。
+ Oracle SQLを触り始めたが、細かいエラーに辟易している方

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

# 参考文献かつ勉強に使った教材
[Ping-t Oracle Master 12c Bronze SQL基礎(1Z0-061)](https://mondai.ping-t.com/)

[オラクルマスター教科書 Silver SQL Oracle Database SQL](https://www.amazon.co.jp/%E3%82%AA%E3%83%A9%E3%82%AF%E3%83%AB%E3%83%9E%E3%82%B9%E3%82%BF%E3%83%BC%E6%95%99%E7%A7%91%E6%9B%B8-Silver-SQL-Oracle-Database-ebook/dp/B09DPJD596/ref=sr_1_1?crid=15OY8PUCGB7CL&keywords=sql+%E9%BB%92%E6%9C%AC&qid=1662081582&sprefix=%2Caps%2C426&sr=8-1)

[1Z0-071: ORACLE MASTER Silver SQL模擬試験問題集(6回分277問)](https://www.udemy.com/course/1z0-071-oracle-master-silver-sql6277/)

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




# ややこしいもの　シリーズ


#### 参照キーは、「参照されるキー」のこと。　
能動的に参照「する」のは外部キー。
親表→参照キー
子表→外部キー



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

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





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




#### Oracleでは空文字(長さ０の文字列)をNULLとして扱う。
また、NULLを検索するにはNULL条件を使用する。

::: note info 
因みに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`コマンドで削除したデータを戻せる可能性がある
+ データと定義は削除される
+ 表に定義されているトリガーもオブジェクト権限も削除される
+ ビューとシノニムは無効になる
::: note warn
(←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




+ 自然結合
二つの表に同名で異なるデータがある場合エラー。
結合列に表接頭辞をセットしている選択肢は不正解　
:::note warn
自然結合では表名しかSQLに指定しないので、結合列は自分で問題文から探す必要がある
:::
:::note info
因みに、問題文に「二つの表に共通する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を表示。

+ 表にのみマルチインサートできる。
  ビューやリモート表には不可。
:::note info
ただし、SQL＊Loaderを使用して外部表にマルチインサートできる。
:::
+ `NOT NULL制約`だけ
制約の追加のSQLは　`ALTER TABLE CONSTRAINT id_pk PRIMARY KEY (id)`
だが、NOT NULL制約だけ`MODIFY`句で追加



+ 暗黙のデータ変換　文字型⇆数値型、文字型⇆日付型は変換可能だが、　数値型⇆日付型は変換できない。
:::note warn 
文字列連結するCONCAT関数の引数に　数値型と日付型が入っていても、
`CONCAT(salary, hiredate)`
エラーにならずどちらも文字列型に変換して連結するため正常に表示される。
:::

+ 日付書式の`MM`'22-05-31'がMONに対応していないので明示的な型変換が必要
他の書式、例えばMON(MAR)は長い月名（MARCH）に対応しているし、RRは4桁の年指定に対応しているのに。。。
:::note info
因みに
`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`は実行できる。


また、

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

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


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

:::note info
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;`　　　　　←失敗

因みに

:::note info
システム権限は複数(英単語二つ以上　`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；`　はできる。すでに未使用になっている列は実は削除できる。(読み取る事がそもそもできないので読み取り専用モードと関係がないという理解)
+ また、制約の追加も可能。




#### どっちが親表(参照キー)でどっちが子表(外部キー)かはっきりしないならアヒルの親子で理解する
![](https://lh3.googleusercontent.com/pw/AL9nZEVU-xZ8jua6NqQDxOPvo6kz79SAW2EL-iPxX8Z1dIIa1t0FhCX-1BBN2f3h0yxfMio7y5jrnzsZO9XYflQT9R_uxIpN-wf8HyNaxuL49bMztn7OBs8LP6fE6j7ZYzFqiefZEN9L97QLGbXUNAsmmpN-=w988-h724-no?authuser=0)

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

:::note warn
因みにこれらの`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`句で指定する際に元の名前を使用したらエラー。列別名を使用しなければならない。

:::note warn
（※因みに通常の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,'進行中'`
:::note warn 
単純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