1.Which two tasks can be performed by using Oracle SQL statements? 

A. changing the password for an existing database 

B. connecting to a database instance 

C. querying data from tables across databases 

D. starting up a database instance 

E. executing operating system (OS) commands in a session Answer:C,E 

正确答案：AC

2.Evaluate the following two queries: 

need-to-insert-img

Which statement is true regarding the above two queries? 

A. Performance would improve query 2 only if there are null values in the CUST__CREDIT__LIMIT column. 

B. There would be no change in performance. 

C. Performance would degrade in query 2. 

D. Performance would improve in query 2. Answer:B 

3.Which statement is true regarding external tables? 

A. The default REJECT LIMIT for external tables is UNLIMITED. 

B. The data and metadata for an external table are stored outside the database. 

C. ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table. 

D. The CREATE TABLE AS SELECT statement can be used to unload data into regular table in the database from an external table. 

Answer:D 

本题解释：A选项错误的原因是，该子句的默认值是0 而不是无限；

               D的翻译是：可用于常规的表在数据库中的数据卸载到外部表

               B错误是因为源数据是应存在于数据库中；

               C错误是因为他们的功能不一样，一个是load时用，一个是unload时用；

need-to-insert-img

4.Which two statements are true about sequences created in a single instance database? (Choose two.) 

A. CURRVAL is used to refer to the last sequence number that has been generated 

B. DELETE <sequencename> would remove a sequence from the database  （应该是drop）

C. The numbers generated by a sequence can be used only for one table （都可以使用）

D. When the MAXVALUE limit for a sequence is reached, you can increase the MAXVALUE limit by using the ALTER SEQUENCE statement 

E. When a database instance shuts down abnormally, the sequence numbers that have been cached but not used would be available once again when the database instance is restarted（当数据库非正常关闭后，已经缓存的序列会丢失） Answer: A,D （答案正确）

Explanation: Gaps in the Sequence 

Although sequence generators issue sequential numbers without gaps, this action occurs independent of a commit or rollback. Therefore, if you roll back a statement containing a sequence, the number is lost. Another event that can cause gaps in the sequence is a system crash. If the sequence caches values in memory, those values are lost if the system crashes. Because sequences are not tied directly to tables, the same sequence can be used for multiple tables. However, if you do so, each table can contain gaps in the sequential numbers. 

Modifying a Sequence 

If you reach the MAXVALUE limit for your sequence, no additional values from the sequence are allocated and you will receive an error indicating that the sequence exceeds the MAXVALUE. To continue to use the sequence, you can modify it by using the ALTER SEQUENCE statement To remove a sequence, use the DROP statement: 

DROP SEQUENCE dept_deptid_seq;  

5.View the Exhibits and examine the structures of the costs and promotions tables? 

need-to-insert-img

Evaluate the following SQL statement:  

SQL> SELECT prod_id FROM costs  

WHERE promo_id IN (SELECT promo_id FROM promotions  

WHERE promo_cost < ALL  

(SELECT MAX(promo_cost) FROM promotions  GROUP BY (promo_end_datepromo_  

begin_date))); 

What would be the outcome of the above SQL statement? A. It displays prod IDs in the promo with the lowest cost. 

B. It displays prod IDs in the promos with the lowest cost in the same time interval. 

C. It displays prod IDs in the promos with the highest cost in the same time interval. 

D. It displays prod IDs in the promos with cost less than the highest cost in the same time interval. Answer:D 

6.Examine the following query: 

need-to-insert-img

What is the output of this query? 

A. It displays 5 percent of the products with the highest amount sold. 

B. It displays the first 5 percent of the rows from the SALES table. 

C. It displays 5 percent of the products with the lowest amount sold. 

D. It results in an error because the ORDER BY clause should be the last clause. Answer:C 

7.Examine the structure of the members table: What is the outcome? 

need-to-insert-img

A. It fails because the alias name specified after the column names is invalid. 

B. It fails because the space specified in single quotation marks after the first two column names is invalid. C. It executes successfully and displays the column details in a single column with only the alias column heading. 

D. It executes successfully and displays the column details in three separate columns and replaces only the last column heading with the alias. Answer:D 

8.Which two statements are true regarding multiple-row subqueries? （暂时没能从网上找到解析）(Choose two.) 

A. They can contain group functions. 

B. They always contain a subquery within a subquery. 

C. They use the < ALL operator to imply less than the maximum. 

