General SQL Operations

Basic Create, Read, Update, Delete operations (Access: Table privileges required)

-- Create table
-- Access: Users with CREATE TABLE privilege
CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL,
    email VARCHAR2(100) UNIQUE,
    hire_date DATE DEFAULT SYSDATE,
    salary NUMBER(10,2),
    department_id NUMBER
);

-- Insert data
-- Access: Users with INSERT privilege on table
INSERT INTO employees (emp_id, first_name, last_name, email, salary, department_id)
VALUES (1, 'John', 'Doe', 'john.doe@company.com', 50000, 10);

-- Insert multiple rows
INSERT ALL
    INTO employees VALUES (2, 'Jane', 'Smith', 'jane.smith@company.com', 55000, 10)
    INTO employees VALUES (3, 'Bob', 'Johnson', 'bob.johnson@company.com', 60000, 20)
SELECT * FROM dual;

-- Update data
-- Access: Users with UPDATE privilege on table
UPDATE employees 
SET salary = salary * 1.1 
WHERE department_id = 10;

-- Delete data
-- Access: Users with DELETE privilege on table
DELETE FROM employees WHERE emp_id = 1;

-- Select data
-- Access: Users with SELECT privilege on table
SELECT 
    emp_id,
    first_name,
    last_name,
    email,
    salary,
    department_id
FROM employees
WHERE department_id = 10
ORDER BY salary DESC;

Advanced query techniques with joins, subqueries, and window functions (Access: SELECT privilege required)

-- Joins
-- Access: Users with SELECT privilege on involved tables
SELECT 
    e.first_name,
    e.last_name,
    e.salary,
    d.department_name,
    m.first_name || ' ' || m.last_name AS manager_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
LEFT JOIN employees m ON e.manager_id = m.emp_id
WHERE e.salary > 50000
ORDER BY e.salary DESC;

-- Subqueries
-- Access: Users with SELECT privilege on involved tables
SELECT 
    first_name,
    last_name,
    salary
FROM employees
WHERE salary > (
    SELECT AVG(salary) 
    FROM employees
);

-- Window functions
-- Access: Users with SELECT privilege on table
SELECT 
    first_name,
    last_name,
    salary,
    department_id,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_in_dept,
    RANK() OVER (ORDER BY salary DESC) AS overall_rank,
    LAG(salary) OVER (ORDER BY salary) AS prev_salary,
    LEAD(salary) OVER (ORDER BY salary) AS next_salary
FROM employees
ORDER BY salary DESC;

-- Common Table Expressions (CTE)
-- Access: Users with SELECT privilege on table
WITH dept_stats AS (
    SELECT 
        department_id,
        COUNT(*) AS emp_count,
        AVG(salary) AS avg_salary,
        MAX(salary) AS max_salary
    FROM employees
    GROUP BY department_id
)
SELECT 
    d.department_name,
    ds.emp_count,
    ds.avg_salary,
    ds.max_salary
FROM departments d
JOIN dept_stats ds ON d.department_id = ds.department_id
ORDER BY ds.avg_salary DESC;

Advanced data manipulation operations (Access: Appropriate table privileges required)

-- Merge statement
-- Access: Users with INSERT, UPDATE, DELETE privileges on table
MERGE INTO employees e
USING (
    SELECT emp_id, first_name, last_name, email, salary, department_id
    FROM new_employees
) n ON (e.emp_id = n.emp_id)
WHEN MATCHED THEN
    UPDATE SET 
        first_name = n.first_name,
        last_name = n.last_name,
        email = n.email,
        salary = n.salary,
        department_id = n.department_id
WHEN NOT MATCHED THEN
    INSERT (emp_id, first_name, last_name, email, salary, department_id)
    VALUES (n.emp_id, n.first_name, n.last_name, n.email, n.salary, n.department_id);

-- Bulk operations
-- Access: Users with appropriate table privileges
-- Insert with bulk collect
DECLARE
    TYPE emp_array IS TABLE OF employees%ROWTYPE;
    emp_data emp_array;
BEGIN
    SELECT * BULK COLLECT INTO emp_data
    FROM employees
    WHERE department_id = 10;
    
    FORALL i IN 1..emp_data.COUNT
        INSERT INTO emp_backup VALUES emp_data(i);
END;
/

-- Update with case statement
-- Access: Users with UPDATE privilege on table
UPDATE employees
SET salary = CASE 
    WHEN department_id = 10 THEN salary * 1.1
    WHEN department_id = 20 THEN salary * 1.05
    ELSE salary * 1.02
END
WHERE hire_date < DATE '2020-01-01';

-- Delete with exists
-- Access: Users with DELETE privilege on table
DELETE FROM employees e
WHERE EXISTS (
    SELECT 1 FROM departments d
    WHERE d.department_id = e.department_id
    AND d.department_name = 'IT'
);