OCP-1Z0-051 第82题 like模糊查询
来源:互联网 发布:怎样利用淘宝漏洞赚钱 编辑:程序博客网 时间:2024/06/06 02:13
一、原题
以下是ABCD四个选项的测试结果:
SQL> with customers as
2 ( select 'Renske Ladwig' CUST_NAME from dual
3 union all
4 select 'Jason Mallin' CUST_NAME from dual
5 union all
6 select 'Samuel McCain' CUST_NAME from dual
7 union all
8 select 'Allan MCEwen' CUST_NAME from dual
9 union all
10 select 'Irene Mikkilineni' CUST_NAME from dual
11 union all
12 select 'Julia Nayer' CUST_NAME from dual
13 )
14 SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
15 FROM customers
16 WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1))='Mc';
no rows selected
SQL> with customers as
2 ( select 'Renske Ladwig' CUST_NAME from dual
3 union all
4 select 'Jason Mallin' CUST_NAME from dual
5 union all
6 select 'Samuel McCain' CUST_NAME from dual
7 union all
8 select 'Allan MCEwen' CUST_NAME from dual
9 union all
10 select 'Irene Mikkilineni' CUST_NAME from dual
11 union all
12 select 'Julia Nayer' CUST_NAME from dual
13 )
14 SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
15 FROM customers
16 WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) LIKE 'Mc%';
SUBSTR(CUST_NAME,INSTR(CUST_NAME,'')+1)
-------------------------------------------------------------------------
McCain
MCEwen
SQL> with customers as
2 ( select 'Renske Ladwig' CUST_NAME from dual
3 union all
4 select 'Jason Mallin' CUST_NAME from dual
5 union all
6 select 'Samuel McCain' CUST_NAME from dual
7 union all
8 select 'Allan MCEwen' CUST_NAME from dual
9 union all
10 select 'Irene Mikkilineni' CUST_NAME from dual
11 union all
12 select 'Julia Nayer' CUST_NAME from dual
13 )
14 SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
15 FROM customers
16 WHERE SUBSTR(cust_name, INSTR(cust_name,' ')+1) LIKE INITCAP('MC%');
SUBSTR(CUST_NAME,INSTR(CUST_NAME,'')+1)
--------------------------------------------------------------------------
McCain
SQL> with customers as
2 ( select 'Renske Ladwig' CUST_NAME from dual
3 union all
4 select 'Jason Mallin' CUST_NAME from dual
5 union all
6 select 'Samuel McCain' CUST_NAME from dual
7 union all
8 select 'Allan MCEwen' CUST_NAME from dual
9 union all
10 select 'Irene Mikkilineni' CUST_NAME from dual
11 union all
12 select 'Julia Nayer' CUST_NAME from dual
13 )
14 SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
15 FROM customers
16 WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) = INITCAP('MC%');
no rows selected
Examine the data in the CUST_NAME column of the CUSTOMERS table.
CUST_NAME
Renske Ladwig
Jason Mallin
Samuel McCain
Allan MCEwen
Irene Mikkilineni
Julia Nayer
You need to display customers' second names where the second name starts with "Mc" or "MC."
Which query gives the required output?
A. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
FROM customers
WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1))='Mc';
B. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
FROM customers
WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) LIKE 'Mc%';
C. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
FROM customers
WHERE SUBSTR(cust_name, INSTR(cust_name,' ')+1) LIKE INITCAP('MC%');
D. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
FROM customers
WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) = INITCAP('MC%');
答案:B
二、题目翻译CUST_NAME
Renske Ladwig
Jason Mallin
Samuel McCain
Allan MCEwen
Irene Mikkilineni
Julia Nayer
You need to display customers' second names where the second name starts with "Mc" or "MC."
Which query gives the required output?
A. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
FROM customers
WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1))='Mc';
B. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
FROM customers
WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) LIKE 'Mc%';
C. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
FROM customers
WHERE SUBSTR(cust_name, INSTR(cust_name,' ')+1) LIKE INITCAP('MC%');
D. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
FROM customers
WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) = INITCAP('MC%');
答案:B
下面是CUSTOMERS表CUST_NAME列的数据,
要显示第二个名字以"Mc" or "MC"开头的员工第二个名字(第二个名字,也就是空格后的名字)
三、题目解析要显示第二个名字以"Mc" or "MC"开头的员工第二个名字(第二个名字,也就是空格后的名字)
A选项不正确,where条件='Mc',没有第二个名字是Mc的人,所以没结果,而且题目要求包含MC或Mc,而不是等于。
B选项正确,INSTR找出空格的位置,然后用SUBSTR从空格后开始找,把找出的结果首字母大写,然后用like模糊查询。
C选项不正确,SUBSTR查找出的结果和INITCAP('MC%')匹配,只能匹配Mc,而无法匹配MC。
D选项不正确,模糊查询,不能用等号。
四、测试B选项正确,INSTR找出空格的位置,然后用SUBSTR从空格后开始找,把找出的结果首字母大写,然后用like模糊查询。
C选项不正确,SUBSTR查找出的结果和INITCAP('MC%')匹配,只能匹配Mc,而无法匹配MC。
D选项不正确,模糊查询,不能用等号。
以下是ABCD四个选项的测试结果:
SQL> with customers as
2 ( select 'Renske Ladwig' CUST_NAME from dual
3 union all
4 select 'Jason Mallin' CUST_NAME from dual
5 union all
6 select 'Samuel McCain' CUST_NAME from dual
7 union all
8 select 'Allan MCEwen' CUST_NAME from dual
9 union all
10 select 'Irene Mikkilineni' CUST_NAME from dual
11 union all
12 select 'Julia Nayer' CUST_NAME from dual
13 )
14 SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
15 FROM customers
16 WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1))='Mc';
no rows selected
SQL> with customers as
2 ( select 'Renske Ladwig' CUST_NAME from dual
3 union all
4 select 'Jason Mallin' CUST_NAME from dual
5 union all
6 select 'Samuel McCain' CUST_NAME from dual
7 union all
8 select 'Allan MCEwen' CUST_NAME from dual
9 union all
10 select 'Irene Mikkilineni' CUST_NAME from dual
11 union all
12 select 'Julia Nayer' CUST_NAME from dual
13 )
14 SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
15 FROM customers
16 WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) LIKE 'Mc%';
SUBSTR(CUST_NAME,INSTR(CUST_NAME,'')+1)
-------------------------------------------------------------------------
McCain
MCEwen
SQL> with customers as
2 ( select 'Renske Ladwig' CUST_NAME from dual
3 union all
4 select 'Jason Mallin' CUST_NAME from dual
5 union all
6 select 'Samuel McCain' CUST_NAME from dual
7 union all
8 select 'Allan MCEwen' CUST_NAME from dual
9 union all
10 select 'Irene Mikkilineni' CUST_NAME from dual
11 union all
12 select 'Julia Nayer' CUST_NAME from dual
13 )
14 SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
15 FROM customers
16 WHERE SUBSTR(cust_name, INSTR(cust_name,' ')+1) LIKE INITCAP('MC%');
SUBSTR(CUST_NAME,INSTR(CUST_NAME,'')+1)
--------------------------------------------------------------------------
McCain
SQL> with customers as
2 ( select 'Renske Ladwig' CUST_NAME from dual
3 union all
4 select 'Jason Mallin' CUST_NAME from dual
5 union all
6 select 'Samuel McCain' CUST_NAME from dual
7 union all
8 select 'Allan MCEwen' CUST_NAME from dual
9 union all
10 select 'Irene Mikkilineni' CUST_NAME from dual
11 union all
12 select 'Julia Nayer' CUST_NAME from dual
13 )
14 SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
15 FROM customers
16 WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) = INITCAP('MC%');
no rows selected
0 0
- OCP-1Z0-051 第82题 like模糊查询
- OCP-1Z0-051 第135题 子查询注意事项
- OCP-1Z0-051 第145题 子查询嵌套
- OCP-1Z0-051 第10题
- OCP-1Z0-051 第54题
- OCP-1Z0-051 第60题
- OCP-1Z0-051 第8题 子查询和多表关联查询和情况
- OCP-1Z0-051 补充题库 第5题 主查询和子查询的注意事项
- OCP-1Z0-051 第133题 子查询的用法
- OCP-1Z0-051 第134题 子查询的用法
- OCP-1Z0-051 第136题 子查询的使用
- OCP-1Z0-051 第137题 子查询的应用
- OCP-1Z0-051 第139题 子查询的注意事项
- OCP-1Z0-051 第140题 子查询中使用GROUP BY注意事项
- OCP-1Z0-051 第141题 子查询的应用
- OCP-1Z0-051 第142题 嵌套子查询的应用
- OCP-1Z0-051 第143题 子查询中ANY、ALL的应用
- OCP-1Z0-051 第144题 子查询的应用
- 项目ITP(三) 玩玩 服务端 到 app端
- 构造函数
- Java设计模式-工厂模式
- 软件的架构和框架
- oracle expdp exp高低版本间导入导出
- OCP-1Z0-051 第82题 like模糊查询
- 条件变量、pthread_cond_init
- 常用控件之listview
- 最长回文子串模板
- VIM装X神器
- MFC ProcessShellCommand()
- 创业公司的每月收入与支出做帐
- 虚拟化基础知识
- SVM中为何间隔边界的值为正负1