Q: 31  In which scenario would an index be most useful?
A.   The indexed column is declared as NOT NULL.
B.   The indexed columns are used in the FROM clause.
C.   The indexed columns are part of an expression.
D.   The indexed column contains a wide range of values.
答案：D
解析：来自oracle的声音：
It is usually not worth creating an index if:
1) The table is small
2) The columns are not often used as a condition in the query
3) Most queries are expected to retrieve more than 2 to 4% of the rows in the table
4) The table is updated frequently
5) The indexed columns are referenced as part of an expression
You should create an index if:
1) A column contains a wide range of values
2) A column contains a large number of null values
3) One or more columns are frequently used together in a WHERE clause or a join condition
4) The table is large and most queries are expected to retrieve less than 2 to 4% of the rows

Q: 32  Which SELECT statement should you use to extract the year from the
system date and display it in the format "1998"?
A.   SELECT TO_CHAR(SYSDATE,'yyyy') FROM dual;
B.   SELECT TO_DATE(SYSDATE,'yyyy') FROM dual;
C.   SELECT DECODE(SUBSTR(SYSDATE, 8), 'YYYY') FROM dual;
D.   SELECT DECODE(SUBSTR(SYSDATE, 8), 'year') FROM dual;
E.   SELECT TO_CHAR(SUBSTR(SYSDATE, 8,2),'yyyy') FROM dual;
答案：B

Q: 33  Examine the description of the EMPLOYEES table:
EMP_ID              NUMBER(4)              NOT NULL
LAST_NAME      VARCHAR2(30)        NOT NULL
FIRST_NAME     VARCHAR2(30)
DEPT_ID            NUMBER(2)
Which statement produces the number of different departments that have employees with last name
Smith?
A.   SELECT COUNT(*) FROM   employees WHERE  last_name='Smith';
B.   SELECT COUNT(dept_id) FROM   employees WHERE  last_name='Smith';
C.   SELECT DISTINCT(COUNT(dept_id)) FROM   employees WHERE  last_name='Smith';
D.   SELECT COUNT(DISTINCT dept_id) FROM   employees WHERE  last_name='Smith';
E.   SELECT UNIQUE(dept_id) FROM   employees WHERE  last_name='Smith';
答案：D

Q: 34  Examine the description of the MARKS table:
STD_ID                 NUMBER(4)
STUDENT_NAME  VARCHAR2(30)
SUBJ1                   NUMBER(3)
SUBJ2                   NUMBER(3)
SUBJ3                   NUMBER(3)
SUBJ1, SUBJ2, and SUBJ3 indicate the marks (grades) obtained by a student in the three subjects.
Which two statements are valid? (Choose two.)
A.   SELECT SUM(subj1, subj2, subj3) FROM   marks;
B.   SELECT SUM(subj1 + subj2 + subj3) FROM   marks;
C.   SELECT SUM(subj1), SUM(subj2), SUM(subj3) FROM   marks;
D.   SELECT MAX(subj1, subj2, subj3) FROM   marks;
E.   SELECT MINIMUM(subj1) FROM   marks;
F.   SELECT COUNT(std_id) FROM   marks WHERE  subj1 >= AVG(subj1);
答案：B,C


Q: 35  Which are iSQL*Plus commands? (Choose all that apply.)
A.   INSERT
B.   UPDATE
C.   SELECT
D.   DESCRIBE
E.   DELETE
F.   RENAME
答案：D
解析：
describe不能在pl/sql里面用,是sqlplus特有的命令，而不是标准的sql命令.

Q: 36  Which three SELECT statements display 2000 in the format "$2,000.00"?
(Choose three.)
A.   SELECT TO_CHAR(2000, '$#,###.##') FROM dual;
B.   SELECT TO_CHAR(2000, '$0,000.00') FROM dual;
C.   SELECT TO_CHAR(2000, '$9,999.00') FROM dual;
D.   SELECT TO_CHAR(2000, '$9,999.99') FROM dual;
E.   SELECT TO_CHAR(2000, '$2,000.00') FROM dual;
F.   SELECT TO_CHAR(2000, '$N,NNN.NN') FROM dual;
答案：B,C,D
解析：
to_char格式化数值时，可用0和9，中间可以有一个小数点号和多个千分符（即逗号）
0和9的最大区别是，0位如果没有，则补0，例：
select to_char(2000.9,'$0909990.00') from dual; 结果： $0002000.90

