I85PREP
⌘ K
Oracle SQL study materials
Oracle SQL study resources

Oracle SQL practice questions

One question at a time. Build your Oracle SQL understanding.

First 50 of 200 questions. Each one lists the choices, the correct answer, and an explanation. The checker at the top of this page saves progress in this browser when it is available.

Continue in the practice tool above to load more questions, 50 at a time.

Question 1Relational database concepts and basic SQL SELECT retrieval

In a relational table, which constraint uniquely identifies each row and forbids NULL values in its columns?

  1. The check constraint
  2. The primary key constraint Correct answer.
  3. The foreign key constraint
  4. The unique constraint

Explanation

A primary key is a column or set of columns whose values uniquely identify every row, and Oracle automatically enforces NOT NULL on those columns. It is the table's designated row identifier.

Question 2Relational database concepts and basic SQL SELECT retrieval

Which element of a SELECT statement collapses duplicate rows in the result set into a single row?

  1. The WHERE clause
  2. The ORDER BY clause
  3. The DISTINCT keyword Correct answer.
  4. The ROWNUM pseudocolumn

Explanation

DISTINCT applied to the SELECT list removes duplicate rows from the output so each combination of selected values appears once. It acts on the projected rows, after they are retrieved.

Question 3Relational database concepts and basic SQL SELECT retrieval

A query ends with the clause ORDER BY salary and specifies no other sort keywords. In what order are the rows returned?

  1. Ascending salary order Correct answer.
  2. Descending salary order
  3. An order chosen unpredictably by the optimizer
  4. The order in which the rows were inserted

Explanation

When neither ASC nor DESC is written, ORDER BY sorts in ascending order. The default applies to every sort key in the clause unless overridden.

Question 4Relational database concepts and basic SQL SELECT retrieval

