SQL試験間違えたところ(1Z0-61)

リレーショナル・データベース

オブジェクト指向データベース(00DB)モデルに関して、正しい記述は？

▼回答

• 同じデータベースで複数のオブジェクトをサポート
• データ構造が定義されたメソッドがくみこまれている
• リレーショナル型データベースと完全な互換性を持つ

▼解説
Oracle Databaseはリレーショナル型およびオブジェクト・リレーショナル型データベースを管理するためのソフトウェアです。

リレーショナル・データベース管理システム(RDBMS)、またオブジェクト・リレーショナル・データベース管理システム(ORDBMS)とも呼ばれます。

オブジェクト・リレーショナル型データベースは、**オブジェクト指向データベース(OODB)**とも呼びます。

• [間違い]ツリー構造でデータを格納する(階層型データベースの説明です)

リレーショナル・データベースの正規化において、表に主キーを設定し、データをグループ化して分割し、また冗長性を排除した正規形は？

▼回答
第一正規形

▼解説
正規化とは、データの重複を排除し、データ間の正しいリレーションシップを設定してデータを一元管理できるようにすることです。

[第一正規形(1NF)]
第一正規形は、非正規系の表に次の作業を行います。

• 主キーを設定する
• 繰り返し現れる列のデータをグループ化して、別の表に切り離す
• **導出項目(他の属性から算出できる項目)を排除}}する

[非正規系]
非正規系は正規化されていないデータです。

[第２正規形]
第２正規化は、第一正規形の表から部分関数従属性であるものを除く。

[第３正規化]
第3正規化は、第２正規形から推移関数従属性を除く。

リレーショナル・データベース(RDB)モデルに関して、正しい記述は？

▼回答

• 複数の表をデータで関連つけることができる
• データを行と列からなる2次元の表形式で格納
• E. F. Coddのルール(エドガー・F・コッドによって考案されたRDBMSに関する規則)をサポートする

SELECT文

SELECT ALL カラム名について

▼解説
ALLは検索結果の重複行を排除せずに表示するためのキーワードです。
ALLキーワードのあとには表示する項目を記述する必要があります。

(例)SELECT ALL employee_name FROM EMPLOYEES;

次のSQL文のうち、正常に実行されるのは？(DBの実行環境は日本語環境)

▼回答

SELECT employee_name AS "EMPNAME" FROM employees ORDER BY empname;
SELECT employee_name "社員名" FROM employees ORDER BY 社員名;
SELECT employee_name emp#name FROM employees ORDER BY emp#name;

▼解説
SQL文の実行結果に表示される列見出には、列別名を指定出来ます。
列別名はオブジェクトのネーミング規則に従い命名しなければなりませんが、列別名を二重引用符で囲むことで、大文字と小文字を区別したり、スペースを含めるなどネーミング規則に反する列別名を使用できます。

列別名を二重引用符で囲んだ場合には、ORDER BY句でも同様に列ベル名を二重引用符で囲む必要があります。

選択肢を1つずつ見ていきましょう。

• SELECT employee_name "社員名" FROM employees ORDER BY 社員名;
  →日本語環境では、列別名に漢字、ひらがな、カタカナも使用出来ます。「社員名」という列別名は2重引用符で囲んでいなくても正常に実行できます。

• SELECT employee_name AS "EMPNAME" FROM employees OREDER BY empname;
  →SELECT句の二重引用符(")で囲んでいる「EMPNAME」は全て大文字で、ORDER BY句の「empname」も二重引用符がなくてもデフォルトで大文字の「EMPNAME」と認識されるため正常に実行できます。

• SELECT employee_name emp#name FROM employees ORDER BY emp#name;
  →特殊文字(#,$,_)はオブジェクト名として使用できるため、列別名を二重引用符で囲む必要はありません。

正常に実行でき、正しいSQLです。

ただし、特殊文字を列別名の先頭や単独で使用する場合は、列別名を二重引用符で囲む必要があります。

データの制限およびソート

##　学び

• 算術式に列別名は指定できる

row_limiting_clauseの正しい説明は？

▼回答
ROW，ROWSキーワードは省略できない。

▼解説
Oracle 12cより、SELECT文の問い合わせ結果として返される行数を制限できる機能、row_limiting_clause(行制限の条件)が加わりました。これにより、例えば上位10番目から20番目のデータを簡単に取り出せます。

row_limiting_clauseにはOFFSET句とFETCH句があり、SELECT文で次のように指定します。

SELECT 列名
FROM 表明
[WHERE　条件]
[ORDER BY 列名]
[OFFSET]
[FETCH]

ORDER BY句は省略できますが、省略した場合はどのような順番で問い合わせ結果が返されるか保証されません。

そのため、「SALARY列の値を大きい順に上位N件標示する」など、ソートした順番でデータを取り出したい場合は、ORDER BY句を指定する必要があります。

OFFSET句にはスキップする行数、FETXH句には返される行数、または行の割合を指定します。

両句にROW,ROWSキーワード肉別は有りませんが、省略出来ません。

以下の条件をすべて満たす問い合わせは？(日付書式：RR-MM-DD)

1. 名前(EMPLOYEE_NAME)に「田」もしくは「藤」のいずれかが含まれていないこと
2. 入社日(HIREDATE)が2001年10月1日より前か、2007年4月1日以降であること
3. 部署(DEPATMENT_ID)が「総務(1)」と「開発(3)」以外であること

▼回答

SELECT department_id, employee_name, hiredate FROM employees
WHERE employee_name NOT LIKE '%田%' AND employee_name NOT LIKE '%藤%'
　AND hiredate NOT BETWEEN '01-10-01' AND '07-03-31'
　AND department_id NOT IN (1, 3);

▼解説
変換後の式が回答に書いてあるのが正です！
まず、各条件の式を1つずつ確認しよう。

1. 名前(EMPLOYEE_NAME)に「田」もしくは「藤」のいずれかが含まれていないこと

→「aかbのいずれかではない」という条件は以下のように変換できます。
NOT(a or b) = (NOT a) AND (NOT b)

また、間違いの選択肢では、

SELECT department_id, employee_name, hiredate FROM employees
　WHERE (employee_name NOT LIKE '%田%' OR employee_name NOT LIKE '%藤%')
　　AND hiredate NOT BETWEEN '01-10-01' AND '07-03-31'
　　AND department_id NOT IN (1, 3);

条件式1の中で2つのNOT LIKEがOR演算子で結ばれているため、以下のように解析されます。
(NOT a) OR (NOT b) = NOT(a AND b)

次のSQLの実行結果として正しい記述はどれ？(DBの実行環境は英語環境、日付表示書式はデフォルト)

SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE), 'FRI'), 'FMDD "is the first" Day "for" Month YYYY') "Result" FROM dual;

// FM : 埋め込みモードの有効/無効。FM有効だと0は取り除かれ、書かないと0が表示される
// DD : 日
// Day : 空白が埋め込まれた9文字の長さの曜日

▼回答
来月の最初の金曜日の日付を表示する

▼解説

設問のSQL文のLAST_DAY(SYSDATE)は、今月の最終日を返します。NEXT_DAY(LAST_DAY(SYSDATE), 'FRI')は、今月の最終日の翌日以降の最初の金曜日、すなわち来月の最初の金曜日の日付を返します。

最後に、上記の結果をTO_CHAR関数で指定した書式で、"Result"という別名で表示しています。

このSQL文に関して正しい記述はどれ？

勤続10年を超えた社員は、勤続年数×1万円の勤続手当がもらえることになりました。

社員名、勤続手当額、該当しない社員には「勤続手当なし」と出力するSQL文を作成しました。

このSQL文に関して正しい記述はどれ？

SELECT employee_name "社員名",
　　CASE WHEN (SYSDATE - hiredate)/ 365 > 10 THEN
　　TO_CHAR(TRUNC((SYSDATE - hiredate)/ 365) * 10000)
　　ELSE '勤続手当なし' END "勤続手当"
　FROM employees;

▼回答
DECODE関数には置き換えられない

▼解説
検索CASE式は、条件を条件1から順番に判定し、条件が心の場合に条件に対応する戻り値を返します。

真となる条件が1つも存在しない場合は、ELSE句に指定されたデフォルトの戻り値が返されますが、ELSE句が指定されていない場合はNULL値を返します。

CASE WHEN 条件1 THEN 戻り値1
　　　　　　　　　[WHEN 条件2 THEN 戻り値2 ...]
　　　　　　　　　[ELSE デフォルトの戻り値]
　END

DECODE(式, 条件式1, 戻り値1,
         , 条件式2, 戻り値2,
         , 条件式3, 戻り値3)

設問のSQL文では、(SYSDATE - hiredate)/365で入社日から現在までの勤続年数を求め、10年を超える場合はTRUNCで小数点以下を切り捨てた勤続年数*10,000で手当の額を算出しています。

この条件に該当しない場合は、「勤続手当なし」と表示します。
正しい結果を返すSQL文です。

CASE式では、WHEN句で比較条件やINやLIKEなどの演算子が使用できますが、DECODE関数では使用出来ません
。

次のSQL文をCASE式を使用して置き換えるとどうなる?

SELECT department_id, department_name,
　　　　　　　　DECODE(department_id, 1, 1013
　　　　　　　　　　　　　　　　　　　　, 2, 1014
　　　　　　　　　　　　　　　　　　　　, manager_id) new_manager
　FROM departments;

▼回答

SELECT department_id, department_name, 
CASE department_id WHEN 1 1013 WHEN 2 THEN 1014
ELSE manager_id END new_manager FROM departments;

「2012-Apr-25」という文字列を以下の形式で表示するには、どの問い合わせを実行する？(実行環境:英語環境)

25TH of April, Two Thousand Twelve

▼回答

SELECT TO_CHAR(TO_DATE('2012-Apr-25', 'YYY-Mon-DD'), 'DDTH' "of" FMMonth, YyyySp') FROM dual;

▼解説
日付を表す文字列を書式化して標示するには、文字列をTO_DATE関数で日付値に変換し、その後TO_CHAR関数で日付書式に従って文字列に変換。

• SP要素 : スペル表記(例 DDSP→FOUR)
• TH要素 : 順序表記(例 DDTH→4th)

次のSQL文でエラーとなるのはどれ?

▼回答

SELECT NVL(manager_id, 'none') FROM employees;

▼解説
NVL関数の第２引数には、第一引数と同じデータ型の値を指定しなければならない。

異なるデータ型の値を指定するとエラーになります。

プロモーションの開始日(PROMO_BEGIN_DATE)が週末だったら「週末」、平日だったら「平日」と表示。正しい結果を得られないSQL文は？

▼回答

SELECT promo_name, promo_begin_date,
　CASE WHEN TO_CHAR(promo_begin_date, 'DAY') BETWEEN '月曜日' AND '金曜日'
　THEN '平日' ELSE '週末' END "開始日"
FROM promotions;

▼解説
検索CASE式です。
BETWHEEN演算子に文字列を指定した場合は、文字列の文字コードの範囲で検索が行われます。

単純に曜日の判定はできないため、エラーとはなりませんが正しい結果は得られません。

エラーになるSQL文は?

▼回答

SELECT COALESCE(employee_id, employee_name, hiredate) FROM employees;

▼解説
COALESCE関数は引数の値を判定し、最初に見つかったNULL値以外の値を返す関数です。

引数は全て同じデータ型の値でなければなりません。
異なるデータ型の値を指定するとエラーになります。

HIREDATE列を次の形式で表示するにはどの問い合わせを実行する?(例 2012年04月01日)

▼回答

SELECT employee_name, TO_CHAR(hiredate, 'YYYY"年"MM"月"DD"日"') FROM employees;

▼解説
通常、DATE型の値を表示させると、「2012-04-01」のように表示されますので、任意のフォーマットに従って表示させるにはTO_CHAR関数を使用します。

TO_CHAR関数の日付書式には日付書式要素の他、文字も含めることができますが、半角記号以外の文字は二重引用符で囲まなければなりません。

数値書式に使用できる主な要素

次のSQL文のうち、エラーとなるのは?

▼回答

SELECT NVL(MONTHS_BETWEEN(SYSDATE, hiredate), SYSDATE) FROM employees;

▼解説
関数がネストしている場合は、内側の関数から実行されます。
内側の関数の結果が外側関数の引数として妥当な値であるか注意が必要です。

以下答えよ

15時より前に完了した決済の日時(PAYMENT_DATE)と金額(PAYMENT_AMT)のレポートを作成します。

金額が入っていない場合は「-1」と表示します。
どの問い合わせを実行しますか?

ただし、実行環境は日本語環境とし、PAYMENT_AMT列を次の形式で表示します。

例) ¥10,000

▼回答

SELECT TO_CHAR(payment_date, 'RR-MM-DD HH24:MI:SS') "決済日時", COALESCE(TO_CHAR(payment_amt, 'L99,999,999'), '-1') "金額"
FROM payments
WHERE TO_CHAR(payment_date, 'HH24') < 15;
SELECT TO_CHAR(payment_date, 'RR-MM-DD HH24:MI:SS') "決済日時", NVL(TO_CHAR(payment_amt, 'L99,999,999'), -1) "金額"
FROM payments
WHERE TO_CHAR(payment_date, 'HH24') < 15;

▼解説
TIMESTAMP型はDATE型を拡張したデータ型で、世紀、年、月、日、時、分、秒に加え、秒の小数点以下の値を格納できます。

設問の条件のように15時より前のPAYMENT_DATEを取り出すには、TO_CHAR(payment_date, 'HH24') < 15で、15時より前の時間を検索します。

HH24 : 時間(0〜23)
HH12 : 時間(0 〜12)

ここでは文字列→数値への暗黙的なデータ変換が行われます。

PAYMENT_AMT列を設問の書式にするには、TO_CHAR(payment_amt, 'L99,999,999')を指定します。

次のSQL文の実行結果として正しいものは？(実行環境は日本語環境)

SELECT TO_DATE('2012-05-21') FROM dual;

▼回答
正常に実行され、「12-05-21」が表示される

▼解説
TO_DATE関数の日付書式やNLSパラメータを省略可能です。
省略された場合はセッションのデフォルト値(デフォルトの書式は年月日を「/」ではなく「-」で区切る)が適用されます。

NLSパラメータ : 日付書式の言語環境指定

// NLSパラメータの書式
TO_CHAR(日付 [, '日付書式'][nls_date_language = '言語'])

// 具体例
TO_CHAR(SYSDATE, 'month:mon:day:dy', 'nls_date_language = AMERICAN')

DAY : 空白が埋め込まれた9文字の長さの曜日(日本語環境では日曜日、月曜日)
DY : 3文字の曜日(日本語では日、月、水)

各プロモーションの期間(日数)を標示するレポートを作成します。プロモーションの終了日(PROMO_END_DATE)がNULLだったら、「進行中」と表示します。どのSQL文を使用する？

▼回答

