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

算術式を指定できる句

FROM句を除く任意の句で使用できる。
(SELECT/WHERE/GROUPBY/HAVING/ORDERBY句)

次の形式で検索結果を表示させるためにはどの問い合わせを実行？

// ○ : 正解
SELECT '従業員番号 : ' || employee_id || ' 従業員氏名 : ' || employee_name FROM employees;

// ✕ 不正解
SELECT "従業員番号 : " || employee_id || " 従業員氏名: " || employee_name FROM employees;

※文字リテラルは二重引用符(")ではなく、一重引用符(')で囲む
※列別名は二重引用符(")で囲むよ！

列別名を指定したSQL文のうち、エラーがなく実行されるのは？

// ①
SELECT employee_name, salary "Salary($)" FROM employees;

// ②
SELECT employee_name "Employee Name", salary AS Salary FROM employees;

// ③　列別名で大/小文字の区別やスペースを使用する場合は「"」を使うが、通常はリテラル不要
SELECT employee_name 従業員名, salary 給与 FROM employees;

エラーを直せ

SELECT DISTINCT employee_id AS EMPLOYEEID,
employee_name || q'['s Salary is ]' || salary yen
FROM employees;

// 間違い①
「yen」はリテラルなので一重引用符(')で囲まれなければならない

// 間違い②
「yen」を連結演算子で連結する必要がある

▼正解

SELECT DISTINCT employee_id AS EMPLOYEEID, employee_name || q'['s Salary is ]' || salary || 'yen'
　FROM employees;

次のSELECT文のエラー原因はいくつ？

SELECT employee_id ID, employee_name 20EMPNAME, hiredate DATE FROM employees;

// 間違い①
「20EMPNAME」は先頭に数字が使用されているためエラー
// 間違い②
「DATE」はSQLの予約語のためエラー

同じ結果となる正しいSQLは？

SELECT q'(It's Monday today.)' FROM dual;

// 正解①
SELECT 'It''s Monday today.' FROM dual;
// 正解② : q演算子は大文字、小文字どちらで記述してもOK
SELECT Q'$It's Monday today.$' FROM dual;

以下形式で表示させるために実行する問い合わせは？

//　正解①
SELECT employee_id AS "社員No", employee_name AS "社員名", salary AS "給与(月給)" FROM employees;

// 正解②
SELECT employee_id "社員No", employee_name 社員名, salary "給与(月給)" FROM employees;

// 備考
「社員No」では大文字と小文字が使用
「給与(年収)」では記号が使用されているので、列別名を二重引用符(")で囲んで指定する必要あり

エラーの原因は？

SELECT employee_name AS "Name", salary * 12 AS "Sal", Sal + commission AS "Total"
　FROM employees;

▼正解
算術式の中で列別名を使うことはできない

salary列がNULL値だった場合、SELECT文の結果はどうなる？

SELECT employee_name AS "Name", salary * 12 AS "Sal", Sal + commission AS "Total"
　FROM employees;

▼正解
「私の月給はです」
→連結演算子||で文字列とNULL値を連結した結果は文字列となる。
設問ではSALARY列の値がNULL値なので、SALARY列を除いた文字列が連結される。

条件を全て満たす問い合わせは？

<条件>
1.名前(EMPLOYEE_NAME)に「田」もしくは「藤」のいずれかが含まれていないこと
2.入社日(HIREDATE)が2001年10月1日より前か、2007年4月1日以降であること
3.部署(DEPARTMENT_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);

▼間違い

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文の実行結果は？

SELECT employee_id, employee_name, manager_id
FROM employees
WHERE manager_id = NULL;

▼正解
検索結果は1件も表示されない

▼解説
NULL値は特殊な値なので、列の値がNULLである行を検索するにはIS NULL,IS NOT NULLが必要。

NULL値に対して=(符号)などの比較演算子を使用した場合、条件の判定がNULL値となり検索結果は1行も表示されません。(エラーにはならない)

次のSQL文を実行して表示される製品名として正しいものは?

SELECT prod_name
FROM products
WHERE UPPER(prod_name) BETWEEN 'H' AND 'LE';

▼答え

• Lantern
• HighPower_LED_ハンディライト2

▼間違え

• (Wide)LEDシーリングライト
• LEDライト
• led light

▼解説
BETWEEN演算子の下限値、上限値に文字リテラルが指定された場合は、指定された文字列の文字コードの範囲で検索が行われます。

設問の場合は、UPPER(prod_name)で全て大文字に変換された商品名の頭文字が「H」で始まるものから、「LE」という2文字の文字コードの範囲までが検索されます。

ちなみに、このSQL文でUPPER関数を使わないと商品名「Lantern」はヒットしません。

2文字目の小文字の「a」は大文字の全アルファベットより文字コードが大きいからです。

★数字<= 英大文字 <= 英小文字

UPPER関数で大文字「LATERN」にした場合は、最初の二文字「LA」の組み合わせ文字コードが、検索上限値の「LE」より小さいため、検索でヒットします。

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

SELECT department_id, department_name
FROM departments
WHERE department_id NOT IN (1, 3, 5);

▼答え

SELECT department_id, department_name
FROM departments
WHERE department_id != 1 AND department_id != 3 AND department_id != 5;

▼私が間違えた回答

SELECT department_id, department_name
FROM departments
WHERE department_id != 1 OR department_id != 3 OR department_id != 5;

// DEPARTMENT_ID列の値が1以外、または3以外、または5以外の行を取り出すので、1以外で取り出すと3や5も含まれてしまってNG

▼解説
設問のSQL文は、指定された条件により、DEPARTMENT_ID値が1,3,5以外の行が取り出されます。

これは、DEPARTMENT_ID値が1以外かつ3以外かつ5以外ということのため、AND演算子を使用します。

IN()の時はORになるよ！

PRODUCT表の製品名に「HITS_」と「's」が順番で含まれている行を検索する正しい条件は?

▼正解

// 「%」「_」と「シングルクォーテーション」を文字列として扱いたいなら、LIKE演算子と代用引用符qを同時に使うよ！
WHERE prod_name LIKE '%HITSW_%''s%' ESCAPE 'W'
WHERE prod_name LIKE q'(%HITSW_%'s%)' ESCAPE 'W

