助け合いフォーラム

Oracle DB

Oracle Master Silver SQL 2019(1Z0-071)

問題ID : 26775

EMPLOYEES表の構造を確認して下さい。

次のSQL文でEMPLOYEES表を実表とするV_EMPビューを作成しました。

　CREATE OR REPLACE VIEW v_emp
　　AS　
　SELECT department_id, employee_id, employee_name, salary
　FROM employees
　WHERE salary > 250000 AND salary < 800000
　　AND department_id = 1
　WITH CHECK OPTION;

V_EMPビューに対して、エラーとならずに実行できるSQL文はどれですか(該当するものを全て選択して下さい)。

INSERT INTO v_emp VALUES (1, 10, 'Tanaka', 600000);

UPDATE v_emp SET salary = 1000000 WHERE department_id = 1;

INSERT INTO v_emp VALUES (1, NULL, 'Tanaka', 40000);

INSERT INTO v_emp VALUES (1, 10, 'Tanaka', 150000);

UPDATE v_emp SET salary = 500000 WHERE department_id = 3;

正解

INSERT INTO v_emp VALUES (1, 10, 'Tanaka', 600000);

UPDATE v_emp SET salary = 500000 WHERE department_id = 3;

解説

ビューの作成時、WITH CHECK OPTIONオプションを指定すると、ビューを通じて実表のデータを操作する場合に、ビューの定義時に指定したWHERE句の条件を満たしていないデータの追加や更新ができなくなります。

V_EMPビューは、
・SALARY列の値が250,000より大きく800,000より小さい
・DEPARTMENT_ID列の値が1
という条件が指定されているので、この条件を満たすデータの追加、更新のみ行うことができます。

以上より、
・INSERT INTO v_emp VALUES (1, 10, 'Tanaka', 600000);
・UPDATE v_emp SET salary = 500000 WHERE department_id = 3;
が正解となります。

後者のSQL文は、WHERE句でdepartment_id = 3という条件が指定されていますが、更新する値(salary = 500000)自体はビューで定義された条件を満たしているためエラーとはなりませんので注意しましょう。
ただし、V_EMPビューではDEPARTMENT_ID列が1のデータにしかアクセス出来ないため、データの更新は行われません。

正解のSQL文の実行結果は次のようになります。

SQLを表示

CREATE OR REPLACE VIEW v_emp
　AS　
SELECT department_id, employee_id, employee_name, salary
FROM employees
WHERE salary > 250000 AND salary < 800000
　AND department_id = 1
WITH CHECK OPTION;

INSERT INTO v_emp VALUES (1, 10, 'Tanaka', 600000);

SQLを表示

UPDATE v_emp SET salary = 500000
WHERE department_id = 3;

その他の選択肢については次のとおりです。

・UPDATE v_emp SET salary = 1000000 WHERE department_id = 1;
・INSERT INTO v_emp VALUES (1, 10, 'Tanaka', 150000);
SALARY列の値がビューの定義を満たしていないため、エラーとなります。

・INSERT INTO v_emp VALUES (1, NULL, 'Tanaka', 40000);
EMPLOYEES表のEMPLOYEE_ID列にはNOT NULL制約が定義されているため、NULL値を登録することはできません。

参考

ビューの作成はCREATE VIEW権限を持つユーザーによって行われます。

　CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW ビュー名 [(別名 [,別名...])]
　　AS
　副問合せ
　[WITH CHECK OPTION [CONSTRAINT 制約名]]
　[WITH READ ONLY [CONSTRAINT 制約名]];

　

SQLを表示

CREATE VIEW v_emp
　AS
SELECT employee_id, employee_name, hiredate, department_id
FROM employees
WHERE salary <= 450000;

SELECT * FROM v_emp;

CREATE VIEW文のオプションは次の通りです。

<img src="/mondai3/img/jpg/kkk49137.jpg">

[FORCE/NOFORCE]
CREATE VIEW文でビューを作成する際に、FORCEオプションを指定すると、実表の有無にかかわらずビューを作成することができます。
メンテナンスや検証時に、一時的に実表が存在しないがビューを作成しておきたい場合などに利用します。

SQLを表示

SELECT * FROM emp3;

CREATE FORCE VIEW view1
　AS　
SELECT employee_id, employee_name, salary
FROM emp3;

