CREATE TABLE CUSTOMER (
    Customer_ID VARCHAR2(5) NOT NULL,
    First_Name VARCHAR2(50) NOT NULL,
    Surname VARCHAR2(100) NOT NULL,
    Address VARCHAR2(100) NOT NULL,
    Contact_Num VARCHAR2(20) NOT NULL,
    Email VARCHAR2(100) NOT NULL,
    CONSTRAINT PK_CUSTOMER PRIMARY KEY(Customer_ID)
);

CREATE TABLE EMPLOYEE (
    Employee_id VARCHAR2(6) NOT NULL,
    First_Name VARCHAR2(50) NOT NULL,
    Surname VARCHAR2(100) NOT NULL,
    Contact_Num VARCHAR2(20) NOT NULL,
    Address VARCHAR2(100) NOT NULL,
    Email VARCHAR2(100) NOT NULL,
    CONSTRAINT PK_EMPLOYEE PRIMARY KEY(Employee_id)
);

CREATE TABLE DONATOR (
    Donator_id VARCHAR2(6) NOT NULL,
    First_Name VARCHAR2(20) NOT NULL,
    Surname VARCHAR2(50) NOT NULL,
    Contact_Num VARCHAR2(20) NOT NULL,
    Address VARCHAR2(100) NOT NULL,
    Email VARCHAR2(100) NOT NULL,
    CONSTRAINT PK_DONATOR PRIMARY KEY(Donator_ID)
);

CREATE TABLE DONATION (
    Donation_ID NUMBER(4) NOT NULL,
    Donator_id VARCHAR2(6) NOT NULL,
    Donation VARCHAR2(50) NOT NULL,
    Price INT NOT NULL,
    Donation_Date DATE NOT NULL,
    CONSTRAINT PK_DONATION PRIMARY KEY(Donation_ID),
    CONSTRAINT FK_DONATOR_DONATION FOREIGN KEY(Donator_id) REFERENCES DONATOR(Donator_id)
);

CREATE TABLE DELIVERY (
    Delivery_id INT NOT NULL,
    Delivery_notes VARCHAR2(100) NOT NULL,
    Dispatch_Date DATE NOT NULL,
    Delivery_Date DATE NOT NULL,
    CONSTRAINT PK_DELIVERY PRIMARY KEY(Delivery_id)
);

CREATE TABLE RETURNS (
    Return_id VARCHAR2(6) NOT NULL,
    Return_Date DATE NOT NULL,
    Reason VARCHAR2(200) NOT NULL,
    Customer_ID VARCHAR2(5) NOT NULL,
    Donation_ID NUMBER(4) NOT NULL,
    Employee_id VARCHAR2(6) NOT NULL,
    CONSTRAINT PK_RETURNS PRIMARY KEY(Return_id),
    CONSTRAINT FK_CUSTOMER_RETURNS FOREIGN KEY(Customer_id) REFERENCES CUSTOMER(Customer_id),
    CONSTRAINT FK_DONATION_RETURNS FOREIGN KEY(Donation_id) REFERENCES DONATION(Donation_id),
    CONSTRAINT FK_EMPLOYEE_RETURNS FOREIGN KEY(Employee_id) REFERENCES EMPLOYEE(Employee_id)
);

CREATE TABLE INVOICE (
    Invoice_num NUMBER(4) NOT NULL,
    Customer_ID VARCHAR2(5) NOT NULL,
    Invoice_Date DATE NOT NULL,
    Employee_id VARCHAR2(6) NOT NULL,
    Donation_ID NUMBER(4) NOT NULL,
    Delivery_id INT NOT NULL,
    CONSTRAINT PK_INVOICE PRIMARY KEY(Invoice_num),
    CONSTRAINT FK_CUSTOMER_INVOICE FOREIGN KEY(Customer_id) REFERENCES CUSTOMER(Customer_id),
    CONSTRAINT FK_DONATION_INVOICE FOREIGN KEY(Donation_id) REFERENCES DONATION(Donation_id),
    CONSTRAINT FK_EMPLOYEE_INVOICE FOREIGN KEY(Employee_id) REFERENCES EMPLOYEE(Employee_id),
    CONSTRAINT FK_DELIVERY_INVOICE FOREIGN KEY(Delivery_id) REFERENCES DELIVERY(Delivery_id)
);

--INSERTS
--Customer
INSERT ALL 
          INTO CUSTOMER VALUES('11011','Jack','Smith','18 Water Rd','0877277521','[email protected]')
          INTO CUSTOMER VALUES('11012','Pat','Hendricks','22 Water Rd','0863257857','[email protected]')
          INTO CUSTOMER VALUES('11013','Andre','Clark','101 Summer Lane','0834567891','[email protected]')
          INTO CUSTOMER VALUES('11014','Kevin','Jones','55 Mountain Way','0612547895','[email protected]')
          INTO CUSTOMER VALUES('11015','Lucy','Williams','5 Main Rd','0827238521','[email protected]')       
