Question

Customer Customer Num LastName FirstName Address City State PostalCode Phone Reservation ReservationID TripID Trip Date Num Persons Trip Price Other Fees Customer Num Trip Guide TripID Guide Num LastName Trip Name Start Location FirstName State Address Distance City Max GrpSize State Type Postal Code Phone Num Season Hire Date TripGuides i-----. TripID Guide Num

14. Write a query that lists the trip id, trip name and state for all trips in New Hampshire. Run "Explain" against this query. Now create an index on the state. Re-run your query and the "Explain" Are there differences in the Explain results? Why or why not?

Answer 1

Here, we have 5 tables called Customer, Trip, Reservation, Guidem TripGuides.

We have a relation between the Customer and Reservation table based on the key field CustomerNum so we can pull the other columns like LastName,FirstName,Address, city,State,PostalCode and phone from the customer table.
using a join.

We have a relation between the Reservation table and Trip table based on Key field TripID on this field basis we can pull remaining fields like TripName,StartLocation,State, DistancemMaxGrapSize,Type and Season using a join.

So based on the question we can write a query to satisfy the condition as follows:-

SELECT A.TripID,B.TripName,C.State from Reservation A
INNER JOIN Trip B on A.TripID=B.TripID
INNER JOIN Customer C on A.CustomerNum=C.CustomerNum
where C.State='New Hampshire';

perform "Explain" command againest this query:-

EXPLAIN QUERY PLAN SELECT A.TripID,B.TripName,C.State from Reservation A
INNER JOIN Trip B on A.TripID=B.TripID
INNER JOIN Customer C on A.CustomerNum=C.CustomerNum
where C.State='New Hampshire';

Query for Creating an Index on the Customer table is as follows:-

CREATE INDEX State_Index
ON Customer (State);

Again perform "Explain" command against this query:-

EXPLAIN QUERY PLAN SELECT A.TripID, B.TripName,C.State from Reservation A
INNER JOIN Trip B on A.TripID=B.TripID
INNER JOIN Customer C on A.CustomerNum=C.CustomerNum
where C.State='New Hampshire';

The previous example, It uses index "State" to optimize a WHERE clause term of the form (State=?) - in this case, State=1". The previous example could not use a covering index, but the above example can, and that fact is reflected in the output so the difference in the outputs will be occured.