Sunday, February 16, 2025

Oracle SQL and PL/SQL Interview questions and Answers

Important interview questions and answers based on Oracle SQL and PL/SQL concepts such as DDL, DML, SELECT, JOINS, FUNCTIONS, SUBQUERY, VIEWS, SEQUENCE, CURSOR, NORMALIZATION, PLSQL INTRO, PROCEDURE, FUNCTIONS, and EXCEPTIONS.

1. DDL (Data Definition Language)

Q1: What is DDL, and can you name some DDL commands in Oracle?

A: DDL (Data Definition Language) is used to define and modify database structures. Some common DDL commands are:

  • CREATE (to create a table, index, or schema)
  • ALTER (to modify an existing object)
  • DROP (to delete objects from the database)
  • TRUNCATE (to remove all records from a table without logging)
  • RENAME (to rename database objects)

Q2: What is the difference between DROP, TRUNCATE, and DELETE?

A:

  • DROP: Completely removes a table and its structure from the database.
  • TRUNCATE: Deletes all records from a table but retains its structure; it is faster than DELETE as it does not log individual row deletions.
  • DELETE: Removes specific records from a table and can be rolled back if within a transaction.

2. DML (Data Manipulation Language)

Q3: What are DML commands?

A: DML commands manipulate data within tables. Examples include:

  • INSERT (adds new records)
  • UPDATE (modifies existing records)
  • DELETE (removes specific records)
  • MERGE (performs UPSERT – update if exists, insert otherwise)

Q4: How can you update multiple rows in a table in Oracle?

A:

sql
UPDATE employees 
SET salary = salary * 1.10 
WHERE department_id = 10;

This increases salaries by 10% for all employees in department 10.

3. SELECT Statement

Q5: What is the difference between WHERE and HAVING in SQL?

A:

  • WHERE is used to filter rows before aggregation.
  • HAVING is used to filter groups after aggregation.

Example:

sql
SELECT department_id, COUNT(*)  
FROM employees  
WHERE salary > 3000  
GROUP BY department_id  
HAVING COUNT(*) > 5;

This counts employees in each department with a salary above 3000 and filters groups having more than 5 employees.

4. JOINS

Q6: What are the types of joins in Oracle SQL?

A: The main types of joins are:

  • INNER JOIN: Returns only matching records from both tables.
  • LEFT OUTER JOIN: Returns all records from the left table and matching records from the right.
  • RIGHT OUTER JOIN: Returns all records from the right table and matching records from the left.
  • FULL OUTER JOIN: Returns all records from both tables.
  • CROSS JOIN: Returns the Cartesian product of both tables.

Q7: Write an example query using INNER JOIN.

A:

sql
SELECT e.employee_id, e.name, d.department_name  
FROM employees e  
INNER JOIN departments d ON e.department_id = d.department_id;

This retrieves employees along with their department names.

5. SQL Functions

Q8: What are aggregate and scalar functions in SQL?

A:

  • Aggregate functions operate on multiple rows and return a single value (e.g., SUM(), AVG(), COUNT(), MAX(), MIN()).
  • Scalar functions operate on a single value and return a single value (e.g., UPPER(), LOWER(), LENGTH(), ROUND()).

Q9: Write a query to find the highest salary in each department.

A:

sql
SELECT department_id, MAX(salary) AS highest_salary  
FROM employees  
GROUP BY department_id;

6. Subqueries

Q10: What is a subquery?

A: A subquery is a query within another SQL query. It is used to retrieve data that will be used in the main query.

Q11: Write a query to find employees who earn more than the average salary.

A:



SELECT name, salary  
FROM employees  
WHERE salary > (SELECT AVG(salary) FROM employees);

7. Views

Q12: What is a View in Oracle SQL?

A: A view is a virtual table based on the result of a SQL query.

Q13: How do you create and use a view?

A:


CREATE VIEW high_paid_employees AS  
SELECT name, salary FROM employees WHERE salary > 5000;

To use:


SELECT * FROM high_paid_employees;

8. Sequences

Q14: What is a sequence in Oracle, and why is it used?

A: A sequence is a database object that generates unique numeric values, often used for primary keys.

Q15: How do you create and use a sequence?

