Free Oracle Database SQL Practice Questions

Sample Practice Questions

Try these 10 sample questions from the Oracle Database SQL practice bank. Each includes the answer and explanation. Continue with the interactive practice above to review the full question set.

1. 1In the relational model, how does a table represent a relation?

   1. A.Rows represent tuples; columns represent attributes.Correct answer: A
   2. B.Rows represent attributes; columns represent tuples.
   3. C.Both rows and columns represent attributes.
   4. D.Both rows and columns represent tuples.

   Explanation: Oracle states: "A table is a two-dimensional representation of a relation in the form of rows (tuples) and columns (attributes)." Thus tuples correspond to rows and attributes to columns.

   Reference: Introduction to Oracle Database — Relational Model

2. 2Without parentheses, what is the result of the Oracle SQL expression 5 + 2 * 4?

   1. A.28
   2. B.13Correct answer: B
   3. C.8
   4. D.7

   Explanation: Oracle states that "multiplication has a higher precedence than addition". Multiplying 2 by 4 and adding 5 yields 13.

   Reference: About SQL Operators — Operator Precedence, Precedence Example

3. 3What does SUBSTR('DATABASE', -3) return in Oracle SQL?

   1. A.DAT
   2. B.BAS
   3. C.ASECorrect answer: C
   4. D.ABA

   Explanation: Oracle states: "If position is negative, then Oracle counts backward from the end of char." It also states: "If substring_length is omitted, then Oracle returns all characters to the end of char." The final three characters are ASE.

   Reference: SUBSTR — Purpose

4. 4Which clause restricts groups to those whose aggregate total meets a specified condition?

   1. A.WHERE
   2. B.HAVINGCorrect answer: B
   3. C.ORDER BY
   4. D.FROM

   Explanation: Oracle states: "You use aggregate functions in the HAVING clause to eliminate groups from the output based on the results of the aggregate functions, rather than on the values of the individual rows of the queried table or view." HAVING filters the grouped results.

   Reference: Aggregate Functions — Aggregate Functions

5. 5In a SELECT expression, what value does a scalar subquery contribute when it returns no rows?

   1. A.0
   2. B.NULLCorrect answer: B
   3. C.An empty result set in place of the scalar
   4. D.An error because a row is required

   Explanation: Oracle states: "If the subquery returns 0 rows, then the value of the scalar subquery expression is NULL. If the subquery returns more than one row, then Oracle returns an error." An empty scalar subquery evaluates to NULL.

   Reference: Scalar Subquery Expressions — Scalar Subquery Expressions

6. 6What must match between the select lists of component queries in an Oracle compound query?

   1. A.The expression counts and corresponding data type groups must match.Correct answer: A
   2. B.The expression counts must match but data type groups may differ.
   3. C.The data type groups must match but expression counts may differ.
   4. D.Neither expression counts nor data type groups need to match.

   Explanation: Oracle states: "The corresponding expressions in the select lists of the component queries of a compound query must match in number and must be in the same data type group (such as numeric or character)." Corresponding expressions must satisfy both requirements.

   Reference: The Set Operators — The Set Operators

7. 7What happens to the indexed table's stored data when an independent index is dropped?

   1. A.The table data remains physically unaffected.Correct answer: A
   2. B.All indexed table rows are deleted.
   3. C.The indexed columns are dropped from the table.
   4. D.The table data is moved into a new index.

   Explanation: Oracle states: "Indexes are schema objects that are logically and physically independent of the data in the objects with which they are associated. Thus, you can drop or create an index without physically affecting the indexed table." Dropping the index does not physically affect the indexed table.

   Reference: Indexes and Index-Organized Tables — Introduction to Indexes

8. 8For a view created WITH READ ONLY, how are UPDATE statements through that view treated?

   1. A.UPDATE through the view is prohibited.Correct answer: A
   2. B.UPDATE is permitted if the changed row satisfies the view query.
   3. C.UPDATE is permitted without checking changed rows against the view query.
   4. D.UPDATE is permitted if the base table has a primary key.

   Explanation: Oracle states: "Specify WITH READ ONLY to indicate that the table or view cannot be updated." The clause prevents updates through the view.

   Reference: CREATE VIEW — subquery_restriction_clause, WITH READ ONLY

9. 9Which is an example of an Oracle system privilege?

   1. A.CREATE TABLECorrect answer: A
   2. B.SELECT on a specific table
   3. C.UPDATE on a specific table
   4. D.DELETE on a specific table

   Explanation: Oracle states: "For example, to create a table, the user must have the CREATE TABLE or CREATE ANY TABLE system privilege." CREATE TABLE is a system privilege; table-specific SELECT, UPDATE and DELETE grants are object privileges.

   Reference: Configuring Privilege and Role Authorization — 4.10.1.9 How Roles Aid or Restrict DDL Usage

10. 10Which time zone determines the value returned by CURRENT_DATE?

    1. A.The session time zoneCorrect answer: A
    2. B.The database time zone regardless of session settings
    3. C.The database server operating-system time zone regardless of session settings
    4. D.UTC regardless of session settings

    Explanation: Oracle states: "CURRENT_DATE returns the current date in the session time zone, in a value in the Gregorian calendar of data type DATE." CURRENT_DATE uses the session time zone.

    Reference: CURRENT_DATE — Purpose