I85PREP
⌘ K
Oracle 1Z0-116 study materials
Oracle 1Z0-116 study resources

Oracle 1Z0-116 practice questions

One question at a time. Build your Oracle 1Z0-116 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 1Introduction to Oracle Database

In an Oracle instance, which background process writes modified (dirty) buffers from the database buffer cache to the data files?

  1. System Monitor (SMON)
  2. Process Monitor (PMON)
  3. Log Writer (LGWR)
  4. Database Writer (DBWn) Correct answer.

Explanation

DBWn is the background process that writes dirty buffers from the database buffer cache to the data files on disk. It does not write redo; LGWR copies redo entries from the redo log buffer to the online redo log files. SMON and PMON handle recovery and process cleanup.

Question 2Introduction to Oracle Database

A dedicated server process performs a large sort whose work area is private to that session. Where is this sort area allocated?

  1. The PGA Correct answer.
  2. The shared pool of the SGA
  3. The redo log buffer in the SGA
  4. The large pool of the SGA

Explanation

Every server process has its own program global area (PGA), a private, non-shared memory region that holds sort areas, hash join areas, and session cursor state. The shared pool, redo log buffer, and large pool all belong to the SGA and are shared across sessions.

Question 3Introduction to Oracle Database

Which statement about Oracle multitenant container databases (CDBs) is true?

  1. A container database can hold only one pluggable database at any given time during normal operation.
  2. Each pluggable database in the container database maintains its own separate control files and online redo log files.
  3. PDBs share one set of control files and redo logs but have their own data files. Correct answer.
  4. Application users connect to the root container rather than to a pluggable database to run their transactions.

Explanation

In a CDB, the root and all pluggable databases share the instance, the SGA and background processes, one control file set, and one set of online redo log files. Each PDB does have its own data files, and application users connect through a service that points at their own PDB rather than at the root.

Question 4Introduction to Oracle Database

An administrator issues STARTUP NOMOUNT for a database that uses a server parameter file. Which statement describes the state reached?

  1. The online data files and redo log files are open for user transactions.
  2. The SPFILE has been read, but no memory or background processes have been allocated.
  3. The control file has been read and the data files have been located.
  4. The instance is started and the control file has not been read. Correct answer.

Explanation

NOMOUNT starts the instance: Oracle reads the server parameter file, allocates the SGA, and starts the background processes, but it does not yet read the control file. Reading the control file occurs at MOUNT, and making the data files and redo logs available to users occurs at OPEN.

Question 5Introduction to Oracle Database

Which statement correctly describes the relationship between a tablespace and its data files in an Oracle database?

  1. A tablespace contains one or more data files, each of which belongs to only that tablespace. Correct answer.
  2. A data file can hold segments that belong to tablespaces other than the one for which the data file was originally created.
  3. A tablespace is the physical operating system file on disk, while a data file is the logical container for segments.
  4. A single data file can be assigned to several different tablespaces at the same time when it has enough free space.

Explanation

A tablespace is a logical storage container composed of one or more data files; a smallfile tablespace can have many files, while a bigfile tablespace has exactly one. Each data file belongs to exactly one tablespace, and segments such as tables and indexes are created in a tablespace and physically stored in its files.

Question 6Introduction to Oracle Database

A developer wants to list only the tables owned by the schema that is currently connected. Which data dictionary view returns exactly that set?

  1. ALL_TABLES
  2. USER_TABLES Correct answer.
  3. ALL_OBJECTS
  4. DBA_TABLES

Explanation

The USER_* family of data dictionary views is restricted to objects owned by the current user, so USER_TABLES lists precisely the tables in the connected schema. ALL_TABLES adds tables the user can access but does not own, and DBA_TABLES covers every table in the database and requires elevated privileges.

Question 7Introduction to Oracle Database

A session commits a transaction that updated rows in a table. Which statement describes the Oracle behavior that makes the commit durable?

  1. PMON flushes the redo log buffer to disk and then signals the client that the transaction has committed.
  2. DBWn writes every changed data block to the data files before the commit returns to the client, so the redo is not needed.
  3. CKPT copies the contents of the redo log buffer directly into the data files at commit time.
  4. LGWR writes the redo to the online redo log before commit returns; data blocks may remain dirty. Correct answer.

