Building a Fleet Management System with Oracle SQL & PL/SQL

-- Core tables
CREATE TABLE drivers (
    driver_id    NUMBER PRIMARY KEY,
    name         VARCHAR2(100) NOT NULL,
    license_no   VARCHAR2(20) UNIQUE NOT NULL,
    phone        VARCHAR2(15),
    status       VARCHAR2(10) DEFAULT 'AVAILABLE'
                 CHECK (status IN ('AVAILABLE','BUSY','OFFLINE')),
    rating       NUMBER(3,2) DEFAULT 5.0,
    created_at   DATE DEFAULT SYSDATE
);

CREATE TABLE vehicles (
    vehicle_id   NUMBER PRIMARY KEY,
    driver_id    NUMBER REFERENCES drivers(driver_id),
    reg_number   VARCHAR2(10) UNIQUE NOT NULL,
    make         VARCHAR2(50),
    model        VARCHAR2(50),
    year         NUMBER(4),
    vehicle_type VARCHAR2(20) CHECK (vehicle_type IN ('STANDARD','PREMIUM','XL'))
);

CREATE TABLE bookings (
    booking_id     NUMBER PRIMARY KEY,
    customer_id    NUMBER REFERENCES customers(customer_id),
    driver_id      NUMBER REFERENCES drivers(driver_id),
    vehicle_id     NUMBER REFERENCES vehicles(vehicle_id),
    pickup_loc     VARCHAR2(200) NOT NULL,
    dropoff_loc    VARCHAR2(200) NOT NULL,
    booking_time   TIMESTAMP DEFAULT SYSTIMESTAMP,
    status         VARCHAR2(15) DEFAULT 'PENDING'
                   CHECK (status IN ('PENDING','CONFIRMED','IN_PROGRESS','COMPLETED','CANCELLED')),
    fare           NUMBER(8,2),
    distance_km    NUMBER(6,2)
);

CREATE SEQUENCE driver_seq START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE booking_seq START WITH 1000 INCREMENT BY 1;

-- Use in insert
INSERT INTO drivers (driver_id, name, license_no)
VALUES (driver_seq.NEXTVAL, 'John Smith', 'DL123456');

CREATE OR REPLACE TRIGGER trg_booking_driver_status
AFTER INSERT ON bookings
FOR EACH ROW
BEGIN
    IF :NEW.status = 'CONFIRMED' THEN
        UPDATE drivers
        SET status = 'BUSY'
        WHERE driver_id = :NEW.driver_id;
    END IF;
END;
/

CREATE OR REPLACE TRIGGER trg_calculate_fare
BEFORE UPDATE ON bookings
FOR EACH ROW
WHEN (NEW.status = 'COMPLETED')
DECLARE
    v_rate NUMBER;
BEGIN
    SELECT rate_per_km INTO v_rate
    FROM vehicle_rates
    WHERE vehicle_type = (
        SELECT vehicle_type FROM vehicles
        WHERE vehicle_id = :NEW.vehicle_id
    );
    
    :NEW.fare := :NEW.distance_km * v_rate;
    
    -- Free driver after completion
    UPDATE drivers SET status = 'AVAILABLE'
    WHERE driver_id = :NEW.driver_id;
END;
/

CREATE OR REPLACE PROCEDURE assign_driver(
    p_booking_id IN NUMBER,
    p_vehicle_type IN VARCHAR2
) AS
    v_driver_id NUMBER;
    v_vehicle_id NUMBER;
BEGIN
    -- Find available driver with matching vehicle type
    SELECT d.driver_id, v.vehicle_id
    INTO v_driver_id, v_vehicle_id
    FROM drivers d
    JOIN vehicles v ON d.driver_id = v.driver_id
    WHERE d.status = 'AVAILABLE'
    AND v.vehicle_type = p_vehicle_type
    AND ROWNUM = 1
    ORDER BY d.rating DESC;
    
    -- Assign to booking
    UPDATE bookings
    SET driver_id = v_driver_id,
        vehicle_id = v_vehicle_id,
        status = 'CONFIRMED'
    WHERE booking_id = p_booking_id;
    
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Driver ' || v_driver_id || ' assigned to booking ' || p_booking_id);
    
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No available drivers for vehicle type: ' || p_vehicle_type);
        ROLLBACK;
END assign_driver;
/

SELECT 
    d.name AS driver_name,
    v.vehicle_type,
    COUNT(b.booking_id) AS total_trips,
    ROUND(SUM(b.fare), 2) AS total_revenue,
    ROUND(AVG(b.fare), 2) AS avg_fare,
    ROUND(AVG(b.distance_km), 1) AS avg_distance,
    ROUND(AVG(d.rating), 2) AS driver_rating
FROM bookings b
JOIN drivers d ON b.driver_id = d.driver_id
JOIN vehicles v ON b.vehicle_id = v.vehicle_id
WHERE b.status = 'COMPLETED'
AND b.booking_time >= ADD_MONTHS(SYSDATE, -1)
GROUP BY d.name, v.vehicle_type
HAVING COUNT(b.booking_id) >= 5
ORDER BY total_revenue DESC;

SELECT 
    TO_CHAR(booking_time, 'HH24') AS hour_of_day,
    COUNT(*) AS booking_count,
    ROUND(AVG(fare), 2) AS avg_fare,
    RANK() OVER (ORDER BY COUNT(*) DESC) AS peak_rank
FROM bookings
WHERE status != 'CANCELLED'
GROUP BY TO_CHAR(booking_time, 'HH24')
ORDER BY booking_count DESC;