SELECT promo_name, NVL2(promo_end_date - promo_begin_date, TO_CHAR(promo_end_date - promo_begin_date), '進行中') status
FROM promotions;

SELECT promo_name, DECODE(promo_end_date - promo_begin_date, NULL, '進行中', promo_end_date - promo_begin_date) status
FROM promotions;

SELECT promo_name, CASE WHEN promo_end_date - promo_begin_date IS NULL THEN '進行中'
ELSE TO_CHAR(promo_end_date - promo_begin_date) END status
FROM promotions;

▼解説
選択肢を1つずつ確認してみましょう。

SELECT promo_name, NVL(promo_end_date - promo_begin_date, '進行中') status
　FROM promotions;

NVL関数の第二引数には、第一引数と同じデータ型の値を指定しなｋればなりません。
promo_end_date - promo_begin_dateがNULLの場合に文字列「進行中」を表示しようとしていますが、promo_end_date - promo_begin_dateは数値のためエラーとなります。

SELECT promo_name, NVL2(promo_end_date - promo_begin_date, TO_CHAR(promo_end_date - promo_begin_date), '進行中') status
FROM promotions;

promo_end_date - promo_begin_dateがNULL以外の場合はTO_CHAR関数で文字列に変換した期間を文字列に変換した期間を、NULLの場合は「進行中」を表示します。正しいSQL文です。

SELECT promo_name, COALESCE(promo_end_date - promo_begin_date, '進行中') status
FROM promotions;

COALESCE関数で引数を順番に判定しNULL値以外の値を返そうとしていますが、引数は全て同じデータ型でなければなりません。エラーとなります。

SELECT promo_name, CASE promo_end_date - promo_begin_date WHEN NULL THEN '進行中'
ELSE TO_CHAR(promo_end_date - promo_begin_date) END status
FROM promotions;

単純CASE文ではNULL値の評価は出来ないため、NULL値の判定はTRUEとならず条件に該当しないことになります。エラーにはなりませんが、「進行中」が表示されません。誤ったSQL文です。

**CASE式で値がNULLかどうかを確認するには「WHEN 項目名 IS NULL」**の形式で指定する。

SELECT promo_name, CASE WHEN promo_end_date - promo_begin_date IS NULL THEN '進行中'
ELSE TO_CHAR(promo_end_date - promo_begin_date) END status
FROM promotions;

検索CASE式では、WHEN句でIS NULL演算子を使うことでNULL値の判定が可能です。正しいSQL文です。

SELECT promo_name, DECODE(promo_end_date - promo_begin_date, NULL, '進行中', promo_end_date - promo_begin_date) status
FROM promotions;

**DECODE関数では、単純CASE式と違ってNULL値の評価が可能**です。

また、**複数の条件と戻り値を指定する場合、"最初"に指定された戻り値のデータ型が採用**されるため、ここではpromo_end_date - promo_begin_dateの明示的なデータ変換は行っていません。
正しいSQL文です。

次のSQL文の実行結果として正しいのは?

SELECT TO_NUMBER('-0.75', '0.99999') * 3
FROM dual;

▼回答
正常に実行され、-2.25が表示される

▼解説
TO_NUMBER関数は文字列を数値へ変換し、数値データを返すため、返された数値を演算で使用することが出来ます。

また、**数値書式で符号を指定する場合は「-」ではなく「S」を使用**します。ただし、-(マイナス)記号は「S」の指定がなくても正常に動作します。

▼参考
TO_NUMBER関数は、文字列を指定された書式に従って数値に変換する関数です。

書式は以下です。

TO_NUMBER(文字列 [, '数値書式'][, NLSパラメータ])

数値書式は第一引数で指定した文字列を数値に変換する際のフォーマットです。

数値書式に使用できる主な要素は次のとおりです。

**TO_NUMBER関数でNLSパラメータを指定すると、小数点文字、桁区切り文字、国際通貨記号、各国通貨記号を指定することが出来ます**が、省略された場合は現在のセッションのデフォルトのパラメータ値が使用されます。

TO_CHAR()とTO_NUMBER()での通貨表記の違い

最終的な表示結果としてTO_CHAR()は通貨表記、TO_NUMBER()は数値表記となる！

// TO_CHAR :第一引数には数値を入れる
TO_CHAR('555,000', 'L999,990')
→ ¥500,000

// TO_NUMBER : 第一引数には文字列を入れる
TO_NUMBER('¥555,000', 'L999,990')
→ 500,000

また、NLSパラメータはどちらも、小数点文字、桁区切り文字、国際通貨記号、各国通貨記号を指定することが出来る！

次の問い合わせでエラーとなるのは?

SELECT employee_id, CONCAT(employee_name, salary) FROM employees;

エラーとなる問い合わせはない

SELECT employee_id, employee_name FROM employees WHERE department_id = '1';

SELECT employee_id, employee_name, hiredate FROM employees WHERE hiredate >= '10-04-01';

SELECT employee_id, employee_name, SUBSTR(hiredate, 1, 5) FROM employees;

▼回答
エラーとなる問い合わせはない

▼解説
Oracle Databaseでは、データ型の変換が意味を持つ場合に、自動的にデータ型の変換が行われます。

そのため、関数の引数やWHERE句の条件等に期待されるデータ型以外の値を指定したとしても、エラーとならない場合があります。

選択肢のSQL文を1つずつ確認してみましょう。

SELECT employee_id, employee_name FROM employees WHERE department_id = '1';

DEPARTMENT_ID列は数値なので、department_id = 1と指定するべきですが、'1'の部分が暗黙的なデータ変換により数値に変換されるのでエラーにはなりません。

SELECT employee_id, CONCAT(employee_name, salary) FROM employees;

CONCAT関数の引数には文字列データを指定するべきですが、salary(数値)が暗黙的なデータ変換により文字列に変換されるので、エラーにはなりません。

SELECT employee_id, employee_name, SUBSTR(hiredate, 1, 5) FROM employees;

**SUBSTR関数の引数には文字列データを指定すべき**ですが、salary(数値)が暗黙的なデータ変換により文字列に変換されるので、エラーにはなりません。

SELECT employee_id, employee_name, hiredate FROM employees WHERE hiredate >= '10-04-01';

HIREDATE列は日付値なので、TO_DATE関数で日付値に変換してから指定するべきですが、'10-04-01'の部分が暗黙的なデータ変換により日付値に変換されるので、エラーにはなりません。

現在の日付は2012年06月23日です。1999年4月1日から現在までの日数を求めるには、どのSQL文を実行しますか(実行環境:英語環境、デフォルト日付表示形式: RR-MM-DD)

▼回答

SELECT TO_DATE(SYSDATE, 'RR-MM-DD') - TO_DATE('99-04-01', 'RR-MM-DD') FROM dual;
SELECT SYSDATE - TO_DATE('99-04-01', 'RR-MM-DD') FROM dual;
SELECT SYSDATE - TO_DATE('1999-Apr-01', 'YYYY-Mon-DD') FROM dual;

▼解説
日付値から日付値を減算すると、2つの日付間の日数を求めることができます。

設問では**「1999年４月1日」から現在までに経過した日数を求めるので、2つの日付の日付値を取得し、減算**を行います。

現在の日付値はSYSDATE関数で取得でき、「1999年4月1日」の日付はTO_DATE関数を使用して日付値を取得します。

MMとMONについて

**'MM'(月: 1,2)は'MON(月: JAN)'や'MONTH(月: JANUARY)'に置き換えられて試行されるが、'MON'書式は'MM'書式に置き換えられないためエラー**になります。

次のSQL文のうち、正常に実行されるものは？(日付書式は"RR-MM-DD")

▼回答

SELECT startdate + '10' FROM prod;
SELECT * FROM prod WHERE startdate = '10-01-30';
SELECT * FROM prod WHERE startdate > '05/01/01';

▼解説

SELECT 'name:' + name FROM prod;

文字リテラルと文字型の値を連結する場合は、連結演算子(||)を使用します。"+"では連結できないためエラーになります。

SELECT * FROM prod WHERE startdate > '05/01/01';

WHERE句に指定された文字リテラルが暗黙的データ変換でDATE型の値に変換され、条件に該当する列が取り出されます。

ここで、日付書式が"RR-MM-DD"であるのに文字リテラルを"RR/MM/DD"の形式として、異なる区切り文字で指定しています。

しかし**Oracle Databaseは区切り文字に関して一定の柔軟性を持って変換しますので、エラーとはなりません**。

次の2つのSQL文の結果として正しいのは?

　1) SELECT employee_name, NVL(salary, salary + 10000) FROM employees;

　2) SELECT employee_name, NVL2(salary, salary + 10000, '') FROM employees;

▼回答
1),2)は正常に実行できるが、異なる結果が出力される

▼解説
NVL２関数の戻り値のデータ型(数値、文字列、日付値)は常に式2のデータ型と同じになります。必要に応じて暗黙的なデータ型変換が行われます！

日付値を次の表示形式で標示する正しいSQL文は？

▼回答

SELECT TO_CHAR(SYSDATE, 'fmDdth "of" Month, YYYY') FROM dual;

▼解説
日付書式はデフォルトで埋め込みモードが有効となっています。
埋め込みモードが有効の場合、数値が１桁の場合に先頭に0付きで表示されたり、文字の前後にスペース付きで表示されます。

埋め込みモードを無効にするには、日付け書式に「FM」を指定します。

設問では、**「01st」ではなく「1st」、また「June」の後ろにスペースがないことから、埋め込みモードを無効にして問い合わせを実行**したことがわかります。

COALESCE関数の戻り値

全ての引数がNULL値だった場合、COALESCE関数はNULL値を返します。

数値書式のうち、負の値に関する書式とその説明として、正しいものは？

▼回答

TO_CHAR(-123456, '999999MI') は 123456- と表示される
TO_CHAR(-123456, '999999PR') は <123456> と表示される

▼解説
選択肢を1つずつ確認してみましょう。

TO_CHAR(-123456, '999999S')は「-123456」と表示される

数値書式「S」は「+」または「-」記号を表示します。
「S」が数値書式の最初に指定された場合は値の前に「S」が数値書式の最後に指定された場合は値の後に「+」または「-」記号を表示します。

この選択肢では、、**TO_CHAR関数の第一引数の値が負の値であり、「S」が数値書式の最後に指定されているため、「123456-」と表示**されます。

TO_CHAR(-123456, '999999') は 123456 と表示される

数値書式「9」は指定された桁数の値を表示します。
値が負の値の場合は、値の前に-記号を表示します。

この選択肢ではTO_CHAR関数の第一引数の値が負の値ですので、-123456と表示されます。

TO_CHAR(-123456, '999999MI') は -123456 と表示される

**数値書式「MI」は値が負の値の場合のみ、値の後ろに-記号を表示**します。

この選択肢ではTO_CHAR関数の第一引数が負の値ですので、123456-と表示されます。

TO_CHAR(-123456, '999999PR') は <123456> と表示される

**数値書式「PR」は値が負の値の場合のみ、値を<>で囲んで表示**します。

この選択肢ではTO_CHAR関数の第一引数の値が負の値ですので、<123456>と表示されます。

▼参考
数値書式に使用できる主な要素は次のとおりです。

NULLIF関数の正しい説明は?

▼回答

• 1番目の引数にリテラルNULL値を指定できない
• 2番目の引数にリテラルNULL値を指定できる
• 2つの値を比較して、等しい場合はNULLを返す

▼解説
**NULLIF関数は第一引数と第二引数が等しい場合はNULL値を、等しくない場合は第一引数の値**を返します。

なお、第一引数にはリテラルのNULL値以外の値を指定しなければなりませんが、第２引数はNULL値を指定出来ます。

次のTO_NUMBER関数のうち、正常に実行出来るものは？

▼回答

TO_NUMBER('-123.456789', 'S999.999999')
// 数値書式「S」は+または-記号を表す

以下の実行結果として正しいのは？

2009年12月31日以前に入社した従業員と2010年1月1日以降に入社した従業員の年収の合計をそれぞれ求めるために次のSQL文を実行しました。ただし、従業員の年収はSALARY列の12倍にCOMMISION列の値を加算したものです。

SELECT SUM(CASE WHEN hiredate < TO_DATE('2010-01-01', 'YYYY-MM-DD') THEN salary * 12 + commission
　　　　　　ELSE NULL END) sum1,
　　　SUM(CASE WHEN hiredate >= TO_DATE('2010-01-01', 'YYYY-MM-DD') THEN salary * 12 + commission
　　　　　　ELSE NULL END) sum2
FROM employees;

▼回答
正常に実行でき、期待したデータが表示される

▼解説
**CASE式には単純CASE式と検索CASE式の2種類**があります。
設問のSQL文で使用されているのは検索CASE式です。

CASE式は関数の引数として記述することもできますし、デフォルトの戻り値にNULL値を指定することも出来ます。

以下SQL文に関して正しい記述はどれ？(DB実行環境:日本、日付け書式: "RR-MM-DD")

SELECT employee_id, employee_name, hiredate
FROM employees
WHERE hiredate >= '30-APR-10'; // 'DD-MON-RR'

▼回答
エラーが発生する

▼解説
Oracle Databaseでは、データ型の変換が意味を持つ場合に、自動的にデータ型の変換が行われます。

しかし、次のような場合にはデータ変換は行われません。

• 数値が期待されている場所に'abc'などの文字列を指定
• 日付書式に合っていない日付リテラルを指定

設問では、日本語環境で日付書式が"RR-MM-DD"であるのに対し、"DD-MON-RR"形式で日付リテラルが指定されているので、暗黙的なデータ変換が行われません。

DECODE関数について

★DECODE関数の条件に比較演算子やIN、LIKEなどの演算子を使うことは出来ない！

DECODE関数は、第一引数に指定された式の値と、第２引数以降に指定された条件を順に判定し、値が合致した条件に対応する戻り値を返します。

なお、DECODE関数の条件に、WHERE句の条件に指定するような比較演算子を使用した条件を記述することは出来ません。

また、**条件が複数指定された場合の戻り値は、第3引数で指定された戻り値のデータ型が採用**されます。

したがって、それぞれの戻り値は第3引数の戻り値のデータ型と同じデータ型の値を指定するか、暗黙的なデータ変換で第3引数の戻り値のデータ型と同じデータ型となるような値を指定しなければなりません。

ORDER BY句とGROUP BY句

**ORDER BY句には、GROUP BY句で指定した列とグループ関数を指定**出来ます。

SALARY列がNULLである従業員の人数を表示するSQL文は?

▼回答

SELECT COUNT(*) - COUNT(salary) FROM employees;

▼解説
■COUNT関数の引数とカウントされるデータの件数

* : 重複した値とNULL値も含む、全てのデータ件数
式またはALL : 重複した値をそれぞれ1件としてカウントしたデータの件数(重複あり、NULL値は含まず)
DISTINCT 式 : 重複した値を1回だけカウントしたデータの件数(重複なし、NULL値含まず)