Q: 37  What does the FORCE option for creating a view do?
A.   creates a view with constraints
B.   creates a view even if the underlying parent table has constraints
C.   creates a view in another schema even if you don't have privileges
D.   creates a view regardless of whether or not the base tables exist
答案：D
加了force，可以对不存在的表建视图，对dba批量操作有帮助，可以不考虑表和视图的先后顺序，默认为noforece。
语法格式：
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];

Q: 38  Click the Exhibit button to examine the structure of the EMPLOYEES,
DEPARTMENTS, and LOCATIONS tables.

image
Two new departments are added to your company as shown:
DEPARTMENT_ID    DEPARTMENT_NAME    MGR_ID    LOCATION_ID
9998                         Engineering                    123
9999                         Administrative                                   Boston
You need to list the names of employees, the department IDs, the department names, and the cities where
the departments are, even if there are no employees in the departments and even if the departments are
not yet assigned to a location. You need to join the EMPLOYEES, DEPARTMENTS, and LOCATIONS
tables to retrieve this information.
Which statement do you execute to retrieve this information?
A.   SELECT e.last_name, d.department_id, d.department_name, l.city
FROM departments d RIGHT OUTER JOIN employees e
ON d.department_id = e.department_id
RIGHT OUTER JOIN locations l
ON d.location_id = l.location_id;
B.   SELECT e.last_name, d.department_id, d.department_name, l.city
FROM departments d
FULL OUTER JOIN employees e
ON d.department_id = e.department_id
FULL OUTER JOIN locations l
ON d.location_id = l.location_id;
C.   SELECT e.last_name, d.department_id, d.department_name, l.city
FROM departments d
LEFT OUTER JOIN employees e
ON d.department_id = e.department_id
LEFT OUTER JOIN locations l
ON d.location_id = l.location_id;
D.   SELECT last_name, department_id, department_name, city
FROM departments d
NATURAL JOIN employees e
NATURAL JOIN locations l;
答案：C
解析：以department为主表.

Q: 39  What is true about joining tables through an equijoin?
A.   You can join a maximum of two tables through an equijoin.
B.   You can join a maximum of two columns through an equijoin.
C.   You specify an equijoin condition in the SELECT or FROM clauses of a SELECT statement.
D.   To join two tables through an equijoin, the columns in the join condition must be primary key and foreign
key columns.
E.   You can join n tables (all having single column primary keys) in a SQL statement by specifying a minimum
of n-1 join conditions.
答案：E
解析:
A,B:equijoin 允许多表，多列连接。
C:等值条件放在where中，而不是select和from中
D:等值连接的字段不一定是主键或外键，但是经常是主键或外键。

Q: 40  Which two statements are true about constraints? (Choose two.)
A.   The UNIQUE constraint does not permit a null value for the column.
B.   A  UNIQUE index gets created for columns with PRIMARY KEY and UNIQUE constraints.
C.   The PRIMARY KEY and FOREIGN KEY constraints create a UNIQUE index.
D.   The NOT NULL constraint ensures that null values are not permitted for the column.
答案：B,D
解析：
A：UNIQUE是唯一性表示，但是可以为null
C：foreign_key 字段的值必须在所涉及外表的值范围中或者为null。

Q: 41  Which two statements complete a transaction? (Choose two.)
A.   DELETE employees;
B.   DESCRIBE employees;
C.   ROLLBACK TO SAVEPOINT C;
D.   GRANT SELECT ON employees TO SCOTT;
E.   ALTER TABLE employees SET UNUSED COLUMN sal;
F.   SELECT MAX(sal) FROM   employees WHERE  department_id = 20;
答案：D,E
解析：
A,B,F：都不会产生事务
什么时候会结束事务：
End with one of the following events:
1）A COMMIT or ROLLBACK statement is issued
2）A DDL or DCL statement executes (automatic commit)
3）The user exits iSQL*Plus
4）The system crashes
rollback后面跟具体得还原点就不会结束事务。