Explanation

At commit, LGWR writes the transaction's redo records from the redo log buffer to the online redo log, and the commit is not acknowledged until that write completes. The changed data blocks can stay dirty in the buffer cache and be written later by DBWn, because the redo log holds everything needed to recover them.

Question 8Introduction to Oracle Database

The instance fails abruptly and the database is restarted. Which process performs instance recovery, and what does it do?

  1. SMON applies redo to roll forward, then uses undo to roll back uncommitted transactions. Correct answer.
  2. DBWn reads the undo segments and rolls back every uncommitted transaction before the database opens.
  3. PMON performs instance recovery by writing all dirty buffers from the buffer cache to the data files at startup.
  4. CKPT applies the redo log entries to the data files and then opens the database for all users to connect.

Explanation

SMON performs instance recovery automatically when the database is opened after an instance failure. It applies the online redo log to roll forward every change, committed or not, and then uses undo data to roll back the transactions that had not committed.

Question 9Retrieve Data using the SQL SELECT Statement

Which statement retrieves every column from the EMPLOYEES table using the SQL SELECT statement?

  1. SELECT * FROM employees; Correct answer.
  2. SELECT % FROM employees;
  3. SELECT COLUMNS FROM employees;
  4. SELECT ALL FROM employees;

Explanation

The asterisk is the SELECT-list wildcard that returns every column from the named table in its defined order. You do not need to list column names when all columns are required.

Question 10Retrieve Data using the SQL SELECT Statement

You need to display LAST_NAME with the column heading Employee Name, including the space. Which SELECT statement is valid in Oracle SQL?

  1. SELECT last_name AS 'Employee Name' FROM employees;
  2. SELECT last_name AS "Employee Name" FROM employees; Correct answer.
  3. SELECT last_name = "Employee Name" FROM employees;
  4. SELECT last_name AS Employee Name FROM employees;

Explanation

A column alias that contains a space or special characters must be enclosed in double quotation marks because it is a quoted identifier. Single quotation marks denote a character literal, not an alias.

Question 11Retrieve Data using the SQL SELECT Statement

An employee has a SALARY value and a NULL COMMISSION_PCT. What does SELECT salary + commission_pct FROM employees return for that employee?

  1. An error, because commission_pct can be NULL.
  2. The salary value, because Oracle ignores a NULL operand in ordinary arithmetic expressions.
  3. NULL, since arithmetic with a NULL operand yields NULL. Correct answer.
  4. Zero, because NULL is converted to zero in numeric arithmetic.

Explanation

In Oracle SQL, an arithmetic expression that includes a NULL operand evaluates to NULL unless you explicitly replace the NULL with a function such as NVL or COALESCE. NULL is not treated as zero in ordinary addition.

Question 12Retrieve Data using the SQL SELECT Statement

You execute SELECT DISTINCT department_id, job_id FROM employees;. Which rows are returned?

  1. one row for each distinct department_id value
  2. one row for each unique pair of department_id and job_id values Correct answer.
  3. one row for each distinct job_id value
  4. one row for each employee record after duplicates are removed independently from each selected column

Explanation

DISTINCT applies to the complete select list, so duplicate elimination is based on the combination of department_id and job_id. It does not remove duplicates from each column independently.

Question 13Retrieve Data using the SQL SELECT Statement

You execute SELECT first_name || NULL || last_name FROM employees;. What is the result for an employee whose FIRST_NAME is 'Ana' and LAST_NAME is 'Lee'?

  1. a single space inserted between first_name and last_name followed by the NULL text
  2. NULL, because any NULL operand makes the whole concatenation NULL
  3. an error, because NULL cannot be used with the concatenation operator
  4. first_name immediately followed by last_name, with the NULL contributing nothing Correct answer.

Explanation

Oracle's concatenation operator treats a NULL operand as having no characters, so the other operands are concatenated normally. The result is 'AnaLee' for the sample values.

Question 14Retrieve Data using the SQL SELECT Statement

