1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Connect to database
sqlplus username/password@database_name

-- Connect with sys privileges
sqlplus / as sysdba

-- Show current user
SHOW USER;

-- List all databases
SELECT name FROM v$database;

-- Show current connection details
SELECT sys_context('USERENV', 'CON_NAME') FROM dual;

1
2
3
4
5
6
7
8
9
# Start Oracle listener
lsnrctl start

# Stop Oracle listener
lsnrctl stop

# Check listener status
lsnrctl status

1
2
3
4
5
6
7
8
9
10
11
12
13
-- Create a new schema
CREATE USER enterprise_user IDENTIFIED BY StrongP@ssw0rd;

-- Grant privileges to schema
GRANT CONNECT, RESOURCE, CREATE TABLE TO enterprise_user;

-- Alter user password
ALTER USER enterprise_user IDENTIFIED BY NewPassword123;

-- Lock/Unlock user account
ALTER USER enterprise_user ACCOUNT LOCK;
ALTER USER enterprise_user ACCOUNT UNLOCK;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Basic table creation
CREATE TABLE employees (
    employee_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) CHECK (salary > 0)
);

-- Create table with foreign key
CREATE TABLE departments (
    dept_id NUMBER PRIMARY KEY,
    dept_name VARCHAR2(100),
    manager_id NUMBER,
    CONSTRAINT fk_manager 
        FOREIGN KEY (manager_id) 
        REFERENCES employees(employee_id)
);

1
2
3
4
5
6
7
8
9
10
11
12
-- Add column
ALTER TABLE employees ADD (phone_number VARCHAR2(20));

-- Modify column
ALTER TABLE employees MODIFY (email VARCHAR2(150));

-- Drop column
ALTER TABLE employees DROP COLUMN phone_number;

-- Rename table
RENAME employees TO company_employees;

1
2
3
-- Drop table with cascade constraints
DROP TABLE employees CASCADE CONSTRAINTS;

1
2
3
4
5
6
7
8
9
10
11
-- Common Oracle Data Types
NUMBER(p,s)    -- Numeric with precision and scale
VARCHAR2(size) -- Variable-length character string
CHAR(size)     -- Fixed-length character string
DATE           -- Date and time
TIMESTAMP      -- More precise date and time
RAW(size)      -- Binary data
CLOB           -- Character large object
BLOB           -- Binary large object
XMLTYPE        -- XML data type

1
2
3
4
5
6
7
8
9
10
11
12
-- Basic insert
INSERT INTO employees 
(employee_id, first_name, last_name, email, salary)
VALUES 
(1, 'John', 'Doe', 'john.doe@company.com', 75000);

-- Bulk insert
INSERT ALL
    INTO employees VALUES (1, 'John', 'Doe', 'john@co.com', 75000)
    INTO employees VALUES (2, 'Jane', 'Smith', 'jane@co.com', 85000)
SELECT * FROM dual;

1
2
3
4
5
6
7
8
9
10
11
12
13
-- Basic select
SELECT * FROM employees;

-- Filtered select
SELECT first_name, last_name, salary 
FROM employees 
WHERE salary > 50000 AND department = 'IT';

-- Pagination (Oracle 12c+)
SELECT * FROM employees
ORDER BY employee_id
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

1
2
3
4
5
6
7
8
9
10
11
12
13
-- Update with condition
UPDATE employees 
SET salary = salary * 1.10 
WHERE performance_rating = 'EXCELLENT';

-- Update with subquery
UPDATE departments d
SET manager_id = (
    SELECT employee_id 
    FROM employees e 
    WHERE e.last_name = 'Smith'
);

1
2
3
4
5
6
7
-- Delete with condition
DELETE FROM employees 
WHERE hire_date < ADD_MONTHS(SYSDATE, -120);

-- Truncate (faster than delete for entire table)
TRUNCATE TABLE temp_employees;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Inner Join
SELECT e.first_name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.dept_id;

-- Left Outer Join
SELECT e.first_name, d.dept_name
FROM employees e
LEFT OUTER JOIN departments d ON e.department_id = d.dept_id;

-- Full Outer Join
SELECT e.first_name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.dept_id;

-- Cross Join
SELECT e.first_name, p.product_name
FROM employees e
CROSS JOIN products p;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Create Index
CREATE INDEX idx_employee_name 
ON employees (last_name, first_name);

-- Unique Constraint
CREATE UNIQUE INDEX idx_unique_email 
ON employees (email);

