Oracle 1Z0-071: Display Data from Multiple Tables

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: Display Data from Multiple Tables

Given these tables:

employees(employee_id, last_name, department_id)
departments(department_id, department_name)

Which query returns each employee’s last_name and matching department_name, excludes unmatched rows from either table, and sorts by last_name?

Options:

• A. SELECT e.last_name, d.department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id ORDER BY e.last_name

• B. SELECT e.last_name, d.department_name FROM employees e CROSS JOIN departments d ORDER BY e.last_name

• C. SELECT e.last_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id ORDER BY e.last_name

• D. SELECT e.last_name, d.department_name FROM employees e JOIN departments d ON e.employee_id = d.department_id ORDER BY e.last_name

Best answer: C

Explanation: The required result set includes only rows where the related columns match in both tables. An inner join on department_id does exactly that and then orders the output by last_name.

An inner join returns only the rows that satisfy the join condition in both tables. Here, the relationship is employees.department_id to departments.department_id, so the join must be written on those columns. That excludes employees with no matching department and also excludes departments with no matching employees.

Using a left outer join would keep unmatched rows from employees, which breaks the requirement. A cross join produces every employee/department combination, and joining employee_id to department_id uses the wrong columns, so it does not represent the table relationship.

For matching rows from two related tables, use JOIN ... ON with the correct foreign-key-to-primary-key columns.

• Left join mismatch keeps employees even when no department row matches, so it returns extra rows.
• Cross join error creates a Cartesian product instead of matching related rows.
• Wrong join columns compares unrelated keys, so the result set does not reflect the table relationship.

Question 2

Topic: Display Data from Multiple Tables

Tables EMPLOYEES and DEPARTMENTS are related by department_id.

Which statement correctly returns employee last names and department names only for rows with matching department_id values, using ANSI inner join syntax?

Options:

• A. SELECT e.last_name, d.department_name FROM employees e INNER JOIN departments d WHERE e.department_id = d.department_id

• B. SELECT e.last_name, d.department_name FROM employees e, departments d INNER JOIN e.department_id = d.department_id

• C. SELECT e.last_name, d.department_name FROM employees e ON e.department_id = d.department_id INNER JOIN departments d

• D. SELECT e.last_name, d.department_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id

Best answer: D

Explanation: An ANSI inner join in Oracle uses FROM table1 INNER JOIN table2 ON condition. The ON clause supplies the join condition, so only rows with matching department_id values from both tables are returned.

The core rule is ANSI join clause placement. In Oracle SQL, an inner join is written by naming the first table, adding INNER JOIN and the second table, then supplying the join condition in an ON clause. That join condition compares the related columns, such as e.department_id = d.department_id.

Using WHERE instead of ON with INNER JOIN is not valid ANSI join syntax. Likewise, ON cannot appear before INNER JOIN, and INNER JOIN must join tables, not a comparison expression.

For matching rows from two related tables, the correct pattern is table alias INNER JOIN table alias ON join_condition.

• Missing ON clause fails because ANSI INNER JOIN requires ON or USING, not a join condition placed only in WHERE.
• Wrong clause order fails because ON must come after the joined table, not before INNER JOIN.
• Invalid join target fails because INNER JOIN must reference a table or view, not a boolean comparison.

Question 3

Topic: Display Data from Multiple Tables

A developer changed an existing outer join and departments with no sales representatives disappeared from the result.

You must return:

• every row from departments
• employees.last_name only for employees whose job_id is SA_REP
• one row with NULL employee columns when a department has no sales representatives

Which query best satisfies the requirement?

Options:

• A. SELECT d.department_name, e.last_name FROM departments d LEFT OUTER JOIN employees e ON d.department_id = e.department_id AND e.job_id = ‘SA_REP’ ORDER BY d.department_name, e.last_name

• B. SELECT d.department_name, e.last_name FROM departments d LEFT OUTER JOIN employees e ON d.department_id = e.department_id WHERE e.job_id = ‘SA_REP’ ORDER BY d.department_name, e.last_name

• C. SELECT d.department_name, e.last_name FROM departments d JOIN employees e ON d.department_id = e.department_id AND e.job_id = ‘SA_REP’ ORDER BY d.department_name, e.last_name

• D. SELECT d.department_name, e.last_name FROM departments d LEFT OUTER JOIN employees e ON d.department_id = e.department_id WHERE e.job_id = ‘SA_REP’ OR e.department_id IS NULL ORDER BY d.department_name, e.last_name

Best answer: A

Explanation: With a LEFT OUTER JOIN, filters on the optional table must stay in the ON clause when you still need unmatched left-side rows. Putting e.job_id = 'SA_REP' in ON keeps all departments and returns NULL employee values when no sales representative matches.