Which statement returns the literal text It's ready as a column value in Oracle SQL?

  1. SELECT q[It's ready] FROM dual;
  2. SELECT 'It's ready' FROM dual;
  3. SELECT "It's ready" FROM dual;
  4. SELECT q'[It's ready]' FROM dual; Correct answer.

Explanation

The alternative quote operator q'[...]' lets a character literal contain a single quotation mark without terminating the literal. The delimiters must include the q, a single quote, the opening bracket, the closing bracket, and a final single quote.

Question 15Retrieve Data using the SQL SELECT Statement

Which statement about the DESCRIBE command in Oracle SQL*Plus is true?

  1. It is a SQL*Plus command that lists a table's column names, data types, and nullability. Correct answer.
  2. It is a DML statement that returns all rows from a table.
  3. It is a SQL SELECT clause that filters rows containing null values.
  4. It is a built-in function that must appear in a SELECT list and returns one row per column.

Explanation

DESCRIBE is a SQL*Plus command, not a SQL SELECT statement, and it reports metadata about a table's columns. The output includes column names, data types, and whether null values are permitted.

Question 16Retrieve Data using the SQL SELECT Statement

You write SELECT salary * 12 AS annual_salary FROM employees;. Which clause can reference annual_salary in the same query?

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

Explanation

A column alias defined in the SELECT list can be referenced by the ORDER BY clause of the same query. It cannot be referenced in WHERE, and Oracle does not allow it in HAVING for this purpose.

Question 17Retrieve Data using the SQL SELECT Statement

What does SELECT 2 + 3 * 4 || 'X' FROM dual; return in Oracle SQL?

  1. 14
  2. 20X
  3. 14X Correct answer.
  4. 2+12X

Explanation

Multiplication has higher precedence than addition, so 3 * 4 is evaluated first and 2 is added to produce 14. Concatenation is then applied, converting the numeric result to text and appending X.

Question 18Retrieve Data using the SQL SELECT Statement

You execute SELECT DISTINCT commission_pct FROM employees; and several employees have a NULL commission_pct. What happens to NULL in the result?

  1. It raises an error because DISTINCT cannot be applied to nullable columns.
  2. It returns no row for NULL, because NULL is not a value.
  3. It returns one row for NULL, because DISTINCT treats all nulls as duplicates. Correct answer.
  4. It returns one row for each employee whose commission_pct is NULL because NULLs are never duplicates.

Explanation

For duplicate elimination, Oracle treats NULLs as equal to one another, so DISTINCT returns a single NULL row. NULL is not excluded merely because it is not an ordinary value.

Question 19Retrieve Data using the SQL SELECT Statement

Which statement executes successfully in Oracle Database when no user table is involved?

  1. SELECT 1 + 1 FROM dual; Correct answer.
  2. SELECT 1 + 1 FROM;
  3. SELECT 1 + 1;
  4. SELECT WHERE 1 + 1 FROM dual;

Explanation

Oracle SQL always requires a FROM clause, even when the query only evaluates expressions. DUAL is a one-row, one-column table owned by SYS that provides that FROM target and returns the value X in column DUMMY. Omitting FROM raises ORA-00923, so only the statement selecting from DUAL is valid.

Question 20Retrieve Data using the SQL SELECT Statement

Which clauses must appear in every Oracle SELECT statement that returns rows?

  1. SELECT only
  2. SELECT and WHERE
  3. SELECT and ORDER BY
  4. SELECT and FROM Correct answer.

Explanation

Every Oracle SELECT statement needs both a SELECT list and a FROM clause, because FROM identifies the source rows even when the source is DUAL. WHERE, GROUP BY, HAVING and ORDER BY are optional and, when present, follow the FROM clause.

Question 21Retrieve Data using the SQL SELECT Statement

A developer enters the statement select first_name, salary from employees; in SQL*Plus. What happens?

  1. The statement runs because keywords and unquoted identifiers are case-insensitive. Correct answer.
  2. The statement runs only if the table was created with lowercase names.
  3. The statement runs but displays the headings in lowercase.
  4. The statement fails because SQL keywords must be uppercase.

Explanation

Oracle SQL keywords and unquoted identifiers are not case sensitive, so a lowercase statement is treated exactly like its uppercase equivalent. Unquoted names are folded to uppercase internally, which is why headings such as FIRST_NAME appear in uppercase.

