Oracle Database SQL Expert - 1Z0-047 Exam Questions [2026]

QUESTION NO: 1
Given below are the SQL statements executed in a user session:
CREATE TABLE product
(pcode NUMBER(2),
pnameVARCHAR2(10));
INSERT INTO product VALUES(1, 'pen');
INSERT INTO product VALUES (2,'pencil);
SAVEPOINT a;
UPDATE product SET pcode = 10 WHERE pcode = 1;
SAVEPOINT b;
DELETE FROM product WHERE pcode = 2;
COMMIT;
DELETE FROM product WHERE pcode=10;
ROLLBACK TO SAVEPOINT a;
Which statement describes the consequences?

A. Only the second DELETE statement would be rolled back. B. Both the DELETE statements and the UPDATE statement would be rolled back. C. No SQL statement would be rolled back. D. Both the DELETE statements would be rolled back.

Correct Answer: C

QUESTION NO: 2
View the Exhibit and examine the data in the DEPARTMENTS tables.
Evaluate the following SQL statement:
SELECT department_id "DEPT_ID", department_name , 'b' FROM departments WHERE department_id=90 UNION SELECT department_id, department_name DEPT_NAME, 'a' FROM departments WHERE department_id=10 Which two ORDER BY clauses can be used to sort the output of the above statement? (Choose two.)

A. ORDER BY DEPT_ID; B. ORDER BY 'b'; C. ORDER BY 3; D. ORDER BY DEPT NAME;

Correct Answer: A,C

QUESTION NO: 3
View the Exhibit and examine the structure of the ORDERS table. Which UPDATE statement is valid?

A. UPDATE orders
SET order_date = 12-mar-2007\ order_total IS NULL WHERE order_id = 2455; B. UPDATE orders
SET order_date = '12-mar-2007'
AND order_total = TO_NUMBER(NULL)
WHERE order_id = 2455; C. UPDATE orders
SET order_date = TO_DATE('12-mar-2007,l,dd-mon-yyyy')l SET order_total =
TO_NUMBER(NULL) WHERE order id = 2455; D. UPDATE orders
SET order_date = 12-mar-2007', order_total = NULL WHERE order_id = 2455;

Correct Answer: D

QUESTION NO: 4
View the Exhibit and examine the data in the DEPARTMENTS tables.
Evaluate the following SQL statement:
SELECT department_id "DEPT_ID", department_name , 'b' FROM departments WHERE department__id=90 UNION SELECT department_id, department_name DEPT_NAME, 'a' FROM departments WHERE department_id=10
Which two ORDER BY clauses can be used to sort the output of the above statement? (Choose two.)

A. ORDER BY'b'; B. ORDER BY DEPT_ID; C. ORDER BY 3; D. ORDER BY DEPT NAME;

Correct Answer: B,C

QUESTION NO: 5
View the Exhibit and examine the descriptions for ORDERS and ORDER_ITEMS tables.
Evaluate the following SQL statement:
SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) "Order Amount"

FROM order_items oi JOIN orders o ON oi.order_id = o.order_id GROUP BY CUBE (o.customer_id, oi.product_id);
Which three statements are true regarding the output of this SQL statement? (Choose three.)

A. t would return the subtotals for the Order Amount of every PRODUCT_ID and CUSTOMER_ID as one group. B. t would return only the grand total for the Order Amount of every CUSTOMER_ID and PRODUCT_ID as one group. C. t would return the subtotals for the Order Amount of every CUSTOMER_ID and PRODUCT_ID as one group. D. t would return the subtotals for the Order Amount for every PRODUCT_ID. E. t would return the subtotals for the Order Amount of every CUSTOMER_ID.

Correct Answer: C,D,E

QUESTION NO: 6
View the Exhibit and examine the details for the CATEGORIES_TAB table. Evaluate the following incomplete SQL statement: SELECT category_name ,category_description
FROM categories_tab
You want to display only the rows that have 'harddisks' as part of the string in the
CATEGORY_DESCRIPTION column.
Which two WHERE clause options can give you the desired result? (Choose two.)

A. WHERE REGEXP_LIKE (category_description, '^H|hard+.s$'); B. WHERE REGEXP_LIKE (category_description, 'hard+.s'); C. WHERE REGEXP_LIKE (category_description, '[^Hlhard+.s]'); D. WHERE REGEXP_LIKE (category_description, '^H|hard+.s');

Correct Answer: B,D

QUESTION NO: 7
View the Exhibit and examine the description of the EMPLOYEES table. Evaluate the following SQL statement: SELECTfirst_name, employee_id, NEXr_DAY(ADD_MONTHS(hire_date, 6), 1) "Review" FROM
employees;
The query was written to retrieve the FIRST_NAME, EMPLOYEE_ID, and review date for employees. The review date is the first Monday after the completion of six months of the hiring. The
NLS_TERRITORY parameter is set to AMERICA in the session. Which statement is true regarding this query?

A. The query would execute to give the desired output. B. The query would execute but the output would give review dates that are Sundays. C. The query would not execute because date functions cannot be nested. D. The query would not execute because the NEXT_DAY function accepts a string as argument.

Correct Answer: B

QUESTION NO: 8
Evaluate the following DELETE statement:
DELETE FROM orders;
There are no other uncommitted transactions on the ORDERS table.
Which statement is true about the DELETE statement?

A. It removes all the rows in the table and does not allow ROLLBACK B. It removes all the rows in the table and allows ROLLBACK C. It removes all the rows as well as the structure of the table. D. It would not remove the rows if the table has a primary key.

Correct Answer: B

QUESTION NO: 9
View the Exhibit and examine the details of the EMPLOYEES table.
Evaluate the following SQL statement:
SELECT phone_number,
REGEXP_REPLACE(phone_number,'([[: digit: ]]{3})\.([[: digit: ]]{3})\.([[: digit: ]]{4})', ,(\1)\2-\3')
"PHONE NUMBER"
FROM employees;
The query was written to format the PHONE_NUMBER for the employees. Which option would
be the correct format in the output?

A. (xxx) xxx-xxxx B. (xxx) xxxxxxx C. xxx-(xxx)-xxxx D. xxx-xxx-xxxx

Correct Answer: A