OCP-1Z0-051-V9 02-13题

梦里梦外; 2022-03-21 11:49 186阅读 0赞

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字符串是以 [单引号][Link 1]作为分界符的，在字符串前面和后面各一个 [单引号][Link 1]。但是字符串中也能包含 [单引号][Link 1]，为了使语法分析器能够区分字符串中的单引号还是分界符。规定当字符串中出现单引号时，在其前面添加一个单引号作为区分。也就是说， 在单引号分隔的字符串中，两个连续的单引号''表示一个单引号字符。

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

**注意，题中答案需要添加||来连接字符串。**

再分享一下我老师大神的人工智能教程吧。零基础！通俗易懂！风趣幽默！还带黄段子！希望你也加入到我们人工智能的队伍中来！[https://blog.csdn.net/jiangjunshow][https_blog.csdn.net_jiangjunshow]

[Link 1]: http://zhidao.baidu.com/search?word=%E5%8D%95%E5%BC%95%E5%8F%B7&fr=qb_search_exp&ie=utf8
[https_blog.csdn.net_jiangjunshow]: https://blog.csdn.net/jiangjunshow/article/details/77338485