The core concept is the difference between ON and WHERE in an outer join. Oracle applies the join condition first; if no row from employees satisfies the ON clause, the LEFT OUTER JOIN still returns the department with NULL values for employee columns. After that, the WHERE clause filters the joined result.

• Put the department match and the job_id = 'SA_REP' condition together in ON.
• That allows only sales representatives to match.
• Departments with no qualifying employee are still preserved as null-extended rows.

The common near-miss is keeping LEFT OUTER JOIN but moving the employee filter into WHERE, which removes the preserved rows.

• Filter after join fails because a WHERE condition on employees.job_id removes the null-extended rows created by the outer join.
• Inner join instead fails because an inner join never preserves departments that have no matching sales representative.
• OR ... IS NULL patch only keeps departments with no employees at all; it still loses departments whose employees exist but none are SA_REP.

Question 4

Topic: Display Data from Multiple Tables

Consider this Oracle query:

SELECT e.employee_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
  ON e.department_id = d.department_id
WHERE d.location_id = 1700;

The query no longer returns employees with no matching department. Why?

Options:

• A. Adding any filter after an outer join always causes a Cartesian product.

• B. The department filter must be written in a HAVING clause.

• C. The WHERE filter on the outer-joined table removes the null-extended rows.

• D. A LEFT OUTER JOIN preserves unmatched rows only when USING is used.

Best answer: C

Explanation: A left outer join preserves unmatched rows by returning NULL for columns from the right-side table. When you then filter that right-side table in the WHERE clause, those NULL-extended rows fail the condition and are removed.

The core concept is that an outer join preserves unmatched rows only until later filtering removes them. In this query, employees without a matching department are returned with NULL values for d.department_name and d.location_id. The condition d.location_id = 1700 is evaluated in the WHERE clause after the join result is formed, so rows where d.location_id is NULL do not satisfy the predicate and are discarded.

To keep unmatched employees while limiting matched departments to location 1700, place that filter in the ON clause of the outer join. That way, the join condition controls which department rows match, while still preserving employees that have no department row at all. The key takeaway is that a WHERE predicate on the null-supplying side can make an outer join behave like an inner join.

• USING confusion fails because ON and USING are both valid join syntaxes; neither determines row preservation by itself.
• Cartesian product confusion fails because a Cartesian product comes from missing join conditions, not from adding a filter to an outer join.
• HAVING misuse fails because HAVING filters groups after aggregation, while this query is filtering joined rows.

Question 5

Topic: Display Data from Multiple Tables

Given these tables:

EMPLOYEES(employee_id, last_name, department_id)
DEPARTMENTS(department_id, department_name)

A report must display each employee’s last_name and matching department_name only when EMPLOYEES.department_id matches DEPARTMENTS.department_id. Employees with NULL department_id values and departments without employees must not appear.

Which query best satisfies this requirement?

Options:

• A. ```sql SELECT e.last_name, d.department_name FROM employees e, departments d WHERE e.department_id IS NOT NULL;

- B. ```sql
SELECT e.last_name, d.department_name
FROM employees e
JOIN departments d
  ON e.employee_id = d.department_id;

• C. ```sql SELECT e.last_name, d.department_name FROM employees e LEFT OUTER JOIN departments d ON e.department_id = d.department_id;

- D. ```sql
SELECT e.last_name, d.department_name
FROM employees e
JOIN departments d
  ON e.department_id = d.department_id;

Best answer: D

Explanation: The requirement is for matched rows only, so an inner join is the correct Oracle SQL rule. JOIN ... ON e.department_id = d.department_id returns only employees whose department exists in DEPARTMENTS, excluding NULL department IDs and unmatched departments.

This is a standard inner-join requirement. In Oracle SQL, an inner join returns only rows that satisfy the join condition, so matching EMPLOYEES.department_id to DEPARTMENTS.department_id produces exactly the employees who belong to an existing department.

A correct inner join does two things here:

• matches the related rows between the two tables
• excludes employees with NULL department_id values because NULL does not equal any department ID
• excludes departments that have no matching employee row

That fits the reporting requirement precisely. The key takeaway is that when you need only matching rows from both tables, use an inner join with the proper join columns in the ON clause.

• Outer join mismatch: the left outer join keeps all employees, so employees without a matching department could still appear with a null department name.
• Missing join condition: the old comma-style FROM list without linking the tables creates a Cartesian product among qualifying employee rows.
• Wrong columns joined: matching employee_id to department_id uses unrelated columns, so it does not represent the stated relationship.

Question 6

Topic: Display Data from Multiple Tables

