【诗檀学院 Oracle OCP认证专题】Oracle 11g OCP 考题讲解051

ZHAODONG

1.View the Exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS, and TIMES tables.
The PROD_ID column is the foreign key in the SALES table, which references the PRODUCTS table.
Similarly, the CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.
Evaluate the 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;
Which statement is true regarding the above command?
A. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition.
B. The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table.
C. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
D. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table

1.查看SALES, CUSTOMERS, PRODUCTS, and TIMES tables结构，PROD_ID 列是SALES table的外键，参照PRODUCTS 表。
同样，CUST_ID and TIME_ID 列也是SALES table的外键，分别参照CUSTOMERS and TIMES 表。
评估下面的CREATE TABLE命令：
CREATE TABLE new_sales(prod_id, cust_id, order_date DEFAULT SYSDATE)
AS
SELECT prod_id, cust_id, time_id
FROM sales;
关于上面这条命令哪句话是正确的？
A. NEW_SALES表不能被建立，因为列定义时不能指定默认值。
B. NEW_SALES表可以建立，并且所有指定列上的已定义的非空约束会传递给新表。
C. NEW_SALES表不能被建立，因为CREATE TABLE命令中的列名与SELECT子句中的列名不匹配。
D. NEW_SALES表可以建立，并且所有指定列上的已定义的外键约束会传递给新表。
答案为B
因为使用CREATE TABLE..AS..语句创建表时，原表列上如果有显式非空约束会传递给新表，其它约束不会传递。
具体请参照：《Oracle Database SQL Language Reference》16-66，16-67 AS subquery部分。

ZHAODONG

2. View the Exhibit to examine the description for the SALES table.
Which views can have all DML 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;

2. 查看SALES表的描述。
可以在哪一个视图上面执行所有的DML操作？（选择所有可以的）

答案为AB
WITH CHECK OPTION表示：Oracle数据库禁止更改不包含在子查询条件里的行。C不正确的原因为有其它列不能为空，D不正确的原因为一个复杂视图，并且SUM(quantity_sold)需要一个别名。

ZHAODONG

3. You need to extract details of those products in the SALES table where the PROD_ID column contains the string '_D123'.
Which WHERE clause could be 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 '\_'

3. 你需要从SALES表中提取出PROD_ID列包含‘_D123’字符串的产品明细。
下面哪一个WHERE条件子句可以获得所需的输出结果？
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 '\_'

答案为B

答案解析：ESCAPE子句为指定转译字符，因为‘_’下滑线在LIKE子句中指的是任意一个字符，所以需要把_’下滑线进行转译。

ZHAODONG

4. Which two statements are true regarding single row functions? (Choose two.)
A. They accept only a single argument.
B. They can be nested only to two levels.
C. Arguments can only be column values or constants.
D. They always return a single result row for every row of a queried table.
E. They can return a data type value different from the one that is referenced.

4.关于单行函数，哪两句话是正确的？（选择两个）
A. 只接受一个参数
B. 只可以被嵌套两级
C. 参数只能是列值或者常量.
D. 对于查询表的每一行总是返回一个结果行.
E. 返回的数据类型值可以与之前引用的数据类型不同

答案为DE

这里提一下Multiple-Row Functions（多行函数）：多个行返回一个结果行。
A不对，单行函数可以接受一个或多个参数，B不对可以被嵌套多级，C不对参数还可以是变量和表达式。
D:他们总是返回一个查询表的每行一个结果行
他们可以返回一个不同的从一个参考数据类型值

ZHAODONG

5. Which SQL statements would display the value 1890.55 as $1,890.55? (Choose three)
A. SELECT TO_CHAR(1890.55,'$0G000D00') FROM DUAL;
B. SELECT TO_CHAR(1890.55,'$9,999V99') FROM DUAL;
C. SELECT TO_CHAR(1890.55,'$99,999D99') FROM DUAL;
D. SELECT TO_CHAR(1890.55,'$99G999D00') FROM DUAL;
E. SELECT TO_CHAR(1890.55,'$99G999D99') FROM DUAL;

5.哪一个SQL语句可以把1890.55显示成$1,890.55 （选择三个）
A. SELECT TO_CHAR(1890.55,'$0G000D00') FROM DUAL;
B. SELECT TO_CHAR(1890.55,'$9,999V99') FROM DUAL;
C. SELECT TO_CHAR(1890.55,'$99,999D99') FROM DUAL;
D. SELECT TO_CHAR(1890.55,'$99G999D00') FROM DUAL;
E. SELECT TO_CHAR(1890.55,'$99G999D99') FROM DUAL

答案为：ADE

ZHAODONG

6. Examine the structure of the SHIPMENTS table:
name               Null             Type
PO_ID              NOT NULL       NUMBER(3)
PO_DATE           NOT NULL       DATE
SHIPMENT_DATE    NOT NULL       DATE
SHIPMENT_MODE                    VARCHAR2(30)
SHIPMENT_COST                     NUMBER(8,2)
You want to generate a report that displays the PO_ID and the penalty amount to be paid if the SHIPMENT_DATE is later than one month from the PO_DATE. The penalty is $20 per day.
Evaluate the following two 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 true regarding the above commands?
A. Both execute successfully and give correct results.
B. Only the first query executes successfully but gives a wrong result.
C. Only the first query executes successfully and gives the correct result.
D. Only the second query executes successfully but gives a wrong result.
E. Only the second query executes successfully and gives the correct result.

6.检查SHIPMENTS表结构：
你想获取一个报表，报表内容显示PO_ID和支付的罚款总金额，SHIPMENT_DATE与PO_DATE进行比较，SHIPMENT_DATE如果比PO_DATE晚一个月，则每天罚款$20。
评估下面两条查询语句：
关于上面的命令哪句话是正确的？

答案为C

MONTHS_BETWEEN(date1, date2): 返回date1和date2两个日期之间间隔几个月，结果可以为正或为负。如果date1晚于date2则结果为正;如果date1早于date2则结果为负;结果的非整数部分也代表月间隔的一部分。
DECODE(expr,search1,result1[,search2,result2……,default])：比较expr与search，如果等于search1则返回result1，如果等于search2则返回result2，依次类推，如果都不等于，如果有default则返回default，否则返回NULL.
ORACLE在比较之前，会自动把expr和每一个search隐式转换成第一个search（search1）的数据类型。自动把返回值转换成第一个result（result1）的数据类型。如果第一个result的数据类型为CHAR或者值是null，则Oracle转换返回值为VARCHAR2.
在DECODE函数中,NULL是相等的，如果expr为空，则Oracle将会返回第一个为NULL的search所对应的result。DECODE列表中的最大表达式个数为255个。

ZHAODONG

7. Which two statements are true regarding the USING and ON clauses in table joins? (Choose two.)
A. Both USING and ON clauses can be used for equijoins and nonequijoins.
B. A maximum of one pair of columns can be joined between two tables using the ON clause.
C. The ON clause can be used to join tables on columns that have different names but compatible data types.
D. The WHERE clause can be used to apply additional conditions in SELECT statements containing the ON or the USING clause.

7.关于表连接的USING和ON子句，哪两句话是正确的？（选择两个）
A. USING和ON子句可以用于等值连接和非等值连接。
B. 使用ON子句最大只能使用两个列连接两个表.
C. ON子句用于连接表的列可以是不同的名字，但是数据类型要兼容。
D. 在包含ON或USING子句的SELECT命令中，WHERE子句可以做为附加的条件。

答案为CD
A不对USING用于等值连接ON用于非等值连接，B不对ON子句可以连接多个列。

ZHAODONG

8. View the Exhibit and examine the structure of the CUSTOMERS table.
Which two tasks would require subqueries or joins to be executed in a single statement? (Choose two)

