Test Your Oracle DISTINCT Command Knowledge

Progress

True / False

The SELECT * keyword in Oracle is used to return only unique values from a query.

True False

The SELECT * keyword eliminates duplicate rows from the result set, returning only unique values.

True / False

In Oracle, you can use SELECT * with a single column in a SELECT statement.

True False

SELECT * can be applied to a single column to return unique values from that column.

True / False

The SELECT * keyword can be used with aggregate functions in Oracle.

True False

SELECT * can be used with aggregate functions, such as COUNT(SELECT * column_name), to return the number of unique values.

True / False

In Oracle, the SELECT * keyword can be used with multiple columns in a SELECT statement.

True False

SELECT * can be applied to multiple columns, returning unique combinations of the specified columns.

True / False

Using SELECT * in Oracle always improves the performance of a query.

True False

Using SELECT * can improve query results by removing duplicates, but it may not always improve performance and can sometimes slow down queries due to the additional processing required.

True / False

In Oracle, SELECT * can be used in subqueries.

True False

SELECT * can be used in subqueries to filter unique results that are then used by the main query.

True / False

In Oracle, using SELECT * with a large dataset can impact query performance and should be used judiciously.

True False

Applying SELECT * to large datasets can impact performance due to the need to sort and filter out duplicates.

True / False

The SELECT * keyword in Oracle can be used with ORDER BY to sort the unique results.

True False

SELECT * can be combined with ORDER BY to sort the unique results returned by the query.

True / False

In Oracle, SELECT * can be used with GROUP BY to get unique groups.

True False

SELECT * and GROUP BY serve different purposes. SELECT * eliminates duplicate rows, while GROUP BY groups rows based on specified columns. Using SELECT * with GROUP BY is redundant.

Q10

True / False

The Oracle Certified Professional (OCP) exam includes knowledge on using the SELECT * keyword effectively in queries and understanding its impact on performance.

True False

The OCP certification covers advanced topics, including how to use the SELECT * keyword effectively in queries and understanding its performance implications, reflecting a deep understanding of Oracle SQL.

Q11

Single Choice

What is the purpose of the SELECT * keyword in an Oracle SQL query?

To count the number of rows in a table To remove duplicate rows from the result set To sort the rows in descending orderTo group rows by a specific column

The SELECT * keyword in Oracle is used to eliminate duplicate rows from the result set, returning only unique rows.

Q12

Single Choice

Consider the following Oracle SQL query:What will this query return?

SQL Code

SELECT SELECT * department_id FROM employees;

All rows from the employees table Unique department IDs from the employees table Duplicate department IDs from the employees tableThe total number of departments

The SELECT * keyword ensures that only unique department_id values are returned, eliminating duplicates.

Q13

Single Choice

Which of the following is true about using SELECT * in Oracle SQL?

It can be used with multiple columns It only works with numeric columns It automatically sorts the result setIt can be used to delete rows

The SELECT * keyword can be used with one or more columns to return unique combinations of values from those columns.

Q14

Single Choice

Consider the following Oracle SQL query:What does this query return?

SQL Code

SELECT SELECT * job_id, department_id FROM employees;

All rows from the employees table Unique combinations of job_id and department_id The total number of jobs and departmentsOnly the first row of the employees table

This query returns unique pairs of job_id and department_id from the employees table, removing any duplicate combinations.

Q15

Single Choice

What is the effect of using SELECT * with the COUNT function in Oracle?

It counts all rows in the table It counts only the unique rows in the table It counts the number of select * columnsIt counts only rows with NULL values

When SELECT * is used with the COUNT function, it counts only the unique values, eliminating duplicates from the count.

Q16

Single Choice

Consider the following Oracle SQL query:What will this query return?

SQL Code

SELECT COUNT(SELECT * department_id) FROM employees;

The total number of employees The number of unique departments in the employees table The total number of departments including duplicatesThe number of employees in each department

This query returns the count of unique department_id values in the employees table, representing the number of select * departments.

Q17

Single Choice

What will be the result of the following Oracle SQL query?

SQL Code

SELECT SELECT * salary, COUNT(*) FROM employees GROUP BY salary;

The query will return the select * salary values and the count of employees for each salary The query will return an error because SELECT * cannot be used with COUNT The query will return the total number of select * salariesThe query will return the select * salary values without the count

The query groups the employees table by salary and uses SELECT * to ensure unique salary values, along with the count of employees earning each salary.

Q18

Single Choice

Consider the following Oracle SQL query:What will this query return?

SQL Code

SELECT SELECT * ON (department_id) department_id, employee_id FROM employees;