A:

sql
CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1;
INSERT INTO employees (employee_id, name) VALUES (emp_seq.NEXTVAL, 'John Doe');

9. Cursor

Q16: What is a cursor in PL/SQL?

A: A cursor is a pointer to a result set in memory that allows row-by-row processing.

Q17: Write an example of an explicit cursor.

A:

plsql
DECLARE  
    CURSOR emp_cursor IS SELECT name, salary FROM employees;  
    emp_record emp_cursor%ROWTYPE;  
BEGIN  
    OPEN emp_cursor;  
    LOOP  
        FETCH emp_cursor INTO emp_record;  
        EXIT WHEN emp_cursor%NOTFOUND;  
        DBMS_OUTPUT.PUT_LINE(emp_record.name || ' earns ' || emp_record.salary);  
    END LOOP;  
    CLOSE emp_cursor;  
END;

10. Normalization

Q18: What is normalization, and why is it important?

A: Normalization organizes data in a database to eliminate redundancy and improve integrity. It follows different normal forms (1NF, 2NF, 3NF, BCNF, etc.).

Q19: What is the difference between 1NF, 2NF, and 3NF?

A:

  • 1NF: Ensures atomicity (each column has unique values).
  • 2NF: Removes partial dependencies (non-key columns should depend on the whole primary key).
  • 3NF: Removes transitive dependencies (non-key columns should not depend on other non-key columns).

11. PL/SQL Procedures and Functions

Q20: What is the difference between a procedure and a function in PL/SQL?

A:

  • A procedure performs an action but does not return a value.
  • A function must return a value.

Q21: How do you create a stored procedure in PL/SQL?

A:

plsql
CREATE PROCEDURE increase_salary (p_emp_id NUMBER, p_percent NUMBER) AS  
BEGIN  
    UPDATE employees SET salary = salary * (1 + p_percent / 100) WHERE employee_id = p_emp_id;  
END;

12. PL/SQL Exception Handling

Q22: What are the types of exceptions in PL/SQL?

A:

  1. Predefined Exceptions (e.g., NO_DATA_FOUND, TOO_MANY_ROWS).
  2. User-Defined Exceptions (declared using EXCEPTION).

Q23: Provide an example of exception handling in PL/SQL.

A:

plsql
BEGIN  
    UPDATE employees SET salary = salary * 1.10 WHERE employee_id = 100;  
EXCEPTION  
    WHEN NO_DATA_FOUND THEN  
        DBMS_OUTPUT.PUT_LINE('No employee found!');  
    WHEN OTHERS THEN  
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);  
END;

1. DDL (Data Definition Language)

Q1: Can we use ALTER to change the data type of a column that already contains data?

A: Yes, but with conditions:

  • The new data type must be compatible with the existing data.
  • If changing from VARCHAR2(10) to VARCHAR2(20), it's allowed.
  • If changing from NUMBER to VARCHAR2, it fails if non-numeric data exists.

Example:

sql
ALTER TABLE employees MODIFY (salary VARCHAR2(20));

Q2: What happens when you DROP a table that is referenced by a foreign key?

A:

  • If the foreign key has ON DELETE CASCADE, dependent records are also deleted.
  • If ON DELETE SET NULL is used, foreign key values are set to NULL.
  • Without these, the DROP fails unless the foreign key constraint is first removed.

2. DML (Data Manipulation Language)

Q3: What happens if you execute DELETE FROM employees; and then rollback?

A:

  • All records are restored if inside a transaction.
  • If TRUNCATE was used instead, rollback wouldn't work.

Q4: How do you insert multiple rows into a table in a single query?

A:

sql
INSERT ALL  
    INTO employees (employee_id, name, salary) VALUES (101, 'John Doe', 5000)  
    INTO employees (employee_id, name, salary) VALUES (102, 'Jane Doe', 6000)  
SELECT * FROM DUAL;

3. SELECT Queries

Q5: How do you retrieve the second highest salary from an employees table?

A:

sql
SELECT MAX(salary) 
FROM employees 
WHERE salary < (SELECT MAX(salary) FROM employees);

Q6: How do you find duplicate records in a table?

A:

