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字符串是以单引号作为分界符的，在字符串前面和后面各一个单引号。但是字符串中也能包含单引号，为了使语法分析器能够区分字符串中的单引号还是分界符。规定当字符串中出现单引号时，在其前面添加一个单引号作为区分。也就是说， 在单引号分隔的字符串中，两个连续的单引号''表示一个单引号字符。
sh@TEST1107> select '''' from dual;
'
-
'
四个连续单引号分界为： 第一个单引号，字符串的开始分界符。
接下来连续的两个单引号，表示字符串的值（一个单引号）
第四个单引号，字符串的结束分界符。
sh@TEST1107> select 'today is ''FRIDAY''' from dual;
'TODAYIS''FRIDAY'
-----------------
today is 'FRIDAY'
在Oracle中，single-quote(‘)是一个表示字符串的关键字。所以在字符串中用两个”表示一个实际的单引号字符。所有才会有了上面第一条SQL的'today is ''FRIDAY'''。双引号“被识别为一个实际的的字符串。其实Oracle提供了一个Q-quote的表达式，用来简化SQL或PLSQL中字符串的表示。
sh@TEST1107> select q'[today is ''FRIDAY'']' names from dual;
NAMES
-------------------
today is ''FRIDAY''
sh@TEST1107> select q'[today is 'FRIDAY']' names from dual;
NAMES
-----------------
today is 'FRIDAY'
看上面两个的差别可知，使用Q-quote的表达式，只需要将要表示的字符串用一对特殊字符括起来，这对字符必须一致。不一致会报错。
sh@TEST1107> select q'[today is 'FRIDAY'|' names from dual; ERROR: ORA-01756: quoted string not properly terminated
sh@TEST1107> select q'|today is 'FRIDAY'|' names from dual;
NAMES
-----------------
today is 'FRIDAY'
从以上实验来解答题中的答案。
A答案错误，使用‘作为特殊字符，is后面缺少单引号，应该为
sh@TEST1107> SELECT prod_name || q'''s category is ''|| prod_category CATEGORIES FROM products;
CATEGORIES
----------------------------------------------------------------------------------------------------
5MP Telephoto Digital Camera's category is Photo
17" LCD w/built-in HDTV Tuner's category is Peripherals and Accessories
Envoy 256MB - 40GB's category is Hardware
Y Box's category is Electronics
Mini DV Camcorder with 3.5" Swivel LCD's category is Photo
B. 错误。
SELECT prod_name q'['s ]'category is ' prod_category CATEGORIES FROM products;
category is ' 这里前面缺少一个单引号。缺少category is 开始的单引号。
应该为：
sh@TEST1107> SELECT prod_name || q'['s ]'||'category is '||prod_category CATEGORIES FROM products;
CATEGORIES
----------------------------------------------------------------------------------------------------
5MP Telephoto Digital Camera's category is Photo
17" LCD w/built-in HDTV Tuner's category is Peripherals and Accessories
Envoy 256MB - 40GB's category is Hardware
Y Box's category is Electronics
C. 正确
SELECT prod_name q'\'s\' ' category is ' prod_category CATEGORIES FROM products;
sh@TEST1107>
SELECT prod_name|| q'\'s\' || ' category is ' || prod_category CATEGORIES FROM products;
CATEGORIES
----------------------------------------------------------------------------------------------------
5MP Telephoto Digital Camera's category is Photo
17" LCD w/built-in HDTV Tuner's category is Peripherals and Accessories
Envoy 256MB - 40GB's category is Hardware
Y Box's category is Electronics
D. 正确
SELECT prod_name q'<'s >' 'category is ' prod_category CATEGORIES FROM products;
sh@TEST1107>
SELECT prod_name || q'<'s >' || 'category is ' || prod_category CATEGORIES FROM products;
CATEGORIES
----------------------------------------------------------------------------------------------------
5MP Telephoto Digital Camera's category is Photo
17" LCD w/built-in HDTV Tuner's category is Peripherals and Accessories
Envoy 256MB - 40GB's category is Hardware
Y Box's category is Electronics
If opportunity doesn’t knock, build a door
