Search Tutorials


Oracle Quiz - MCQ - Multiple Choice Questions | JavaInUse

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 organization
B. 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. SELECT
B. 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 constraints
B. 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 integrity
B. 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 query
B. 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 columns
B. 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 results
B. 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 queries
B. 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 efficiently
B. 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 statements
B. 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. 
Positive
B. 
Negative
C. 
Zero
D. 
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 managers
B. 
Names of employees who are not managers
C. 
Names of all employees
D. 
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 managers
B. 
Names of employees who are managers
C. 
Names of all employees
D. 
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 number
B. 
Medium number
C. 
Large number
D. 
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;

Popular Posts





See Also