sql
SELECT name, COUNT(*)  
FROM employees  
GROUP BY name  
HAVING COUNT(*) > 1;

4. JOINS

Q7: What is the difference between USING and ON in a JOIN?

A:

  • USING: Used when column names are the same in both tables.
  • ON: Used when column names are different.

Example:

sql
SELECT e.employee_id, d.department_name  
FROM employees e  
JOIN departments d USING (department_id);

vs

sql
SELECT e.employee_id, d.department_name  
FROM employees e  
JOIN departments d ON e.dept_id = d.department_id;

Q8: How do you perform a self-join to find employees who report to the same manager?

A:

sql
SELECT e1.name AS Employee, e2.name AS Colleague, e1.manager_id  
FROM employees e1  
JOIN employees e2 ON e1.manager_id = e2.manager_id  
WHERE e1.employee_id <> e2.employee_id;

5. SQL Functions

Q9: How do you concatenate strings in Oracle SQL?

A:

sql
SELECT first_name || ' ' || last_name AS full_name FROM employees;

Q10: How do you extract the first three characters from a column?

A:

sql
SELECT SUBSTR(name, 1, 3) FROM employees;

6. Subqueries

Q11: What is the difference between EXISTS and IN?

A:

  • EXISTS: Stops as soon as a match is found.
  • IN: Compares all values before filtering.

Example:

sql
SELECT name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'NY');

vs

sql
SELECT name FROM employees WHERE EXISTS (SELECT 1 FROM departments WHERE employees.department_id = departments.department_id AND location = 'NY');

7. Views

Q12: Can we update data in a View?

A: Yes, but:

  • The View must be based on a single table.
  • It must not contain DISTINCT, GROUP BY, or HAVING.

Q13: How do you create a View with an alias for a column?

A:

sql
CREATE VIEW employee_info AS  
SELECT name AS emp_name, salary AS emp_salary FROM employees;

8. Sequences

Q14: Can we reset a sequence in Oracle?

A: No direct reset, but a workaround:

sql
DROP SEQUENCE emp_seq;  
CREATE SEQUENCE emp_seq START WITH 1;

9. Cursor

Q15: What happens if you forget to close a cursor in PL/SQL?

A: It stays open in session memory and can lead to memory leaks.

Q16: How do you use a cursor with parameters?

A:

plsql
DECLARE  
    CURSOR emp_cursor(p_dept_id NUMBER) IS  
    SELECT name FROM employees WHERE department_id = p_dept_id;  
BEGIN  
    FOR rec IN emp_cursor(10) LOOP  
        DBMS_OUTPUT.PUT_LINE(rec.name);  
    END LOOP;  
END;

10. Normalization

Q17: How do you identify if a table is in 3NF?

A: A table is in 3NF if:

  1. It is in 2NF.
  2. It has no transitive dependencies (non-key attributes do not depend on other non-key attributes).

11. PL/SQL Procedures and Functions

Q18: Can a function have OUT parameters in PL/SQL?

A: No, a function must return a value, but a procedure can have OUT parameters.

Q19: How do you call a procedure inside another procedure?

A:

plsql
BEGIN  
    my_procedure(param1, param2);  
END;

12. Exception Handling

Q20: How do you create a user-defined exception in PL/SQL?

A:

plsql
DECLARE  
    ex_salary EXCEPTION;  
BEGIN  
    IF salary < 3000 THEN  
        RAISE ex_salary;  
    END IF;  
EXCEPTION  
    WHEN ex_salary THEN  
        DBMS_OUTPUT.PUT_LINE('Salary is too low!');  
END;

Q21: What is the difference between RAISE and RAISE_APPLICATION_ERROR?

A:

  • RAISE throws predefined exceptions.
  • RAISE_APPLICATION_ERROR(-20001, 'Custom Error Message'); allows custom error messages.

Bonus: Trick Questions

Q22: Can a View be created without a table?

A: No, a View must always be based on an existing table.

Q23: What will happen if you use NULL in an arithmetic operation?

A: The result will always be NULL.

Example:

sql
SELECT 10 + NULL FROM DUAL; -- Result: NULL

These tricky questions will help you stand out in Oracle SQL and PL/SQL interviews.

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)