Database: The physical collection of files (data files, control files, redo log files) stored on disk that contains the actual data and metadata.

Instance: The combination of memory structures (SGA) and background processes that manage access to the database. An instance is what runs in memory and manages the database files.

Key Points:

• One database can have multiple instances (RAC environment)
• An instance can exist without a database being mounted
• When you start Oracle, you start an instance, then mount and open a database

DELETE:

• Removes specific rows based on WHERE condition
• Can be rolled back (uses transaction log)
• Fires triggers
• Slower for large datasets

DELETE FROM employees WHERE department_id = 10;

TRUNCATE:

• Removes all rows from a table
• Cannot be rolled back (DDL statement)
• Doesn't fire triggers
• Much faster, resets high water mark

TRUNCATE TABLE employees;

DROP:

• Removes the entire table structure and data
• Cannot be rolled back
• Frees up storage space completely

DROP TABLE employees;

INNER JOIN: Returns records that have matching values in both tables

SELECT e.employee_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

LEFT JOIN: Returns all records from left table and matched records from right table

SELECT e.employee_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

RIGHT JOIN: Returns all records from right table and matched records from left table

SELECT e.employee_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

FULL OUTER JOIN: Returns all records when there's a match in either table

SELECT e.employee_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id;

UNION: Combines results from two queries and removes duplicate rows

SELECT employee_id FROM employees WHERE department_id = 10
UNION
SELECT employee_id FROM employees WHERE salary > 50000;

UNION ALL: Combines results from two queries and keeps all rows including duplicates

SELECT employee_id FROM employees WHERE department_id = 10
UNION ALL
SELECT employee_id FROM employees WHERE salary > 50000;

Key Differences:

• UNION performs duplicate elimination (slower)
• UNION ALL returns all rows (faster)
• UNION sorts the result set to eliminate duplicates
• Use UNION ALL when you know there are no duplicates or duplicates are acceptable

PL/SQL (Procedural Language/SQL) is Oracle's procedural extension to SQL. It combines the data manipulation power of SQL with the processing power of procedural languages.

Key Advantages:

• Integration: Seamlessly integrates with SQL
• Performance: Reduced network traffic by processing logic on database server
• Error Handling: Robust exception handling mechanisms
• Portability: Runs on any platform where Oracle runs
• Security: Stored procedures provide security layer
• Modularity: Code reusability through procedures and functions

Basic Structure:

DECLARE
    -- Variable declarations
    v_employee_name VARCHAR2(100);
BEGIN
    -- Executable statements
    SELECT first_name INTO v_employee_name 
    FROM employees 
    WHERE employee_id = 100;
    
    DBMS_OUTPUT.PUT_LINE(v_employee_name);
EXCEPTION
    -- Exception handling
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee not found');
END;
/