CREATE TABLE CATEGORY ( CATEGORY_ID NUMBER PRIMARY KEY, CATEGORY_NAME VARCHAR2(50) NOT NULL, DESCRIPTION VARCHAR2(200) );

CREATE TABLE PRODUCT ( PRODUCT_ID NUMBER PRIMARY KEY, PRODUCT_NAME VARCHAR2(100) NOT NULL, PRICE NUMBER(10,2) NOT NULL, QUANTITY NUMBER NOT NULL, CATEGORY_ID NUMBER, CONSTRAINT FK_PRODUCT_CATEGORY FOREIGN KEY (CATEGORY_ID) REFERENCES CATEGORY(CATEGORY_ID) );

CREATE TABLE CUSTOMER ( CUSTOMER_ID NUMBER PRIMARY KEY, FIRST_NAME VARCHAR2(50) NOT NULL, LAST_NAME VARCHAR2(50) NOT NULL, PHONE VARCHAR2(20), EMAIL VARCHAR2(100), ADDRESS VARCHAR2(150) );

CREATE TABLE EMPLOYEE ( EMPLOYEE_ID NUMBER PRIMARY KEY, FIRST_NAME VARCHAR2(50), LAST_NAME VARCHAR2(50), POSITION VARCHAR2(50), SALARY NUMBER(10,2), PHONE VARCHAR2(20) );

CREATE TABLE SUPPLIER ( SUPPLIER_ID NUMBER PRIMARY KEY, SUPPLIER_NAME VARCHAR2(100), PHONE VARCHAR2(20), EMAIL VARCHAR2(100), ADDRESS VARCHAR2(100) );

CREATE TABLE SALES ( SALE_ID NUMBER PRIMARY KEY, CUSTOMER_ID NUMBER NOT NULL, EMPLOYEE_ID NUMBER NOT NULL, SALE_DATE DATE, TOTAL_AMOUNT NUMBER(10,2),

CONSTRAINT FK_SALES_CUSTOMER
    FOREIGN KEY (CUSTOMER_ID)
    REFERENCES CUSTOMER(CUSTOMER_ID),

CONSTRAINT FK_SALES_EMPLOYEE
    FOREIGN KEY (EMPLOYEE_ID)
    REFERENCES EMPLOYEE(EMPLOYEE_ID)

);

CREATE TABLE SALE_DETAILS ( SALE_DETAIL_ID NUMBER PRIMARY KEY, SALE_ID NUMBER NOT NULL, PRODUCT_ID NUMBER NOT NULL, QUANTITY NUMBER NOT NULL, UNIT_PRICE NUMBER(10,2),

CONSTRAINT FK_DETAIL_SALE
    FOREIGN KEY (SALE_ID)
    REFERENCES SALES(SALE_ID),

CONSTRAINT FK_DETAIL_PRODUCT
    FOREIGN KEY (PRODUCT_ID)
    REFERENCES PRODUCT(PRODUCT_ID)

); -- Categories Sample Data INSERT INTO CATEGORY VALUES (1, 'Beverages', 'Soft drinks and juices'); INSERT INTO CATEGORY VALUES (2, 'Food', 'Rice, sugar, flour'); INSERT INTO CATEGORY VALUES (3, 'Cleaning', 'Soap and detergents'); COMMIT;

-- Products Sample Data INSERT INTO PRODUCT VALUES (101, 'Coca-Cola', 1200, 50, 1); INSERT INTO PRODUCT VALUES (102, 'Rice 5kg', 18000, 20, 2); INSERT INTO PRODUCT VALUES (103, 'Bath Soap', 1500, 100, 3); COMMIT;

-- Customers Sample Data INSERT INTO CUSTOMER VALUES (1,'John','Doe','0788000001','john@gmail.com','Kigali'); INSERT INTO CUSTOMER VALUES (2,'Alice','Smith','0788000002','alice@gmail.com','Huye'); INSERT INTO CUSTOMER VALUES (3,'David','Mugisha','0788000003','david@gmail.com','Musanze'); COMMIT;

-- Employees Sample Data INSERT INTO EMPLOYEE VALUES (1,'Peter','Niyonzima','Manager',500000,'0788111111'); INSERT INTO EMPLOYEE VALUES (2,'Grace','Uwase','Cashier',250000,'0788222222'); INSERT INTO EMPLOYEE VALUES (3,'Eric','Hakizimana','Store Keeper',300000,'0788333333'); COMMIT;

-- Suppliers Sample Data INSERT INTO SUPPLIER VALUES (1,'ABC Foods','0788444444','abc@gmail.com','Kigali'); INSERT INTO SUPPLIER VALUES (2,'Clean Ltd','0788555555','clean@gmail.com','Huye'); INSERT INTO SUPPLIER VALUES (3,'Fresh Drinks','0788666666','fresh@gmail.com','Rubavu'); COMMIT;

-- Sales Sample Data INSERT INTO SALES VALUES (1,1,2,SYSDATE,25200); INSERT INTO SALES VALUES (2,2,1,SYSDATE,18000); COMMIT;

-- Sale Details Sample Data INSERT INTO SALE_DETAILS VALUES (1,1,101,2,1200); INSERT INTO SALE_DETAILS VALUES (2,1,102,1,18000); INSERT INTO SALE_DETAILS VALUES (3,2,102,1,18000); COMMIT;

-- Update Statements UPDATE PRODUCT SET PRICE = 1500 WHERE PRODUCT_ID = 101; COMMIT;

-- Verification Queries SELECT table_name FROM user_tables; SELECT * FROM CATEGORY; SELECT * FROM PRODUCT; SELECT * FROM CUSTOMER; SELECT * FROM EMPLOYEE; SELECT * FROM SUPPLIER; SELECT * FROM SALES; SELECT * FROM SALE_DETAILS;

-- Additional Analytics & Reports Queries SELECT * FROM PRODUCT WHERE PRICE > 1500; SELECT * FROM PRODUCT ORDER BY PRICE DESC; SELECT COUNT(*) FROM CUSTOMER;

SELECT S.SALE_ID, C.FIRST_NAME, C.LAST_NAME, S.SALE_DATE, S.TOTAL_AMOUNT FROM SALES S JOIN CUSTOMER C ON S.CUSTOMER_ID = C.CUSTOMER_ID;

SELECT SD.SALE_ID, P.PRODUCT_NAME, SD.QUANTITY, SD.UNIT_PRICE FROM SALE_DETAILS SD JOIN PRODUCT P ON SD.PRODUCT_ID = P.PRODUCT_ID;

SELECT COUNT(*) AS TOTAL_PRODUCTS FROM PRODUCT; SELECT SUM(PRICE * QUANTITY) AS STOCK_VALUE FROM PRODUCT; SELECT AVG(PRICE) AS AVERAGE_PRICE FROM PRODUCT; SELECT MAX(PRICE) AS HIGHEST_PRICE FROM PRODUCT; SELECT MIN(PRICE) AS LOWEST_PRICE FROM PRODUCT;

SELECT CATEGORY_ID, COUNT(*) AS NUMBER_OF_PRODUCTS FROM PRODUCT GROUP BY CATEGORY_ID;

SELECT P.PRODUCT_NAME, C.CATEGORY_NAME, P.PRICE FROM PRODUCT P JOIN CATEGORY C ON P.CATEGORY_ID = C.CATEGORY_ID;