OCP-1Z0-051-題目解析-第13題

13. View the Exhibit and examine the structure of the PRODUCTS table.

You need to generate a report in the following format:
CATEGORIES
5MP Digital Photo Camera's category is Photo
Y Box's category is Electronics
Envoy Ambassador's category is Hardware
Which two queries would give the required output? (Choose two.)

A. SELECT prod_name q'''s category is ' prod_category CATEGORIES
FROM products;
B. SELECT prod_name q'['s ]'category is ' prod_category CATEGORIES
FROM products;
C. SELECT prod_name q'\'s\' ' category is ' prod_category CATEGORIES
FROM products;
D. SELECT prod_name q'<'s >' 'category is ' prod_category CATEGORIES
FROM products;

注意：選項中字串之間的串連應加串連符||
Answer: CD

題目解析：
sql中字串的兩邊是需要加單引號的，表示字串的開始和結束，如果我們想在sql中輸出一個實際的單引號',那麼需要連續輸入四個單引號''''，其中中間的兩個單引號表示一個單引號。
第一個單引號：表示字串開始
第二個單引號：表示轉義，後面接的單引號轉義為真正的單引號
第三個單引號：表示實際輸出的單引號
第四個單引號：表示字串結束
如：
SQL> select '''' name from dual;

N
-
'
SQL> select 'Camera''s category is Photo' name from dual;

NAME
--------------------------
Camera's category is Photo

另外Oracle還提供了一個Q-quote的運算式，來簡化SQL或PLSQL中字串的表示，其格式為q'[Camera's category is Photo]',輸出為方括弧中的原始字串格式，其中方括弧可以更換為其他的任意一對特殊符號，但必須是成對出現的，不可為q'[Camera's category is Photo|'。
如：
SQL> select q'[Camera's category is Photo]' name from dual;

NAME
--------------------------
Camera's category is Photo

SQL> select q'|Camera's category is 'Photo'|' name from dual;

NAME
----------------------------
Camera's category is 'Photo'

A：錯誤， is後面應該是兩個單引號，正確的為
SELECT prod_name || q'''s category is ''|| prod_category CATEGORIES
FROM products;
B:錯誤，category 前面少了一個',正確的為
SELECT prod_name || q'['s ]'||'category is '||prod_category CATEGORIES FROM products;