SELECT*FROM DUAL;

--Employee
INSERT ALL 
          INTO EMPLOYEE VALUES('emp101','Jeff','Davis','0877277521','10 Main Road','[email protected]')
          INTO EMPLOYEE VALUES('emp102','Kevin','Marks','0837377522','18 Water Road','[email protected]')
          INTO EMPLOYEE VALUES('emp103','Adanya','Andrews','0817117523','21 Circle Lane','[email protected]')
          INTO EMPLOYEE VALUES('emp104','Adebayo','Dryer','0797215244','1 Sea Road','[email protected]')
          INTO EMPLOYEE VALUES('emp105','Xolani','Samson','0827122255','12 Main Road','')
SELECT*FROM DUAL;

--DONATOR
INSERT ALL 
          INTO DONATOR VALUES('20111','Jeff','Watson','0827172250','[email protected]')
          INTO DONATOR VALUES('20112','Stephen','Jones','0837865670','[email protected]')
          INTO DONATOR VALUES('20113','James','Joe','0878978650','[email protected]')
          INTO DONATOR VALUES('20114','Kelly','Ross','0826575650','[email protected]')
          INTO DONATOR VALUES('20115','Abraham','Clark','0797656430','[email protected]')
SELECT*FROM DUAL;

--DONATION
INSERT ALL 
         INTO DONATION VALUES('7111','20111','KIC Fridge','599','1 May 2024')
         INTO DONATION VALUES('7112','20112','Samsung 42inch LCD','1299','3 May 2024')
         INTO DONATION VALUES('7113','20113','Sharp Microwave','1599','3 May 2024')
         INTO DONATION VALUES('7114','20115','6 Seat dining room table','799','5 May 2024')
         INTO DONATION VALUES('7115','20114','Lazyboy sofa','1199','7 May 2024')
         INTO DONATION VALUES('7116','20113','JVC Surround sound system','179','9 May 2024')
SELECT*FROM DUAL;

-- delivery 
INSERT ALL 
         INTO DELIVERY VALUES('511','Double packaging requested','10 May 2024','15 May 2024')
         INTO DELIVERY VALUES('512','Delivery to work address','12 May 2024','15 May 2024')
         INTO DELIVERY VALUES('513','Signature requested','12 May 2024','17 May 2024')
         INTO DELIVERY VALUES('514','No notes','12 May 2024','15 May 2024')
         INTO DELIVERY VALUES('515','Birthday Present wrapping required','18 May 2024','19 May 2024')
         INTO DELIVERY VALUES('516','Delivery to work address ','20 May 2024','25 May 2024')
SELECT*FROM DUAL;

--Return
INSERT ALL 
         INTO RETURN VALUES('ret001','25 May 2024','Customer not satisfied with product','11011','7116','emp101')
         INTO RETURN VALUES('ret002','25 May 2024','Product had broken section','11013','7114','emp103')
SELECT*FROM DUAL;

--INVOICE
INSERT ALL 
INTO INVOICE VALUES('8111','11011','15 May 2024','emp103','7111','511')
INTO INVOICE VALUES('8112','11013','15 May 2024','emp101','7112','512')
INTO INVOICE VALUES('8113','11012','17 May 2024','emp101','7113','513')
INTO INVOICE VALUES('8114','11015','17 May 2024','emp102','7114','514')
INTO INVOICE VALUES('8115','11011','17 May 2024','emp102','7115','515')
INTO INVOICE VALUES('8116','11015','18 May 2024','emp103','7116','516')
SELECT* FROM DUAL;

--Question 2
SELECT 
    c.First_Name || ' ' || c.Surname AS customer_name,
    i.Employee_id,
    dl.Delivery_notes,
    d.Donation,
    i.Invoice_num
FROM INVOICE i
INNER JOIN CUSTOMER c ON i.Customer_ID = c.Customer_ID
INNER JOIN DELIVERY dl ON i.Delivery_id = dl.Delivery_id
INNER JOIN DONATION d ON i.Donation_ID = d.Donation_ID
WHERE i.Invoice_Date < '18-MAY-2024';

--Question 4 PL/SQL
DECLARE
    v_customer_name VARCHAR2(100);
    v_donation_purchased VARCHAR2(100);
    v_donation_price NUMBER;
    v_return_reason VARCHAR2(200);
