Oracle 1Z0-071: SQL SELECT

Topic snapshot

Sample questions

These questions are original IT Mastery practice items aligned to this topic area. They are designed for self-assessment and are not official exam questions.

Question 1

Topic: Retrieve Data by Using SQL SELECT Statements

A report must return every row from EMPLOYEES with exactly two columns:

• EMPLOYEE_ID
• CONTACT, displayed as Last Name, First Name - Phone: value

If PHONE_NUMBER is NULL, the display must show N/A instead of the phone number.

Which query best meets the requirement?

Options:

• A. ```sql SELECT employee_id, last_name || ‘, ’ || first_name || ’ - Phone: ’ || phone_number AS contact FROM employees WHERE phone_number IS NOT NULL;

- B. ```sql
SELECT employee_id,
       NVL(last_name || ', ' || first_name || ' - Phone: ' || phone_number, 'N/A') AS contact
FROM employees;

• C. ```sql SELECT employee_id || ’ - ’ || last_name || ‘, ’ || first_name || ’ - Phone: ’ || NVL(phone_number, ‘N/A’) AS contact FROM employees;

- D. ```sql
SELECT employee_id,
       last_name || ', ' || first_name || ' - Phone: ' || NVL(phone_number, 'N/A') AS contact
FROM employees;

Best answer: D

Explanation: The best query separates projection from formatting: EMPLOYEE_ID is returned as its own column, and CONTACT is a formatted display expression. In Oracle, NVL must be applied to PHONE_NUMBER itself to substitute N/A when that value is NULL.

This tests the difference between what columns are projected and how one projected column is formatted. The requirement asks for two columns only: the original EMPLOYEE_ID column and one formatted text column named CONTACT. In Oracle, string literals use single quotes and concatenation uses ||.

To preserve all rows and replace only a missing phone number, apply NVL to PHONE_NUMBER inside the concatenation:

• keep employee_id as its own selected column
• build CONTACT with last_name, first_name, literal text, and NVL(phone_number, 'N/A')
• do not filter out rows just because PHONE_NUMBER is NULL

A close distractor wraps NVL around the whole concatenated expression, but in Oracle a NULL concatenated with text does not make the full display expression become NULL, so N/A would not appear as required.

• Whole-expression NVL fails because NVL must replace only the missing phone value, not the entire concatenated string.
• One-column output fails because concatenating EMPLOYEE_ID into the display text removes the required separate EMPLOYEE_ID column.
• Filtering NULL phones fails because the report must return every row and display N/A, not exclude employees with missing phone numbers.

Question 2

Topic: Retrieve Data by Using SQL SELECT Statements

Table employees contains the columns department_id and job_id.

Requirement: return each distinct department_id/job_id combination once, projecting department_id first and job_id second. Use the column aliases Dept and Job.

Which statement satisfies the requirement?

Options:

• A. SELECT DISTINCT department_id, job_id Job FROM employees;

• B. SELECT DISTINCT department_id Dept, job_id Job FROM employees;

• C. SELECT DISTINCT job_id Job, department_id Dept FROM employees;

• D. SELECT department_id Dept, DISTINCT job_id Job FROM employees;

Best answer: B

Explanation: In Oracle SQL, the select list controls both the projected columns and their order. DISTINCT must appear immediately after SELECT, and each alias must be attached to its projected expression.

The core rule is that Oracle reads the projection from left to right in the SELECT list, so column order in the result is determined by the order of expressions you write. When you need unique rows, DISTINCT is written once immediately after SELECT, and it applies to the full combination of selected columns, not to a single column.

Here, the requirement is:

• project department_id first
• project job_id second
• return only distinct combinations
• use aliases Dept and Job

So the valid statement must place DISTINCT right after SELECT and list department_id Dept, job_id Job in that exact order. Reversing the columns, omitting an alias, or misplacing DISTINCT does not satisfy the stated requirement.

• Misplaced DISTINCT fails because Oracle does not allow DISTINCT in the middle of the select list.
• Reversed projection fails because job_id appears before department_id, which changes the requested column order.
• Missing alias fails because the first projected column does not use the required alias Dept.

Question 3

Topic: Retrieve Data by Using SQL SELECT Statements

In Oracle, a query uses SELECT DISTINCT department_id, job_id FROM employees. The result still shows the same department_id on multiple rows. Which statement explains this behavior?

Options:

• A. DISTINCT requires an ORDER BY clause before duplicates can be removed.

• B. DISTINCT removes duplicate rows only when all selected columns match exactly.

• C. DISTINCT works only when the select list contains one column.

• D. DISTINCT removes duplicates from the first selected column only.

Best answer: B