*(アスタリスク)にDISTINCTオプションは指定できない！

*COUNT関数でNULL値を含むデータ件数を返すのは、引数に(アスタリスク)を指定した場合のみ**です。

したがって、COUNT(*)から**NULL値を含まない件数であるCOUNT(salary)を減算**すれば良いことになります。

これは、以下のSQL文と同じ結果となります。

SELECT COUNT(*) FROM employees WHERE salary IS NULL;

その他の回答候補としては、

SELECT COUNT(DISTINCT salary) FROM employees;
SELECT COUNT(*) - COUNT(DISTINCT salary) FROM employees;

COUNT(DISTINCT salary)は、**NULL値と重複した値を除いたデータ件数を返します。
したがって、COUNT(*) - COUNT(DISTINCT salary)はNULL値と重複したデータ件数の合計**となります。

次の問い合わせのうち、エラーとなる問い合わせは?

▼回答

SELECT department_id, job_id, MIN(salary) FROM employees WHERE hiredate >= '03-04-01' GROUP BY department_id, job_id HAVING salary BETWEEN 200000 AND 500000;

SELECT department_id, job_id, MIN(salary) FROM employees GROUP BY department_id, job_id HAVING AVG(salary) BETWEEN 200000 AND 500000 WHERE hiredate >= '03-04-01';

▼解説
**HAVING句にはグループ関数とGROUP BY句に指定された列のみ指定**できます。

また、**SELECT句でグループ関数の引数に列別名を指定することは出来ません。エラー**となります。

次の問い合わせのうち、エラーとなる問い合わせはどれ？

▼回答
SELECT AVG(hiredate) FROM employees;

SELECT employee_name FROM employees WHERE salary > AVG(salary);

**▼解説**
グループ関数はSELECT句、HAVING句、ORDER BY句で使用できますが、WHERE句では使用出来ません。

主なグループ関数であるCOUNT,MAX,MIN,SUM,AVG関数の引数には次の値を指定します。

- **COUNT関数**:数値、文字列、**日付型の値**を返す式または列と*(アスタリスク)
- MAX/MIN関数:数値、文字列、日付型の値を返す式または列
- **<font color="FireBrick">SUM/AVG関数:数値型</font>**の値を返す式または列

MAX?MINには引数に日付型を指定できますが、SUM/AVG関数は数値しか指定出来ません。

## 次の問い合わせを実行したところエラーとなりました。正常に実行するためにはどこを修正する?

```sql
SELECT MIN(AVG(salary)) FROM employees
GROUP BY department_id
HAVING MIN(AVG(salary)) > 400000;

▼回答
HAVING句のグループ関数

▼解説
設問のSQL文では、HAVING句で取り出すグループを制限していますが、HAVING句ではグループ関数をネスト出来ません。
SELECT句ではグループ関数を2つまでネスト出来ます。

正しくは以下！

SELECT MIN(AVG(salary)) FROM employees
GROUP BY department_id
HAVING AVG(salary) > 400000;

また、**SELECT句でGROUP BY句とORDER BY句を併用する場合、ORDER BY句にはGROUP BY句で指定した列かグループ関数のみ指定**できます。

Oracle Databaseに用意されているSQL関数で行える演算はどれ?

▼回答

• 最大値を求める
• 合計値を求める
• べき乗を求める

▼解説
SQL関数とは、Oracleに予め用意されている関数のことです。
主に以下のような関数があります。

[単一行関数]
MOD(m,n) : mをnで割った余りを返す
POWER(m,n):mをn乗したべき乗を返す
ROUND(m[,n]):mを小数点以下n桁に四捨五入した値を返す

[グループ関数]
COUNT([DISTINCT | ALL]{列名 | 列名を含む式}):データの件数を返す
MAX([DISTINCT | ALL] {列名 | 列名を含む式}): 最大値を返す
MIN([DISTINCT | ALL] {列名 | 列名を含む式}) : 最小値を返す
AVG([DISTINCT | ALL] {列名 | 列名を含む式}) : 平均値を返す
SUM([DISTINCT | ALL] {列名 | 列名を含む式}) : 合計値を返す

以下の、演算を行えるSQL関数はありません。

• 減算
• 除算の商を求める

年ごとのオーダー数を集計するために以下のSQL文を実行しましたがエラーとなりました。エラー原因は?

SELECT TO_CHAR(order_date, 'YYYY'), SUM(order_total)
FROM orders
GROUP BY TO_CHAR(order_date, 'RR');

▼回答
SELECT句のデータ型変換がGROUP BY句と異なるため

▼解説
GROUP BY句を指定したSELECT文のSELECT句には、GROUP BY区で指定した列、もしくはグループ関数のみを指定できます。

設問のSQLのSELECT句ではTO_CHAR(order_date, 'YYYY')に変換していますが、GROUP BY区ではRR書式に変換しています。

このようにSELECT句とGROUP BY句のデータ型の変換が異なるとエラーとなります。

全社員の平均月収を求めるSQLは?月収はSALARY列、SALARY列がNULL値である場合は月収を0として扱う

▼回答

SELECT SUM(salary) / COUNT(*) FROM employees;

SELECT AVG(NVL(salary, 0)) FROM employees;

▼解説
平均月収を求めるにはAVG関数を使用しますが、グループ関数はNULL値を無視して集計してしまうので、AVG関数の引数にSALARY列をそのまま指定してしまうと、NULL値以外の件数で割った平均が求められます。

全従業員の平均値を求めるには、

• SALARY列のNULL値を0に変換してからAVG関数で集計
• AVG関数を使用せずに、SALARY列の和を全従業員数で割る

グループ関数が使用できるのはどの句?

▼回答

• HAVING句
• ORDER BY句
• SELECT句

SELECT文内のGROUP BY句

SELECT文にGROUP BY句が指定されている場合は、SELECT句にはGROUP　BY句で指定された列かグループ関数しか指定出来ませんが、必ず指定する必要はありません。※SELECT句に2つの列があり、GROUP BY句で1つしか列を選ばないとエラーになり、GROUP BY句で2つの列を使用せずGROUP BY句だけなら問題ない！(byあさみ)

年収の最高額を表示するSQL文は?

ただし、年収はSALARY列の値を12倍したものにCOMMISSION列の値を加算したものとします。

また、SALARY列またはCOMMISSIOn列のいずれかがNULL値である従業員については対象外とします。

▼回答

SELECT MAX(salary * 12 + commission) FROM employees;

▼解説
MAX関数は指定された列や式の値の最大値を返す関数です。
年収は(salary * 12 + commission)で求めることが出来るので、年収の最高額を求めるには、MAX関数の引数に(salary * 12 + commission)を指定します。

なお、**SALARY列またはCOMMISSION列のいずれかがNULL値である場合は(salary * 12 + commission)の結果がNULL値となり、MAX関数の集計対象外**となります。

次の問い合わせのうち、エラーとなる問い合わせは?

SELECT department_id DEPT, AVG(salary) AVGSAL FROM employees GROUP BY DEPT;

SELECT department_id, job_id, SUM(salary) FROM employees WHERE SUM(salary) > 1000000 GROUP BY department_id, job_id;

SELECT department_id, job_id, MAX(salary) FROM employees GROUP BY department_id;

▼解説
SELECT文にGROUP BY句を指定すると、関連のある行をグループ化できますが、GROUP BY句を指定する場合にはいくつかの要件を満たす必要があります。

• GROUP BY句には1つ以上の列を指定
• GROUP BY句に列別名を指定することはできない
• GROUP BY句を指定したSELECT文のSELECT句には、GROUP BY句で指定した列、もしくはグループ関数のみ指定出来る！(SELECT句に指定したグループ関数以外の列は全てGROUP BY句で指定する必要あり)

Oracleで使用できる主なグループ関数

エラーとならない問い合わせは?

▼回答

SELECT MAX(salary), MIN(salary), AVG(salary) FROM employees;

SELECT MAX(AVG(salary)) FROM employees GROUP BY department_id;

▼解説
SELECT文にGROUP BY句を指定しない場合、グループ関数を使用することはできますが、グループ関数をネストすることは出来ません。

また、GROUP BY句を指定しても、グループ関数は2レベルまでしかネスト出来ません。

表の結合

外部結合の説明として正しいのは?

▼回答

• 結合条件を満たす行と満たさない行の両方を取り出す
• Oracle独自の結合構文には完全外部結合はない

▼解説
結合条件を満たしたデータのみを取り出すのではなく、検索条件を満たしていないデータも一緒に取り出す方法を外部結合と言います。

Oracle独自の結合構文では外部結合演算子(+)を使用して外部結合を行えますが、完全外部結合は行なえません。

• 結合条件を満たす行のみを取り出す
  →**結合条件を満たす行のみを取り出す結合を内部結合**といいます。

次のSQL文と同じ実行結果となるSQL文は？

SELECT e.employee_id, d.department_name, e.employee_name
FROM departments d JOIN employees e
ON d.department_id = e.department_id
WHERE e.hiredate >= '2005-10-01';

▼回答

SELECT e.employee_id, d.department_name, e.employee_name FROM departments d JOIN employees e ON d.department_id = e.department_id AND e.hiredate >= '2005-10-01';

SELECT e.employee_id, d.department_name, e.employee_name FROM departments d JOIN employees e USING(department_id) WHERE e.hiredate >= '2005-10-01';

▼解説
表の結合時、結合した表から取り出したい行を指定してSQL文を実行することができます。

**ON句による結合では、"ON句の後"に"WHERE句またはAND句"で選択条件を指定**します。

**USING句による結合では、USING 句のあとにWHERE句で選択条件を指定**します。

全従業員の従業員名、所属部署名、職種名を表示するにはどの問い合わせを実行する?

ただし、上司のいない従業員に関してはEMPLOYEES表のMANAGER_ID列にNULL値が登録されています。

▼回答

SELECT employee_name, department_name, job_name FROM departments d JOIN employees e ON d.department_id = e.department_id JOIN jobs j ON e.job_id = j.job_id;

SELECT employee_name, department_name, job_name FROM departments JOIN employees USING (department_id) JOIN jobs USING (job_id);

SELECT employee_name, department_name, job_name FROM employees NATURAL JOIN jobs JOIN departments USING (department_id);

▼解説
3つ以上の表を結合する場合は、

表名1 JOIN 表名2 ON 結合条件
JOIN 表名3 ON 結合条件

のように記述しますが、表Aは自然結合で結合する、表BはUSING　を使用した結合で結合するなどのように、結合方法を混在することも出来ます。

設問では、DEPARTMENTS表とEMPLOYEES表ではDEPARTMENT_ID列を、EMPLOYEES表とJOBS表ではJOB_ID列を結合列として表を結合します。

DEPARTMENTS表とEMPLOYEES表では、MANAGER_ID列も2つの表において同名の列ですが、MANAGER_ID列を結合列に加えてしまうとDEPARTMENT_ID列とMANAGER_ID列の組み合わせで結合されるため、MANAGER_IDがNULL値である場合は結合されず、全従業員のデータを取り出すことができなくなってしまうので注意が必要です。

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

SELECT employee_name, department_name, job_name FROM departments NATURAL JOIN employees JOIN jobs　USING (job_id);

DEPARTMENTS表とEMPLOYEES表にはDEPARTMENT_ID列とMANAGER_ID列の2つの同名列があります。

自然結合ではこの2つの列が結合列として使用されますが、MANAGER_IDがNULL値である列は結合されませんので、エラーとはなりませんが、全従業員のデータを取り出すことが出来ません！

どの問い合わせを実行する？

EMPLOYEES表のMANAGER_ID列には上司のEMPLOYEE_IDが登録されている。また、上司のいない従業員のMANAGER_ID列にはNULL値が登録されています。

従業員とその上司の氏名を標示するには、どの問い合わせを実行しますか？

▼回答

SELECT emp.employee_name, mgr.employee_name FROM employees emp LEFT OUTER JOIN employees mgr ON emp.manager_id = mgr.employee_id;

▼解説
従業員の氏名はEMPLOYEES表から取り出せます。
しかし上司の氏名を取り出すには、ENPLOYEES表のMANAGER_ID列の値でEMPLOYEES表を検索し、EMPLOYEE_NAME列の値を取り出す必要があります。

このようなデータの取り出しを行うには、**従業員の氏名をもつEMPLOYEES表とMANAGER_IDを持つEMPLOYEES表があると見立てて、2つの表を結合**します(自己結合といいます)

また、設問の
上司がいない従業員については、上司の氏名欄を空欄にしてください」より、上司のいない従業員のデータも取り出すということがわかります。

これは結合条件に合致しないデータも取り出すということですので、外部結合を行います。

全ての選択肢において表別名empから従業員名を、mgrから上司名を取り出していますが、empから取り出す従業員名は結合条件に合致していないデータも取り出さなくてはなりません。

全ての選択肢において列別名empはJOIN句の左側にありますので、LEFT OUTER JOINを使用します。

どのような場合にUSING句を使用しますか

▼回答

• 結合する表の一部の列を結合列として使用する場合
• 結合する2つの表に**同じ列名でデータ型の異なる列**がある場合

▼解説
USING句を使用した結合は、等価結合の1つで、結合列を明示的に指定することが出来るので、**2つの表に同名の列が複数あり、その一部を結合列にしたい場合や同名でデータ型の異なる列がある場合に、USING句で結合列にしないことでエラーを回避**が出来ます。

自然結合では、同名でデータ型が異なる列を結合してしまい、エラーとなります。

Oracleでの結合方法

全社員の氏名とその上司の氏名と職種を標示するSQL文を作成しました。空欄に入るJOIN句の組合わせとして正しいのは?

SELECT e.employee_name, m.employee_name manager_name, j.job_name
FROM employees e ___________ employees m
ON e.manager_id = m.employee_id
__________ jobs j ON e.job_id = j.job_id;

▼回答

LEFT OUTER JOIN, LEFT OUTER JOIN

▼解説
全社員の氏名はEMPLOYEES表から取り出せます。
社員の上司の氏名を取り出すには、EMPLOYEES表のMANAGER_ID列の値でENPLOYEES表を検索し、EMPLOYEE_NAME列を取り出す必要があります。

このようなデータの取り出しを行うには、従業員の氏名を持つEMPLOYEES表とMANAGER_IDを持つEMPLOYEES表があると見立てて、2つの表を結合します(自己結合)。

また、職種を取り出すには、EMPLOYEES表とJOB表をJOB_ID列で結合し、JOB_NAME列の値を取り出します。

従業員の氏名を持つEMPLOYEES表、MANAGER_IDを持つEMPLOYEES表、JOBS表の3つの表を結合します。

全社員の氏名を表示するということは「上司のいない(MANAGER_IDがNULL)社員も表示」するので、EMPLOYEES表の自己結合ではJOIN句の左側に指定された従業員の氏名を持つEMPLOYEES表のデータを全て取り出す左側外部結合を行います。

また、EMPLOYEE_ID列とMANAGER_ID列を結合するため、*

EMPLOYEES表とJOBS表の結合においても、全社員の氏名を表示するということは「職種のない(JOB_IDがNULL)の社員も表示」するので、JOIN句の左側のデータを全て取り出す左側外部結合を行います。

次のSQL文と同じ結果となるSQL文は？

SELECT department_id, department_name, employee_name, hiredate
FROM departments NATURAL JOIN employees
ORDER BY department_id, employee_id, hiredate;

▼回答

SELECT department_id, d.department_name, e.employee_name, e.hiredate FROM departments d JOIN employees e USING(department_id, manager_id) ORDER BY department_id, e.employee_id, e.hiredate;

SELECT d.department_id, d.department_name, e.employee_name, e.hiredate FROM departments d JOIN employees e ON d.department_id = e.department_id AND d.manager_id = e.manager_id ORDER BY department_id, employee_id, hiredate;

▼解説
設問のSQL文のようなNATURAL JOIN句による表の結合を自然結合といいます。

**自然結合では、2つの表に共通して存在する同名で同じデータ型の列に基づいて2つの表を結合**します。

設問のDEPARTMENTS表とEMPLOYEES表では、同名で同じデータ型の列は「DEPARTMENT_ID列」「MANAGER_ID列」になりますので、設問のSQL文の結果は、これらの2つの列が等しいという結合条件で2つの表を結合した結果と等しくなります。

2つの表の特定の列の値が等しいデータを結合することを等価結合といいますが、等価結合を行うには自然結合のほか、USING句による結合とON句による結合、Oracle独自の結合構文による結合があります。

設問の選択肢では、USING句による結合とON句による結合があります。

USING句による結合では、2つの表に共通するある同名の列で、結合に使用する列をUSING句に指定すれば良いので、USING句にDEPARTMENT_ID列とMANAGER_ID列を指定します。

また、ON句による結合では、結合条件をON句に指定しますので、ON句にDEPARTMENTS表.DEPARTMENT_ID = EMPLOYEES表.DEPARTMENT_IDとDEPARTMENTS表.MANAGER_ID = EMPLOYEES表.MANAGER_IDを指定します。

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

SELECT d.department_id, d.department_name, e.employee_name, e.hiredate FROM departments d JOIN employees e ORDER BY department_id, employee_id, hiredate;

NATURAL JOIN句では結合する列が自動的に判断されるため結合条件の記述は必要ありませんが、**JOIN句の場合はUSING句やON句で結合条件を記述する必要**があります。

次のSQL文の実行結果として正しいものは?

SELECT c.category, c.name category_name, p.name prod_name
FROM category c FULL OUTER JOIN prod p
ON c.category = p.category
ORDER BY c.category;

▼回答

▼解説
設問のSQL文では、CATEGORY表とPROD表を完全外部結合で結合しています。J完全外部結合では、指定された2つの表の条件を満たしていないデータも全て取り出します。

従業員名とその上司の氏名を標示する問い合わせは?

▼回答

SELECT emp.employee_name, mgr.employee_name FROM employees emp, employees mgr WHERE emp.manager_id = mgr.employee_id;

SELECT emp.employee_name, mgr.employee_name FROM employees emp JOIN employees mgr ON emp.manager_id = mgr.employee_id;

▼解説
従業員の氏名はEMPLOYEES表から取り出せます。
しかし、上司の氏名を取り出すには、EMPLOYEES表のMANAGER_ID列の値でEMPLOYEES表を検索し、EMPLOYEE_NAME列の値を取り出す必要があります。

このようなデータの取り出しを行うには、従業員の氏名を持つEMPLOYEES表とMANAGER_IDを持つEMPLOYEES表があると見立て、2つの表を自己結合します。

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

SELECT emp.employee_name, mgr.employee_name FROM employees emp JOIN employees mgr USING (employee_id);

**従業員名とその上司の氏名を表示するには、EMPLOYEE_ID列とMANAGER_ID列で結合**しなければなりません！

OROD表、OLDPROD表、CATEGORY表の構造とデータを確認してください

次のSQL文の実行結果として正しいものは?

SELECT c.category c_category, p.category p_category, c.name c_name, p.name p_name
FROM category c JOIN prod p
ON c.category = p.category
JOIN oldprod o
ON p.category = o.category;

▼回答

▼解説
3つ以上の表を結合する場合、Oracle Databaseは結合順序を自動的に決定します。

設問のSQL文のように、3つの表を結合する場合、Oracle Databaseは**最初に結合する2つの表を選択し、その結合結果と残りの表を結合**します。

設問のSQL文では、次のどちらかの順序で結合が行われます。

• CATEGORY表とPROD表の等価結合を行い、その結果とOLDPROD表を等価結合する
  

次の作業のうち副問合せが必要な作業は?

▼回答

• 全社員の平均給与より給与が多い従業員を表示
• 全社員の平均給与より給与が少ない従業員を表示

▼解説
選択肢の作業をSQL文にすると、それぞれ以下のようになります。

• 全社員の平均給与を表示する

SELECT AVG(sal) FROM employees;

• 全社員の平均給与より給与が多い従業員を表示する

SELECT employee_name FROM employees 
WHERE salary > (SELECT ANG(sal) FROM employees);

- 部署ごとの平均給与を表示

```sql
SELECT AVG(salary) FROM employees GROUP BY department_id;

