Test Your Oracle SELECT INTO Knowledge

Progress

True / False

In ORACLE, the SELECT INTO statement is used to select data from one or more tables and insert it into another table.

True False

The SELECT INTO statement in ORACLE is used to select data from a table and insert it into a variable, not another table.

True / False

The SELECT INTO statement in ORACLE can only be used within PL/SQL blocks.

True False

SELECT INTO is used to assign values to PL/SQL variables, and thus it must be used within a PL/SQL block.

True / False

You can use SELECT INTO in ORACLE to insert multiple rows of data into multiple variables.

True False

SELECT INTO in ORACLE can only insert one row of data into multiple variables. If the query returns more than one row, it will result in an error.

True / False

In ORACLE, if a SELECT INTO statement does not return any rows, a NO_DATA_FOUND exception is raised.

True False

When a SELECT INTO statement in ORACLE does not find any rows, the NO_DATA_FOUND exception is automatically raised.

True / False

The SELECT INTO statement in ORACLE can include joins between multiple tables.

True False

You can use joins in a SELECT INTO statement to retrieve data from multiple tables and assign it to variables.

True / False

You can use the SELECT INTO statement in ORACLE to fetch data into a collection type variable.

True False

SELECT INTO is used to fetch data into scalar variables or record types, not collections. To fetch data into collections, a cursor or bulk collect should be used.

True / False

In ORACLE, the SELECT INTO statement supports the use of aggregate functions like SUM and AVG.

True False

Aggregate functions can be used within a SELECT INTO statement to assign the result to a variable.

True / False

Using SELECT INTO with a subquery that includes a GROUP BY clause in ORACLE will always require the variable to be of a collection type.

True False

A GROUP BY clause can be used in a SELECT INTO statement as long as the subquery returns a single row; otherwise, it will cause an error.

True / False

The SELECT INTO statement in ORACLE can be used with a DISTINCT keyword to eliminate duplicate values before assigning to variables.

True False

The DISTINCT keyword can be used in a SELECT INTO statement to ensure that only unique values are assigned to variables.

Q10

True / False

In ORACLE, when using SELECT INTO, the variables must be declared in the same order as the columns in the SELECT statement.

True False

The variables in a SELECT INTO statement must be declared in the same order as the columns in the SELECT statement because the values are assigned positionally.

Q11

Single Choice

What is the primary purpose of the SELECT INTO statement in Oracle?

To update existing records in a table. To create a new table and insert the result of a query into it. To retrieve data from a table and store it in variables.To delete records from a table.

The SELECT INTO statement in Oracle is used to retrieve data from a table and store it in PL/SQL variables.

Q12

Single Choice

Which of the following is the correct syntax for using SELECT INTO to store a single row's NAME column value into a PL/SQL variable?

SELECT NAME INTO v_name FROM EMPLOYEES WHERE ID = 1; SELECT v_name INTO NAME FROM EMPLOYEES WHERE ID = 1; SELECT NAME FROM EMPLOYEES INTO v_name WHERE ID = 1;SELECT NAME INTO EMPLOYEES FROM v_name WHERE ID = 1;

The correct syntax for storing a single row's column value into a PL/SQL variable is SELECT column_name INTO variable_name FROM table_name WHERE condition.

Q13

Single Choice

What will happen if the SELECT INTO statement in Oracle retrieves no rows?

The variable will be set to NULL. An error will be raised. The variable will retain its previous value.The SELECT INTO statement will return a NULL value.

If a SELECT INTO statement retrieves no rows, Oracle raises a NO_DATA_FOUND exception.

Q14

Single Choice

In Oracle, how can you use SELECT INTO to fetch multiple column values into multiple PL/SQL variables?

SELECT ID, NAME INTO v_id, v_name FROM EMPLOYEES WHERE ID = 1; SELECT INTO v_id, v_name FROM EMPLOYEES WHERE ID = 1; SELECT v_id, v_name INTO ID, NAME FROM EMPLOYEES WHERE ID = 1;SELECT * INTO v_id, v_name FROM EMPLOYEES WHERE ID = 1;