Explanation: DISTINCT in Oracle evaluates the full select-list combination. If two rows share the same department_id but have different job_id values, they are still distinct rows, so both are returned.

The key rule is that DISTINCT compares the entire row produced by the SELECT list, not a single column by itself. In SELECT DISTINCT department_id, job_id FROM employees, Oracle keeps one row for each unique (department_id, job_id) pair. That means repeated department_id values can still appear if they are paired with different job_id values.

If you want unique departments only, select just department_id with DISTINCT. If you need one row per department while also showing other data, you usually need grouping or another query design. The common mistake is assuming DISTINCT acts only on the first selected column.

• First-column only is wrong because Oracle does not limit duplicate checking to the first expression in the select list.
• Single-column only is wrong because DISTINCT can be used with multiple selected columns and expressions.
• Needs sorting is wrong because ORDER BY affects presentation order, not whether duplicate rows are eliminated.

Question 4

Topic: Retrieve Data by Using SQL SELECT Statements

Examine the Oracle SQL statement:

SELECT first_name || ' ' || last_name || ' earns ' || salary AS "EMPLOYEE DETAILS"
FROM employees;

Which statement best describes the result of this query?

Options:

• A. It returns four columns: first name, last name, the literal text, and salary.

• B. It permanently renames last_name to EMPLOYEE DETAILS.

• C. It returns one derived column headed EMPLOYEE DETAILS.

• D. It fails unless salary is converted with TO_CHAR first.

Best answer: C

Explanation: In Oracle, each comma-separated item in the SELECT list is one projected column. Here there is only one item: a concatenated expression that combines columns and literals, displayed with the alias EMPLOYEE DETAILS.

The key idea is projection versus display formatting. Oracle projects one column for each expression in the SELECT list, and this statement has only one expression because everything is joined with the concatenation operator || and there are no commas separating additional items.

The literals such as ' ' and ' earns ' are just pieces of the displayed value. They do not create extra columns. The alias EMPLOYEE DETAILS changes only the output heading for that derived expression. Oracle can also implicitly convert salary to character data during concatenation, although TO_CHAR is often used when you need specific numeric formatting.

So this query returns one formatted text column per row, not multiple projected columns.

• Multiple columns idea fails because literals and concatenated parts inside one expression do not become separate projected columns.
• Explicit conversion required fails because Oracle allows implicit conversion when a number is concatenated with character data.
• Permanent rename idea fails because a column alias affects only the query output heading, not the table definition.

Question 5

Topic: Retrieve Data by Using SQL SELECT Statements

The EMPLOYEES table has columns LAST_NAME, SALARY, and COMMISSION_PCT. You need a query that returns all rows and:

• builds EMP_INFO as Last_name - Commission: <value>
• shows the literal No commission when COMMISSION_PCT is NULL
• returns ANNUAL_SALARY as SALARY multiplied by 12

Which SQL statement meets the requirements?

Options:

• A. SELECT CONCAT(last_name, ’ - Commission: ‘, NVL(TO_CHAR(commission_pct), ‘No commission’)) AS emp_info, salary * 12 AS annual_salary FROM employees;

• B. SELECT last_name || ’ - Commission: ’ || NVL(TO_CHAR(commission_pct), ‘No commission’) AS emp_info, salary * 12 AS annual_salary FROM employees;

• C. SELECT last_name + ’ - Commission: ’ + NVL(TO_CHAR(commission_pct), ‘No commission’) AS emp_info, salary * 12 AS annual_salary FROM employees;

• D. SELECT last_name || ’ - Commission: ’ || NVL(commission_pct, ‘No commission’) AS emp_info, salary || 12 AS annual_salary FROM employees;

Best answer: B

Explanation: The correct query must use Oracle string concatenation with ||, not +, and must keep data types compatible when replacing a NULL numeric commission with text. Converting COMMISSION_PCT to character before NVL allows the text literal to be used safely, while salary * 12 keeps the annual salary numeric.

This item tests how Oracle SELECT lists combine literals, expressions, aliases, and concatenation. In Oracle SQL, character strings are joined with ||, and arithmetic expressions such as salary * 12 remain numeric expressions that can be given a column alias.

Because COMMISSION_PCT is numeric, replacing a NULL with the text No commission requires both possible results to be character data. That is why TO_CHAR(commission_pct) is needed before NVL. The derived column is then built by concatenating LAST_NAME, the literal text, and the converted commission value.

The closest distractor uses NVL directly on the numeric column with a character literal and also concatenates salary with 12, which produces the wrong result type and value.

• CONCAT misuse fails because Oracle CONCAT accepts only two arguments, not three pieces in one call.
• Wrong operator fails because + is arithmetic addition, not Oracle string concatenation.
• Type confusion fails because NVL(commission_pct, 'No commission') mixes numeric and character results, and salary || 12 concatenates text instead of multiplying.

