SUBSTR(cust_name,4') 返回指定位置后的字符串 De Hann

LPAD(‘De Hann’,11 ,'*')

****De Hann

INSTR(cust_name, '',1,2)<>0; 表示搜索空字符串，从第一个字符开始，到最后的得到

空字符串在该字符串中出现的次数为2次，说明该字符串有三个Names

INSTR(cust_name, '',-1,2)<>0 同理，从倒数第一个位置搜索

84. View the Exhibit andexamine the structure of the EMPLOYEES table.

Examine the data in theENAME and HIREDATE columns of the EMPLOYEES table:

ENAME HIREDATE

SMITH 17-DEC-80

ALLEN 20-FEB-81

WARD 22-FEB-81

You want to generate alist of user IDs as follows:

USERID

Smi17DEC80

All20FEB81

War22FEB81

You issue the followingquery:

SQL>SELECTCONCAT(SUBSTR(INITCAP(ename),1,3), REPLACE(hiredate,'-')) "USERID"

FROM employees;

What is the outcome

A. It executessuccessfully and gives the correct output.

B. It executessuccessfully but does not give the correct output.

C. It generates an errorbecause the REPLACE function is not valid.

D. It generates an errorbecause the SUBSTR function cannot be nested in the CONCAT function.

Answer: A

以SMITH 17-DEC-80 为列：

CONCAT(SUBSTR(INITCAP(ename),1,3),REPLACE(hiredate,'-'))

INITCAP(ename) 返回首字母大写，其他字母小写 Smith

SUBSTR(‘Smith’,1,3) 截取字串 Smi

REPLACE(hiredate,'-') 如果不指定代替的字符串，将直接删除要替代的字串

scott@ORCL>selectreplace('17-DEC-80','-') from dual;

REPLACE

-------

17DEC80

CONCAT(‘Smi’,’ 17DEC80’)

Smi17DEC80

85. View the E xhibitand examine the structure and data in the INVOICE table.

Which statements aretrue regarding data type conversion in expressions used in queries (Choose all

that apply.)

A. inv_amt ='0255982': requires explicit conversion

B. inv_date >'01-02-2008' : uses implicit conversion

C.CONCAT(inv_amt,inv_date) : requires explicit conversion

D. inv_date ='15-february-2008' : uses implicit conversion

E. inv_no BETWEEN '101'AND '110' : uses implicit conversion

Answer: DE

B选项不能隐式转换，需要sql函数转换

86. Examine thestructure and data of the CUST_TRANS table:

CUST_TRANS

Name Null Type

CUSTNO NOT NULL CHAR(2)

TRANSDATE DATE

TRANSAMT NUMBER(6,2)

CUSTNO TRANSDATETRANSAMT

11 01-JAN-07 1000

22 01-FEB-07 2000

33 01-MAR-07 3000

Dates are stored in thedefault date format dd-mon-rr in the CUST_TRANS table.

Which SQL statementswould execute successfully (Choose three .)

A. SELECT transdate +'10' FROM cust_trans;

B. SELECT * FROMcust_trans WHERE transdate = '01-01-07';

C. SELECT transamt FROMcust_trans WHERE custno > '11';

D. SELECT * FROMcust_trans WHERE transdate='01-JANUARY-07';

E. SELECT custno + 'A'FROM cust_trans WHERE transamt > 2000;

Answer: ACD

解析：
由上题可知 A正确，B错误，D正确

C选项，测试：
scott@ORCL>insert into zbcxy values('11');

已创建 1 行。

scott@ORCL>insertinto zbcxy values('22');

已创建 1 行。

scott@ORCL>select *from zbcxy where id>'11';

ID

--

22

E选项,无法将A转换为数字，测试：

scott@ORCL>selectid+'s' from zbcxy;

select id+'s' from zbcxy

*

第 1 行出现错误:

ORA-01722: 无效数字

scott@ORCL>selectid+'3' from zbcxy;

ID+'3'

----------

14

25

87. You want to displaythe date for the first Mon day of the next month and issue the following

command:

SQL>SELECTTO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE),'MON'),

'dd "is the firstMonday for" fmmonth rrrr')

FROM DUAL;

What is the outcome

A. It executessuccessfully and returns the correct result.

B. It executessuccessfully but does not return the correct result.

C. It generates an errorbecause TO_CHAR should be replaced with TO_DATE.

D. It generates an errorbecause rrrr should be replaced by rr in the format string.