QUESTION 1
--create table CUSTOMER
CREATE TABLE CUSTOMER (
CUSTOMER_ID INT PRIMARY KEY,
FIRST_NAME VARCHAR(50),
SURNAME VARCHAR(50),
ADDRESS VARCHAR(100),
CONTACT_NUMBER VARCHAR(20),
EMAIL VARCHAR(100)
);
-- create table EMPLOYEE
CREATE TABLE EMPLOYEE (
EMPLOYEE_ID VARCHAR PRIMARY KEY,
FIRST_NAME VARCHAR(50),
SURNAME VARCHAR(50),
CONTACT_NUMBER VARCHAR(20),
ADDRESS VARCHAR(100),
EMAIL VARCHAR(100)
);
CREATE TABLE DONATOR (
DONATOR_ID INT PRIMARY KEY,
FIRST_NAME VARCHAR(50),
SURNAME VARCHAR(50),
ADDRESS VARCHAR(100),
CONTACT_NUMBER VARCHAR(20),
EMAIL VARCHAR(100)
);
-- create table DONATION
CREATE TABLE DONATION (
DONATION_ID INT PRIMARY KEY,
DONATOR_ID INT,
DONATION VARCHAR(100),
PRICE DECIMAL(10, 2),
DONATION_DATE DATE,
FOREIGN KEY (DONATOR_ID) REFERENCES DONATOR(DONATOR_ID)
);
-- create table DELIVERY
CREATE TABLE DELIVERY (
DELIVERY_ID INT PRIMARY KEY,
DELIVERY_NOTES VARCHAR(200),
DISPATCH_DATE DATE,
DELIVERY_DATE DATE
);
-- create table RETURNS
CREATE TABLE RETURNS (
RETURN_ID VARCHAR(10) PRIMARY KEY,
RETURN_DATE DATE,
REASON VARCHAR(200),
CUSTOMER_ID VARCHAR(10),
DONATION_ID INT
EMPLOYEE_ID INT,
FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER(CUSTOMER_ID),
FOREIGN KEY (DONATION_ID) REFERENCES CUSTOMER(DONATION_ID),
FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID)
);
-- create table INVOICE
CREATE TABLE INVOICE (
INVOICE_NUM INT PRIMARY KEY,
INVOICE_DATE DATE,
EMPLOYEE_ID INT,
DONATION_ID INT,
DELIVERY_ID INT,
FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID),
FOREIGN KEY (DONATION_ID) REFERENCES DONATION(DONATION_ID),
FOREIGN KEY (DELIVERY_ID) REFERENCES DELIVERY(DELIVERY_ID)
);
-- Inserting data into CUSTOMER table
INSERT INTO CUSTOMER VALUES (11011, 'Jack', 'Smith', '18 Water Rd', 0877277521, '[email protected]');
INSERT INTO CUSTOMER VALUES (11012, 'Pat', 'Hendricks', '22 Water Rd', 0863257857, '[email protected]');
INSERT INTO CUSTOMER VALUES (11013, 'Andre', 'Clark', '101 Summer Lane', 0834567891, '[email protected]');
INSERT INTO CUSTOMER VALUES (11014, 'Kevin', 'Jones', '55 Mountain way', 0612547895, '[email protected]');
INSERT INTO CUSTOMER VALUES (11015, 'Lucy', 'Williams', '5 Main rd', 0827238521, '[email protected]');
SELECT * FROM CUSTOMER;
-- Inserting data into EMPLOYEE table
INSERT INTO EMPLOYEE VALUES ('emp101', 'Jeff', 'Davis', 0877277521, '10 main road', '[email protected]');
INSERT INTO EMPLOYEE VALUES ('emp102', 'Kevin', 'Marks', 0837377522, '18 water road', '[email protected]');
INSERT INTO EMPLOYEE VALUES ('emp103', 'Adanya', 'Andrews', 0817117523, '21 circle lane', '[email protected]');
INSERT INTO EMPLOYEE VALUES ('emp104', 'Adebayo', 'Dryer', 0797215244, '1 sea road', '[email protected]');
INSERT INTO EMPLOYEE VALUES ('emp105', 'Xolani', 'Samson', 0827122255, '12 main road', '[email protected]');
SELECT * FROM EMPLOYEE;
-- Inserting data into DONATOR table
INSERT INTO DONATOR VALUES (20111, 'Jeff', 'Watson', 0827172250, '[email protected]');
INSERT INTO DONATOR VALUES (20112, 'Stephen', 'Jones', 0837865670, '[email protected]');
INSERT INTO DONATOR VALUES (20113, 'James', 'Joe', 0878978650, '[email protected]');
INSERT INTO DONATOR VALUES (20114, 'Kelly', 'Ross', 0826575650, '[email protected]');
INSERT INTO DONATOR VALUES (20115, 'Abraham', 'Clark', 0797656430, '[email protected]');
SELECT * FROM DONATOR;
-- Inserting data into DONATION table
INSERT INTO DONATION VALUES (7111, 20111, 'KIC Fridge', 599, '1-May-2024');
INSERT INTO DONATION VALUES (7112, 20112, 'Samsung 42inch LCD', 1299,'3 May 2024');
INSERT INTO DONATION VALUES (7113, 20113, 'Sharp Microwave', 1599, '3 May 2024');
INSERT INTO DONATION VALUES (7114, 20115, '6 Seat Dining room table', 799, '5 May 2024');
INSERT INTO DONATION VALUES (7115, 20114, 'Lazyboy Sofa', 1199, '7 May 2024');
INSERT INTO DONATION VALUES (7116, 20113, 'JVC Surround Sound System', 179, '9 May 2024');
SELECT * FROM DONATION;
-- Inserting data into DELIVERY table
INSERT INTO DELIVERY VALUES (511, 'Double packaging requested', '10 May 2024', '15 May 2024');
INSERT INTO DELIVERY VALUES (512, 'Delivery to work address', '12 May 2024', '15 May 2024');
INSERT INTO DELIVERY VALUES (513, 'Signature required', '12 May 2024', '17 May 2024');
INSERT INTO DELIVERY VALUES (514, 'No notes', '12 May 2024', '15 May 2024');
INSERT INTO DELIVERY VALUES (515, 'Birthday present wrapping required', '18 May 2024', '19 May 2024');
INSERT INTO DELIVERY VALUES (516, 'Delivery to work address', '20 May 2024', '25 May 2024');
SELECT * FROM DELIVERY;
-- Inserting data into RETURNS table
INSERT INTO RETURNS VALUES ('ret001', '25 May 2024', 'Customer not satisfied with product', 11011, 7116, 'emp101');
INSERT INTO RETURNS VALUES ('ret002', '25 May 2024', 'Product had broken section', 11013, 7114, 'emp103');
SELECT * FROM RETURNS;
-- Inserting data into INVOICE table
INSERT INTO INVOICE VALUES (8111,11011, '15 May 2024', 'emp103', 7111, 511);
INSERT INTO INVOICE VALUES (8112, 11013, '15 May 2024', 'emp101', 7114, 512);
INSERT INTO INVOICE VALUES (8113, 11012, '17 May 2024', 'emp101', 7112, 513);
INSERT INTO INVOICE VALUES (8114, 11015, '17 May 2024', 'emp102', 7113, 514);
INSERT INTO INVOICE VALUES (8115, 11011, '17 May 2024', 'emp102', 7115, 515);
INSERT INTO INVOICE VALUES (8116, 11015, '18 May 2024', 'emp103', 7116, 516);
SELECT * FROM INVOICE;
QUESTION 2
CHARITY 4 AFRICA requires a report containing the combined customer name, employee id, delivery notes, donation purchased and the invoice number. Create SQL query to generate the required report. In your query, only display results that have any invoice date before 18 May 2024
SELECT CUSTOMER.FIRST_NAME, CUSTOMER.SURNAME, EMPLOYEE.EMPLOYEE_ID,
DELIVERY.DELIVERY_NOTES, DONATION.DONATION, INVOICE.INVOICE_NUM, INVOICE.INVOICE_DATE
FROM CUSTOMER
INNER JOIN INVOICE ON INVOICE.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID
INNER JOIN DELIVERY ON DELIVERY.DELIVERY_ID = INVOICE.DELIVERY_ID
INNER JOIN DONATION ON DONATION.DONATION_ID = INVOICE.DONATION_ID
INNER JOIN EMPLOYEE ON EMPLOYEE.EMPLOYEE_ID = INVOICE.EMPLOYEE_ID
WHERE INVOICE.INVOICE_DATE < '2022-05-18';
QUESTION 3
Management of CHARITY 4 AFRICA would like to add new table called Funding that at the present will not have referential Integrity. The organisation would like an automatically generated unique id every time a record is inserted into the new table. The attributes the table must contain for now are funding_id, funder, and funding amount
Create the new implement solution to automatically generate the unique ids with every new insert. In your solution provide an example of the insert statement.
CREATE TABLE FUNDING(
FUNDING_ID NUMBER(10) PRIMARY KEY,
FUNDER VARCHAR(20) NOT NULL,
FUNDING_AMOUNT DECIMAL(10,0) NOT NULL
);
CREATE SEQUENCE FUNDING_SEQ;
CREATE OR REPLACE TRIGGER FUNDING_TRIGGER
BEFORE INSERT ON FUNDING
FOR EACH ROW
BEGIN
SELECT FUNDING_SEQ.NEXTVAL
INTO :new.FUNDING_ID
FROM dual;
END;
INSERT INTO FUNDING(FUNDER, FUNDING_AMOUNT) VALUES ('Jack Smith', 1700);
INSERT INTO FUNDING(FUNDER, FUNDING_AMOUNT) VALUES ('Adanya Andrews', 1701);
INSERT INTO FUNDING(FUNDER, FUNDING_AMOUNT) VALUES ('Kelly Ross', 1702);
INSERT INTO FUNDING(FUNDER, FUNDING_AMOUNT) VALUES ('Jeff Watson', 1703);
INSERT INTO FUNDING(FUNDER, FUNDING_AMOUNT) VALUES ('Xolani Samson', 1704);
SELECT * FROM FUNDING;
QUESTION 4
Create PL/SQL query to display the combined customer name, donation purchased, donation price and the reason that the donation was returned.
SET SERVEROUTPUT ON;
BEGIN
FOR item IN(
SELECT CUSTOMER.FIRST_NAME,CUSTOMER.SURNAME, DONATION.DONATION, DONATION.PRICE, RETURNS.REASON
FROM CUSTOMER
INNER JOIN RETURNS ON RETURNS.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID
LEFT JOIN DONATION ON DONATION.DONATION_ID = RETURNS.DONATION_ID
)
LOOP
DBMS_OUTPUT.PUT_LINE('__________________________________________________________');
DBMS_OUTPUT.PUT_LINE('CUSTOMER: ' ||item.FIRST_NAME || ', ' ||item.SURNAME);
DBMS_OUTPUT.PUT_LINE('DONATION PURCHASED: ' ||item.DONATION);
DBMS_OUTPUT.PUT_LINE('PRICE: R' ||item.PRICE);
DBMS_OUTPUT.PUT_LINE('RETURN REASON: ' ||item.REASON);
END LOOP;
DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------');
END;
QUESTION 5
Create PL/SQL query to display customer’s name, donation, dispatch date and delivery date. In query, also display the number of days between the dispatch date and the delivery dates for customer 11013
SET SERVEROUTPUT ON;
BEGIN
FOR item IN(
SELECT
CUSTOMER.FIRST_NAME,
CUSTOMER.SURNAME,
EMPLOYEE. FIRST_NAMES ,
EMPLOYEE. SURNAMES,
DONATION.DONATION,
DELIVERY.DISPATCH_DATE,
DELIVERY.DELIVERY_DATE,
DELIVERY.DELIVERY - DELIVERY.DISPATCH_DATE AS DAYS_TO_DELIVER
FROM CUSTOMER
INNER JOIN INVOICE ON INVOICE.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID
INNER JOIN EMPLOYEE ON EMPLOYEE.EMPLOYEE_ID = INVOICE.EMPLOYEE_ID
INNER JOIN DONATION ON DONATION.DONATION_ID = INVOICE.DONATION_ID
INNER JOIN DELIVERY ON DELIVERY.DELIVERY_ID = INVOICE.DELIVERY_ID
WHERE CUSTOMER.CUSTOMER_ID = 11013
)
LOOP
DBMS_OUTPUT.PUT_LINE('------------------------------------------');
DBMS_OUTPUT.PUT_LINE('CUSTOMER: ' || item.FIRST_NAME|| ' ' || item.SURNAME);
DBMS_OUTPUT.PUT_LINE('EMPLOYEE: ' || item.FIRST_NAMES|| ' ' || item. SURNAMES);
DBMS_OUTPUT.PUT_LINE('DONATION: ' || item.DONATION);
DBMS_OUTPUT.PUT_LINE('DISPATCH_DATE: ' || item.DISPATCH_DATE);
DBMS_OUTPUT.PUT_LINE('DELIVERY_DATE: ' || item.DELIVERY_DATE);
DBMS_OUTPUT.PUT_LINE('DAYS_TO_DELIVER: ' || item.DAYS_TO_DELIVER);
DBMS_OUTPUT.PUT_LINE('------------------------------------------');
END LOOP;
END;
QUESTION 6
Create a report to display the combined customer name and total amount spent by each customer on the purchases. In your solution determine the customer rating as follows: if the total spent amount is greater than or equal to R1 500 the customer receives a star rating, otherwise no star rating applies.
SELECT
CUSTOMER.FIRST_NAME,
CUSTOMER.SURNAME,
SUM(DONATION.PRICE) AS TOTAL_AMOUNT_SPENT,
CASE
WHEN SUM(DONATION.PRICE) >= 1500 THEN 'Star Rating'
ELSE 'No Star Rating'
END AS CUSTOMER_RATING
FROM CUSTOMER
INNER JOIN INVOICE ON INVOICE.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID
INNER JOIN DONATION ON DONATION.DONATION_ID = INVOICE.DONATION_ID
GROUP BY CUSTOMER.FIRST_NAME, CUSTOMER.SURNAME;
QUESTION 7
Provide relevant code examples, with code comments based on this case study of how to implement the following
Q.7.1 %TYPE attribute
DECLARE
v_employee_name EMPLOYEE.FIRST_NAME%TYPE; -- Declaring a variable with the same datatype as the FIRST_NAME column in the EMPLOYEE table.
BEGIN
SELECT FIRST_NAME INTO v_employee_name FROM EMPLOYEE WHERE EMPLOYEE_ID = 'emp101';
-- Using the v_employee_name variable for further processing.
DBMS_OUTPUT.PUT_LINE('Employee name: ' || v_employee_name);
END;
Q.7.2%ROWTYPE attribute
DECLARE
v_employee_record EMPLOYEE%ROWTYPE; -- Declaring a variable with the same structure as a row in the EMPLOYEE table.
BEGIN
SELECT * INTO v_employee_record FROM EMPLOYEE WHERE EMPLOYEE_ID = 'emp101';
-- Using the v_employee_record variable for further processing.
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_record.EMPLOYEE_ID);
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_record.FIRST_NAME || ' ' || v_employee_record.SURNAME);
END;
Q.7.3 system defined exception
DECLARE
v_amount NUMBER := 100;
BEGIN
IF v_amount < 500 THEN
RAISE VALUE_ERROR; -- Raising a system-defined exception if the condition is not met.
END IF;
EXCEPTION
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Amount must be at least 500.');
END;
Q.7.4 user define exception
DECLARE
insufficient_funds EXCEPTION; -- Declaring a user-defined exception.
v_balance NUMBER := 200;
v_withdrawal_amount NUMBER := 500;
BEGIN
IF v_withdrawal_amount > v_balance THEN
RAISE insufficient_funds; -- Raising the user-defined exception.
END IF;
EXCEPTION
WHEN insufficient_funds THEN
DBMS_OUTPUT.PUT_LINE('Insufficient funds for withdrawal.');
END;
Write, Run & Share PL/SQL code online using OneCompiler's Oracle PL/SQL online editor and compiler for free. It's one of the robust, feature-rich online editor and compiler for Oracle PL/SQL running on latest version 23c (23.3.0.0). Getting started with the OneCompiler's Oracle PL/SQL editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose language as 'PL/SQL' and start writing code to learn and test online without worrying about tedious process of installation.
PL/SQL is procedural extension for SQL created by Oracle. It is by default embedded into the Oracle Database. PL/SQL program units are compiled and stored inside the Oracle Database which results in optimal execution times as the PL/SQL and SQL run within the same server process.
Following is the syntax structure for the PL/SQL code blocks
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling>
END;
DECLARE
message varchar2(100):= 'Hello, World!';
BEGIN
dbms_output.put_line(message);
END;
/
CREATE OR REPLACE FUNCTION
hello_user
(user_name IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
RETURN 'Hello ' || user_name;
END hello_user;
/
BEGIN
dbms_output.put_line(hello_user('Peter'));
END;
/
BEGIN
DBMS_OUTPUT.put_line (1/0);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('error is: ' || SQLERRM);
END;