ocp 1Z0-051 1-22题解析-MySQL-Tutorial-php.cn

1. View the Exhibit andexamine the structure of the SALES, CUSTOMERS, PRODUCTS, and TIMES tables. The PROD_ID column isthe foreign key in the SALES table, which references the PRODUCTS table. Similarly, the CUST_IDand TIME_ID columns are a

1. View the Exhibit andexamine the structure of the SALES, CUSTOMERS, PRODUCTS, and TIMES

tables.

The PROD_ID column isthe foreign key in the SALES table, which references the PRODUCTS table.

Similarly, the CUST_IDand TIME_ID columns are also foreign keys in the SALES table referencing the

CUSTOMERS and TIMEStables, respectively.

Evaluate the followingCREATE TABLE command:

CREATE TABLEnew_sales(prod_id, cust_id, order_date DEFAULT SYSDATE)

SELECT prod_id, cust_id,time_id

FROM sales;

Which statement is trueregarding the above command?

A. The NEW_SALES tablewould not get created because the DEFAULT value cannot be specified in the

column definition.

B. The NEW_SALES tablewould get created and all the NOT NULL constraints defined on the specified

columns would be passedto the new table.

C. The NEW_SALES tablewould not get created because the column names in the CREATE TABLE

command and the SELECTclause do not match.

D. The NEW_SALES tablewould get created and all the FOREIGN KEY constraints defined on the

specified columns wouldbe passed to the new table.

Answer: B

解析：题目意思是利用：

CREATE TABLEnew_sales(prod_id, cust_id, order_date DEFAULT SYSDATE)

SELECT prod_id, cust_id,time_id

FROM sales;

创建表能否传递sales表的非空约束限制和外键约束

测试：

create table zbcxy

(

id number(20) not null,

sno number(10) unique,

sex char(2) check (sex in('男','女')),

cno number(6) references student(cno)

);

create table zbdba

select * from zbcxy;

erwei@ORCL>desc zbdba;

名称

-------------------------

ID not null number(20)

SNO number(10)

SEX char(2)

CNO number(6)

经过一系列插入数据操作，发现只有非空约束才能传递。

2. View the Exhibit toexamine the description for the SALES table.

Which views can have allDML operations performed on it? (Choose all that apply.)

A. CREATE VIEW v3

AS SELECT * FROM SALES

WHERE cust_id = 2034

WITH CHECK OPTION;

B. CREATE VIEW v1

AS SELECT * FROM SALES

WHERE time_id

WITH CHECK OPTION;

C. CREATE VIEW v2

AS SELECT prod_id,cust_id, time_id FROM SALES

WHERE time_id

WITH CHECK OPTION;

D. CREATE VIEW v4

AS SELECT prod_id,cust_id, SUM(quantity_sold) FROM SALES

WHERE time_id

GROUP BY prod_id,cust_id

WITH CHECK OPTION;

Answer: AB

解析：

这道题的意思是哪个视图可以做dml的所有操作，很明显c和d选项是不行的，因为c和d选项创建视图的时候值查询了sales表的部分字段，如果在该视图上做insert操作，即使满足with check option，也无法对原表进行插入，原sales表所有属性列都是为非空的

这里的 with checkoption就是对视图操作的一个条件限制，条件就是创建视图时 where后面的条件，例如 B选项，则对视图做更新操作时，time_id是不能>sysdate-2*365的

3. You need to extractdetails of those products in the SALES table where the PROD_ID column

contains the string'_D123'.

Which WHERE clause couldbe used in the SELECT statement to get the required output?

A. WHERE prod_id LIKE'%_D123%' ESCAPE '_'

B. WHERE prod_id LIKE'%_D123%' ESCAPE ''

C. WHERE prod_id LIKE '%_D123%'ESCAPE '%_'

D. WHERE prod_id LIKE'%_D123%' ESCAPE '_'

Answer: B

解析：
如果要查询的信息本身包含”%”或”_”,则可以使用ESCAPE定义一个用于表示转义的字符

例如：
查询名字中包含”_”的员工信息

Select * from emp whereename like ‘’%x_%’ escape ‘x’;

4. Which two statementsare true regarding single row functions? (Choose two.)

A. They a ccept only asingle argument.

B. They c an be nestedonly to two levels.

C. Arguments can only becolumn values or constants.

D. They a lways return asingle result row for every row of a queried table.

E. They c an return adata type value different from the one that is referenced.

Answer: DE

解析：

引用oracle 官方文档 sql reference中对Single-rowfunctions的解释
Single-row functions return a single result row for every row of a queriedtable or

view. These functionscan appear in select lists, WHERE clauses, START WITH and

