NEW QUESTION 1
Evaluate the following query:
SQL> SELECT TRUNC (ROUND (156.00, -1),-1) FROM DUAL;
What would be the outcome?

• A. 150
• B. 200
• C. 160
• D. 16
• E. 100

Answer: C

Explanation: References:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions135.htm https://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_functions_2127.htm

NEW QUESTION 2
Examine the data in the CUSTOMERS table:

You want to list all cities that have more than one customer along with the customer details. Evaluate the following query:

Which two JOIN options can be used in the blank in the above query to give the correct output? (Choose two.)

• A. LEFT OUTER JOIN
• B. JOIN
• C. NATURAL JOIN
• D. RIGHT OUTER JOIN
• E. FULL OUTER JOIN

Answer: BD

NEW QUESTION 3
View the Exhibit and examine the structure of the EMP table which is not partitioned and not an index-organized table. (Choose two.)

Evaluate this SQL statement: ALTER TABLE emp
DROP COLUMN first_name; Which two statements are true?

• A. The FIRST_NAME column can be dropped even if it is part of a composite PRIMARY KEY provided the CASCADE option is added to the SQL statement.
• B. The FIRST_NAME column would be dropped provided at least one column remains in the table.
• C. The FIRST_NAME column would be dropped provided it does not contain any data.
• D. The drop of the FIRST_NAME column can be rolled back provided the SET UNUSED option is added to the SQL statement.

Answer: B

NEW QUESTION 4
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. Which SQL statement must you execute?

• A. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", $2 AS "LATE FEE" FROM BOOKS_TRANSACTIONS
• B. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", '$2' AS "LATE FEE" FROM BOOKS_TRANSACTIONS
• C. SELECT member_id 'MEMBER ID', due_date 'DUE DATE', '$2 AS LATE FEE' FROM BOOKS_TRANSACTIONS;
• D. SELECT member_id AS MEMBER_ID, due_date AS DUE_DATE, $2 AS LATE_FEE FROM BOOKS_TRANSACTIONS

Answer: B

NEW QUESTION 5
Examine the data in the CUST_NAME column of the CUSTOMERS table.
CUST_NAME
-------------------
Renske Ladwig Jason Mallin Samuel McCain Allan MCEwen Irene Mikilineni Julia Nayer
You need to display customers' second names where the second name starts with "Mc" or "MC". Which query gives the required output?

• A. SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)FROM customersWHERE SUBSTR (cust_name, INSTR (cust_name, ' ')+1)LIKE INITCAP ('MC%');
• B. SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)FROM customersWHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ' ')+1)) ='Mc';
• C. SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)FROM customersWHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ' ')+1))LIKE 'Mc%';
• D. SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)FROM customersWHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ' ')+1)) =INITCAP 'MC%';

Answer: C

NEW QUESTION 6
Examine the structure of the MARKS table:

Which two statements would execute successfully? (Choose two.)

• A. SELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1)FROM marksWHERE subject1 > subject2;
• B. SELECT student_name subject1FROM marksWHERE subject1 > AVG(subject1);
• C. SELECT SUM(subject1+subject2+subject3)FROM marksWHERE student_name IS NULL;
• D. SELECT student_name,SUM(subject1)FROM marksWHERE student_name LIKE ‘R%’;

Answer: AC

NEW QUESTION 7
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 AS “Name”, cust_credit_limit + 1000AS “New Credit Limit”FROM customers;
• B. SELECT cust_last_name AS Name, cust_credit_limit + 1000AS New Credit LimitFROM customers;
• C. SELECT cust_last_name AS Name, cust_credit_limit + 1000“New Credit Limit”FROM customers;
• D. SELECT INITCAP (cust_last_name) “Name”, cust_credit_limit + 1000INITCAP (“NEW CREDIT LIMIT”)FROM customers;

Answer: A

NEW QUESTION 8
Examine the structure of the MEMBERS table: NameNull?Type
------------------------------------------------------------ MEMBER_IDNOT NULLVARCHAR2 (6)
FIRST_NAMEVARCHAR2 (50)
LAST_NAMENOT NULLVARCHAR2 (50)
ADDRESSVARCHAR2 (50)
CITYVARCHAR2 (25)
STATEVARCHAR2 (3)
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: B

NEW QUESTION 9
Which statement is true about an inner join specified in the WHERE clause of a query?

• A. It must have primary-key and foreign-key constraints defined on the columns used in the join condition.
• B. It requires the column names to be the same in all tables used for the join conditions.
• C. It is applicable for equijoin and nonequijoin conditions.
• D. It is applicable for only equijoin conditions.

Answer: C

NEW QUESTION 10
Examine the structure of the EMPLOYEES table. (Choose the best answer.)

You must display the details of employees who have manager with MANAGER_ID 100, who were hired in the past 6 months and who have salaries greater than 10000.