To fetch multiple column values into multiple PL/SQL variables, the correct syntax is SELECT column1, column2 INTO variable1, variable2 FROM table_name WHERE condition.

Q15

Single Choice

What will happen in Oracle if the SELECT INTO statement retrieves more than one row?

SQL Code

DECLARE
 v_name VARCHAR2(100);
BEGIN
 SELECT NAME INTO v_name FROM EMPLOYEES;
END;

The first row will be fetched, and the rest will be ignored. An error will be raised. The last row will be fetched, and the rest will be ignored.All rows will be fetched and stored in the variable.

If SELECT INTO retrieves more than one row, Oracle raises a TOO_MANY_ROWS exception.

Q16

Single Choice

Which of the following is true about using SELECT INTO in Oracle with aggregate functions?

SELECT INTO cannot be used with aggregate functions. Aggregate functions with `SELECT INTO` will raise an error if no rows are found. Aggregate functions will return `NULL` when no rows match the condition.`SELECT INTO` must always return exactly one row, even with aggregate functions.

When using aggregate functions with `SELECT INTO`, if no rows match the condition, Oracle returns `NULL`.

Q17

Single Choice

Consider the following Oracle SQL code. What will be the result of executing the `SELECT INTO` statement?

SQL Code

DECLARE
 v_total_salary NUMBER;
BEGIN
 SELECT SUM(SALARY) INTO v_total_salary
 FROM EMPLOYEES WHERE DEPARTMENT_ID = 10;
END;

The total salary for department 10 will be stored in v_total_salary. An error will occur if no employees belong to department 10. The v_total_salary will remain NULL if no rows are found.The v_total_salary will be set to 0 if no rows are found.

If no employees belong to department 10, the SUM function returns NULL, and v_total_salary will be NULL.

Q18

Single Choice

How can you handle exceptions in Oracle when using SELECT INTO to avoid runtime errors if no data is found?

Use `SELECT INTO` without any exception handling. Use an `EXCEPTION` block with `WHEN NO_DATA_FOUND THEN`. Use `SELECT INTO` with an aggregate function.Use `SELECT INTO` in a cursor to handle exceptions automatically.

To handle exceptions when using `SELECT INTO`, an `EXCEPTION` block should be used with `WHEN NO_DATA_FOUND THEN`.

Q19

Single Choice

In Oracle, how would you modify the following code to ensure that `v_avg_salary` is set to 0 if no rows are found?

SQL Code

DECLARE
 v_avg_salary NUMBER;
BEGIN
 SELECT AVG(SALARY) INTO v_avg_salary
 FROM EMPLOYEES WHERE DEPARTMENT_ID = 10;

 IF v_avg_salary IS NULL THEN
 v_avg_salary := 0;
 END IF;
END;

Replace SELECT AVG(SALARY) with NVL(SELECT AVG(SALARY), 0). Replace SELECT AVG(SALARY) with SELECT NVL(AVG(SALARY), 0). Add a DEFAULT 0 clause to the SELECT INTO statement.Use v_avg_salary := 0 before the SELECT INTO statement.

The NVL function can be used in Oracle to replace NULL with a specified value. Here, SELECT NVL(AVG(SALARY), 0) ensures that v_avg_salary is set to 0 if no rows are found.

Q20

Single Choice

In Oracle, which of the following is true about the SELECT INTO statement in PL/SQL?

SELECT INTO can be used to insert data into a table. SELECT INTO must always be followed by an UPDATE statement. SELECT INTO can retrieve multiple rows without raising an exception.SELECT INTO retrieves a single row into one or more PL/SQL variables.

The SELECT INTO statement in Oracle retrieves a single row of data from a query into one or more PL/SQL variables. If multiple rows are returned, an exception is raised.

Q21

Multiple Choice

Identify the SQL scripts that correctly perform a basic SELECT INTO operation in Oracle.

SQL Code

You need to create a new table 'backup_employees' by copying all data from the 'employees' table.