- **<font color="FireBrick">所属従業員の給与合計が一番多い部署の給与合計を表示</font>**

```sql
SELECT MAX(SUM(salary)) FROM employees GROUP MY department_id;

• 全社員の平均給与より給与が少ない従業員を表示

SELECT employee_name FROM employees WHERE salary < (SELECT AVG(salary) FROM employees);

FROM句の副問合せのことをなんと呼ぶ?

▼回答
インライン・ビュー

▼解説
副問合せは、SELECT文のSELECT句、FROM句、WHERE句、HAVING句の他、INSERT文やUPDATE文等のDML文でも使用できます。

**"FROM句"の副問合せは、インライン・ビュー**とも呼ばれます。

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

• ネスト
  関数の引数に別の関数を指定することを「関数のネスト」と言います。

• 内部結合
  内部結合は、表の結合において結合条件を満たすデータのみを取り出す方法です。

• 複数行副問合せ
  **複数行副問合せは、複数件のデータを返す副問合せのこと**です。

給与の額が所属する部署の最低給与を超える従業員一覧と部署ごとの最低給与を表示します。正しい記述は?

SELECT e.employee_name, e.salary, e.department_id, s.minsal
FROM employees e, (SELECT department_id, MIN(salary) minsal FROM employees GROUP BY department_id) s
WHERE e.salary > s.minsal
ORDER BY e.department_id, e.salary;

▼回答
実行されるが、正しい結果が返されない

▼解説
設問のSQL文は、**Oracle独自の結合構文**です。

• 結合する表名は,(カンマ)で区切ってFROM句に指定
• 結合条件はWHERE句に指定
• 結合条件以外の条件はWHERE句に指定した結合条件のあとにAND演算子で指定

設問のSQL文ではEMPLOYEES表と部署ごとの最低給与を問い合わせる()で囲まれた副問合せをFROM句に指定し、両者を結合しています。

しかし、WHERE句には「e.salary > s.minsal」という検索条件しか指定されておらず、結合条件が欠けています。

この場合エラーとはなりませんが、結合した全てのデータの組み合わせが返ってくるので正しい結果は得られません。

正しく実行するには、WHERE句に結合条件を指定します。

副問合せに関する説明として正しいのは?

▼回答

• 複数行副問合せの比較演算子に単一行演算子=を使用するとエラーになる
• SELECT文で使用する副問合せは()括弧で囲む

▼解説
通常の副問合せを使用したSQL文ではまず副問合せが実行され、副問合せの実行結果をもとに主問い合わせが実行されます。

副問合せの部分は()括弧で囲みます。
INSERT文で副問合せを使用してデータの追加を行う場合は()は必須ではありません。

通常は副問合せ→主問い合わせの順に実行されますが、副問合せの中でのそのFROM句に無い表を参照する(副問合せの外側にある表を参照する)「相関副問合せ」では、主問い合わせで取り出される各行ごとに副問い合せが実行されます！

副問合せが使用できる句はどれですか

※ 副問合せが使用できるのは、GROUP BY句以外全部！！

• SELECT句
• FROM句
• WHERE句
• HAVING句
• ORDER BY句

▼解説
以下にSELECT文のそれぞれの句で副問合せを使用した例を紹介します。

[SELECT句]

[FROM句]

[WHERE句]

[HAVING句]

[ORDER BY句]

以下のSQL文を実行した正しい結果は?

SELECT department_id, maxincome, minincome
FROM (SELECT department_id, MAX(commission + (salary * 12)) maxincome,
MIN(commission + (salary * 12)) minincome FROM employees GROUP BY　department_id)
WHERE minincome > maxincome / 2
ORDER BY department_id;

▼回答
部署の最低年収が最高年収の半分より多い部署の成功が表示される

▼解説
SQL文は以下の順序で評価されるため、**通常は列別名だけを指定できるのはORDER BY句のみ**です。
(SELECT句で指定した列別名を認識できるのはORDER BY句のみのため)

[評価順]
FROM句→WHERE句→GROUP BY句→HAVING句→SELECT句

しかし、設問のSQL文では**FROM句の副問い合わせ(インラインビュー)で算術式に列別名を指定しているため、FROM句の後に評価される全ての区で列別名が認識**されます。

FROM句の副問い合わせでは、部署ごとの年収(commission + (salary * 12))の最高額と最低額それぞれmaxincome、minincomeと列別名を指定し、その後のWHERE句→SELECT句でも簡潔な列別名で問い合わせが実行されます。

GROUP BY句やHAVING句が含まれていたとしても、同様に列別名は認識されます。

FROM句で部署ごとの最高年収、最低年収を取り出し、WHERE句で最高年収の半分より多い最低年収という条件に絞っています。

EMPLOYEES表から部下のいない従業員の名前を表示します

各従業員のMANAGER_ID列に上司のEMPLOYEE_IDが登録されていますが、上司がいない従業員も存在します。次のSQL文の実行結果として正しいものは？

1) SELECT m.employee_name FROM employees m
WHERE NOT EXISTS (SELECT e.employee_id FROM employees e WHERE e.manager_id = m.employee_id);

2) SELECT employee_name FROM employees
WHERE employee_id NOT IN (SELECT manager_id FROM employees);

▼回答
どちらも正常に実行されるが、異なる結果となる

▼解説
1)のSQL文の**NOT EXISTS演算子は、主問い合わせで取り出した1行が副問合せの条件を満たしていない場合、つまり副問合せの結果が1行も返されない場合にTRUEとして評価され、"主問い合わせの結果"が返されます**。

主問い合わせのm表で取り出したデータが副問合せの「e.manager_id = m.employee_id」という条件を満たしていない場合、部下のいない従業員として主問い合わせは結果を返します。正しいSQL文です。

1)のSQL文の**NOT IN演算子はリスト内の全ての値と等しくない場合にTRUEを返しますが、副問合せの結果にNULL値が含まれていると全ての値と等しくないという判定がされません**。

副問合せのMANAGER_ID列にNULL値が含まれているため、エラーとはなりませんが主問い合わせは1行もデータを返しません。

次の問い合わせの実行結果として正しいのは？

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) <ALL (SELECT MAX(salary) / 2 FROM employees GROUP BY department_id);

▼回答
各部署の最高給与の半分の最小値より、部署の平均給与が少ない部署を検索

▼解説
設問のSQL文の副問合せは、部署ごとの最高給与の半分の値を取り出す複数行副問合せです。

**小なりALLは、左辺の値が右辺のリスト内の最小値よりも小さい場合にTRUEを返す複数行演算子**です。

次の問い合わせを実行すると何件のデータが返される?

[参考データ]

SELECT prodid, name, category
FROM prod
WHERE category <=ANY
(SELECT category FROM oldprod
WHERE category < 30);

▼回答
1

▼解説
ANY演算子はリストのいずれかの値が条件を満たす場合にTRUEを返しますので、<=ANY(値のリスト)は比較する値がリストのいずれかの値と等しいか小さい場合にTRUEを返します。

ANY演算子はリスト内の値のうち1つでも条件を満たす値があればTRUEとなるので、**<=ANY(値のリスト)はリスト内の最大値以下の場合にTRUEになる(いずれかより小さい=最大値より小さいとOK!)**ということです

複数行副問合せに関する説明として正しいのは？

▼回答

• **複数の"列"**を返すことも出来る
• 複数行副問合せは複数件のデータを返す
• 複数行副問合せをネストすることができる

次の問い合わせの実行結果として正しいのは?

[参考データ]

SELECT prodid, name, category
FROM prod
WHERE category IN
(SELECT category FROM oldprod);

▼回答
正常に実行され、PROD表のCATEGORY列の値が"10"と"40"の行が表示される

▼解説
IN演算子はリスト内のいずれかの値と等しい場合にTRUEを返すため、IN演算子の値のリストにNULL値以外の値が含まれていて、その値と比較対象の値が等しければ、主問い合わせでデータが取り出されます。

PROD表のCATEGORY列が"NULL"である行もありますが、}}NULL値同士を判定してもNULL値となり、値が等しい判定が出来ないのでCATEGORY列が"NULL"である行は取り出されません**。

EMPLOYEE_IDが「1008」の従業員と上司が同じである従業員の名前と上司のEMPLOYEE_IDを出力します。どの問い合わせを使用する？

▼正解

SELECT m.employee_name, e.manager_id FROM employees e
RIGHT OUTER JOIN employees m on (e.manager_id = m.manager_id)
WHERE e.employee_id = 1008;

SELECT employee_name, manager_id FROM employees
WHERE manager_id = (SELECT manager_id FROM employees WHERE employee_id = 1008);

▼解説
EMPLOYEE_IDが「1008」の従業員と上司が同じである従業員、つまり**「1008」の同僚を求める問題**です。

1つのSQL文で問い合わせるには、自己結合と副問合せを使用します。

まず、自己結合の方ですが、EMPLOYEE_IDが「1008」の従業員データのEMPLOYEES表eと同僚のデータEMPLOYEES表mがあると見立てます。

上司が同じであるという条件のため、ON句には**「e.manager_id = m.manager_id」**という結合条件を指定します/

※自己結合の例は通常、従業員データのEMPLOYEES表wと上司データのEMPLOYEES表mを見立てることが多いため、結合条件を「e.manager_id = m.employee_id」と間違わないように注意！

JOIN句については、上記の結合条件の表eとmには片方の表にしか行が存在しないため、LEFT OUTER JOINとRIGHT OUTER JOINのどちらの外部結合でも、また内部結合のJOIN句でも結果は同じです。

次の問い合わせの実行結果として正しいのは?

SELECT p.prod_name, s.qty_sold
FROM (SELECT prod_id, SUM(quantity_sold) qty_sold FROM sales GROUP BY prod_id) s
RIGHT OUTER JOIN products p
ON s.prod_id = p.prod_id;

▼解説
売上の有無に関わらず、全ての製品名が表示される

▼解説
設問のSQL文では、FROM句の副問合せでSALES表からPROD_IDごとの販売総数を取り出し、その結果とPRODUCTS表を結合して製品名と販売総数を表示しています。

JOIN句では、RIGHT OUTER JOINの右側にPRODUCTS表が指定されているため、結合条件を満たしていない製品名も全て取り出されます
。

また、**WHERE句に指定した副問合せでは255レベルのネストが可能**だよ！

また、副問合せがデータを1件も返さない場合は、主問い合わせにNULLを返す。

同じ価格の製品が複数ある場合、その製品名と価格を検索して表示します。目的の結果を得るためには、どの問い合わせを使用しますか?

▼回答

SELECT DISTINCT p1.prod_name, list_price
FROM new_products p1 JOIN new_products p2 USING (list_price)
WHERE p1.prod_name <> p2.prod_name
ORDER BY 2;

SELECT prod_name, list_price FROM new_products
WHERE list_price IN (SELECT list_price FROM new_products GROUP BY list_price
　HAVING COUNT(list_price) > 1)
ORDER BY 2;

▼解説
設問のような条件を1つのSQL文で問い合わせるには、自己結合もしくは副問合せを使用します。

選択肢を1つずつ確認してみましょう。

SELECT DISTINCT p1.prod_name, list_price
FROM new_products p1 JOIN new_products p2 USING (list_price)
WHERE p1.prod_name <> p2.prod_name
ORDER BY 2;

USING句を使用した自己結合で、同じ価格でかつ製品名が違う行を検索し、DISTINCT句で冗長な行を省いています。正しいSQL文です。

SELECT DISTINCT p1.prod_name, p2.list_price
FROM new_products p1, new_products p2
WHERE p1.prod_name <> p2.prod_name
ORDER BY 2;

Oracle独自の結合構文による自己結合ですが、WHERE句に結合条件(p1.list_price = p2.list_price)を記述していないため、行の全ての組み合わせのデカルト積が返されます。誤ったSQL文です。

SELECT prod_name, list_price FROM new_products
WHERE list_price IN (SELECT list_price FROM new_products GROUP BY list_price)
AND COUNT(list_price) > 1
ORDER BY 2;

副問合せを使用していますが、**WHERE句にグループ関数を記述しているためエラー**となります。誤ったSQL文です。

SELECT prod_name, list_price FROM new_products
WHERE list_price IN (SELECT list_price FROM new_products GROUP BY list_price
HAVING COUNT(list_price) > 1)
ORDER BY 2;

副問合せのGROUP BY句で価格ごとにグループ化し、HAVING句で同価格のデータが複数あるグループに制限して検索しています。正しいSQL文です。

次の問い合わせを実行したところエラーになりました。エラーの原因は?

ただし、MANAGER_IDはDEPARTMENT_IDごとに異なる値が登録されているものとします。

SELECT employee_id, employee_name
FROM employees
WHERE department_id =
(SELECT department_id
　FROM departments
　WHERE manager_id =
　　(SELECT employee_id
　　　FROM employees
　　　WHERE salary =
　　　　(SELECT MAX(salary)
　　　　　FROM employees)));

▼回答
従業員の中で一番多い給与額の従業員が複数いるため

▼解説
副問合せはネストすることが出来ます。
WHERE句に指定する副問合せでは最大255レベルまでのネストが可能です。

ネストした問い合わせでは、内側の問い合わせから実施されます。

設問のSQL文では、最初に一番内側の副問合せである、

SELECT MAX(salary) FROM employees

が実施されます。
この問い合わせでは、全従業員の中で給与額の多い従業員の給与額を1件返します。

次に、

SELECT employee_id
FROM employees
WHERE salary = (内側の問合せの結果)

この問い合わせでは、一番給与額の多い従業員の従業員番号を返します。ここで、一番給与額の多い従業員が複数人いた場合、複数件のデータを返します。

次に、

SELECT department_id
FROM departments
WHERE manager_id = (内側の問合せの結果)

この問い合わせでは、マネージャー番号が一番給与額の多い従業員と一致する部署の部署番号を返します。

もしも内側の問い合わせから複数件のデータが返された場合は、単一業演算子を使用しているためエラーとなります。

また、マネージャー番号が一番給与額の多い従業員と一致しない場合はNULL値を返します。

エラーとならなかった場合は最後に、主問い合わせでマネージャー番号が一番給与額の多い従業員と一致する部署の従業員番号と従業員名が取り出されます。

先の副問合せからNULL値が返された場合は、エラーとはならずデータが1件も表示されません。

したがって、エラーが発生する可能性があるのは、一番給与額の多い従業員が複数人いる場合となります。

単一行副問合せに関する説明として、正しいものは？

▼回答

• 単一行副問合せとANY演算子を組み合わせて使用できる
• 単一行問い合わせとIN演算子を組み合わせて使用出来る

▼解説
**単一行副問合せは主問い合わせに1件のデータを返す副問合せ**です。
副問合せがWHERE句などに指定する条件の一部として使用される場合、単一行副問合せでは単一行演算子を使用して条件を記述します。

ただし、**ANY演算子などの複数行演算子と単一行副問合せを組み合わせて使用してもエラーにはならず正常に実行**されます

UNION演算子

UNION演算子を用いた複合問い合わせでは、2つの問い合わせの結果から重複行を排除して表示します。

以下の問い合わせと同じ結果になるSQL文は?

SELECT prod_name, prod_status FROM new_products
MINUS
SELECT prod_name, prod_status FROM new_products
WHERE prod_status = '生産中止';

▼回答

SELECT prod_name, prod_status FROM new_products
INTERSECT
SELECT prod_name, prod_status FROM new_products
WHERE prod_status != '生産中止';

▼解説
MINUS演算子を用いた複合問い合わせでは、1つ目の問い合わせ結果から2つ目の問い合わせ結果にない行を表示します。

INTERSECT演算子は、2つの問い合わせ結果のうち共通する行だけを戻します。

2つの問い合わせに同一の行がある場合、その行は1度しか表示されません。

集合演算子

**集合演算子に優先順位**はありません。
1つのSQL文に複数の集合演算子が使用されている場合は、SQL文の先頭から順番に複合問い合わせが行われます。

優先順位を明示的に指定したい場合は、()括弧を用いて優先順位を指定します。

エラーがなく実行されるSQL文は?

▼回答

SELECT employee_id FROM employees
UNION
SELECT department_id FROM departments;

SELECT manager_id FROM employees
INTERSECT
SELECT employee_id FROM employees;

SELECT manager_id FROM employees
INTERSECT
SELECT manager_id FROM employees;

▼解説
集合演算子を用いて複合問い合わせを行う場合には、いくつかのガイドラインがあります。

[SELECT句の指定]

• 複合問い合わせの列見出しは1つ目の問い合わせに指定された列名が使用される(それぞれの問い合わせで指定される列名が異なっていても良い)
• 2つの問い合わせでSELECT 句に指定する列や式の数を同数にしなければいけない
• 2つの問い合わせでSELECT句に指定する列や式のデータ型を同じ、もしくは同じデータ型グループにしなければならない(ただし、データサイズは異なっていて良い)
  ※同じデータ型グループとは、CHAR型to
  VARCHAR2型のように文字同士など同じ種類のデータ型のこと

選択肢を1つずつ確認してみましょう。

SELECT employee_id FROM employees
UNION
SELECT department_id FROM departments;

EMPLOYEE_IDとDEPARTMENT_IDは同じデータ型なので正常に実行され、両方の問い合わせの全ての行が表示されます。正しいSQL文です。

SELECT department_id FROM departments
UNION ALL
SELECT department_name FROM departments;

DEPARTMEN_IDとDEPARTMENT_NAMEは数値と文字で異なるデータ型なのでエラーとなります。

部署が「営業」で10年以内に入社した給与が30万円以上の従業員を出力します。どの問い合わせを使用する?

▼回答

SELECT department_id, employee_name, hiredate, salaray FROM employees
WHERE hiredate > SYSDATE - 365 * 10
INTERSECT
SELECT department_id, employee_name, hiredate, salary FROM employees
WHERE salary >= 300000
MINUS
SELECT department_id, employee_name, hiredate, salary FROM employees
WHERE department_id NOT IN (SELECT deaprtment_id FROM departments WHERE departmen_name = '営業');

▼解説
集合演算子を用いて複合問い合わせを行うには、いくつかのガイドラインがあります。

[集合演算子の優先順位]

• 集合演算子には優先順位はない
• 1つのSQL文に複数の集合演算子が使用されている場合は、SQL文の先頭から順番に複合問い合わせが実行される
• 優先順位を明示的に指定したい場合は、()括弧を用いて優先順位を指定する

各条件を以下のように定義して、選択肢を1つずつ確認してみましょう。

条件1 : 部署が「営業」
**条件2 : 10年以内に入社
条件3 : 給与が30万以上

SELECT department_id, employee_name, hiredate, salary FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = '営業')
UNION
SELECT department_id, employee_name, hiredate, salary FROM employees
WHERE hiredate > SYSDATE - 365 * 10
INTERSECT
SELECT department_id, employee_name, hiredate, salary FROM employees
WHERE salary >= 300000;

「条件1 UNION 条件2 INTERSECT 条件3」の順に実行されますが、UNION演算子は条件1と条件2それぞれの結果を統合しますので、誤ったSQL文です。

SELECT department_id, employee_name, hiredate, salary FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = '営業')
INTERSECT
SELECT department_id, employee_name, hiredate, salary FROM employees
WHERE hiredate > SYSDATE - 365 * 10
UNION ALL
SELECT department_id, employee_name, hiredate, salary FROM employees
WHERE salary >= 300000;

「条件1 INTERSECT 条件2 UNION ALL 条件3」の順に実行されますが、UNION ALL演算子は条件1,2の結果を統合しますので、誤ったSQL文です。

SELECT department_id, employee_name, hiredate, salary FROM employees
WHERE hiredate > SYSDATE - 365 * 10
INTERSECT
SELECT department_id, employee_name, hiredate, salary FROM employees
WHERE salary >= 300000
MINUS
SELECT department_id, employee_name, hiredate, salary FROM employees
WHERE department_id NOT IN (SELECT department_id FROM departments WHERE department_name = '営業');

「条件2 INTERSECT 条件3 MINUS 条件1でない」の順に実行され、条件2,3の結果からMINUS演算子で条件1でない問い合わせの結果を除いた結果を表示します。正しいSQL文です。

SELECT department_id, employee_name, hiredate, salary FROM employees
WHERE department_id NOT IN (SELECT department_id FROM departments WHERE department_name = '営業')
MINUS
(SELECT department_id, employee_name, hiredate, salary FROM employees
WHERE hiredate > SYSDATE - 365 * 10
INTERSECT
SELECT department_id, employee_name, hiredate, salary FROM employees
WHERE salary >= 300000);

「条件1でないz MINUS (条件2 INTERSECT 条件3)」のように()括弧で囲まれた部分から順に実行されますが、条件1でない問い合わせ結果からmINUS演算子で条件2,3を除いた結果となり、誤ったSQL文です。

次の順番でSQL文を実行した時の説明として正しいのは？

なお、それぞれのユーザーは、Oracleへの接続には同一アカウントを使用して同じデータにアクセスしているものとします。

ユーザーA：
　　1.SQL> INSERT INTO prod VALUES (11, 'Debussy', 10, SYSDATE, NULL);
　　2.SQL> COMMIT;
　　3.SQL> SELECT name FROM prod WHERE prodid = 11;
　　4.SQL> UPDATE prod SET name = 'Liszt' WHERE prodid = 11;
　ユーザーB：
　　5.SQL> UPDATE prod SET name = 'Chopin' WHERE prodid = 11;
　ユーザーC：
　　6.SQL> SELECT name FROM prod WHERE prodid = 11;
　ユーザーA：
　　7.SQL> COMMIT;
　ユーザーB：
　　8.SQL> SELECT name FROM prod WHERE prodid = 11;
　　9.SQL> COMMIT;
　ユーザーA：
　　10.SQL> SELECT name FROM prod WHERE prodid = 11;

▼回答

• ユーザーAが10.で参照したデータのNAME列の値は"Chopin"である
• ユーザーBの操作5.は待機させられる

▼解説
ユーザーAが変更処理を行うと、変更対象となる行は排他ロックがかけられ、ほかユーザーが変更処理を行おうとすると、ユーザーAの変更処理が確定されるまで待機しなければなりません。

また、変更処理中のデータを他のユーザーが参照すると、他のユーザーは変更処理が確定されるまで、変更後のデータを参照することは出来ません。

設問のSQL文を順に説明します。

1. ユーザーAがINSERT文により、PROD表にデータが1件追加。ただし、INSERT文はDML文に該当し自動コミットされないため、データの追加は確定されていない状態。

2. ユーザーAがCOMMIT文により、それぞれの処理を確定。PROD表のPRODIDが11であるデータのNAMEは"Debussy"になる。

3. ユーザーAがSELECT文でPROD表のPRODIDが11であるデータを参照。このときのNAME列の値は"Debussy"である。

4. ユーザーAがUPDATE文により、PROD表のPRODIDが11であるデータのNAME列の値を変更。ただし、UPDATE文はDML文に該当し自動コミットされないため、データの変更は確定されず。この時UPDATED文の対象の行に排他ロックがかかります。

5. ユーザーBがUPDATE文によりPROD表のORODIDが11であるデータのNAME列の値を変更します。

ただし、PROD表のPRODIDが11である行は、**ユーザーAの4.の操作で排他ロックがかかっているため、4.の操作が確定されるまで待機**させられます。

1. ユーザーCがSELECT文でPROD表のPRODIDが11であるデータを参照します。このときのNAME列の値は4.,5.の変更が確定されていないので"Debussy"になります。

2. ユーザーAがCOMMIT文でそれまでの処理を確定します。この時、排他ロックは解除されます。また、排他ロックが解除されたので、ユーザーB.の操作が実行され余す。

3. ユーザーBがSELECT文でPROD表のPRODIDが11であるデータを参照します。この時のNAME列の値は"Chopin"です。自身が行った変更は、確定前でも変更後のデータを参照出来ます。

4. ユーザーBがCOMMIT文により、それまでの処理を確定します。

5. PROD表のPRODIDが11であえるデータのNAME列の値は"Choplin"になります。

6. ユーザーAがSELECT文でPRODUCT表のPRODIDが11であるデータを参照します。このときのNAME列の値はユーザーBの5.の変更が9.により確定されるので"Chopin"です。

PROD表と同じ構造を持つPROD2表を作成し、PROD表のデータをコピーしました。正常に実行されるSQL文は?

INSERT INTO prod2(prodid, name, category) (SELECT prodid, name, category FROM prod);

INSERT INTO prod2 SELECT * FROM prod;

▼解説
副問合せを使用してデータの追加を行うことも出来ます。
その場合、次のように記述します。

INSERT INTO 表名1[(列名[, 列名...])]
(SELECT 列名 [, 列名...] FROM 表名2 [WHERE 条件]);

なお、副問合せの部分を囲む()は必須では有りません。

副問合せを使用したINSERT文では、VALUES句は使用できません！

INSERT句に指定する列のリストと、副問合せのSELECT句に指定する列のリストは同数かつ同じ順番で指定します。

また、INSERT句の列のリストは省略可能ですが、省略する場合、副問合せのSELECT句の列のリストには、データを追加する表の全ての列を表の列構成の順番で指定しなければなりません。

STARTDATE列の値が"2003年4月1日"以降である行を削除するSQL文は？

▼回答

DELETE FROM prod WHERE startdate >= '03-04-01';

▼解説
**表のデータを削除するには、DELETE文を使用**します。
ある条件に合致したデータだけを削除する場合は、DELETE文にWHERE句を指定して削除する行の条件を指定します。

次のSQL文を実行するとどうなる？

ただし、現在コミットしていないトランザクションはないものとする

DELETE FROM employees;

▼回答
表のデータが削除されるが、ロールバックすることが出来る

▼解説
DELETE文は表のデータを削除しますが、表の構造は削除しません。
また、**DELETE文はDML文ですので、COMMIT文で操作が確定されるまではROLLBACK文で操作の取消が可能**です。

次のSQL文の実行結果として正しいのは?

UPDATE (SELECT department_id, employee_id FROM employees)
SET salary = (SELECT MIN(salary)FROM employees)
WHERE department_id
IN (SELECT department_id FROM departments);

▼回答
UPDATE句の副問合せのSELECT句に指定された列しか変更できないため、エラーとなる

▼解説
UPDATE文では、更新の対象となる表を指定する際、表名ではなく副問合せを使用することが出来ます。

表名の代わりに副問合せを指定したUPDATE文では、副問合せで取り出された列しかSET句に指定することが出来ません。

設問のSQL文では、UPDATE句の副問合せでEMPLOYEES表のDEPARTMENT_ID列とEMPLOYEE_ID列が取り出されるので、SET句にはこの2つの列しか指定することが出来ませんが、SET句にSALARY列を指定しているためエラーとなります。

次の順番でSQL文を実行したときの説明として正しいものは?

なお、それぞれのユーザーはOracleへの接続には同一アカウントを使用して同じデータにアクセスしているものとする。

　ユーザーA：
　　SQL> SELECT d.department_name, e.employee_name
　　 2 FROM departments d JOIN employees e USING (department_id)
　　 3 FOR UPDATE OF e.employee_name NOWAIT;　…　①
　ユーザーB：
　　SQL> SELECT employee_id, employee_name FROM employees
　　 2 FOR UPDATE NOWAIT;　…　②
　　SQL> SELECT department_id, department_name FROM departments
　　 2 FOR UPDATE NOWAIT;　…　③
　ユーザーA：
　　SQL> UPDATE employees SET salary = 350000 WHERE employee_id = 1020;
　　SQL> COMMIT;

▼回答

• ①を実行すると、EMPLOYEES表の該当する行に排他ロックがかかる
• ②を実行するとエラーになる
• ③を実行すると検索結果が表示される

▼解説
Oracleサーバのデフォルト動作では、SELECT文の実行時にはロックはかかりません。

しかし、座席予約のシステムなど場合によってはロックをかけることが必要な場面もあります。

FOR UPDATE句は
①SELECT文のFOR UPDATE句にOF 表名.列名オプジョンが指定されています。EMPLOYEES表のEMPLOYEE_NAMEが含まれる行にのみ排他ロックが掛けられます。

座席確認時の矛盾を防止するためにはSELECT文のFOR UPDATE句を使用します。

**FOR UPDATE句を使用すると、SELECT文の実行時に対象の行に行レベルの排他ロックをかける**ことができます。

SELECT 列名[,列名...]
FROM 表名
[WHERE 条件]
FOR UPDATE [OF 表名.列名][NOWAIT | WAIT 秒数]
[ORDER BY 列名[, 列名]]

②:①で**ユーザーAが排他ロックを掛けているため、ユーザーBはSELECT文にFOR UPDATE句を指定しても排他ロックをかけることはできません。さらにNOWAITオプションを指定しているので、SELECT文実行後、直ちにエラー**となります。

③:DEPARTMENTS表には排他ロックは掛けられないので、検索後該当する行が表示されます。

次の問い合わせの説明として正しいものは?

SELECT employee_id, employee_name, hiredate
FROM employees
WHERE department_id IN (1, 2, 3)
FOR UPDATE NOWAIT;

▼回答
検索対象の行が他のユーザーにロックされている場合はエラーとなる

▼解説
**SELECT文にFOR UPDATE句を指定すると、SELECT文で取り出される行に排他ロックを掛ける**ことができます。

この時、SELECT文で取り出される行が別のセッションですでにロックされていると、別のセッションのロックが解除されるまで、SELECT文は待機しますが、NOWAITオプションを指定すると、待機せずにすぐにエラーを返します。

設問のSELECT文では、FOR UPDATE句にNO WAITオプションが指定されているので、SELECT文で取り出される行がすでに別のセッションで排他ロックを掛けられている場合は、待機せずにエラーを返します。

どのSQLを実行する？

EMPLOYEE_ID列が1015である従業員のSALARY列とCOMMISSION列の値を変更します。

SALARY列はEMPLOYEE_IDが1008の従業員の1.1倍に、COMMISSION列はEMPLOYEE_ID列が1011の従業員と同じ値に変更するには、どのSQL文を実行しますか?

▼回答

UPDATE employees SET salary = (SELECT salary * 1.1 FROM employees WHERE employee_id = 1008), commission = (SELECT commission FROM employees WHERE employee_id = 1011) WHERE employee_id = 1015;

▼解説
他のデータに基づいて表の値を更新する場合は、"副問合せ"を使用してデータを更新します。

SALARY列、COMMISSION列はそれぞれ異なるデータに基づいて更新するため、SALARY列の更新に使用する副問合せとCOMMISSION列の更新に使用する副問合せを用意しなければなりません。

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

UPDATE employees SET (salary, commission) = (SELECT salary * 1.1, commission FROM employees WHERE employee_id = 1008 OR employee_id = 1011), WHERE employee_id = 1015;

SALARY列、COMMISSION列はそれぞれ異なるデータに基づいて更新するため、共通の副問合せで更新すると期待する値に更新することが出来ません。

また、副問合せで2行のデータが返されるため、エラーとなります。

2008年4月1日以降に入社した従業員のMANAGER_ID列の値を1005に変更するSQL文として正しいものは？

UPDATE (SELECT manager_id, hiredate FROM employees) SET manager_id = 1005 WHERE hiredate >= '08-04-01';

UPDATE employees SET manager_id = 1005 WHERE hiredate >= '08-04-01';

▼解説
表名の代わりに副問合せを指定したUPDATE文では、副問合せのSELECT句に指定した列しか更新することができません。

また、**表名の代わりに副問合せを指定したUPDATE文にWHERE句を指定する場合も、副問い合わせのSELECT句に指定した列に関する条件しか指定出来ません**ので注意しましょう。

PROD表の全てのデータを削除するには、どのSQL分を実行する?

▼回答

TRUNCATE TABLE prod;
DELETE FROM prod;
DELETE prod;

▼解説
**表のすべてのデータを削除するには、DELETE文を削除する行の条件を指定せずに実行するか、TRUNCATE文を実行**します。

**DELETE文のFROMキーワードは省略が可能**です。

次の順番でSQL文を実行。次のSQL文を実行するとどうなる？

　1. SQL> INSERT INTO prod VALUES (11, 'Debussy', 10, SYSDATE, NULL);
　2. SQL> SAVEPOINT a;
　3. SQL> UPDATE prod SET name = 'Chopin' WHERE prodid = 11;
　4. SQL> ROLLBACK TO SAVEPOINT a;
　5. SQL> DELETE prod WHERE prodid = 11;
　6. SQL> COMMIT;

　7. SQL> ROLLBACK TO SAVEPOINT a;

▼回答
エラーとなる

▼解説
6. COMMIT文により、トランザクション内の処理が確定され、作成したセーブポイントはすべて破棄されます。したがって、7. ROLLBAXCK TO SAVEPOINT文を実行したときにはセーブポイントaは破棄されているのでエラーとなります。

PROD表と同じ構造をもつPROD2表を作成し、以下のSQL文を実行。実行結果について正しいものは？

INSERT INTO prod2
VALUES (1, (SELECT name FROM prod WHERE prodid = 1),
(SELECT category FROM prod WHERE prodid = 1), SYSDATE, NULL);

▼回答
正常に実行される

▼解説
1つの列の1行のみを返す副問合せは、INSERT文のVALUES句の中でも使用できます。

次のSQL文の説明として正しいものは？

TRUNCATE TABLE employees;

▼回答

• 表のデータが全て削除される
• 表が使用していた領域が開放される(TRUNCATE文で索引は削除されない)

排他ロックに関する説明として正しいものは？

▼回答
INSERT,UPDATE,DELETE文実行時、Oracleサーバーは排他ロックを掛ける

▼解説
Oracle Databaseは複数のユーザーが同時に同じ行に対して変更処理(INSERT,UPDATE,DELETE)を行った場合に、データの矛盾が生じないよう、行毎に排他ロックをかけて変更処理を行います。

排他ロックがかかっている行に対して変更処理を行おうとすると、排他ロックが解除されるまで待機させられます。

**排他ロックはトランザクションの終了時に解除**されます。

PROD表と同じ構造を持つPROD2表を作成し、次のSQL文を実行したところ、エラーとなりました。エラーの原因は？

INSERT INTO PROD2
SELECT name, category, prodid, startdate, enddate
FROM prod
WHERE prodid < 50;

▼回答

• 副問合せの列のリストがPROD2表の列構成の順番と異なっているため
• INSERT句に列のリストが副問合せの順番で指定されていないため

▼解説
INSERT句の列のリストを省略する場合、**副問合せのSELECT句の列のリストには、データを追加する表のすべての列を表の列構成の順番で指定**しなければなりません。

MERGE文の説明として正しいものは？

▼回答

• DML文に分類される
• 行の挿入と更新を同時に行える

▼解説
**MERGE文は異なる表の行をマージできるDML文**です。

1つのMERGE文で、該当する行があればUPDATE、なければINSERTというように、行の挿入と更新を同時に行えます。

MERGE文は次のように使用します。

MERGE INTO 表名1 [表別名]
USING 表名2 | 副問合せ [表別名]
ON (結合条件)
WHEN MATCHED THEN
UPDATE SET 列名 = 値,///
WHEN NOT MATCHED THEN 
INSERT [(列名,...)]
VALUES(値,...)

スキーマオブジェクト

USER_CONS_COLUMNSビューの説明として正しいのは？

▼回答
ユーザー所有の成約が定義されている列を表示

▼解説
**USER_CONS_COLUMNSビューはビューにアクセスするデータベースユーザーが所有しており、成約が定義されている列を表示するデータ・ディクショナリ・ビュー**です。

データ・ディクショナリ・ビューとは、データベース内のオブジェクトやユーザー、権限などに関する様々な情報が格納されているが直接アクセスできないデータ・ディクショナリ表を参照するためのものです。

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

• **ユーザーがアクセスできる、【成約が定義されている全ての"列"】を表示
  →ALL_CONS_COLUMSNビュー**の説明です。

• **ユーザー【所有の表の成約】を表示
  →USER_CONSTRAINTSビュー**の説明です。

• **ユーザーが【アクセスできる表の成約を表示】**する
  →ALL_CONSTRAINTSビューの説明です。

※アクセスできるのは接頭辞に【ALL】がついていて、ユーザー所有のものは接頭辞が【USER】だよ！！！

次のSQL文の実行結果として正しい記述は？

CREATE SEQUENCE seq_id
MAXVALUE 100;

CREATE TABLE emp
(
　id NUMBER(3) DEFAULT seq_id.NEXTVAL PRIMARY KEY,　　 
  name VARCHAR2(10),
　hiredate DATE DEFAULT SYSDATE
);

▼回答
正常表が作成される

▼解説
Oracle 12cより、**CREATE TABLE文やALTER TABLE文の"デフォルト値"**にNEXTVAL疑似列とCURRVAL疑似列を指定出来るようになりました。

**新しい番号(順序値)を参照するには「順序名.NEXTVAL」を指定します。
また、使用しているセッションで最後に使用した順序番号を確認するには「順序名.CURRVAL」**を指定します。

※疑似列とは
実際には表に登録されている列ではないが、列を参照した際と同様にデータを返す項目を示すキーワードのこと。

※Oracle 11gまでは、列のデフォルト値にNEXTVAL疑似列とCURRVAL疑似列を指定できませんでした。

また、順序の作成時に指定できる主なオプションとして、

• START WITH : 最初に生成される順序の初期値
• INCREMENT BY : 順序の増分値(いくつずつ増やすか)

以下の例では、ORDERS表用の順序を作成し、それをINSERT文の中で使用している。

CREATE SEQUENCE s_ord_ordno START WITH 14 INCREMENT BY 1;
// 順序が作成されました
INSERT INTO (ordno,custno) VALUES (s_ord_ordno.NEXTVAL, 1000);

NEXTVAL疑似列、　CURRVAL疑似列は以下では利用出来ません。
※あくまで、デフォルト値として使う！！

• SELECT文、UPDATE文、DELETE文内の副問合せ
• ビューのSELECT句
• DISTINCTキーワードが指定されたSELECT文
• GROUP BY句、ORDER BY句、HAVING句を持つSELECT文
• 集合演算子UNION、INTERSECT、MINUSによって別のSELECT文と結合されているSELECT文
• SELECT文のWHERE句
• CHECK成約の条件

順序に関する説明として正しいのは？

▼回答

• 初期値のデフォルトは1である
• 増分値を指定することが出来る

▼解説
順序を生成する際に指定可能なオプションは以下です。

パブリックシノニムを作成するSQL文は？

▼回答

CREATE PUBLIC SYNONYM dept FOR departments;

▼解説
**シノニムは別名を意味**する。
オブジェクトの別名を表すスキーマオブジェクトだよ！

シノニムには、**作成したユーザーだけが参照できる「プライベートシノニム」とすべてのユーザーが参照できる「パブリックシノニム」の2種類**があります。

パブリックシノニムはCREATE PUBLIC SYNONYM権限を持つユーザーによって作成されます。

シノニム(別名)に関する説明として誤っているものは？

▼回答
(誤)パブリックシノニムとプライベートシノニムではパブリックシノニムが優先される　→**パブリックシノニムが優先**される！

▼解説
シノニムはオブジェクトの別名を表すスキーマ・オブジェクトです。
シノニムには、作成したユーザーだけが使用できるプライベートシノニムと全てのユーザーが使用できるパブリックシノニムの2種類があります。

プライベートシノニムとパブリックシノニムで同じ名前のシノニムを作成できますが、**プライベートシノニムとパブリックシノニムで同名のシノニムがある場合は、"プライベートシノニムが優先"**されます。

順序に関する説明として、正しいものは？

▼回答

• 複数のユーザーで共有可能
• **欠番が発生する場合**もある
• 1つの順序を複数の表で使用できる

▼解説
順序は複数のユーザーで共有可能なため、複数のユーザーが同じ順序で順序値を生成した場合、常に全体を通して一意な値を生成します。

また、順序は表と関連付けられるものではないので、複数の表で1つの順序を使用することもできます。

なお、順序は連番が保証されているわけではなく、ロールバックが発生した場合などに欠番が生じることもあります。

シノニムに関する説明として正しいものは？

▼回答
表に定義されたシノニムを削除しても、表に影響はない

▼解説
シノニムを削除した場合、シノニムが参照していた表自体に影響は有りません。

また、シノニムが作成されている表を削除しても、シノニムは削除されません。

ビューに関する説明として誤っているものは？

▼回答
問い合わせのパフォーマンスが向上する

▼解説
ビューを利用する目的は次のとおりです。

• データへのアクセス制御
• 複雑なSQL文の簡素化
• データの独立性を確保
• 同じデータを異なる視点で表示

**ビューとは、1つまたは複数の表や他のビューを元にして作成する仮想的な表**のことです。

ビューには実データは含まれません。
ビューに対する問い合わせを実行すると、Oracleサーバはビューのもととなっている実表へ問い合わせ処理を行います。

順序に関する説明として、正しいものは？

▼回答

• 順序を削除する場合は、DROP SEQUENCE文で削除
• **セッション開始後、CURVAL疑似列を参照する前にNEXRVAL疑似列を参照**しなければならない

▼解説
順序はCREATE SWQUENCE文で作成し、ALTER SEQUENCE文で変更、DROP SEQUENCE文で削除します。

作成した順序から新しい順序値を取得したり、現在の順序値を確認するためには、NEXTVAL疑似列、CURRVAL疑似列を参照します。

ただし、CURRCAL疑似列(セッションで最後に参照した順序番号)は、順序生成後似一度NEXTVAL疑似列(新しい番号(順序値)を参照した順序番号)を参照してから参照しなければなりません。

索引に関する説明として正しいものは？

▼回答

• **DML文の実行時、索引がメンテナンス**される
• 表のデータへのアクセスを高速化する
• **索引を設定している表ではROWIDを使用して検索**する

▼解説
**索引はデータの検索を高速化するスキーマ・オブジェクト**です。
**表の列に索引が設定されると、"索引が設定された列の値"とその"物理的な格納場所(ROWID)を登録"**します。

索引が設定されていない表でデータを検索する場合、問い合わせの条件に従って、表の先頭のデータから1行ずつ検索していくため、データが大量にある場合には相当の時間がかかります。

しかし、索引を設定している表では、ROWIDを使用してデータを検索するため、大量のデータの中からでも高速に目的のデータを探すことが出来ます。

また、**表に対してDML文を実行する度に、表に設定された索引はメンテナンス(必要あれば更新)**されます。

また、FORIGN KEY成約を定義すると自動的に索引が作成されるのではなく、**PRIMARY KEY成約またはUNIQUE成約の定義時に一位索引が自動的に作成**されます。

DDL

次のSQL文のうち、エラーとなるものは？

▼回答

CREATE TABLE temp
(
　id NUMBER(2) CONSTRAINT id_pk PRIMARY KEY,
　name VARCHAR2(10) CONSTRAINT name_pk PRIMARY KEY,
　birth DATE
);

▼解説
PRIMARY KEY制約は1つの表に1つしか定義することができません。
複数の列に個別にPRIMARY KEY制約を定義することは出来ません。

また、**複数の列の組み合わせに対してPRIMARY KEY制約を定義する場合は、表レベルで定義**します。

// 表レベル制約の構文(カンマ区切りで列定義が先、制約はまとめて一番最後)
CREATE TABLE temp
(
 id NUMBER(2),
 name VARCHAR2(10),
 CONSTRAINT temp_pk PRIMARY KEY(id, name)
);

なお、**PRIMARY KEY_UNIQUE制約を定義すると、自動的に制約と同じ名前の一位索引が作成**されます。

[索引の確認方法]
ログインユーザーが所有する索引(自動的に作成された索引含む)の情報を調べるには、**USER_IND_COLUMNSデータ・ディクショナリを参照**します。

USER_IND_COLUMNSデータ・ディクショナリには索引名や索引を作成した表名、列名などの情報が登録されています。

オブジェクト名として使用できるものはどれ？

▼回答

• EMP_
• Dept

▼解説
オブジェクト名は以下の命名規則に従う必要があります。

[オブジェクトの命名規則]

• オブジェクト名は30バイト以下
• 使用できる文字は、0〜9、A〜Z、a〜z(日本語環境の場合は漢字、ひらがな、カタカナも使用)
• 使用できる記号は**「_」「#」「$」**の3つ
• オブジェクト名の先頭の文字は数字、記号以外の文字
• Oracle予約語は使用できない

また、同一スキーマでは重複するオブジェクト名は使用できません。
また、アルファベットの大文字と小文字は区別されません。

TIMESTAMP型の説明として正しいものは？

▼回答
TIMESTAMP型は秒の小数点以下の値も格納できる

▼解説
**TIMESTAMP型はDATE型を拡張したデータ型で、世紀、年、月、日、時、分、秒に加え、小数点以下の値を格納**することが出来ます。

TIMESTAMP型の列に値を格納するためには、日付リテラルを使用するか、**文字列や数値をTO_TIMESTAMP関数でTIMESTAMP型の値に変換**します。

また、TIMESTAMP型には2種類のバリエーションがあります。

• TIMESTAMP WITH TIMEZONE : タムゾーンの時差を含むことができる。タイムゾーンの時差は列の一部として格納され、表示される**(+09:00と表示)**
• TIMESTAMP WITH LOCAL TIMEZONE : タイムゾーンの時差を含むことが出来る。データ取得時にローカルセッションのタイムゾーンの値で表示される。(デフォルトで日付だけ表示)

次のSQL文の実行結果として正しいものは？

SELECT INTERVAL '200' MONTH, INTERVAL '50-11' YEAR TO MONTH,　INTERVAL '4 12:30:10.1234567' DAY TO SECOND
FROM dual;

▼回答

+16-08 +50-11 +04 12:30:10.123457

▼解説
期間を表す値を記述するには、期間リテラルの書式に基づいて記述します。

設問のINTERVAL '200' MONTHは200ヶ月の意味なので、16年8ヶ月を表すINTERVAL '16-8' YEAR TO MONTHと同義です。

なお、期間データ型には書式モデルはありません。

バイナリデータを格納するデータ型として正しいものは？

• BLOB(4GB)
• BFILE(4GB)
• LONG RAW(2GB)
• RAW(2,000バイト)

▼解説
バイナリデータを扱う主なデータ型は以下です。
基本的に可変長のデータ型で、BFILEだけ読み取り専用のデータ型です！

• RAW : 最大2,000バイトまで格納できる可変長のデータ型
• LONG RAW : 最大2GBまで格納できる可変長のデータ型
• BLOB : 最大4GBまで格納できる可変長データ型
• BFILE : 最大4GBまで格納できる、**読みとり**専用のデータ型
  

なお、LONG RAW型はLONG型と同様に以下の制約があります。

• LONG RAW型の列は1つのひょうに1つだけ定義できる
• LONG RAW型の列には制約は定義できない
• LONG RAW型の列はGROUP BY句やORDER BY句に指定できない
• 副問合せによる表の作成時、LONG RAW型の列はコピーできない

スキーマに関する説明として正しいのは？

▼解説
他のユーザーが所有する表を参照するときは、スキーマ名を接頭辞として参照

▼解説
表やビュー等のスキーマ・オブジェクトは必ずいずれかのユーザーに所要されています。

**スキーマとは、オブジェクトの所有者を表す論理的な概念**です。

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

• オブジェクトを格納する領域である
  →**オブジェクトを格納する領域を記憶域**といいます。
  記憶域はスキーマ・オブジェクトではありません。

• スキーマが異なっていても、同じ表名は使用できない
  →表名はスキーマ内で一意でなければなりませんが、異なるスキーマは同じ表名を使用することができます。

▼参考
データベースに格納できる表やビューなどを総称してデータベース・オブジェクトと言います。データベース・オブジェクトは必ずいずれかのユーザーに所有されており、スキーマオブジェクトとも呼ばれます。

[スキーマ・オブジェクト]

• 表 : データを格納
• 索引 : データへ高速にアクセスするために使用
• ビュー : 1つ以上の表を元にして作成する仮想的な表
• シノニム : オブジェクトの別名
• 順序 : 一意な整数値を生成
  

ALTER TABLE文のSET UNUSED句について正しいのは?

回答

• UNUSEDにした列の索引と制約は削除
• UNUSEDにした列はALTER TABLE ...DROP UNUSED COLUMNSコマンドで削除する
• UNUSEDにした列を含むビューは無効になる

▼解説
ALTER TABLE文で既存の表の列を削除出来ますが、列の削除中はひょうにロックがかかり、列に多くのデータが含まれている場合は削除に時間がかかります。

多くのユーザーがデータベースを利用する時間帯に負荷の高い削除処理を行いたくない場合、削除したい列にUNUSEDマークを設定して未使用にできます。

ALTER TABLE 表名 SET UNUSED (列名[, 列名...])
[CASCADE CONSTRAINTS];

※1つの列のみUNUSEDにする場合は、次の構文も使用できる。

ALTER TABLE 表名 SET UNUSED COLUMN　列名
[CASCADE CONSTRAINTS];

**※CASCADE CONSTRAINTSとは
削除する列を参照する外部キー制約や全ての複数列制約を削除**できます。

他の表の列や対象の表で他の列が参照している制約がある場合は、
CASCADE CONSTRAINTSを指定する必要があります。

SET UNUSED句に関しては、以下の注意事項があります。

• UNUSEDに設定した列は戻せず、DESCRIBEコマンドなどで列名やデータ型を確認できなくなる
• UNUSEDにした列に作成された**索引や制約は削除**される
• UNUSEDにした列と同じ名前の列を表に追加できる
• **UNUSEDにした列の表名と列数は「USER_UNUSED_COL_TABS」データディクショナリで確認**できる
• 表に対して作成したシノニムの再作成は必要ない(シノニムは正常にアクセスできる**)
• UNUSEDにした列を含む**ビューは無効**になる

ALTER TABLE文での列の削除について、正しい記述は？

• 複数の列を同時に削除
• 削除した列は戻せない
• 参照整合性制約の親キー列はCASCADEオプションを指定する

▼解説
ALTER TABLE文で既存の表の列の削除を行えます。

ALTER TABLE 表名 DROP (列名[, 列名...])
[CASCADE CONSTRAINTS];

※1つの列のみ削除する場合は、次の構文も使用できます。

ALTER TABLE 表名 DROP COLUMN 列名
[CASCADE CONSTRAINTS];

[列の削除に関する注意事項]

• 削除した列は戻せない
• 表には最低1つの列を残す必要がある
• **他から参照される参照整合性制約の"親表の列"はCASCADE CONSTRAINTSオプションを指定**する必要がある
• 列に多くのデータが含まれている場合は削除に時間がかかる

EMP7表に対して以下の順番でSQL文を実行した後の正しい説明は?

① INSERT INTO emp7 VALUES (2000, '中山浩二', SYSDATE, NULL, 5);

② ALTER TABLE emp7 MODIFY (salary DEFAULT 200000);

③ INSERT INTO emp7 (employee_id, employee_name, hiredate, department_id)
　　VALUES (2001, '長谷川智子', SYSDATE, 2);

▼回答
③で挿入された行のSALARY列の値が200,000となる

▼解説
ALTER TABLE文では、既存の列へデフォルト値を設定できます。
**この変更が反映されるのは、ALTER TABLE文のあとに挿入される行だけ**です。

PROD3表には6件のデータが登録。新たにSTATUS列を追加する以下のSQL文を実行。正しい結果は？

ALTER TABLE prod3
ADD status VARCHAR2(20) DEFAULT 'FOR SALE' NOT NULL;

▼回答
正常に列が追加され、既存の行のSTATUS列にデフォルト値が入る

▼解説
**ALTER TABLE文で、既存の表へ新しい列を追加**出来ます。

ALTER TABLE 表名 ADD
(列名 データ型(サイズ)　[DEFAULT 値] [[CONSTRAINT 制約名 ]制約タイプ])

表に既存のデータが存在する場合、新しく追加された列にはデフォルトでNULL値が設定されます。

表が空でない場合、NOT NULL制約を定義した列を追加しようとするとエラーとなりますが、列の追加時にNULL値以外のデフォルト値を指定することで、既存のデータの列にもデフォルト値が設定されます。

次のSQL文を実行しました。

　CREATE TABLE parent
　(
　　id NUMBER(2) CONSTRAINT pid_pk PRIMARY KEY,
　　dept_name VARCHAR2(10)
　);

　CREATE TABLE child
　(
　　id NUMBER(2) CONSTRAINT cid_pk PRIMARY KEY,
　　name VARCHAR2(10) CONSTRAINT cname_uq UNIQUE,
　　deptid NUMBER(2) CONSTRAINT dept_fk REFERENCES parent (id) ON DELETE CASCADE
　);

　INSERT INTO parent VALUES (1, 'parent');
　INSERT INTO child VALUES (10, 'child', 1);

これらの表に対して、正常に実行できるSQL文はどれ？

▼回答

ALTER TABLE child (id);
ALTER TABLE parent DROP COLUMN id CASCADE CONSTRAINTS;

▼解説
設問のSQL文では、CHILD表のdeptID列にPARENT表のIDを参照するFOREIGN KEY制約を定義しています。

その際、ON DELETEオプションを指定していますが、親表の行を削除した場合の動作なので、親表の列の削除には関係ありません。

PARENT表のID列を削除するにはCASCADE CONSTRAINTSオプションが必要です。

他の表や列から参照されていない制約の列はCASCADEオプションなしで削除が可能です。

その際、列に定義されている制約も同時に削除されます。

###FOREIGN KEY制約(外部キー制約)
FOREIGN KEY制約を定義すると、その列には参照先の列に存在する値しか格納できなくなります。

[FOREIGN KEY列レベル制約の構文]
※列レベルで列名は自明なので、FOREIGN KEYキーワードで列名を指定する必要はない

[CONSTRAINT 制約名] REFERENCES 親表名(参照する列名 [,参照する列名 ])

[FOREIGN KEY表レベル制約の構文]

[CONSTRAINT 制約名] FOREIGN KEY(列名[,列名])REFERENCES 親表名(参照する列名 [,参照する列名 ])

[子表に定義]ON DELETE CASCADEとON DELETE SET NULL

FOREIGN KEY制約と「ON DELETE CASCADEキーワード」や「ON DELETE SET NULL」キーワードを組み合わせて定義すると、**親表のデータを削除した場合のデフォルトの動作を次のように変更**できます。

• 指定なし : 子表に親表を参照する行が存在する場合は、親表の参照されている行は削除できない
• ON DELETE CASCADE :子表に親表を参照する行が存在する場合、親表の参照されている行を削除すると、子表の参照している行も削除。列レベル構文には使用できない
• ON DELETE SET NULL:子表に親表を参照する行が存在する場合、親表の参照されている行を削除すると、戸表の参照している行にNULL値が設定される。列レベル構文には使用できない

次のSQL文のうち、エラーとなるものは？

CREATE TABLE temp
(
　id NUMBER(2) CONSTRAINT temp_uq UNIQUE (id, name),
　name VARCHAR2(10) CONSTRAINT name_nn NOT NULL,
　birth DATE
);

CREATE TABLE temp
(
　id NUMBER(2) CONSTRAINT id_uq UNIQUE,
　name VARCHAR2(10) CONSTRAINT name_nn NOT NULL,
　birth DATE,
　CONSTRAINT temp_uq UNIQUE
);

UNIQUE制約は列レベル、表レベルのどちらでも定義できますが、複数の列の組み合わせに対してUNIQUE制約を定義する場合は、表レベルで定義しなければなりません。

また、表レベルで制約を定義する場合は、制約を定義する列を指定しなければなりません。

EMP表の構造を確認してください。

　CREATE TABLE emp
　(
　　id NUMBER(2) PRIMARY KEY,
　　name VARCHAR2(10),
　　birth DATE,
　　salary NUMBER(8),
　　note LONG
　);

次のSQL文のうち、正常に実行されるものはどれ？

SELECT id, name, birth FROM emp ORDER BY birth;

CREATE TABLE emp2 AS SELECT id, salary FROM emp;

▼解説
LONG列には以下の制限があります。

• LONG列は1つの表に1つだけ定義できる
• LONG型の列には制約は定義できない(NULLとNOTNULL制約を除く)
• ORDER BY句とGROUP BY句には指定できない
• 副問合せによる表の作成時、LONG型の列はコピーできない

次のSQL文を実行しました。

　CREATE TABLE parent
　(
　　id NUMBER(2) CONSTRAINT pid_pk PRIMARY KEY,
　　dept_name VARCHAR2(10)
　);

　CREATE TABLE child
　(
　　id NUMBER(2) CONSTRAINT cid_pk PRIMARY KEY,
　　name VARCHAR2(10) CONSTRAINT cname_uq UNIQUE,
　　deptid NUMBER(2) CONSTRAINT dept_fk REFERENCES parent (id)
　);

　INSERT INTO parent VALUES (1, 'parent');
　INSERT INTO child VALUES (10, 'child', 1);

この後、次のSQL文を実行するとエラーになりました。

DELETE FROM parent WHERE id = 1;

上記のDELETE文の前に、どのSQL文を実行すると正常にDELETE文を実行できるようになりますか(2つ選択して下さい)。

▼回答

ALTER TABLE child DISABLE CONSTRAINT dept_fk;
DELETE FROM child WHERE deptid=1;

▼解説
設問のPARENT表のID列は、CHILD表のDEPTID列からFOREIGN KYE制約の親キーとして参照されています。

参照する側のデータ、子レコードが存在する場合、
親レコードを削除しようとするとエラーが発生します。

①先に子コードを削除するか
②FOREIGN KEY制約を無効にする
上記で、親レコードを削除できるようになります。

もしくは、CHILD表のFOREIGN KEY制約にON DELETEオプションを指定して再作成し、親レコード削除時の処理を設定します。

**ALTER TABLE文で既存の表の制約を無効化、もしくは有効化**できます。

ALTER TABLE 表名 DISABLE | ENABLE CONSTRAINT 制約名;

データ型に関する正しい説明は？

▼解説

• LONG型の列には、NULLおよびNOT NULL以外の制約を定義できない
• CHAR(5)に「abc」を入力すると、スペースを加え「abc 」が格納される

その他の選択肢は次のとおりです。

• BFILE型は4GBまでのバイナリデータを格納できますが、読み取り専用のデータ型のため値の変更はできません

• NUMBER(10,3)に123.4567を入力すると小数点第4位が四捨五入され、123.347が格納される

ROWID型の説明として正しいものは？

▼回答
行の一意なアドレスを表す"BASE64文字列"

▼解説
ROWID型は、行の一意なアドレスであるROWID疑似列(実際には列として定義されていない列)から返される値を列に格納するために使用する、BASE64文字型のデータ型です

FOREIGN KEY制約の説明としてただしものは？

▼回答

• FOREIGN KEY制約を定義すると、依存する行の有無に関わらず親表を削除できない
• ON DELETE CASCADE オプションを指定すると、親表の行を削除する際に、親表を参照している子表の行も自動的に削除

副問合せによる表の作成時、新たに作成した表にコピーされるものはどれ？

▼回答

• **データ型
• NOT NULL制約**

▼解説
副問合せによる表の作成時、新たに作成する表の列にデータ型を指定することはできません。

列のデータ型は副問合せにより、自動的に定義されます。

次のSQL文でPARENT行とCHILD表を作成しました。

　CREATE TABLE parent
　(
　　id NUMBER(2) CONSTRAINT pid_pk PRIMARY KEY,
　　dept_name VARCHAR2(10)
　);

　CREATE TABLE child
　(
　　id NUMBER(2) CONSTRAINT cid_pk PRIMARY KEY,
　　name VARCHAR2(10) CONSTRAINT cname_uq UNIQUE,
　　deptid NUMBER(2) CONSTRAINT dept_fk REFERENCES parent (id) ON DELETE SET NULL
　);

次のSQL文の実行結果として正しいものは？

▼回答
エラーとなり、表もデータも削除されない

▼解説
★ON DELETE SET NULLオプションは、列レベル構文には使用できない！

FOREIGN KEY制約の親表として指定された表は、参照されている行がない場合でも削除できなくなります。

親表を削除する場合は、参照している子表を先に削除しなければなりません。

CREATE TABLE文の実行結果として正しいものは？

　CREATE TABLE tmp
　(
　　id#9 NUMBER(2) NOT NULL,
　　name$ VARCHAR2(20),
　　birth_day DATE NOT NULL,
　　age DATE DEFAULT SYSDATE - birth_day,
　　image777 LONG
　);

▼回答
DEFAULTオプションの指定に他の列を使用できないため、エラーとなる！
DEFAULTオプションには式や関数を指定できるが、他の列を参照する式は指定できない！

次のSQL文のうち、正常に実行できるSQL文は？

CREATE TABLE temp
(
　id NUMBER(2) CONSTRAINT id_pk PRIMARY KEY,
　name VARCHAR2(10),
　salary NUMBER(8),
　commission NUMBER(8),
// 表レベルの制約だからOK！
　CONSTRAINT salary_ck CHECK (salary < commission)
);

▼解説
CHECK制約の条件にはWHERE句に指定する条件と同等のものを指定出来ますが、次の指定はできません。

• **CURRVAL/NEXTVAL/LEVEL/ROWNUM疑似列
• SYSDATE,UID,USER,USERENV関数
• 他の行を参照する問い合わせ**

次のSQL文の実行結果として正しいのは?

DROP TABLE prod_table;

▼回答
PROD_TABLE表の構造とデータが削除され、トランザクションが終了

▼解説
DROP TABLE文はDDL文に該当します。
DDL文では、1文で１つのトランザクションとなるため、DROP TABLE文実行後はトランザクションが終了します。

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

• 表を削除してもビューは削除されません！

文字列を扱うデータ型の説明として、誤っているものは?

▼回答

• [誤]文字型の列を定義する場合は、必ずサイズを指定しなければならない
• [誤]VARCHAR2型とCHAR型に違いはない

▼解説
文字列を扱うデータ型には、CHAR型、VARCHAR2型、LONG型、CLOB型等のデータ型あります。

[文字データを格納できる主なデータ型]

• CHAR型 : 最大2,000バイトまでの文字列データを格納できる、固定長のデータ型
• VARCHAR2型:最大4,000バイトまでの文字列データを格納できる**、可変長**のデータ型
• LONG型:最大2GBまでの文字データを格納できる可変長のデータ型
• CLOB型:最大4GBまでの文字データを格納できるデータ型
• NCLOB型:最大４GBまでの**Unicode文字データを格納できるデータ**型

ALTER TABLE文で実行できる項目はどれ？

▼回答

• 制約の追加
• 列の追加
• データ型の変更

▼解説
**"既存の表の定義"を変更するにはALTER TABLE文**を使用します。

• 新しい列の追加
• 既存の列のデータ型変更
• 既存の列へのデフォルト値の設定
• 既存の列への制約の定義
• 既存の列の削除
• 表のモード変更(読み取り/書き込みモード、読み取り専用モード)

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

• 表の削除はDROP TABLE文で行います
• データの削除はDELETE文またはTRUNCATE文で行います

NUMBER型の説明として誤っているのは?

▼回答

• [誤]データ型がNUMBER(5,2)の列に12.345を入力すると、12.34が格納される

▼解説
データ型がNUMBER(5,2)の列に12.345を入力すると、小数点第三位が四捨五入され、12.35が格納されます。

▼参考
NUMBER型は数値データを格納するために使用するデータ型です。

列名 NUMBER [(最大精度[,位取り])]

最大精度 : 格納する数値の全体桁数(1〜38桁)
位取り：小数点以下の桁数(-84〜127)。負の値が指定された場合は、整数部の丸める桁数を指定したことになる

位取りを省略した場合は、NUMBER(最大桁数,0)と同じこと！

次のSQL文を実行しました。

　CREATE TABLE parent
　(
　　id NUMBER(2) CONSTRAINT pid_pk PRIMARY KEY,
　　dept_name VARCHAR2(10)
　);

　CREATE TABLE child
　(
　　id NUMBER(2) CONSTRAINT cid_pk PRIMARY KEY,
　　name VARCHAR2(10) CONSTRAINT cname_uq UNIQUE,
　　deptid NUMBER(2) CONSTRAINT dept_fk REFERENCES parent (id) ON DELETE CASCADE
　);

　INSERT INTO parent VALUES (1, 'parent');
　INSERT INTO child VALUES (10, 'child', 1);

この後、正常に実行できるSQL文はどれ？

▼回答

DELETE FROM parent;

▼解説
PARENT表はCHILD表のFOREIGN KEY制約の親表として参照されています。

この場合、参照されている行がない場合でも、子表より先に親表を削除できません。

DROP TABLE文はエラーとなります。

しかしデータの削除に関しては、FOREIGN KEY制約にON DELETEオプションを指定することによって、親表の表を削除した場合に子表の行をどのようにするかを指定できます。

設問のCHILD表のFOREIGN　KEY制約には「ON DELETE CASCADE」が指定されているので、PARENT表の行を削除する子表の行も削除されます。

よって、DELETE文は正常に実行できます。

TRUNCATE TABLE文に関しては、データが存在した場合は、FOREIGN KEY制約の親表を切り捨てる事はできません。

表の削除に関する説明として正しいのは？

▼回答

• ゴミ箱に移動した表を復元することができる
• 表を削除すると、表に定義されている制約や索引も同時に削除される
• 表の削除はDROP ANY TABLE権限を持つすべてのユーザーが表を削除できる

次のSQL文の実行結果として正しいものは？

CREATE TABLE tmp
(
　id NUMBER PRIMARY KEY,
　name VARCHAR2,
　birth_day DATE DEFAULT SYSDATE NOT NULL,
　age NUMBER,
　image777 LONG
);

▼回答
VARCHAR2型にデータ長が指定されていないためエラーとなる

▼解説

• デフォルト値の指定に他の列を指定することはできないが、関数や式は指定できる！
  ※関数が指定できないのはCHECK制約！
• CURRVAL/NEXTVAL/LEVEL/ROWNUM疑似列
• SYSDATE,UID,USER,USERENV関数
• 他の行を参照する問い合わせ

以下のSQL文でEMPLOYEES2表を作成し、データを登録します。

　CREATE TABLE employees2
　(
　　emp_id NUMBER(2) NOT NULL,
　　name VARCHAR2(10) NOT NULL,
　　birth DATE
　);

　INSERT INTO employees2 VALUES(1, 'Tanaka', '1995-05-18');
　INSERT INTO employees2 VALUES(2, 'Tanaka', '1979-12-10');
　INSERT INTO employees2 VALUES(1, 'Yamada', '1999-01-31');

データ登録後に、EMP_ID列にORIMARY KEY制約を定義するとどうなる？
▼回答
既存のデータがPRIMARY KEY制約を満たしていないためエラーとなる

FOREIGN KEY制約での学び

• FOREIGN KEY制約で参照できる親表の列は、PRIMARY KEY制約またはUNIQUE制約が定義されている列だけです！

次のSQL文実行後、EMPLOYEES2表に実施できる行為はどれ？

ALTER TABLE employees2 READ ONLY;

▼回答
表の削除

▼解説
設問のSQL文を実行すると、EMPLOYEES2表は読み取り専用モードに変更されます。

読み取り専用モードの表では、データの追加、更新、削除はできませんが、表の削除は行うことが出来ます。

次のSQL文のうち、正常に実行されるものはどれ？

▼回答

CREATE TABLE table1
(
　cust_id NUMBER(2),
　order_id NUMBER(2),
　qty NUMBER(2) CONSTRAINT qty_ck CHECK (qty BETWEEN 1 AND 20),
　CONSTRAINT id_pk PRIMARY KEY (cust_id, order_id)
);　

以下誤りのSQL文

CREATE TABLE table1
(
　cust_id NUMBER(2) UNIQUE, NOT NULL,
　order_id NUMBER(2),
　qty NUMBER(2) CONSTRAINT qty_ck CHECK (qty BETWEEN 1 AND 20)
);

1つの列に2つ以上の制約を定義する場合は、,(カンマ)で区切るのではなく、スペースで区切ります！

EMPLOYEE表をコピーしてEMPLOYEES表を作成するSQL文として誤っているものは？

▼回答

CREATE TABLE employees2
　AS
SELECT employee_id, employee_name, salary * 12 + commission
FROM employees;

▼解説
副問合せによる表の作成では、**副問合せのSELECT句に計算式や関数を指定する場合は、計算式や関数に"列別名"を指定するか、CREATE TABLE文で"列名"を指定**しなければなりません。

その他

-　**データ型がNUMBER(5)の列に123.45を入力すると、"小数点第一位で四捨五入"され、123が格納**される
-　データ型がNUMBERの列に1.345を入力すると、12.345が格納される