NEW QUESTION 1
View the Exhibit and examine the structure of the ORDERS table. The ORDER_ID column is the PRIMARY KEY in the ORDERS table.

Evaluate the following CREATE TABLE command:
CREATE TABLE new_orders(ord_id, ord_date DEFAULT SYSDATE, cus_id) AS
SELECT order_id.order_date,customer_id FROM orders;
Which statement is true regarding the above command?

• A. The NEW_ODRDERS table would not get created because the DEFAULT value cannot be specified in the column definition.
• B. The NEW_ODRDERS table would get created and only the NOT NULL constraint defined on the specified columns would be passed to the new table.
• C. The NEW_ODRDERS table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
• D. The NEW_ODRDERS table would get created and all the constraints defined on the specified columns in the ORDERS table would be passed to the new table.

Answer: B

NEW QUESTION 2
Examine the structure of the BOOKS_TRANSACTIONS table:

Examine the SQL statement:

Which statement is true about the outcome?

• A. It displays details only for members who have borrowed before today with RM as TRANSACTION_TYPE.
• B. It displays details for members who have borrowed before today’s date with either RM as TRANSACTION_TYPE or MEMBER_ID as A101 and A102.
• C. It displays details for only members A101 and A102 who have borrowed before today with RM TRANSACTION_TYPE.
• D. It displays details for members who have borrowed before today with RM as TRANSACTION_TYPE and the details for members A101 or A102.

Answer: D

NEW QUESTION 3
View the exhibit for the structure of the STUDENT and FACULTY tables. STUDENT
NameNull?Type
-------------------------------------------------- STUDENT_IDNOT NULLNUMBER(2) STUDENT_NAMEVARCHAR2(20) FACULTY_IDVARCHAR2(2) LOCATION_IDNUMBER(2) FACULTY
NameNull?Type
-------------------------------------------------- FACULTY_IDNOT NULLNUMBER(2) FACULTY_NAMEVARCHAR2(20) LOCATION_IDNUMBER(2)
You need to display the faculty name followed by the number of students handled by the faculty at the base location.
Examine the following two SQL statements: Statement 1
SQL>SELECT faculty_name, COUNT(student_id) FROM student JOIN faculty
USING (faculty_id, location_id) GROUP BY faculty_name; Statement 2
SQL>SELECT faculty_name, COUNT(student_id)
FROM student NATURAL JOIN faculty GROUP BY faculty_name;
Which statement is true regarding the outcome?

• A. Only statement 2 executes successfully and gives the required result.
• B. Only statement 1 executes successfully and gives the required result.
• C. Both statements 1 and 2 execute successfully and give different results.
• D. Both statements 1 and 2 execute successfully and give the same required result.

Answer: B

NEW QUESTION 4
Examine these SQL statements that are executed in the given order:
CREATE TABLE emp
(emp_no NUMBER (2) CONSTRAINT emp_emp_no_pk PRIMARY KEY, ename VARCHAR 2 (15),
salary NUMBER (8, 2),
mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp (emp_no)); ALTER TABLE emp
DISABLE CONSTRAINT emp_emp_no_pk CASCADE; ALTER TABLE emp
ENABLE CONSTRAINT emp_emp_no_pk;
What will be the status of the foreign key EMP_MGR_FK?

• A. It will be enabled and immediate.
• B. It will be enabled and deferred.
• C. It will remain disabled and can be re-enabled manually.
• D. It will remain disabled and can be enabled only by dropping the foreign key constraint and re-creating it.

Answer: C

NEW QUESTION 5
You issue the following command to drop the PRODUCTS table: (Choose all that apply.) SQL > DROP TABLE products;
Which three statements are true about the implication of this command?

• A. All data along with the table structure is deleted.
• B. A pending transaction in the session is committed.
• C. All indexes on the table remain but they are invalidated.
• D. All views and synonyms on the table remain but they are invalidated.
• E. All data in the table is deleted but the table structure remains.

Answer: ABD

NEW QUESTION 6
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 || ‘ ’ || cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers.
• B. SELECT DISTINCT cust_income_level || ‘ ’ || cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers.
• C. SELECT DISTINCT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers.
• D. SELECT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers

Answer: B

NEW QUESTION 7
View the exhibit and examine the structure of ORDERS and CUSTOMERS tables. ORDERS
Name Null? Type
ORDER_ID NOT NULL NUMBER(4) ORDER_DATE NOT NULL DATE ORDER_MODE VARCHAR2(8) CUSTOMER_ID NOT NULL NUMBER(6) ORDER_TOTAL NUMBER(8, 2) CUSTOMERS
Name Null? Type
CUSTOMER_ID NOT NULL
NUMBER(6) CUST_FIRST_NAME NOT NULL VARCHAR2(20) CUST_LAST_NAME NOT NULL VARCHAR2(20) CREDIT_LIMIT NUMBER(9,2) CUST_ADDRESS VARCHAR2(40)
Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600? Assume there exists only one row with CUST_LAST_NAME as Roberts and CREDIT_LIMIT as 600.

