助け合いフォーラム
Oracle Master Silver SQL 2019(1Z0-071)
問題ID : 26884
問題を開く
DEPARTMENTS表とEMPLOYEES表の構造を確認して下さい。
以下の順番でSQL文を実行した後の説明として、正しいものはどれですか。
① CREATE OR REPLACE VIEW v_emp
AS
SELECT department_id, AVG(salary) avg_sal
FROM employees
GROUP BY department_id;
② CREATE TABLE dep_salary
(department_id NUMBER(2),
department_name VARCHAR2(14),
avarage_salary NUMBER(7));
③ INSERT INTO dep_salary
SELECT department_id, department_name, avg_sal
FROM v_emp JOIN departments
USING (department_id);
以下の順番でSQL文を実行した後の説明として、正しいものはどれですか。
① CREATE OR REPLACE VIEW v_emp
AS
SELECT department_id, AVG(salary) avg_sal
FROM employees
GROUP BY department_id;
② CREATE TABLE dep_salary
(department_id NUMBER(2),
department_name VARCHAR2(14),
avarage_salary NUMBER(7));
③ INSERT INTO dep_salary
SELECT department_id, department_name, avg_sal
FROM v_emp JOIN departments
USING (department_id);
正解
正しく実行できる
解説
INSERT文に副問合せを使用してデータの挿入を行えます。
INSERT INTO 表名1 [(列名 [, 列名...])]
(SELECT 列名 [, 列名...] FROM 表名2 [WHERE 条件]);
※副問合せの部分を囲む()は必須ではありません。
設問の①では、EMPLOYEES表から部署ごとの平均給与を取り出した複合ビューV_EMPを作成しています。
②では、CREATE TABLE文に記述した構造を持つDEP_SALARY表を作成しています。
③では、副問合せでV_EMPビューとDEPARTMENTS表を結合して部署ID、部署名、平均給与を取り出し、DEP_SALARY表に挿入しています。
INSERT句の列のリストを省略した場合は、副問合せのSELECT句に指定する列のリストは同数かつ同じ順番で指定する必要がありますが、③の文は問題ありません。
V_EMPではavg_sal列、DEP_SALARY表ではavarage_salary列と、平均給与の列名が違いますが、データ型や桁数は同じのため問題ありません。
以上より、
・正しく実行できる
が正解となります。
設問のSQL文を実行した結果は次のようになります。
その他の選択肢については次のとおりです。
・INSERT文に副問合せを使用できないので③でエラーとなる
INSERT文に副問合せを使用できます。
・ビューと表は結合できないので③でエラーとなる
ビューと表は結合できます。
・③の副問合せの列名とDEP_SALARY表の列名が違うのでエラーとなる
列名が違っていても、データ型や列のリストの数や順番が同じであれば問題ありません。
INSERT INTO 表名1 [(列名 [, 列名...])]
(SELECT 列名 [, 列名...] FROM 表名2 [WHERE 条件]);
※副問合せの部分を囲む()は必須ではありません。
設問の①では、EMPLOYEES表から部署ごとの平均給与を取り出した複合ビューV_EMPを作成しています。
②では、CREATE TABLE文に記述した構造を持つDEP_SALARY表を作成しています。
③では、副問合せでV_EMPビューとDEPARTMENTS表を結合して部署ID、部署名、平均給与を取り出し、DEP_SALARY表に挿入しています。
INSERT句の列のリストを省略した場合は、副問合せのSELECT句に指定する列のリストは同数かつ同じ順番で指定する必要がありますが、③の文は問題ありません。
V_EMPではavg_sal列、DEP_SALARY表ではavarage_salary列と、平均給与の列名が違いますが、データ型や桁数は同じのため問題ありません。
以上より、
・正しく実行できる
が正解となります。
設問のSQL文を実行した結果は次のようになります。
その他の選択肢については次のとおりです。
・INSERT文に副問合せを使用できないので③でエラーとなる
INSERT文に副問合せを使用できます。
・ビューと表は結合できないので③でエラーとなる
ビューと表は結合できます。
・③の副問合せの列名とDEP_SALARY表の列名が違うのでエラーとなる
列名が違っていても、データ型や列のリストの数や順番が同じであれば問題ありません。
参考
表にデータを追加するにはINSERT文を使用します。
INSERT INTO 表名[(列名1 [, 列名2 ...])] VALUES (値1 [, 値2 ...]);
列名と値は1対1で指定します。VALUES句の値1が列名1の列に格納されます。指定した列名の個数と値の個数が一致しない場合はエラーとなります。また、追加する値は列に定義されているデータ型と一致していなければなりません。
なお、列名を省略する場合は、表に定義されている全ての列の値を指定しなければならず、省略はできません。VALUES句には、表内の列の構成と同じ順序で値を指定します。
[列名を明示的に指定]
列名を指定する場合は、指定する列の個数とVALUES句に指定する値の個数を同数にしなければなりません。また、表に定義されていない列名を指定するとエラーとなります。
列名を明示的に指定する場合、列名の並びは任意です(表内の列の構成と同じ並びにする必要はありません)。
また、NOT NULL制約が定義されている列以外の列名は省略できます。省略された列の値にはデフォルトでNULL値が入ります。
[列名の指定を省略]
列名の指定を省略した場合は、表内の列の構成と同じ順序で値を指定しなければなりません。表内の列の構成はDESCコマンドで確認できます。
また、副問合せを使用してデータの追加も行えます。
副問合せを使用してデータを追加するには、次のように記述します。
INSERT INTO 表名1 [(列名 [, 列名...])]
(SELECT 列名 [, 列名...] FROM 表名2 [WHERE 条件]);
なお、副問合せの部分を囲む()は必須ではありません。以下の実行例では省略しています。
副問合せを使用したINSERT文では、VALUES句は使用できません。
※1つの列の1行のみ返す副問合せは、INSERT文のVALUES句の中でも使用できます。この後に解説します。
INSERT句に指定する列のリストと、副問合せのSELECT句に指定する列のリストは同数かつ同じ順番で指定します。
また、INSERT句の列のリストは省略可能ですが、省略する場合、副問合せのSELECT句の列のリストには、データを追加する表のすべての列を表の列構成の順番で指定しなければなりません(表の列構成はDESCコマンドで確認できます)。
1つの列の1行のみ返す副問合せであれば、INSERT文のVALUES句の中でも使用できます。
次のSQL文では2列目と3列目にそれぞれ1つの値を返す副問合せを使用しているため正常に実行できます。副問合せが複数の行や列を返す場合はエラーとなります。
INSERT INTO 表名[(列名1 [, 列名2 ...])] VALUES (値1 [, 値2 ...]);
SQLを表示
INSERT INTO prod(prodid, name, category, startdate, enddate)
VALUES (20, 'SuperMan', 20, '2001-02-15', '2010-10-30');
VALUES (20, 'SuperMan', 20, '2001-02-15', '2010-10-30');
列名と値は1対1で指定します。VALUES句の値1が列名1の列に格納されます。指定した列名の個数と値の個数が一致しない場合はエラーとなります。また、追加する値は列に定義されているデータ型と一致していなければなりません。
なお、列名を省略する場合は、表に定義されている全ての列の値を指定しなければならず、省略はできません。VALUES句には、表内の列の構成と同じ順序で値を指定します。
[列名を明示的に指定]
列名を指定する場合は、指定する列の個数とVALUES句に指定する値の個数を同数にしなければなりません。また、表に定義されていない列名を指定するとエラーとなります。
SQLを表示
INSERT INTO prod(prodid, name, category, startdate, enddate)
VALUES (20, 'SuperMan', 20, '2001-02-15', '2010-10-30');
VALUES (20, 'SuperMan', 20, '2001-02-15', '2010-10-30');
列名を明示的に指定する場合、列名の並びは任意です(表内の列の構成と同じ並びにする必要はありません)。
また、NOT NULL制約が定義されている列以外の列名は省略できます。省略された列の値にはデフォルトでNULL値が入ります。
SQLを表示
INSERT INTO prod(category, name, prodid)
VALUES (20, 'SuperMan', 20);
VALUES (20, 'SuperMan', 20);
[列名の指定を省略]
列名の指定を省略した場合は、表内の列の構成と同じ順序で値を指定しなければなりません。表内の列の構成はDESCコマンドで確認できます。
SQLを表示
DESC prod;
SQLを表示
INSERT INTO prod
VALUES (20, 'SuperMan', 20, '2001-02-15', '2010-10-30');
VALUES (20, 'SuperMan', 20, '2001-02-15', '2010-10-30');
また、副問合せを使用してデータの追加も行えます。
副問合せを使用してデータを追加するには、次のように記述します。
INSERT INTO 表名1 [(列名 [, 列名...])]
(SELECT 列名 [, 列名...] FROM 表名2 [WHERE 条件]);
なお、副問合せの部分を囲む()は必須ではありません。以下の実行例では省略しています。
SQLを表示
INSERT INTO prod2 (prodid, name, category, startdate, enddate)
SELECT prodid, name, category, startdate, enddate
FROM prod
WHERE category < 50;
SELECT prodid, name, category, startdate, enddate
FROM prod
WHERE category < 50;
副問合せを使用したINSERT文では、VALUES句は使用できません。
※1つの列の1行のみ返す副問合せは、INSERT文のVALUES句の中でも使用できます。この後に解説します。
INSERT句に指定する列のリストと、副問合せのSELECT句に指定する列のリストは同数かつ同じ順番で指定します。
また、INSERT句の列のリストは省略可能ですが、省略する場合、副問合せのSELECT句の列のリストには、データを追加する表のすべての列を表の列構成の順番で指定しなければなりません(表の列構成はDESCコマンドで確認できます)。
SQLを表示
DESC prod2;
SQLを表示
INSERT INTO prod2
SELECT name, category, prodid, startdate, enddate
FROM prod;
SELECT name, category, prodid, startdate, enddate
FROM prod;
1つの列の1行のみ返す副問合せであれば、INSERT文のVALUES句の中でも使用できます。
次のSQL文では2列目と3列目にそれぞれ1つの値を返す副問合せを使用しているため正常に実行できます。副問合せが複数の行や列を返す場合はエラーとなります。
SQLを表示
INSERT INTO prod2
VALUES (1, (SELECT name FROM prod WHERE prodid = 1),
(SELECT category FROM prod WHERE prodid = 1), SYSDATE, NULL);
VALUES (1, (SELECT name FROM prod WHERE prodid = 1),
(SELECT category FROM prod WHERE prodid = 1), SYSDATE, NULL);
③の接頭句について
s
sako39
投稿日 2024/10/03
③のSELECT文INSERT後のSELECT文で接頭句がないので、
department_idの指定でv_empからとるのかdepartmentsからとるのかが
わからないです。
接頭句なしでエラーになるような気がして・・・
ご教示お願いできますでしょうか。
r
rink_rew
2024/10/04 15:59
③の副問合せのSELECT文はUSING句を使用した結合を行ってます。USINGはそもそも2つの表に共通する列（同じ名前の列）を結合条件として使用するので、接頭辞をつける必要はありません。副問合せの出力結果は以下のとおりとなり、問題なくINSERTに使用できます。
SQL> SELECT department_id, department_name, avg_sal FROM v_emp JOIN departments USING (department_id);
DEPARTMENT_ID DEPARTMENT_NAME AVG_SAL
------------- ------------------------------ ----------
1 総務 431250
2 営業 400000
3 開発 400000
4 マーケティング 366666.667
5 品質管理 450000
コメント
この投稿に対して返信しませんか？
s sako39
2024/10/07 08:54
回答ありがとうございます。 もし別名でv_empに「ve」、departmentsに「dp」とつけた場合に、 以下のようなSQLは動作する認識であっていますでしょうか。 SELECT department_id, dp.department_name, ve.avg_sal FROM v_emp ve JOIN departments dp USING (department_id); USINGの結合条件では、接頭辞の指定はできないと思うのですが、問い合わせ結果のdepartment_idに接頭辞をつけたらエラーになるのでしょうか。