▼解説
LIKE演算子を使用すると、指定したパターンに一致した行を検索できる。
文字パターンには任意の1文字と一致する「_」や0文字以上の任意の文字列と一致する「%」といったワイルドカードを利用できる。

ワイルドカードをリテラルの一部として使用する場合は、ESCAPEオプションを指定してワイルドカードをリテラルとして使用できるようにしなければなりません。

設問では「HITS_」が含まれている行を検索するので、文字パターンは「'%HITS_%'：となりますが、文字パターンに「""」が含まれているので、**ESCAPEオプションを指定して「」をリテラルとして扱う**必要があります。

さらに**「'S」も含まれるようにしなければなりませんが、一重引用符(')が含まれている**ので、代用引用符q演算子を使用するか、一重引用符(')を2つ記述して、一重引用符(')をリテラルとする。

LIKE演算子と代用引用符q演算子の違い

• LIKE演算子 ESCAPEオプション
  **「_」と「%」**を文字列リテラルとして扱いたい場合

• 代用引用符q演算子
  シングルクォーテーションを文字列として扱いたい場合

LIKE演算子

**文字列が部分一致する行を問い合わせるには、LIKE句を使用**します。
ワイルドカードを使用した比較には、符号(=)ではなくLIKE句を使用します。

CUSTOMERS表のCUST_LAST_NAME列の値が「佐藤」であるデータを検索する条件として正しいものはどれですか

▼回答

WHERE cust_last_name = '佐藤'
WHERE cust_last_name LIKE '佐藤'
WHERE cust_last_name IN ('佐藤')

▼解説
**LIKE演算子でワイルドカードを用いない場合、「=」演算子と同じ**だよ！

次のSQL文の実行結果として表示される列の組み合わせは？

SELECT department_id, employee_id, employee_name, salary, commission, hiredate
　FROM employees
　WHERE department_id = 3
　　OR salary > 400000
　　AND commission <= 1200000
　　OR hiredate > '2008-04-01';

▼回答
B,C,D,E

▼解説
WHERE句に条件が複数指定されている場合は、論理演算子の優先順位に従って条件が評価される。

「AND演算子 > OR演算子」

よって設問の条件は以下のように検索される。

「SALARY列が400000より大きくかつcommission列が1200000以下(無し)」、または「DEPARTMENT_ID列が3(D,E)」、または「HIREDATE列が2008年4月1日より大きい(新しい)(B,C)」となります。

この問い合わせの結果は？

SELECT customer_id, cust_last_name, cust_first_name
FROM customers
WHERE 1 = 5;

▼回答
データは一件も検索されない

2010年4月1日から2011年3月31日までの注文状況をORDERS表から取得するには、どの問い合わせを実行しますか。

SELECT order_id, order_date, order_total FROM ORDERS WHERE order_date BETWEEN '2010-04-01' AND '2011-03-31';

▼解説
BETWEEN演算子の下限値、上限値には文字リテラルや日付リテラルを指定することも出来ます。

