Oracle Database SQL Expert (1Z1-047) Free Practice Test

Question 1

View the Exhibit and examine the structure of the ORDER_ITEMS table. You need to display the ORDER_ID of the order that has the highest total value among all the
orders in the ORDER_ITEMS table. Which query would produce the desired output?
ORDER ITEMS

A. SELECT order_id FROM order_items
WHERE (unit_price*quantity) = (SELECT MAX(unit_price*quantity) FROM order_items GROUP
BY order_id);

B. SELECT order_id FROM order_items
WHERE(unit_price*quantity) = MAX(unit_price*quantity) GROUP BY order_id;

C. SELECT order_id FROM order_items GROUP BY order_id
HAVING SUM(unit_price*quantity) =(SELECT MAX(SUM(unit_price*quantity)j FROM
order_items GROUP BY order_id);

D. SELECT order_id FROM order_items
WHERE(unit_price*quantity) = (SELECT MAX(unit_price*quantity) FROM order_items) GROUP
BY order_id;

Correct Answer: C

Question 2

Which three statements are true? (Choose three.)

A. ATIMESTAMP data type column stores only time values with fractional seconds.

B. The value for a CHAR data type column is blank-padded to the maximum defined column width.

C. The minimum column width that can be specified for a varchar2 data type column is one.

D. The BLOB data type column is used to store binary data in an operating system file.

E. Only one LONG column can be used per table.

Correct Answer: B,C,E

Question 3

Which three statements indicate the end of a transaction? (Choose three.)

A. after a CREATE statement is issued

B. after a COMMIT is issued

C. after a SELECT statement is issued

D. after a ROLLBACK is issued

E. after a SAVEPOINT is issued

Correct Answer: A,B,D

Question 4

Evaluate the following query:
SELECT INTERVAL 300' MONTH,
INTERVAL 54-2' YEAR TO MONTH,
INTERVAL '11:12:10.1234567' HOUR TO SECOND
FROM dual;
What is the correct output of the above query?

A. +25-00, +54-02, +00 11:12:10.123457

B. +00-300, +00-650, +00 11:12:10.123457

C. +25-00, +00-650, +00 11:12:10.123457

D. +00-300, +54-02, +00 11:12:10.123457

Correct Answer: A

Question 5

View the Exhibit and examine the structure of ORDER_ITEMS and ORDERS tables.
You need to remove from the ORDER_ITEMS table those rows that have an order status of 0 or
1 in the ORDERS table.
Which DELETE statements are valid? (Choose all that apply.)

A. DELETE FROM order_items WHERE order_id IN (SELECT order_id FROM orders WHERE order_status in (0,1));

B. DELETE FROM order_items i WHERE order_id = (SELECT order_id FROM orders o
WHERE i. order_id = o. order_id AND
order_status IN (0,1));

C. DELETE FROM (SELECT* FROM order_items i.orders o
WHERE i.order_id = o.order_id AND order_status IN (0,1));

D. DELETE * FROM order_items WHERE order_id IN (SELECT order_id FROM orders WHERE order_status IN (0,1));

Correct Answer: A,B,C

Question 6

The details of the order ID, order date, order total, and customer ID are obtained from the ORDERS table. If the order value is more than 30000, the details have to be added to the LARGEjDRDERS table. The order ID, order date, and order total should be added to the ORDERJHISTORY table, and order ID and customer ID should be added to the CUSTJHISTORY table. Which multitable INSERT statement would you use?

A. Pivoting INSERT

B. Unconditional INSERT

C. Conditional ALL INSERT

D. Conditional FIRST INSERT

Correct Answer: C

Question 7

Given below is a list of functions and the tasks performed by using these functions, in random
order.
Function Usage
1) LPAD a) Used to truncate a column, expression, or value to n decimal places 2) TRUNC b)
Used to remove heading or trailing or both characters from the character string 3) DECODE c)
Pads the character value right-justified to a total width of n character positions 4) TRIM d) Used to
return the numeric value for position of a named character from the character string 5) INSTR e) Used to translate an expression after comparing it with each search value
Which option correctly matches the function names with their usage?

A. 1-e, 2-a, 3-c, 4-d, 5-b

B. 1-e, 2-b, 3-c, 4-a, 5-d

C. 1-c, 2-a, 3-e, 4-b, 5-d

D. 1-c, 2-b, 3-e, 4-a, 5-d