Q: 42  Which SQL statement returns a numeric value?
A.   SELECT ADD_MONTHS(MAX(hire_Date), 6) FROM EMP;
B.   SELECT ROUND(hire_date) FROM EMP;
C.   SELECT sysdate-hire_date FROM EMP;
D.   SELECT TO_NUMBER(hire_date + 7) FROM EMP;
答案：C
解析：
A：date类型，B：date类型 D:语法有错，不能吧date类型直接转为number类型。

Q: 43  Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:
EMPLOYEES
EMPLOYEE_ID      NUMBER       Primary Key
FIRST_NAME        VARCHAR2(25)
LAST_NAME         VARCHAR2(25)
HIRE_DATE          DATE
NEW_EMPLOYEES
EMPLOYEE_ID     NUMBER             Primary Key
NAME                   VARCHAR2(60)
Which DELETE statement is valid?
A.   DELETE FROM employees
WHERE employee_id =  (SELECT employee_id
FROM employees);
B.   DELETE *  FROM employees
WHERE employee_id =  (SELECT employee_id
FROM new_employees);
C.   DELETE FROM employees
WHERE employee_id IN (SELECT employee_id
FROM new_employees
WHERE name ='Carrey');
D.   DELETE  * FROM employees
WHERE employee_id IN (SELECT employee_id
FROM new_employees
WHERE last_name ='Carrey');
答案：C
解析：等号后面得子函数只能返回一个结果。

Q: 44  You need to create a table named ORDERS that contains four columns:
1.  an ORDER_ID column of number data type
2.  a CUSTOMER_ID column of number data type
3.  an ORDER_STATUS column that contains a character data type
4.  a DATE_ORDERED column to contain the date the order was placed
When a row is inserted into the table, if no value is provided for the status of the order, the value
PENDING should be used instead.
Which statement accomplishes this?
A.   CREATE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(8),
order_status NUMBER(10) DEFAULT 'PENDING',
date_ordered DATE );
B.   CREATE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(8),
order_status VARCHAR2(10) = 'PENDING',
date_ordered DATE );
C.   CREATE OR REPLACE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(8),
order_status VARCHAR2(10) DEFAULT 'PENDING',
date_ordered DATE );
D.   CREATE OR REPLACE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(8),
order_status VARCHAR2(10) = 'PENDING',
date_ordered DATE );
E.   CREATE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(8),
order_status VARCHAR2(10) DEFAULT 'PENDING
date_ordered DATE );
F.   CREATE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(8),
order_status VARCHAR2(10) DEFAULT 'PENDING',
date_ordered VARCHAR2 );
答案：E

Q: 45  In which two cases would you use an outer join? (Choose two.)
A.   The tables being joined have NOT NULL columns.
B.   The tables being joined have only matched data.
C.   The columns being joined have NULL values.
D.   The tables being joined have only unmatched data.
E.   The tables being joined have both matched and unmatched data.
F.   Only when the tables have a primary key-foreign key relationship.
答案：C，E
解析：
A:not null column不一定要用外连接
B:只有匹配的数据，一般用inner join
D:不好解释，只有匹配不到的数据，好像也可以用外连接，我的理解是没有相同的列能进行关联
F:不是only。

Q: 46  Which two statements are true about WHERE and HAVING clauses? (Choose two.)
A.   A WHERE clause can be used to restrict both rows and groups.
B.   A WHERE clause can be used to restrict rows only.
C.   A HAVING clause can be used to restrict both rows and groups.
D.   A HAVING clause can be used to restrict groups only.
E.   A WHERE clause CANNOT be used in a query if the query uses a HAVING clause.
F.   A HAVING clause CANNOT be used in subqueries.
答案：B,D
解析：
where只能限制行，不能限制分组以后的结果，所以不能在where子句中使用组函数
having 只有在group by 后才能使用，用来限制分组后的记录
where 和 having可以同时存在，作用域不一样而已。