Question 22Retrieve Data using the SQL SELECT Statement

You run SELECT hire_date + 30 FROM employees;. What does each row return?

  1. An error, because a number cannot be added to a DATE value.
  2. A NUMBER value counting 30 days beyond 01-JAN-4712 BC.
  3. A DATE value 30 days after the hire date. Correct answer.
  4. A VARCHAR2 value showing the hire date followed by the text 30.

Explanation

Adding a number to a DATE advances the date by that many days, so hire_date + 30 returns a DATE exactly 30 days after the hire date. The result keeps the DATE data type and the original time-of-day component; only date minus date produces a NUMBER.

Question 23Retrieve Data using the SQL SELECT Statement

You execute SELECT 'Order ' || 250 FROM dual;. Which value is returned?

  1. A single character value containing Order || 250 literally.
  2. A single character value containing Order 250. Correct answer.
  3. The statement fails because || accepts only character operands.
  4. Two columns: the text Order and the number 250.

Explanation

The concatenation operator || joins its operands into one character value. When one operand is a character string, Oracle implicitly converts the numeric operand to a string, producing the single value Order 250 in one column of data type VARCHAR2.

Question 24Retrieve Data using the SQL SELECT Statement

You need a column alias named order, which is a reserved word. Which statement is valid?

  1. SELECT salary AS 'order' FROM employees;
  2. SELECT salary AS order FROM employees;
  3. SELECT salary AS Order Name FROM employees;
  4. SELECT salary AS "order" FROM employees; Correct answer.

Explanation

A column alias must be a single identifier, and reserved words are not legal unquoted identifiers. Wrapping the alias in double quotes makes it valid and also makes it case sensitive, so the heading appears exactly as order. Single quotes cannot delimit an alias because they introduce a character literal.

Question 25Retrieve Data using the SQL SELECT Statement

You execute SELECT SYSDATE - hire_date FROM employees;. What does each row return?

  1. A DATE value representing the elapsed time.
  2. A VARCHAR2 value containing a formatted interval.
  3. An error, because one DATE cannot be subtracted from another DATE.
  4. A NUMBER of days between the hire date and the current date. Correct answer.

Explanation

Subtracting one DATE from another returns a NUMBER equal to the difference in days, including a fractional part when a time component exists. SYSDATE always carries a time component, so the result typically includes decimals unless it is truncated or rounded.

Question 26Retrieve Data using the SQL SELECT Statement

You execute SELECT salary * 12 FROM employees; without giving an alias. Which heading appears in SQL*Plus?

  1. COLUMN1
  2. An error, because expressions in the select list need an alias.
  3. SALARY*12 Correct answer.
  4. A blank heading

Explanation

When an arithmetic expression has no alias, SQL*Plus uses the text of the expression itself as the column heading, so SALARY*12 is displayed. Aliases are optional and are needed only when you want a friendlier or reusable heading.

Question 27Retrieve Data using the SQL SELECT Statement

In SQL*Plus, you query a column that contains NULL for several rows. How does SQL*Plus display those NULL values in the result set?

  1. Nothing is printed; the column position is left blank Correct answer.
  2. The word NULL is printed in the column position
  3. The value 0 is printed in the column position
  4. The text (null) is printed in the column position

Explanation

SQL*Plus does not print placeholder text for NULL; the column position is simply left empty, so the row shows a gap. Some graphical clients such as SQL Developer can be configured to display (null), but that is a client setting rather than SQL behaviour, and NULL is never converted to zero or to the word NULL.

Question 28Retrieve Data using the SQL SELECT Statement

The EMPLOYEES table contains 107 rows. You execute the statement SELECT DISTINCT 1 FROM employees;. How many rows does the result set contain?

  1. 107 rows, one for each employee
  2. One row Correct answer.
  3. Zero rows, because a constant is not a column
  4. The statement fails because DISTINCT requires a column name

Explanation

DISTINCT eliminates duplicate rows from the result set after the select list has been evaluated, so the constant 1 produces the identical value for every employee and all duplicates collapse into a single row. The size of EMPLOYEES only determines how many duplicate rows are removed, and a literal is a perfectly valid select-list element.