In a LIKE pattern, which character matches any sequence of zero or more characters?

  1. The percent character (%) Correct answer.
  2. The hash character (#)
  3. The underscore character (_)
  4. The asterisk character (*)

Explanation

In Oracle LIKE patterns the percent sign is the multi-character wildcard and matches any string of zero or more characters. The underscore is the single-character wildcard.

Question 5Relational database concepts and basic SQL SELECT retrieval

Which operator joins two character strings into a single string in Oracle SQL?

  1. +
  2. &
  3. .
  4. || Correct answer.

Explanation

The double pipe operator performs string concatenation, producing one character value from two. If either operand is NULL, the result is the other operand.

Question 6Relational database concepts and basic SQL SELECT retrieval

Which object can be queried to evaluate an expression once when no user table is needed?

  1. TAB
  2. ALL_OBJECTS
  3. DUAL Correct answer.
  4. USER_TABLES

Explanation

DUAL is a one-row, one-column table owned by SYS and available to every user, so SELECT expressions FROM dual returns exactly one row. It is the standard way to test expressions or call functions.

Question 7Relational database concepts and basic SQL SELECT retrieval

A column COMM holds NULL in some rows. Which predicate returns exactly the rows where COMM is NULL?

  1. COMM = NULL
  2. COMM <> NULL
  3. COMM IS NULL Correct answer.
  4. NVL(COMM, 0) = NULL

Explanation

NULL represents an unknown value, so any comparison with = or <> against NULL evaluates to UNKNOWN and returns no rows. The IS NULL predicate is the only test that identifies null values.

Question 8Relational database concepts and basic SQL SELECT retrieval

Which statement correctly distinguishes WHERE from HAVING?

  1. WHERE filters groups after aggregation; HAVING filters rows before aggregation.
  2. Both clauses can reference only columns that appear in the SELECT list.
  3. HAVING may be used only when the query also specifies DISTINCT.
  4. WHERE filters rows before grouping; HAVING filters groups after grouping. Correct answer.

Explanation

WHERE is applied to individual rows before any grouping takes place, while HAVING is applied to the aggregated groups produced by GROUP BY. Conditions on group results, such as COUNT(*) > 5, belong in HAVING.

Question 9Relational database concepts and basic SQL SELECT retrieval

For the predicate salary BETWEEN 1000 AND 2000, which salary values satisfy it?

  1. 1000 is included and 2000 is excluded
  2. Both endpoints are excluded
  3. Both 1000 and 2000 are included Correct answer.
  4. 1000 is excluded and 2000 is included

Explanation

BETWEEN is an inclusive range test, equivalent to salary >= 1000 AND salary <= 2000. Rows whose values equal either boundary are returned.

Question 10Relational database concepts and basic SQL SELECT retrieval

In the condition A OR B AND C, how does Oracle evaluate the operators?

  1. The optimizer reorders the operands, so results can vary
  2. Strictly left to right, so A OR B is evaluated first
  3. AND binds more tightly, so B AND C is evaluated first Correct answer.
  4. OR binds more tightly, so A OR B is evaluated first

Explanation

AND has higher precedence than OR, so the AND portion is grouped first and the expression is equivalent to A OR (B AND C). Parentheses should be used when a different grouping is intended.

Question 11Relational database concepts and basic SQL SELECT retrieval

A SELECT list defines the alias total_sal for an expression. Where can that alias be referenced within the same query?

  1. In the HAVING clause and the WHERE clause
  2. In the ORDER BY clause only Correct answer.
  3. In the WHERE clause and the GROUP BY clause
  4. In every clause of the same query

Explanation

A column alias is assigned when the SELECT list is evaluated, so only ORDER BY, which runs afterwards, can refer to it. WHERE, GROUP BY and HAVING are evaluated earlier and must use the underlying expression or column.

Question 12Relational database concepts and basic SQL SELECT retrieval

A table has 10 rows and the column BONUS is NULL in 4 of them. What does AVG(bonus) return?

  1. NULL, because the column contains NULL values
  2. The average over all 10 rows, treating NULL as zero
  3. The average over all 10 rows, treating NULL as one
  4. The average of only the 6 non-NULL values Correct answer.

Explanation

Aggregate functions ignore NULL values, so AVG sums the non-NULL values and divides by the count of those values, which is 6. NVL would be needed to treat the nulls as zeros.

Question 13Relational database concepts and basic SQL SELECT retrieval

A query is written as SELECT department_id, job_id, COUNT(*) FROM emp GROUP BY department_id; What is the outcome?

  1. The query runs and returns the first job_id found in each department.
  2. The query runs and returns one row per department, using an arbitrary job_id.
  3. An error occurs because COUNT(*) cannot be combined with GROUP BY.
  4. An error occurs because job_id is not in the GROUP BY clause. Correct answer.

Explanation

Every non-aggregate item in the SELECT list must also appear in the GROUP BY clause, otherwise each group could correspond to several values. Oracle therefore raises ORA-00979 for job_id.

Question 14Relational database concepts and basic SQL SELECT retrieval

Table A contains 5 rows and table B contains 4 rows. A query selects from both tables in the FROM clause with no join condition. How many rows does it return?

  1. 5
  2. 1
  3. 20 Correct answer.
  4. 9

Explanation

Without a join condition the result is a Cartesian product, in which every row of A is paired with every row of B. The number of rows is therefore 5 multiplied by 4.

Question 15Relational database concepts and basic SQL SELECT retrieval

What is the result of the expression 500 + NULL?

  1. An error is raised
  2. 500
  3. 0
  4. NULL Correct answer.

Explanation

Arithmetic involving NULL produces NULL, because the unknown value makes the result unknown as well. A function such as NVL or COALESCE is required to substitute a default.

Question 16Relational database concepts and basic SQL SELECT retrieval

Which statement returns the number of different, non-NULL department_id values in EMP?

  1. SELECT COUNT(DISTINCT department_id) FROM emp; Correct answer.
  2. SELECT COUNT(department_id) FROM emp;
  3. SELECT COUNT(*) FROM emp GROUP BY department_id;
  4. SELECT DISTINCT COUNT(department_id) FROM emp;

Explanation

COUNT(DISTINCT column) counts each distinct non-NULL value once, returning a single number. The DISTINCT keyword must sit inside the aggregate's parentheses to affect what is counted.

Question 17Relational database concepts and basic SQL SELECT retrieval

The subquery (SELECT manager_id FROM emp) returns some NULL values. Which predicate returns no rows at all?

  1. WHERE emp_id = ANY (SELECT manager_id FROM emp)
  2. WHERE emp_id IN (SELECT manager_id FROM emp)
  3. WHERE EXISTS (SELECT 1 FROM emp e2 WHERE e2.manager_id = emp.emp_id)
  4. WHERE emp_id NOT IN (SELECT manager_id FROM emp) Correct answer.

Explanation

NOT IN is equivalent to a series of <> comparisons combined with AND, so a NULL in the list makes each comparison UNKNOWN and the whole condition never evaluates to TRUE. Rows that match a non-NULL value also fail the NOT IN test.

Question 18Relational database concepts and basic SQL SELECT retrieval

The maximum salary in EMP is 800. What does SELECT MAX(sal) FROM emp HAVING MAX(sal) > 1000; return?

  1. No rows, because the single aggregate group fails the HAVING condition Correct answer.
  2. An error, because GROUP BY is missing
  3. One row containing NULL
  4. One row containing 800

Explanation

An aggregate query without GROUP BY treats the whole table as one group, and HAVING may then filter that single group. Because 800 is not greater than 1000, the group is discarded and no rows are returned.

Question 19Relational database concepts and basic SQL SELECT retrieval

A table contains COMM values including NULLs. A query ends with ORDER BY comm using ascending order and no null-handling keywords. Where do the NULL rows appear?

  1. Last, because NULLs sort last in ascending order by default Correct answer.
  2. First, because NULL is treated as smaller than any other value
  3. They are removed from the result set by the sort
  4. Their position depends on the execution plan chosen

Explanation

Oracle treats NULL as larger than any non-NULL value when sorting, so ascending order places NULLs last and descending order places them first. NULLS FIRST and NULLS LAST can override this default.

Question 20Relational database concepts and basic SQL SELECT retrieval

A query contains WHERE, GROUP BY and HAVING. Which sequence describes the order in which these clauses are processed?

  1. GROUP BY, then WHERE, then HAVING
  2. WHERE, then GROUP BY, then HAVING Correct answer.
  3. WHERE, then HAVING, then GROUP BY
  4. HAVING, then WHERE, then GROUP BY

Explanation

Rows are first restricted by WHERE, then collapsed into groups by GROUP BY, and finally the groups are filtered by HAVING. This is why WHERE cannot reference aggregates while HAVING can.

Question 21Restricting and sorting data

Which clause is used to restrict the rows that a SELECT statement returns?

  1. ORDER BY
  2. WHERE Correct answer.
  3. HAVING
  4. FROM

Explanation

The WHERE clause evaluates a condition for each row and keeps only the rows for which the condition evaluates to TRUE. ORDER BY only sorts the rows that survive, and HAVING filters grouped results after aggregation.

Question 22Restricting and sorting data

Which operator tests whether a value falls inside an inclusive range of values?

  1. LIKE
  2. BETWEEN ... AND ... Correct answer.
  3. IN
  4. EXISTS

Explanation

BETWEEN low AND high is true when the value is greater than or equal to the low bound and less than or equal to the high bound, so both endpoints are included. The other operators test membership in a list, pattern matching, or the existence of rows.

Question 23Restricting and sorting data

Which condition returns employees whose last name begins with the letters Sm?

  1. WHERE last_name LIKE 'Sm%' Correct answer.
  2. WHERE last_name LIKE '_Sm%'
  3. WHERE last_name = 'Sm%'
  4. WHERE last_name LIKE '%Sm'

Explanation

The percent sign matches any sequence of characters, including none, so 'Sm%' matches values that start with Sm followed by anything. A leading percent would require the letters Sm at the end instead.

Question 24Restricting and sorting data

Which condition correctly tests a column for a missing value?

  1. WHERE col IS NULL Correct answer.
  2. WHERE col = NULL
  3. WHERE col = 'NULL'
  4. WHERE col <> NULL

Explanation

A NULL is not a value, so any comparison with NULL using = or <> evaluates to UNKNOWN and never returns a row. The IS NULL predicate is the only one designed to detect the absence of a value.

Question 25Restricting and sorting data

When a query uses ORDER BY salary with no direction keyword, how are the salaries returned?

  1. In the order the rows were inserted.
  2. In an undefined order.
  3. From highest to lowest.
  4. From lowest to highest. Correct answer.

Explanation

ORDER BY defaults to ascending order when neither ASC nor DESC is specified, so the smallest value appears first. Without any ORDER BY clause at all, row order is undefined.

Question 26Restricting and sorting data

In Oracle, where do NULL values appear by default when a column is sorted in ascending order?

  1. They appear first only when the column is numeric
  2. They are removed from the result set
  3. At the end of the result set Correct answer.
  4. At the beginning of the result set

Explanation

Oracle treats NULLs as larger than any non-null value, so ascending sorts place them last and descending sorts place them first. NULLS FIRST or NULLS LAST can override this behaviour.

Question 27Restricting and sorting data

You want all rows where the value in column CODE contains a literal underscore character. Which WHERE clause does this?

  1. WHERE code LIKE '%_%'
  2. WHERE code LIKE '%#_%'
  3. WHERE code LIKE '%#_%' ESCAPE '#' Correct answer.
  4. WHERE code LIKE '%\_%'

Explanation

In a LIKE pattern the underscore matches exactly one character of any kind, so it must be escaped to be searched for literally. The ESCAPE clause declares which character cancels the special meaning of a wildcard immediately after it.

Question 28Restricting and sorting data

Consider the condition WHERE job_id = 'SA_REP' OR job_id = 'IT_PROG' AND salary > 8000. Which rows satisfy it?

  1. Only rows that are SA_REP with a salary above 8000, plus every IT_PROG row
  2. Every SA_REP row, plus IT_PROG rows whose salary is above 8000 Correct answer.
  3. Only rows that are SA_REP and also have a salary above 8000
  4. Only rows that are SA_REP or IT_PROG and have a salary above 8000

Explanation

AND has higher precedence than OR, so the condition is evaluated as job_id = 'SA_REP' OR (job_id = 'IT_PROG' AND salary > 8000). The salary test therefore applies only to the IT_PROG branch.

Question 29Restricting and sorting data

HIRE_DATE is a DATE column that stores a time component. Which rows does WHERE hire_date BETWEEN '01-JAN-2020' AND '31-DEC-2020' return?

  1. No rows, because BETWEEN cannot be used with DATE columns
  2. Only rows whose hire_date is exactly 01-JAN-2020 or exactly 31-DEC-2020
  3. Rows from 01-JAN-2020 up to midnight at the start of 31-DEC-2020 Correct answer.
  4. Rows from 01-JAN-2020 through the final second of 31-DEC-2020

Explanation

The string literal '31-DEC-2020' is converted to a date at midnight, so the upper bound is 31-DEC-2020 00:00:00. Rows hired later that day fall outside the range and require a bound such as < '01-JAN-2021'.

Question 30Restricting and sorting data

Which statement about a column alias defined in the SELECT list is true?

  1. It cannot be referenced anywhere else in the statement
  2. It can be used in both the WHERE clause and ORDER BY
  3. It can be used in the WHERE clause but not in ORDER BY
  4. It can be used in ORDER BY but not in WHERE Correct answer.

Explanation

ORDER BY is evaluated after the SELECT list, so aliases are available there. The WHERE clause is evaluated before the SELECT list is formed, so aliases are not visible to it.

Question 31Restricting and sorting data

The statement SELECT last_name, salary, hire_date FROM employees ORDER BY 3 is executed. By which item are the rows sorted?

  1. hire_date Correct answer.
  2. last_name
  3. The constant 3, so the order is unchanged
  4. salary

Explanation

An integer in the ORDER BY clause is a positional reference to the third item of the SELECT list, which is hire_date. It does not refer to a column named 3 or to a constant value.

Question 32Restricting and sorting data

The statement SELECT last_name, department_id, salary FROM employees ORDER BY department_id DESC, salary ASC is executed. How are employees who share the same department_id ordered?

  1. By salary in the order the rows are stored
  2. By salary from lowest to highest Correct answer.
  3. By last_name in alphabetical order
  4. By salary from highest to lowest

Explanation

Each ORDER BY item carries its own direction, and the second item breaks ties within the first. Here the tie-breaker is salary with ASC, which is the default direction.

Question 33Restricting and sorting data

Which condition is equivalent to WHERE department_id IN (10, 20, 30)?

  1. WHERE department_id BETWEEN 10 AND 30
  2. WHERE department_id <> 10 AND department_id <> 20 AND department_id <> 30
  3. WHERE department_id = 10 AND department_id = 20 AND department_id = 30
  4. WHERE department_id = 10 OR department_id = 20 OR department_id = 30 Correct answer.

Explanation

IN is shorthand for a series of equality tests joined by OR, so the listed values act as alternatives. A row qualifies when it matches any one of them.

Question 34Restricting and sorting data

LAST_NAME is a VARCHAR2 column. Which rows does WHERE last_name > 'M' return?

  1. Rows whose last_name contains the letter M anywhere in the value
  2. No rows, because > cannot be used with character data
  3. Only rows where last_name is exactly the single letter M
  4. Rows whose last_name sorts after 'M' in the database character set Correct answer.

Explanation

Character strings are compared position by position according to the collating sequence, so > 'M' means the value sorts after the letter M. Values such as 'Martin' and 'Smith' satisfy the test.

Question 35Restricting and sorting data

Which LIKE pattern matches values that are exactly five characters long and end with the letters son?

  1. '__son' Correct answer.
  2. '__%son'
  3. '%son'
  4. '_%son'

Explanation

Each underscore stands for exactly one character, so two underscores followed by son fixes the total length at five. The percent sign stands for any number of characters, which would make the length variable.

Question 36Restricting and sorting data

A DATE column is compared with the string literal '15-MAR-2021' in a WHERE clause. What happens during execution?

  1. The string is converted to a date using the session's default date format Correct answer.
  2. The row is returned only if the column stores the text '15-MAR-2021'
  3. The statement fails because DATE columns require an explicit TO_DATE call
  4. The string is compared as plain text, character by character

Explanation

Oracle performs implicit conversion of the character literal to DATE using the NLS_DATE_FORMAT of the session. That is why a literal in a different pattern can raise an error or match unexpected rows.

Question 37Restricting and sorting data

The statement SELECT * FROM employees WHERE department_id NOT IN (10, 20, NULL) is executed. What is returned?

  1. No rows at all Correct answer.
  2. All employees, including those with a NULL department_id
  3. Only the employees whose department_id is NULL
  4. All employees whose department_id is neither 10 nor 20

Explanation

NOT IN is shorthand for <> ALL, so each row is compared with every value in the list. Because comparing anything with NULL yields UNKNOWN, no row can make the whole condition TRUE, and an empty result is returned.

Question 38Restricting and sorting data

COMMISSION_PCT is a numeric column that contains NULLs in some rows. Which rows does WHERE NOT (commission_pct > 0.2) return?

  1. Only rows where commission_pct is not null and is 0.2 or less Correct answer.
  2. All rows where commission_pct is 0.2 or less, plus rows where it is null
  3. Only rows where commission_pct is null
  4. All rows where commission_pct is greater than 0.2

Explanation

When commission_pct is NULL, the inner comparison is UNKNOWN and NOT UNKNOWN remains UNKNOWN, so those rows are filtered out. Only rows whose value is known and fails the > 0.2 test are returned.

Question 39Restricting and sorting data

The statement SELECT DISTINCT department_id FROM employees ORDER BY salary is executed. What is the outcome?

  1. The statement succeeds and sorts each department by its lowest salary
  2. Rows are sorted by salary, then duplicates are removed
  3. The statement succeeds but the ORDER BY clause is silently ignored
  4. The statement fails because salary is not in the select list Correct answer.

Explanation

With DISTINCT, the sort key must appear in the select list because the result set contains only the distinct combinations of the listed columns. A column that is not selected cannot be used to order the result.

Question 40Restricting and sorting data

The statement SELECT salary * 12 "Annual Salary" FROM employees ORDER BY "Annual Salary" is executed. What is the result?

  1. It executes but sorts by the SALARY column, ignoring the alias
  2. It executes and sorts by the computed annual salary Correct answer.
  3. It fails because ORDER BY cannot reference a column alias
  4. It fails because identifiers containing spaces are not allowed

Explanation

A quoted alias preserves the exact spelling including the space, and ORDER BY may reference a select-list alias. Writing the alias without the quotes would raise an error because unquoted identifiers cannot contain spaces.

Question 41Using single-row functions to customize output

Which single-row function converts a character string to all uppercase letters?

  1. CONCAT
  2. LOWER
  3. INITCAP
  4. UPPER Correct answer.

Explanation

UPPER converts every alphabetic character in its argument to uppercase. It is a single-row character function, so it returns one result per row processed.

Question 42Using single-row functions to customize output

What is the result of LENGTH('Oracle 23ai')?

  1. 10
  2. NULL
  3. 12
  4. 11 Correct answer.

Explanation

LENGTH counts every character in the string, including spaces and digits. The string contains 6 letters, 1 space, and 4 characters for '23ai', giving 11.

Question 43Using single-row functions to customize output

What does the expression NVL(NULL, 'unknown') return?

  1. An ORA-00932 data type error
  2. NULL
  3. 0
  4. 'unknown' Correct answer.

Explanation

NVL returns the second argument whenever the first argument evaluates to NULL. Because the first argument is a NULL literal, the replacement value is returned.

Question 44Using single-row functions to customize output

Which statement best describes the value returned by the SYSDATE function?

  1. The date on which the current user session was started
  2. The current date and time of the database server Correct answer.
  3. Only the current date, with the time always set to 00:00:00
  4. The current date converted to UTC

Explanation

SYSDATE is a date function that returns the current date and time as set on the operating system hosting the database instance. It is evaluated once per statement and includes a time component.

Question 45Using single-row functions to customize output

What does the expression ROUND(45.926, 2) return?

  1. 45.93 Correct answer.
  2. 45.92
  3. 45.9
  4. 46

Explanation

The second argument of ROUND for numbers specifies the number of digits kept to the right of the decimal point. The third decimal digit is 6, so the value is rounded up to 45.93.

Question 46Using single-row functions to customize output

What does LPAD('SQL', 6, '*') return?

  1. 'SQL***'
  2. 'SQL'
  3. '***SQL' Correct answer.
  4. 'SQL '

Explanation

LPAD pads the string on the left until it reaches the total length given by the second argument, using the pad string supplied as the third argument. Here three asterisks are added to reach six characters.

Question 47Using single-row functions to customize output

What does SUBSTR('Oracle Database', 8, 4) return?

  1. 'Database'
  2. 'atab'
  3. 'Datab'
  4. 'Data' Correct answer.

Explanation

SUBSTR starts extracting at the position given by the second argument and returns the number of characters given by the third argument. Position 8 is the letter D, and four characters from there are 'Data'.

Question 48Using single-row functions to customize output

What does INSTR('Oracle Database', 'a', 1, 2) return?

  1. 9 Correct answer.
  2. 11
  3. 0
  4. 3

Explanation

INSTR returns the position of the requested occurrence of the substring, with the third argument giving the start position and the fourth giving which occurrence to find. The second lowercase 'a' in the string sits at position 9.

Question 49Using single-row functions to customize output

What does REPLACE('Oracle', 'Ora', 'O') return?

  1. 'Oracle'
  2. 'Ocle' Correct answer.
  3. 'Ora'
  4. 'OOracle'

Explanation

REPLACE substitutes every occurrence of the search string with the replacement string. 'Ora' appears once at the start of 'Oracle', so it is replaced by 'O' and the remainder 'cle' is kept.

Question 50Using single-row functions to customize output

What is returned by ADD_MONTHS(TO_DATE('2024-01-31','YYYY-MM-DD'), 1)?

  1. 2024-03-01
  2. 2024-02-28
  3. 2024-02-29 Correct answer.
  4. 2024-03-02

Explanation

ADD_MONTHS keeps the day-of-month where possible, but when that day does not exist in the target month it returns the last day of that month. January 31 plus one month lands on the last day of February 2024, which is the 29th because 2024 is a leap year.