THE GEORGE WASHINGTON UNIVERSITY – ISTM 6215 – GROUP PROJECT
Rental Car Inventory Relational Database System Research
Alana Hill Alexander J. Singleton Gabriela Marchiani Kangkyu Lee Thomas Lebeau
April 30, 2017
The George Washington University School of Business
Our Mission Statement
4
Mission Objectives
4
Requirements
4
Entity Relationship Diagram (ERD)
5
Attribute Modeling Tables Accessory (GPS, etc.) etc.)
6 6 6
Customer
6
Employee
6
Rental Office Location
6
Reservation
7
Vehicle
7
Post-Mortem
8
References
10
Appendix
11
Entity Relationship Diagram
11
Relational Database Model
12
Queries
13
Number of Rater Per Location
13
Number of Employee Positions
14
Number of Employee Positions
15
Total Revenue Collected Per State
16
Models Available Within Range
17
Subquery to View Reservation with the Oldest Date
18
Display Customer Reservations with Make and Model of Vehicle
19
Vehicle Maintenance Record Count Completed by Each Employee
20
Total Reservations of Each Rental Office by Sales in Descending Order
21
Total Reservations of Each Rental Office by Sales in Descending Order
22
Employee Information Grouped by Last Name
23
Total Cost of Reservation by Customer ID and Rental Vehicle VIN#
24
Maintenance Log Ordered by Date
25
Reservation Date and Cost According to Rental Office Location
26
Cost of Each Accessory According to Descending Order
27
Report Displaying Customer, Reservation and Vehicle Information
28
Prices of Reservations with the Accessory Cost Added
30
Vehicle Age with Model and Make
31
Employee Count by Rental Office
32
Show Vehicles with Maintenance Records
33
Reservation Count by Office Location
34
Customer Accessory Selection
35
Number of Rental Days
37
1. Our Our Miss Mission ion State Statemen mentt a. Our mission is is to satisfy our customer’s customer’s rental car needs needs while delivering delivering the best quality, service and value; we believe the ideal system is a human-centered user-experience modeled according to a consistent object-oriented model from the front-end all the way to the back-end of the database. database.1 Concordantly, Concordantly, we believe both the entity relationship relationship diagram and relational database-model specifications required by the rental-car management inventory system will seek to: i. Proactiv Proactively ely maintain maintain and monitor monitor the database database system system to preve prevent nt any potential issues.2 ii. Accurately diagnose and forecast the database system health and capacity to maximize return on investment for the rental-car inventory management system and promptly respond to the stakeholder’s support request(s) to remove any work bottleneck on DBA side.3 iii. Persistently Persistently seek innovative ways to improve DBA work efficiently yet effectively.4 2. Missio Mission n Obje Objecti ctive ves s a. Check vehicle inventory by location location b. Associate customer customer information information with rental rental car reservation reservation c. Track rental car inventory d. Track rental car maintenance e. Track accessory inventory 3. Requ Requir irem emen ents ts a. A rental car car company wants wants to monitor monitor vehicle-inventory vehicle-inventory and and reservations. b. For each vehicle, vehicle, we will record Make, Make, Model, Year, Year, Color, LicensePlate, LicensePlate, Mileage and VIN. c. For each customer, customer, we assign assign a unique-identifi unique-identifier er (ID), FirstName, FirstName, LastName, PhoneNumber, EmailAddress, Driver’s License, and RewardsNumber. d. For each rental-office rental-office location, we will will assign unique unique identifier (ID) for RentalOfficeID, StreetAddress, City, State and ZipCode.
1
Singleton, Singleton, 3 Singleton, 4 Singleton, 2
Alexander Alexander Alexander Alexander
J., J., J., J.,
and and and and
Shaulinator Shaulinator Shaulinator Shaulinator
via via via via
Database Database Database Database
Admininstrators Admininstrators Admininstrators Admininstrators
StackExchange StackExchange StackExchange StackExchange
e. Every time a customer customer reserves a car, car, the reservation reservation will be assigned assigned a unique-identifier (ID), Pick-up Date, ReturnDate, DailyRate, Total, CustomerID and RentalOfficeID. f. A customer customer can reserve reserve many many cars, but must have at least one rental reservation. g. A car may be reserved by many customers, customers, or none at all. h. A maintenance-log maintenance-log will record VehicleID, VehicleID, Procedure, Procedure, Date. i. The rental-compan rental-company y will will offer accessories for each each customer-reser customer-reservation. vation. For each accessory, we will maintain a unique-identifier called SerialNumber, type, cost, and quantity which will correspond to RentalOfficeID, CustomerID, VehicleID and ReservationID. j. A Rental Office Location can have many vehicles, but must have at least one. k. A Vehicle may may be used by multiple Rental Office Locations, Locations, but but must have at least one. l. A Vehicle Vehicle may may have have many many reservations, reservations, or none none at all. m. A Rental Office Location can have many employees, but must have at least one. n. An Employee can work at multiple Rental Rental Office Locations, Locations, but must must work at at least one. o. A Rental Office Location Location can have multiple multiple Reservations, Reservations, and may have have none. p. A Reservation Reservation must have have one Rental Rental Office Location. Location. q. A Reservation Reservation must must have one one vehicle. vehicle. r. A Reservation Reservation may have one or more accessories, accessories, but may have none. none. s. An Accessory Accessory can be used in multiple multiple Reservations, Reservations, or none none at all. t. Maintenance Maintenance must be completed completed by at least one Employee, but can be completed by multiple employees u. The attribute ‘vehicle ‘vehicle age’ is calculated calculated by subtracting subtracting vehicle model model year from the date of reservation. 4. Entity Entity Relat Relations ionship hip Diagram Diagram (ERD) (ERD) a. An Employee may perform multiple maintenance maintenance activities, activities, or they they may not perform any. b. A Rental Office Location Location may have multiple multiple customers, customers, or none at all c. A Customer may use multiple multiple Rental Office Locations, Locations, but must use at least one (five entities and at least four binary relationships). relationship s). At least one of the relationships relationships should be many many to many. Show all attributes attributes of all entities. d. We invite review of the ERD modeled at [Lucid Charts](link). Charts](link).
e. Attri Attribu bute te Model Modeling ing i. Bold - Foreign Key ii. ii. Und Underli erlin ne - Potential Primary Key iii. Tables 1. Acces Accesso sory ry (GP (GPS, S, etc.) etc.) a. Serial Serial Number Number b. Type Type c. Cost Cost d. Quantity Quantity 2. Custo stomer mer a. Custom CustomerID erID b. Customer Customer FName c. Customer Customer LName LName d. Customer CustomerEmai Emaill e. Driver’s License Number (U) f. RewardNu RewardNumber mber (O) 3. CustomerP CustomerPhone honeNo No a. CustomerID CustomerID (FK) b. Customer CustomerPhon PhoneNo eNo 4. Emplo mploy yee a. Employ EmployeeI eeID D b. Employee EmployeeFName FName c. Employee EmployeeLNam LName e d. Title Title e. SSN (U ) 5. Employing Employing a. RentalOff RentalOfficeID iceID (FK) b. EmployeeID EmployeeID (FK) 6. Includ Includes es a. SerialNumb SerialNumber er (FK) b. Reservation ReservationID ID (FK) 7. Maintena Maintenance nce Log a. EmployeeID EmployeeID (FK) b. VIN (FK) (FK) c. Da Date te d. Procedur Procedure e 8. Renta Rentall Offic Office e Locat Location ion a. RentalOf RentalOfficeI ficeID D b. StreetAd StreetAddres dress s
c. City City d. State State e. ZipCod ZipCode e 9. Reserv Reserves es a. SerialNumb SerialNumber er (FK) b. ReservationID (FK ) 10.Reservation a. Reservation ReservationID ID b. Da Date( te(s) s) c. Da Daily ily Rate Rate d. Total Total e. Customer CustomerID ID (FK) f. RentalOf RentalOfficeI ficeID D (FK) 11.Vehicle a. VIN b. Make Make c. Mode Modell d. Year Year e. Colo Colorr f. License License Plate Plate (U) g. Milea Mileage ge
Post-Mortem ●
●
●
●
●
Our initial models included two relations that had identical names: “Reserves.” This was a simple oversight that merely required us to alter the name of the relation between Accessory and Reservation to “Includes.” Our initial design called for a ternary ternary relationship between between Vehicle, Accessory, Accessory, and Reservation tables. However, after discussion we decided to divide the single relation out into two separate relations, one between Vehicle and Reservation tables, and one between Accessory and Reservation tables. Our logic behind this decision is that it is fully possible for a reservation to not have any accessories attached to it, and by automatically including a column for the accessory serial number in the relation table could result in a significant amount of null values for that column, which would have served as a foreign key. In dividing the two relations into separate tables, we were able to ensure that there would be no null foreign key values. The initial design only allowed allowed for a single phone number to be assigned to each customer. However, after discussion it was decided that this would not be sufficient, as multiple contact numbers are common. To solve for this, we added an additional table that is linked to the Customer table, using CustomerID as a foreign key to incorporate multiple phone numbers. Our initial implementation of this table was incorrect though, as there was no primary key assigned to the table. After testing the initial table CREATE and data INSERT statements, we discovered our error and established the phone number column as the primary key column. At the start, we had misstated the requirements requirements necessary necessary for how tracking vehicle maintenance would work. We had incorrectly assumed a one to many relationship between the vehicle and the employee, thus overlooking the need for a relational table that linked these two entities. Once the requirements had been corrected, we also designed the Maintenance_Log table, that would store the necessary, and relevant data, for tracking maintenance updates performed on the vehicles in question. Our updated design called for a column labelled “Procedure” in the Maintenance_Log Table. During our design phase though, we did not realize that “Procedure” is a reserved term in SQL, and it would not be possible for us to use this title for the column. After troubleshooting our CREATE statement, we discovered the error, and had to backtrack and update our design to re-name this column “Maintenance_Procedure.”
Our updated design was also lacking another critical piece of data in regards to the Maintenance_Log table: the primary key. After experiencing some issues in creating and populating the table, we realized that each individual procedure would needs its own unique identifier, and added in the Maintenance_ID column. requirements for our Reservation table, we had specified specified the ● In defining the requirements need to store data that would allow for the calculation of total cost of the reservation, and decided to do so by tracking the dates of the rental and the daily cost. However, in our initial design, the table only had a single column for reservation dates. Storing this data in a single column would not have allowed us to easily track the total number of days that the reservation spanned, so we had to update the database to hold an additional column, thus allowing us to split the dates into a Pick_Up_Date column and a Return_date column. ●
References 1. Singleto Singleton, n, Alexan Alexander der J., J., and and Shauli Shaulinato nator. r. "What is a database mission-statement?" Sql What is a database mission-statement? - Database Administrators Stack Exchange : [https://dba.stackexchange.com/questions/165669/what-is-a-database-mission-statemen t]. Stack Exchange: Database Administrators, 28 Feb. 2017. Web. 08 Mar. 2017.
Appendix Entity Relationship Diagram
Relational Database Model
Queries The following code-blocks are MySQL-code prefaced with comments explaining the subsequent MySQL-query above the next code-block containing the terminal-output. For our top-5 selections, please see the attached PowerPoint presentation included within the compressed final-deliverable final-deliverable file.
Number of Rater Per Location
-- This query will count all records within the Daily_Rate column using the count-function count-functi on returning the number of records within the RESERVATIONRESERVATION-table table grouped by RentalOfficeID, which will then be displayed as an alias-column, "Number of rates per location." SELECT Daily_Rate, count(*) AS "Number of rates per location" FROM RESERVATION RentalOfficeID; ID; GROUP BY RentalOffice
Number of Rater Per Location Terminal Output
mysql> SELECT Daily_Rate, count(*) AS "Number of rates per location" -> FROM RESERVATION -> GROUP BY RentalOfficeI RentalOfficeID; D; +------------+ ------------+------------------------------------------------------+ ----+ | Daily_Rate | Number of rates per location | +------------+ ------------+------------------------------------------------------+ ----+ | 95.00 | 1 | | 125.00 | 2 | | 125.00 | 2 | | 80.00 | 1 | +------------+ ------------+------------------------------------------------------+ ----+ 4 rows in set ( (0.08 0.08 sec) sec)
Number of Employee Positions -- This query will count all of the employed positions by selecting the Title Column from the Employee-table using the COUNT method in conjunction with the DISTINCT method to effectively -- count all unique last-names then grouping the count of unique last names corresponding correspondin g to those employee-positions employee-positions by Title from the EMPLOYEE-Tab EMPLOYEE-Table le displayed in an alias-table "COUNT OF EMPLOYEES." "TITLE", , COUNT(DISTINCT EmployeeLNAME EmployeeLNAME) ) SELECT TITLE AS "TITLE" AS "Number of employee-positions" FROM EMPLOYEE GROUP BY TITLE;
Number of Employee Positions Terminal Output
mysql> SELECT TITLE AS "TITLE" "TITLE", , COUNT(DISTINCT EmployeeLNAM EmployeeLNAME) E) -> AS "Number of employee-pos employee-positions" itions" -> FROM EMPLOYEE GROUP BY TITLE; +--------------------------------------------------+-----------+------------------------------------------+ -----+ | TITLE | Number of employee-pos employee-positions itions | +--------------------------------------------------+-----------+------------------------------------------+ -----+ | Customer Care Specialist | 4 | | Mechanic | 4 | | Office Manager | 4 | +--------------------------------------------------+-----------+------------------------------------------+ -----+ 3 rows in set ( (0.07 0.07 sec) sec) mysql>
Number of Employee Positions -- The query utilizes the SOUNDEX-method to find a customer that sounds phoentically similar to "Zitanna" (e.g. "Find name that sounds like using Zitanna"). -- According to Wikipedia, "SOUNDEX was developed in 1985 by genealogist Gary Mokotoff and later improved by genealogist Randy Daitch because of problems they encountered -- while trying to apply the Russell Soundex to Jews with Germanic or Slavic surnames (such as Moskowitz vs. Moskovitz or Levine vs. Lewin)." (https://en.wikipedia.org (https://en. wikipedia.org/wiki/Soundex /wiki/Soundex) ) SELECT CustomerFName FROM CUSTOMER (CustomerFName) me) = SOUNDEX('Zitanna' 'Zitanna'); ); WHERE SOUNDEX(CustomerFNa
Number of Employee Positions Terminal Output
mysql> SELECT CustomerFName -> FROM CUSTOMER -> WHERE SOUNDEX(CustomerFN (CustomerFName) ame) = SOUNDEX('Zitanna' 'Zitanna'); ); +---------------+ | CustomerFName | +---------------+ | Zatanna | +---------------+ 1 row in set ( (0.07 0.07 sec) sec)
Total Revenue Collected Per State -- This query will display the total-revenue collected per state by utiliziing an implicit-join format in addition to alias tables to effectively displaying the total-revenue collected by state correspondin corresponding g to the "RentalOfficeID" column contained within the two separate tables, joining or "zipping-up" the data separated between between the tables. According to OracleSQL blog-spot, the "implicit join notation" simply lists the tables for joining. (http://sql-plsql.blogspo (http://sqlplsql.blogspot.com/2011/02 t.com/2011/02/explicit-imp /explicit-implicit-sql-joi licit-sql-joins.html) ns.html) SELECT STATE, Total FROM RENTAL_OFFICE_LOCATION r,
RESERVATION i r.RentalOfficeID=i.Rental ceID=i.RentalOfficeID OfficeID WHERE r.RentalOffi ORDER BY Total;
Total Revenue Collected Per State Terminal Output
mysql> SELECT STATE, Total -> FROM RENTAL_OFFICE_LOCATION r, -> RESERVATION i -> WHERE r.RentalOffi r.RentalOfficeID=i.Rental ceID=i.RentalOfficeID OfficeID -> ORDER BY Total; +-------+---------+ | STATE | Total | +-------+---------+ | OR | 285.00 | | NY | 380.00 | | CO | 575.00 | | CO | 875.00 | | NY | 875.00 | | ME | 1120.00 | +-------+---------+ 6 rows in set ( (0.06 0.06 sec) sec)
Models Available Within Range -- The query should should only display display the models models within the above range, which this case should be two different -- makes and the two corresponding corresponding models accordingly: accordingly: "available "available make make and models with mileage at least 10,000 miles but less than 20,000 miles." SELECT Make, Model FROM VEHICLE WHERE Mileage >= 10000 20000; ; AND Mileage < 20000
Models Available Within Range Terminal Output
mysql> SELECT Make, Model -> FROM VEHICLE -> WHERE Mileage >= 10000 -> AND Mileage < 20000 20000; ; +---------+----------+ | Make | Model | +---------+----------+ | Hyundai | Veloster | | Chevy | Tahoe | +---------+----------+ 2 rows in set ( (0.06 0.06 sec) sec)
Subquery to View Reservation with the Oldest Date -- This query uses a subquery to view a reservation with the oldest date. SELECT * FROM RESERVATION Pick_Up_Date e = (SELECT MIN(Pick_Up_Date) FROM RESERVATION); WHERE Pick_Up_Dat
Subquery to View Reservation with the Oldest Date Terminal Output
mysql> SELECT * FROM RESERVATION -> WHERE Pick_Up_Date = ( SELECT MIN(Pick_Up_Date) FROM RESERVATION) RESERVATION); ; +---------------------------+-----------+--------------+-----------+-------------+-----------+------------+-----------+--------+-------+-----------+----------------+ | ReservationID ReservationID | Pick_Up_Date | Return_Date Return_Date | Daily_Rate Daily_Rate | Total | CustomerID | RentalOfficeID | +---------------------------+-----------+--------------+-----------+-------------+-----------+------------+-----------+--------+-------+-----------+----------------+ | 2 | 2017-03-01 | 2017-03-03 | 95.00 | 285.00 | 2 | 1 | +---------------------------+-----------+--------------+-----------+-------------+-----------+------------+-----------+--------+-------+-----------+----------------+ 1 row in set ( (0.06 0.06 sec) sec)
Display Customer Reservations with Make and Model of Vehicle -- This query will display display customer reservations with the make and model of vehicle using three inner joins. CUSTOMER.CustomerID, stomerID, CUSTOMER.Cus CUSTOMER.CustomerFname tomerFname AS First_Name, SELECT CUSTOMER.Cu CUSTOMER.CustomerLname AS Last_Name, RESERVES.VIN, VEHICLE.Make, VEHICLE.Model FROM (((CUSTOMER CUSTOMER.CustomerID tomerID = RESERVATION RESERVATION.CustomerID) .CustomerID) INNER JOIN RESERVATION ON CUSTOMER.Cus RESERVATION.ReservationID eservationID = RESERVES.Rese RESERVES.ReservationID) rvationID) INNER JOIN RESERVES ON RESERVATION.R INNER JOIN VEHICLE ON RESERVES.VIN = VEHICLE.VIN);
Display Customer Reservations with Make and Model of Vehicle Terminal Output
mysql> SELECT CUSTOMER.Cust CUSTOMER.CustomerID, omerID, CUSTOMER.Cu CUSTOMER.CustomerFname stomerFname AS First_Name, CUSTOMER.CustomerLname AS Last_Name, RESERVES.VIN, VEHICLE.Make, VEHICLE.Model -> FROM (((CUSTOMER -> INNER JOIN RESERVATION ON CUSTOMER.CustomerID = RESERVATION.CustomerID) -> INNER JOIN RESERVES ON RESERVATION. RESERVATION.ReservationID ReservationID = RESERVES.ReservationID) -> INNER JOIN VEHICLE ON RESERVES.VIN = VEHICLE.VIN); +------------+ ------------+------------+ ------------+-------------------------+-----------+------------+---------+-+---------+----------+ --------+ | CustomerID | First_Name | Last_Name | VIN | Make | Model | +------------+ ------------+------------+ ------------+-------------------------+-----------+------------+---------+-+---------+----------+ --------+ | 1 | John | Constantine | 11111AAAAA | Honda | Accord | | 2 | Hal | Jordan | 22222BBBBB | Hyundai | Veloster | | 3 | Barry | Allen | 33333CCCCC | Chevy | Tahoe | | 6 | Zatanna | Zatara | 33333CCCCC | Chevy | Tahoe | | 4 | Dinah | Lance | 44444DDDDD | Audi | A4 | | 5 | Felicity | Smoak | 55555EEEEE | Dodge | Charger | +------------+ ------------+------------+ ------------+-------------------------+-----------+------------+---------+-+---------+----------+ --------+ 6 rows in set ( (0.07 0.07 sec) sec)
Vehicle Maintenance Record Count Completed by Each Employee -- This query counts the maintenance records that each employee has completed. EMPLOYEE.EmployeeID, ployeeID, EMPLOYEE.Emp EMPLOYEE.EmployeeFname loyeeFname AS First_Name, SELECT EMPLOYEE.Em EMPLOYEE.EmployeeLname AS Last_Name, COUNT(MAINTENANCE_LOG.Maintenance_ID) Maintenance_Count ount AS Maintenance_C FROM EMPLOYEE MAINTENANCE_LOG LOG ON EMPLOYEE.EmployeeID = INNER JOIN MAINTENANCE_ MAINTENANCE_LOG.EmployeeID GROUP BY EMPLOYEE.Empl EMPLOYEE.EmployeeID; oyeeID;
Vehicle Maintenance Record Count Completed by Each Employee Terminal Output
EMPLOYEE.EmployeeID, ployeeID, EMPLOYEE.Emp EMPLOYEE.EmployeeFname loyeeFname AS First_Name, SELECT EMPLOYEE.Em EMPLOYEE.EmployeeLname AS Last_Name, COUNT(MAINTENANCE_LOG.Maintenance_ID) Maintenance_Count ount AS Maintenance_C -> FROM EMPLOYEE -> INNER JOIN MAINTENANCE_L MAINTENANCE_LOG OG ON EMPLOYEE.EmployeeID = MAINTENANCE_LOG.EmployeeID GROUP BY EMPLOYEE.Empl EMPLOYEE.EmployeeID; oyeeID; +------------+ ------------+------------+ ------------+-----------+-----------+-------------------------------+ -----+ | EmployeeID | First_Name | Last_Name | Maintenance_Count | +------------+ ------------+------------+ ------------+-----------+-----------+-------------------------------+ -----+ | 9 | Bruce | Wayne | 1 | | 10 | Clark | Kent | 1 | | 11 | Lex | Luthor | 1 | | 12 | Edward | Nygma | 1 | +------------+ ------------+------------+ ------------+-----------+-----------+-------------------------------+ -----+ 4 rows in set ( (0.07 0.07 sec) sec)
Total Reservations of Each Rental Office by Sales in Descending Order --This query adds the total rervations of each rental office by sales in descending order. The SUM function, a join, group by and order by are used. SELECT SUM(RESERVATION.Total) AS
Total_Sales,RENTAL_OFFICE_LOCATION.Ren Total_Sales,RENTAL_OFFICE _LOCATION.RentalOfficeID talOfficeID FROM RESERVATION RENTAL_OFFICE_LOCATION E_LOCATION ON RESERVATION. RESERVATION.RentalOfficeI RentalOfficeID D = INNER JOIN RENTAL_OFFIC RENTAL_OFFICE_LOCATION.Re RENTAL_OFFIC E_LOCATION.RentalOfficeID ntalOfficeID GROUP BY RESERVATION. RESERVATION.RentalOfficeI RentalOfficeID D ORDER BY SUM(RESERVATION.Total) DESC;
Total Reservations of Each Rental Office by Sales in Descending Order
mysql> SELECT SUM(RESERVATION.Total) AS Total_Sales,RENTAL_OFFICE Total_Sales, RENTAL_OFFICE_LOCATION.Ren _LOCATION.RentalOfficeID talOfficeID FROM RESERVATION -> INNER JOIN RENTAL_OFFICE RENTAL_OFFICE_LOCATION _LOCATION ON RESERVATION.R RESERVATION.RentalOfficeID entalOfficeID = RENTAL_OFFICE_LOCATION.Re RENTAL_OFFIC E_LOCATION.RentalOfficeID ntalOfficeID GROUP BY RESERVATION. RESERVATION.RentalOfficeI RentalOfficeID D ORDER BY SUM(RESERVATION.Total) DESC; +-------------+----------------+ | Total_Sales | RentalOfficeID | +-------------+----------------+ | 1450.00 | 3 | | 1255.00 | 2 | | 1120.00 | 4 | | 285.00 | 1 | +-------------+----------------+ 4 rows in set ( (0.07 0.07 sec) sec)
Total Reservations of Each Rental Office by Sales in Descending Order VEHICLE.Year, r, VEHICLE.Make VEHICLE.Make, , VEHICLE.Mod VEHICLE.Model, el, RESERVES.VIN RESERVES.VIN, , SELECT VEHICLE.Yea SUM(RESERVATION.Total) AS TOTAL FROM ((VEHICLE RESERVES.VIN) ) INNER JOIN RESERVES ON VEHICLE.VIN = RESERVES.VIN RESERVES.ReservationID ervationID = INNER JOIN RESERVATION ON RESERVES.Res RESERVATION.ReservationID) GROUP BY VEHICLE.VIN ORDER BY SUM(RESERVATION.Total) DESC;
Total Reservations of Each Rental Office by Sales in Descending Order Terminal Output
mysql> SELECT VEHICLE.Year, VEHICLE.Make, VEHICLE.Model, RESERVES.VIN, SUM(RESERVATION.Total) AS TOTAL -> FROM ((VEHICLE -> INNER JOIN RESERVES ON VEHICLE.VIN = RESERVES.VIN) -> INNER JOIN RESERVATION ON RESERVES.ReservationID = RESERVATION.ReservationID) GROUP BY VEHICLE.VIN ORDER BY SUM(RESERVATION.Total) DESC; +------+-----------+---------+-----------+----------+-----------+------------+---------+ -+---------+ | Year | Make | Model | VIN | TOTAL | +------+-----------+---------+-----------+----------+-----------+------------+---------+ -+---------+ | 2012 | Chevy | Tahoe | 33333CCCCC | 1500.00 | | 2014 | Honda | Accord | 11111AAAAA | 875.00 | | 2017 | Audi | A4 | 44444DDDDD | 875.00 | | 2015 | Dodge | Charger | 55555EEEEE | 575.00 | | 2016 | Hyundai | Veloster | 22222BBBBB | 285.00 | +------+-----------+---------+-----------+----------+-----------+------------+---------+ -+---------+ 5 rows in set ( (0.06 0.06 sec) sec)
Employee Information Grouped by Last Name -- 1.) This query will pull employee information, and group the results by their last name. SELECT EmployeeID, EmployeeLName, EmployeeFName FROM EMPLOYEE EmployeeLName; e; GROUP BY EmployeeLNam
Employee Information Grouped by Last Name Terminal Output
mysql> SELECT EmployeeID, EmployeeLName, EmployeeFName -> FROM EMPLOYEE -> GROUP BY EmployeeLName EmployeeLName; ; +------------+ ------------+---------------------------+-----------+---------------+ -----+ | EmployeeID | EmployeeLName | EmployeeFName | +------------+ ------------+---------------------------+-----------+---------------+ -----+ | 2 | Curry | Arthur | | 3 | Danvers | Kara | | 5 | Gordon | Barbara | | 4 | Isley | Pamela | | 7 | Jones | John | | 10 | Kent | Clark | | 11 | Luthor | Lex | | 12 | Nygma | Edward | | 8 | Queen | Oliver | | 6 | Quinzel | Harleen | | 1 | Stone | Victor | | 9 | Wayne | Bruce | +------------+ ------------+---------------------------+-----------+---------------+ -----+ 12 rows in set ( (0.06 0.06 sec) sec)
Total Cost of Reservation by Customer ID and Rental Vehicle VIN# -- 2.) This query will pull vehicle and customer identification identification information from existing reservations, and display them in order based on the total cost of the reservation. SELECT VIN, CustomerID, Total FROM RESERVES, RESERVATION RESERVES.ReservationID=RE ervationID=RESERVATION.Res SERVATION.ReservationID ervationID WHERE RESERVES.Res ORDER BY Total;
Total Cost of Reservation by Customer ID and Rental Vehicle VIN# Terminal Output
mysql> SELECT VIN, CustomerID, Total -> FROM RESERVES, RESERVATION -> WHERE RESERVES.Res RESERVES.ReservationID=RE ervationID=RESERVATION.Res SERVATION.ReservationID ervationID -> ORDER BY Total; +------------+ ------------+------------+ ------------+---------+ ---------+ | VIN | CustomerID | Total | +------------+ ------------+------------+ ------------+---------+ ---------+ | 22222BBBBB | 2 | 285.00 | | 33333CCCCC | 6 | 380.00 | | 55555EEEEE | 5 | 575.00 | | 11111AAAAA | 1 | 875.00 | | 44444DDDDD | 4 | 875.00 | | 33333CCCCC | 3 | 1120.00 | +------------+ ------------+------------+ ------------+---------+ ---------+ 6 rows in set ( (0.06 0.06 sec) sec)
Maintenance Log Ordered by Date -- 3.) This query will pull all rows from the maintenance log, and will order them by date from most recent to oldest. SELECT * MAINTENANCE_LOG OG FROM MAINTENANCE_L Maintenance_Date; Date; ORDER BY Maintenance_
Maintenance Log Ordered by Date Terminal Output
mysql> SELECT * -> FROM MAINTENANCE_L MAINTENANCE_LOG OG -> ORDER BY Maintenance_D Maintenance_Date; ate; +----------------------------+-----------+------------+-----------+------------+-----------+------------------+-----------+------------------------------+ | Maintenance_ Maintenance_ID ID | EmployeeID | VIN | Maintenance_ Maintenance_Date Date | Maintenance_Procedure Maintenance_ Procedure | +----------------------------+-----------+------------+-----------+------------+-----------+------------------+-----------+------------------------------+ | 1 | 9 | 11111AAAAA | 2016-05-08 | Oil Change | | 2 | 10 | 10 | 22222 22222BBBBB BBBBB | 2016-07-27 | Tire Replacement | | 3 | 11 | 11 | 33333 33333CCCCC CCCCC | 2016-11-27 | Annual Maintenance | | 4 | 12 | 12 | 44444 44444DDDDD DDDDD | 2017-02-08 | Alignment | Check +----------------------------+-----------+------------+-----------+------------+-----------+------------------+-----------+------------------------------+ 4 rows in set ( (0.06 0.06 sec) sec)
Reservation Date and Cost According to Rental Office Location -- 4.) This query will pull reservation date and cost information, and will display it organized by office location. RentalOfficeID, eID, Pick_Up_Date, Return_Date, Daily_Rate, Total SELECT RentalOffic FROM RESERVATION RentalOfficeID; ID; GROUP BY RentalOffice
Reservation Date and Cost According to Rental Office Location Terminal Output
mysql> SELECT RentalOfficeID, Pick_Up_Date, Return_Date, Daily_Rate, Total -> FROM RESERVATION -> GROUP BY RentalOfficeI RentalOfficeID; D; +----------------------------+-----------+--------------+-----------+-------------+-----------+------------+-----------+---------+ ---+ | RentalOffic RentalOfficeID eID | Pick_Up_Dat Pick_Up_Date e | Return_Date | Daily_Rate | Total | +----------------------------+-----------+--------------+-----------+-------------+-----------+------------+-----------+---------+ ---+ | 1 | 2017-03-01 | 2017-03-03 | 95.00 | 285.00 | | 2 | 2017-06-06 | 2017-06-13 | 125.00 | 875.00 | | 3 | 2017-06-06 | 2017-06-13 | 125.00 | 875.00 | | 4 | 2017-04-05 | 2017-04-19 | 80.00 | 1120.00 | +----------------------------+-----------+--------------+-----------+-------------+-----------+------------+-----------+---------+ ---+ 4 rows in set ( (0.06 0.06 sec) sec)
Cost of Each Accessory According to Descending Order -- 5.) This query will list each accessory type and its related cost, and will organize the data in descending order by said cost. Select Type, Cost FROM ACCESSORY ORDER BY Cost DESC;
Cost of Each Accessory According to Descending Order Terminal Output
mysql> Select Type, Cost -> FROM ACCESSORY -> ORDER BY Cost DESC; +----------------+-------+ | Type | Cost | +----------------+-------+ | Power Inverter | 50.00 | | GPS | 25.00 | | GPS | 25.00 | | Phone Charger | 10.00 | | Phone Charger | 10.00 | +----------------+-------+ 5 rows in set ( (0.07 0.07 sec sec
Report Displaying Customer, Reservation and Vehicle Information /*Report showing Customer Information, Reservation Information, and Vehicle Information */ SELECT RESERVATION.ReservationID,CUSTOMER.CustomerFName AS FirstName, CUSTOMER.CustomerLName AS LastName, CUSTOMER_PHONE_NO.CustomerPhoneNo AS PhoneNumber, RESERVATION.Pick_Up_Date, RESERVATION. Pick_Up_Date, RESERVATION. RESERVATION.Total Total as RentalPrice, RENTAL_OFFICE_LOCATION.Ren RENTAL_OFFICE _LOCATION.RentalOfficeID talOfficeID AS RentalLocatio RentalLocation, n, VEHICLE.VIN AS VehicleInfo CUSTOMER_PHONE_NO, NE_NO, RENTAL_OFFICE_LOCATION, FROM CUSTOMER, RESERVATION, CUSTOMER_PHO
VEHICLE, RESERVES CUSTOMER.CustomerID tomerID = RESERVATION. RESERVATION.CustomerID CustomerID AND WHERE CUSTOMER.Cus CUSTOMER_PHONE_NO.Custome CUSTOMER_PHON E_NO.CustomerID rID = CUSTOMER.Cus CUSTOMER.CustomerID tomerID AND RENTAL_OFFICE_LOCATION.Re RENTAL_OFFICE _LOCATION.RentalOfficeID ntalOfficeID = RESERVATION RESERVATION.RentalOffice .RentalOfficeID ID AND
VEHICLE.VIN = RESERVES.VI RESERVES.VIN N AND RESERVATION.ReservationID RESERVATION.R eservationID = RESERVES.Rese RESERVES.ReservationID rvationID ReservationID; D; ORDER BY ReservationI
Report Displaying Customer, Reservation and Vehicle Information Terminal Output
mysql> SELECT RESERVATION.ReservationID,CUSTOMER.CustomerFName AS FirstName, CUSTOMER.Cus CUSTOMER.CustomerLName tomerLName AS LastName, -> CUSTOMER_PHONE_NO.CustomerPhoneNo AS PhoneNumber, RESERVATION.Pick_Up_Date, RESERVATION. Pick_Up_Date, RESERVATION. RESERVATION.Total Total as RentalPrice, -> RENTAL_OFFICE_LOCATION.RentalOfficeID AS RentalLocati RentalLocation, on, VEHICLE.VIN AS VehicleInfo -> FROM CUSTOMER, RESERVATION, CUSTOMER_PHONE_NO, RENTAL_OFFICE_LOCATION, RENTAL_OFFIC E_LOCATION, VEHICLE, RESERVES -> WHERE CUSTOMER.Cus CUSTOMER.CustomerID tomerID = RESERVATION RESERVATION.CustomerID .CustomerID AND -> CUSTOMER_PHONE_NO.Custome CUSTOMER_PHO NE_NO.CustomerID rID = CUSTOMER.Cust CUSTOMER.CustomerID omerID AND -> RENTAL_OFFICE_LOCATION.Re RENTAL_OFFIC E_LOCATION.RentalOfficeID ntalOfficeID = RESERVATION.RentalOfficeID AND -> VEHICLE.VIN = RESERVES.VIN AND -> RESERVATION.ReservationID RESERVATION. ReservationID = RESERVES.Res RESERVES.ReservationID ervationID -> ORDER BY ReservationID ReservationID; ;
+---------------------------+-----------+-----------+-----------+-------------+-----------+---------------+-----------+--------------+-------+------------+----------------------+----------------+-----------+-------------+ -----+ | ReservationI ReservationID D | FirstName | LastName | PhoneNumber | Pick_Up_Date | RentalPrice | RentalLocation | VehicleInfo | +---------------------------+-----------+-----------+-----------+-------------+-----------+---------------+-----------+--------------+-------+------------+----------------------+----------------+-----------+-------------+ -----+ | 1 | John | Constantine | (555)555-1313 | 2017-06-06 | 875.00 | 3 | 11111AAAAA | | 1 | John | Constantine | (555)555-1919 | 2017-06-06 | 875.00 | 3 | 11111AAAAA | | 2 | Hal | Jordan | (555)555-0101 | 2017-03-01 | 285.00 | 1 | 22222BBBBB | | 2 | Hal | Jordan | (555)555-9999 | 2017-03-01 | 285.00 | 1 | 22222BBBBB | | 3 | Barry | Allen | (555)555-0001 | 2017-04-05 | 1120.00 | 4 | 33333CCCCC | | 4 | Dinah | Lance | (555)555-8888 | 2017-06-06 | 875.00 | 2 | 44444DDDDD | | 5 | Felicity | Smoak | (555)555-1612 | 2017-07-22 | 575.00 | 3 | 55555EEEEE | | 6 | Zatanna | Zatara | (555)555-6666 | 2017-08-19 | 380.00 | 2 | 33333CCCCC | +---------------------------+-----------+-----------+-----------+-------------+-----------+---------------+-----------+--------------+-------+------------+----------------------+----------------+-----------+-------------+ -----+ 8 rows in set ( (0.06 0.06 sec) sec)
Prices of Reservations with the Accessory Cost Added
/* Show prices of reservations with the accessory cost added */ RESERVATION.ReservationI .ReservationID, D, (RESERVATION (RESERVATION.Total .Total + ACCESSORY.Cos ACCESSORY.Cost) t) AS SELECT RESERVATION TotalCost, CustomerLNam CustomerLName e AS LastName FROM RESERVATION, INCLUDES, ACCESSORY, CUSTOMER RESERVATION.ReservationID ReservationID = INCLUDES.Res INCLUDES.ReservationID ervationID AND WHERE RESERVATION. INCLUDES.SerialNumber INCLUDES.Seri alNumber = ACCESSORY.Se ACCESSORY.SerialNumber rialNumber AND CUSTOMER.CustomerID = RESERVATION. CUSTOMER.CustomerID RESERVATION.CustomerID CustomerID RESERVATION.ReservationI ReservationID; D; ORDER BY RESERVATION.
Prices of Reservations with the Accessory Cost Added Terminal Output
mysql> SELECT RESERVATION.R RESERVATION.ReservationID, eservationID, (RESERVATION.Total (RESERVATION.Total + ACCESSORY.Cost) AS TotalCost, CustomerLName AS LastName -> FROM RESERVATION, INCLUDES, ACCESSORY, CUSTOMER -> WHERE RESERVATION. RESERVATION.ReservationID ReservationID = INCLUDES.Res INCLUDES.ReservationID ervationID AND -> INCLUDES.SerialNumber INCLUDES.Ser ialNumber = ACCESSORY.Ser ACCESSORY.SerialNumber ialNumber AND -> CUSTOMER.CustomerID CUSTOMER.Cus tomerID = RESERVATION.C RESERVATION.CustomerID ustomerID -> ORDER BY RESERVATION.R RESERVATION.ReservationID; eservationID; +---------------------------+-----------+-----------+-----------+-------------+ --+ | ReservationI ReservationID D | TotalCost | LastName | +---------------------------+-----------+-----------+-----------+-------------+ --+ | 1 | 900.00 | Constantine | | 2 | 310.00 | Jordan | | 3 | 1130.00 | Allen | | 4 | 925.00 | Lance | +---------------------------+-----------+-----------+-----------+-------------+ --+ 4 rows in set ( (0.08 0.08 sec) sec)
Vehicle Age with Model and Make /*Vehicle Age with model and make*/ VEHICLE.LicensePlate, ensePlate, VEHICLE.Make, VEHICLE.Model, SELECT VEHICLE.Lic (YEAR(RESERVATION. (RESERVATION.Pick_Up_Date) Pick_Up_Date) - VEHICLE.Year VEHICLE.Year) ) AS VehicleAge FROM VEHICLE, RESERVES, RESERVATION RESERVATION.ReservationID ReservationID = RESERVES.Res RESERVES.ReservationID ervationID AND WHERE RESERVATION. VEHICLE.VIN = RESERVES.VIN RESERVES.VIN; ;
Vehicle Age with Model and Make Terminal Output
mysql> SELECT VEHICLE.Licen VEHICLE.LicensePlate, sePlate, VEHICLE.Make, VEHICLE.Mode VEHICLE.Model, l, (YEAR(RESERVATION. (RESERVATION.Pick_Up_Date) Pick_Up_Date) - VEHICLE.Year VEHICLE.Year) ) AS VehicleAge -> FROM VEHICLE, RESERVES, RESERVATION -> WHERE RESERVATION. RESERVATION.ReservationID ReservationID = RESERVES.Res RESERVES.ReservationID ervationID AND -> VEHICLE.VIN = RESERVES.VIN; +--------------------------+---------+-+---------+----------+-----------+------------+ ---------+ | LicensePlate | Make | Model | VehicleAge | +--------------------------+---------+-+---------+----------+-----------+------------+ ---------+ | AA11BB22 | Honda | Accord | 3 | | BB22CC33 | Hyundai | Veloster | 1 | | CC33DD44 | Chevy | Tahoe | 5 | | CC33DD44 | Chevy | Tahoe | 5 | | DD44EE55 | Audi | A4 | 0 | | EE55FF66 | Dodge | Charger | 2 | +--------------------------+---------+-+---------+----------+-----------+------------+ ---------+ 6 rows in set ( (0.06 0.06 sec) sec)
Employee Count by Rental Office /* Employee count by rental office */ RENTAL_OFFICE_LOCATION.R CE_LOCATION.RentalOfficeID entalOfficeID, , RENTAL_OFFICE RENTAL_OFFICE_LOCATION.Cit _LOCATION.City y SELECT RENTAL_OFFI EmployeeCount t AS Location, COUNT(EMPLOYEE.EmployeeID) AS EmployeeCoun RENTAL_OFFICE_LOCATION, _LOCATION, EMPLOYEE, EMPLOYING FROM RENTAL_OFFICE RENTAL_OFFICE_LOCATION.Re E_LOCATION.RentalOfficeID ntalOfficeID = EMPLOYING.Ren EMPLOYING.RentalOfficeID talOfficeID AND WHERE RENTAL_OFFIC EMPLOYING.EmployeeID = EMPLOYEE.Empl EMPLOYING.EmployeeID EMPLOYEE.EmployeeID oyeeID RENTAL_OFFICE_LOCATION.R E_LOCATION.RentalOfficeID entalOfficeID; ; GROUP BY RENTAL_OFFIC
Employee Count by Rental Office Terminal Output
mysql> SELECT RENTAL_OFFICE RENTAL_OFFICE_LOCATION.Ren _LOCATION.RentalOfficeID, talOfficeID, RENTAL_OFFICE_LOCATION.City AS Location, COUNT(EMPLOYEE.EmployeeID) AS EmployeeCount -> FROM RENTAL_OFFICE RENTAL_OFFICE_LOCATION, _LOCATION, EMPLOYEE, EMPLOYING -> WHERE RENTAL_OFFIC RENTAL_OFFICE_LOCATION.Re E_LOCATION.RentalOfficeID ntalOfficeID = EMPLOYING.RentalOfficeID AND -> EMPLOYING.EmployeeID EMPLOYING.Em ployeeID = EMPLOYEE.Emp EMPLOYEE.EmployeeID loyeeID -> GROUP BY RENTAL_OFFICE RENTAL_OFFICE_LOCATION.Ren _LOCATION.RentalOfficeID; talOfficeID; +----------------------------+-----------+------------+-----------+---------------+ ------+ | RentalOffic RentalOfficeID eID | Location | EmployeeCou EmployeeCount nt | +----------------------------+-----------+------------+-----------+---------------+ ------+ | 1 | Portland | 3 | | 2 | Gilead | 3 | | 3 | Estes Park | 3 | | 4 | Derry | 3 | +----------------------------+-----------+------------+-----------+---------------+ ------+ 4 rows in set ( (0.06 0.06 sec) sec)
Show Vehicles with Maintenance Records /*Show vehicles with maintenance record*/ MAINTENANCE_LOG.Maintena _LOG.Maintenance_ID, nce_ID, MAINTENANCE_ MAINTENANCE_LOG.Maintenan LOG.Maintenance_Date, ce_Date, SELECT MAINTENANCE MAINTENANCE_LOG.Maintenance_Procedure, MAINTENANCE_LOG.Maintenan ce_Procedure, VEHICLE.LicensePlate, VEHICLE.Lice nsePlate, VEHICLE.Model VEHICLE.Model, , EMPLOYEE.Em EMPLOYEE.EmployeeID, ployeeID, EMPLOYEE.EmployeeLName AS LastName FROM MAINTENANCE_LOG, VEHICLE, EMPLOYEE MAINTENANCE_LOG.EmployeeI LOG.EmployeeID D = EMPLOYEE.Em EMPLOYEE.EmployeeID ployeeID AND WHERE MAINTENANCE_ MAINTENANCE_LOG.VIN MAINTENANCE_L OG.VIN = VEHICLE.VIN;
Show Vehicles with Maintenance Records Terminal Output
mysql> SELECT MAINTENANCE_L MAINTENANCE_LOG.Maintenanc OG.Maintenance_ID, e_ID, MAINTENANCE_LOG.Maintenan MAINTENANCE_ LOG.Maintenance_Date, ce_Date, MAINTENANCE_ MAINTENANCE_LOG.Maintenan LOG.Maintenance_Procedure, ce_Procedure, -> VEHICLE.LicensePlate, VEHICLE.Licen sePlate, VEHICLE.Mode VEHICLE.Model, l, EMPLOYEE.Emp EMPLOYEE.EmployeeID, loyeeID, EMPLOYEE.EmployeeLName AS LastName -> FROM MAINTENANCE_LOG, VEHICLE, EMPLOYEE -> WHERE MAINTENANCE_ MAINTENANCE_LOG.EmployeeI LOG.EmployeeID D = EMPLOYEE.Empl EMPLOYEE.EmployeeID oyeeID AND -> MAINTENANCE_LOG.VIN MAINTENANCE_ LOG.VIN = VEHICLE.VIN; +----------------------------+-----------+------------------+-----------+-----------------------------------+-----------+---------------------+----------+------------+ +----------+ ------------+----------+ ----------+ | Maintenance_ID | Maintenance_ Maintenance_Date Date | Maintenance_P Maintenance_Procedure rocedure | LicensePlate | Model | EmployeeID | LastName | +----------------------------+-----------+------------------+-----------+-----------------------------------+-----------+---------------------+----------+------------+ +----------+ ------------+----------+ ----------+ | 1 | 2016-05-08 | Oil Change | AA11BB22 | Accord | 9 | Wayne | | 2 | 2016-07-27 | Tire Replacement | BB22CC33 | Veloster | 10 | Kent | | 3 | 2016-11-27 | Annual Maintenance | CC33DD44 | Tahoe | 11 | Luthor | | 4 | 2017-02-08 | Alignment Check | DD44EE55 | A4 | 12 | Nygma | +----------------------------+-----------+------------------+-----------+-----------------------------------+-----------+---------------------+----------+------------+ +----------+ ------------+----------+ ----------+ 4 rows in set ( (0.06 0.06 sec) sec)
Reservation Count by Office Location /*Reservation /*Reservatio n Count by Office Location*/ SELECT
RESERVATION.RentalOfficeID,RENTAL_OFFI RESERVATION.RentalOfficeI D,RENTAL_OFFICE_LOCATION.C CE_LOCATION.City,RENTAL_OF ity,RENTAL_OFFICE_LOCATI FICE_LOCATI ON.State,COUNT(RESERVATION.RESERVATIONID) AS RESERVATIONC RESERVATIONCOUNT OUNT RESERVATION,RENTAL_OFFICE RENTAL_OFFICE_LOCATION,RES _LOCATION,RESERVES ERVES FROM RESERVATION, RESERVATION.RentalOffice .RentalOfficeID ID = RENTAL_OFFICE RENTAL_OFFICE_LOCATION.Ren _LOCATION.RentalOfficeID talOfficeID WHERE RESERVATION RESERVATION.ReservationI .ReservationID D = RESERVES.Re RESERVES.ReservationID servationID AND RESERVATION RENTAL_OFFICE_LOCATION.Ren _LOCATION.RentalOfficeID; talOfficeID; GROUP BY RENTAL_OFFICE
Reservation Count by Office Location Terminal Output
mysql> SELECT RESERVATION.RentalOfficeI RESERVATION. RentalOfficeID,RENTAL_OFFI D,RENTAL_OFFICE_LOCATION.C CE_LOCATION.City,RENTAL_OF ity,RENTAL_OFFICE_LOCATI FICE_LOCATI ON.State,COUNT(RESERVATION.RESERVATIONID) AS RESERVATIONC RESERVATIONCOUNT OUNT -> FROM RESERVATION, RESERVATION,RENTAL_OFFICE RENTAL_OFFICE_LOCATION,RES _LOCATION,RESERVES ERVES -> WHERE RESERVATION.R RESERVATION.RentalOfficeID entalOfficeID = RENTAL_OFFICE_LOCATION.Re RENTAL_OFFIC E_LOCATION.RentalOfficeID ntalOfficeID -> RESERVATION.ReservationID eservationID = RESERVES.Rese RESERVES.ReservationID rvationID AND RESERVATION.R -> GROUP BY RENTAL_OFFIC RENTAL_OFFICE_LOCATION.Re E_LOCATION.RentalOfficeID; ntalOfficeID; +----------------------------+-----------+------------+-------+---+-------+------------------------------+ ----+ | RentalOffice RentalOfficeID ID | City | State | RESERVATIONCO RESERVATIONCOUNT UNT | +----------------------------+-----------+------------+-------+---+-------+------------------------------+ ----+ | 1 | Portland | OR | 1 | | 2 | Gilead | NY | 2 | | 3 | Estes Park | CO | 2 | | 4 | Derry | ME | 1 | +----------------------------+-----------+------------+-------+---+-------+------------------------------+ ----+ 4 rows in set ( (0.07 0.07 sec) sec)
Customer Accessory Selection /*Customer Accessory Selection*/ RESERVATION.ReservationI .ReservationID,CUSTOMER.Cu D,CUSTOMER.CustomerID,CUST stomerID,CUSTOMER.Customer OMER.CustomerFName FName Select RESERVATION FirstName,CUSTOMER.Custome TOMER.CustomerLName rLName AS AS FirstName,CUS LastName,RENTAL_OFFICE_LOCATION.City,R LastName,RENTAL_OFFICE_LO CATION.City,RENTAL_OFFICE_ ENTAL_OFFICE_LOCATION.Stat LOCATION.State,ACCESSORY e,ACCESSORY .Type,ACCESSORY.QUANTITY RENTAL_OFFICE_LOCATION FROM RESERVATION, INCLUDES, ACCESSORY, CUSTOMER, RENTAL_OFFICE_LOCATION RESERVATION.ReservationID ReservationID = INCLUDES.Res INCLUDES.ReservationID ervationID AND WHERE RESERVATION. INCLUDES.SerialNumber INCLUDES.Seri alNumber = ACCESSORY.Ser ACCESSORY.SerialNumber ialNumber AND CUSTOMER.CustomerID CUSTOMER.Cust omerID = RESERVATION.C RESERVATION.CustomerID ustomerID AND RESERVATION.RentalOfficeID RESERVATION.R entalOfficeID = RENTAL_OFFIC RENTAL_OFFICE_LOCATION.Re E_LOCATION.RentalOfficeID ntalOfficeID CUSTOMER.CustomerLName; tomerLName; ORDER BY CUSTOMER.Cus
Customer Accessory Selection Terminal Output
mysql> Select RESERVATION.ReservationID RESERVATION. ReservationID,CUSTOMER.Cus ,CUSTOMER.CustomerID,CUSTO tomerID,CUSTOMER.CustomerF MER.CustomerFName Name AS FirstName,CUSTOMER.CustomerLName AS LastName,RENTAL_OFFICE_LO LastName,REN TAL_OFFICE_LOCATION.City,R CATION.City,RENTAL_OFFICE_ ENTAL_OFFICE_LOCATION.Stat LOCATION.State,ACCESSORY e,ACCESSORY .Type,ACCESSORY.QUANTITY -> FROM RESERVATION, INCLUDES, ACCESSORY, CUSTOMER, RENTAL_OFFICE_LOCATION -> WHERE RESERVATION. RESERVATION.ReservationID ReservationID = INCLUDES.Res INCLUDES.ReservationID ervationID AND -> INCLUDES.Seri INCLUDES.SerialNumber alNumber = ACCESSORY.Se ACCESSORY.SerialNumber rialNumber AND -> CUSTOMER.Cust CUSTOMER.CustomerID omerID = RESERVATION. RESERVATION.CustomerID CustomerID AND -> RESERVATION.R RESERVATION.RentalOfficeI entalOfficeID D = RENTAL_OFFIC RENTAL_OFFICE_LOCATION.Re E_LOCATION.RentalOfficeID ntalOfficeID -> ORDER BY CUSTOMER.Cust CUSTOMER.CustomerLName; omerLName; +---------------------------+-----------+------------+-----------+-----------+-----------+-------------+-----------+------------+-------+------+----------------+----------+ | ReservationI ReservationID D | CustomerID | FirstName | LastName | City | State | Type | QUANTITY | +---------------------------+-----------+------------+-----------+-----------+-----------+-------------+-----------+------------+-------+------+----------------+----------+ | 3 | 3 | Barry | Allen | Derry | ME | Phone Charger | 1 | | 1 | 1 | John | Constantine | Estes Park | CO
| GPS | 1 | | 2 | 2 | Hal | Jordan | Portland | OR | GPS | 1 | | 4 | 4 | Dinah | Lance | Gilead | NY | Power Inverter | 1 | +---------------------------+-----------+------------+-----------+-----------+-----------+-------------+-----------+------------+-------+------+----------------+----------+ 4 rows in set ( (0.07 0.07 sec) sec)
Number of Rental Days /*Number of Rental Days*/ SELECT
RESERVATION.ReservationID,VEHICLE.VIN, RESERVATION.ReservationID ,VEHICLE.VIN,VEHICLE.MAKE, VEHICLE.MAKE,VEHICLE.MODEL VEHICLE.MODEL, , DATEDIFF (R ESERVATION.Pick_Up_Date,R ESERVATION.P ick_Up_Date,RESERVATION.Re ESERVATION.Return_Date) turn_Date) AS NumOfRentalD NumOfRentalDays ays RESERVATION,VEHICLE,RESERV EHICLE,RESERVES ES FROM RESERVATION,V RESERVATION.ReservationID ReservationID = RESERVES.Res RESERVES.ReservationID ervationID WHERE RESERVATION. VEHICLE.VIN; ; AND RESERVES.VIN = VEHICLE.VIN
Number of Rental Days Terminal Output
mysql> SELECT RESERVATION.ReservationID RESERVATION. ReservationID,VEHICLE.VIN, ,VEHICLE.VIN,VEHICLE.MAKE, VEHICLE.MAKE,VEHICLE.MODEL VEHICLE.MODEL, , DATEDIFF (R ESERVATION.Pick_Up_Date,R ESERVATION.P ick_Up_Date,RESERVATION.Re ESERVATION.Return_Date) turn_Date) AS NumOfRentalD NumOfRentalDays ays -> FROM RESERVATION,V RESERVATION,VEHICLE,RESERV EHICLE,RESERVES ES -> WHERE RESERVATION. RESERVATION.ReservationID ReservationID = RESERVES.Res RESERVES.ReservationID ervationID -> AND RESERVES.VIN = VEHICLE.VIN; +---------------------------+-----------+------------+---------+ --+---------+----------+-----------+-----------------------------+ --+ | ReservationI ReservationID D | VIN | MAKE | MODEL | NumOfRentalD NumOfRentalDays ays | +---------------------------+-----------+------------+---------+ --+---------+----------+-----------+-----------------------------+ --+ | 1 | 11111AAAAA | Honda | Accord | -7 | | 2 | 22222BBBBB | Hyundai | Veloster | -2 | | 3 | 33333CCCCC | Chevy | Tahoe | -14 | | 6 | 33333CCCCC | Chevy | Tahoe | -4 | | 4 | 44444DDDDD | Audi | A4 | -7 | | 5 | 55555EEEEE | Dodge | Charger | -5 | +---------------------------+-----------+------------+---------+ --+---------+----------+-----------+-----------------------------+ --+ 6 rows in set ( (0.06 0.06 sec) sec)