Oracle Database Guide — SQL, PL/SQL, and Enterprise Features

# Connect as system administrator
sqlplus system/your_password@localhost:1521/XEPDB1

# Expected output:
# Connected to:
# Oracle Database 21c Express Edition Release 21.0.0.0.0

-- View your current user and database
SELECT USER, SYSDATE FROM DUAL;

-- Output:
-- USER    | SYSDATE
-- SYSTEM  | 2026-06-07 10:30:00

-- Create a customers table with Oracle features
CREATE TABLE customers (
    customer_id NUMBER(10) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL,
    email VARCHAR2(100) NOT NULL,
    phone VARCHAR2(20),
    credit_limit NUMBER(10, 2) CHECK (credit_limit > 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR2(20) DEFAULT 'ACTIVE'
);

-- Create an orders table
CREATE TABLE orders (
    order_id NUMBER(10) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    customer_id NUMBER(10) NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount NUMBER(12, 2) NOT NULL,
    status VARCHAR2(20) DEFAULT 'PENDING',
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Add a comment on a column
COMMENT ON COLUMN customers.credit_limit IS 'Maximum credit allowed for this customer';

-- Create a sequence
CREATE SEQUENCE customer_seq
    START WITH 1000
    INCREMENT BY 1
    NOCACHE
    NOCYCLE;

-- Use the sequence in an INSERT
INSERT INTO customers (customer_id, first_name, last_name, email)
VALUES (customer_seq.NEXTVAL, 'Alice', 'Johnson', 'alice@example.com');

-- Check the current value without incrementing
SELECT customer_seq.CURRVAL FROM DUAL;

CREATE OR REPLACE PROCEDURE apply_credit_increase (
    p_customer_id NUMBER,
    p_increase_percent NUMBER
) AS
    v_current_limit NUMBER(10, 2);
BEGIN
    -- Get the current credit limit
    SELECT credit_limit INTO v_current_limit
    FROM customers
    WHERE customer_id = p_customer_id;

    -- Update with the increase
    UPDATE customers
    SET credit_limit = v_current_limit * (1 + p_increase_percent / 100)
    WHERE customer_id = p_customer_id;

    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Credit limit increased for customer ' || p_customer_id);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Customer not found: ' || p_customer_id);
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END apply_credit_increase;
/

-- Call the procedure
EXEC apply_credit_increase(1000, 10);

-- B-tree index (default) — good for equality and range queries
CREATE INDEX idx_customers_email ON customers(email);

-- Bitmap index — good for low-cardinality columns (status, gender)
CREATE BITMAP INDEX idx_orders_status ON orders(status);

-- Function-based index — index on an expression
CREATE INDEX idx_customers_upper_email ON customers(UPPER(email));

-- Check index usage
SELECT index_name, index_type, uniqueness
FROM user_indexes
WHERE table_name = 'CUSTOMERS';

CREATE OR REPLACE VIEW vw_customer_orders AS
SELECT
    c.customer_id,
    c.first_name || ' ' || c.last_name AS full_name,
    COUNT(o.order_id) AS order_count,
    NVL(SUM(o.total_amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name;

-- Query the view (looks like a table)
SELECT * FROM vw_customer_orders WHERE total_spent > 1000;

-- Refreshed daily
CREATE MATERIALIZED VIEW mv_monthly_sales
REFRESH COMPLETE
START WITH SYSDATE
NEXT SYSDATE + 1
AS
SELECT
    TRUNC(order_date, 'MM') AS month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue
FROM orders
GROUP BY TRUNC(order_date, 'MM');

-- The table might not exist, or you don't have SELECT privilege
SELECT * FROM secret_table;
-- ORA-00942: table or view does not exist

INSERT INTO customers (customer_id, email) VALUES (1000, 'alice@example.com');
-- If customer_id 1000 or email already exists:
-- ORA-00001: unique constraint (SYSTEM.SYS_C0012345) violated

-- WRONG — Oracle doesn't support JOIN ... USING with AS table alias before 23c
SELECT * FROM orders o JOIN customers c USING (customer_id);

-- RIGHT
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.customer_id;

INSERT INTO customers (first_name, last_name) VALUES (NULL, 'Smith');
-- ORA-01400: cannot insert NULL into ("SYSTEM"."CUSTOMERS"."FIRST_NAME")

SELECT * FROM customers WHERE customer_id = 'ABC';
-- ORA-01722: invalid number

BEGIN
    FOR rec IN (SELECT first_name, last_name FROM customers) LOOP
        DBMS_OUTPUT.PUT_LINE('Customer: ' || rec.first_name || ' ' || rec.last_name);
    END LOOP;
END;
/

-- Audit log table
CREATE TABLE audit_log (
    audit_id NUMBER(10) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    table_name VARCHAR2(100) NOT NULL,
    action VARCHAR2(10) NOT NULL CHECK (action IN ('INSERT', 'UPDATE', 'DELETE')),
    record_id NUMBER(10) NOT NULL,
    old_values CLOB,
    new_values CLOB,
    changed_by VARCHAR2(100) DEFAULT USER,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Trigger to capture changes on customers table
CREATE OR REPLACE TRIGGER trg_customers_audit
AFTER INSERT OR UPDATE OR DELETE ON customers
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        INSERT INTO audit_log (table_name, action, record_id, new_values)
        VALUES ('CUSTOMERS', 'INSERT', :NEW.customer_id,
                'first_name=' || :NEW.first_name || ', email=' || :NEW.email);
    ELSIF UPDATING THEN
        INSERT INTO audit_log (table_name, action, record_id,
                old_values, new_values)
        VALUES ('CUSTOMERS', 'UPDATE', :OLD.customer_id,
                'first_name=' || :OLD.first_name,
                'first_name=' || :NEW.first_name);
    ELSIF DELETING THEN
        INSERT INTO audit_log (table_name, action, record_id, old_values)
        VALUES ('CUSTOMERS', 'DELETE', :OLD.customer_id,
                'first_name=' || :OLD.first_name || ', email=' || :OLD.email);
    END IF;
END;
/

-- Which customers haven't ordered anything?
SELECT c.first_name, c.last_name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

-- Monthly order summary
SELECT
    TO_CHAR(order_date, 'YYYY-MM') AS month,
    COUNT(*) AS orders,
    SUM(total_amount) AS revenue
FROM orders
GROUP BY TO_CHAR(order_date, 'YYYY-MM')
ORDER BY month;

-- Customer ranking by total spend
SELECT
    first_name || ' ' || last_name AS customer,
    NVL(SUM(total_amount), 0) AS total_spent,
    RANK() OVER (ORDER BY NVL(SUM(total_amount), 0) DESC) AS rank
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, first_name, last_name;