Oracle Database SQL (1Z0-071) サンプル問題の解答と解説

Oracle Database SQL (1Z0-071-JPN) サンプル問題

【第1問】

次のビジネス・ルールを確認してください。

各従業員は必ず一つの部署に配属されます。また、各部署には、複数の従業員が配属されます。最適なERモデルを設計し、次の形式でレポートを作成できるようにします。

EMPLOYEE_ID FIRST_NAME DEPARTMENT_ID DEPARTMENT_NAME

正しい記述はどれですか。

選択肢

1. ERには、EMPLOYEESエンティティとDEPARTMENTSエンティティの間に1対多の関係が必要である。

2. ERには、EMPLOYEESエンティティとDEPARTMENTSエンティティの間に多対多の関係があり、この関係を1対多の関係に分解する必要がある。

3. EMPLOYEE_IDはDEPARTMENTSエンティティの外部キーである。

4. DEPARTMENT_IDはEMPLOYEESエンティティの外部キーである。

解答

DEPARTMENT_IDはEMPLOYEESエンティティの外部キーである

解説

EMPLOYEESエンティティとDEPARTMENTSエンティティとの間には、多対１の関係が成り立ちます。この場合、1側の主キーが多側の外部キーとなります。なお、多対多の関係の場合は交差エンティティを追加して1対多に分解する必要があります。

【第2問】

EMPLOYEES表からFIRST_NAME列の値とDEPARTMENT_ID列の値を取り出し、次のようなレポートを作成します。どの問合せを発行すればよいですか。

（FIRST_NAMEがSCOTT, DEPARTMENT_IDが10とします。）

Scott’s department is 10.

選択肢

1. SELECT INITCAP(first_name)||’s department is ||department_id||. FROM employees;

2. SELECT INITCAP(first_name)||q’{’s department is}’||department_id||’.’ FROM employees;

3. SELECT UPPER(first_name)||’’s department is’||department_id||’.’ FROM employees;

4. SELECT INITCAP(first_name)||”’s department is’||department_id||”.” FROM employees;

解答

SELECT INITCAP(first_name)||q’{’s department is}’||department_id||’.’ FROM employees;

解説

リテラルとの連結には||を使用します。またリテラル自体は’ で括ります。連結したいリテラルが’を含んでいる場合、q’括弧　括弧’　を使用します。

【第3問】

あなたは、FIRST_NAME列の値の2文字目がAで、その後に少なくとも1文字が続く名前の従業員をレポートしようと考えています。どの問合せを使用しますか。

1. SELECT * FROM employees WHERE first_name LIKE ‘_A_%’;

2. SELECT * FROM employees WHERE first_name LIKE ‘%A_%’;

3. SELECT * FROM employees WHERE first_name = ‘_A_%’;

4. SELECT * FROM employees WHERE first_name = ‘%A_%’;

解答

SELECT * FROM employees WHERE first_name LIKE ‘_A_%’;

解説

2文字目がAということなので、任意の1文字を表す_と任意の文字列を表す%とを組み合わせて記述します。また曖昧検索では、LIKEを使用します。

【第4問】

あなたは、セッションで初めて実行されるときは表名と3つの列名を入力するように求め、その後、同じセッションで同じ問合せを実行するときは3つの列名のみ入力を求められるような問合せを作成します。どの問合せを使用しますか。

選択肢

1. SELECT &col1, &col2, &col3 FROM &tab;

2. SELECT &&col1, &&col1, &&col1 FROM &&tab;

3. SELECT &col1, &col2, &col3 FROM &&tab;

4. SELECT &&col1, &&col2, &&col3 FROM &tab;

解答

SELECT &col1, &col2, &col3 FROM &&tab;

解説

セッションで初めて実行する時は表名も入力、2度目以降は3つの列名のみ入力するとのことなので、表名は&&、列名は&置換変数を使用します。

【第5問】

NLS_DATE_FORMATがDD-MON-YYYY HH24:MI:SSに設定されているセッションの日時関数に関して正しい記述はどれですか。

選択肢

1． SYSDATEは、DUAL表からのみ問い合せることができる。

2． NLS_DATE_FORMATがDD-MON-YYYY HH24:MI:SS に設定されているため、SYSDATEを使用するとエラーになる。

