Your final task is to use your tables for an update and for reports. There are two requirements. You will demonstrate an update that includes two...

Your final task is to use your tables for an update and for reports. There are two requirements.

1. You will demonstrate an update that includes two tables and uses a subquery. Make sure to prove that your update executed correctly by showing data in the tables before and after the update.
2. You will also develop at least four meaningful reports. One must include a join, one must use a subquery, and one must use an aggregate function. You need to describe the business requirement of the update and each report. Note that numeric PK values are not often meaningful to a person looking at the report. 
3. You will create a script file that has each of the queries called yourname_task3.txt. You will also document the results of this task in a Word document called yourname_reports.docx to show the result of each query. For the update and each report,

• describe the update and report requirement;
• show the query; and 
• show the result of the query.

where my SQL

DROP TABLE IF EXISTS `Customer` ;

DROP TABLE IF EXISTS `Vehicle_Info` ;

DROP TABLE IF EXISTS `Insurance_info` ;

DROP TABLE IF EXISTS `Address` ;

DROP TABLE IF EXISTS `Rental_Transaction` ;

DROP TABLE IF EXISTS `Department` ;

DROP TABLE IF EXISTS `Employee` ;

CREATE TABLE Address (

Address_id char(9) PRIMARY KEY,

Address_Line_1 varchar(120) NOT NULL,

Address_Line_2 varchar(120),

City varchar(40),

State varchar(20),

Zip VARCHAR(5) NOT NULL);

CREATE TABLE Customer (

customer_id INT(8) PRIMARY KEY AUTO_INCREMENT, 

customer_firstname VARCHAR(20) NOT NULL,

customer_lastname VARCHAR(30) NOT NULL,

customer_business_name VARCHAR(65),

customer_license_number VARCHAR(20) NOT NULL,

customer_insurance_carrier VARCHAR(65) NOT NULL,

customer_policynumber VARCHAR(20) NOT NULL,

customer_phone VARCHAR(20),

address_id CHAR(9),

CONSTRAINT customer_fk_address

FOREIGN KEY (address_id)

REFERENCES address (address_id)

);

CREATE TABLE Insurance_info (

Insurance_policynumber INTEGER PRIMARY KEY,

Insurance_carrier varchar(65) NOT NULL,

Insurance_cost DECIMAL(15,2) NOT NULL);

CREATE TABLE Vehicle_Info (

Vin Char(17) PRIMARY KEY COMMENT 'Vin #',

Vehicle_make VARCHAR(25) NOT NULL,

Vehicle_model VARCHAR(25) NOT NULL,

Vehicle_year YEAR NOT NULL,

Vehicle_mileage INTEGER(7) NOT NULL,

vehicle_color Varchar(20) NOT NULL,

Vehicle_transmission VARCHAR(35) NOT NULL,

Insurance_policynumber INTEGER,

CONSTRAINT Vehicle_fk_Insurance_policynumber

foreign key (Insurance_policynumber) references Insurance_info(Insurance_policynumber));

CREATE TABLE Department (

department_id SMALLINT PRIMARY KEY,

department_name VARCHAR(20) NOT NULL UNIQUE,

department_headcount INT(8) NOT NULL,

department_budget DECIMAL(15,2) NOT NULL

);

CREATE TABLE Employee(

employee_id INT PRIMARY KEY AUTO_INCREMENT,

employee_first_name VARCHAR(65) NOT NULL,

employee_last_name VARCHAR(65) NOT NULL,

employee_phone VARCHAR(20) NOT NULL,

employee_pay DECIMAL(15,2) NOT NULL,

employee_benefits VARCHAR(65),

address_id CHAR(9) NOT NULL,

department_id SMALLINT NOT NULL,

CONSTRAINT employee_fk_address FOREIGN KEY (address_id) REFERENCES address (address_id),

CONSTRAINT employee_fk_department FOREIGN KEY (department_id) REFERENCES department (department_id));

