Oracle Certification
61 practice questions with correct answers and detailed explanations. Use this guide to review concepts before taking the practice exam.
The Oracle Oracle Database SQL (1Z0-071) certification validates professional expertise in Oracle technologies. This study guide covers all 61 practice questions from our 1Z0-071 practice test, complete with correct answers and explanations to help you understand each concept thoroughly.
Review each question and explanation below, then test yourself with the full interactive practice exam to measure your readiness.
Which clause is used to eliminate duplicate rows from the result set of a query?
The DISTINCT keyword removes duplicate rows from query results. UNIQUE is a constraint type, GROUP BY is for aggregation, and HAVING filters grouped data.
What is the purpose of the HAVING clause in SQL?
HAVING filters groups after GROUP BY aggregation is performed, typically using aggregate functions like SUM(), COUNT(), or AVG().
Which of the following statements about INNER JOIN is correct?
INNER JOIN returns only rows that have matching values in both tables based on the join condition. Unmatched rows are excluded.
What will be the result of the following query? SELECT employee_id, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
This query uses a subquery in the WHERE clause to calculate the average salary, then filters employees earning above that average. Subqueries are valid in WHERE clauses.
Which join type will return all rows from the left table and matching rows from the right table?
LEFT OUTER JOIN returns all rows from the left table plus matching rows from the right table. Non-matching right table rows will have NULL values.
What does the UNION operator do when combining results from two SELECT statements?
UNION combines two result sets and automatically removes duplicate rows. UNION ALL keeps all rows including duplicates, INTERSECT returns common rows, and MINUS returns differences.
Which aggregate function returns the number of non-NULL values in a column?
COUNT() returns the number of non-NULL values in a specified column. COUNT(*) counts all rows including those with NULL values.
What is the result of executing: SELECT ROUND(15.567, 2) FROM dual;
ROUND(15.567, 2) rounds the number to 2 decimal places, resulting in 15.57.
Which SQL function is used to convert a string to uppercase?
UPPER() is the standard Oracle SQL function to convert strings to uppercase. UPCASE(), TO_UPPER(), and UPPERCASE() are not valid Oracle functions.
What is the difference between CHAR and VARCHAR2 data types in Oracle?
CHAR stores fixed-length strings and pads with spaces to reach the specified length, while VARCHAR2 stores variable-length strings without padding.
Which of the following queries will return employee names and their corresponding department names?
An INNER JOIN on the department ID column correctly matches employees with their departments. Other options either return only names or produce meaningless results.
What will happen if you use COUNT(*) with a GROUP BY clause that produces no matching rows?
If no rows match the GROUP BY criteria, the query returns no rows at all, not zero counts. This is different from a group that exists but has zero matching rows.
Which statement about NULL values in Oracle SQL is correct?
NULL represents unknown or missing data and cannot be compared using standard operators. IS NULL and IS NOT NULL operators must be used to test for NULL values.
What does the following query return? SELECT department_id, COUNT(*) as emp_count FROM employees GROUP BY department_id HAVING COUNT(*) > 5;
This query groups employees by department and uses HAVING to filter only those groups with more than 5 employees, returning department IDs and their counts.
Which function would you use to extract the month from a date column?
EXTRACT() is the Oracle SQL function to extract date parts like MONTH, YEAR, or DAY from a date column. MONTH() and DATE_PART() are not standard Oracle functions.
What is the correct syntax for a CASE expression that returns values based on conditions?
CASE WHEN...THEN...ELSE...END is the correct Oracle syntax for conditional expressions. IF(), SWITCH() and other variations are not valid in Oracle SQL.
Which of the following will correctly add 6 months to a date column?
Both ADD_MONTHS() and INTERVAL syntax are valid in Oracle SQL for adding months to dates. Adding 180 (days) would be approximate and not accurate.
What happens when you use an aggregate function like SUM() without a GROUP BY clause?
When using an aggregate function without GROUP BY, SQL calculates the aggregate across all rows and returns a single row with that result.
Which statement correctly describes the behavior of LEFT OUTER JOIN when there are no matching rows?
LEFT OUTER JOIN includes all rows from the left table. When there's no match in the right table, the right table columns contain NULL values.
What is the correct way to sort results by multiple columns in descending order?
Each column in ORDER BY clause needs its own sort direction specification. DESC must follow the column name to sort that column in descending order.
Which of the following correctly demonstrates the use of a correlated subquery?
A correlated subquery references columns from the outer query (e.department_id). This query finds employees earning more than the average in their department.
What will the following query return? SELECT * FROM employees WHERE commission_percent IS NOT NULL AND salary > 3000;
The AND operator requires both conditions to be true: commission_percent must have a value (not NULL) AND salary must exceed 3000.
Which aggregate function would you use to find the highest salary in the employees table?
MAX() is the standard Oracle SQL aggregate function to find the maximum value in a column. HIGHEST(), MAXIMUM(), and TOP() are not valid aggregate functions.
What is the difference between a subquery in the WHERE clause versus a subquery in the FROM clause?
WHERE subqueries filter based on conditions, while FROM subqueries (derived tables/inline views) create a temporary result set that the main query can select from.
When using GROUP BY, which columns can appear in the SELECT list without being in an aggregate function?
In GROUP BY queries, non-aggregated columns in SELECT must appear in the GROUP BY clause, otherwise results would be ambiguous or cause an error.
What does the INTERSECT operator return when used between two SELECT statements?
INTERSECT returns rows that are common to both queries' result sets. UNION combines all rows, MINUS returns first set minus second, and EXCEPT is a non-standard operator.
Which of the following statements about implicit data type conversion in Oracle is most accurate?
While Oracle does perform some implicit conversions, using explicit functions like TO_NUMBER(), TO_CHAR(), and TO_DATE() is considered best practice for clarity and predictability.
Which clause is used to filter groups in a GROUP BY query?
HAVING is the correct clause for filtering groups created by GROUP BY, whereas WHERE filters individual rows before grouping.
Which of the following is a valid use of the CASE expression in Oracle SQL?
The correct CASE syntax is CASE WHEN condition THEN result ELSE alternative_result END, as shown in option A.
What does the INTERSECT operator return?
INTERSECT returns only the rows that exist in both result sets, automatically removing duplicates.
Which function converts a character string to a number data type?
Both CAST and TO_NUMBER can convert character strings to numbers in Oracle; CAST is ANSI standard while TO_NUMBER is Oracle-specific.
What is the purpose of the ORDER BY clause?
ORDER BY sorts the result set in ascending (ASC) or descending (DESC) order based on specified columns.
Which of the following statements correctly uses the DISTINCT keyword?
DISTINCT must appear immediately after SELECT and applies to all columns in the result set; it removes duplicate rows.
What does an INNER JOIN return?
INNER JOIN returns only the rows that have matching values in both tables based on the join condition.
Which aggregate function returns the number of non-NULL values in a column?
COUNT() counts the number of non-NULL values; COUNT(*) counts all rows including those with NULL values.
In a LEFT OUTER JOIN, what happens to rows in the right table that have no match in the left table?
LEFT OUTER JOIN preserves all rows from the left table; unmatched rows from the right table are excluded and replaced with NULLs only for right table columns where a match occurs.
What is the correct syntax for a correlated subquery?
A correlated subquery references columns from the outer query (in this case, e.department_id), executing once for each outer row.
Which function extracts the year from a DATE column?
EXTRACT is the Oracle SQL function to extract date components; DATEPART is SQL Server syntax.
What does the UNION operator do?
UNION combines results from multiple SELECT statements and automatically removes duplicates; use UNION ALL to keep duplicates.
When using GROUP BY with aggregate functions, which columns can appear in the SELECT clause without aggregation?
In a GROUP BY query, non-aggregated columns in SELECT must appear in the GROUP BY clause to avoid ambiguity about which row's value to display.
Which of the following correctly uses the LIKE operator with wildcards?
All three examples correctly demonstrate LIKE usage: % matches zero or more characters and _ matches exactly one character.
What is returned by: SELECT NVL(NULL, 'No value') FROM DUAL;
NVL returns the second parameter when the first parameter is NULL, so NVL(NULL, 'No value') returns 'No value'.
Which JOIN type would you use to display all employees and their corresponding departments, including employees with no department assignment?
LEFT OUTER JOIN preserves all rows from the left table (employees) and includes matching rows from the right table (departments), with NULLs for unmatched rows.
What is the difference between COUNT(*) and COUNT(column_name)?
COUNT(*) counts every row in the result set including NULLs, while COUNT(column_name) excludes rows where that column is NULL.
Which statement is true about self-joins?
A self-join joins a single table to itself, requiring aliases (like e1 and e2) to reference the different instances of the same table.
What is the correct way to handle NULL values when sorting with ORDER BY?
Oracle SQL allows NULLS FIRST and NULLS LAST clauses in ORDER BY to explicitly position NULL values in the result set.
Which of the following is the correct use of an IN operator with a subquery?
The correct syntax is column_name IN (subquery), where the subquery must be enclosed in parentheses.
What does the CROSS JOIN produce?
A CROSS JOIN (or Cartesian join) returns all possible combinations of rows, with each row from table1 paired with every row from table2.
Which function would you use to remove leading and trailing spaces from a character string?
TRIM removes both leading/trailing spaces by default; LTRIM and RTRIM handle leading/trailing spaces individually; all are valid approaches.
In the expression SELECT salary, AVG(salary) FROM employees, why will this fail?
Without a GROUP BY clause, all columns in SELECT must be aggregate functions or constants; salary must be aggregated to avoid ambiguity.
Which of the following statements about HAVING is correct?
HAVING filters groups after GROUP BY is applied and can use aggregate functions; it appears after GROUP BY in the query structure.
Which clause is used to eliminate duplicate rows from the result set of a query?
The DISTINCT keyword removes duplicate rows from query results. UNIQUE is a constraint type, not a clause, and REMOVE DUPLICATES is not valid SQL syntax.
You need to retrieve employee names and their department names using a join. Employee records may not have corresponding department records. Which join type should you use?
LEFT OUTER JOIN returns all rows from the left table (employees) even if they have no matching records in the right table (departments). INNER JOIN would exclude employees without departments, while RIGHT OUTER JOIN would give the opposite result.
What is the primary purpose of the HAVING clause in SQL?
HAVING filters grouped data after GROUP BY is applied, whereas WHERE filters individual rows before grouping. This distinction is critical for understanding aggregate functions and group filtering.
You execute the following query: SELECT department_id, COUNT(*) FROM employees GROUP BY department_id HAVING COUNT(*) > 5; What does this query return?
The HAVING clause filters groups to include only those where COUNT(*) is greater than 5, returning department IDs and their employee counts meeting this condition. The HAVING clause operates on aggregated data, not individual rows.
Which statement about subqueries is correct?
Subqueries are flexible and can appear in multiple clauses (WHERE, FROM, SELECT, HAVING). They can return single or multiple rows and may use various operators depending on context.
You want to find the employee with the maximum salary in each department. Which approach would be most efficient?
Window functions like RANK() with PARTITION BY are optimized for this type of analysis and provide better performance than correlated subqueries or self-joins on large datasets.
What is the difference between UNION and UNION ALL in Oracle SQL?
UNION eliminates duplicate rows from the combined result set, while UNION ALL includes all rows. UNION ALL is typically faster because it avoids the overhead of removing duplicates.
Consider this query: SELECT employee_id, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); What does this return?
This query uses a scalar subquery to calculate the company-wide average salary, then returns employee_id and salary for those individuals whose salary exceeds that average. It does not perform any grouping.
Which of the following correctly describes the behavior of the CASE expression in Oracle SQL?
CASE expressions are versatile and support both simple CASE (comparing one value) and searched CASE (multiple conditions) formats. They work with any data type and can appear in various clauses. The ELSE clause is optional; if omitted, unmatched values return NULL.
You need to concatenate first_name and last_name columns with a space between them. In Oracle SQL, which is the preferred modern approach?
The concatenation operator (||) is the standard Oracle SQL approach and handles multiple values elegantly. CONCAT() exists but is more commonly associated with MySQL; it also only handles two arguments natively. The + operator is not valid for string concatenation in Oracle SQL.
You've reviewed all 61 questions. Take the interactive practice exam to simulate the real test environment.
▶ Start Practice Exam — Free