3． CURRENT_DATEはデータベース・サーバーのOSのタイムゾーンで現在の日時を返す。

4． CURRENT_DATEはセッションのタイムゾーンで現在の日時を返す。

解答

CURRENT_DATEはセッションのタイムゾーンで現在の日時を返す。

解説

SYSDATEはDUAL表に限らず、どこでも使用可能です。また、時間も返します。

CURRENT_DATEはセッションのタイムゾーンで日時を返します。

【第6問】

次の問合せを確認してください。

SELECT TRUNC(ROUND(283.94,-2),1) FROM DUAL;

どのような結果になりますか。

選択肢

1. 200

2. 283.9

3. 283.94

4. 300

解答　

300

解説

関数をネストして使用しています。内側から評価しますので、ROUND(283.94,-2)を先に処理しますが、この戻り値は300となります。TRUNC(300,1)の戻り値は300となります。

【第7問】

EMPLOYEES表には、データ型がNUMBER(8, 2)のSALARY列があります。次のSQL文を確認してください。

SELECT TO_CHAR(salary, '$9,999') FROM employees;

出力に関して正しい記述はどれですか。

選択肢

1． SALARYが1011.99の場合、$1,011.99と表示される。

2． SALARYが10111.99の場合、$1,011と表示される。

3． SALARYが10111.99の場合、#######と表示される。

4． SALARYが10111.99の場合、$10,111と表示される。

解答

SALARYが10111.99の場合、#######と表示される。

解説

TO_CHAR関数で表示書式を指定しています。$9,999と指定しているので、先行０は無しで整数4桁で表示することになります。SALARY列の整数部が5桁以上になる場合は桁あふれとなり、#####表示となります。

【第8問】

EMPLOYEES表があります。どの問合せが有効ですか。

選択肢

1． SELECT dept_id, AVG(MAX(salary)) FROM employees GROUP BY dept_id;

2． SELECT dept_id, hire_date, SUM(salary) FROM employees GROUP BY dept_id, hire_date;

3． SELECT dept_id, hire_date, SUM(salary) FROM employees WHERE dept_id=10 GROUP BY hire_date;

4． SELECT dept_id, hire_date, SUM(salary) FROM employees GROUP BY dept_id;

解答

SELECT dept_id, hire_date, SUM(salary) FROM employees GROUP BY dept_id, hire_date;

解説

グループ関数を使用するときは、SELECT句で指定した列をすべてGROUP BY句に書く必要があります。選択肢3のようにWHERE句で絞り込んでいる場合も構文上、GROUP BY句に書く必要があります。

【第9問】

COUNT関数に関して正しい記述はどれですか。

選択肢

1. ORDER BY句と併用できない。

2. COUNT(*)は、重複する行を除いた行数を返す。

3. COUNT(salary)は、SALARY列にNULLが含まれている行を含む、表内の行の数を返す。

4. COUNT(DISTINCT salary)は、SALARY列の重複する行とNULLが含まれている行とを除いた行の数を返す。

解答

COUNT(DISTINCT salary)は、SALARY列の重複する行とNULLが含まれている行とを除いた行の数を返す。

解説

COUNT(*)は重複行も含めて、表に含まれる全ての行数を返します。

COUNT(列名)では、その列の列値がNULL以外の値の個数を返します。

COUNT(DISTINCT 列名)では、その列の列値がNULL以外で、かつ、列値の重複を除いた数を返します。

【第10問】

複合問合せでINTERSECT演算子を使用した結果に関して正しい記述はどれですか。

選択肢

1. 表の順序を逆にすると、出力に影響を与える可能性がある。

2. INTERSECTでは、ORDER BY句は併用できない。

3. 複合問合せの各SELECTの列名は、異なるものにすることができる。

4. 複合問合せの各SELECTの列の数は、異なるものにすることができる。

解答

複合問合せの各SELECTの列名は、異なるものにすることができる。

解説

複合問合せの各SELECTの列数、データ型は揃える必要がありますが、列名は異なっていて構いません。MINUSは表の順を逆にすると出力に影響があります。

【第11問】

条件付きINSERT ALLに関して正しい記述はどれですか。

選択肢