Correct Answer: C

Question 8

View the Exhibit and examine the structure of the EMPLOYEES table. You want to retrieve hierarchical data of the employees using the top-down hierarchy. Which SQL
clause would let you choose the direction to walk through the hierarchy tree?

A. HAVING

B. WHERE

C. CONNECT BY PRIOR

D. GROUP BY

E. START WITH

Correct Answer: C

Question 9

View the Exhibit and examine the description of the CUSTOMERS table. You want to add a constraint on the CUST_FIRST_NAME column of the CUSTOMERS table so
that the value inserted in the column does not have numbers. Which SOL statement would you use to accomplish the task?

A. ALTER TABLE CUSTOMERS ADD CONSTRAINT cust_f_name CHECK(REGEXP_LIKE(cust_first_name1'^A-Z '))NOVALIDATE;

B. ALTER TABLE CUSTOMERS ADD CONSTRAINT cust_f_name CHECK(REGEXP_LIKE(cust_first_name,'[[:alpha: ]]'))NOVALIDATE;

C. ALTER TABLE CUSTOMERS ADD CONSTRAINT cust_f_name CHECK(REGEXP_LIKE(cust_first_name,'^[0-9] '))NOVALIDATE;

D. ALTER TABLE CUSTOMERS ADD CONSTRAINT cust_f_name CHECK(REGEXP_LIKE(cust_first_name,'[[:digit: ]]'))NOVALIDATE ;

Correct Answer: B

Question 10

Which statement is true regarding Flashback Version Query?

A. It can be used in subqueries contained only in a SELECT statement.

B. It returns versions of rows only within a transaction.

C. It retrieves all versions including the deleted as well as subsequently reinserted versions of the rows.

D. It will return an error if the undo retention time is less than the lower bound time or SCN specified.

Correct Answer: C

Question 11

Which three tasks can be performed using regular expression support in Oracle Database 10g? (Choose three.)

A. it can be used to format the output for a column or expression having string data.

B. it can be used to concatenate two strings.

C. it can be used for string manipulation and searching operations.

D. it can be used to find out the total length of the string.

E. it can be used to find and replace operations for a column or expression having string data.

Correct Answer: A,C,E

Question 12

View the Exhibit and examine the description of the EMPLOYEES table.
You want to know the EMPLOYEE_ID and FIRST_NAME of all the records in the EMPLOYEES table wherein the JOBJD column has ST_CLERK or ST_MAN values, the DEPARTMENT_ID column has value 30, and the SALARY column has a value greater than 3,000.
Which SOL statement would get you the desired result?

A. SELECT employee_id, first_name
FROM employees
WHERE (job_id like '%MAN' AND job_id like ^CLERK")
AND department_id = 30 OR salary > 3000;

B. SELECT employee_id, first_name
FROM employees
WHERE job_id like '%MAN' OR job_id like '%CLERK'
AND (department_id = 30 OR salary > 3000);

C. SELECT employee_id, first_name
FROM employees
WHERE job_id like 'MAN%' OR job_id like 'CLERK%'
AND department_id = 30 AND salary > 3000;

D. SELECT employee_id, first_name
FROM employees
WHERE (job_id like '%MAN' OR job_id like '%CLERK')
AND department_id = 30 AND salary > 3000;

Correct Answer: D

Question 13

View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. 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?

A. INSERT INTO orders (order_id.order_date.order_mode,
(SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND
credit _limit=600) .order_total)
VALUES(1 ,'IO-mar-2007', 'direct', &customer_id, 1000);

B. INSERT INTO orders (order_id,order_date,order_mode,
(SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND
credit_limit=600) .order_total)
VALUES(1 ,'10-mar-2007', 'direct', &&customer_id, 1000);

C. INSERT INTO(SELECT o.order_id, o.order_date.o.orde_mode.c.customer_id, o.order_total
FROM orders o, customers c WHERE o.customer_id = c.customer_id
AND c.cust_last_name='Roberts'ANDc. Credit_limit=600) VALUES (1,'10-mar-2007', 'direct',(SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND Credit_limit=600), 1000);

D. INSERT INTO orders
VALUES (1,'10-mar-2007', 'direct',
(SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND
credit_limit=600), 1000);

Correct Answer: D

Welcome to TestSimulate

Oracle Database SQL Expert (1Z1-047) Free Practice Test