• A. INSERT INTO (SELECT o.order_id, o.order_date, o.order_mode, c.customer_id, o.order_totalFROM orders o, customers cWHERE o.customer_id = c.customer_id AND c.cust_last_name='Roberts' AND c.credit_limit=600)VALUES (1,'10-mar-2007', 'direct', (SELECT customer_idFROM customersWHERE cust_last_name='Roberts' AND credit_limit=600), 1000);
• B. INSERT INTO orders (order_id, order_date, order_mode,(SELECT customer idFROM customersWHERE cust_last_name='Roberts' AND credit_limit=600), order_total);VALUES (1,'10-mar-2007', 'direct', &customer_id, 1000);
• C. INSERT INTO ordersVALUES (1,'10-mar-2007', 'direct',(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' AND credit_limit=600), 1000);
• D. INSERT INTO orders (order_id, order_date, order_mode,(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' AND credit_limit=600), order_total);VALUES (1,'10-mar-2007', 'direct', &customer_id, 1000);

Answer: C

NEW QUESTION 8
View the Exhibit and examine the data in the PRODUCT_INFORMATION table.

Which two tasks would require subqueries? (Choose two.)

• A. displaying all the products whose minimum list prices are more than average list price of products having the status orderable
• B. displaying the total number of products supplied by supplier 102071 and having product status OBSOLETE
• C. displaying the number of products whose list prices are more than the average list price
• D. displaying all supplier IDs whose average list price is more than 500
• E. displaying the minimum list price for each product status

Answer: AC

NEW QUESTION 9
View the exhibit and examine the structure of the PROMOTIONS table.

You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the ‘INTERNET’ category.
Which query would give you the required output?

• A. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date> ALL (SELECT MAX (promo_begin_date)FROM promotions) ANDpromo_category= ‘INTERNET’;
• B. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date IN (SELECT promo_begin_dateFROM promotionsWHERE promo_category= ‘INTERNET’);
• C. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date > ALL (SELECT promo_begin_dateFROM promotionsWHERE promo_category = ‘INTERNET’);
• D. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date> ANY (SELECT promo_begin_dateFROM promotionsWHERE promo_category= ‘INTERNET’);

Answer: C

NEW QUESTION 10
Which two statements are true regarding single row functions? (Choose two.)

• A. MOD : returns the quotient of a division.
• B. TRUNC : can be used with NUMBER and DATE values.
• C. CONCAT : can be used to combine any number of values.
• D. SYSDATE : returns the database server current date and time.
• E. INSTR : can be used to find only the first occurrence of a character in a string.
• F. TRIM : can be used to remove all the occurrences of a character from a string.

Answer: BD

NEW QUESTION 11
Which two statements are true regarding subqueries? (Choose two.)

• A. A subquery can appear on either side of a comparison operator.
• B. Only two subqueries can be placed at one level.
• C. A subquery can retrieve zero or more rows.
• D. A subquery can be used only in SQL query statements.
• E. There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement.

Answer: AC

NEW QUESTION 12
View the exhibit and examine the structure and data in the invoice table. (Choose two.)

Which two SQL statements would execute successfully?

• A. SELECT MAX(AVG(SYSDATE -inv_date)) FROM invoice
• B. SELECT AVG(inv_date) FROM invoice
• C. SELECT MAX(inv_date), MIN(cust_id) FROM invoice
• D. SELECT AVG( inv_date -SYSDATE), AVG(inv_amt) FROM invoice

Answer: CD

NEW QUESTION 13
Which statement is true about SQL query processing in an Oracle database instance? (Choose the best answer.)

• A. During parsing, a SQL statement containing literals in the WHERE clause that has been executed by any session and which is cached in memory, is always reused for the current execution.
• B. During executing, the oracle server may read data from storage if the required data is not already in memory.
• C. During row source generation, rows that satisfy the query are retrieved from the database and stored in memory.
• D. During optimization, execution plans are formulated based on the statistics gathered by the database instance, and the lowest cost plan is selected for execution.

Answer: B

NEW QUESTION 14
The following are the steps for a correlated subquery, listed in random order:
The WHERE clause of the outer query is evaluated.
The candidate row is fetched from the table specified in the outer query.
This is repeated for the subsequent rows of the table, till all the rows are processed.
Rows are returned by the inner query, after being evaluated with the value from the candidate row in the outer query.
Which is the correct sequence in which the Oracle server evaluates a correlated subquery?

• A. 2, 1, 4, 3
• B. 4, 1, 2, 3
• C. 4, 2, 1, 3
• D. 2, 4, 1, 3

Answer: D

Explanation: References:
http://rajanimohanty.blogspot.co.uk/2014/01/correlated-subquery.html

NEW QUESTION 15
Which statement is true regarding the USING clause in table joins? (Choose two.)

• A. It can be used to join a maximum of three tables.
• B. It can be used to access data from tables through equijoins as well as nonequijoins.
• C. It can be used to join tables that have columns with the same name and compatible data types.
• D. It can be used to restrict the number of columns used in a NATURAL join.

Answer: CD

NEW QUESTION 16
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 consists 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: CDF

Explanation: References:
https://docs.oracle.com/cd/B10501_01/server.920/a96524/c05dicti.htm

NEW QUESTION 17
View the Exhibit and examine the details of the PRODUCT_INFORMATION table. (Choose two.)

Evaluate this SQL statement:
SELECT TO_CHAR (list_price, '$9,999') From product_information;
Which two statements are true regarding the output?

• A. A row whose LIST_PRICE column contains value 11235.90 would be displayed as #######.
• B. A row whose LIST_PRICE column contains value 1123.90 would be displayed as $1,123.
• C. A row whose LIST_PRICE column contains value 1123.90 would be displayed as $1,124.
• D. A row whose LIST_PRICE column contains value 11235.90 would be displayed as $1,123.

Answer: AC

NEW QUESTION 18
Examine the structure of the ORDERS table: (Choose the best answer.)

You want to find the total value of all the orders for each year and issue this command:
SQL> SELECT TO_CHAR(order_date,'rr'), SUM(order_total) FROM orders GROUP BY TO_CHAR(order_date, 'yyyy');
Which statement is true regarding the result?

• A. It executes successfully but does not give the correct output.
• B. It executes successfully but gives the correct output.
• C. It returns an error because the TO_CHAR function is not valid.
• D. It return an error because the datatype conversion in the SELECT list does not match the data type conversion in the GROUP BY clause.

Answer: D