1. 副問合せによって返される各行は、いずれか1つのターゲット表にのみ挿入される。

2. 1つのWHEN条件を複数のINTO句に対して使用できる。

3. ELSE句を指定することはできない。

4. 挿入される行の総数は、副問合せによって返される行の数と必ず等しくなる。

解答

1つのWHEN条件を複数のINTO句に対して使用できる。

解説

副問合せによって返される各行は、いずれか1つのターゲット表にのみ挿入されるというのは、INSERT FIRSTの説明です。INSERT ALLでもINSERT FIRSTでもELSE句を指定できます。INSERT ALLでは、条件に合致すれば複数の表にINSERTが行われるので、挿入される行の総数は副問合せによって返される行数より多くなる可能性もあります。

【第12問】

Oracleデータベースの索引とその管理に関して正しい記述はどれですか。

選択肢

1. 降順索引は、関数ベースの索引である。

2. INVISIBLE索引はDMLの際、索引エントリはメンテナンスされない。

3. 外部キー制約を定義すると、外部キー列に自動的に索引が作成される。

4. 索引列をWHERE句で指定すると、アクセスパスとして必ず索引が使用される。

解答

降順索引は、関数ベースの索引である。

解説

降順索引は関数索引として内部的には処理されます。

INVISIBLE索引であってもDMLの際、メンテナンスされます。

制約定義時に自動的に索引が作成されるのは、主キー制約と一意キー制約です。

索引列をWHERE句で指定しても、問合せオプティマイザは必ずしも索引アクセスを選択するとは限らず、統計情報を元にアクセスパスを決定します。

【第13問】

WITH GRANT OPTION句に関して正しい記述はどれですか。

選択肢

１．システム権限で使用できる。

２．権限受領者は、別のユーザーに対して、受領した権限を付与できる。

３． WITH GRANT OPTION付で付与された権限を取り消しても、その権限受領者が別のユーザーに対して付与した権限は取り消されない。

４．システム権限とオブジェクト権限とで使用できる。

解答

権限受領者は、別のユーザーに対して、受領した権限を付与できる。

解説

WITH GRANT OPTIONはオブジェクト権限で使用できます。

また、WITH GRANT OPTION付で付与された権限を取り消したとき、その権限受領者が別のユーザーに対して付与した権限は連鎖的に取り消されます。

【第14問】

EMPLOYEES表には、データ型がNUMBERのSALARY列があります。休職中の社員はSALARY列がNULLになっています。

正常に実行される問合せはどれですか。2つ選択してください。

選択肢

1． SELECT employee_id, NVL(salary*1.1, 'On Leave') FROM employees;

2． SELECT employee_id, NVL2(salary*1.1, 'On Leave') FROM employees;

3． SELECT employee_id, NVL(TO_CHAR(salary*1.1), 'On Leave') FROM employees;

4． SELECT employee_id, NVL(salary*1.1, TO_NUMBER('On Leave')) FROM employees;

5． SELECT employee_id, NVL2(salary*1.1, TO_CHAR(salary*1.1), 'On Leave') FROM employees;

解答

SELECT employee_id, NVL(TO_CHAR(salary*1.1), 'On Leave') FROM employees;

SELECT employee_id, NVL2(salary*1.1, TO_CHAR(salary*1.1), 'On Leave') FROM employees;

解説

NVL関数、NVL2関数はNULLを指定された値で置き換える関数ですが、戻り値のデータ型が同じになる必要があります。そのため、TO_CHAR変換関数を用いてデータ型を揃えています。

【第15問】

EMPLOYEES表から部門IDと職種IDのリストを作成します。同じ組み合わせは一度しか表示しません。正しい問合せはどれですか。

選択肢

1． SELECT UNIQUE department_id, job_id FROM hr.employees;

2． SELECT UNIQUE department_id, UNIQUE job_id FROM hr.employees;

3． SELECT department_id, DISTINCT job_id FROM hr.employees;

4． SELECT DISTINCT department_id, UNIQUE job_id FROM hr.employees;

解答

SELECT UNIQUE department_id, job_id FROM hr.employees;

解説

同じ組み合わせは一度のみ、重複を表示しないので、DISTINCTもしくはUNIQUEを使用します。