D. They can be used to retrieve multiple rows from a single table only. 

E. They should not be used with the NOT IN operator in the main query if NULL is likely to be a part of the result of the subquery. Answer:A,E 

9.Examine the structure of the members table: 

need-to-insert-img

You want to display details of all members who reside in states starting with the letter A followed by exactly one character. 

Which SQL statement must you execute? 

A. SELECT * FROM MEMBERS WHERE state LIKE '%A_* ; 

B. SELECT * FROM MEMBERS WHERE state LIKE 'A_*; 

C. SELECT * FROM MEMBERS WHERE state LIKE 'A_%'; 

D. SELECT * FROM MEMBERS WHERE state LIKE 'A%'; 

Answer:A （答案是错误的，这题貌似应该选B， 参考链接 http://cdn-media1.teachertube.com/doc604/32117.pdf）

10.Examine the structure of the employees table. 

need-to-insert-img

There is a parent/child relationship between  EMPLOYEE_ID and MANAGER_ID. 

You want to display the last names and manager IDs of employees who work for the same manager as the employee whose EMPLOYEE_ID123. 

Which query provides the correct output? A) 

need-to-insert-img

B) 

need-to-insert-img

C) 

need-to-insert-img

D) 

need-to-insert-img

A. Option A 

B. Option B 

C. Option C 

D. Option D Answer:B 

11.View the Exhibit and examine the structure of the CUSTOMERS and CUST_HISTORY tables. 

need-to-insert-img

The CUSTOMERS table contains the current location of all currently active customers. The 

CUST_HISTORY table stores historical details relating to any changes in the location of all current as well as previous customers who are no longer active with the company. 

You need to find those customers who have never changed their address. 

Which SET operator would you use to get the required output? 

A. INTERSECT 

B. UNION ALL 

C. MINUS D. UNION 

Answer:C 

12.Examine the structure of the invoice table. 

need-to-insert-img

Which two SQL statements would execute successfully? A) 

need-to-insert-img

B) 

need-to-insert-img

C) 

need-to-insert-img

D) 

need-to-insert-img

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer:CD 

第十二提答案不确定哦

need-to-insert-img

13.Which statement is true regarding the INTERSECT operator? 

A. It ignores NULL values 

B. The number of columns and data types must be identical for all SELECT statements in the query 

C. The names of columns in all SELECT statements must be identical 

D. Reversing the order of the intersected tables the result Answer:B 

Explanation: 

INTERSECT Returns only the rows that occur in both queries’ result sets, sorting them and removing duplicates. The columns in the queries that make up a compound query can have different names, but the output result set will use the names of the columns in the first query. 

14.Which two statements are true regarding the COUNT function? (Choose two.) 

A. COUNT(*) returns the number of rows including duplicate rows and rows containing NULL value in any of the columns 

B. COUNT(cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID column 

C. COUNT(DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and 

NULL values in the INV_AMT column 

D. A SELECT statement using COUNT function with a DISTINCT keyword cannot have a WHERE clause 

E. The COUNT function can be used only for CHAR, VARCHAR2 and NUMBER data types 

Answer:A,C Explanation: 

Using the COUNT Function  

The COUNT function has three formats:  

COUNT(*)  

COUNT(expr)  

COUNT(DISTINCT expr)  

COUNT(*) returns the number of rows in a table that satisfy the criteria of the SELECT statement, including duplicate rows and rows containing null values in any of the columns. If a WHERE clause is included in the SELECT statement, COUNT(*) returns the number of rows that satisfy the condition in the WHERE clause. In contrast, COUNT(expr) returns the number of non-null values that are in the column identified by expr.  

COUNT(DISTINCT expr) returns the number of unique, non-null values that are in the column identified by expr. 

15.Which statements are true? (Choose all that apply.) （这题不会）

A. The data dictionary is created and maintained by the database administrator. 

B. The data dictionary views can consist of joins of dictionary base tables and user-defined tables. 

C. The usernames of all the users including the database administrators are stored in the data dictionary. D. The USER_CONS_COLUMNS view should be queried to find the names of the columns to which a constraint applies. 

E. Both USER_OBJECTS and CAT views provide the same information about all the objects that are owned by the user. 

F. Views with the same name but different prefixes, such as DBA, ALL and USER, use the same base tables from the data dictionary Answer:C,D,F 

16.Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS: 

need-to-insert-img

You want to generate a list of all department IDs along with any course IDs that may have been assigned to them. Which SQL statement must you use? A) 

need-to-insert-img

B) 

