?

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的翻譯是：可用于常規(guī)的表在數(shù)據(jù)庫中的數(shù)據(jù)卸載到外部表

? ? ? ? ? ? ? ?B錯誤是因為源數(shù)據(jù)是應存在于數(shù)據(jù)庫中梦裂；

? ? ? ? ? ? ? ?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（當數(shù)據(jù)庫非正常關閉后，已經緩存的序列會丟失）?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的意思是系統(tǒng)會自動用空格填充味赃，直到滿足固定長度要求）

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 “l(fā)ate fee” from books_transactions

這一題目考的是單引號和雙引號的區(qū)別土童，需要記住。答案是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 括號的區(qū)別）

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并在一起顯示，問題是||不是函數(shù)function王污，注意審題）

題意問：內置到Oracle數(shù)據(jù)庫使用SQL函數(shù)可以執(zhí)行哪些任務罢吃？ （選擇三項）。

A.顯示一個非默認格式的日期昭齐，例如：to_char()轉換日期輸出?

B.在一個表達式中查找字符的數(shù)量尿招，使用REGEXP_COUNT 函數(shù)。REGEXP_COUNT 返回在源串中出現(xiàn)的模式的次數(shù)阱驾。?

C.使用指定的字符串來替換文本表達式中的字符串就谜，replace函數(shù)。?

D.聯(lián)合超過兩個列或表達式輸出為一個列里覆，contact只能連接兩列丧荐。||可以連接多個，但它不是SQL函數(shù)喧枷。

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語句執(zhí)行完后自動提交事務。

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這個函數(shù)退子，看可不可以用這個函數(shù)合并三個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授予所有用戶執(zhí)行過程proc1的權限,這是對象權限不是系統(tǒng)權限B. GRANT CREATE VIEW ON table1 TO user1create view是系統(tǒng)權限,沒有在某個表上的創(chuàng)建視圖的權限,得到create view權限和select on table對象權限就可以創(chuàng)建到其他用戶的表的視圖C. GRANT CREATE TABLE TO user1,user2(right)GRANT?權限名?TO?用戶(角色)1,用戶(角色)2D. GRANT CREATE SESSION TO ALL要想所有用戶授權是to public不是to all