SQL Queries And Procedures - Database Fundamentals Essay.

Queries:

Questions :- 

1. Using the tables provided above, provide SQL statements for the following queries.

a. Show the store information along with its departments for all stores that opens the latest during weekend.
b. Show the name and address of all team members for store S001.
c. List the franchise application details for each department of H2H along with the department id and title. Include the departments that have not received any application yet. 
d. Show the team information and number of members for all successful franchise application.
e. Show the details of the franchise application that took the longest time to reach a decision.
f.  List the team member details (including shareholder and employee details such as id, name, phone and email) for all the team members who are part of a franchise team of a successful application. For each of the     members, also include the date on which their franchise application was granted. 
g. List the team member details (shareholder/employee details) who has the maximum credit to his name, based on his percentage of share in the team and the positive account balance of the franchising                      department. (Hint: account balance can be found in the Account table)
h. Show the top 4 employees (name, salary earned) that have earned the highest salary from January 2017 – December 2017. Assume that all casual employees work 12 hours per week and there are 45 working          weeks a year.

2. Provide the implementation of the following stored procedures and function. For submission, please include both the PL/SQL code and an execute procedure/SQL statement to demonstrate the functionality.

a. Write a procedure that takes two dates as input (a duration) and updates the commission amount of every weekly report for which the start date falls under this duration. The commission amount is the result of          multiplication of sale amount and commission rate (%). The procedure also generates an output in the following format for every affected row:

The commission amount for report <ReportID> has been updated to <ComAmount> dollars, which is <ComRate>% of the total sale amount of <SaleAmount> dollars.

b. Write a procedure that takes an employee id as argument and lists the details of an employee’s department and reporting boss. Note that an employee’s reporting boss is the person who has the highest                      percentage of share in the franchise team. The reporting boss can be either a full-time employee or a shareholder. If the reporting boss is a shareholder, display number of shares along with contact details and if        the boss is an employee, mention DOB, gender and joining date along with the contact information.
c. Write a function that takes an employee id and a month-year (date of ‘MON-YYYY’ format) and returns the total bonus amount the employee have earned by making exceptional sales during that month. If the            employee was not awarded a bonus for that month, the function should return zero.

For the execution of the function, write a procedure that accepts as input, an employee id and a month (in the same format), and calls the function to get the employee’s bonus amount and prints the following message:

The employee named <employee name> has earned a total bonus amount of <calculated amount> for the month of <the full name of the month> by making outstanding sales.

Hint: Although we did not maintain referential integrity constraint between EMPLOYEE and WEEKLY_SALES_REPORT, you can safely assume that the employee listed as the best performing employee in a sales report is a valid employee, who is present in the employee table.

3. Provide the implementation of the following triggers. For submission, please include both the PL/SQL code and a DML statement (insert, update or delete) to demonstrate the trigger functionality.

a. A trigger that prevents a full-time employee from applying for a franchise if he/she has less than 10 years of experience as an employee of H2H.
b. A trigger that automatically backs-up the oldest weekly sales report of a specific department when a new report is generated (for the same department) and the number of reports in the report table exceeds 30. To preserve the data, the trigger stores the selected report into a backup table:

1.Table Diagram

2. ER Diagram

3. SQL Code

-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------

-- Schema 735998-Database Fundamentals

-- -----------------------------------------------------

-- -----------------------------------------------------

-- Schema 735998-Database Fundamentals

-- -----------------------------------------------------

CREATE SCHEMA IF NOT EXISTS `735998-Database Fundamentals` DEFAULT CHARACTER SET utf8 ;

USE `735998-Database Fundamentals` ;

-- -----------------------------------------------------

