Oracle Database 12c: SQL Fundamentals (1z1-061) Free Practice Test

Question 1

View the Exhibit to examine the description for the SALES table. Which views can have all DML operations performed on it? (Choose all that apply.)

A. CREATE VIEW v3AS SELECT * FROM SALESWHERE cust_id = 2034WITH CHECK
OPTION;

B. CREATE VIEW v1AS SELECT * FROM SALESWHERE time_id <= SYSDATE -
2 *365WITH CHECK OPTION;

C. CREATE VIEW v4AS SELECT prod_id, cust_id, SUM(quantity_sold) FROM
SALESWHERE time_id <= SYSDATE - 2*365GROUP BY prod_id, cust_idWITH CHECK OPTION;

D. CREATE VIEW v2AS SELECT prod_id, cust_id, time_id FROM SALESWHERE time_id
< = SYSDATE - 2*365WITH CHECK OPTION;

Correct Answer: A,B

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 2

Examine the data in the PROMO_BEGIN_DATE column of the promotions table:

You want to display the number of promotions started in 1999 and 2000.
Which query gives the correct output?

A. Option D

B. Option C

C. Option A

D. Option B

Correct Answer: C

Question 3

View the Exhibit and examine the structure of the PROMOTIONS table.

Examine the following two SQL statements:

Which statement is true regarding the above two SQL statements?

A. statement 2 gives an error, statement 1 executes successfully

B. statement 1 and statement 2 execute successfully and give a different output

C. statement 1 gives an error, statement 2 executes successfully

D. statement 1 and statement 2 execute successfully and give the same output

Correct Answer: B

Question 4

Evaluate the SQL statement
DROP TABLE DEPT:
Which four statements are true of the SQL statement? (Choose four.)

A. All pending transactions are committed.

B. You cannot roll back this statement.

C. All synonyms based on the DEPT table are deleted.

D. All views based on the DEPT table are deleted.

E. All data in the table is deleted, and the table structure is also deleted.

F. All data in the table is deleted, but the structure of the table is retained.

G. All indexes based on the DEPT table are dropped.

Correct Answer: A,B,E,G

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 5

What is true regarding sub queries?

A. The inner query returns a value to the outer query

B. The inner query always sorts the results of the outer query

C. The outer query always sorts the results of the inner query

D. The outer query must return a value to the outer query

E. The inner query must always return a value or the outer query will give an error

Correct Answer: A

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 6

Which three statements/commands would cause a transaction to end? (Choose three.)

A. SAVEPOINT

B. COMMIT

C. ROLLBACK

D. SELECT

E. CREATE

Correct Answer: B,C,E

Question 7

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

A. All views and synonyms remain but they are invalidated.

B. A pending transaction in the session is committed.

C. All data in the table is deleted but the table structure remains.

D. All indexes on the table remain but they are invalidated.

E. All data along with the table structure is deleted.

Correct Answer: A,B,E

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 8

Which are iSQL*Plus commands? (Choose all that apply.)

A. DESCRIBE

B. RENAME

C. DELETE

D. SELECT

E. INSERT

F. UPDATE

Correct Answer: A

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 9

There is a simple view SCOTT.DEPT_VIEW on the table SCOTT.DEPT.
This insert fails with an error:
SQL> insert into dept_view values('SUPPORT','OXFORD'); insert into dept_view values('SUPPORT','OXFORD')
* ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."DEPT"."DEPTNO")
What might be the problem?

A. The view was created as WITH READ ONLY.

B. The INSERT violates a constraint on the view.

C. The view was created as WITH CHECK OPTION.

D. The INSERT violates a constraint on the detail table.

Correct Answer: D

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 10

View the Exhibit and examine the structure of the CUSTOMERS table.

Which two tasks would require subqueries or joins to be executed in a single statement?
(Choose two.)

A. listing of customers who do not have a credit limit and were born before 1980

B. listing of those customers whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo'

C. finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers

D. finding the number of customers, in each city, whose marital status is 'married'

E. finding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'

Correct Answer: B,C

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 11

The STUDENT_GRADES table has these columns:
STUDENT_ID NUMBER(12)
SEMESTER_END DATE
GPA NUMBER(4, 3)
Which statement finds the highest grade point average (GPA) per semester?

A. SELECT MAX(gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL;

B. SELECT MAX(gpa) GROUP BY semester_end WHERE gpa IS NOT NULL FROM
student_grades;

C. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL;

D. SELECT (gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL;

E. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL GROUP BY
semester_end;

Correct Answer: E

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 12

Which statements are correct regarding indexes? (Choose all that apply.)

A. A nondeferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index.

B. A FOREIGN KEY constraint on a column in a table automatically creates a non unique key

C. When a table is dropped, the corresponding indexes are automatically dropped

D. For each data manipulation language (DML) operation performed, the corresponding indexes are automatically updated.

Correct Answer: A,C,D

Welcome to TestSimulate

Oracle Database 12c: SQL Fundamentals (1z1-061) Free Practice Test