その場合は、１重引用符(')で囲まなければなりません。

EMPLOYEES表のDEPARTMENT_IDには1~5の値が登録。EMPLOYEES表のDEPARTMENT_IDが1,2,3の従業員がヒットする条件として正しいものはどれですか(該当するものを全て選択して下さい)。

WHERE department_id IN (1, 2, 3)
WHERE department_id NOT IN (4, 5) // WHERE department_id !=4 AND department_id !=5
WHERE department_id = 1 OR department_id = 2 OR department_id = 3

次の条件式で検索できる商品名は？

WHERE prod_name LIKE '_i%LEDW_%ライト%' ESCAPE 'W'

▼回答

Silver LED_ライト
HighPower_LED_ハンディライト2
Light_LED_ハンディライト(6球)

▼解説
LIKE演算子を使用すると、指定した文字パターンに一致した行を検索できます。

文字パターンには、任意の1文字と一致する「_」や**0文字以上の任意の文字列と一致する「%」**といったワイルドカードを利用できます。

ワイルドカードをリテラルの一部として使用する場合は、ESCAPEオプションを指定してワイルドカードをリテラルとして扱えるようにしなければなりません。

設問では「W」がエスケープ文字として指定されています。
そのため、「W」の直後に来るワイルドカードは、通常のリテラルとして扱われます。

「'i%LEDW%ライト%'」

設問の文字パターンでは、2つ目の「_」がワイルドカードではなく、
文字のアンダーバーとして扱われます。

この文字パターンは、以下のような文字列を表します。

• １文字目　任意の一文字
• 2文字目 「i」
• 任意の文字列(0文字でも可)
• 「LED_」という文字列
• 任意の文字列(0文字でも可)
• 「ライト」という文字列
• 任意の文字列(0文字でも可)

EMPLOYEES表のDEPARTMENT_IDが1以外の従業員を検索する条件として、正しいものを全て選択

WHERE department_id != 1
WHERE department_id ^= 1
WHERE department_id <> 1

次のSQLの実行結果として表示される列の組み合わせとして正しいのは？

SELECT department_id, employee_id, employee_name, salary, commission, hiredate
　FROM employees
　WHERE (department_id = 3
　　OR salary > 400000)
　　AND commission <= 1200000
　　OR hiredate > '2008-04-01';

▼回答
B, C, E

▼解説
WHERE句に条件が複数指定されている場合は、論理演算子の優先順位に従って条件が評価される。

AND演算子とOR演算子では、AND演算子のほうが先に評価されますが、
**()括弧がある場合は、カッコ内の演算子を優先して評価**します。

置き換え変数に関する説明として正しいものは？

• 「&置換変数」と「&&置換変数」の2種類がある
• 変数値を再利用する場合は「&&置換変数」を利用する
• 実行時に条件などの値を指定することができる

▼解説
SQLPlusやSQLDeveloperなどのツールでは、置換変数を利用出来ます。

置き換え変数を利用すると、**WHERE句の条件などに指定する値をSQLの中に直接記述するのではなく、実行時に値を指定できる**ようになります。

単一行関数の説明として正しいのは？

• ネストレベルに制限はない
• 1行ごとに1つの結果を返す

※NOT : 引数には常に列名を指定する(例：UPPER('asami')だと、列名ではなく文字列指定できる！)

PROD表のデータを確認してください。

上記データから、次のような8桁のキャンペーンコードを生成するにはどのSQL文を使用する？

▼回答

SELECT SUBSTR(INITCAP(name), 1, 2) || REPLACE(startdate, '-') "Code"
　FROM prod WHERE name IS NOT NULL;

▼解説
キャンペーンコードを確認すると、NAME列の最初の2文字+STARTDATE列から「-(ハイフン)」を取り除いた数字の組み合わせであることがわかる。

以下の関数を使用できます。
SUBSTR(文字列, m[,n]) : 文字列のm番目からｎ文字分の文字列を返す。

REPLACE(文字列, 変更前文字列 [.変更後文字列]) : 変更後文字列が省略された場合は、文字列から変更前文字列を削除した文字列を返す

ただし、NAME列が「ELLE」の行は「El」と2文字目が小文字になっているため、INITCAP関数で先頭を大文字、2文字目以降を小文字に変換しなければなりません。

以上より、

SELECT SUBSTR(INITCAP(name), 1, 2) || REPLACE(startdate, '-') "Code"
　FROM prod WHERE name IS NOT NULL;

上記が正解となります。

PRODUCT表のデータを確認してください。

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

SELECT TRIM('LED' FROM UPPER(prod_name)) FROM products
WHERE LOWER(prod_name) LIKE 'led%';

▼回答
エラーになる

▼解説
TRIM([LEADING | TRALING | BOTH] [削除文字] FROM 文字列)
または
TRIM(文字列)

LEADING,TRALING,BOTHを省略した場合は、文字列の前後の削除文字が取り除かれます。

削除文字には任意の一文字を指定できますが、文字列は指定できません。

設問のSQL文ではTRIM('LED' FROM UPPER (prod_name))で、大文字に変換したPROD_NAME列のデータの前後から'LED'という文字列を削除しようとしていますが、削除文字が1文字ではないのでエラーとなる。

日付値の演算について正しいものは？(日付書式:RR-M-DD)

▼回答
TO_DATE('12-01-01') + 12/24の結果は"12-01-01の正午"である

▼解説
**日付値に対して、「数値/24」を加算したり減算した場合は、数値を「時間数」として加算したり減算**したりします。

設問の**「12/2４」であれば、12時間として演算**します。

また、**TO_DATE()関数**は文字リテラルを日付値に変換します。

**引数に時刻が指定されていない場合は午前0時0分0秒が設定**されます。

以上より、TO_DATE('12-01-01') + 12/24の結果は"12-01-01の正午"となります。

SQL関数である単一行関数のタイプとして正しいものは？

• **汎用関数(引数は任意のデータ型とし、NULL値に関する処理**を行う)
• 日付関数
• 変換関数

▼解説

論理関数 / データ関数のような関数はありません。

SQL関数の結果として正しいのは？

INITCAP('merry x''mas')

▼回答
Merry X'Mas

▼解説
INITCAP関数は、引数で指定された文字列中の単語の先頭文字を大文字、それ以外を小文字で返す関数です。

文字列「merry x'mas」は**スペースと「'」が単語の区切りとして認識される**ので、「Merry X'Mas」が返されます。

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

顧客名と郵便番号の「-(ハイフン)」より後ろの数字を「*」に置き変えて表示します。どのSQL文を使う？

顧客名　　郵便番号
------ ----------
田中浩二　　142-****
佐々木二郎　146-****
山口真弓　　142-****

▼回答

SELECT cust_last_name || cust_first_name "顧客名",
　RPAD(SUBSTR(cust_postal_code, 1, INSTR(cust_postal_code, '-')), LENGTH(cust_postal_code), '*') "郵便番号"
FROM customers;

▼解説
選択肢のSQL文を1つずつ確認してみましょう。
INSTR(cust_postal_code, '-')でCUST_POSTAL_CODEの「-(ハイフン)」の位置を抽出し、SUBSTR(cust_postal_code, 1, INSTR(cust_postal_code, '-'))でCUSTO_POSTAL_CODEの先頭から「-(ハイフン)までの文字列を抽出します。

SUBSTR(文字列, 開始位置, 文字の長さ)

抽出した文字列に、RPAD関数でLENGTH(cust_postal_code) = CUST_POSTAL_CODEに文字数分になるように右側に「*」を埋め込みます。

RPAD(文字列,　桁数, 埋め込む文字列)

このSQL文が正解です。

日付値の特徴として誤っている記述は？

• 内部的には文字列で格納されている
  →日付値は世紀、年、月、日、時、分、秒を表す**内部的な数値形式**で格納
• 日付値の表示形式は言語環境により決まっており、変更することはできない
  →変更できる

また、日付値のデフォルトの表示書式は
英語環境のデフォルト値: "DD-MON-RR"(日-月-年)
日本語環境のデフォルト値: "RR-MM-DD"(年-月-日)

明日以降で最初の月曜日を表示するSQL文として正しいものはどれですか。

ただし、データベースの実行環境は日本語環境とし、日付の表示書式はRR-MM-DDとします。

▼回答

SELECT NEXT_DAY(SYSDATE, '月曜日') FROM dual;

▼解説
NEXT_DAY関数は引数で指定した日付の翌日以降に、指定した曜日になる最初の日付を返す関数です。

曜日の指定は言語環境で異なります。
**日本語環境の場合は、'月曜日'や省略形の'月'のように指定**します。

**英語環境の場合は、'Sunday'や省略形の'Sun'と指定**します。

現在の日時は2010年12月25日14時40分です。ROUND関数の正しい結果は？(日付の表示書式は「RR-MM-DD」)

▼回答

• ROUND(SYSDATE, 'YEAR')の結果は"11-01-01"である
• ROUND(SYSDATE)の結果は"10-12-26"である
• ROUND(SYSDATE, 'MONTH')の結果は"10-12-01"である

以下の問い合わせの結果と同じデータ型を返すSQLは？

▼回答

SELECT TO_CHAR(hiredate, 'RR') + 10 FROM employees;

▼解説
設問のSQL文のSYSDATE - hiredate(日付値 - 日付値)は、２つの日付期間の日数を計算するので、演算結果のデータ型は数値です。

上記の回答は、TO_CHAR(hiredate, 'RR')で年だけを文字列として取り出し、それに数値を加算した場合、暗黙的なデータ変換が行われ、数値データが返ります。

実行結果に"Oracle"が表示されるのはどの問い合わせ？

▼回答

SELECT SUBSTR('Oracle Master', 1, 6) FROM dual;
SELECT TRIM(RPAD('Oracle Master', 7, ' ')) FROM dual;
SELECT TRIM(REPLACE('Oracle Master', 'Master')) FROM dual;
SELECT INITCAP('oracle') FROM dual;

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

• SELECT SUBSTR('Oracle Master', 1, 6) FROM dual;
  →SUBSTR関数で文字列"Oracle Master"の1文字目から6文字目の文字列を返すので、実行結果は"Oracle"となります。

• SELECT TRIM(REPLACE('Oracle Master', 'Master')) FROM dual;
  →**REPLACE関数で文字列"Oracle Master"から"Master"が削除**され、"Oracle"となります。その後、TRIM関数で前後のスペースが取り除かれるので、実行結果は"Oracle"となります。

REPLACE(文字列, どこを, どの文字に置き換えるか:第三引数が省略された場合は、第2引数を削除)

• SELECT INITCAP('oracle') FROM dual;
• SELECT TRIM(RPAD('Oracle Master', 7, '')) FROM dual;
  →RPAD関数で長さが7文字の文字列が返されるので"Oracle"となります。その後、TRIM関数で前後のスペースが取り除かれるので、実行結果は"Oracle"となります。
  

入社日(hiredate)から現在までの年数を求めるにはどの問い合わせを実行する？(端数は切り捨てて年数のみ求める)

▼回答

SELECT TRUNC((SYSDATE - hiredate) / 365) FROM employees;

▼解説
入社してから現在までの年数は、現在の日時から入社日を減算することで求めることが出来ます。

現在の日時はSYSDATE関数で、入社日はhiredate列から取得できます。

日付-日付の演算では、2つの日付期間の日数が数値で戻されます。
設問では端数を切り捨てた年数が問われているので、求めた**日数を365で割り、かつTRUNC関数で小数点を切り捨て**ます。

単一行関数

実行結果が"12-04-01"と表示される問い合わせは？(DBは日本語環境、日付書式はRR-MM-DD、現在は2012年3月26日)

▼回答

SELECT NEXT_DAY('12-03-26', '日') FROM dual;
SELECT LAST_DAY('12-03-01') + 1 FROM dual;
SELECT ROUND(TO_DATE('12-04-10'), 'MONTH') FROM dual;

▼解説
TO_DATE関数は文字リテラルを日付値に変換します。
引数に時刻が指定されていない場合は、午前0時0分0秒が設定されます。

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

• SELECT ROUND(TO_DATE('12-04-10'), 'MONTH') FROM dual;
  →ROUND関数の書式に**"MONTH"が指定されているので、丸めて当月の1日**が返されます。したがって、実行結果は"12-04-01"となります。

• SELECT LAST_DAY('12-03-01') + 1 FROM dual;
  →LAST_DAY関数で当月の最終日を求めると"12-03-31"となります。その後で1日を加算しているので、翌日の"12-04-01"が返されます。

• SELECT NEXT_DAY('12-03-26', '日') FROM dual;
  →"12-03-26"は月曜日。NEXT_DAY関数で翌日以降で最初の日曜日が返されます。したがって実行結果は"12-04-01"となります。

• SELECT ROUND(TO_DATE('12-04-01'). 'DD') FROM dual;
  →TO_DATE('12-04-01')では時刻は午前0時に設定されます。
  ROUND関数の書式に"DD"が指定されているので、丸めて当日の午前0時が返されます(変化なし)。したがって実行結果は"12-04-01"となります。

名字が「佐藤」で始まる従業員を検索するには、どの問い合わせを実行する?

▼回答

SELECT employee_id, employee_name FROM employees WHERE INSTR(employee_name, '佐藤') = 1;
SELECT employee_id, employee_name FROM employees WHERE SUBSTR(employee_name, 1, 2) = '佐藤';

▼解説
SUBSTR関数は引数で指定された文字列の部分文字列を返す関数、INSTR関数は引数で指定した文字列中から検索文字列を検索し、その位置を数値で返す関数です。