Question 29Restrict and Sort Data

In an Oracle SQL query, which clause restricts the rows that are returned before any grouping or aggregation takes place?

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

Explanation

The WHERE clause filters individual rows as they are read, before GROUP BY forms groups. HAVING filters groups after aggregation, and ORDER BY only sorts the final result set.

Question 30Restrict and Sort Data

A query ends with ORDER BY last_name and specifies neither ASC nor DESC. In Oracle SQL, how are the rows ordered?

  1. Ascending, with null values first
  2. Ascending, with null values last Correct answer.
  3. Descending, with null values first
  4. The order is unpredictable unless a direction is specified

Explanation

ORDER BY defaults to ascending order. In Oracle, ascending sorts place null values last unless NULLS FIRST is specified, while descending sorts place nulls first by default.

Question 31Restrict and Sort Data

Given the predicate salary BETWEEN 3000 AND 5000, which salaries satisfy the condition?

  1. Only salaries equal to 3000 or 5000
  2. Only salaries greater than 3000 and less than 5000
  3. Salaries from 3000 to 5000, inclusive Correct answer.
  4. Salaries greater than 3000 and less than or equal to 5000

Explanation

BETWEEN is inclusive at both ends, so it is equivalent to salary >= 3000 AND salary <= 5000. Rows with exactly 3000 or exactly 5000 are returned.

Question 32Restrict and Sort Data

Consider the WHERE clause: department_id = 10 OR department_id = 20 AND salary > 5000. How does Oracle evaluate this condition?

  1. department_id = 10 AND (department_id = 20 OR salary > 5000)
  2. (department_id = 10 OR department_id = 20) AND salary > 5000
  3. department_id = 10 OR (department_id = 20 AND salary > 5000) Correct answer.
  4. department_id = (10 OR 20) AND salary > 5000

Explanation

AND has higher precedence than OR, so the salary test is evaluated only with the department_id = 20 condition. Parentheses are required to change that grouping.

Question 33Restrict and Sort Data

You need rows whose product_name begins with the two characters A_ (the letter A followed by a literal underscore). Which condition accomplishes this in Oracle SQL?

  1. product_name LIKE 'A_%'
  2. product_name LIKE 'A\_%'
  3. product_name LIKE 'A\_%' ESCAPE '\' Correct answer.
  4. product_name LIKE 'A%_'

Explanation

In a LIKE pattern, the underscore wildcard matches exactly one character. The ESCAPE clause declares a character that removes the wildcard meaning from the following character, so the pattern matches a literal underscore after A.

Question 34Restrict and Sort Data

Which condition correctly returns employees for whom commission_pct has no value?

  1. commission_pct <> NULL
  2. commission_pct IS NULL Correct answer.
  3. commission_pct IN (NULL)
  4. commission_pct = NULL

Explanation

NULL represents an unknown value, so comparisons using =, <>, or IN with NULL do not evaluate to true. IS NULL is the predicate that tests for the absence of a value.

Question 35Restrict and Sort Data

A SELECT list defines the alias annual_salary. Where can that alias be referenced directly in the same query?

  1. In the WHERE clause only
  2. In both the WHERE and ORDER BY clauses
  3. In the ORDER BY clause only Correct answer.
  4. In neither clause; aliases can only be used in a surrounding query

Explanation

Oracle evaluates WHERE before the SELECT list aliases exist, so an alias cannot be used there. ORDER BY is evaluated after the SELECT list, so it can reference a column alias.

Question 36Restrict and Sort Data

What does this query return? SELECT employee_id FROM employees WHERE department_id NOT IN (10, 20, NULL);

  1. All employees whose department_id is not 10 or 20
  2. Only employees whose department_id is null
  3. No rows at all Correct answer.
  4. All employees, because NULL is ignored in NOT IN lists

Explanation

NOT IN expands to a series of <> comparisons joined with AND. Comparing any value with NULL yields unknown, so the whole predicate is never true and no rows are returned.

Question 37Restrict and Sort Data

