Hello I need to design queries for Usage Scenario 1 A custom

Hello, I need to design queries for:

Usage Scenario 1:

A customer intends to book a flight.

The customer’s query is based on the following information:

Destination airport code (ex: ATL, LAX, etc.)

Departure Date

Arrival Date

Usage Scenario 2:

A customer intends to reserve a hotel room.

The customer’s query is based on his/her date of arrival and departure.

Usage Scenario 3:

A customer intends to reserve a rental car.

The customer’s query is based on his/her date of arrival and departure.

Usage Scenario 4:

A vendor needs to upload newly created items (flights, rooms, cars, etc.).

The system should allow vendor to update the appropriate data. Appropriate data includes:

Flight

o Destination city

o Departure Date

o Return Date

Car

o Check-out Date

o Return Date

Hotel

o Check-in Date

o Checkout Date

Following the addition of the flight/hotel/car to the database, the vendor should be given the database ID of the item (s)he just added.

(This is simply the value of the primary key for the flight/car/hotel table for the newly added row.)

Usage Scenario 5:

A vendor needs to see which customers (by name) are currently scheduled for a particular flight/car/hotel based on the FlightID/CarID/HotelID. The vendor should be shown a list of current customers who have made reservations for that particular car, flight, or hotel.

Usage Scenario 6:

A customer intends to see all the items that he or she has reserved.

Usage Scenario 7:

Based upon a customer’s name, a vendor needs to delete a customer’s itinerary since the customer has cancelled the trip.

Already have the first 4(Please let me know if they are right or not):

Scenario2:

Scenario3:

Scenario4:

Table: Cars Column Name Not Null Column Type CarlD Yes nteger DATETIME Rental StartDate Yes Rental EndDate DATETIME Yes Table: CarReservations Column Type Not Null Column Name Car ReservationID NTEGER Yes INTEGER CarlD Yes CustomerID INTEGER Yes Table: Customers Not Null Column Name Column Type Customer ID Yes INTEGER VARCHAR (45) First Name Yes VARCHAR(45) Last Name Yes VARCHAR (100) Ema Yes Mailing Address VARCHAR (100) Yes Billing Address VARCHAR(100) Yes VARCHAR (15) Phone Yes Table: Flights Not Null Column Name Column Type FlightlD INTEGER Yes VARCHAR(3) Destination Yes DepartureDate DATETIME Yes DATE TIME Return Date Yes Table FlightReservations Column Name Column Type Not Null FlightReservationID NTEGER Yes CustomerID INTEGER Yes FlightlD INTEGER Yes Auto Increment Yes No No Auto Increment Yes No No Auto Increment Yes Auto Increment Yes Auto Increment Yes Primary Key Yes No No Primary Key Yes No No Primary Key Yes Primary Key Yes Primary Key Yes

Solution

Yes. Most of your queries work. But there is a minor problem with your code.
When inserting / querying dates, to differentiate dates from strings, you have to use the
to_date() function.
So, now the queries are:
Scenario1:
//to see all the flights information
SELECT *
FROM flights
WHERE Destination=\'ATL\' and DepartureDate=to_date(\'04-20-2017\', \'MM-DD-YYYY\') AND ReturnDate=to_date(\'04-29-2017\', \'MM-DD-YYYY\');

//to book a flight
INSERT
INTO FlightReservations (CustomerID,FlightID)
VALUES(1,1);
Scenario2:
//to see all the Hotels information
SELECT *
FROM Hotels
WHERE DepartureDate=to_date(\'04-20-2017\', \'MM-DD-YYYY\') AND ArrivalDate=(\'04-29-2017\', \'MM-DD-YYYY\');

//to book a hotel
INSERT
INTO HotelReservations (CustomerID,HotelID)
VALUES(1,1);
Scenario3:
//to see all the cars information
SELECT *
FROM Cars
WHERE RentalStartDate=to_date(\'04-20-2017\', \'MM-DD-YYYY\') AND RentalEndDate=(\'04-29-2017\', \'MM-DD-YYYY\');

//to book a Car
INSERT
INTO CarReservations (CustomerID,CarID)
VALUES(1,1);
Scenario4:
//to upload newly created items
INSERT
INTO Flights (Destination, DepartureDate, ReturnDate)
VALUES(\'LAX\',to_date(\'09-01-2017\', \'MM-DD-YYYY\'), to_date(\'09-20-2017\', \'MM-DD-YYYY\'));

INSERT
INTO Cars (RentalStartDate, RentalEndDate)
VALUES(to_date(\'09-01-2017\', \'MM-DD-YYYY\'),to_date(\'09-20-2017\', \'MM-DD-YYYY\'));

INSERT
INTO Hotels (DepartureDate, ArrivalDate)
VALUES(to_date(\'09-01-2017\', \'MM-DD-YYYY\'),to_date(\'09-20-2017\', \'MM-DD-YYYY\'));

SELECT CustomerID FROM CarReservations WHERE CarID = 1; Will list all customers who booked Car with ID 1.
SELECT CustomerID FROM FlightReservations WHERE FlightID = 1; Will list all customers who booked Flight with ID 1.
SELECT CustomerID FROM HotelReservations WHERE HotelID = 1; Will list all customers who booked Hotel with ID 1.

SELECT CarID FROM CarReservations WHERE CustomerID = 1; Will list all Cars booked by Customer with ID 1.
SELECT FlightID FROM FlightReservations WHERE CustomerID = 1; Will list all Flights booked by Customer with ID 1.
SELECT HotelID FROM HotelReservations WHERE CustomerID = 1; Will list all Hotels booked by Customer with ID 1.

DELETE * FROM CarReservations WHERE CustomerID = (SELECT CustomerID FROM Customers WHERE FirstName = \'Donald\' AND LastName = \'Trump\') will delete all Cars booked by Customer named Donald Trump.
DELETE * FROM FlightReservations WHERE CustomerID = (SELECT CustomerID FROM Customers WHERE FirstName = \'Donald\' AND LastName = \'Trump\') will delete all Cars booked by Customer named Donald Trump.
DELETE * FROM HotelReservations WHERE CustomerID = (SELECT CustomerID FROM Customers WHERE FirstName = \'Donald\' AND LastName = \'Trump\') will delete all Cars booked by Customer named Donald Trump.