【第16問】

NLS_DATE_FORMATがDD-MON-RR、NLS_DATE_LANGUAGEがAMERICANに設定されています。
次の問合せのうち、エラーにならずに実行できるものはどれですか(2つ選択してください)。

選択肢

1． SELECT * from hr.employees where hire_date > '31-03-20';

2． SELECT * from hr.employees where hire_date > '31-03-2020';

3． SELECT * from hr.employees where hire_date > '31-MAR-20';

4． SELECT * from hr.employees where hire_date > '31-MARCH-2020';

解答

SELECT * from hr.employees where hire_date > '31-MAR-20';

SELECT * from hr.employees where hire_date > '31-MARCH-2020';

解説

NLS_DATE_FORMATがDD-MON-RRなので、月はMARもしくはMARCHと指定する必要があります。月を03と書いてしまうとエラーになります。

【第17問】

UNIONなどの集合演算子を用いた複合問合せについて正しい説明はどれですか。

選択肢

1. 複合問合せでは、各SELECTにORDER BY句をつけることができる。

2. 最初の問合せによって返される各列のデータ型は、2番目の問合せによって返される対応する列のデータ型と一致する必要がある。

3. ORDER BY句では、2番目の問合せで指定した別名で指定できる。

4. すべての集合演算子で出力がソートされる。

解答

最初の問合せによって返される各列のデータ型は、2番目の問合せによって返される対応する列のデータ型と一致する必要がある。

解説

最初の問合せによって返される各列のデータ型と、返される列数は、2番目の問合せによって返される対応する列のデータ型、列数と一致する必要がある。

ORDER BY句は一番目の問合せで指定した列名で指定する必要があります。

UNION ALL句を使用した場合は、ソートされません。

【第18問】

内部結合と外部結合に関して正しい記述はどれですか。

選択肢

1. 外部結合は結合条件が一致しない行だけを返す。

2. Oracle結合構文を使って完全外部結合を記述できる。

3. Oracle結合構文を使って右側外部結合を記述することはできない。

4. Oracle結合構文とSQL:1999準拠のANSI結合構文とで、パフォーマンスは変わらない。

解答

Oracle結合構文とSQL:1999準拠のANSI結合構文とで、パフォーマンスは変わらない。

解説

Oracle結合構文とSQL:1999準拠のANSI結合構文とで、パフォーマンスは変わりません。Oracle結合構文と呼んでいるのは、JOIN句は使わず、WHERE句で結合条件を指定するタイプの結合構文のことです。

【第19問】

シーケンスについて正しい記述はどれですか。

選択肢

1. シーケンスを使って生成した主キー値に欠番が生じることはない。

2. 一つのシーケンスを複数の表の主キー値の生成で使用することができる。

3. 表を削除すると、その表の列値の生成に使用していたシーケンスは無効になる。

4. シーケンスはシステム・オブジェクトに分類される。

解答

一つのシーケンスを複数の表の主キー値の生成で使用することができる。

解説

一つのシーケンスを複数の表の主キー値の生成で使用することは可能です。ただし、それぞれの表ごとに見ると、主キー値に欠番が生じます。

表とシーケンスは定義上の依存関係はありませんので、表を削除したからといってシーケンスが無効になることはありません。

シーケンスはスキーマ・オブジェクトに分類されます。

【第20問】

Oracle Databaseのタイム・ゾーン、日付データ型およびタイムスタンプ・データ型に関して正しい記述はどれですか。1つ選択してください。

選択肢

1． CURRENT_TIMESTAMPは、タイム・ゾーン無しのタイムスタンプを返す。

2． SESSIONTIMEZONEは、現行セッションのタイムゾーンを返す。

3． DBTIMEZONEは現行セッションのタイムゾーンを返す。

4． SYSDATEは現行セッションのタイム・ゾーン情報付きの結果を返す。

解答

SESSIONTIMEZONEは、現行セッションのタイムゾーンを返す。

解説

SESSIONTIMEZONEは、現行セッションのタイムゾーンを返します。CURRENT_TIMESTAMPは、タイムゾーン付きの結果を返します。

DBTMEZONEはデータベースのタイムゾーンを返します。

SYSDATEはDBサーバーの日時を返します。