The employees table has columns employee_id, last_name, and manager_id. manager_id stores the employee_id of that employee’s manager in the same table.

Complete this query so the first selected name is the employee and the second is that employee’s manager. Include only rows that have a matching manager.

SELECT e.last_name, m.last_name
FROM ...

Options:

• A. employees e JOIN employees m ON e.manager_id = m.employee_id

• B. employees e JOIN employees e ON e.manager_id = e.employee_id

• C. employees JOIN employees ON manager_id = employee_id

• D. employees e JOIN employees m ON e.employee_id = m.manager_id

Best answer: A

Explanation: A self join requires different aliases for each logical role of the same table. Here, one alias must represent the employee row and the other the manager row, with the join condition e.manager_id = m.employee_id so the employee name appears first and the manager name second.

When the same table appears more than once in a query, Oracle SQL uses table aliases to distinguish the two roles. In this case, e should represent the employee row and m should represent the manager row from the same employees table. The correct join matches the employee’s manager_id to the manager’s employee_id, and an inner join returns only rows where that manager row exists.

So the logic is:

• e = employee row
• m = manager row
• join on e.manager_id = m.employee_id

Using unqualified column names in a self join is ambiguous, and reusing the same alias for both table instances is invalid. Reversing the join condition swaps the employee/manager roles in the selected columns.

• Same alias twice fails because one query block cannot use the same table alias for both instances of employees.
• No aliases used fails because the self join needs qualified column references to distinguish which copy of the table each column comes from.
• Reversed relationship fails because it makes the first selected alias represent the manager row rather than the employee row required by the stem.

Question 7

Topic: Display Data from Multiple Tables

A developer must assign each employee to a salary grade. JOB_GRADES contains one row per salary range with columns GRADE_LEVEL, LOWEST_SAL, and HIGHEST_SAL.

This query returns only a few employees, even though every employee salary falls into exactly one grade range:

SELECT e.last_name, e.salary, j.grade_level
FROM employees e
JOIN job_grades j
  ON e.salary = j.lowest_sal;

What is the best fix?

Options:

• A. Change the join to LEFT OUTER JOIN but keep e.salary = j.lowest_sal

• B. Replace the join condition with e.salary BETWEEN j.lowest_sal AND j.highest_sal

• C. Use a Cartesian join and filter with e.salary >= j.lowest_sal

• D. Add DISTINCT to the SELECT list

Best answer: B

Explanation: JOB_GRADES stores ranges, not exact salary keys. An equality join matches only salaries that are exactly equal to LOWEST_SAL, so most employees are excluded. The correct fix is a non-equi join that tests whether each salary falls within the grade range.

Use a non-equi join when one table stores boundary values and the other table has a value that must fall inside that range. Here, employees.salary must be matched to the row in job_grades whose lowest_sal and highest_sal bracket the salary. Equality joins are appropriate for exact key matches, such as department_id to department_id, but not for range lookups.

SELECT e.last_name, e.salary, j.grade_level
FROM employees e
JOIN job_grades j
  ON e.salary BETWEEN j.lowest_sal AND j.highest_sal;

Changing join type or removing duplicates does not fix the matching rule; the key issue is that the relationship is range-based, not equality-based.

• Keeping equality and switching to an outer join still compares salary to only one boundary value, so the missing matches remain.
• Adding DISTINCT addresses duplicate output, but the symptom here is missing rows caused by the wrong join condition.
• Using a Cartesian join with only >= lowest_sal ignores the upper limit and can match a salary to multiple grade rows.

Question 8

Topic: Display Data from Multiple Tables

Two small lookup tables are used to build a test matrix: colors(color_name) and sizes(size_name). You must return every color combined with every size, and the SQL must make the Cartesian-product intent explicit so it is not mistaken for a missing join condition. Which query best meets the requirement?

Options:

• A. SELECT c.color_name, s.size_name FROM colors c LEFT OUTER JOIN sizes s ON c.color_name = s.size_name ORDER BY c.color_name, s.size_name

• B. SELECT c.color_name, s.size_name FROM colors c CROSS JOIN sizes s ORDER BY c.color_name, s.size_name

• C. SELECT c.color_name, s.size_name FROM colors c, sizes s ORDER BY c.color_name, s.size_name

• D. SELECT c.color_name, s.size_name FROM colors c JOIN sizes s ON c.color_name = s.size_name ORDER BY c.color_name, s.size_name

Best answer: B

Explanation: Use CROSS JOIN when you intentionally need every row from one table paired with every row from another. It returns the full matrix and states that purpose directly, unlike a comma-separated FROM list that can look like a missing join predicate.

