1Z0-051(V9.02) 解析1-5

dcren521

于 2017-02-23 18:54:53 发布

阅读量615

点赞数

本文链接：https://blog.csdn.net/dcren521/article/details/56678039

版权

1. Viewthe Exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS, andTIMES tables. The PROD_ID column is the foreign key in the SALES table, whichreferences the PRODUCTS table. Similarly, the CUST_ID and TIME_ID columns arealso foreign keys in the SALES table referencing the CUSTOMERS and TIMEStables, respectively.

Evaluatethe following CREATE TABLE command:

CREATE TABLE new_sales(prod_id, cust_id,order_date DEFAULT SYSDATE) AS

SELECT prod_id, cust_id, time_id

FROM sales;

Whichstatement is true regarding the above command?

A. The NEW_SALES table would not get createdbecause the DEFAULT value cannot be specified in the column definition.

B. The NEW_SALES table would get created andall the NOT NULL constraints defined on the specified columns would be passedto the new table.

C. The NEW_SALES table would not get createdbecause the column names in the CREATE TABLE command and the SELECT clause donot match.

D. The NEW_SALES table would get created andall the FOREIGN KEY constraints defined on the specified columns would bepassed to the new table.

Answer: B

实验部分：

SQL> createtable t1(idnumberprimarykey,namevarchar2(8)notnull);

Table created

SQL> createtable t2(id,name,dd) as selectid,name,sysdatefrom t1;

Table created

-- 结论1：可以通过以上语句创建T2

SQL> desc t1;

NameType NullableDefault Comments

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

ID NUMBER

NAMEVARCHAR2(8)

SQL> desc t2;

NameType NullableDefault Comments

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

ID NUMBER Y

NAMEVARCHAR2(8)

DD DATE Y

-- 结论2：查看以上2个表结构发现，主键约束无法传递，但not null 非空约束可以传递

相关理论：

createtable employees_copyasselect *from employees ;

这条语句会创建EMPLOYEES_COPY.它完全是EMPLOYEES表的副本，它们的定义和包含的行完全相间。

列上的所有的NOTNULL(非空)和CHECK(检查)约束也将应用于新的表，

但所有PRIMARYKEY(主键)、UNIQUE(唯一值)或者FOREIGNKEY(外键)约束则不适用。这是因为这三类约束需要索引，这些索引可能不是必需的.

createtable employees_copyasselect *from employees;

This statement will create a table EMPLOYEES_COPY, which is an exact copy of the EMPLOYEES table, identical in both definition and the rows it contains.

Any notnull and check constraints on the columns will also be applied to the new table,

But any primary key, unique, or foreign key constraints will not be.This is because these three types of constraints require indexes that might not be desired.

2. Viewthe Exhibit to examine the description for the SALES table.

Whichviews can have all DML operations performed on it? (Choose all that apply.)

A. CREATEVIEW v3 AS SELECT * FROM SALES WHERE cust_id = 2034 WITH CHECK OPTION;

B. CREATEVIEW v1 AS SELECT * FROM SALES WHERE time_id <= SYSDATE - 2*365 WITH CHECKOPTION;

C. CREATEVIEW v2 AS SELECT prod_id, cust_id, time_id FROM SALES WHERE time_id <=SYSDATE - 2*365 WITH CHECK OPTION;

D. CREATEVIEW v4 AS SELECT prod_id, cust_id, SUM(quantity_sold) FROM SALES WHERE time_id<= SYSDATE - 2*365 GROUP BY prod_id, cust_id WITH CHECK OPTION;

Answer:AB

答案解析：

就视图的建立而言，ABC均可建立，D项中只需将SUM(quantity_sold)命一个别名 SQ 就可以成功建立视图。但本题并非考察视图是否可以成功创建的问题。

而是考察四个选项中那个语句创建的视图，可以执行所有的dml语句。

那么什么是dml语句？

dml语句指的是insert,update,delete,merge

sales表中每个字段都不能为null，因此执行视图insert的时候，视图必须包括所有的列。