Q: 47  Examine the structure of the EMPLOYEES table:
Column name              Data type               Remarks
EMPLOYEE_ID            NUMBER                NOT NULL, Primary Key
EMP_NAME                 VARCHAR2(30)
JOB_ID                        VARCHAR2(20)      NOT NULL
SAL                              NUMBER
MGR_ID                       NUMBER                 References EMPLOYEE_ID column
DEPARTMENT_ID        NUMBER                 Foreign key to DEPARTMENT_ID column
You need to create a view called EMP_VU that allows the users to insert rows through the view.
Which SQL statement, when used to create the EMP_VU view, allows the users to insert rows?
A.   CREATE VIEW emp_vu AS
SELECT employee_id, emp_name,
department_id
FROM   employees
WHERE  mgr_id IN (102, 120);
B.   CREATE VIEW emp_vu AS
SELECT employee_id, emp_name, job_id,
department_id
FROM   employees
WHERE  mgr_id IN (102, 120);
C.   CREATE VIEW emp_vu AS
SELECT department_id, SUM(sal) TOTALSAL
FROM   employees
WHERE  mgr_id IN (102, 120)
GROUP BY department_id;
D.   CREATE VIEW emp_vu AS
SELECT employee_id, emp_name, job_id,
DISTINCT department_id
FROM   employees;
答案：B
解析：
来自oracle的声音：
You cannot modify data in a view if it contains:
– Group functions
– A GROUP BY clause
– The DISTINCT keyword
– The pseudocolumn ROWNUM keyword
– Columns defined by expressions
You cannot remove a row if the view contains the following:
– Group functions
– A GROUP BY clause
– The DISTINCT keyword
– The pseudocolumn ROWNUM keyword
You cannot add data through a view if the view includes:
– Group functions
– A GROUP BY clause
– The DISTINCT keyword
– The pseudocolumn ROWNUM keyword
– Columns defined by expressions
– NOT NULL columns in the base tables that are not selected by the view
A,C,D的错误都在上面有说明。

Q: 48  Evaluate this SQL statement:
e.employee_id, (.15* e.salary) + (.5 * e.commission_pct)
             + (s.sales_amount * (.35 * e.bonus)) AS CALC_VALUE
FROM employees e, sales s
WHERE e.employee_id = s.emp_id;
What will happen if you remove all the parentheses from the calculation?
A.   The value displayed in the CALC_VALUE column will be lower.
B.   The value displayed in the CALC_VALUE column will be higher.
C.   There will be no difference in the value displayed in the CALC_VALUE column.
D.   An error will be reported.
答案：C
解析：运算符优先级，跟数学的运算优先级一样的。

Q: 49  Which four are attributes of single row functions? (Choose four.)
A.   cannot be nested
B.   manipulate data items
C.   act on each row returned
D.   return one result per row
E.   accept only one argument and return only one value
F.   accept arguments which can be a column or an expression
答案：B,C,D,F
解析：
来自oracle的声音：
Single row functions:
1)Manipulate data items
2)Accept arguments and return one value
3)Act on each row returned
4)Return one result per row
5)May modify the data type
6)Can be nested
7)Accept arguments which can be a column or an expression

Q: 50  Which view should a user query to display the columns associated with the
constraints on a table owned by the user?
A.   USER_CONSTRAINTS
B.   USER_OBJECTS
C.   ALL_CONSTRAINTS
D.   USER_CONS_COLUMNS
E.   USER_COLUMNS
答案：D
解析：
user_constraints是表约束的视图,描述的是约束类型(constraint_type)是什么,属于哪些表(table_name),如果约束的类型为R(外键)的话,那么r_constraint_name字段存放的就是被引用主表中的主键约束名。  
user_cons_columns是表约束字段的视图,说明表中的和约束相关的列参与了哪些约束。这些约束有主键约束,外键约束,索引约束.
两者可以通过(owner,constraint_name,table_name)关联；
USER_OBJECTS 当前用户模式下有哪些数据库对象
ALL_CONSTRAINTS 当前用户能访问表的所有约束，user_constraints是他的子集。