Question 6

Topic: Retrieve Data by Using SQL SELECT Statements

A report against the EMPLOYEES table must return only EMPLOYEE_ID and LAST_NAME for employees in department 50, sorted by LAST_NAME. Which SQL statement best meets the requirement?

Options:

• A. SELECT employee_id, last_name, department_id FROM employees WHERE department_id = 50 ORDER BY last_name;

• B. SELECT employee_id, last_name FROM employees WHERE department_id >= 50 ORDER BY last_name;

• C. SELECT * FROM employees WHERE department_id = 50 ORDER BY last_name;

• D. SELECT employee_id, last_name FROM employees WHERE department_id = 50 ORDER BY last_name;

Best answer: D

Explanation: When a task requires a limited projection, list only the needed columns instead of using SELECT *. The correct statement returns just EMPLOYEE_ID and LAST_NAME, filters to department 50, and orders the result by LAST_NAME.

This question tests projection in a SELECT statement. In Oracle SQL, SELECT * returns every column from the table, which is inappropriate when the requirement explicitly says to return only specific columns. The best solution is to name the needed columns directly in the select list.

Here, the query must do three things:

• return employees only from department 50
• display only EMPLOYEE_ID and LAST_NAME
• sort by LAST_NAME

A statement that includes extra columns does not satisfy the limited-projection requirement, even if the filter and sort are correct. A statement with the wrong filter returns the wrong row set. The key takeaway is that projection should match the report requirement exactly, not broadly.

• Using SELECT * fails because it returns all columns, not just the two required columns.
• Including DEPARTMENT_ID fails because the task explicitly asks for only EMPLOYEE_ID and LAST_NAME.
• Using department_id >= 50 fails because it returns rows from departments other than 50.

Question 7

Topic: Retrieve Data by Using SQL SELECT Statements

The employees table has columns employee_id and salary. You need a query that returns employee_id and annual salary as annual_sal using salary * 12, keeps only rows where annual salary is greater than 100,000, and reuses the alias later in the statement only where Oracle allows it. Which statement is best?

Options:

• A. SELECT employee_id, salary * 12 AS annual_sal FROM employees WHERE annual_sal > 100000 ORDER BY annual_sal DESC

• B. SELECT employee_id, salary * 12 AS annual_sal, annual_sal + 5000 AS adjusted_sal FROM employees WHERE salary * 12 > 100000 ORDER BY annual_sal DESC

• C. SELECT employee_id, salary * 12 AS annual_sal FROM employees WHERE salary * 12 > 100000 ORDER BY salary * 12 DESC

• D. SELECT employee_id, salary * 12 AS annual_sal FROM employees WHERE salary * 12 > 100000 ORDER BY annual_sal DESC

Best answer: D

Explanation: In Oracle, a column alias defined in the SELECT list can be reused in ORDER BY within the same query block. It cannot be used in the WHERE clause, so the filter must repeat the expression while the sort can use annual_sal.

The core rule is alias visibility within a single query block. Oracle assigns the select-list alias after the WHERE clause is processed, so annual_sal is not available for filtering there. However, Oracle does allow a select-list alias to be referenced in ORDER BY, which is why the filter uses salary * 12 and the sort uses annual_sal.

A good way to remember it is:

• Use the full expression in WHERE
• Use the alias in ORDER BY
• Do not reuse the alias in another select-list expression in the same query block

The closest distractor sorts correctly, but it does not meet the stated requirement to reuse the alias where Oracle permits it.

• Alias in WHERE fails because annual_sal is not visible to the WHERE clause in the same query block.
• Repeat expression in ORDER BY can return the right rows and order, but it does not satisfy the requirement to reuse the alias where allowed.
• Alias in SELECT list fails because a select-list alias cannot be referenced by another select-list expression in the same query block.

Question 8

Topic: Retrieve Data by Using SQL SELECT Statements

A developer runs this query to display a full name, a calculated annual salary, and user-friendly headings, but Oracle returns ORA-00923: FROM keyword not found where expected.

SELECT employee_id,
       first_name || ' ' || last_name Employee Name,
       salary * 12 Annual Salary,
       'USD' currency
FROM employees;

What is the best fix?

Options:

• A. Convert salary * 12 to TO_CHAR(salary * 12).

• B. Enclose the aliases with spaces in single quotes.

• C. Enclose the aliases with spaces in double quotes.

• D. Replace || with CONCAT in the name expression.

Best answer: C

Explanation: The query fails because Employee Name and Annual Salary are written as unquoted aliases containing spaces. In Oracle, single quotes create string literals, while double quotes are required when an alias includes spaces or other special characters.

