CSE2DBF 2018 Assignment 2: SQL Queries, Procedures, and Triggers

This document presents a comprehensive solution to a database assignment (CSE2DBF 2018 Assignment 2), focusing on SQL queries, stored procedures, and triggers. The solution encompasses various SQL queries to retrieve data from different tables, including filtering based on specific criteria and joining tables to extract relevant information. It also includes the creation of stored procedures to automate tasks, such as calculating commissions and generating reports, and stored functions for calculating bonuses. Furthermore, the solution demonstrates the implementation of triggers to enforce data integrity, such as validating employee tenure before inserting a new franchise application and backing up data upon deletion from a table. The assignment brief required the student to demonstrate an understanding of database concepts, SQL, and PL/SQL. The solution effectively addresses all the requirements of the assignment.

Task 1
a.
SELECT
h2hschema.department.departmentid,
h2hschema.department.departmenttitle
FROM
h2hschema.department
WHERE
h2hschema.department.storeid = (select h2hschema.store.storeid from
h2hschema.store WHERE h2hschema.store.weekendhours = '10:00 AM - 04:00 PM')
b.
SELECT
h2hschema.employee.ename,
h2hschema.employee.eaddress
FROM
h2hschema.employee
INNER JOIN h2hschema.department ON h2hschema.employee.departmentid =
h2hschema.department.departmentid
AND
h2hschema.department.storeid = 'S001'
c.
SELECT
h2hschema.franchise_app.appnumber,
h2hschema.franchise_app.appdate,
h2hschema.franchise_app.decisiondate,
h2hschema.franchise_app.status,
h2hschema.department.departmenttitle,
h2hschema.department.departmentid
FROM
h2hschema.franchise_app
RIGHT JOIN h2hschema.department ON h2hschema.franchise_app.departmentid =
h2hschema.department.departmentid

d.
SELECT
COUNT(h2hschema.membership.memberid),
h2hschema.team.teamname,
h2hschema.team.teamleaderid,
h2hschema.team.teamid,
h2hschema.franchise_app.status
FROM
h2hschema.franchise_app
INNER JOIN h2hschema.team ON h2hschema.team.teamid = h2hschema.franchise_app.teamid
RIGHT JOIN h2hschema.membership ON h2hschema.membership.teamid = h2hschema.team.teamid
WHERE h2hschema.franchise_app.status='Granted'
group by h2hschema.team.teamname, h2hschema.team.teamleaderid, h2hschema.team.teamid,
h2hschema.franchise_app.status
e.
SELECT
franchise_app.decisiondate - franchise_app.appdate AS max_column1,
franchise_app.appdate,
franchise_app.decisiondate,
franchise_app.appnumber
FROM
franchise_app
GROUP BY
franchise_app.decisiondate - franchise_app.appdate,
franchise_app.appdate,
franchise_app.decisiondate,
franchise_app.appnumber
HAVING
MAX(decisiondate-appdate)= (select MAX(decisiondate-appdate) from franchise_app)