Unique employee_id values for each department An error because SELECT * ON is not valid in Oracle All unique department_id valuesThe first employee_id for each department

Oracle does not support the SELECT * ON clause. Instead, a GROUP BY clause or a subquery should be used to achieve similar results.

Q19

Single Choice

How does Oracle handle NULL values when using the SELECT * keyword in a query?

NULL values are ignored by SELECT * Each NULL value is treated as select * All NULL values are treated as duplicates and removedThe query fails if NULL values are present

In Oracle, SELECT * treats each NULL as a unique value, so if multiple NULL values are present, only one NULL will be included in the result set.

Q20

Single Choice

In an Oracle certification exam, you encounter the following SQL statement:What does this query return?

SQL Code

SELECT SELECT * first_name, last_name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);

All employees in the specified location Unique first and last name combinations of employees in departments located in location 1700 All departments in location 1700An error because SELECT * cannot be used with subqueries

This query returns unique combinations of first_name and last_name for employees who work in departments located in location_id 1700.

Q21

Multiple Choice

Which SQL code snippet correctly uses the SELECT * keyword to retrieve unique department IDs from the 'employees' table in Oracle?

SQL Code

SELECT SELECT * department_id
FROM employees;

SELECT department_id
FROM employees
GROUP BY department_id;

SELECT department_id
FROM employees;

SELECT SELECT * department_id FROM employees; SELECT department_id FROM employees GROUP BY department_id; SELECT department_id FROM employees;SELECT SELECT * department_id FROM employees WHERE department_id > 10;

Options A and B correctly retrieve unique department IDs. Option A uses SELECT *, and Option B uses GROUP BY. Option C retrieves all department IDs without filtering duplicates.

Q22

Multiple Choice

Which SQL code snippet demonstrates how to use SELECT * to retrieve unique job titles from the 'employees' table in Oracle?

SQL Code

SELECT SELECT * job_title
FROM employees;

SELECT job_title
FROM employees
GROUP BY job_title;

SELECT job_title
FROM employees
ORDER BY job_title;

SELECT SELECT * job_title FROM employees; SELECT job_title FROM employees GROUP BY job_title; SELECT job_title FROM employees ORDER BY job_title;SELECT SELECT * job_title FROM employees WHERE job_title LIKE 'Manager%';

Options A and B correctly retrieve unique job titles using SELECT * and GROUP BY, respectively. Option C retrieves all job titles but orders them without filtering duplicates.

Q23

Multiple Choice

Which SQL code snippet uses SELECT * to retrieve unique combinations of department ID and job title from the 'employees' table in Oracle?

SQL Code

SELECT SELECT * department_id, job_title
FROM employees;

SELECT department_id, job_title
FROM employees
GROUP BY department_id, job_title;

SELECT department_id, job_title
FROM employees
ORDER BY department_id, job_title;

SELECT SELECT * department_id, job_title FROM employees; SELECT department_id, job_title FROM employees GROUP BY department_id, job_title; SELECT department_id, job_title FROM employees ORDER BY department_id, job_title;SELECT SELECT * department_id, job_title FROM employees WHERE department_id = 20;

Options A and B correctly retrieve unique combinations of department ID and job title. Option C retrieves all combinations but orders them without filtering duplicates.

Q24

Multiple Choice

Which SQL code snippet demonstrates how to use SELECT * with COUNT to find the number of unique job titles in the 'employees' table in Oracle?

SQL Code

SELECT COUNT(SELECT * job_title) AS unique_jobs
FROM employees;

SELECT COUNT(job_title) AS total_jobs
FROM employees;

SELECT COUNT(SELECT * job_title)
FROM employees
WHERE department_id = 10;

SELECT COUNT(SELECT * job_title) AS unique_jobs FROM employees; SELECT COUNT(job_title) AS total_jobs FROM employees; SELECT COUNT(SELECT * job_title) FROM employees WHERE department_id = 10;SELECT SELECT * COUNT(job_title) FROM employees;

Options A and C correctly use SELECT * with COUNT to find the number of unique job titles. Option B counts all job titles without filtering duplicates.

Q25

Multiple Choice

Which SQL code snippet uses SELECT * to retrieve unique city names from the 'locations' table in Oracle?

SQL Code

SELECT SELECT * city
FROM locations;

SELECT city
FROM locations
GROUP BY city;

SELECT SELECT * city
FROM locations
WHERE country_id = 'US';

SELECT SELECT * city FROM locations; SELECT city FROM locations GROUP BY city; SELECT SELECT * city FROM locations WHERE country_id = 'US';SELECT SELECT * city FROM locations;

