I need the SQL statements for these questions: 1. For each reservation, list the reservation ID, trip ID, customer number, and customer last name. Order the results by customer last name.
2. For each reservation for customer Ryan Goff, list the reservation ID, trip ID, and number of persons.
3. List the trip name of each trip that has Miles Abrams as a guide.
4. List the trip name of each trip that has the type Biking and that has Rita Boyers as a guide.
5. For each reservation that has a trip date of July 23, 2016, list the customer’s last name, the trip name, and the start location.
6. List the reservation ID, trip ID, and trip date for reservations for a trip in Maine (ME). Use the IN operator in your query.
7. Repeat Exercise 6, but this time use the EXISTS operator in your query.
8. Find the guide last name and guide first name of all guides who can lead a paddling trip. (Note: The query results should include duplicate values.)
Here are the tables:
CUSTOMER FIRST NAME POSTAL CODE CUSTOMER NUM LAST NAME ADDRESS Northfold Liam 9 Old Mill Rd Londonderry 10 NH 03053 103 Kasuma Sujata 132 Main St. #1 East Hartford CT 06108 MA 01854 Goff 164A South Bend Rd. Lowell Ryan 105 McLean Kyle 345 Lower Ave. Wolcott NY 14590 106 Morontoia Joseph 156 Scholar St Johnston 02919 107 Marchand Quinn 76 Cross Rd Bath NH 03740 108 32 Sheep stop St Edinboro PA Rulf Usch 16412 109 Caron Jean Luc 10 Greenfield St Rome ME 04963 110 Martha York Bers 65 Granite St NY 14592 112 Jones Laura 373 Highland Ave. Somerville MA 02143 115 Nu Adam 1282 Ocean Walk Ocean City Vaccari 08226 116 Murakami Iris 7 Cherry Blossom St. Weymouth MA 02188 119 Londonderry vT Chau 18 Ark Ledge Ln Clement 05148 120 Gernowski Sadie 24 stump Rd. Athens ME 04912 121 Cambridge VT Bretton-Borak Siam 10 Old Main St 122 Hefferson orlagh 132 South St. Apt 27 Manchester NH 03101 123 25 Stag Rd Fairfield Barnett Larry 06824 124 Busa Karen 12 Foster St. South Windsor CT 06074 125 51 Fredrick St Albion Peterson Becca NY 14411 126 Brown Brianne 154 Central St Vernon CT 06066 PHONE Click to Add 603-555-7563 413-555-3212 860-555-0703 781-555-8423 585-555-5321 401-555-4848 603-555-0456 814-555-5521 207-555-9643 585-555-0111 857-555-6258 609-555-5231 617-555-6665 802-555-3096 207-555-4507 802-555-3443 603-555-3476 860-555-9876 857-555-5532. 585-555-0900 860-555-3234

Explanation:

/* From the information provided, For now will consider the name of table as TRIPGUIDES*/

/*In all the answers below, the syntax is based on Oracle SQL. In case of usage of other database queries, answer may vary to some extent*/

1.

Select R.Reservation_ID, R.Trip_ID , C.Customer_Num,C.Last_Name from Reservation R, Customer C where C.Customer_Num=R.Customer_Num ORDER BY C.Last_Name

/*idea is to select the join the two tables by comparing customer_id field in two tables as it is the only field which is common and then print the desired result later ordering by last name to get the results in sorted order*/

2.

Select R.Reservation_ID, R.Trip_ID , R.NUM_PERSONS from Reservation R, Customer C where C.Customer_Num=R.Customer_Num and C.LAST_NAME='Goff' and C.FIRST_NAME='Ryan'

/*Here, the explaination will be similar to the first query. Choose the desired columns from the tables, and join the two tables by equating the common field

*/

3.

Select T.TRIP_NAME from TRIP T,GUIDE G,TRIPGUIDES TG where T.TRIP_ID=TG.TRIP_ID and TG.GUIDE_NUM=G.GUIDE_NUM and G.LAST_NAME='Abrams' and G.FIRST_NAME='Miles'

