Please keep things simple all needed info to Answer is below, will give thumbs up:)!

LINK TO INSERT STATEMENT

https://drive.google.com/file/d/199vcBG3YYArpUExUhNfd3mH83V-SGSCc/view?usp=sharing

& TABLE.SQL

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';

-- ----------------------------------------------------- -- Table state -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS state ( state_id INT NOT NULL AUTO_INCREMENT, name VARCHAR(45) NOT NULL, abb VARCHAR(2) NOT NULL, PRIMARY KEY (state_id), UNIQUE INDEX abb_UNIQUE (abb ASC)) ENGINE = InnoDB;

-- ----------------------------------------------------- -- Table address -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS address ( address_id INT NOT NULL AUTO_INCREMENT, street1 VARCHAR(45) NOT NULL, street2 VARCHAR(45) NULL, city VARCHAR(45) NOT NULL, zip VARCHAR(5) NOT NULL, state_id INT NOT NULL, PRIMARY KEY (address_id), INDEX FK_ADDRESS_STATE_IDX (state_id ASC), CONSTRAINT FK_ADDRESS_STATE FOREIGN KEY (state_id) REFERENCES state (state_id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

-- ----------------------------------------------------- -- Table person -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS person ( person_id INT NOT NULL AUTO_INCREMENT, first_name VARCHAR(45) NOT NULL, last_name VARCHAR(45) NOT NULL, sex VARCHAR(1) NOT NULL, dob DATETIME, address_id INT, PRIMARY KEY (person_id), INDEX FK_PERSON_ADDRESS_IDX (address_id ASC), CONSTRAINT FK_PERSON_ADDRESS FOREIGN KEY (address_id) REFERENCES address (address_id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

-- ----------------------------------------------------- -- Table branch -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS branch ( branch_no VARCHAR(5) NOT NULL, address_id INT NOT NULL, PRIMARY KEY (branch_no), INDEX FK_BRANCH_ADDRESS_IDX (address_id ASC) COMMENT ' ', UNIQUE INDEX ADDRESS_ID_UNIQUE (address_id ASC), CONSTRAINT FK_BRANCH_ADDRESS FOREIGN KEY (address_id) REFERENCES address (address_id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

-- ----------------------------------------------------- -- Table staff -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS staff ( staff_no VARCHAR(5) NOT NULL, person_id INT NOT NULL, position VARCHAR(45) NULL, salary INT NULL, branch_no VARCHAR(5) NOT NULL, PRIMARY KEY (staff_no), INDEX FK_STAFF_PERSON_IDX (person_id ASC), UNIQUE INDEX PERSON_ID_UNIQUE (person_id ASC), INDEX fk_staff_branch1_idx (branch_no ASC), CONSTRAINT FK_STAFF_PERSON FOREIGN KEY (person_id) REFERENCES person (person_id) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_staff_branch1 FOREIGN KEY (branch_no) REFERENCES branch (branch_no) 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;

Continue with database from the previous labs 1. Show the maximum and average salary of all managers. 2. Show all positions (excluding NULL) along with: i. Number of staff members ii. Maximum salary iii. Average salary I position | Count | Maximum Average | Assistant | Manager | Supervisor | Teller 28 24 1 27 1 21 1 300001 24642.8571 210000 | 196250.0000 360000 | 336296.2963 | 1000001 84761.90481 3. Using subqueries, show the branch(s) that have a manager. | branchNo street | city | postcode | B005 | B003 22 Deer Rd | London | SWI AEH | 163 Main St | Glasgow | G11 99x