19.2 Sample Questions Explained
Key Concepts
1. SQL Query Execution
Understanding how SQL queries are executed in Oracle Database is crucial for optimizing performance. This includes understanding the query optimizer, execution plans, and the role of indexes.
Example:
Consider a query to retrieve employee details:
SELECT * FROM employees WHERE department_id = 10;
The query optimizer determines the best execution plan, which might involve using an index on the department_id column to speed up the query.
2. Data Types and Conversion
Oracle Database supports various data types, and understanding how to convert between them is essential. This includes implicit and explicit data type conversion.
Example:
Converting a string to a date:
SELECT TO_DATE('2023-10-01', 'YYYY-MM-DD') FROM dual;
This converts the string '2023-10-01' to a date data type.
3. Joins and Subqueries
Joins and subqueries are fundamental for combining data from multiple tables. Understanding the different types of joins (INNER, OUTER, etc.) and how to use subqueries effectively is key.
Example:
Using an INNER JOIN to combine data from two tables:
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;
This query retrieves employee details along with their department names.
4. Aggregation Functions
Aggregation functions like SUM, AVG, COUNT, etc., are used to perform calculations on groups of rows. Understanding how to use these functions and the GROUP BY clause is important.
Example:
Calculating the average salary by department:
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;
This query returns the average salary for each department.
5. Transactions and Concurrency
Transactions ensure data integrity by grouping SQL statements into logical units of work. Understanding transaction control statements (COMMIT, ROLLBACK) and concurrency issues is crucial.
Example:
Using COMMIT and ROLLBACK:
BEGIN
UPDATE employees SET salary = salary + 1000 WHERE employee_id = 101;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
This block updates the salary of an employee and commits the transaction, rolling back if an error occurs.
6. Indexing and Performance Tuning
Indexes improve query performance by providing faster access to data. Understanding how to create and use indexes effectively is essential for performance tuning.
Example:
Creating an index on a column:
CREATE INDEX idx_employee_name ON employees(first_name);
This index speeds up queries that filter or sort by the first_name column.
7. PL/SQL Basics
PL/SQL is Oracle's procedural language extension for SQL. Understanding basic PL/SQL constructs like blocks, variables, and control structures is important.
Example:
A simple PL/SQL block:
DECLARE
v_name VARCHAR2(50);
BEGIN
SELECT first_name INTO v_name FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE(v_name);
END;
This block retrieves the first name of an employee and prints it.
8. Triggers and Constraints
Triggers are PL/SQL blocks that execute automatically in response to specific events. Constraints enforce data integrity rules. Understanding both is important for maintaining data integrity.
Example:
Creating a trigger:
CREATE OR REPLACE TRIGGER check_salary_before_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary < 1000 THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary too low');
END IF;
END;
This trigger prevents the insertion of employees with a salary less than 1000.
9. Views and Materialized Views
Views are virtual tables based on the result of a query. Materialized views store the result of a query physically. Understanding their use cases and differences is important.
Example:
Creating a view:
CREATE VIEW employee_details AS
SELECT employee_id, first_name, last_name, department_id
FROM employees;
This view provides a simplified way to access employee details.
10. Stored Procedures and Functions
Stored procedures and functions are PL/SQL blocks stored in the database. They can be called with parameters and are useful for encapsulating business logic.
Example:
Creating a stored procedure:
CREATE OR REPLACE PROCEDURE increase_salary (emp_id IN NUMBER, inc_amount IN NUMBER) IS
BEGIN
UPDATE employees SET salary = salary + inc_amount WHERE employee_id = emp_id;
END;
This procedure increases the salary of an employee by a specified amount.
11. Cursors and Cursor Variables
Cursors are used to handle the result sets of SQL queries. Cursor variables allow for more dynamic and flexible handling of result sets.
Example:
Using a cursor:
DECLARE
CURSOR emp_cur IS SELECT * FROM employees;
emp_rec emp_cur%ROWTYPE;
BEGIN
OPEN emp_cur;
LOOP
FETCH emp_cur INTO emp_rec;
EXIT WHEN emp_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(emp_rec.first_name);
END LOOP;
CLOSE emp_cur;
END;
This block fetches and prints the first names of all employees.
12. Dynamic SQL
Dynamic SQL allows for the construction and execution of SQL statements at runtime. This is useful when the SQL statement is not known until runtime.
Example:
Using dynamic SQL:
DECLARE
sql_stmt VARCHAR2(100);
emp_name VARCHAR2(50);
BEGIN
sql_stmt := 'SELECT first_name FROM employees WHERE employee_id = :id';
EXECUTE IMMEDIATE sql_stmt INTO emp_name USING 101;
DBMS_OUTPUT.PUT_LINE(emp_name);
END;
This block retrieves the first name of an employee using dynamic SQL.
13. Bulk Operations
Bulk operations allow for processing multiple rows of data in a single operation, improving performance by reducing context switches between PL/SQL and SQL engines.
Example:
Using BULK COLLECT:
DECLARE
TYPE emp_id_table IS TABLE OF employees.employee_id%TYPE;
emp_ids emp_id_table;
BEGIN
SELECT employee_id BULK COLLECT INTO emp_ids FROM employees WHERE department_id = 10;
FOR i IN 1..emp_ids.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(emp_ids(i));
END LOOP;
END;
This block retrieves all employee IDs in department 10 using BULK COLLECT.
14. User-Defined Exceptions
User-defined exceptions allow for creating custom error conditions that can be raised and handled within PL/SQL code.
Example:
Using a user-defined exception:
DECLARE
invalid_salary EXCEPTION;
v_salary NUMBER(8,2);
BEGIN
SELECT salary INTO v_salary FROM employees WHERE employee_id = 101;
IF v_salary < 1000 THEN
RAISE invalid_salary;
END IF;
EXCEPTION
WHEN invalid_salary THEN
DBMS_OUTPUT.PUT_LINE('Invalid Salary');
END;
This block raises a user-defined exception if the salary is less than 1000.
15. Pipelined Functions
Pipelined functions return a set of values one at a time, allowing for efficient processing of large datasets.
Example:
Creating a pipelined function:
CREATE OR REPLACE FUNCTION get_employees RETURN SYS.ODCINUMBERLIST PIPELINED IS
BEGIN
FOR rec IN (SELECT employee_id FROM employees) LOOP
PIPE ROW(rec.employee_id);
END LOOP;
RETURN;
END;
This function returns employee IDs one at a time.
16. Object-Oriented Features
Oracle Database supports object-oriented programming (OOP) features like objects, methods, and inheritance.
Example:
Creating an object type:
CREATE OR REPLACE TYPE t_employee AS OBJECT (
id NUMBER,
name VARCHAR2(50),
MEMBER FUNCTION get_details RETURN VARCHAR2
);
This creates an object type for an employee.
17. Collections
Collections in PL/SQL are similar to arrays in other programming languages. They allow for storing and manipulating groups of related data items.
Example:
Using a collection (VARRAY):
DECLARE
TYPE name_array IS VARRAY(5) OF VARCHAR2(50);
names name_array := name_array('Alice', 'Bob', 'Charlie', 'David', 'Eve');
BEGIN
FOR i IN 1..names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(names(i));
END LOOP;
END;
This block prints the names stored in a VARRAY.
18. Records
Records are composite data types that allow for grouping related data items into a single variable.
Example:
Using a record:
DECLARE
TYPE emp_rec IS RECORD (
emp_id NUMBER,
name VARCHAR2(50),
salary NUMBER(8,2)
);
emp emp_rec;
BEGIN
SELECT employee_id, first_name, salary INTO emp FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE(emp.name);
END;
This block retrieves and prints the name of an employee using a record.
19. Security and Privileges
Security in Oracle Database involves controlling access to database objects and ensuring that sensitive data is protected. This includes using roles, privileges, and encryption.
Example:
Granting and revoking privileges:
GRANT SELECT ON employees TO hr_user;
REVOKE SELECT ON employees FROM hr_user;
This grants and revokes SELECT privilege on the employees table to a user.