ただし、ビューの参照時に実表が存在しない場合はエラーとなります。

<img src="/mondai3/img/jpg/kk49149.jpg">

SQLを表示

SELECT * FROM view1;

NOFORCEオプションを指定すると、実表がある場合のみビューが作成され、実表がない場合はエラーとなります。

<img src="/mondai3/img/jpg/kkk49149.jpg">

SQLを表示

SELECT * FROM emp3;

CREATE NOFORCE VIEW view1
AS
SELECT employee_id, employee_name, salary
FROM emp3;

なお、CREATE VIEW文ではNOFORCEがデフォルトとなりますので、FORCE、NOFORCEどちらも指定しない場合は、実表がある場合のみビューを作成することができます。

[WITH CHECK OPTION]
ビューの作成時、WITH CHECK OPTIONオプションを指定すると、ビューを通じて実表のデータを操作する場合に、ビューの定義時に指定したWHERE句の条件を満たしていないデータの追加や更新ができなくなります。

SQLを表示

CREATE VIEW v_emp
　AS
SELECT employee_name, salary
FROM employees
WHERE department_id = 5
　AND salary >= 400000
WITH CHECK OPTION;

UPDATE v_emp SET salary = 300000;

UPDATE v_emp SET salary = 600000;

CREATE VIEW文でビューを作成する場合は、次の点に注意が必要です。

・副問合せのSELECT句に計算式や関数を指定する場合は、CREATE VIEW文で別名を指定するか、SELECT句の計算式や関数に列別名を指定する

<img src="/mondai3/img/jpg/kkkkk49137.jpg">

SQLを表示

CREATE VIEW v_emp
　AS
SELECT employee_id, employee_name, salary*12, hiredate
FROM employees;

・ビューの列名を定義する場合は、副問合せのSELECT句に指定する列の数と同数の列名を定義する

<img src="/mondai3/img/jpg/kkkkkk49137.jpg">

SQLを表示

CREATE VIEW v_emp (id, name, sal, hireadte)
　AS
SELECT * FROM employees
WHERE salary >= 500000;

なお、ビューの構造は表と同様にDESCコマンドで確認することができます。

<img src="/mondai3/img/jpg/kkkk49137.jpg">

SQLを表示

DESC v_emp;

参考URL

Oracle® Database SQL言語リファレンス「CREATE VIEW」: WITH CHECK OPTION

上に戻る

WITH CHECK OPTION

r roki0094

投稿日 2024/01/03

解説より、

V_EMPビューは、
・SALARY列の値が250,000より大きく800,000より小さい
・DEPARTMENT_ID列の値が1
という条件が指定されているので、この条件を満たすデータの追加、更新のみ行うことができます。

とありますが、以下が正解になっています。depaerment_id = 3 なので更新できないのでは？解説と回答がアンマッチだと思います。

UPDATE v_emp SET salary = 500000 WHERE department_id = 3;

r rink_rew

2024/01/06 11:36

過去にも同じ質問があったようです。
https://mondai.ping-t.com/g/posts/603

INSERT時は範囲外の値をセットすると「範囲外の値は入力できない（エラー）」となりますが、UPDATE時は更新指示自体がエラーになるのではなく「処理を実行した結果0件でした」となるのだよ、という違いですね。_

ちなみに、ping-tさん提供のテストデータで実際に実行してみた結果は以下のとおりです。

SQL> select * from v_emp;

DEPARTMENT_ID EMPLOYEE_ID EMPLOYEE_NAME 		     SALARY
------------- ----------- ------------------------------ ----------
            1        1001 山田二郎                           500000
            1        1008 中山大輔                           400000
            1        1013 米村真司                           350000

SQL> UPDATE v_emp SET salary = 500000 WHERE department_id = 3;

0行が更新されました。

SQL> select * from v_emp;

DEPARTMENT_ID EMPLOYEE_ID EMPLOYEE_NAME 		     SALARY
------------- ----------- ------------------------------ ----------
            1        1001 山田二郎                           500000
            1        1008 中山大輔                           400000
            1        1013 米村真司                           350000

SQL>

r roki0094

2024/01/07 20:26

解説ありがとうございます。理解できました！また、過去同様の質問があった事に気づけず失礼致しました。

この投稿に対して返信しませんか？

助け合いフォーラム