Which of the following SQL scripts correctly implement this scenario?

CREATE TABLE backup_employees AS SELECT * FROM employees; SELECT * INTO backup_employees FROM employees; INSERT INTO backup_employees SELECT * FROM employees;CREATE backup_employees AS SELECT * FROM employees;

The SELECT INTO statement is used in Oracle to create a new table and populate it with the result of a SELECT query. The correct scripts will demonstrate how to copy data into a new table.

Q22

Multiple Choice

Select the SQL scripts that correctly use SELECT INTO with WHERE clause in Oracle.

SQL Code

You need to create a new table 'active_employees' by copying only the rows where 'status' is 'Active' from the 'employees' table.

Which of the following SQL scripts correctly implement this scenario?

CREATE TABLE active_employees AS SELECT * FROM employees WHERE status = 'Active'; SELECT * INTO active_employees FROM employees WHERE status = 'Active'; INSERT INTO active_employees SELECT * FROM employees WHERE status = 'Active';CREATE active_employees AS SELECT * FROM employees WHERE status = 'Active';

Using the SELECT INTO statement with a WHERE clause allows for selective data copying in Oracle. The correct scripts will demonstrate how to filter data during the SELECT INTO operation.

Q23

Multiple Choice

Determine the SQL scripts that correctly perform a SELECT INTO operation with column selection in Oracle.

SQL Code

You need to create a new table 'employee_names' by copying only the 'employee_id' and 'full_name' columns from the 'employees' table.

Which of the following SQL scripts correctly implement this scenario?

CREATE TABLE employee_names AS SELECT employee_id, full_name FROM employees; SELECT employee_id, full_name INTO employee_names FROM employees; INSERT INTO employee_names (employee_id, full_name) SELECT employee_id, full_name FROM employees;CREATE employee_names AS SELECT employee_id, full_name FROM employees;

The SELECT INTO statement can be used to select specific columns when creating a new table. The correct scripts will demonstrate how to copy selected columns into a new table.

Q24

Multiple Choice

Identify the SQL scripts that correctly handle a SELECT INTO operation with an aggregate function in Oracle.

SQL Code

You need to create a new table 'department_totals' that contains the total number of employees per department from the 'employees' table.

Which of the following SQL scripts correctly implement this scenario?

CREATE TABLE department_totals AS SELECT department_id, COUNT(*) AS total_employees FROM employees GROUP BY department_id; SELECT department_id, COUNT(*) AS total_employees INTO department_totals FROM employees GROUP BY department_id; INSERT INTO department_totals SELECT department_id, COUNT(*) AS total_employees FROM employees GROUP BY department_id;CREATE department_totals AS SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;

Using SELECT INTO with aggregate functions like COUNT can be useful for summarizing data into a new table. The correct scripts will demonstrate how to create a table with aggregated results.

Q25

Multiple Choice

Select the SQL scripts that correctly perform a SELECT INTO operation with a JOIN clause in Oracle.

SQL Code

You need to create a new table 'employee_departments' by joining the 'employees' and 'departments' tables on 'department_id'.

Which of the following SQL scripts correctly implement this scenario?

CREATE TABLE employee_departments AS SELECT e.employee_id, e.full_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id; SELECT e.employee_id, e.full_name, d.department_name INTO employee_departments FROM employees e JOIN departments d ON e.department_id = d.department_id; INSERT INTO employee_departments SELECT e.employee_id, e.full_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id;CREATE employee_departments AS SELECT e.employee_id, e.full_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id;

The SELECT INTO statement can be combined with JOINs to merge data from multiple tables into a new table. The correct scripts will demonstrate how to create a table using a JOIN in SELECT INTO.

Q26

Multiple Choice

Determine the SQL scripts that correctly perform a SELECT INTO operation with a subquery in Oracle.

SQL Code

You need to create a new table 'high_salary_employees' by copying data for employees whose salary is higher than the average salary in the 'employees' table.

Which of the following SQL scripts correctly implement this scenario?