You execute SELECT DISTINCT department_id FROM employees ORDER BY salary;. What is the result in Oracle Database?

  1. Duplicate department_id values are returned, sorted by salary
  2. It raises ORA-01791: not a SELECTed expression Correct answer.
  3. The ORDER BY clause is silently ignored
  4. Rows are sorted by salary within each department

Explanation

When SELECT DISTINCT is used, Oracle requires each ORDER BY expression to appear in the select list. Since salary is not selected, the statement fails with ORA-01791 rather than sorting by an invisible column.

Question 38Restrict and Sort Data

Which clause returns only the first five rows of a sorted result set in Oracle Database 12c and later?

  1. GROUP BY ... HAVING ROWNUM <= 5
  2. WHERE ROWNUM <= 5
  3. ORDER BY ... FETCH FIRST 5 ROWS ONLY Correct answer.
  4. LIMIT 5

Explanation

Oracle 12c introduced the row limiting clause FETCH FIRST n ROWS ONLY, which is applied after ORDER BY. WHERE ROWNUM <= 5 is evaluated before sorting, and LIMIT is not Oracle SQL syntax.

Question 39Restrict and Sort Data

In an Oracle Database WHERE clause, which operator is a valid synonym for the not-equal comparison operator <>?

  1. ^= Correct answer.
  2. =!
  3. =>
  4. <>=

Explanation

Oracle SQL accepts three spellings of the inequality comparison: <>, != and ^=. The other forms shown are not comparison operators in Oracle SQL; => belongs to named-parameter notation and =! is a syntax error.

Question 40Restrict and Sort Data

The commission_pct column is NULL for several employees. A query ends with ORDER BY commission_pct ASC and specifies no NULLS clause. Where does Oracle place the NULL values in the result?

  1. After every non-NULL value, because ascending order defaults to NULLS LAST Correct answer.
  2. The query fails because NULL values cannot be sorted without a NULLS clause
  3. Before every non-NULL value, because NULL sorts lowest
  4. Interleaved with non-NULL values according to rowid order

Explanation

In Oracle, ascending order treats NULLs as larger than any non-NULL value, so the default for ASC is NULLS LAST. Descending order uses the opposite default, NULLS FIRST. A NULLS clause is only needed to override those defaults.

Question 41Restrict and Sort Data

For rows where salary is not NULL, which salaries satisfy the predicate salary NOT BETWEEN 3000 AND 5000?

  1. Salaries strictly less than 3000 or strictly greater than 5000 Correct answer.
  2. Salaries less than or equal to 3000 or greater than or equal to 5000
  3. Only salaries exactly equal to 3000 or 5000
  4. Salaries from 3000 through 5000 inclusive

Explanation

BETWEEN is inclusive at both bounds, so NOT BETWEEN negates that inclusive range and keeps values outside it: below 3000 or above 5000, with the endpoint values themselves excluded.

Question 42Restrict and Sort Data

Consider this statement: SELECT last_name, salary FROM employees ORDER BY 2 DESC;. What does the ORDER BY clause do?

  1. It sorts by the constant value 2, so the row order is effectively unchanged
  2. It sorts by salary, the second expression of the select list, from highest to lowest Correct answer.
  3. It raises ORA-01785 because numeric literals are not permitted in ORDER BY
  4. It sorts by a table column literally named "2" in employees

Explanation

A bare integer in ORDER BY is a positional reference into the select list, not a constant or a column name. Here position 2 is salary, and DESC reverses the order so the highest salaries appear first.

Question 43Restrict and Sort Data

You run: SELECT last_name, salary FROM employees ORDER BY 3;. What is the outcome?

  1. Rows are returned in an unspecified order because the position is ignored
  2. The statement fails with ORA-01785 because 3 does not correspond to any select-list expression Correct answer.
  3. The statement sorts by the third column of the employees table, hire_date
  4. The statement sorts by salary because the position is reduced to the last valid expression

Explanation

Positional ORDER BY values must refer to an expression that exists in the select list. This select list has only two expressions, so position 3 is invalid and Oracle raises ORA-01785.

Question 44Restrict and Sort Data