-- Bitmap Index (good for low-cardinality columns)
CREATE BITMAP INDEX idx_department 
ON employees (department);

-- Constraints
ALTER TABLE employees
ADD CONSTRAINT chk_salary 
CHECK (salary BETWEEN 30000 AND 150000);

1
2
3
4
5
6
7
8
-- String manipulation
SELECT 
    UPPER(first_name) AS upper_name,
    LOWER(last_name) AS lower_name,
    INITCAP(email) AS proper_case,
    SUBSTR(first_name, 1, 3) AS name_prefix
FROM employees;

1
2
3
4
5
6
7
8
-- Date calculations
SELECT 
    SYSDATE,
    ADD_MONTHS(hire_date, 12) AS one_year_anniversary,
    MONTHS_BETWEEN(SYSDATE, hire_date) AS months_employed,
    LAST_DAY(SYSDATE) AS month_end
FROM employees;

1
2
3
4
5
6
7
-- Numeric operations
SELECT 
    ROUND(salary, -3) AS salary_rounded,
    TRUNC(salary, 0) AS salary_truncated,
    MOD(employee_id, 5) AS employee_group
FROM employees;

1
2
3
4
5
6
7
8
9
10
11
12
13
DECLARE
    v_employee_count NUMBER;
    v_avg_salary     NUMBER;
BEGIN
    SELECT COUNT(*), AVG(salary)
    INTO v_employee_count, v_avg_salary
    FROM employees;

    DBMS_OUTPUT.PUT_LINE('Total Employees: ' || v_employee_count);
    DBMS_OUTPUT.PUT_LINE('Average Salary: ' || v_avg_salary);
END;
/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE OR REPLACE PROCEDURE update_employee_salary (
    p_employee_id IN NUMBER,
    p_raise_percentage IN NUMBER
) AS
BEGIN
    UPDATE employees
    SET salary = salary * (1 + p_raise_percentage/100)
    WHERE employee_id = p_employee_id;
    
    COMMIT;
END;
/

-- Execute procedure
EXEC update_employee_salary(1001, 5);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE OR REPLACE TRIGGER trg_audit_employees
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit_log (
        action, employee_id, change_date
    ) VALUES (
        CASE 
            WHEN INSERTING THEN 'INSERT'
            WHEN UPDATING THEN 'UPDATE'
            WHEN DELETING THEN 'DELETE'
        END,
        :NEW.employee_id,
        SYSDATE
    );
END;
/

1
2
3
4
5
6
7
8
9
10
11
12
-- Create sequence
CREATE SEQUENCE seq_employee_id
START WITH 1
INCREMENT BY 1
MAXVALUE 999999
NOCACHE
NOCYCLE;

-- Use in insert
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (seq_employee_id.NEXTVAL, 'New', 'Employee');

1
2
3
4
5
6
7
8
9
10
11
12
13
-- Range Partitioning
CREATE TABLE sales_data (
    sale_date DATE,
    product_id NUMBER,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE (sale_date) (
    PARTITION sales_q1 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD')),
    PARTITION sales_q2 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD')),
    PARTITION sales_q3 VALUES LESS THAN (TO_DATE('2023-10-01', 'YYYY-MM-DD')),
    PARTITION sales_q4 VALUES LESS THAN (MAXVALUE)
);

1
2
3
4
5
6
7
8
9
10
11
CREATE MATERIALIZED VIEW mv_employee_summary
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS
SELECT 
    department_id, 
    COUNT(*) as employee_count, 
    AVG(salary) as avg_salary
FROM employees
GROUP BY department_id;

1
2
3
4
5
6
7
8
9
10
11
-- Organizational Hierarchy
SELECT 
    employee_id, 
    first_name, 
    last_name, 
    manager_id,
    LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

1
2
3
4
5
6
EXPLAIN PLAN FOR
SELECT * FROM employees 
WHERE department_id = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

1
2
3
4
5
6
-- Force index usage
SELECT /*+ INDEX(employees idx_employee_name) */ 
    first_name, last_name 
FROM employees 
WHERE last_name LIKE 'S%';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Create Role
CREATE ROLE enterprise_read_only;

-- Grant privileges to role
GRANT SELECT ON employees TO enterprise_read_only;

-- Create user with role
CREATE USER readonly_user 
IDENTIFIED BY ReadOnlyP@ss123
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;

GRANT enterprise_read_only TO readonly_user;