创建视图中with check option的意思是，执行所有的dml语句只能符合该视图能看到的，也即是符合where条件的。

比如A选项，如果insert的话，只能插入cust_id列为2034，其他是无法插入，下面通过实验证明。

实验部分：

createtable T1

(

id NUMBERprimary key,

nameVARCHAR2(8)notnull

);

createorreplaceview tv1 as select ID from t1whereid=4WITH CHECK OPTION;

createorreplaceview tv2 as select ID,NAMEfrom t1 whereid<4WITH CHECK OPTION;

SQL> select * from t1;

ID NAME

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

1 a

2 b

3 c

4 vd

SQL> select * from tv2;

ID NAME

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

1 a

2 b

3 c

SQL> insert into tv2 values (5,'ve');

1 row inserted

SQL> select * from t1;

ID NAME

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

1 a

2 b

3 c

4 vd

SQL> select * from tv2;

ID NAME

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

1 a

2 b

3 c

SQL> insert into tv2 values (5,'ve');

insert into tv2 values (5,'ve')

ORA-01402: view WITH CHECK OPTION where-clause violation

SQL> insert into t1 values (5,'ve');

1 row inserted

SQL> commit;

Commit complete

SQL> insert into tv1 values (6);

insert into tv1 values (6)

ORA-01400: cannot insert NULL into ("SH"."T1"."NAME")

SQL> delete from tv2 where id=1;

1 row deleted

SQL> commit;

Commit complete

SQL> select * from tv2;

ID NAME

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

2 b

3 c

SQL> delete from tv2 where id =4;

0 rows deleted

SQL> delete from t1 where id =4;

1 row deleted

相关理论：

• 创建视图时加WITH CHECK OPTION选项，这是用来处理DML选项.如果子查询包括WHERE子句，那私这个选项会防止插入视图中不可见的行，或者防止导致行从视图中消失的更新.默认情况不启用此选项，它会产生令人不安的结果。

• 创建视图时加WITH READ ONLY可以防止任何DML通过视图。

• WITH CHECK OPTION This has to do with DML. If the subquery includes a WHERE clause, then this option will prevent insertion of rows that wouldn’t be seen in the view or updates that would cause a row to disappear from the view. By default, this option is not enabled, which can give disconcerting results.

• WITH READ ONLY Prevents any DML through the view.

3. You need toextract details of those products in the SALES table where the PROD_ID column containsthe string '_D123'.

Which WHERE clause could be usedin 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

相关理论：

下划线通配符替代字面值中的-个字符。假设要搜索员工，这些员工姓氏的长度为4个字母，以字母" K" 开头，第二个字母未知，结尾为ng。可以执行下面的语句:

where last_name like 'K_ng' ;

当搜索包含百分比或者下划线字符的字面值时，会出现什么情况?与ESCAPE字符结合使用ESCAPE 标识符，可以转义(或者作为正常的非特殊符号对待)自然出现的下划线字符。在下面的例子中，返回以三个字符"SA "开头的所有JOB_ID值:

select jOb_id f rom jobs where job_id like 'SA\_%" escape '\';

The underscore wildcard symbol substitutes exactly one other character in a literal. Consider searching for employees whose last names are four letters long, begin with a “K,” have an unknown second letter, and end with an “ng.” You may issue the following statement:

where last_name like 'K_ng';

What about when you are searching for a literal that contains a percentage or underscore character? A naturally occurring underscore character may be escaped (or treated as a regular nonspecial symbol) using the ESCAPE identifier in conjunction with an ESCAPE character. In the following example, any JOB_ID values that begin with the three characters “SA_” will be returned:

select job_id from jobs where job_id like 'SA\_%' escape '\';

4. Whichtwo statements are true regarding single row functions? (Choose two.)

A. Theyaccept only a single argument.

B. Theycan be nested only to two levels.

C.Arguments can only be column values or constants.

D. Theyalways return a single result row for every row of a queried table.

E. Theycan return a data type value different from the one that is referenced.

Answer:DE