01_setup.sql

-- Car Wash Database Setup Script
-- Creates tables and sample data for PL/SQL demo

BEGIN EXECUTE IMMEDIATE 'DROP TABLE carwash_services CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN NULL; END; /
BEGIN EXECUTE IMMEDIATE 'DROP TABLE carwash_orders CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN NULL; END; /
BEGIN EXECUTE IMMEDIATE 'DROP TABLE carwash_customers CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN NULL; END; /

CREATE TABLE carwash_customers (
    customer_id NUMBER PRIMARY KEY,
    full_name   VARCHAR2(100),
    phone       VARCHAR2(20)
);

CREATE TABLE carwash_services (
    service_id NUMBER PRIMARY KEY,
    service_name VARCHAR2(50),
    price NUMBER
);

CREATE TABLE carwash_orders (
    order_id NUMBER PRIMARY KEY,
    customer_id NUMBER REFERENCES carwash_customers(customer_id),
    service_id NUMBER REFERENCES carwash_services(service_id),
    order_date DATE
);

INSERT INTO carwash_customers VALUES (1, 'John Mugabe', '078888888');
INSERT INTO carwash_customers VALUES (2, 'Alice Uwimana', '079999999');
INSERT INTO carwash_customers VALUES (3, 'Eric Ndayishimiye', '078123456');

INSERT INTO carwash_services VALUES (1, 'Basic Wash', 5000);
INSERT INTO carwash_services VALUES (2, 'Full Wash', 10000);
INSERT INTO carwash_services VALUES (3, 'Interior Cleaning', 15000);
INSERT INTO carwash_services VALUES (4, 'Engine Wash', 20000);
INSERT INTO carwash_services VALUES (5, 'VIP Package', 30000);

INSERT INTO carwash_orders VALUES (101, 1, 1, SYSDATE - 2);
INSERT INTO carwash_orders VALUES (102, 1, 3, SYSDATE - 1);
INSERT INTO carwash_orders VALUES (103, 2, 5, SYSDATE);
INSERT INTO carwash_orders VALUES (104, 3, 2, SYSDATE);

COMMIT;