Task 2
a.
SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE COMMISSION
(
PARAM1 weekly_sales_reports.startdate%type,
PARAM2 weekly_sales_reports.enddate%type
) AS
CURSOR comm_cursor IS SELECT reportID,comamount,comrarate,saleamount FROM weekly_sales_report
WHERE startdate = &&PARAM1 AND enddate = &&PARAM2;
cursor_mon comm_cursor%ROWTYPE;
BEGIN
OPEN comm_cursor;
LOOP
FETCH comm_cursor INTO cursor_mon;
IF comm_cursor%FOUND THEN
DBMS_OUTPUT.PUT_LINE( 'ENTER START DATE:' ||&PARAM1);
DBMS_OUTPUT.PUT_LINE( 'ENTER START DATE:' ||&PARAM2);
UPDATE weekly_sales_report SET comamount = comrate * saleamount
WHERE startdate = &PARAM1 AND enddate = &PARAM2;
END IF;
EXIT WHEN comm_cursor%NOTFOUND;
DBMS_OUTPUT.PUTLINE('The commission amount for report' ||cursor_mon.reportid|| 'has
been updated to' ||cursor_mon.comamount|| 'dollars,
which is' ||cursor,comrate||'% of the total sale amount of'|| cursor.saleamount||'dollars');
END LOOP;
CLOSE comm_cursor
END COMMISSION;
b.

CREATE OR REPLACE PROCEDURE REPORTINGBOSS
(
EMPLOYEE_ID IN NUMBER
) AS
department_id VARCHAR2(10);
department_title VARCHAR2(30);
no_of_employees NUMBER(2,0);
account_no VARCHAR2(10);
store_id VARCHAR2(10);
team_id VARCHAR2(10);
member_id VARCHAR2(10);
shares NUMBER(4,2);
shareholder_name VARCHAR2(30);
shareholder_phone VARCHAR2(20);
shareholder_email VARCHAR2(30);
shareholder_address VARCHAR2(50);
no_of_shares NUMBER(6,0);
BEGIN
SELECT departmentid INTO department_id FROM employee
WHERE employeeid = employee_id;
SELECT departmenttitle,noofemployees,accountno,storeid
INTO department_title,no_of_employees,account_no,store_id
FROM department
WHERE departmentid = department_id;
SELECT teamid INTO team_id FROM franchise_app
WHERE departmentid = department_id;
SELECT
membership.memberid,percentageofshare
INTO member_id,shares
FROM membership
WHERE membership.teamid = team_id
AND percentageofshare = (select max(percentageofshare) FROM membership WHERE teamid =
team_id );
SELECT shname,shphone,shemail,shaddress,noofshares
INTO
shareholder_name,shareholder_phone,shareholder_email,shareholder_address,no_of_shares
FROM shareholder
WHERE memberid = member_id;
DBMS_OUTPUT.PUT_LINE(department_id,department_title,no_of_employees,account_no,store_i
d,shareholder_name,
shareholder_phone,shareholder_email,shareholder_address,no_of_shares);
END REPORTINGBOSS;
c.
CREATE OR REPLACE FUNCTION BONUS
(
EMPLOYEEID IN NUMBER
, BONUSDATE IN DATE
) RETURN NUMBER AS
bonus NUMBER(8,2);
nobonus EXCEPTION;

BEGIN
SELECT ebonus INTO bonus FROM weekly_sales_report
WHERE weekly_sales_report.employeeid = employeeid
and weekly_sales_report.enddate = bonusdate;
RETURN (bonus);
IF SQL%NOTFOUND THEN
RAISE nobonus;
END IF;
EXCEPTION
WHEN nobonus THEN
DBMS_OUTPUT.PUT_LINE('No bonus awarded this month');
RETURN 0;
END BONUS;
Task 3
a.
CREATE OR REPLACE TRIGGER NEW_APPLICATION
BEFORE INSERT ON FRANCHISE_APP
DECLARE
empdate DATE;
BEGIN
SELECT JOININGDATE INTO empdate from employee where employeeid = &empid;
SELECT MONTHS_BETWEEN(TO_DATE(sysdate,'DD-MON-YYYY'), TO_DATE(:empdate,'DD-MON-
YYYY'))/12
INTO EMP_AGE FROM DUAL;
IF (EMP_AGE < 10) THEN
RAISE_APPLICATION_ERROR(-20000,'Employee must have 10 years or more in the
franchise.');
END IF;
END;
b. CREATE OR REPLACE TRIGGER WEEK_TRIG
AFTER DELETE ON WEEKLY_SALES_REPORT
FOR EACH ROW
BEGIN
INSERT INTO backupReport(reportid,
startdate,enddate,saleamount
)
VALUES(:old.reportid,
:old.startdate,

:old.enddate,
:old.saleamount
);
END;