Exam 1Z0-047 - Oracle Database SQL Certified Expert

SQL, or Structured Query Language, is essential for managing data in a relational database management system (RDBMS). Oracle has been the leading player in the RDBMS game for quite a while now, and while you may occasionally hear opinions to the contrary, that position of preeminence is likely to remain secure in the foreseeable future.

‍

Oracle certification expert Matthew Morris has created a series of practice tests at his site, Oracle Certification Prep. These free practice questions are a sampling of what you'll find there. After you try these questions, visit Oracle Certification Prep for (paid) access to a full 1Z0-047: Oracle Database SQL Expert practice test.

‍

Ready to rumble? Simply scroll down the page to begin your quiz. Or check out our other free Oracle quizzes.

‍

Note: Your browser must permit multiple dialog boxes per page in order to complete this quiz.

‍

Question 1:

Which of the following is a valid function to use for measures in a pattern matching query?

A. RUNNING COUNT()
B. LAST FINAL()
C. AVG COUNT()
D. MULTIPLIER()
E. NEXT COUNT()

‍

Question 2:

Which of the following statements is true about a correlated subquery?

A. They occur when a nested subquery references a column from a table referred to in a parent statement two or more levels above the subquery.
B. A correlated subquery is evaluated once for each row processed by the parent statement.
C. Correlated subqueries only occur when the parent statement is a SELECT operation.
D. They are used to resolve multiple-part question for which the answer is independent of the row processed by the parent statement.
E. Correlating the subquery with the parent normally leads to improved query performance.

‍

Question 3:

In a database with MAX_STRING_SIZE = EXTENDED, which of the following statements about Oracle built-in data types is true?

A. The precision of a NUMBER column can range from -84 to 127 and the scale can range from 1 to 38.
B. A table can have only a single column with the data type ROWID.
C. The maximum size of a VARCHAR2 column is 4000.
D. The INTERVAL YEAR TO MONTH data type stores a period of time in years and months.
E. The maximum size of a CHAR column is 32767.

‍

Question 4:

Which of the following statements about synonyms is true?

A. It is possible to use a synonym when performing a TRUNCATE operation on a referenced table.
B. Creating a PUBLIC synonym for a table is equivalent to granting SELECT on it to PUBLIC.
C. A user can create a public synonym for objects in their own schema without having the CREATE PUBLIC SYNONYM privilege.
D. Synonyms can allow applications to function without modification regardless of which database holds a referenced object.
E. It is not possible to have both a PUBLIC and PRIVATE synonym with the same name on a given table.

‍

Question 5:

Which of the following statements about the Oracle data dictionary is true?

A. Data for the dynamic performance views is stored in the SYSTEM tablespace.
B. The V$DICTIONARY dynamic view contains the names and abbreviated descriptions of all data dictionary views.
C. The SYS and SYSTEM users own all of the base tables and user-accessible views of the data dictionary.
D. The data dictionary tables and views for a database are stored in the SYSTEM and SYSAUX tablespaces.
E. There is a dedicated cache used to store much of the data dictionary in memory for rapid access.