What does this query return? SELECT employee_id FROM employees WHERE department_id IN (10, 20, NULL);

  1. Only employees whose department_id is NULL
  2. No rows, because the NULL in the list makes the whole condition unknown
  3. Employees in department 10 or 20; the NULL in the list does not block a match Correct answer.
  4. All employees, because NULL in a list matches any value

Explanation

For a positive IN test, Oracle evaluates each list element and returns the row when any comparison is TRUE. A NULL element yields UNKNOWN rather than TRUE, so it neither matches rows nor suppresses the rows matched by 10 and 20.

Question 45Restrict and Sort Data

The query SELECT employee_id, commission_pct FROM employees ORDER BY commission_pct DESC NULLS LAST; is executed. How are the rows arranged?

  1. NULL commissions appear first, then commissions from highest to lowest
  2. The statement is rejected because NULLS LAST cannot be combined with DESC
  3. NULL commissions appear last, then commissions from lowest to highest
  4. Non-NULL commissions appear from highest to lowest, and NULL commissions are placed after them Correct answer.

Explanation

DESC alone determines the direction of the non-NULL values, so the largest commissions come first. The explicit NULLS LAST clause overrides the DESC default of NULLS FIRST and places the NULL rows at the end.

Question 46Restrict and Sort Data

Which ORDER BY clause lists employees starting with the shortest last_name value and ending with the longest?

  1. ORDER BY SHORTEST(last_name)
  2. ORDER BY last_name ASC LENGTH
  3. ORDER BY LENGTH(last_name) Correct answer.
  4. ORDER BY last_name LENGTH

Explanation

Sorting by character length requires a SQL function applied to the column, and LENGTH returns the number of characters. Oracle evaluates the expression for each row and orders by the resulting numeric values.

Question 47Restrict and Sort Data

You must list employees in department 10 first, then all remaining employees, and within each of those two groups sort by salary from highest to lowest. Which ORDER BY clause achieves this?

  1. ORDER BY department_id = 10, salary DESC
  2. ORDER BY salary DESC, CASE WHEN department_id = 10 THEN 0 ELSE 1 END
  3. ORDER BY department_id DESC, salary DESC
  4. ORDER BY CASE WHEN department_id = 10 THEN 0 ELSE 1 END, salary DESC Correct answer.

Explanation

A CASE expression maps department 10 to the lower sort key 0 and every other department to 1, so department 10 rows lead regardless of department_id values. The second sort key, salary DESC, then orders rows inside each group.

Question 48Restrict and Sort Data

The last_name column is VARCHAR2 and stores names in mixed case. With the default binary sort, which ORDER BY clause produces a case-insensitive alphabetical listing?

  1. ORDER BY UPPER(last_name) Correct answer.
  2. ORDER BY last_name NOCASE
  3. ORDER BY LOWER(last_name) DESC
  4. ORDER BY last_name COLLATE NOCASE

Explanation

Oracle's default binary sort compares character codes, so uppercase letters sort before lowercase letters. Applying UPPER to the column normalizes case and yields a case-insensitive alphabetical order; LOWER would do the same but its direction must remain ascending.

Question 49Single-Row Functions

A query must display the value stored in the LAST_NAME column so that only the first character is uppercase and every other character is lowercase, regardless of the case used when the row was inserted. Which single-row function returns that result?

  1. LPAD(last_name, 15, '*')
  2. INITCAP(last_name) Correct answer.
  3. UPPER(last_name)
  4. LOWER(last_name)

Explanation

INITCAP converts the first character of each word to uppercase and forces the remaining characters to lowercase, which matches the requested display. UPPER and LOWER change every character to one case, and LPAD only pads the value to a fixed length with a fill character.

Question 50Single-Row Functions

The COMMISSION_PCT column is nullable. A report must show 0 for employees whose commission is missing and the stored commission value for all others. Which expression produces that output?

  1. NVL(commission_pct, 0) Correct answer.
  2. NULLIF(commission_pct, 0)
  3. DECODE(commission_pct, NULL, commission_pct, 0)
  4. COALESCE(0, commission_pct)

Explanation

NVL returns its second argument when the first argument is null and otherwise returns the first argument, so missing commissions become 0. COALESCE(0, commission_pct) always returns 0 because the first argument is already non-null, and the other options reverse or compare the values instead of substituting for null.