need-to-insert-img

C) 

need-to-insert-img

D) 

need-to-insert-img

A. Option A 

B. Option B 

C. Option C 

D. Option D Answer:C 

解析：right join肯定不对。

正确答案应该是

SELECT d.department_id, c.course_id FROM department_details d LEFT OUTER JOIN course_details c ON(d.department_id=c. department_id);

17.Which three statements are true regarding the data types? 

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 width. 

（E的意思是系统会自动用空格填充，直到满足固定长度要求）

Answer: A,D,E （这题答案正确，百度过）

18.Which two statements are true regarding the GROUP BY clause in a SQL statement? (Choose two.) 

（这题答案要背熟啊，不是很理解）

A. You can use column alias in the GROUP BY clause. （试过了，不可以， select 1 aa from dual 

Group by aa 报错，但是group by 1 可以；）

B. Using the WHERE clause after the GROUP BY clause excludes the rows after creating groups. 

C. The GROUP BY clause is mandatory if you are using an aggregate function in the SELECT clause. 

D. Using the WHERE clause before the GROUP BY clause excludes the rows before creating groups. 

E. If the SELECT clause has an aggregate function, then those individual columns without an aggregate function in the SELECT clause should be included in the GROUP BY clause.

还有，where语句只能在group by前面使用，在group by之后再filter就只能用having了

所以B答案错误。 

Answer:D,E 

19.Examine the structure of the BOOKS_TRANSACTIONS table: You want to display the member IDs, due date, and late fee as $2 for all transactions. 

need-to-insert-img

Which SQL statement must you execute? A) 

need-to-insert-img

B) 

need-to-insert-img

C) 

Select member_id as “member_id” ,due_date as “due_date” ,’$2’ as “late fee” from books_transactions

这一题目考的是单引号和双引号的区别，需要记住。答案是C

need-to-insert-img

D) 

need-to-insert-img

A. Option A 

B. Option B 

C. Option C 

D. Option D Answer:C 

20.See the Exhibit and Examine the structure of the CUSTOMERS table: 

need-to-insert-img

Using the CUSTOMERS table, you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed. 

Which SQL statement would produce the required result? 

A. SELECT NVL(cust_credit_limit,'Not Available')*.15 "NEW CREDIT" FROM customers; 

B. SELECT NVL(cust_credit_limit*.15,'Not Available') "NEW CREDIT" FROM customers; 

C. SELECT TO_CHAR(NVL(cust_credit_limit*.15,'Not Available')) "NEW CREDIT" FROM customers; 

D. SELECT NVL(TO_CHAR(cust_credit_limit*.15),'Not Available') "NEW CREDIT" FROM customers; Answer:D 

Explanation: 

NVL Function Converts a null value to an actual value: Data types that can be used are date, character, and number. Data types must match: 

-NVL(commission_pct,0) 

-NVL(hire_date,'01-JAN-97') 

-NVL(job_id,'No Job Yet') 

21.Evaluate the following ALTER TABLE statement: 

ALTER TABLE orders SET UNUSED order_date; Which statement is true? 

A. The DESCRIBE command would still display the ORDER_DATE column. 

B. ROLLBACK can be used to get back the ORDER_DATE column in the ORDERS table. 

C. The ORDER_DATE column should be empty for the ALTER TABLE command to execute successfully. D. After executing the ALTER TABLE command, you can add a new column called ORDER_DATE to the ORDERS table. 

Answer: D （这一题答案肯定是D）

22.View the Exhibit and examine the descriptions of the DEPT and LOCATIOMS tables. 

need-to-insert-img

You want to update the CITY column of the DEPT table for all the rows with the corresponding value in the CITY column of the LOCATIONS table for each department. 

Which SQL statement would you execute to accomplish the task? 

A. UPDATE dept d  

SET city = ANY (SELECT city FROM locations l); 

B. UPDATE dept d  

SET city = (SELECT city FROM locations l)  

WHERE d.location_id = l.location_id; 

C. UPDATE dept d  

SET city = (SELECT city FROM locations l 

WHERE d.location_id = l.location_id); 

D. UPDATE dept d  

SET city = ALL (SELECT city FROM locations l  

WHERE d.location_id = l.location_id); 

（解析，注意这一题答案B和C 括号的区别）

Answer:C 

23.Which three tasks can be performed using SQL functions built into Oracle Database? (Choose three.) 

