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

Question 1

Evaluate the following SQL statements in the given order:
DROP TABLE dept;
CREATE TABLE dept (deptno NUMBER(3) PRIMARY KEY, deptname VARCHAR2(10));
DROP TABLE dept;
FLASHBACK TABLE dept TO BEFORE DROP;
Which statement is true regarding the above FLASHBACK operation?

A. It does not recover any of the tables because FLASHBACK is not possible in this case.

B. It recovers both the tables but the names would be changed to the ones assigned in the
RECYCLEBIN.

C. It recovers only the first DEPT table.

D. It recovers only the second DEPT table.

Correct Answer: D

Question 2

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 3

EMPDET is an external table containing the columns EMPNO and ENAME. Which command would work in relation to the EMPDET table?

A. CREATE INDEX empdet_idx
ON empdet(empno);

B. CREATE VIEW empvu AS SELECT* FROM empdept;

C. UPDATE empdet SET ename = 'Amit'
WHERE empno = 1234;

D. DELETE FROM empdet
WHERE ename LIKE 'J%';

Correct Answer: B

Question 4

View the Exhibit and examine DEPARTMENTS and the LOCATIONS tables.
Evaluate the following SOL statement:
SELECT location_id, city FROM locations I WHERE NOT EXISTS (SELECT location_id FROM departments WHERE location_id <> I. location_id);
This statement was written to display LOCATION_ID and CITY where there are no departments located. Which statement is true regarding the execution and output of the command?

A. The statement would execute but it will return zero rows because the WHERE clause in the inner query should have the = operator instead of <>.

B. The statement would not execute because the WHERE clause in the outer query is missing the column name for comparison with the inner query result.

C. The statement would not execute because the = comparison operator is missing in the WHERE clause of the outer query.

D. The statement would execute and would return the desired results.

Correct Answer: A

Question 5

Which two statements are true regarding multiple-row subqueries? (Choose two.)

A. They can be used to retrieve multiple rows from a single table only.

B. They can contain group functions.

C. They use the < ALL operator to imply less than the maximum.

D. 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.

E. They always contain a subquery within a subquery.

Correct Answer: B,D

Question 6

View the Exhibit and examine the details of the
PRODUCT_INFORMATION table.
Evaluate the following SQL statement:
SELECT TO_CHAR(list_price ,'$9,999")
FROM product_information;

Which two statements would be true regarding the output for this SQL statement? (Choose two.)

A. The LIST_PRICE column having value 11235.90 would be displayed as #######.

B. The LIST_PRICE column having value 1123.90 would be displayed as $1,123.

C. The LIST_PRICE column having value 1123.90 would be displayed as $1,124.

D. The LIST_PRICE column having value 11235.90 would be displayed as $1,123.

Correct Answer: A,C

Question 7

Which two statements are true? (Choose two.)

A. All the dynamic performance views prefixed with V$ are accessible to all the database users.

B. The USER_OBJECTS view can provide information about the tables and views created by the user only.

C. The user SYSTEM owns all the base tables and user-accessible views of the data dictionary.

D. The USER_SYNONYMS view can provide information about private synonyms.

E. DICTIONARY is a view that contains the names of all the data dictionary views that the user can access.

Correct Answer: D,E

Question 8

View the Exhibit and examine the structure of the ORDERS and ORDER_ITEMS tables.
Evaluate the following SQL statement:
SELECT oi.order_id, product_id, order_date FROM order_items oi JOIN orders o
USING(order_id);
Which statement is true regarding the execution of this SQL statement?

A. The statement would not execute because the column part of the USING clause cannot have a qualifier in the SELECT list.

B. The statement would not execute because the table alias prefix is not used in the USING clause.

C. The statement would not execute because table aliases are not allowed in the JOIN clause.

D. The statement would not execute because all the columns in the SELECT clause are not prefixed with table aliases.

Correct Answer: A

Welcome to TestSimulate

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