This is a SELECT-list aliasing issue, not a problem with concatenation, arithmetic, or literals. In Oracle SQL, these are all valid in the SELECT list:

• expressions such as salary * 12
• concatenation such as first_name || ' ' || last_name
• string literals such as 'USD'

The failure occurs because unquoted column aliases must be a single token. When an alias contains a space, Oracle treats the second word as unexpected syntax unless the full alias is enclosed in double quotes, such as "Employee Name" and "Annual Salary".

The key takeaway is that the expressions are valid; only the spaced aliases need quoting.

• Single quotes misuse fails because single quotes define character literals, not column aliases.
• Concatenation operator is already valid; || is the normal Oracle concatenation operator.
• Character conversion is unnecessary because arithmetic expressions can appear directly in the SELECT list.

Question 9

Topic: Retrieve Data by Using SQL SELECT Statements

A developer must create a view for the HR team that should expose only EMPLOYEE_ID, LAST_NAME, and DEPARTMENT_NAME.

The current statement fails with a duplicate column name error:

CREATE VIEW emp_dept_v AS
SELECT *
FROM employees e
JOIN departments d
  ON e.department_id = d.department_id;

What is the best fix?

Options:

• A. Change the join to NATURAL JOIN

• B. Add DISTINCT to the SELECT * query

• C. Keep SELECT * and specify three view column aliases

• D. List only e.employee_id, e.last_name, and d.department_name

Best answer: D

Explanation: The task requires a limited projection, so SELECT * is the wrong choice. Listing only the needed columns both matches the view requirement and avoids duplicate column names that can occur when joining tables in a view definition.

This is a projection problem, not a duplicate-row problem. When a view should expose only a few columns, the correct fix is to replace SELECT * with an explicit column list. In a join, SELECT * pulls every column from both tables, which can introduce duplicate column names into the view definition and also returns far more data than the requirement allows.

Using explicit columns helps because it:

• returns only the columns the user needs
• avoids duplicate-name issues in joined views
• keeps the view stable if base tables later gain new columns

The closest distractors try to preserve SELECT *, but the real issue is that the task calls for a limited projection.

• DISTINCT confusion removes duplicate rows, not duplicate column definitions or unnecessary columns.
• NATURAL JOIN misuse changes join behavior based on same-named columns and still does not enforce the required limited projection.
• View alias shortcut fails because three aliases cannot rename a much larger SELECT * column list.

Question 10

Topic: Retrieve Data by Using SQL SELECT Statements

Which Oracle SQL statement correctly displays each employee last_name prefixed by the fixed text Employee: in a single output column?

Options:

• A. SELECT 'Employee: ' || last_name FROM employees;

• B. SELECT 'Employee: ' + last_name FROM employees;

• C. SELECT 'Employee: ' AS last_name FROM employees;

• D. SELECT "Employee: " || last_name FROM employees;

Best answer: A

Explanation: Oracle string literals use single quotes, and character values are combined with the concatenation operator ||. To show fixed text together with a column value in one result column, place the literal in the select list and concatenate it to the column.

The core rule is that Oracle treats text enclosed in single quotes as a character literal. When you want fixed text and a column value to appear together in one displayed column, use the concatenation operator ||.

In this case, the correct pattern is:

• put the fixed text in single quotes
• concatenate it with the column
• select from the target table

So SELECT 'Employee: ' || last_name FROM employees; returns values such as Employee: King.

Using double quotes does not create a string literal in Oracle; double quotes are for quoted identifiers. Using + is not the Oracle operator for character concatenation, and selecting only the literal does not include the employee name.

• Double quotes misuse fails because "Employee: " is treated as an identifier, not a character literal.
• Wrong operator fails because Oracle uses || for string concatenation, not +.
• Missing column value fails because selecting only the literal text does not display last_name alongside it.

Continue with full practice

Use the Oracle 1Z0-071 Practice Test page for the full IT Mastery route, mixed-topic practice, timed mock exams, explanations, and web/mobile app access.

Try Oracle 1Z0-071 on Web View Oracle 1Z0-071 Practice Test

Related focused pages

• Free Oracle 1Z0-071 Full-Length Practice Exam
• Relational Database Concepts
• Restrict and Sort Data
• Single-Row Functions
• Conversion and CASE
• Group Functions
• Display Data from Multiple Tables
• Use Subqueries to Solve Queries
• Use Set Operators
• Manage Tables by Using DML Statements
• Indexes and Sequences
• DDL Tables and Relationships
• Manage Views
• Control User Access
• Data Dictionary Views
• Manage Data in Different Time Zones

Free review resource

Read the Oracle 1Z0-071 Cheat Sheet on Tech Exam Lexicon, then return to IT Mastery for timed practice.