-- Table `735998-Database Fundamentals`.`tblStore`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `735998-Database Fundamentals`.`tblStore` (

  `StoreID` INT NOT NULL,

  `StoreAddress` VARCHAR(45) NULL,

  `WeekDaysHours` VARCHAR(45) NULL,

  `WeekendHours` VARCHAR(45) NULL,

  PRIMARY KEY (`StoreID`))

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `735998-Database Fundamentals`.`tblAccount`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `735998-Database Fundamentals`.`tblAccount` (

  `AccountNo` INT NOT NULL,

  `AccountName` VARCHAR(45) NULL,

  `Balance` VARCHAR(45) NULL,

  PRIMARY KEY (`AccountNo`))

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `735998-Database Fundamentals`.`tblTeam`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `735998-Database Fundamentals`.`tblTeam` (

  `TeamID` INT NOT NULL,

  `TeamName` VARCHAR(45) NULL,

  `TeamLeaderID` VARCHAR(45) NULL,

  `MemberID` INT NULL,

  PRIMARY KEY (`TeamID`))

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `735998-Database Fundamentals`.`tblMembership`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `735998-Database Fundamentals`.`tblMembership` (

  `MemberID` INT NOT NULL,

  `TeamID` INT NULL,

  `PercentageOfShare` DECIMAL(10,0) NULL,

  `tblTeam_TeamID` INT NOT NULL,

  PRIMARY KEY (`MemberID`),

  INDEX `fk_tblMembership_tblTeam1_idx` (`tblTeam_TeamID` ASC),

  CONSTRAINT `fk_tblMembership_tblTeam1`

    FOREIGN KEY (`tblTeam_TeamID`)

    REFERENCES `735998-Database Fundamentals`.`tblTeam` (`TeamID`)

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `735998-Database Fundamentals`.`tblTeamMember`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `735998-Database Fundamentals`.`tblTeamMember` (

  `MemberID` INT NOT NULL,

  `tblMembership_MemberID` INT NOT NULL,

  PRIMARY KEY (`MemberID`),

  INDEX `fk_tblTeamMember_tblMembership1_idx` (`tblMembership_MemberID` ASC),

  CONSTRAINT `fk_tblTeamMember_tblMembership1`

    FOREIGN KEY (`tblMembership_MemberID`)

    REFERENCES `735998-Database Fundamentals`.`tblMembership` (`MemberID`)

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `735998-Database Fundamentals`.`tblShakeholder`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `735998-Database Fundamentals`.`tblShakeholder` (

  `SHID` INT NOT NULL,

  `SHName` VARCHAR(45) NULL,

  `SHPhone` VARCHAR(45) NULL,

  `SHEmail` VARCHAR(45) NULL,

  `SHAddress` VARCHAR(45) NULL,

  `NoOfShares` VARCHAR(45) NULL,

  `MemberID` VARCHAR(45) NULL,

  `tblTeamMember_MemberID` INT NOT NULL,

  PRIMARY KEY (`SHID`),

  INDEX `fk_tblShakeholder_tblTeamMember1_idx` (`tblTeamMember_MemberID` ASC),

  CONSTRAINT `fk_tblShakeholder_tblTeamMember1`

    FOREIGN KEY (`tblTeamMember_MemberID`)

    REFERENCES `735998-Database Fundamentals`.`tblTeamMember` (`MemberID`)

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `735998-Database Fundamentals`.`tblDepartment`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `735998-Database Fundamentals`.`tblDepartment` (

  `DepartmentID` INT NOT NULL,

  `DepartmentTitle` VARCHAR(45) NULL,

  `NoOfEmployees` VARCHAR(45) NULL,

  `AccountNo` INT NULL,

  `StoreID` INT NULL,

  `FranchiseTeamID` INT NULL,

  `tblAccount_AccountNo` INT NOT NULL,

  `tblStore_StoreID` INT NOT NULL,

  PRIMARY KEY (`DepartmentID`),

  INDEX `fk_tblDepartment_tblAccount1_idx` (`tblAccount_AccountNo` ASC),

  INDEX `fk_tblDepartment_tblStore1_idx` (`tblStore_StoreID` ASC),

  CONSTRAINT `fk_tblDepartment_tblAccount1`

    FOREIGN KEY (`tblAccount_AccountNo`)

    REFERENCES `735998-Database Fundamentals`.`tblAccount` (`AccountNo`)

    ON DELETE NO ACTION

    ON UPDATE NO ACTION,

  CONSTRAINT `fk_tblDepartment_tblStore1`

    FOREIGN KEY (`tblStore_StoreID`)

    REFERENCES `735998-Database Fundamentals`.`tblStore` (`StoreID`)

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `735998-Database Fundamentals`.`tblFRANCHISE_APP`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `735998-Database Fundamentals`.`tblFRANCHISE_APP` (

  `AppNumber` INT NOT NULL,

  `AppDate` VARCHAR(45) NULL,

  `DecisionDate` VARCHAR(45) NULL,

  `Status` VARCHAR(45) NULL,

  `DepartmentID` INT NULL,

  `TeamID` INT NULL,

  `tblTeam_TeamID` INT NOT NULL,

  PRIMARY KEY (`AppNumber`),

  INDEX `fk_tblFRANCHISE_APP_tblTeam1_idx` (`tblTeam_TeamID` ASC),

  CONSTRAINT `fk_tblFRANCHISE_APP_tblTeam1`

    FOREIGN KEY (`tblTeam_TeamID`)

    REFERENCES `735998-Database Fundamentals`.`tblTeam` (`TeamID`)

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `735998-Database Fundamentals`.`tblEmployee`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `735998-Database Fundamentals`.`tblEmployee` (

  `EmployeeID` INT NOT NULL,

  `EName` VARCHAR(45) NULL,

  `EEmail` VARCHAR(45) NULL,

  `EAddress` VARCHAR(45) NULL,

  `EGender` VARCHAR(45) NULL,

  `EDOB` VARCHAR(45) NULL,

  `JoiningDate` VARCHAR(45) NULL,

  `CentralOrLocalEmployment` VARCHAR(45) NULL,

  `CANumber` VARCHAR(45) NULL,

  `DepartmentID` INT NULL,

  `tblDepartment_DepartmentID` INT NOT NULL,

  PRIMARY KEY (`EmployeeID`),

  UNIQUE INDEX `tblEmployeecol_UNIQUE` (`EEmail` ASC),

  INDEX `fk_tblEmployee_tblDepartment_idx` (`tblDepartment_DepartmentID` ASC),

  CONSTRAINT `fk_tblEmployee_tblDepartment`

    FOREIGN KEY (`tblDepartment_DepartmentID`)

    REFERENCES `735998-Database Fundamentals`.`tblDepartment` (`DepartmentID`)

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;