CREATE TABLE high_salary_employees AS SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); SELECT * INTO high_salary_employees FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); INSERT INTO high_salary_employees SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);CREATE high_salary_employees AS SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

Using a subquery within a SELECT INTO statement allows for more complex data selection. The correct scripts will demonstrate how to use a subquery to filter data during the SELECT INTO operation.

Q27

Multiple Choice

Identify the SQL scripts that correctly handle errors during a SELECT INTO operation in Oracle.

SQL Code

You need to ensure that any errors during the SELECT INTO operation for creating a new table 'project_managers' are handled appropriately.

Which of the following SQL scripts correctly implement this scenario?

BEGIN CREATE TABLE project_managers AS SELECT * FROM employees WHERE job_title = 'Manager'; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An error occurred during the SELECT INTO operation.'); END; CREATE TABLE project_managers AS SELECT * FROM employees WHERE job_title = 'Manager'; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An error occurred during the SELECT INTO operation.'); BEGIN SELECT * INTO project_managers FROM employees WHERE job_title = 'Manager'; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An error occurred during the SELECT INTO operation.'); END;CREATE TABLE project_managers AS SELECT * FROM employees WHERE job_title = 'Manager'; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An error occurred during the SELECT INTO operation.');

Handling errors during a SELECT INTO operation can be done using PL/SQL exception handling. The correct scripts will demonstrate how to catch and handle errors that occur during the operation.

Q28

Multiple Choice

Select the SQL scripts that correctly use SELECT INTO to copy data with transformations in Oracle.

SQL Code

You need to create a new table 'employee_uppercase_names' by copying the 'employee_id' and an uppercase version of the 'full_name' from the 'employees' table.

Which of the following SQL scripts correctly implement this scenario?

CREATE TABLE employee_uppercase_names AS SELECT employee_id, UPPER(full_name) AS full_name FROM employees; SELECT employee_id, UPPER(full_name) INTO employee_uppercase_names FROM employees; INSERT INTO employee_uppercase_names (employee_id, full_name) SELECT employee_id, UPPER(full_name) FROM employees;CREATE employee_uppercase_names AS SELECT employee_id, UPPER(full_name) FROM employees;

Transformations such as applying functions during a SELECT INTO operation can be useful for modifying data during the copy process. The correct scripts will demonstrate how to transform data while copying it into a new table.

Q29

Multiple Choice

Determine the SQL scripts that correctly use SELECT INTO with a temporary table in Oracle.

SQL Code

You need to create a temporary table 'temp_projects' by copying data from the 'projects' table for processing within a session.

Which of the following SQL scripts correctly implement this scenario?

CREATE GLOBAL TEMPORARY TABLE temp_projects AS SELECT * FROM projects; SELECT * INTO temp_projects FROM projects; CREATE TEMPORARY TABLE temp_projects AS SELECT * FROM projects;CREATE TEMPORARY TABLE temp_projects AS SELECT * FROM projects WITH NOLOGGING;

Creating a temporary table with a SELECT INTO operation allows for session-specific data processing. The correct scripts will demonstrate how to create and use a temporary table in Oracle.

Q30

Multiple Choice

Identify the SQL scripts that correctly perform a SELECT INTO operation with a sequence in Oracle.

SQL Code

You need to create a new table 'order_details' by copying data from the 'orders' table, and assigning a new sequence-generated ID to each row.

Which of the following SQL scripts correctly implement this scenario?

CREATE TABLE order_details AS SELECT order_seq.NEXTVAL AS order_detail_id, * FROM orders; SELECT order_seq.NEXTVAL AS order_detail_id, * INTO order_details FROM orders; CREATE TABLE order_details AS SELECT order_seq.NEXTVAL AS order_detail_id, o.* FROM orders o;CREATE TABLE order_details AS SELECT * FROM orders o ORDER BY order_seq.NEXTVAL;

Using a sequence during a SELECT INTO operation allows for the generation of unique identifiers for each row. The correct scripts will demonstrate how to use a sequence to populate a new column in the target table.

Oracle Database Quiz Questions