Understanding SQL and PL/SQL
Before diving into specific interview questions, it's essential to grasp the fundamental differences between SQL and PL/SQL.
What is SQL?
SQL (Structured Query Language) is a standard programming language used for managing and manipulating relational databases. It allows users to perform various operations such as querying data, updating records, and managing database schemas.
What is PL/SQL?
PL/SQL (Procedural Language/SQL) is an extension of SQL that provides additional programming constructs. It allows for the inclusion of procedural logic, enabling developers to write complex scripts and applications that interact with Oracle databases.
Common SQL Interview Questions
Here are some frequently asked SQL interview questions, along with their answers:
1. What is the difference between INNER JOIN and LEFT JOIN?
- INNER JOIN: Returns records that have matching values in both tables. If there is no match, the records are omitted.
- LEFT JOIN: Returns all records from the left table and the matched records from the right table. If there is no match, NULL values are returned for columns from the right table.
Example:
```sql
SELECT a., b.
FROM TableA a
LEFT JOIN TableB b ON a.id = b.a_id;
```
2. What are aggregate functions in SQL?
Aggregate functions perform a calculation on a set of values and return a single value. Common aggregate functions include:
- COUNT(): Counts the number of rows.
- SUM(): Calculates the total sum of a numeric column.
- AVG(): Computes the average value of a numeric column.
- MIN(): Finds the smallest value in a column.
- MAX(): Determines the largest value in a column.
3. Explain the concept of normalization and its types.
Normalization is the process of organizing data in a database to minimize redundancy and improve data integrity. The main types of normalization include:
1. First Normal Form (1NF): Ensures that all columns contain atomic values and that each entry is unique.
2. Second Normal Form (2NF): Achieves 1NF and ensures that all non-key attributes are fully functionally dependent on the primary key.
3. Third Normal Form (3NF): Achieves 2NF and removes transitive dependencies, ensuring that non-key attributes are not dependent on other non-key attributes.
Common PL/SQL Interview Questions
PL/SQL is widely used in Oracle databases, and understanding its features is essential for any developer. Here are some common PL/SQL interview questions:
1. What are the main advantages of using PL/SQL?
PL/SQL offers several advantages, including:
- Performance: PL/SQL programs run on the server, reducing network traffic and improving performance.
- Tight Integration: It integrates seamlessly with SQL, allowing for complex database operations.
- Error Handling: PL/SQL provides robust error handling mechanisms through exception handling.
- Modularity: PL/SQL supports modular programming through the use of procedures and functions.
2. What is a PL/SQL block?
A PL/SQL block is the basic unit of a PL/SQL program. It consists of three sections:
1. Declaration Section: Variables and constants are defined here.
2. Execution Section: The main logic is executed in this section, with SQL statements and PL/SQL logic.
3. Exception Handling Section: Errors are managed in this section, providing appropriate responses to exceptions.
Example:
```sql
DECLARE
v_employee_name VARCHAR2(50);
BEGIN
SELECT name INTO v_employee_name FROM employees WHERE id = 1;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee found.');
END;
```
3. What are cursors in PL/SQL?
Cursors are pointers that allow you to retrieve multiple rows from a query. There are two types of cursors:
- Implicit Cursors: Automatically created by Oracle when a SQL statement is executed. Used for single-row queries.
- Explicit Cursors: Defined by the user for queries that return multiple rows. They provide more control over the context area.
Example of explicit cursor:
```sql
DECLARE
CURSOR emp_cursor IS
SELECT name FROM employees;
v_name employees.name%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_name;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_name);
END LOOP;
CLOSE emp_cursor;
END;
```
Advanced SQL and PL/SQL Interview Questions
For candidates with more experience, advanced questions may arise. Here are some examples:
1. What is a trigger in PL/SQL?
A trigger is a stored procedure that automatically executes in response to specific events on a table or view, such as INSERT, UPDATE, or DELETE. Triggers are used for tasks like enforcing business rules or auditing changes.
Example:
```sql
CREATE OR REPLACE TRIGGER employee_audit
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
INSERT INTO audit_table (employee_id, change_time)
VALUES (:NEW.id, SYSDATE);
END;
```
2. Explain the difference between a procedure and a function in PL/SQL.
- Procedure: A procedure is a subprogram that performs an action and does not return a value. It can have input and output parameters.
- Function: A function is a subprogram that returns a single value and must have a return statement.
Example of a procedure:
```sql
CREATE OR REPLACE PROCEDURE update_salary(emp_id IN NUMBER, increase IN NUMBER) AS
BEGIN
UPDATE employees SET salary = salary + increase WHERE id = emp_id;
END;
```
Example of a function:
```sql
CREATE OR REPLACE FUNCTION get_employee_salary(emp_id IN NUMBER) RETURN NUMBER AS
v_salary NUMBER;
BEGIN
SELECT salary INTO v_salary FROM employees WHERE id = emp_id;
RETURN v_salary;
END;
```
3. What are collections in PL/SQL?
Collections are data structures that allow you to hold multiple values. There are three types of collections:
1. Associative Arrays: Key-value pairs that can be indexed by integers or strings.
2. Nested Tables: Similar to arrays but can be sparse. They can be stored in the database.
3. Varrays: Fixed-size arrays that can hold a set number of elements.
Example of a nested table:
```sql
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
emp_list emp_table;
BEGIN
SELECT BULK COLLECT INTO emp_list FROM employees;
FOR i IN 1..emp_list.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(emp_list(i).name);
END LOOP;
END;
```
Conclusion
Preparing for SQL and PL/SQL interviews involves understanding a range of questions from basic concepts to advanced features. Familiarity with SQL queries, PL/SQL blocks, cursors, and the differences between procedures and functions is crucial. This comprehensive guide to SQL PL/SQL interview questions and answers should provide you with a solid foundation for your upcoming interviews. Remember to practice coding and problem-solving to enhance your skills further, as hands-on experience is invaluable in any technical interview. Good luck!
Frequently Asked Questions
What is the difference between SQL and PL/SQL?
SQL (Structured Query Language) is a standard language used for managing and manipulating relational databases, while PL/SQL (Procedural Language/SQL) is an extension of SQL that adds procedural features, allowing for advanced programming constructs such as loops, conditions, and exception handling.
Can you explain what a cursor is in PL/SQL?
A cursor in PL/SQL is a pointer that allows you to retrieve and manipulate the result set of a SQL query row-by-row. There are two types of cursors: implicit cursors, which are automatically created by Oracle for single SQL statements, and explicit cursors, which you define when you need to handle multiple rows.
How do you handle exceptions in PL/SQL?
In PL/SQL, exceptions can be handled using the EXCEPTION block. You can define custom exception handling logic by declaring exceptions and using the WHEN clause to specify actions upon encountering those exceptions. The predefined exceptions like NO_DATA_FOUND and TOO_MANY_ROWS can also be used.
What is a stored procedure and how is it different from a function in PL/SQL?
A stored procedure is a compiled collection of SQL statements and optional control-flow statements stored in the database. It performs an action but does not return a value. A function, on the other hand, is also a stored program but must return a value and can be used in SQL expressions.
What is the significance of the %ROWTYPE attribute in PL/SQL?
The %ROWTYPE attribute is used in PL/SQL to declare a record that represents a single row of a database table. It allows you to retrieve all the columns of a table into a single variable, making it easier to work with the data without having to define each column individually.
