Oracle Quiz - MCQ - Multiple Choice Questions
Q. What is the primary purpose of using indexes in an Oracle database?
A. To improve data storage and organizationB. To enhance data retrieval performance
C. To ensure data integrity and security
D. To facilitate complex data calculations
Q. Which SQL command is used to retrieve data from a database table?
A. SELECTB. INSERT
C. UPDATE
D. DELETE
Q. What is a subquery in SQL, and how is it used?
A. A subquery is a query within another query, used to filter data based on a condition.B. A subquery is a query that runs independently and returns data to the main query.
C. A subquery is a query that runs multiple times to retrieve data from different tables.
D. A subquery is a query within a query used to perform calculations on aggregated data.
Q. How can you ensure data integrity in an Oracle database?
A. By using constraintsB. By regularly backing up the database
C. By using triggers
D. All of the above
Q. What is normalization in the context of database design?
A. The process of organizing data into tables to minimize redundancy and improve data integrityB. The technique of combining multiple tables into a single table to improve query performance
C. The process of creating indexes on tables to speed up data retrieval
D. The method of encrypting data in a database to enhance security
Q. What is a view in an Oracle database?
A. A virtual table based on the result of a queryB. A copy of a table stored in a different location
C. A temporary table used for data manipulation
D. A collection of tables grouped together for security purposes
Q. How can you improve the performance of a slow-running query in Oracle?
A. Use indexes on the relevant columnsB. Rewrite the query using joins instead of subqueries
C. Increase the database buffer cache size
D. All of the above
Q. What is a stored procedure in Oracle, and what are its benefits?
A. A precompiled collection of SQL statements stored in the database, providing modularity and reusability.B. A database object that automatically triggers an action when a specific event occurs, enhancing data validation.
C. A security mechanism that restricts access to specific database objects based on user roles and permissions.
D. All of the above
Q. What is the purpose of the Predicate functional interface in Java 8?
A. To produce resultsB. To consume values
C. To filter elements based on a condition
D. To transform values
Q. What is the benefit of using PL/SQL instead of pure SQL in Oracle?
A. PL/SQL provides improved performance for complex queriesB. PL/SQL allows for procedural logic and control structures
C. PL/SQL offers better security features compared to pure SQL
D. All of the above
Q. How can you optimize a query that involves multiple tables in Oracle?
A. Use joins to combine the tables efficientlyB. Create indexes on the relevant columns in each table
C. Use subqueries to retrieve data from each table separately
D. All of the above
Q. What is a trigger in an Oracle database, and what is its purpose?
A. A trigger is a database object that automatically performs an action when a specific event occurs, such as inserting, updating, or deleting data.B. A trigger is a security mechanism that restricts access to specific database objects based on user roles and permissions.
C. A trigger is a type of stored procedure used to handle complex business logic in the database.
D. A trigger is a way to schedule and automate database maintenance tasks.
Q. What is the difference between a LEFT JOIN and a RIGHT JOIN in SQL?
A. LEFT JOIN returns all rows from the left table and matching rows from the right table, while RIGHT JOIN returns all rows from the right table and matching rows from the left table.B. LEFT JOIN returns distinct rows from the left table and matching rows from the right table, while RIGHT JOIN returns distinct rows from both tables.
C. LEFT JOIN returns all rows from both tables, while RIGHT JOIN returns only matching rows from both tables.
D. LEFT JOIN and RIGHT JOIN are equivalent and can be used interchangeably.
Q. How can you handle database transactions in Oracle?
A. By using the COMMIT and ROLLBACK statementsB. By setting the AUTOCOMMIT mode to true
C. By using the SAVEPOINT statement to create transaction checkpoints
D. All of the above
Which of the following code snippets demonstrates the correct way to create a stored procedure in Oracle that takes an input parameter and returns a result set?
A.CREATE PROCEDURE GetEmployeesByDepartment(dept_id IN NUMBER) AS CURSOR c_employees IS SELECT employee_id, employee_name FROM employees WHERE department_id = dept_id; BEGIN OPEN c_employees; END;B.
CREATE PROCEDURE GetEmployeesByDepartment(dept_id IN NUMBER) AS CURSOR c_employees IS SELECT employee_id, employee_name FROM employees WHERE department_id = dept_id; BEGIN FOR emp IN c_employees LOOP DBMS_OUTPUT.PUT_LINE(emp.employee_id || ' ' || emp.employee_name); END LOOP; END;C.
CREATE OR REPLACE PROCEDURE GetEmployeesByDepartment(dept_id IN NUMBER) AS CURSOR c_employees IS SELECT employee_id, employee_name FROM employees WHERE department_id = dept_id; v_employee_id employees.employee_id%TYPE; v_employee_name employees.employee_name%TYPE; BEGIN OPEN c_employees; LOOP FETCH c_employees INTO v_employee_id, v_employee_name; EXIT WHEN c_employees%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_employee_id || ' ' || v_employee_name); END LOOP; CLOSE c_employees; END;D.
CREATE PROCEDURE GetEmployeesByDepartment(dept_id IN NUMBER) AS CURSOR c_employees IS SELECT employee_id, employee_name FROM employees WHERE department_id = dept_id; BEGIN FOR emp IN c_employees LOOP DBMS_OUTPUT.PUT_LINE(emp.employee_id || ' ' || emp.employee_name); END LOOP; END;
What will be the output of the following PL/SQL code snippet?
DECLARE v_number NUMBER := 5; v_result NUMBER; BEGIN v_result := CASE WHEN v_number > 0 THEN 'Positive' WHEN v_number < 0 THEN 'Negative' ELSE 'Zero' END; DBMS_OUTPUT.PUT_LINE(v_result); END;A.
PositiveB.
NegativeC.
ZeroD.
Compilation Error
Which of the following code snippets demonstrates the correct way to insert data into a table using a PL/SQL procedure?
A.CREATE PROCEDURE InsertEmployee( p_employee_id IN employees.employee_id%TYPE, p_employee_name IN employees.employee_name%TYPE ) AS BEGIN INSERT INTO employees (employee_id, employee_name) VALUES (p_employee_id, p_employee_name); END;B.
CREATE PROCEDURE InsertEmployee( p_employee_id IN NUMBER, p_employee_name IN VARCHAR2 ) AS BEGIN INSERT INTO employees (employee_id, employee_name) VALUES (p_employee_id, p_employee_name); END;C.
CREATE PROCEDURE InsertEmployee( employee_id IN NUMBER, employee_name IN VARCHAR2 ) AS BEGIN INSERT INTO employees (employee_id, employee_name) VALUES (employee_id, employee_name); END;D.
CREATE PROCEDURE InsertEmployee( p_employee_id IN NUMBER, p_employee_name IN VARCHAR2 ) AS v_employee_id employees.employee_id%TYPE := p_employee_id; v_employee_name employees.employee_name%TYPE := p_employee_name; BEGIN INSERT INTO employees (employee_id, employee_name) VALUES (v_employee_id, v_employee_name); END;
What will be the output of the following SQL query?
SELECT employee_name FROM employees WHERE employee_id IN (SELECT manager_id FROM employees);A.
Names of employees who are also managersB.
Names of employees who are not managersC.
Names of all employeesD.
Compilation Error
Which of the following code snippets demonstrates the correct way to handle exceptions in PL/SQL?
A.DECLARE v_number NUMBER; BEGIN v_number := 10 / 0; EXCEPTION WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('Division by zero error'); END;B.
BEGIN DECLARE v_number NUMBER; BEGIN v_number := 10 / 0; EXCEPTION WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('Division by zero error'); END; END;C.
DECLARE v_number NUMBER; BEGIN v_number := 10 / 0; IF SQLCODE = -204 THEN DBMS_OUTPUT.PUT_LINE('Division by zero error'); END IF; END;D.
DECLARE v_number NUMBER; BEGIN v_number := 10 / 0; END; EXCEPTION WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('Division by zero error'); END;
What will be the output of the following SQL query?
SELECT employee_name FROM employees WHERE employee_id NOT IN (SELECT manager_id FROM employees);A.
Names of employees who are not managersB.
Names of employees who are managersC.
Names of all employeesD.
Compilation Error
Which of the following code snippets demonstrates the correct way to create a function in PL/SQL that returns the square of a number?
A.CREATE FUNCTION Square(num IN NUMBER) RETURN NUMBER AS v_result NUMBER; BEGIN v_result := num * num; RETURN v_result; END;B.
CREATE FUNCTION Square(num NUMBER) RETURN NUMBER AS BEGIN RETURN num * num; END;C.
CREATE FUNCTION Square(num NUMBER) AS v_result NUMBER; BEGIN v_result := num * num; RETURN v_result; END;D.
CREATE FUNCTION Square(num NUMBER) RETURN NUMBER AS BEGIN RETURN num ^ 2; END;
What will be the output of the following PL/SQL code snippet?
DECLARE v_number NUMBER := 10; BEGIN IF v_number BETWEEN 1 AND 5 THEN DBMS_OUTPUT.PUT_LINE('Small number'); ELSIF v_number BETWEEN 6 AND 10 THEN DBMS_OUTPUT.PUT_LINE('Medium number'); ELSE DBMS_OUTPUT.PUT_LINE('Large number'); END IF; END;A.
Small numberB.
Medium numberC.
Large numberD.
Compilation Error
Which of the following code snippets demonstrates the correct way to update data in a table using a PL/SQL procedure?
A.CREATE PROCEDURE UpdateEmployee( p_employee_id IN employees.employee_id%TYPE, p_new_salary IN employees.salary%TYPE ) AS BEGIN UPDATE employees SET salary = p_new_salary WHERE employee_id = p_employee_id; END;B.
CREATE PROCEDURE UpdateEmployee( p_employee_id IN NUMBER, p_new_salary IN NUMBER ) AS BEGIN UPDATE employees SET salary = p_new_salary WHERE employee_id = p_employee_id; END;C.
CREATE PROCEDURE UpdateEmployee( employee_id IN NUMBER, new_salary IN NUMBER ) AS BEGIN UPDATE employees SET salary = new_salary WHERE employee_id = employee_id; END;D.
CREATE PROCEDURE UpdateEmployee( p_employee_id IN NUMBER, p_new_salary IN NUMBER ) AS v_employee_id employees.employee_id%TYPE := p_employee_id; v_new_salary employees.salary%TYPE := p_new_salary; BEGIN UPDATE employees SET salary = v_new_salary WHERE employee_id = v_employee_id; END;