BEGIN
    FOR rec IN (SELECT c.First_Name || ' ' || c.Surname AS customer_name,
                       d.Donation,
                       d.Price,
                       r.Reason
                  FROM RETURNS r
                       JOIN CUSTOMER c ON r.Customer_ID = c.Customer_ID
                       JOIN DONATION d ON r.Donation_ID = d.Donation_ID)
    LOOP
        v_customer_name := rec.customer_name;
        v_donation_purchased := rec.Donation;
        v_donation_price := rec.Price;
        v_return_reason := rec.Reason;

        DBMS_OUTPUT.PUT_LINE('Customer Name: ' || v_customer_name);
        DBMS_OUTPUT.PUT_LINE('Donation Purchased: ' || v_donation_purchased);
        DBMS_OUTPUT.PUT_LINE('Donation Price: ' || v_donation_price);
        DBMS_OUTPUT.PUT_LINE('Return Reason: ' || v_return_reason);
        DBMS_OUTPUT.PUT_LINE('----------------------------------------');
    END LOOP;
END;
/

--Question 5

SET SERVEROUTPUT ON;

DECLARE
    v_customer_name VARCHAR2(100);
    v_employee_name VARCHAR2(100);
    v_donation VARCHAR2(100);
    v_dispatch_date DATE;
    v_delivery_date DATE;
    v_days_between NUMBER;
BEGIN
    SELECT c.First_Name || ' ' || c.Surname,
           e.First_Name || ' ' || e.Surname,
           d.Donation,
           dv.Dispatch_Date,
           dv.Delivery_Date
    INTO v_customer_name, v_employee_name, v_donation, v_dispatch_date, v_delivery_date
    FROM INVOICE i
    JOIN CUSTOMER c ON i.Customer_ID = c.Customer_ID
    JOIN EMPLOYEE e ON i.Employee_id = e.Employee_id
    JOIN DONATION d ON i.Donation_ID = d.Donation_ID
    JOIN DELIVERY dv ON i.Delivery_id = dv.Delivery_id
    WHERE c.Customer_ID = '11013';

    v_days_between := NVL(TO_NUMBER(v_delivery_date - v_dispatch_date), 0);

    DBMS_OUTPUT.PUT_LINE('Customer Name: ' || v_customer_name);
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
    DBMS_OUTPUT.PUT_LINE('Donation: ' || v_donation);
    DBMS_OUTPUT.PUT_LINE('Dispatch Date: ' || TO_CHAR(v_dispatch_date, 'DD-MON-YYYY'));
    DBMS_OUTPUT.PUT_LINE('Delivery Date: ' || TO_CHAR(v_delivery_date, 'DD-MON-YYYY'));
    DBMS_OUTPUT.PUT_LINE('Days Between Dispatch and Delivery: ' || v_days_between);
END;
/

--Question 6

SELECT c.First_Name || ' ' || c.Surname AS customer_name,
       SUM(i.Price) AS total_amount_spent,
       CASE
           WHEN SUM(i.Price) >= 1500 THEN 'Star'
           ELSE 'No Star'
       END AS customer_rating
FROM INVOICE i
INNER JOIN CUSTOMER c ON i.Customer_ID = c.Customer_ID
GROUP BY c.First_Name, c.Surname;
by

Oracle Online Compiler

Write, Run & Share Oracle queries online using OneCompiler's Oracle online editor and compiler for free. It's one of the robust, feature-rich online editor and compiler for Oracle running on latest version 23c (23.3.0.0). Getting started with the OneCompiler's Oracle editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose language as 'Oracle' and start writing queries to learn and test online without worrying about tedious process of installation.

About Oracle

Oracle Database is world's most popular database built by Oracle Corporation. It is a multi-model database management system. It's known for its robustness, scalability, and comprehensive feature set, making it popular for enterprise-level applications and large-scale data management.

Syntax help

Commands

1. CREATE

CREATE TABLE table_name (
                column1 datatype,
                column2 datatype,
                ....);

Example

CREATE TABLE EMPLOYEE (
  empId NUMBER PRIMARY KEY,
  name VARCHAR2(15) NOT NULL,
  dept VARCHAR2(10) NOT NULL
);

2. Add Data

Example

INSERT INTO EMPLOYEE VALUES (1, 'Dave', 'Sales');

3. TRUNCATE

TRUNCATE table table_name;

4. DROP

DROP TABLE table_name;

6. COMMENTS

Single-Line Comments:

 --Line1;

Multi-Line comments:

   /* Line1,
   Line2 */

DML Commands

1. INSERT

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

Note: Column names are optional.

Example

INSERT INTO EMPLOYEE VALUES (1, 'Ava', 'Sales');

2. SELECT

SELECT column1, column2, ...
FROM table_name
[where condition];

Example

SELECT * FROM EMPLOYEE where dept ='sales';

3. UPDATE

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example

UPDATE EMPLOYEE SET dept = 'Sales' WHERE empId='0001';

4. DELETE

DELETE FROM table_name where condition;

Example

DELETE from EMPLOYEE where empId='0001';