助け合いフォーラム
Oracle Master Silver SQL 2019(1Z0-071)
問題ID : 26794
問題を開く
EMPLOYEES表の構造を確認して下さい。
EMPLOYEES表を基に次の3つのビューを作成しました。ただし、INSTEAD OFトリガーは定義していないものとします。
ビューと実行可能な操作の組合せとして正しいものはどれですか(該当するものを2つ選択してください)。
ビュー1)
CREATE OR REPLACE VIEW v_emp1
AS
SELECT employee_id, employee_name, salary * 12 sal
FROM employees;
ビュー2)
CREATE VIEW v_emp2
AS
SELECT department_id, AVG(salary) avgsal
FROM employees
WHERE salary > 600000
GROUP BY department_id;
ビュー3)
CREATE VIEW v_emp3
AS
SELECT employee_name, salary
FROM employees;
EMPLOYEES表を基に次の3つのビューを作成しました。ただし、INSTEAD OFトリガーは定義していないものとします。
ビューと実行可能な操作の組合せとして正しいものはどれですか(該当するものを2つ選択してください)。
ビュー1)
CREATE OR REPLACE VIEW v_emp1
AS
SELECT employee_id, employee_name, salary * 12 sal
FROM employees;
ビュー2)
CREATE VIEW v_emp2
AS
SELECT department_id, AVG(salary) avgsal
FROM employees
WHERE salary > 600000
GROUP BY department_id;
ビュー3)
CREATE VIEW v_emp3
AS
SELECT employee_name, salary
FROM employees;
正解
ビュー1を通じて、EMPLOYEES表のデータの削除ができる
ビュー3を通じて、EMPLOYEES表のデータの更新ができる
解説
作成したビューを通じて実表のデータを操作することができます。
ただし、表に直接アクセスする場合とは異なり、ビューを通じての実表のデータの操作には、ビューの定義によっていろいろな制限があります。
ビューを通じて実表のデータを操作することができる基本的なルールは次の通りです。
設問のビュー1はビューの定義に式によって定義された列が含まれています。データの削除を行うことができます。
ビュー2はビューの定義にGROUP BY句とグループ関数が使用されているため、データの追加、更新、削除の全てを行うことができません。
ビュー3はNOT NULL制約が定義されているEMPLOYEE_ID列がビューの定義に含まれていません。データの更新を行うことができます。
以上より、
・ビュー1を通じて、EMPLOYEES表のデータの削除ができる
・ビュー3を通じて、EMPLOYEES表のデータの更新ができる
が正解となります。
設問のSQL文の実行結果は次のようになります。
[ビュー1]
[ビュー2]
[ビュー3]
ただし、表に直接アクセスする場合とは異なり、ビューを通じての実表のデータの操作には、ビューの定義によっていろいろな制限があります。
ビューを通じて実表のデータを操作することができる基本的なルールは次の通りです。
設問のビュー1はビューの定義に式によって定義された列が含まれています。データの削除を行うことができます。
ビュー2はビューの定義にGROUP BY句とグループ関数が使用されているため、データの追加、更新、削除の全てを行うことができません。
ビュー3はNOT NULL制約が定義されているEMPLOYEE_ID列がビューの定義に含まれていません。データの更新を行うことができます。
以上より、
・ビュー1を通じて、EMPLOYEES表のデータの削除ができる
・ビュー3を通じて、EMPLOYEES表のデータの更新ができる
が正解となります。
設問のSQL文の実行結果は次のようになります。
[ビュー1]
SQLを表示
CREATE OR REPLACE VIEW v_emp1
AS
SELECT employee_id, employee_name, salary * 12 sal
FROM employees;
INSERT INTO v_emp1
VALUES (2000, 'Tanaka', 500000);
UPDATE v_emp1 SET sal = 500000;
DELETE v_emp1 WHERE sal > 5000000;
AS
SELECT employee_id, employee_name, salary * 12 sal
FROM employees;
INSERT INTO v_emp1
VALUES (2000, 'Tanaka', 500000);
UPDATE v_emp1 SET sal = 500000;
DELETE v_emp1 WHERE sal > 5000000;
[ビュー2]
SQLを表示
CREATE VIEW v_emp2
AS
SELECT department_id, AVG(salary) avgsal
FROM employees
WHERE salary > 600000
GROUP BY department_id;
INSERT INTO v_emp2
VALUES (6, 500000);
UPDATE v_emp2 SET department_id = 6;
DELETE v_emp2 WHERE department_id = 5;
AS
SELECT department_id, AVG(salary) avgsal
FROM employees
WHERE salary > 600000
GROUP BY department_id;
INSERT INTO v_emp2
VALUES (6, 500000);
UPDATE v_emp2 SET department_id = 6;
DELETE v_emp2 WHERE department_id = 5;
[ビュー3]
SQLを表示
CREATE VIEW v_emp3
AS
SELECT employee_name, salary
FROM employees;
INSERT INTO v_emp3
VALUES ('Tanaka', 400000);
UPDATE v_emp3 SET salary = 250000;
DELETE v_emp3 WHERE salary = 250000;
AS
SELECT employee_name, salary
FROM employees;
INSERT INTO v_emp3
VALUES ('Tanaka', 400000);
UPDATE v_emp3 SET salary = 250000;
DELETE v_emp3 WHERE salary = 250000;
参考
作成したビューを通じて実表のデータを操作することができます。
ただし、表に直接アクセスする場合とは異なり、ビューを通じての実表のデータの操作には、ビューの定義によっていろいろな制限があります。
ビューを通じて実表のデータを操作することができる基本的なルールは次の通りです。
[ビューを通じてのデータの追加]
ビューを通じて実表のデータを追加する場合には、ビューの定義に次の要素が含まれていないことが条件になります。
・GROUP BY句
・グループ関数
・ROWNUM擬似列
・DISTINCTキーワード
・式によって定義された列 ※
・ビューに含まれない実表の列に定義されたNOT NULL制約 ※
ただし、NOT NULL制約が定義されている列に、デフォルト値が設定されていれば、ビューを通じてデータを追加することができます。
[ビューを通じてのデータの更新]
ビューを通じて実表のデータを更新する場合には、ビューの定義に次の要素が含まれていないことが条件になります。
・GROUP BY句
・グループ関数
・ROWNUM擬似列
・DISTINCTキーワード
・式によって定義された列 ※
ただし、ビューの定義に式を含む列が定義されている場合でも、対象列以外はデータの追加、更新を行うことができます。
[ビューを通じてのデータの削除]
ビューを通じて実表のデータを削除する場合には、ビューの定義に次の要素が含まれていないことが条件になります。
・GROUP BY句
・グループ関数
・ROWNUM擬似列
・DISTINCTキーワード
なお、実表に制約が定義されている場合は、ビューを通したデータの操作時にも制約のチェックが行われます。制約を満たさないデータの操作はできません。
【INSTEAD OFトリガー】
INSTEAD OFトリガーは、上記のようにDML文（INSERT、UPDATE、DELETE）を実行できないビューに対してDMLを実行できるようにするトリガーです。トリガーとは指定した条件の操作が行われた際に起動するプログラムです。Oracle独自のプログラミング言語であるPL/SQLで記述します。
ここでは、前述の[ビューを通じてのデータの削除]で作成したDISTINCTキーワードを定義に含むV_PRODビューに対してDELETE文が発行された時に起動するINSTEAD OFトリガーの例を記載します。
本来はビューの定義にDISTINCTキーワードが含まれている場合はビューを通じて実表のデータを削除することはできません。しかし、V_PRODビューにDELETE文が発行されると代わりに（INSTEAD OF）起動するINSTEAD OFトリガーを作成すると、実表に対してDELETE文が実行できるようになります。
ただし、表に直接アクセスする場合とは異なり、ビューを通じての実表のデータの操作には、ビューの定義によっていろいろな制限があります。
ビューを通じて実表のデータを操作することができる基本的なルールは次の通りです。
[ビューを通じてのデータの追加]
ビューを通じて実表のデータを追加する場合には、ビューの定義に次の要素が含まれていないことが条件になります。
・GROUP BY句
・グループ関数
・ROWNUM擬似列
・DISTINCTキーワード
・式によって定義された列 ※
・ビューに含まれない実表の列に定義されたNOT NULL制約 ※
SQLを表示
CREATE VIEW v_prod
AS
SELECT prodid, category, name
FROM prod
WHERE category < 50;
INSERT INTO v_prod
VALUES (50, 60, 'MP3 Player');
SELECT prodid, category, name
FROM prod;
AS
SELECT prodid, category, name
FROM prod
WHERE category < 50;
INSERT INTO v_prod
VALUES (50, 60, 'MP3 Player');
SELECT prodid, category, name
FROM prod;
SQLを表示
DESC prod;
CREATE VIEW v_prod
AS
SELECT category, name
FROM prod
WHERE category < 50;
INSERT INTO v_prod
VALUES (70, 'HeadPhone');
CREATE VIEW v_prod
AS
SELECT category, name
FROM prod
WHERE category < 50;
INSERT INTO v_prod
VALUES (70, 'HeadPhone');
ただし、NOT NULL制約が定義されている列に、デフォルト値が設定されていれば、ビューを通じてデータを追加することができます。
SQLを表示
CREATE TABLE table2
(id NUMBER(2),
name VARCHAR2(10),
birth DATE DEFAULT SYSDATE NOT NULL
);
CREATE VIEW view2
AS
SELECT id, name FROM table2;
INSERT INTO view2
VALUES (1, 'Tanaka');
(id NUMBER(2),
name VARCHAR2(10),
birth DATE DEFAULT SYSDATE NOT NULL
);
CREATE VIEW view2
AS
SELECT id, name FROM table2;
INSERT INTO view2
VALUES (1, 'Tanaka');
[ビューを通じてのデータの更新]
ビューを通じて実表のデータを更新する場合には、ビューの定義に次の要素が含まれていないことが条件になります。
・GROUP BY句
・グループ関数
・ROWNUM擬似列
・DISTINCTキーワード
・式によって定義された列 ※
SQLを表示
CREATE VIEW v_prod
AS
SELECT category, name
FROM prod;
SELECT * FROM v_prod;
UPDATE v_prod SET name = 'Chopin'
WHERE category = 10;
SELECT prodid, category, name
FROM prod;
AS
SELECT category, name
FROM prod;
SELECT * FROM v_prod;
UPDATE v_prod SET name = 'Chopin'
WHERE category = 10;
SELECT prodid, category, name
FROM prod;
SQLを表示
CREATE VIEW v_prod
AS
SELECT category, MAX(name) name
FROM prod
GROUP BY category;
UPDATE v_prod SET name = 'Chopin'
WHERE category = 10;
AS
SELECT category, MAX(name) name
FROM prod
GROUP BY category;
UPDATE v_prod SET name = 'Chopin'
WHERE category = 10;
ただし、ビューの定義に式を含む列が定義されている場合でも、対象列以外はデータの追加、更新を行うことができます。
SQLを表示
DESC employees;
CREATE VIEW v_emp
AS
SELECT employee_id, employee_name, salary * 12 salary
FROM employees;
INSERT INTO v_emp
VALUES (1000, 'Tanaka', 8000000);
INSERT INTO v_emp (employee_id, employee_name)
VALUES (1000, 'Tanaka');
UPDATE v_emp SET salary = 0;
UPDATE v_emp SET employee_name = 'Yamada'
WHERE employee_id = 1000;
CREATE VIEW v_emp
AS
SELECT employee_id, employee_name, salary * 12 salary
FROM employees;
INSERT INTO v_emp
VALUES (1000, 'Tanaka', 8000000);
INSERT INTO v_emp (employee_id, employee_name)
VALUES (1000, 'Tanaka');
UPDATE v_emp SET salary = 0;
UPDATE v_emp SET employee_name = 'Yamada'
WHERE employee_id = 1000;
[ビューを通じてのデータの削除]
ビューを通じて実表のデータを削除する場合には、ビューの定義に次の要素が含まれていないことが条件になります。
・GROUP BY句
・グループ関数
・ROWNUM擬似列
・DISTINCTキーワード
SQLを表示
CREATE VIEW v_prod
AS
SELECT category, name
FROM prod;
SELECT * FROM v_prod;
DELETE FROM v_prod
WHERE category = 50;
AS
SELECT category, name
FROM prod;
SELECT * FROM v_prod;
DELETE FROM v_prod
WHERE category = 50;
SQLを表示
CREATE VIEW v_prod
AS
SELECT DISTINCT category, name
FROM prod;
SELECT * FROM v_prod;
DELETE FROM v_prod
WHERE category = 40;
AS
SELECT DISTINCT category, name
FROM prod;
SELECT * FROM v_prod;
DELETE FROM v_prod
WHERE category = 40;
なお、実表に制約が定義されている場合は、ビューを通したデータの操作時にも制約のチェックが行われます。制約を満たさないデータの操作はできません。
【INSTEAD OFトリガー】
INSTEAD OFトリガーは、上記のようにDML文（INSERT、UPDATE、DELETE）を実行できないビューに対してDMLを実行できるようにするトリガーです。トリガーとは指定した条件の操作が行われた際に起動するプログラムです。Oracle独自のプログラミング言語であるPL/SQLで記述します。
ここでは、前述の[ビューを通じてのデータの削除]で作成したDISTINCTキーワードを定義に含むV_PRODビューに対してDELETE文が発行された時に起動するINSTEAD OFトリガーの例を記載します。
本来はビューの定義にDISTINCTキーワードが含まれている場合はビューを通じて実表のデータを削除することはできません。しかし、V_PRODビューにDELETE文が発行されると代わりに（INSTEAD OF）起動するINSTEAD OFトリガーを作成すると、実表に対してDELETE文が実行できるようになります。
SQLを表示
CREATE VIEW v_prod
AS
SELECT DISTINCT category, name
FROM prod;
CREATE OR REPLACE TRIGGER delete_v_prod
INSTEAD OF DELETE ON v_prod
BEGIN
DELETE prod WHERE category = :OLD.category;
END;
/
DELETE FROM v_prod
WHERE category = 40;
SELECT * FROM prod;
AS
SELECT DISTINCT category, name
FROM prod;
CREATE OR REPLACE TRIGGER delete_v_prod
INSTEAD OF DELETE ON v_prod
BEGIN
DELETE prod WHERE category = :OLD.category;
END;
/
DELETE FROM v_prod
WHERE category = 40;
SELECT * FROM prod;
ビュー1は追加可能ではないでしょうか
投稿日 2023/07/09
ビュー1を通じて、EMPLOYEES表へのデータの追加ができる
は、計算式を含む列以外の指定であればデータの追加は可能なのではないでしょうか。
スタッフからの返信
この投稿に対して返信しませんか？
s staff_ishii
2023/07/11 17:50
shuichi0705 さん ご指摘の点を修正いたしました。 ご報告、誠にありがとうございました。