A. listing of customers who do not have a credit limit and were born before 1980
B. finding the number of customers, in each city, whose marital status is 'married'
C. finding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'
D. listing of those customers whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo’.
E. finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers.
8.查看CUSTOMERS表结构。
哪两个任务需要在一条语句中执行子查询或表连接？（选择两个）
A. 列出没有信贷限额并且1980年以前出生的客户。
B. 查找每个城市的已婚客户的数量。
C. 查找属于'Tokyo'或者 'Sydney'两个城市的男性客户的平均信贷限额。
D 列出与'Tokyo'城市的客户的信贷限额相等的客户
E. 查找每个城市的客户数量，这些客户的信贷限额大于所有客户的平均信贷限额。
答案为DE

ZHAODONG

9.Which statement is true regarding the INTERSECT operator?
A. It ignores NULL values.
B. Reversing the order of the intersected tables alters the result.
C. The names of columns in all SELECT statements must be identical.
D. The number of columns and data types must be identical for all SELECT statements in the query

9.关于INTERSECT操作符，哪句话正确？
A. 它忽略空值
B. 交换交集表的前后顺序可以改变交集结果
C. 所有SELECT查询语句中的列的名字必须相同。
D. 对于所有SELECT查询语句，列的数量和数据类型必须相同。

答案为D
A不对不忽略空值，B不对不能改变结果，C不对列的名字不需要相同。

ZHAODONG

10. View the Exhibit; examine the structure of the PROMOTIONS table.
Each promotion has a duration of at least seven days.
Your manager has asked you to generate a report, which provides the weekly cost for each promotion done to l date.
Which query would achieve the required result?
A. SELECT promo_name, promo_cost/promo_end_date-promo_begin_date/7
FROM promotions;
B. SELECT promo_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

10查看PROMOTIONS表结构。
每个促销的促销期至少持续七天。
你的经理让你去获取一个报表，该报表提供促销期间每周的成本。
哪个查询能得到所需的结果？
答案为：D

ZHAODONG

11.View the Exhibit and examine the structure of the PRODUCTS table.
All products have a list price.
You issue the following command to display the total price of each product after a discount of 25% and a tax of 15% are applied on 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 "TOTAL PRICE"
FROM products;
What would be the outcome if all the parentheses are removed from the above statement?

A. It produces a syntax error.
B. The result remains unchanged.
C. The total price value would be lower than the correct value.
D. The total price value would be higher than the correct value.
11.查看PRODUCTS表结构
所有产品有一个list price。
执行下面这条命令显示每个产品打折25%和交15%税后的总价格，每个产品会有$100的运费。
如果上面这条语句的括号全都去掉，会输出什么？
A. 报语法错误
B. 结果不变
C. 总价格将低于正确值
D. 总价格将高于正确值
答案为：B
因为去掉括号后虽然计算顺序稍微变化，但是不影响结果。

ZHAODONG

12. You need to produce a report where each customer's credit limit has been incremented by $1000. In the output, the customer's last name should have the heading Name and the incremented credit limit should be labeled New Credit Limit. The column headings should have only the first letter of each word in uppercase.
Which statement would accomplish this requirement?
A. SELECT cust_last_name Name, cust_credit_limit + 1000
"New Credit Limit"
FROM customers;
B. SELECT cust_last_name AS Name, cust_credit_limit + 1000
AS New Credit Limit
FROM customers;
C. SELECT cust_last_name AS "Name", cust_credit_limit + 1000
AS "New Credit Limit"
FROM customers;
D. SELECT INITCAP(cust_last_name) "Name", cust_credit_limit + 1000
INITCAP("NEW CREDIT LIMIT")
FROM customers;

12.你需要获得一个报表，该报表显示每一个客户的信贷限额增加$1000后的结果，客户的last name列标题应显示为Name，并且增加的信贷限额列标题应显示为New Credit Limit，列标题应该每个单词的首字母大写显示。
哪个命令能完成该需求？

答案为：C
A不对Name需要加双引号，B两个都需要加双引号，D里的别名不能使用INITCAP("NEW CREDIT LIMIT")

ZHAODONG

13. View the Exhibit and examine the structure of the PRODUCTS table.
You need to generate a report in the following format:
CATEGORIES
5MP Digital Photo Camera's category is Photo
Y Box's category is Electronics
Envoy Ambassador's category is Hardware
Which two queries would give the required output? (Choose two.)