The core concept is distinguishing an intentional Cartesian product from an accidental one. In Oracle SQL, CROSS JOIN is the clearest way to say that every row in colors must be paired with every row in sizes. That exactly matches a test-matrix requirement.

A comma-separated FROM list without a join predicate can also produce a Cartesian product, but it is ambiguous to readers and reviewers because it often appears when a join condition was accidentally omitted. By contrast, CROSS JOIN makes the intent explicit in the SQL text itself. Equijoins and outer joins are different join types: they depend on a matching condition and therefore do not return every possible combination.

When the requirement is “all combinations, on purpose,” CROSS JOIN is the best choice.

• Comma syntax returns the same combinations, but it does not clearly communicate that the Cartesian product is deliberate.
• Equijoin logic only returns rows where the color value equals the size value, not the full combination set.
• Outer join logic preserves rows from one table, but it still depends on matches and does not generate every pair.

Question 9

Topic: Display Data from Multiple Tables

Examine the table definitions.

EMPLOYEES
---------
EMPLOYEE_ID      NUMBER
LAST_NAME        VARCHAR2(25)
DEPARTMENT_ID    NUMBER

DEPARTMENTS
-----------
DEPARTMENT_ID    NUMBER
DEPARTMENT_NAME  VARCHAR2(30)

Which query returns each employee’s LAST_NAME with the matching DEPARTMENT_NAME and shows only rows that exist in both tables?

Options:

• A. SELECT e.last_name, d.department_name FROM employees e LEFT OUTER JOIN departments d ON e.department_id = d.department_id

• B. SELECT e.last_name, d.department_name FROM employees e JOIN departments d ON e.employee_id = d.department_id

• C. SELECT e.last_name, d.department_name FROM employees e, departments d

• D. SELECT e.last_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id

Best answer: D

Explanation: An inner join returns rows only when the join condition matches in both tables. Here, EMPLOYEES.DEPARTMENT_ID must match DEPARTMENTS.DEPARTMENT_ID to pair each employee with the correct department name.

The core concept is an inner join with the correct join condition. DEPARTMENT_ID is the related column between these tables, so joining employees to departments on that column returns each employee together with the matching department name, and excludes rows without a match.

In Oracle SQL, JOIN ... ON ... is the clear way to express this requirement:

• match related rows from two tables
• use the common key columns in the ON clause
• return only rows present in both tables

Using the wrong columns compares unrelated values, and omitting a join condition creates a Cartesian product instead of matched pairs.

• Wrong columns: joining EMPLOYEE_ID to DEPARTMENT_ID compares unrelated keys, so the matches are not logically correct.
• Outer join mismatch: using a left outer join can keep employees even when no department row matches, which breaks the requirement.
• No join condition: listing both tables without a condition returns every combination of rows from both tables.

Question 10

Topic: Display Data from Multiple Tables

A schema contains these tables:

DEPARTMENTS(department_id PRIMARY KEY, department_name)
EMPLOYEES(employee_id PRIMARY KEY, last_name,
          department_id REFERENCES DEPARTMENTS(department_id))

EMPLOYEES.department_id is nullable. A developer needs an ANSI inner join that matches each employee to the correct parent department and does not create false matches for employees whose department_id is NULL.

Which ON clause correctly applies this Oracle SQL rule?

Options:

• A. ON e.department_id = d.department_id

• B. ON NVL(e.department_id, d.department_id) = d.department_id

• C. ON e.department_id IS NULL OR e.department_id = d.department_id

• D. ON e.employee_id = d.department_id

Best answer: A

Explanation: The correct join condition compares the child foreign key to the parent primary key: EMPLOYEES.department_id = DEPARTMENTS.department_id. In an inner join, employees with a NULL department value simply do not match, which avoids incorrect extra rows.

The core rule is to join parent and child tables by matching the child table’s foreign key to the parent table’s referenced key. Here, EMPLOYEES.department_id is the foreign key, and DEPARTMENTS.department_id is the primary key, so those two columns must be compared.

In Oracle, a NULL foreign key does not equal any parent key in an inner join, because NULL = value is not true. That is the desired behavior when an employee is not assigned to a department. Adding logic that treats NULL as a match can incorrectly pair one employee with many departments.

The key takeaway is simple: use the defined relationship columns directly, and do not force NULL child keys into matches.

• The condition using IS NULL OR ... is wrong because any employee with a NULL department would match every department row.
• The NVL expression is wrong because when the child key is NULL, it effectively makes the join condition true for all parent rows.
• The condition comparing employee_id to department_id uses unrelated columns, so it does not represent the parent-child relationship.

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
• SQL SELECT
• Restrict and Sort Data
• Single-Row Functions
• Conversion and CASE
• Group Functions
• 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.