A. Combining more than two columns or expressions into a single column in the output 

B. Displaying a date in a nondefault format 

C. Substituting a character string in a text expression with a specified string 

D. Finding the number of characters in an expression 

Answer: B,C,D (这个答案是对的，A我们可以使用||来把很多个column并在一起显示，问题是||不是函数function，注意审题）

题意问：内置到Oracle数据库使用SQL函数可以执行哪些任务？ （选择三项）。

A.显示一个非默认格式的日期，例如：to_char()转换日期输出 

B.在一个表达式中查找字符的数量，使用REGEXP_COUNT 函数。REGEXP_COUNT 返回在源串中出现的模式的次数。 

C.使用指定的字符串来替换文本表达式中的字符串，replace函数。 

D.联合超过两个列或表达式输出为一个列，contact只能连接两列。||可以连接多个，但它不是SQL函数。

24.View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables. 

ORDER__ID is the primary key in the ORDERS table. It is also the foreign key in the ORDER_ITEMS table wherein it is created with the ON DELETE CASCADE option. 

need-to-insert-img

Which DELETE statement would execute successfully? 

A. DELETE order_id  

FROM orders  

WHERE order_total < 1000; 

B. DELETE orders  

WHERE order_total < 1000; 

C. DELETE  

FROM orders  

WHERE (SELECT order_id FROM order_items); 

D. DELETE orders o, order_items i  

WHERE o.order id = i.order id; 

Answer:B 

解析：oracle delete语句一次只能delete一张表，不可以两张表，所以D错误。

delete的语法是delete from tablename，或者直接delete tablename，这个from是可选的，所以A错误

这题有点意思。

25.When does a transaction complete? (Choose all that apply.) 

A. When a PL/SQL anonymous block is executed 

B. When a DELETE statement is executed 

C. When a data definition language statement is executed 

D. When a TRUNCATE statement is executed after the pending transaction 

E. When a ROLLBACK command is executed 

Answer:C,D,E 

思路：一个事务是否完成，就看是否commit或者rollback。

delete是dml语句，不提交事务。

ddl语句执行完后自动提交事务。

truncate也属于ddl语句

26.View the Exhibit and examine the structure of the EMPLOYEES table. 

You want to display all employees and their managers having 100 as the MANAGER_ID. You want the output in two columns: the first column would have the LAST_NAME of the managers and the second column would have LAST_NAME of the employees. 

Which SQL statement would you execute? 

need-to-insert-img

A. SELECT m.last_name "Manager", e.last_name "Employee"  

FROM employees m JOIN employees e  

ON m.employee_id = e.manager_id  

WHERE m.manager_id=100; 

B. SELECT m.last_name "Manager", e.last_name "Employee"  

FROM employees m JOIN employees e  

ON m.employee_id = e.manager_id  

WHERE e.manager_id=100; 

C. SELECT m.last_name "Manager", e.last_name "Employee"  

FROM employees m JOIN employees e  

ON e.employee_id = m.manager_id  

WHERE m.manager_id=100; 

D. SELECT m.last_name "Manager", e.last_name "Employee"  

FROM employees m JOIN employees e  

WHERE m.employee_id = e.manager_id AND e.manager_id=100; 

Answer: B 这答案是对的。

这题有点绕。

解析技巧：and their managers having 100 决定了是从雇员表里找他们的经理

27.Which three statements are true regarding group functions? (Choose three.) 

A. They can be used on columns or expressions. 

B. They can be passed as an argument to another group function. 

C. They can be used only with a SQL statement that has the GROUP BY clause. 

D. They can be used on only one column in the SELECT clause of a SQL statement.、 

E. They can be used along with the single-row function in the SELECT clause of a SQL statement. Answer: A,B,E  

这题答案应该是对的。可以参考网页https://www.cnblogs.com/longjshz/p/4303320.html

28.You execute the following commands: 

need-to-insert-img

For which substitution variables are you prompted for the input? 

A. None, because no input required 

B. Both the substitution variables 'hiredate' and 'mgr_id\ 

C. Only 'hiredate' 

D. Only 'mgr_id' Answer: B

这题答案选b没错 我自己在机器上实验了

29.Which statements are true regarding the WHERE and HAVING clauses in a SELECT statement? 

(Choose all that apply.) 

A. The HAVING clause can be used with aggregate functions in subqueries. 

B. The WHERE clause can be used to exclude rows after dividing them into groups. 错误

C. The WHERE clause can be used to exclude rows before dividing them into groups. 

D. The aggregate functions and columns used in the HAVING clause must be specified in the SELECT list of the query. 

E. The WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the table. 错误

Answer: A,C  这题答案应该没有问题。这题我自己做我也选AC

30.You issue 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 in the table are deleted but the table structure will remain 错

B. All data along with the table structure is deleted 对

C. All views and synonyms will remain but they are invalidated 

D. The pending transaction in the session is committed 

E. All indexes on the table will remain but they are invalidated 

Answer:B,C,D 

这题答案没问题，可以参考网页https://blog.csdn.net/dwj19830118/article/details/50587966

drop命令会把table相关的index都一起删除，但是view和synonyms（同义词）会保留，不过都无效了。

31.Which three statements are true reading subquenes?

A Main query can have many subqueries. 

B. A subquery can have more than one main query 

C. The subquery and main query must retrieve date from the same table. 

D. The subquery and main query can retrieve data from different tables. 

E. Only one column or expression can be compared between the subquery and main query. 

F. Multiple columns or expressions can be compared between the subquery and main query. Answer:A,D,F 

解析：这题答案应该没问题。

32.You are designing the structure of a table in which two columns have the specifications: 

COMPONENT_ID - must be able to contain a maximum of 12 alphanumeric characters and uniquely identify the row  解析 number(12,0)

EXECUTION_DATETIME - contains Century, Year, Month, Day, Hour, Minute, Second to the maximum precision and is used for calculations and comparisons between components. 

Which two options define the data types that satisfy these requirements most efficiently? A. The EXECUTION_DATETIME must be of INTERVAL DAY TO SECOND data type. 

B. The EXECUTION _DATETIME must be of TIMESTAMP data type. 

C. The EXECUTION_DATATIME must be of DATE data type. 

D. The COMPONENT_ID must be of ROWID data type. 

E. The COMPONENT_ID must be of VARCHAR2 data type. 

F. The COMPONENT_ID column must be of CHAR data type. Answer:C,E 

33.View the Exhibit and examine the ORDERS table. 

The ORDERS table contains data and all orders have been assigned a customer ID. Which statement would add a NOT NULL constraint to the CUSTOMER_ID column? 

need-to-insert-img

A. ALTER TABLE orders  

ADD CONSTRAINT orders_cust_id_nn NOT NULL (customer_id); 

B. ALTER TABLE orders  

MODIFY customer_id CONSTRAINT orders_cust_id_nn NOT NULL; 

C. ALTER TABLE orders  

MODIFY CONSTRAINT orders_cust_id_nn NOT NULL (customer_id); 

D. ALTER TABLE orders  

ADD customer_id NUMBER(6)CONSTRAINT orders_cust_id_nn NOT NULL;

 Answer:B 

34.View the Exhibit and examine the structure of the stores table. 

need-to-insert-img

You want to display the name of the store along with the address, START_DATE, PROPERTV_PRICE, and the projected property price, which is 115% of the property price. The stores displayed must have START_DATE in the range of 36 months starting from 01Jan-2000 and above. 

Which SQL statement would get the desired output? 

A) 

need-to-insert-img

B) 

need-to-insert-img

C) 

need-to-insert-img

D) 

need-to-insert-img

A. Option A 

B. Option B 

C. Option C 

D. Option D Answer: C

解析：网上说答案是B，明天去公司测试一下concat这个函数，看可不可以用这个函数合并三个column。 

35.Which statement correctly grants a system privilege? 

A. GRANT EXECUTE  

ON prod  

TO PUBLIC; 

B. GRANT CREATE VIEW  ON tablel TO  used; 

C. GRANT CREATE TABLE  

TO user1 ,user2; 

D. GRANT CREATE SESSION  

TO ALL; 

Answer:C 

这题答案没问题

A. GRANT EXECUTE ON proc1 TO PUBLIC授予所有用户执行过程proc1的权限,这是对象权限不是系统权限B. GRANT CREATE VIEW ON table1 TO user1create view是系统权限,没有在某个表上的创建视图的权限,得到create view权限和select on table对象权限就可以创建到其他用户的表的视图C. GRANT CREATE TABLE TO user1,user2(right)GRANT 权限名 TO 用户(角色)1,用户(角色)2D. GRANT CREATE SESSION TO ALL要想所有用户授权是to public不是to all