A. SELECT prod_name q'''s category is ' prod_category CATEGORIES
FROM products;
B. SELECT prod_name q'['s ]'category is ' prod_category CATEGORIES
FROM products;
C. SELECT prod_name q'\'s\' ' category is ' prod_category CATEGORIES
FROM products;
D. SELECT prod_name q'<'s >' 'category is ' prod_category CATEGORIES
FROM products;

13.查看PRODUCTS表结构。
你需要获得如下格式的报表：
哪两个查询给出需求的结果？（选择两个）
答案为：CD
A和B语法不对

ZHAODONG

14. Using the CUSTOMERS table, you need to generate a report that shows 50% of each credit amount in each income level. The report should NOT show any repeated credit amounts in each income level.
Which query would give the required result?
A. SELECT cust_income_level, DISTINCT cust_credit_limit * 0.50
AS "50% Credit Limit"
FROM customers;
B. SELECT DISTINCT cust_income_level, DISTINCT cust_credit_limit * 0.50
AS "50% Credit Limit"
FROM customers;
C. SELECT DISTINCT cust_income_level ||' '|| cust_credit_limit * 0.50 AS "50% Credit Limit"
FROM customers;
D. SELECT cust_income_level|| ' ' ||cust_credit_limit * 0.50 AS "50% Credit Limit"
FROM customers;


14 使用CUSTOMERS表，你需要获得一个报表，显示在每一个收入水平下的每一个信贷数量的50%，报表不显示重复的值。
哪一个查询能给出所需的结果？
答案为：C         A和B语法不对，D没有去重复值。

ZHAODONG

15. View the Exhibit and examine the data in the CUSTOMERS table.
Evaluate the following query:
SQL> SELECT cust_name AS "NAME", cust_credit_limit/2 AS MIDPOINT,MIDPOINT+100 AS "MAX LOWER LIMIT"
FROM customers;
The above query produces an error on execution.
What is the reason for the error?

A. An alias cannot be used in an expression.
B. The alias NAME should not be enclosed with in double quotation marks .
C. The MIDPOINT+100 expression gives an error because CUST_CREDIT_LIMIT contains NULL values.
D. The alias MIDPOINT should be enclosed with in double quotation marks for the
CUST_CREDIT_LIMIT/2 expression.

15.查看CUSTOMERS表的数据。
评估下面的查询语句：
上面的语句在执行时会产生一个错误。
错误原因是什么？
A. 别名不能用在表达式里
B. 别名不应该附在双引号里
C. MIDPOINT+100表达式给出一个错误，因为CUST_CREDIT_LIMIT包含空值。
values.
D. 对于CUST_CREDIT_LIMIT/2表达式，别名MIDPOINT应该附在双引号里。
答案为：A

ZHAODONG

16. Evaluate the following query:
SQL> SELECT promo_name  q'{'s start date was }'  promo_begin_date
AS "Promotion Launches"
FROM promotions;
What would be the outcome of the above query?
A. It produces an error because flower braces have been used.
B. It produces an error because the data  types are not matching.
C. It executes successfully and introduces an  's at the end of each promo_name in the output.
D. It executes successfully and displays the literal " {'s start date was } " for each row in the output

16. 评估下面的查询语句：
上面的查询语句输出什么内容？
A. 它产生一个错误，因为花括号被使用。
B. 它产生一个错误，因为数据类型不匹配。
C. 它执行成功，并且每一个promo_name后面会输出一个’s.
D. 它执行成功，并且每一行会显示" {'s start date was } "。
答案为：C

ZHAODONG

17. View the Exhibit and examine the data in the EMPLOYEES table.
You want to generate a report showing the total compensation paid to each employee to date.
You issue the following query:
SQL>SELECT ename ' joined on ' hiredate     
', the total compensation paid is '   
TO_CHAR(ROUND(ROUND(SYSDATE-hiredate)/365) * sal + comm)   
"COMPENSATION UNTIL DATE"   
FROM employees;
What is the outcome?

A. It generates an error because the alias is not valid.
B. It executes successfully and gives the correct output.
C. It executes successfully but does not give the correct output.
D. It generates an error because the usage of the  ROUND function in the expression is not valid.
E. It generates an error because the concatenation operator can be used to combine only two items.

17. 查看EMPLOYEES表的数据。
你想获得一个报表，显示每位员工按年限所获得的总薪酬。
你执行下面的查询语句：
结果是什么？
A. 它产生一个错误，因为别名是无效的。
B. 它执行成功，并且给出正确的输出。
C. 它执行成功，但是没有给出正确结果。
D. 它产生一个错误，因为在表达式中使用ROUND函数是无效的。
E. 它产生一个错误，因为连接操作符只能用来连接两项。

答案为：C   因为COMM里有NULL值，参与运算后会得到空值，会使某些行的值不正确。

ZHAODONG

18. Examine the structure of the PROMOTIONS table:
name              Null        Type
PROMO_ID         NOT NULL   NUMBER(6)
PROMO_NAME      NOT NULL   VARCHAR2(30)
PROMO_CATEGORY  NOT NULL   VARCHAR2(30)
PROMO_COST       NOT NULL   NUMBER(10,2)
The management wants to see a report of unique promotion costs in each promotion category.
Which query would achieve the required result?
A. SELECT DISTINCT promo_cost, promo_category FROM promotions;
B. SELECT promo_category, DISTINCT promo_cost FROM promotions;
C. SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;
D. SELECT DISTINCT promo_category, promo_cost FROM promotions;

18. 检查PROMOTIONS表结构。
管理人员想看一个报表，显示每一个促销种类唯一的促销成本。
哪一个查询能获得所需结果？

答案为：D
A的内容相反，B、C的语法不正确

ZHAODONG

19. Evaluate the following query:
SELECT INTERVAL '300' MONTH,
INTERVAL '54-2' YEAR TO MONTH,
INTERVAL '11:12:10.1234567' HOUR TO SECOND
FROM dual;
What is the correct output of the above query?
A. +25-00 , +54-02, +00 11:12:10.123457
B. +00-300, +54-02, +00 11:12:10.123457
C. +25-00 , +00-650, +00 11:12:10.123457
D. +00-300 , +00-650, +00 11:12:10.123457

19. 评估下面的查询语句：
上面的查询语句应输出什么？
答案为A  

INTERVAL '300' MONTH：300个月为+25-00（25年-0个月，即25*12+0）
INTERVAL '54-2' YEAR TO MONTH：+54-02
INTERVAL '11:12:10.1234567' HOUR TO SECOND: +00 11:12:10.123457（+00表示0天）
解释：
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
Interval year(precision) to month  
precision是这个时限的年部分所要求的最大位数。 默认为2，范围为0~9
Interval day(d_precision) to second(s_precision)  
d_precision是这个时限的天部分所要求的最大位数,默认为2，范围也是0~9
s_precision是这个时限的秒部分所要求的小数点右边的位数，默认为6，范围是0~9
求精确的时间间隔
把一段间隔时间明细化得一种方法
例如 问你活了多少年了? 我们都能答出来
     问你活了多少天了? 我们算起来很麻烦 中间又有闰年什么的.
而这种类型就能给取出非常准确的时间间隔
例如计算SCOTT至今为止在公司多久了.
year to month 返回23年10个月
day to second 返回8716天14小时....
SQL> SELECT (SYSdate - hiredate) YEAR TO MONTH FROM emp where ename='SCOTT';
(SYSDATE-HIREDATE)YEARTOMONTH
---------------------------------------------------------------------------
+23-10
SQL> SELECT (SYSdate - hiredate) DAY(9) TO SECOND FROM emp where ename='SCOTT';
(SYSDATE-HIREDATE)DAY(9)TOSECOND
---------------------------------------------------------------------------
+000008716 14:49:34.000000

ZHAODONG

20. Which three statements are true regarding the data types in Oracle Database 10g/11g? (Choose three.)
A. Only one LONG column can be used per table.
B. A TIMESTAMP data type column stores only time values with fractional seconds.
C. The BLOB data type column is used to store binary data in an operating system file.
D. The minimum column width that can be specified for a VARCHAR2 data type column is one.
E. The value for a CHAR data type column is blank-padded to the maximum defined column

20. 关于Oracle Database 10g/11g中的数据类型，哪三个语句是正确的？（选择三个）
A. 每个表中只能使用一个LONG列。
B. 一个TIMESTAMP数据类型列只能存储带有小数秒的时间值。
C. BLOG数据类型列被用于存储二进制数据的操作系统文件。
D. 对于VARCHAR2数据类型最小的列宽可以为1（VARCHAR2为可变长度的数据类型）.
E. CHAR数据类型列的值是用空格填充到列定义的最大值（也就是说，如果定义了CHAR(20),而字符串长度不够20个，则用空格填充不够的位数，CHAR为固定长度数据类型）

答案为A、D、E
B不对因为TIMESTAMP可以存储不带有小数秒的日期或时间
C不对因为BLOG存储的是非结构化的二进制大对象
LONG: 可变长的字符串数据，最长2G，LONG具有VARCHAR2列的特性，可以存储长文本一个表中最多一个LONG列
　　LONG RAW: 可变长二进制数据，最长2G
　　CLOB:  字符大对象Clob 用来存储单字节的字符数据
　　NCLOB: 用来存储多字节的字符数据
　　BLOB: 用于存储二进制数据
　　BFILE: 存储在文件中的二进制数据，这个文件中的数据只能被只读访。但该文件不包含在数据库内。
        bfile字段实际的文件存储在文件系统中,字段中存储的是文件定位指针.bfile对oracle来说是只读的,也不参与事务性控制和数据恢复. 　
　　CLOB，NCLOB，BLOB都是内部的LOB(Large Object)类型，最长4G，没有LONG只能有一列的限制
　　要保存图片、文本文件、Word文件各自最好用哪种数据类型?
　　--BLOB最好，LONG　RAW也不错，但Long是oracle将要废弃的类型，因此建议用BLOB。

ZHAODONG

21. Examine the description of the EMP_DETAILS table given below:
name           NULL          TYPE
EMP_ID         NOT NULL     NUMBER
EMP_NAME     NOT NULL     VARCHAR2 (40)
EMP_IMAGE                    LONG
Which two statements are true regarding SQL statements that can be executed on the EMP_DETAIL table? (Choose two.)
A. An EMP_IMAGE column can be included in the GROUP BY clause.
B. An EMP_IMAGE column cannot be included in the ORDER BY clause.
C. You cannot add a new column to the table with LONG as the data type.
D. You can alter the table to include the NOT NULL constraint on the EMP_IMAGE column

21.检查EMP_DETAILS表的描述（也就是表结构）
关于能在EMP_DETAILS 表上执行的SQL语句，哪两句话是正确的？（选择两个）
A. EMP_IMAGE表的一个列可以包含在GROUP BY子句中
B. EMP_IMAGE表的一个列不可以包含在ORDER BY子句中
C. 不能添加一个新的LONG数据类型列
D. 你能把EMP_IMAGE表的列转换成包含NOT NULL的约束

答案为：BC

A不对因为LONG列不能包含在GROUP BY子句中，当然也不能包含在ORDER BY或者DISTINCT或者CONNECT BY子句中。D不对因为如果列中已经有值可能会包含空值，这样就不能设置为NOT NULL。

ZHAODONG

22. You need to create a table for a banking application.One of the columns in the table has the following requirements:
1) You want a column in the table to store the duration of the credit period.
2) The data in the column should be stored in a format such that it can be easily added and subtracted with DATE data type without using conversion functions.
3) The maximum period of the credit provision in the application is 30 days.
4) The interest has to be calculated for the number of days an individual has taken a credit for.
Which data type would you use for such a column in the table?
A. DATE
B. NUMBER
C. TIMESTAMP
D. INTERVAL DAY TO SECOND
E. INTERVAL YEAR TO MONTH

22. 你需要为银行的应用程序建立一个表。表的其中一个列有以下需求：
1) 你想让该列存储信贷周期的持续时间
2) 该列的日期格式如下：它能非常容易的与DATE数据类型进行加减，无需使用转换函数
3) 应用程序中的最大信贷条款周期是30天
4) 已经使用信用卡的个人按天计算利息（貌似翻译的不太准确）
该列可以使用哪种数据类型？

答案为：D

A和C都是存储的时间点，不是一段时间，而B是数值型数据不能满足条件，E不正确因为虽然它也是存储一段时间但是，是从年到月的，不能满足第3条和第4条件需要精确到天；
存储单位为天和秒的时间间隔,The default format for INTERVAL DAY(2) TO SECOND(6) is 'DD HH:MI:SS.sss'.
INTERVAL DAY TO SECOND：存储一段时间，从天精确到秒，例如：
INTERVAL '4 5:12:10.222' DAY TO SECOND会输出：+04 05:12:10.222000
（DAY到MINUTE的默认位数为2位，SECOND默认为6位，DAY(3)可以改变位数，该值的意思为4天5小时12分10.222秒，222这里可以认为是222/1000秒）

ZHAODONG

23. Examine the structure proposed for the TRANSACTIONS table:
name               Null             Type
TRANS_ID          NOT NULL       NUMBER(6)
CUST_NAME        NOT NULL       VARCHAR2(20)
CUST_STATUS      NOT NULL       CHAR
TRANS_DATE       NOT NULL       DATE
TRANS_VALIDITY                     VARCHAR2
CUST_CREDIT_LIMIT                  NUMBER
Which statements are true regarding the creation and storage of data in the above table structure?
(Choose all that apply.)
A. The CUST_STATUS column would give an error.
B. The TRANS_VALIDITY column would give an error.
C. The CUST_STATUS column would store exactly one character.
D. The CUST_CREDIT_LIMIT column would not be able to store decimal values
E. The TRANS_VALIDITY column would have a maximum size of one character
F. The TRANS_DATE column would be able to store day, month, century, year, hour, minutes, seconds and fractions of seconds

23. 检查TRANSACTIONS表结构
关于上面表结构数据创建和存储哪句话是正确的？（选择所有合适的）
A. CUST_STATUS列将报错
B. TRANS_VALIDITY列将报错
C. CUST_STATUS列将精确的存储一个字符
D. CUST_CREDIT_LIMIT列不能存储小数值
E. TRANS_VALIDITY列将会有一个字符的最大大小
F. TRANS_DATE列能存储日，月，世纪，年，小时，分，秒，小数秒

答案为BC
A不对因为CUST_STATUS列定义的CHAR类型是可以省略size的，默认为1byte
D不对因为NUMBER可以存储小数值，例如：NUMBER(5,2)代表数值长度为5位，四舍五入保留两位小数。
E不对因为VARCHAR2数据类型必须要指定size，即VARCHAR(10)，不然会报错
F不对因为DATE类型不能存储小数秒

ZHAODONG

24. Examine the structure proposed for the TRANSACTIONS table:
name                 Null           Type
TRANS_ID            NOT NULL     NUMBER(6)
CUST_NAME          NOT NULL     VARCHAR2(20)
CUST_STATUS        NOT NULL     VARCHAR2
TRANS_DATE         NOT NULL     DATE
TRANS_VALIDITY                    INTERVAL DAY TO SECOND
CUST_CREDIT_VALUE               NUMBER(10)
Which two statements are true regarding the storage of data in the above table structure? (Choose two.)
A. The TRANS_DATE column would allow storage of dates only in the dd-mon-yyyy format.
B. The CUST_CREDIT_VALUE column would allow storage of positive and negative integers.
C. The TRANS_VALIDITY column would allow storage of a time interval in days, hours, minutes, and seconds.
D. The CUST_STATUS column would allow storage of data up to the maximum VARCHAR2 size of 4,000 characters.

24. 检查TRANSACTIONS表结构
关于该表结构的数据存储哪两句话是正确的？(选择两个)
A. TRANS_DATE列只能存储dd-mon-yyyy格式的数据
B. CUST_CREDIT_VALUE列可以存储正整数与负整数
C. TRANS_VALIDITY列可以存储从days, hours, minutes, 到seconds的一段时间间隔
D. CUST_STATUS列存储的数据可以达到VARCHAR2的最大值4000个字符

答案：BC
A不对因为DATE不光能存储该dd-mon-yyyy格式的日期数据，例如yyyy-mm-dd也可以
D不对因为VARCHAR2不能省略size，例如：VARCHAR2(10)

ZHAODONG

25. You need to create a table with the following column specifications:
1. Employee ID (numeric data type) for each employee
2. Employee Name (character data type) that stores the employee name
3. Hire date, which stores the date of joining the organization for each employee
4. Status (character data type), that contains the value 'ACTIVE' if no data is entered
5. Resume (character large object [CLOB] data type), which contains the resume submitted by the employee
Which is the correct syntax to create this table?
A. CREATE TABLE EMP_1
(emp_id NUMBER(4),
emp_name VARCHAR2(25),
start_date DATE,
e_status VARCHAR2(10) DEFAULT 'ACTIVE',
resume CLOB(200));
B. CREATE TABLE 1_EMP
(emp_id NUMBER(4),
emp_name VARCHAR2(25),
start_date DATE,
emp_status VARCHAR2(10) DEFAULT 'ACTIVE',
resume CLOB);
C. CREATE TABLE EMP_1
(emp_id NUMBER(4),
emp_name VARCHAR2(25),
start_date DATE,
emp_status VARCHAR2(10) DEFAULT "ACTIVE",
resume CLOB);
D. CREATE TABLE EMP_1
(emp_id NUMBER,
emp_name VARCHAR2(25),
start_date DATE,
emp_status VARCHAR2(10) DEFAULT 'ACTIVE',
resume CLOB) ;

25. 你需要建立一个符合下列规范的表：
1. 为每位员工建立一个Employee ID（数值数据类型）
2. 建立一个存储员工姓名的Employee Name (字符数据类型)
3. 雇佣日期，存储每位员工加入公司的日期
4. Status（状态，字符数据类型），如果没有数据输入则值为'ACTIVE'
5. Resume（个人简历，CLOB数据类型－字符大对象）,包含员工提交的个人简历
哪一个正确的语法能建立该表？

答案为：D
A不对因为CLOB数据类型不需要设置CLOB（200）size
B不对因为1_EMP如果不加双引号，表名只能以字母开头
C不对因为DEFAULT "ACTIVE"，字符默认值不能用双引号引用，需要使用单引号

ZHAODONG

26. Which is the valid CREATE TABLE statement?
A. CREATE TABLE emp9$# (emp_no NUMBER (4));
B. CREATE TABLE 9emp$# (emp_no NUMBER(4));
C. CREATE TABLE emp*123 (emp_no NUMBER(4));
D. CREATE TABLE emp9$# (emp_no NUMBER(4), date DATE);

26. 哪一个是有效的建表语句？
答案为：A

B不对因为不加双引号只能以字母开头
C不对因为不加双引表名中不能包含*号
D不对因为date不能做为列名
一般表名要以字母开头，可以包含字母、数字下划线(_),美元符($),井字号(#)

ZHAODONG

27. Which two statements are true regarding tables? (Choose two.)
A. A table name can be of any length.
B. A table can have any number of columns.
C. A column that has a DEFAULT value cannot store null values.
D. A table and a view can have the same name in the same schema
E. A table and a synonym can have the same name in the same schema.
F. The same table name can be used in different schemas in the same database.

27. 关于表哪两个句子是正确的？（选择两个）
A. 表名可以是任意长度的
B. 表能有任意数量的列
C. 有DEFAULT值的列不能存储空值
D. 在相同的schema里，表和视图可以有相同的名字
E. 在相同的schema里，表和同义词可以有相同的名字
F. 在相同的数据库不同的schema里，表名可以相同

答案为EF
A不正确因为表名的长度为1-30个字符
B不正确因为表的列最多为1000个
C不正确因为有DEFAULT值的列可以存储空值
D不正确因为在相同的schema里，表和视图的名字不能相同
E在这里说明一个，我使用CREATE SYNONYM建立相同表名字的SYNONYM不成功，但是建立PUBLIC SYNONYM成功，所以这句话也有点牵强

ZHAODONG

28. Which two statements are true regarding constraints? (Choose two.)
A. A foreign key cannot contain NULL values.
B. A column with the UNIQUE constraint can contain NULL values.
C. A constraint is enforced only for the INSERT operation on a table.
D. A constraint can be disabled even if the constraint column contains data.
E. All constraints can be defined at the column level as well as the table level.

28. 关于约束哪两个句子是正确的？（选择两个）
A. 外键不能包含空值
B. 唯一约束的列可以包含空值
C. 约束只能作用在INSERT操作上
D. 即使约束列包含数据，约束也能被禁用
E. 所有能在列级定义的约束，也能在表级定义

答案为：BD
A不对因为外键可以包含空值
C不对因为约束可以作用在INSERT,UPDATE,DELETE操作上
E不对因为NOT NULL约束只能在列级定义，不能在表级定义

ZHAODONG

29. Which two statements are true regarding constraints? (Choose two.)
A. A foreign key cannot contain NULL values.
B. The column with a UNIQUE constraint can store NULLS .
C. A constraint is enforced only for an INSERT operation on a table.
D. You can have more than one column in a table as part of a primary key.

29. 关于约束哪两个句子是正确的？（选择两个）
A. 外键不能包含空值
B. 唯一约束的列可以包含空值
C. 约束只能作用在INSERT操作上
D. 表的主键可以包含多个列

答案为：BD
A不对因为外键可以包含空值
C不对因为约束可以作用在INSERT,UPDATE,DELETE操作上

ZHAODONG

30. Evaluate the following CREATE TABLE commands:
CREATE TABLE orders
(ord_no NUMBER(2) CONSTRAINT ord_pk PRIMARY KEY,
ord_date DATE,
cust_id NUMBER(4));
CREATE TABLE ord_items
(ord_no NUMBER(2),
item_no NUMBER(3),
qty NUMBER(3) CHECK (qty BETWEEN 100 AND 200),
expiry_date date CHECK (expiry_date > SYSDATE),
CONSTRAINT it_pk PRIMARY KEY (ord_no,item_no),
CONSTRAINT ord_fk FOREIGN KEY(ord_no) REFERENCES orders(ord_no));
The above command fails when executed. What could be the reason?
A. SYSDATE cannot be used with the CHECK constraint.
B. The BETWEEN clause cannot be used for the CHECK constraint.
C. The CHECK constraint cannot be placed on columns having the DATE data type.
D. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY.

30. 评估下面的建表语句：
当执行上面的命令时失败。原因是什么？
A. SYSDATE不能用于CHECK约束中
B. BETWEEN子句不能用于CHECK约束中
C. CHECK约束不能作用在DATE类型的列
D. ORD_NO和ITEM_NO不能作为组合主键，因为ORD_NO是一个外键

答案：A
BETWEEN可以用于CHECK约束，CHECK约束可以作用在DATE类型的列，D选项没有这个限制，即使ORD_NO为一个外键，ORD_NO和ITEM_NO也可以组成组合主键

ZHAODONG

31. Evaluate the following SQL commands:
SQL>CREATE SEQUENCE ord_seq
INCREMENT BY 10
START WITH 120
MAXVALUE 9999
NOCYCLE;
SQL>CREATE TABLE ord_items
(ord_no NUMBER(4) DEFAULT ord_seq.NEXTVAL NOT NULL,
item_no NUMBER(3),
qty NUMBER(3) CHECK (qty BETWEEN 100 AND 200),
expiry_date date CHECK (expiry_date > SYSDATE),
CONSTRAINT it_pk PRIMARY KEY (ord_no,item_no),
CONSTRAINT ord_fk FOREIGN KEY(ord_no) REFERENCES orders(ord_no));
The command to create a table fails. Identify the reason for the SQL statement failure? (Choose all that apply.)
A. You cannot use SYSDATE in the condition of a CHECK constraint.
B. You cannot use the BETWEEN clause in the condition of a CHECK constraint.
C. You cannot use the NEXTVAL sequence value as a DEFAULT value for a column.
D. You cannot use ORD_NO and ITEM_NO columns as a composite primary key because ORD_NO is also the FOREIGN KEY

31. 评估下面的SQL命令：
建表命令失败，标识命令失败的原因？（选择所有合适的）
A. SYSDATE不能用在CHECK约束中
B. BETWEEN不能用在CHECK约束中
C. 不能使用NEXTVAL序列值作为一个列的默认值
D. 不能使用ORD_NO和ITEM_NO作为组合主键，因为ORD_NO是一个外键
答案：AC

ZHAODONG

32. Which CREATE TABLE statement is valid?
A. CREATE TABLE ord_details
(ord_no NUMBER(2) PRIMARY KEY,
item_no NUMBER(3) PRIMARY KEY,
ord_date DATE NOT NULL);
B. CREATE TABLE ord_details
(ord_no NUMBER(2) UNIQUE, NOT NULL,
item_no NUMBER(3),
ord_date DATE DEFAULT SYSDATE NOT NULL);
C. CREATE TABLE ord_details
(ord_no NUMBER(2) ,
item_no NUMBER(3),
ord_date DATE DEFAULT NOT NULL,
CONSTRAINT ord_uq UNIQUE (ord_no),
CONSTRAINT ord_pk PRIMARY KEY (ord_no));
D. CREATE TABLE ord_details
(ord_no NUMBER(2),
item_no NUMBER(3),
ord_date DATE DEFAULT SYSDATE NOT NULL,
CONSTRAINT ord_pk PRIMARY KEY (ord_no, item_no));

32 哪个建表语句是有效的？
答案为：D
A不正确因为一个表只能有一个主键
B不正确因为ord_no NUMBER(2) UNIQUE, NOT NULL（UNIQUE与NOT NULL中间不应该加逗号）
C不正确因为ord_date DATE DEFAULT NOT NULL没有指定默认值，并且ord_no列上不能同时添加唯一约束与主键约束，因为主键约束其实就是唯一约束和非空约束的组合，但是非空约束可以和主键约束一起创建。

ZHAODONG

33. You want to create an ORD_DETAIL table to store details for an order placed having the following business requirement:
1) The order ID will be unique and cannot have null values.
2) The order date cannot have null values and the default should be the current date.
3) The order amount should not be less than 50.
4) The order status will have values either shipped or not shipped.
5) The order payment mode should be cheque, credit card, or cash on delivery (COD).
Which is the valid DDL statement for creating the ORD_DETAIL table?

33.你想建立一个ORD_DETAIL表存储订单的信息，并且需要遵循下面的商业需求：
1) order ID要唯一，并且不能有空值
2) order date不能有空值，并且默认值为当前日期
3) order amount不应该小于50
4) order status的值或者是shipped，或者是not shipped
5) order payment mode应该为cheque（支票）,credit card（信用卡）,或者cash on delivery (货到付款COD)
建立ORD_DETAIL表，哪一个DDL语句有效？
A. CREATE TABLE ord_details
(ord_id NUMBER(2) CONSTRAINT ord_id_nn NOT NULL,
ord_date DATE DEFAULT SYSDATE NOT NULL,
ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min
CHECK (ord_amount > 50),
ord_status VARCHAR2(15) CONSTRAINT ord_status_chk
CHECK (ord_status IN ('Shipped', 'Not Shipped')),
ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk
CHECK (ord_pay_mode IN ('Cheque', 'Credit Card',
'Cash On Delivery')));
B. CREATE TABLE ord_details
(ord_id NUMBER(2) CONSTRAINT ord_id_uk UNIQUE NOT NULL,
ord_date DATE DEFAULT SYSDATE NOT NULL,
ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min
CHECK (ord_amount > 50),
ord_status VARCHAR2(15) CONSTRAINT ord_status_chk
CHECK (ord_status IN ('Shipped', 'Not Shipped')),
ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk
CHECK (ord_pay_mode IN ('Cheque', 'Credit Card',
'Cash On Delivery')));
C. CREATE TABLE ord_details
(ord_id NUMBER(2) CONSTRAINT ord_id_pk PRIMARY KEY,
ord_date DATE DEFAULT SYSDATE NOT NULL,
ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min
CHECK (ord_amount >= 50),
ord_status VARCHAR2(15) CONSTRAINT ord_status_chk
CHECK (ord_status IN ('Shipped', 'Not Shipped')),
ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk
CHECK (ord_pay_mode IN ('Cheque', 'Credit Card',
'Cash On Delivery')));
D. CREATE TABLE ord_details
(ord_id NUMBER(2),
ord_date DATE NOT NULL DEFAULT SYSDATE,
ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min
CHECK (ord_amount >= 50),
ord_status VARCHAR2(15) CONSTRAINT ord_status_chk
CHECK (ord_status IN ('Shipped', 'Not Shipped')),
ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk
CHECK (ord_pay_mode IN ('Cheque', 'Credit Card',
'Cash On Delivery')));

答案为：C
A不正确因为ord_id列只有非空约束，没有唯一约束不满足条件1
B 不正确因为ord_amount列应该是大于等于50的不满足条件3
D 不正确因为ord_id没有定义相关约束，不满足条件1，并且ord_date DATE NOT NULL DEFAULT SYSDATE语法也不正确，DEFAULT SYSDATE应该在NOT NULL的前面

ZHAODONG

34. You created an ORDERS table with the following description:
name             Null               Type
ORD_ID          NOT NULL          NUMBER(2)
CUST_ID         NOT NULL          NUMBER(3)
ORD_DATE       NOT NULL          DATE
ORD_AMOUNT    NOT NULL          NUMBER (10,2)
You inserted some rows in the table. After some time, you want to alter the table by creating the PRIMARY KEY constraint on the ORD_ID column. Which statement is true in this scenario?
A. You cannot have two constraints on one column.
B. You cannot add a primary key constraint if data exists in the column.
C. The primary key constraint can be created only at the time of table creation .
D. You can add the primary key constraint even if data exists, provided that there are no duplicate values.

34. 你使用如下表结构建立了一个ORDERS表：
你往表里插入了一些行。不久之后，你想在ORD_ID列上建立主键约束来改变表，这种场景下哪句话是正确的？
A. 一个列上不能有两个约束
B. 如果数据已经存在不能添加主键约束
C. 主键约束只有在表创建时才能被创建
D. 即使数据存在，如果没有重复值，你能添加主键约束

答案：D
一个列上可以有两个约束，例如NOT NULL与PRIMARY KEY

ZHAODONG

35. Which two statements are true regarding constraints? (Choose two.)
A. A table can have only one primary key and one foreign key.
B. A table can have only one primary key but multiple foreign keys.
C. Only the primary key can be defined at the column and table levels.
D. The foreign key and parent table primary key must have the same name.
E. Both primary key and foreign key constraints can be defined at both column and table levels.

35.关于约束哪两个句子是正确的？（选择两个）
A. 一个表只能有一个主键和一个外键
B. 一个表只能有一个主键，但可以有多个外键
C. 只有主键能被定义成列级或表级
D. 外键和父表主键必须名字相同
E. 主键和外键都可以被定义成列级或表级

答案为：BE
A不正确因为一个表只能有一个主键，但可以有多个外键
C不正确因为除了NOT NULL只有被定义成列级，其它的约束都可以被定义成列级或表级
D不正确因为名字可以不同，但是数据类型需一致

ZHAODONG

36. Examine the following SQL commands:(检查下面的SQL语句)
SQL>CREATE TABLE products (
prod_id NUMBER(3) CONSTRAINT p_ck CHECK (prod_id > 0),
prod_name CHAR(30),
prod_qty NUMBER(6),
CONSTRAINT p_name NOT NULL,
CONSTRAINT prod_pk PRIMARY KEY (prod_id));
SQL>CREATE TABLE warehouse (
warehouse_id NUMBER(4),
roomno NUMBER(10) CONSTRAINT r_id CHECK(roomno BETWEEN 101 AND 200),
location VARCHAR2(25),
prod_id NUMBER(3),
CONSTRAINT wr_pr_pk PRIMARY KEY (warehouse_id,prod_id),
CONSTRAINT prod_fk FOREIGN KEY (prod_id) REFERENCES products(prod_id));
Which statement is true regarding the execution of the above SQL commands?
（关于执行上面的SQL命令，哪句话是正确的？）
A. Both commands execute successfully.（两个命令都执行成功）
B. The first CREATE TABLE command generates an error because the NULL constraint is not valid.（第一个命令报错，因为空约束是无效的）
C. The second CREATE TABLE command generates an error because the CHECK constraint is not valid.（第二个命令报错，因为CHECK约束是无效的）
D. The first CREATE TABLE command generates an error because CHECK and PRIMARY KEY constraints cannot be used for the same column.
（第一个命令报错，因为CHECK和主键约束不能用在相同的列上）
E.The first CREATE TABLE command generates an error because the column PROD_ID cannot be used in the PRIMARY KEY and FOREIGN KEY constraints.
（第一个命令报错，因为PROD_ID列不能被用于主键和外键约束）
答案为：B
第一个命令报错，因为CONSTRAINT p_name NOT NULL不能定义为表级，第一个命令里的CHECK和主键约束能用在相同的列上。

ZHAODONG

37. You issued the following command to drop the PRODUCTS table:
SQL> DROP TABLE products;
What is the implication of this command? (Choose all that apply.)
A. All data along with the table structure is deleted.
B. The pending transaction in the session is committed.
C. All indexes on the table will remain but they are invalidated.
D. All views and synonyms will remain but they are invalidated
E. All data in the table are deleted but the table structure will remain

37.执行下面的命令删除PRODUCTS表：
该命令隐含什么意思？（选择所有合适的）
A. 表结构和所有数据被删除
B. 该会话中的暂挂事务被提交
C. 表上所有的索引会保留，但是变为无效的
D. 视图和同义词会保留，但是变为无效的
E. 表中所有的数据被删除，但是表结构保留

答案为：ABD
表删除后索引会被删除（参考SQL Language Reference里的DROP TABLE部分，但是我自己做实验删除表后查询user_constraints或者user_cons_columns数据字典视图时是会有和回收站里名字一样的索引，不知道为什么没有移入回收站，但是如果drop table加purge的话，这两个数据字典视图里的相关索引就会删除），但是视图和同义词会保留变为无效的

ZHAODONG

38. Which two statements are true regarding views? (Choose two.)
A. A simple view in which column aliases have been used cannot be updated.
B. Rows cannot be deleted through a view if the view definition contains the DISTINCT keyword.
C. Rows added through a view are deleted from the table automatically when the view is dropped.
D. The OR REPLACE option is used to change the definition of an existing view without dropping and re-creating it.
E. The WITH CHECK OPTION constraint can be used in a view definition to restrict the columns displayed through the view.

38. 关于视图哪两个句子是正确的？（选择两个）
A. 一个简单的视图中使用别名的列不能被更新
B. 如果视图定义时包含了DISTINCT关键字，不能通过视图删除行
C. 当视图被删除时，通过视图添加的行从表中自动删除
D. OR REPLACE选项用于改变一个已存在的视图的定义，而不需要删除后重建
E. WITH CHECK OPTION用于限制通过视图显示的列

答案：BD
A不对，简单视图中使用别名的列是可以使用UPDATE命令更新的
E不对，WITH CHECK OPTION用于限制通过视图更改基表的条件，禁止更改不包含在子查询条件里的行。

ZHAODONG

39. Evaluate the following command:（评估下面的命令）
CREATE TABLE employees
(employee_id NUMBER(2) PRIMARY KEY,
last_name VARCHAR2(25) NOT NULL,
department_id NUMBER(2) NOT NULL,
job_id VARCHAR2(8),
salary NUMBER(10,2));
You issue the following command to create a view that displays the IDs and last names of the sales staff in the organization:（执行下面的命令建立一个视图，显示IDs和last names）
CREATE OR REPLACE VIEW sales_staff_vu AS
SELECT employee_id,last_name,job_id
FROM employees
WHERE job_id LIKE 'SA_%'
WITH CHECK OPTION;
Which two statements are true regarding the above view? (Choose two.)
A. It allows you to insert rows into the EMPLOYEES table.(允许插入行)
B. It allows you to delete details of the existing sales staff from the EMPLOYEES table.
（允许删除员工信息）
C. It allows you to update job IDs of the existing sales staff to any other job ID in the EMPLOYEES table.（允许更新已存在员工的jobIDs为任意job ID）
D. It allows you to insert IDs, last names, and job IDs of the sales staff from the view if it is used in multitable INSERT statements.
（如果使用multitable INSERT语句，允许通过视图插入员工的IDs, last names, and job IDs）

答案：BD（但是D答案查资料multitable INSERT不能用于视图，所有只有硬记住了）
A不正确，因为视图里不包括非空的department_id列，所以不能插入行
C不正确，不能任意更新员工jobID，需要使用WHERE job_id LIKE 'SA_%'进行条件限制

ZHAODONG

40. View the Exhibit to examine the description for the SALES and PRODUCTS tables.
检查SALES and PRODUCTS表结构
You want to create a SALE_PROD view by executing the following SQL statement:
你想通过下面的SQL语句建立SALE_PROD视图
CREATE VIEW sale_prod
AS SELECT p.prod_id, cust_id, SUM(quantity_sold) "Quantity" , SUM(prod_list_price) "Price"
FROM products p, sales s
WHERE p.prod_id=s.prod_id
GROUP BY p.prod_id, cust_id;
Which statement is true regarding the execution of the above statement?
关于上面的语句哪句话是正确的？
A. The view will be created and you can perform DML operations on the view.
（视图将会建立，你能在视图上执行DML操作）
B. The view will be created but no DML operations will be allowed on the view.
（视图将会建立，但是DML操作不能在视图上执行）
C. The view will not be created because the join statements are not allowed for creating a view.（视图不能建立，因为建立视图不能使用join语句）
D. The view will not be created because the GROUP BY clause is not allowed for creating a view（视力不能被建立，因为因为建立视图不能使用GROUP BY语句）

答案为：B
视图可以使用join,group by等子句

ZHAODONG

41. Which two statements are true regarding views? (Choose two.)
A. A subquery that defines a view cannot include the GROUP BY clause.
B. A view that is created with the subquery having the DISTINCT keyword can be updated.
C. A view that is created with the subquery having the pseudo column ROWNUM keyword cannot be updated.
D. A data manipulation language ( DML) operation can be performed on a view that is created with the subquery having all the NOT NULL columns of a table.

41. 关于视图哪两句话是正确的？（选择两个）
A. 定义视图的子查询不能包含GROUP BY子句
B. 包含DISTINCT关键字的子查询建立的视图可以被更新
C. 包含ROWNUM关键字伪列的子查询建立的视图不能被更新
D. 包含表中所有非空列的子查询建立的视图可以执行DML操作

答案：CD
A可以包含GROUP BY子句，B包含DISTINCT关键字不可以更新

ZHAODONG

42. Which three statements are true regarding views? (Choose three.)
A. Views can be created only from tables.
B. Views can be created from tables or other views.
C. Only simple views can use indexes existing on the underlying优先 tables.
D. Both simple and complex views can use indexes existing on the underlying tables.
E. Complex views can be created only on multiple tables that exist in the same schema.
F. Complex views can be created on multiple tables that exist in the same or different schemas.

42. 关于视图哪三个句子是正确的？（选择三个）
A. 视图只能使用表来建立。
B. 视图可以使用表或其它视图来建立
C. 只有简单视图能使用表上存在的索引
D. 简单视图和复杂视图都能使用表上存在的索引
E. 复杂视图只能使用一个schema里的多个表进行建立
F. 复杂视图可以使用相同或不同schema里的多个表进行建立
答案：BDF

ZHAODONG

43. Evaluate the following CREATE SEQUENCE statement:
(评估下面的CREATE SEQUENCE语句)
CREATE SEQUENCE seq1
START WITH 100
INCREMENT BY 10
MAXVALUE 200
CYCLE
NOCACHE;
The SEQ1 sequence has generated numbers up to the maximum limit of 200. You issue the following SQL statement:（SEQ1序列已经增长到200的最大值限制，你执行下面的SQL语句）
SELECT seq1.nextval FROM dual;
What is displayed by the SELECT statement?（SELECT语句显示什么？）
A. 1
B. 10
C. 100
D. an error

答案为：A
因为该序列是循环的，到达最大值后从最小值MINVALUE开始循环，该序列因为省略MINVALUE省略，所以默认为NOMINVALUE最小值为 1，所有又从1开始。

ZHAODONG

44. View the Exhibit and examine the structure of the ORD table.（查看ORD表结构）
Evaluate the following SQL statements that are executed in a user session in the specified order:（按指定的顺序执行下面的SQL语句）
CREATE SEQUENCE ord_seq;
SELECT ord_seq.nextval
FROM dual;
INSERT INTO ord
VALUES (ord_seq.CURRVAL, '25-jan-2007',101);
UPDATE ord
SET  ord_no= ord_seq.NEXTVAL
WHERE cust_id =101;
What would be the outcome of the above statements?（上面语句的结果是什么？）

A. All the statements would execute successfully and the ORD_NO column would contain the value 2 for the CUST_ID 101.
（所有语句执行成功，对于CUST_ID为101的ORD_NO列的值为2）
B. The CREATE SEQUENCE command would not execute because the minimum value and maximum value for the sequence have not been specified.
（CREATE SEQUENCE命令不能执行，因为没有指定最大值和最小值）
C. The CREATE SEQUENCE command would not execute because the starting value of the sequence and the increment value have not been specified.
（CREATE SEQUENCE命令不能执行，因为起始值和步长值没有指定）
D. All the statements would execute successfully and the ORD_NO column would have the value 20 for the CUST_ID 101 because the default CACHE value is 20.
（所有语句执行成功，对于CUST_ID为101的ORD_NO列的值为20，因为CACHE默认为20）
答案：A
CREATE SEQUENCE时可以省略所有参数，默认起始值为1，步长为1，无上限
此处提一下CACHE这个关键字：如果建立序列时不指定CACHE和NOCACHE，默认值为20，即一次性从序列里取20个数放入内存，如果内存崩溃，则这20个数就会丢失，再取值时从第21个数开始取值，CACHE设置的最小值为2

ZHAODONG

45. 在单实例数据库中关于创建序列哪两句话是正确的？（选择两个）
A. 一个序列生成的值只能用于一个表
B. DELETE <sequencename>可以从数据库里移除一个序列
C. CURRVAL是指生成的最后的序列值
D. 当达到序列的MAXVALUE限制时，你可以使用ALTER SEQUENCE语句增加MAXVALUE限制
E. 当数据库实例非正常关闭，已经缓存到内存里但是没有被使用的序列数当实例再次打开后可以再次使用

答案：CD
A不正确，因为一个序列生成的值可以用于多个表
B不正确，因为删除序列的语句为DROP SEQUENCE sequencename
E不正确，因为如果内存非正常关闭，缓存的数会丢失，请参照44题说明。


46. Which statements are correct regarding indexes? (Choose all that apply.)
A. When a table is dropped, the corresponding indexes are automatically dropped.
B. A FOREIGN KEY constraint on a column in a table automatically creates a nonunique index.
C. A nondeferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index.
D. For each data manipulation language (DML) operation performed, the corresponding indexes are automatically updated.

46. 关于索引哪句话是正确的？（选择所有合适的）
A. 当表被删除后，对应的索引也自动删除
B. 表中列上的外键约束自动创始一个非唯一索引
C. 表中的非延迟PRIMARY KEY或者UNIQUE KEY约束自动创建一个唯一索引
D. 对于执行的每一个DML操作，对应的索引也自动更新

答案：ACD
FOREIGN KEY和NOT NULL、CHECK约束都不创建索引

ZHAODONG

47. View the Exhibit and examine the structure of ORD and ORD_ITEMS tables.
The ORD_NO column is PRIMARY KEY in the ORD table and the ORD_NO and ITEM_NO columns are composite PRIMARY KEY in the ORD_ITEMS table.
Which two CREATE INDEX statements are valid? (Choose two.)

A. CREATE INDEX ord_idx1
ON ord(ord_no);
B. CREATE INDEX ord_idx2
ON ord_items(ord_no);
C. CREATE INDEX ord_idx3
ON ord_items(item_no);
D. CREATE INDEX ord_idx4
ON ord,ord_items(ord_no, ord_date,qty);
47. 查看ORD and ORD_ITEMS表结构。
ORD 表中ORD_NO列是PRIMARY KEY，ORD_ITEMS表中ORD_NO and ITEM_NO是组合PRIMARY KEY。哪两个CREATE INDEX语句是有效的？（选择两个）
答案：BC（可以在两个列中分别再建立索引）
A不对，因为ORD 表中ORD_NO列是PRIMARY KEY，已经自动创建索引，不能再创建
D语法不正确

ZHAODONG

48. Which two statements are true regarding indexes? (Choose two.)
A. They can be created on tables and clusters.
B. They can be created on tables and simple views.
C. You can create only one index by using the same columns.
D. You can create more than one index by using the same columns if you specify distinctly different combinations of the columns.

48. 关于索引哪两个句子是正确的？（选择两个）
A. 可以在tables and clusters上建立索引
B. 可以在表和简单视图上创建索引
C. 同一个列只能创建一个索引
D. 如果你指定了不同列组合，可以使用相同的列创建多个索引

答案：AD
B不正确因为简单视图上不能创建索引
C不正确因为可能参考Database Concepts的INDEX部分如下：
You can create multiple indexes using the same columns if you specify distinctly different permutations of the columns. For example, the following SQL statements specify valid permutations:（相同列上可以创建多个索引，如果你指定了不同列组合，下面的例子就是有效的列排列）
CREATE INDEX employee_idx1 ON employees (last_name, job_id);
CREATE INDEX employee_idx2 ON employees (job_id, last_name);


49. The ORDERS table belongs to the user OE. OE has granted the SELECT privilege on the ORDERS table to the user HR.
Which statement would create a synonym ORD so that HR can execute the following query successfully?
SELECT * FROM ord;
A. CREATE SYNONYM ord FOR orders; This command is issued by OE.
B. CREATE PUBLIC SYNONYM ord FOR orders; This command is issued by OE.
C. CREATE SYNONYM ord FOR oe.orders; This command is issued by the database administrator.
D. CREATE PUBLIC SYNONYM ord FOR oe.orders; This command is issued by the database administrator.

49. ORDERS表属于OE用户.OE把ORDERS表的SELECT权限授予HR用户。
哪个语句建立一个ORD同义词，以便HR能成功执行下面这个查询？

答案：D
A和C都建立一个私有同义词，其它用户不能访问，B不正确因为题中没有提到OE用户有CREATE PUBLIC SYNONYM的权限，如果有是正确的，如果没有则提示权限不足不能建立。

ZHAODONG

50. SLS is a private synonym for the SH.SALES table.
The user SH issues the following command:
DROP SYNONYM sls;
Which statement is true regarding the above SQL statement?
A. Only the synonym would be dropped.
B. The synonym would be dropped and the corresponding table would become invalid.
C. The synonym would be dropped and the packages referring to the synonym would be dropped.
D. The synonym would be dropped and any PUBLIC synonym with the same name becomes invalid.

50. SLS是SH.SALES表的私有同义词。
SH用户执行下面的命令：
关于上面的语句哪句话是正确的？
A. 只删除同义词
B. 同义词被删除，并且对应的表也变的无效
C. 同义词被删除，并且关联同义词的包也被删除
D. 同义词被删除，并且同名的公共同义词也变的无效
答案：A

ZHAODONG

51. Which statement is true regarding synonyms?
A. Synonyms can be created only for a table.
B. Synonyms are used to reference only those tables that are owned by another user.
C. A public synonym and a private synonym can exist with the same name for the same table.
D. The DROP SYNONYM statement removes the synonym, and the table on which the synonym has been created becomes invalid.

51. 关于同义词哪句话是正确的？
A. 只能为表建立同义词
B. 同义词只能用来参照那些属于另一个用户的表
C. 对同一个表可以存在同名的public synonym和private synonym
D. DROP SYNONYM语句移除同义词，并且创建同义词的表也变的无效

答案：C
A不正确因为同义词是一个schema object的别名，例如可以为table、view、sequence 或者another synonym等等建立同义词

ZHAODONG

52. View the Exhibit and examine the structure of the PRODUCTS table.
Using the PRODUCTS table, you issue the following query to generate the names, current list price,and discounted list price for all those products whose list price falls below $10 after a discount of 25% is applied on it.
SQL>SELECT prod_name, prod_list_price,
prod_list_price - (prod_list_price * .25) "DISCOUNTED_PRICE"
FROM products
WHERE discounted_price < 10;
The query generates an error.What is the reason for the error?

A. The parenthesis should be added to enclose the entire expression.
B. The double quotation marks should be removed from the column alias.
C. The column alias should be replaced with the expression in the WHERE clause.
D. The column alias should be put in uppercase and enclosed with in double quotation marks in the WHERE clause.

52.查看PRODUCTS表结构。
使用PRODUCTS表，执行下面的查询来获取当产品打折25%之后价格低于$10的产品的名称，当前价格，打折后的价格。
查询会报错，错误的原因是什么？
A. 括号应该把整个表达式都括起来
B. 双引号应该从列别名中移除
C. WHERE子句中的列别名应该使用表达式替换
D. WHERE子句中的列别名应该大写并且使用双引号引起来

答案：C
WHERE子句中不能使用列别名