CONNECT BY clauses, andHAVING clauses

很多单行函数都不止传一个参数，比如数值函数中的ATAN2(m,n),所以a选项错误

单行函数返回的结果也不一定和改行的数据类型相同，比如字符函数中的ASCII(char)

返回的number类型

综上可得 D和E正确

5. Which SQL statementswould display the value 1890.55 as $1,890.55? (Choose three .)

A. SELECTTO_CHAR(1890.55,'$0G000D00')

FROM DUAL;

B. SELECTTO_CHAR(1890.55,'$9,999V99')

FROM DUAL;

C. SELECTTO_CHAR(1890.55,'$99,999D99')

FROM DUAL;

D. SELECTTO_CHAR(1890.55,'$99G999D00')

FROM DUAL;

E. SELECTTO_CHAR(1890.55,'$99G999D99')

FROM DUAL;

Answer: ADE

解析：

scott@ORCL>SELECTTO_CHAR(1890.55,'$0G000D00')

2 FROMDUAL;

TO_CHAR(18

----------

$1,890.55

scott@ORCL>SELECTTO_CHAR(1890.55,'$9,999V99')

2 FROMDUAL;

TO_CHAR(1

---------

$1,89055

scott@ORCL>SELECT TO_CHAR(1890.55,'$99,999D99')

2 FROMDUAL;

SELECTTO_CHAR(1890.55,'$99,999D99')

第 1 行出现错误:

ORA-01481: 无效的数字格式模型

scott@ORCL>SELECTTO_CHAR(1890.55,'$99G999D00')

2 FROMDUAL;

TO_CHAR(189

-----------

$1,890.55

scott@ORCL>SELECTTO_CHAR(1890.55,'$99G999D99')

2 FROMDUAL;

TO_CHAR(189

-----------

$1,890.55

格式里面的G代表逗号，D代表为点

V 它的作用在于做一个计算。

例如TO_CHAR(N,'999V9'),以p表示V的位置,则该表达式＝to_char(N×(10的P-1次方)).但是9个数又必须保证大于等于乘积之后表示的位数.

更多格式说明：
http://blog.csdn.net/zbdba/article/details/17042195

6. Examine the structureof the SHIPMENTS table:

name Null Type

PO_ID NOT NULL NUMBER(3)

PO_DATE NOT NULL DATE

SHIPMENT_DATE NOT NULLDATE

SHIPMENT_MODEVARCHAR2(30)

SHIPMENT_COSTNUMBER(8,2)

You want to generate areport that displays the PO_ID and the penalty amount to be paid if the

SHIPMENT_DATE is laterthan one month from the PO_DATE. The penalty is $20 per day.

Evaluate the followingtwo queries:

SQL> SELECT po_id,CASE

WHEN MONTHS_BETWEEN(shipment_date,po_date)>1 THEN

TO_CHAR((shipment_date -po_date) * 20) ELSE 'No Penalty' END PENALTY

FROM shipments;

SQL>SELECT po_id,DECODE

(MONTHS_BETWEEN(po_date,shipment_date)>1,

TO_CHAR((shipment_date- po_date) * 20), 'No Penalty') PENALTY

FROM shipments;

Which statement is trueregarding the above commands?

A. Both executesuccessfully and give correct results.

B. Only the first queryexecutes successfully but gives a wrong result.

C. Only the first queryexecutes successfully and gives the correct result.

D. Only the second queryexecutes successfully but gives a wrong result.

E. Only the second queryexecutes successfully and gives the correct result.

Answer: C

解析：

第一个查询用的 case expressionswhich let you use IF ... THEN ... ELSE logic in SQL statements without

having to invokeprocedures

经过测试，第一个是可以执行成功

第二个查询用的decode,引用官方文档的介绍：

DECODE compares expr toeach search value one by one. If expr is equal to a

search, then OracleDatabase returns the corresponding result. If no match is

found, then Oraclereturns default. If default is omitted, then Oracle returns null.

The arguments can be anyof the numeric types (NUMBER, BINARY_FLOAT, or

BINARY_DOUBLE) orcharacter types.

就是第一个表达式的值和后面的一一匹配，成功就返回匹配成功对应的那个值

经测试，第二表达式是无法执行的，参数个数无效

7. Which two statementsare true regarding the USING and ON clauses in table joins? (Choose two.)

A. Both USING and ONclauses can be used for equijoins and nonequijoins.

B. A maximum of one pairof columns can be joined between two tables using the ON clause.

C. The ON clause can beused to join tables on columns that have different names but compatible data

types.

D. The WHERE clause canbe used to apply additional conditions in SELECT statements containing the

ON or the USING clause.

Answer: CD

解析：

引用官方文档中的说明：

ON condition Use the ON clause to specify a joincondition. Doing so lets you specify

join conditions separatefrom any search or filter conditions in the WHERE clause.

USING (column) When you are specifying an equijoin ofcolumns that have the same

name in both tables, theUSING column clause indicates the columns to be used. You

can use this clause onlyif the join columns in both tables have the same name. Within

this clause, do notqualify the column name with a table name or table alias.

8. View the Exhibit andexamine the structure of the CUSTOMERS table.

Which two tasks wouldrequire subqueries or joins to be executed in a single statement? (Choose two.)

A. listing of customerswho do not have a credit limit and were born before 1980

B. finding the number ofcustomers, in each city, whose marital status is 'married'

C. finding the averagecredit limit of male customers residing in 'Tokyo' or 'Sydney'

D. listing of thosecustomers whose credit limit is the same as the credit limit of customersresiding in the

city 'Tokyo'

E. finding the number ofcustomers, in each city, whose credit limit is more than the average creditlimit of

all the customers

Answer: DE

解析：

该题的意思是哪些选项需要用子查询或者join连接，很容易看出。

9. Which statement istrue regarding the INTERSECT operator?

A. It ignores NULLvalues.

B. Reversing the orderof the intersected tables alters the result.

C. The names of columnsin all SELECT statements must be identical.

D. The number of columnsand data types must be identical for all SELECT statements in the query.

Answer: D

解析：通过insert规则很容易得到D选项

10. View the Exhibit; examine the structure of the PROMOTIONS table.

Each promotion has a durationof at least seven days .

Your manager has askedyou to generate a report, which provides the weekly cost for each promotion

done to l date.

Which query wouldachieve the required result?

A. SELECT promo_name,promo_cost/promo_end_date-promo_begin_date/7

FROM promotions;

B. SELECTpromo_name,(promo_cost/promo_end_date-promo_begin_date)/7

FROM promotions;

C. SELECT promo_name,promo_cost/(promo_end_date-promo_begin_date/7)

FROM promotions;

D. SELECT promo_name, promo_cost/((promo_end_date-promo_begin_date)/7)

FROM promotions;

Answer: D

解析：这个题就是涉及到优先级的问题，括号里面的优先执行

结束日期减去开始日期除以7得到星期数，再用总值除以星期数就是每个星期的

11. View the Exhibit andexamine the structure of the PRODUCTS table.

All products have a listprice.

You issue the followingcommand to display the total price of each product after a discount of 25% anda

tax of 15% are appliedon it. Freight charges of $100 have to be applied to all the products.

SQL>SELECT prod_name,prod_list_price -(prod_list_price*(25/100))

+(prod_list_price-(prod_list_price*(25/100))*(15/100))+100

AS "TOTALPRICE"

FROM products;

What would be theoutcome if all the parenthese s are removed from the above statement?

A. It produces a syntaxerror.

B. The result remainsunchanged.

C. The total price valuewould be lower than the correct value.

D. The total price valuewould be higher than the correct value.

Answer: B

解析：题目的意思是去掉括号会输出什么，去掉括号应该是毫无影响的，因为所有加括号的地方都是乘法或者除法运算，他们谁优先不会影响到结果

12. You need to producea report where each customer's credit limit has been incremented by $1000. In

the output, t hecustomer's last name should have the heading Name and the incremented creditlimit

should be labeled NewCredit Limit. The column headings should have only the first letter of eachword in

uppercase .

Which statement wouldaccomplish this requirement?

A. SELECT cust_last_nameName, cust_credit_limit + 1000

"New CreditLimit"

FROM customers;

B. SELECT cust_last_nameAS Name, cust_credit_limit + 1000

AS New Credit Limit

FROM customers;

C. SELECT cust_last_nameAS "Name", cust_credit_limit + 1000

AS "New CreditLimit"

FROM customers;

D. SELECTINITCAP(cust_last_name) "Name", cust_credit_limit + 1000

INITCAP("NEW CREDITLIMIT")

FROM customers;

Answer: C

解析：

scott@ORCL>selectename Name,sal+1000 "New Credit Litmit" from emp;

NAME New Credit Litmit

scott@ORCL>selectename as "Name",sal+1000 as "New Credit Limit" from emp;

Name New Credit Limit

加上as 才能以规定样式输出，当然双引号也可以：

scott@ORCL>selectename "Name",sal+1000 "New Credit Litmit" from emp;

Name New Credit Litmit

13. View the Exhibit andexamine the structure of the PRODUCTS table.

You need to generate areport in the following format: