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.

eva luate the followingCREATE TABLE command:

CREATE TABLEnew_sales(prod_id, cust_id, order_date DEFAULT SYSDATE)

AS

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)

AS

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

as

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 <=SYSDATE - 2*365

WITH CHECK OPTION;

C. CREATE VIEW v2

AS SELECT prod_id,cust_id, time_id FROM SALES

WHERE time_id <=SYSDATE - 2*365

WITH CHECK OPTION;

D. CREATE VIEW 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

解析：

这道题的意思是哪个视图可以做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

解析：