/*

Here,we choose three tables TRIP,GUIDE and TRIPGUIDES. Here we selected those trips where we have guides as Miles Abrms in the GUIDES table and equated Trip_id from TRIPGUIDES to TRIP.TRIP_Name so that can have the desired results

*/

4.

Select T.TRIP_NAME

from TRIP T,TRIPGUIDES TG ,G.GUIDE

where T.TRIP_ID=TG.TRIP_ID and T.TYPE='Biking' and TG.GUIDE_NUM=G.GUIDE_NUM and G.LAST_NAME='Boyers' and G.FIRST_NAME='Rita'

/*

In the above question, we first selected the trip name from trip table. To put the condition we first make sure that all the three tables are connected properly. In order to do so, we have equated Guide_nums in guide and tripguides. and also equated trip_id in tripguides and trip. Then we equated names from guide tables and type from trip table for the desired results.

*/

5.

SELECT C.LAST_NAME , T.TRIP_NAME , T.START_LOCATION FROM CUSTOMER C, TRIP T, RESERVATION R WHERE R.TRIP_DATE='2016-07-23' AND T.TRIP_ID=R.TRIP_ID AND C.CUSTOMER_NUM=R.CUSTOMER_NUM

/*

The explaination for this one will be equivalent to the previous question where we just equated the desired columns where we equiated the desired columns in respective fields and also equated the common entities like trip ids and customer ids so that can join tables properly

*/

/*The comparison of dates in SQL depends on the format in which they are stored. In the upper case if the

dates are stored in the format as -MM-DD, then the above query mentioned will work. In case dates are stored in the form of a string then the following query will work.

SELECT C.LAST_NAME , T.TRIP_NAME , T.START_LOCATION FROM CUSTOMER C, TRIP T, RESERVATION R WHERE R.TRIP_DATE='7/23/2016' AND T.TRIP_ID=R.TRIP_ID AND C.CUSTOMER_NUM=R.CUSTOMER_NUM

*/

6.

Select R.RESERVATION_ID, R.TRIP_ID,R.TRIP_DATE FROM RESERVATION R WHERE R.TRIP_ID IN

{SELECT TRIP_ID FROM TRIP T WHERE STATE='ME'}

/*

In the above question, we firstly extracted all the trip id's which are having locations as maine. Now we have the list of all the trip_id's that have location maine. Now we just need to extract the reservation ids for the same which can be trivally done by simply using the in clause stating print all the tuples whose id's are there in the list of inner query. Remember, IN always checks in the set of values.

*/

7.

Select R.RESERVATION_ID, R.TRIP_ID,R.TRIP_DATE FROM RESERVATION WHERE

EXISTS {SELECT TRIP_ID FROM TRIP T WHERE STATE='ME' and R.TRIP_ID=T.TRIP_ID}

/*

Unlike IN, Exist returns either true or false based on existance of any tuple in the condition provided. In the question above, firstly we checked for the possibilities if there is a trip in state ME and TRIP_IDs are common. Then we selected reservation ID, trip ID and Trip dates for all queries that returns true for inner query

*/

8.

SELECT G.LAST_NAME,G.FIRST_NAME FROM GUIDE WHERE G.GUIDE_NUM IN

{

SELECT DISTINCT TG.GUIDE_NUM FROM TRIPGUIDES TG WHERE TG.TRIPID IN {

SELECT T.TRIP_ID FROM TRIP T WHERE T.TYPE='Paddling'

}

}

/*

We have used here double nested IN queries. Firstly we selected all the trips which had paddling type (from the inner most queries). Using the same, we get the list of guides,(basically got the list of guide_numbers) of all the guides eds which were on trips with trip id we got from the inner most queries. Now that we have all the guide_Nums that were on trip with type paddling, we can simply use the query select last name and first name of all the guides which are having guide nums in the list returned by middle query.

*/

deudtgc hdb d gd gsyjs bgwgsvsvd bsbeve,r3hfnfb. dv c cncnd.