Your final task is to use your tables for an update and for reports. There are two requirements.
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,
Hi there! Click one of our representatives below and we will get back to you as soon as possible.