• A. SELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000UNION ALL SELECT last_name, hire_date, salaryFROM employeesWHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100)INETRSECTSELECT last_name, hire_date, salaryFROM employees WHERE hire_date > SYSDATE- 180;
• B. SELECT last_name, hire_date, salaryFROM employeesWHERE manager_id = (SELECT employee_id FROM employees WHERE employee_id = 100)UNION ALL(SELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE -180INTERSECTSELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000);
• C. SELECT last_name, hire_date, salaryFROM employeesWHERE manager_id = (SELECT employee_id FROM employees WHERE employee_id = '100')UNIONSELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE -180INTERSECTSELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000;
• D. (SELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000UNION ALLSELECT last_name, hire_date, salaryFROM employeesWHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100))UNIONSELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE -180;

Answer: C

NEW QUESTION 11
Examine the structure of the MEMBERS table: (Choose the best answer.)

Examine the SQL statement:
SQL > SELECT city, last_name LNAME FROM MEMBERS ORDER BY 1, LNAME DESC;
What would be the result execution?

• A. It displays all cities in descending order, within which the last names are further sorted in descending order.
• B. It fails because a column alias cannot be used in the ORDER BY clause.
• C. It fails because a column number and a column alias cannot be used together in the ORDER BY clause.
• D. It displays all cities in ascending order, within which the last names are further sorted in descending order.

Answer: D

NEW QUESTION 12
Which three statements are true regarding the WHERE and HAVING clauses in a SQL statement? (Choose three.)

• A. WHERE and HAVING clauses cannot be used together in a SQL statement.
• B. The HAVING clause conditions can have aggregate functions.
• C. The HAVING clause conditions can use aliases for the columns.
• D. The WHERE clause is used to exclude rows before the grouping of data.
• E. The HAVING clause is used to exclude one or more aggregated results after grouping data.

Answer: ABD

NEW QUESTION 13
Examine the following query:
SQL> SELECT prod_id, amount_sold FROM sales
ORDER BY amount_sold
FETCH FIRST 5 PERCENT ROWS ONLY;
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

Explanation: References:
https://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1

NEW QUESTION 14
The first DROP operation is performed on PRODUCTS table using the following command: DROP TABLE products PURGE;
Then you performed the FLASHBACK operation by using the following command: FLASHBACK TABLE products TO BEFORE DROP;
Which statement describes the outcome of the FLASHBACK command?

• A. It recovers only the table structure.
• B. It recovers the table structure, data, and the indexes.
• C. It recovers the table structure and data but not the related indexes.
• D. It is not possible to recover the table structure, data, or the related indexes.

Answer: D

Explanation: References:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9003.htm

NEW QUESTION 15
On your Oracle 12c database, you invoked SQL *Loader to load data into the EMPLOYEES table in the HR schema by issuing the following command:
$> sqlldr hr/hr@pdb table=employees
Which two statements are true regarding the command?

• A. It succeeds with default settings if the EMPLOYEES table belonging to HR is already defined in the database.
• B. It fails because no SQL *Loader data file location is specified.
• C. It fails if the HR user does not have the CREATE ANY DIRECTORY privilege.
• D. It fails because no SQL *Loader control file location is specified.

Answer: AC

NEW QUESTION 16
View the Exhibit and examine the structure of the ORDERS table.

Which UPDATE statement is valid?

• A. UPDATE ordersSET order_date = ’12-mar-2007’,order_total IS NULLWHERE order_id = 2455;
• B. UPDATE ordersSET order_date = ’12-mar-2007’,AND order_total = TO_NUMBER(NULL)WHERE order_id = 2455;
• C. UPDATE ordersSET order_date = ’12-mar-2007’,order_total = NULLWHERE order_id = 2455;
• D. UPDATE ordersSET order_date = TO_DATE(’12-mar-2007’,’dd-mon-yyyy’),SET order_total = TO_NUMBER (NULL)WHERE order_id = 2455;

Answer: C

NEW QUESTION 17
View the Exhibit and examine the structure of the PRODUCTS table. (Choose the best answer.)

You must display the category with the maximum number of items.
You issue this query:
SQL > SELECT COUNT(*), prod_category_id FROM products
GROUP BY prod_category_id
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM porducts);
What is the result?

• A. It generates an error because = is not valid and should be replaced by the IN operator.
• B. It executes successfully but does not give the correct output.
• C. It executes successfully and gives the correct output.
• D. It generate an error because the subquery does not have a GROUP BY clause.

Answer: D

NEW QUESTION 18
Which two statements are true about sequences crated in a single instance Oracle database?

• A. The numbers generated by an explicitly defined sequence can only be used to insert data in one table.
• B. DELETE <sequencename> would remove a sequence from the database.
• C. CURRVAL is used to refer to the most recent sequence number that has been generated for a particular sequence.
• D. When the MAXVALUE limit for a sequence is reached, it can be increased by using the ALTER SEQUENCE statement.
• E. When the database instance shuts down abnormally, sequence numbers that have been cached but not used are available again when the instance is restarted.

Answer: CD