CREATE TABLE Rental_Transaction (

transaction_number INT(8) PRIMARY KEY,

rental_pickupdate DATETIME NOT NULL,

rental_returndate DATETIME NOT NULL,

rental_pickupmileage INT(8) NOT NULL,

rental_returnmileage INT(8) NOT NULL,

rental_pickupfuel INT(3) NOT NULL,

rental_returnfuel INT(3) NOT NULL,

rental_pickupdamage VARCHAR(120) NOT NULL,

rental_returndamage VARCHAR(120) NOT NULL,

rental_insurancewaiver BOOLEAN NOT NULL,

rental_gasprepay BOOLEAN NOT NULL,

rental_GPS BOOLEAN NOT NULL,

rental_pricing DECIMAL(15,2) NOT NULL,

customer_id INT(8) NOT NULL,

VIN CHAR(17) NOT NULL,

employee_id INT (8) NOT NULL,

CONSTRAINT rental_transaction_fk_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id),

CONSTRAINT rental_fk_vin FOREIGN KEY (VIN) REFERENCES vehicle_info (VIN),

CONSTRAINT rental_fk_employee FOREIGN KEY (employee_id) REFERENCES Employee (employee_id));

INSERT INTO Insurance_info (Insurance_policynumber, Insurance_carrier, Insurance_cost ) values

('1234567890','Springfield Insurance','1156.00'), 

('1234567891','State Farm Insurance','1199.89'), 

('1234567892','Progessive Insurance','1369.76'),

('1234567893','Downhill Insurance','1528.68'),

('1234567894','Fresh Prints Insurance','1475');

INSERT INTO Vehicle_Info(Vin,Vehicle_make,Vehicle_model,Vehicle_year,Vehicle_mileage,Vehicle_color,Vehicle_transmission,Insurance_policynumber) values

('123456789abcdefgh','Ford','Mustang','2012','98000','Blue','manual','1234567890'),

('987654321abcdefgh','Dodge','Dart','2017','18246','Blue','manual','1234567891'),

('543698721abcdefgh','Ford','Tarus','2010','110000','Purple','manual','1234567892'),

('569832164abcdefgh','Dodge','Charger','2011','25000','Black','Automatic','1234567893'),

('765942310abcdefgh','Ford','Mustang','2018','15788','Red','Automatic','1234567894');

 INSERT INTO Address(Address_id, Address_Line_1, Address_Line_2, City, State, Zip) values

(1,'123 Cherry Street',NULL,'Albany','New York','12202'), 

(2,'123 Apple Street', 'apt 103','Columbus','Ohio','43202'), 

(3,'123 Banana Street',NULL,'Austin','Texas','78717'), 

(4,'321 Tomato Street',NULL,'Sacramento','California','95816'), 

(5,'321 Kiwi Steet','apt 502','Honolulu','Hawaii','69817'), 

(6,'321 Orange Street',NULL,'Denver','Colorado','80247');

INSERT INTO Customer(customer_id,customer_firstname,customer_lastname,customer_business_name,customer_license_number,customer_insurance_carrier,customer_policynumber,customer_phone,address_id) values

(1,'Homer', 'Simpson', 'Springfield Nuclear Power Plant', '12345678910', 'Springfield Insurance', 'DOH1234', '814-555-1234', '1'),

(2,'Lynette', 'Scavo', NULL,'22345678910', 'State Farm Insurance', '981873', '224-555-435', '2'),

(3,'Jerry', 'Seinfeld', 'Self Employed', '32345678910', 'Progessive Insurance', '762E-90877', '212-555-2390', '3'),

(4,'Danny', 'Tanner', 'Wake Up, San Francisco', '42345678910', 'Downhill Insurance', '2828282828', '415-555-2424', '4'),

(5,'Philip', 'Banks', 'City of Bel Air', '12345678910', 'Fresh Prints Insurance', 'BANKS7654', '410-555-2000', '5');

INSERT INTO Department(department_id, department_name, department_headcount, department_budget) values

(111,'Administration','12','15000.00'),

(222,'Sales','10','2000.00'),

(333,'Marketing','15','7000.00'),

(444,'Service','25','10000.00'),

(555,'IT','6','5000.00');

INSERT INTO Rental_Transaction(transaction_number,rental_pickupdate, rental_returndate, rental_pickupmileage, rental_returnmileage, 

                rental_pickupfuel, rental_returnfuel, rental_pickupdamage, rental_returndamage, rental_insurancewaiver,