rink_rewさんの投稿一覧
ping-tさん提供の検証用データに基づいて、順をおって動きを見てみるとこんな感じでしょうか。
SELECT e.employee_name FROM employees e WHERE EXISTS (SELECT * FROM employees m WHERE e.manager_id = m.employee_id);
- employees e の1行を取得
EMPLOYEE_ID EMPLOYEE_NAME MANAGER_ID
----------- ------------------------------ ----------
1001 山田二郎
- 副問合せの条件は「e.manager_id = m.employee_id」なので、副問合せで参照している employees m 表から、m.employee_id と e.manager_id が等しい行を取得する。
- 上の 1. で employees e から取得した山田二郎の manager_id は null。
- 副問合せで参照している employees m 表の m.employee_id と一致する行がない。
-> 副問合せから行が返されないので、主問合せの EXISTS 条件は FALSE。この行の e.employee_name は返されない。（出力されない）
- employees e の他の1行を取得
EMPLOYEE_ID EMPLOYEE_NAME MANAGER_ID
----------- ------------------------------ ----------
1008 中山大輔 1001
- 副問合せの条件は「e.manager_id = m.employee_id」なので、副問合せで参照している employees m 表から、m.employee_id と e.manager_id が等しい行を取得する。
- 上の 3. で employees e から取得した中山大輔の manager_id は 1001。
- 副問合せで参照している employees m 表の m.employee_id と一致する行(山田二郎)がある。
-> 副問合せから行が返されるので、主問合せの EXISTS 条件は TRUE。この行の e.employee_name は返される。（出力される）
=> 結果として、employees 表 の manager_id 列に、同表内に存在する employee_id の値を持つ行（上司のいる従業員）が返される。
SELECT m.employee_name FROM employees m WHERE EXISTS (SELECT * FROM employees e WHERE e.manager_id = m.employee_id);
- employees m の1行を取得
EMPLOYEE_ID EMPLOYEE_NAME MANAGER_ID
----------- ------------------------------ ----------
1001 山田二郎
- 副問合せの条件は「e.manager_id = m.employee_id」なので、副問合せで参照している employees e 表から、m.employee_id と e.manager_id が等しい行を取得する。
- 上の 1. で employees m から取得した山田二郎の employee_id は 1001。
- 副問合せで参照している employees e 表の e.manager_id と一致する行(中山大輔、米村真司、等)がある
-> 副問合せから行が返されるので、主問合せの EXISTS 条件は TRUE。この行の m.employee_name は返される。（出力される）
- employees e の他の1行を取得
EMPLOYEE_ID EMPLOYEE_NAME MANAGER_ID
----------- ------------------------------ ----------
1008 中山大輔 1001
- 副問合せの条件は「e.manager_id = m.employee_id」なので、副問合せで参照している employees m 表から、m.employee_id と e.manager_id が等しい行を取得する。
- 上の 3. で employees e から取得した中山大輔の employee_id は 1008。
- 副問合せで参照している employees e 表の e.manager_id と一致する行がない。
-> 副問合せから行が返されないので、主問合せの EXISTS 条件は FALSE。この行の m.employee_name は返されない。（出力されない）
=> 結果として、employees 表の manager_id 列に存在する値を、employee_id 列の値として持つ行（部下を持つ従業員（上司））が返される。
セッションというのは各ユーザーがオラクルにアクセスするたびにそれぞれのセッションが作成されるイメージを持っています。←あっているでしょうか？
はい。その認識で良いと思います。
sqlplusなどを使用して対話的にアクセスしている状況ならば「各ユーザ」と呼べますし、開発した複数のアプリケーションからのアクセスであれば「各アプリケーション」とも呼べると思います。
もし、セッションが各ユーザがオラクルにアクセスするたびに作成されるのであれば、上記セッションは終了しておらず他のセッションからTEMP表を参照するとデータが1件表示されるのではないか？
ご認識の通り、セッションは終了していないと考えられます。終了しているのは「トランザクション」のみです。テーブル定義時に「ON COMMIT PRESERVE ROWS」を指定しているので、トランザクション終了後もデータを参照できています。（解説の実行例の通り）
この設問でのポイントは、「一時表のデータはあくまでもセッション内でのみ参照可能である」という点だと思います。他のセッションからは「表の構造」は参照できますが（解説の画像でいうところのDESC）、「データ」は参照できません(解説の画像でいうところのSELECT文の結果)。
一つのコマンドプロンプト（Linuxやmacであればターミナル等）を立ち上げて設問で提示されているSQLを実行後、同じコマンドプロンプト上から（同じセッション内からの意）はCOMMIT後も挿入したデータが参照できます。
もう一つ別のコマンドプロンプトを立ち上げて「temp」表に対してSELECT文を実行しても、データは参照できません。これは、前のコマンドプロンプトにおける最後の「COMMIT;」の前でも後でも同様です。
このことから「他のセッションからTEMP表を参照すると、データは0件である」が正答であると導き出せると思います。
問題文の冒頭に「EMPLOYEE_ID列にはPRIMARY KEY制約だけが定義されています」とあるので、画像の「NOT NULL」は、明示的に付与したNOT NULL制約というわけではなく、PRIMARY KEY制約を付与した際に暗黙的に作成されたものだと判断できると思います。
運営さんに修正してもらうとしたら、明示的か暗黙的かを明記してもらうぐらいでしょうか。
実際の試験において、どの程度明確な問題文が提示されるかはわかりませんが...。
ユーザーAが発行したFOR UPDATE WAIT 10はユーザーAの前の処理に対して働いていると考えますがあっているでしょうか？
そうですね。
①のSELECT文で「FOR UPDATE」を用いてロックを取得しようとしていますが、競合するトランザクションがすでにある場合には待たされます。「WAIT 10」は競合するトランザクションがすでにあった場合に「10秒までは待つ」という意味で、10秒経過するとエラーが返ります。
この設問では、①の前に何かトランザクションがあるか否かといった情報は特に提示されていないので、ロックを取得できると考えられます。
また、解説③にはユーザーAがかけた排他ロックが解除されるまで待機するとありますが、これはユーザーBのSQL文でUPDATE prod SET name = 'Chopin' WHERE prodid = 2 の後に FOR UPDATE WAIT がありませんがデフォルトでそのような挙動になるのでしょうか？
FOR UPDATE句はSELECT文で使用できる句です。UPDATE文ではそもそもロックを取得するので、FOR UPDATE句はありません。（「SELECT ... FOR UPDATE」文としてひと塊りで覚えてしまった方がわかりやすいかもしれません）
参考: DML操作での自動ロック
https://docs.oracle.com/cd/F19136_01/sqlrf/Automatic-Locks-in-DML-Operations.html#GUID-3D57596F-8B73-4C80-8F4D-79A12F781EFD
私は同じ状況になったことはないのですが、試しに今PCからログインしてみましたところ、特に問題なくログインできました。
ログインできないとのことですが、何かエラーなど出ている場合には、Oracle側に問い合わせをしてみた方が早いかもしれませんね。
https://www.oracle.com/jp/education/certification/migration-to-certview.html#Contact
ここの、「オラクル認定資格事務局(oraclecert_jp@oracle.com)」でしょうか。