Options A and C correctly retrieve unique city names. Option A retrieves all unique cities, while Option C filters by country before applying SELECT *.

Q26

Multiple Choice

Which SQL code snippet demonstrates advanced usage of SELECT * to retrieve unique combinations of employee names and their departments in Oracle?

SQL Code

SELECT SELECT * first_name, last_name, department_id
FROM employees;

SELECT first_name, last_name, department_id
FROM employees
GROUP BY first_name, last_name, department_id;

SELECT SELECT * first_name, last_name
FROM employees
WHERE department_id > 10;

SELECT SELECT * first_name, last_name, department_id FROM employees; SELECT first_name, last_name, department_id FROM employees GROUP BY first_name, last_name, department_id; SELECT SELECT * first_name, last_name FROM employees WHERE department_id > 10;SELECT SELECT * first_name, department_id FROM employees;

Options A and B correctly retrieve unique combinations of employee names and their departments. Option C retrieves unique names but filters by department.

Q27

Multiple Choice

Which SQL code snippet uses SELECT * to retrieve unique job titles from the 'employees' table where the salary is above a certain threshold in Oracle?

SQL Code

SELECT SELECT * job_title
FROM employees
WHERE salary > 50000;

SELECT job_title
FROM employees
WHERE salary > 50000
GROUP BY job_title;

SELECT SELECT * job_title
FROM employees
WHERE salary > 100000;

SELECT SELECT * job_title FROM employees WHERE salary > 50000; SELECT job_title FROM employees WHERE salary > 50000 GROUP BY job_title; SELECT SELECT * job_title FROM employees WHERE salary > 100000;SELECT SELECT * job_title FROM employees WHERE salary > 50000;

Options A and C correctly retrieve unique job titles where the salary is above a certain threshold. Option B retrieves job titles using GROUP BY.

Q28

Multiple Choice

Which SQL code snippet demonstrates the advanced use of SELECT * to retrieve unique customer names and order dates from the 'orders' table in Oracle?

SQL Code

SELECT SELECT * customer_name, order_date
FROM orders;

SELECT customer_name, order_date
FROM orders
GROUP BY customer_name, order_date;

SELECT SELECT * customer_name, order_date
FROM orders
WHERE order_date > SYSDATE - 30;

SELECT SELECT * customer_name, order_date FROM orders; SELECT customer_name, order_date FROM orders GROUP BY customer_name, order_date; SELECT SELECT * customer_name, order_date FROM orders WHERE order_date > SYSDATE - 30;SELECT SELECT * order_date FROM orders WHERE customer_name IS NOT NULL;

Options A and C correctly retrieve unique customer names and order dates. Option C includes a filter on order dates.

Q29

Multiple Choice

Which SQL code snippet uses SELECT * to retrieve unique product IDs from the 'order_items' table where the quantity is above a certain number in Oracle?

SQL Code

SELECT SELECT * product_id
FROM order_items
WHERE quantity > 10;

SELECT product_id
FROM order_items
WHERE quantity > 10
GROUP BY product_id;

SELECT SELECT * product_id
FROM order_items
WHERE quantity > 50;

SELECT SELECT * product_id FROM order_items WHERE quantity > 10; SELECT product_id FROM order_items WHERE quantity > 10 GROUP BY product_id; SELECT SELECT * product_id FROM order_items WHERE quantity > 50;SELECT SELECT * product_id FROM order_items WHERE quantity > 10;

Options A and C correctly retrieve unique product IDs where the quantity is above a certain number. Option B retrieves product IDs using GROUP BY.

Q30

Multiple Choice

Which SQL code snippet demonstrates the certification-level use of SELECT * to find the number of unique customers who placed orders in the last month in Oracle?

SQL Code

SELECT COUNT(SELECT * customer_id) AS unique_customers
FROM orders
WHERE order_date > SYSDATE - 30;

SELECT SELECT * customer_id
FROM orders
WHERE order_date > SYSDATE - 30;

SELECT COUNT(SELECT * customer_id)
FROM orders WHERE order_date > SYSDATE - 30;

SELECT COUNT(SELECT * customer_id) AS unique_customers FROM orders WHERE order_date > SYSDATE - 30; SELECT SELECT * customer_id FROM orders WHERE order_date > SYSDATE - 30; SELECT COUNT(SELECT * customer_id) FROM orders WHERE order_date > SYSDATE - 30;SELECT SELECT * COUNT(customer_id) FROM orders WHERE order_date > SYSDATE - 30;

Options A and C correctly use SELECT * to find the number of unique customers who placed orders in the last month. Option B retrieves the unique customer IDs without counting them.

Oracle Database Quiz Questions