I am working on multi-table queries for my SQL class using a premade database, w
ID: 3736064 • Letter: I
Question
I am working on multi-table queries for my SQL class using a premade database, which I have included screenshots of. I am very unsure and need assistance writing the queries for the following questions.
1. List all current reservations with the trip ID, customer’s first name, last name and date of trip. List the reservations alphabetically by customer’s last name.
2. List the trip name of each of Glory Unser’s trips.
3. Create a list of all customers taking a trip on July 23, 2016. Include the customer’s first name, last name and the start location.
4. List the reservation ID, trip ID, and trip date for reservations for trips in ME. Run the query once using the IN operator and write the query and run it again using the EXISTS operator. Be sure and provide both screenshots.
5. List the first and last name of all guides that lead biking trips. Query results should have duplicate values.
6. Repeat the query from #5 without duplicates and sort the list by guide’s last name.
7. List the first name and last name of all customers that have reservations for a trip being led by Zach Gregory.
8. List the first name, last name, phone number of all customers that either live in NJ or have a current reservation.
9. List the first name, last name, phone number of all customers that either live in NJ and have a current reservation.
10. List the name of all trips and the trip date from the reservations. Trips that do not have reservations associated with them should also be in the list but trip date will be blank. (HINT: different kind of join to get records that do not have matches in the second table!)
Explanation / Answer
I haven't answer for all such queries that were related to Table named as GUIDE because i can not see GUIDE_ID is foreign Key of any other given table. That's why it is not possible to answer those queries untill GUIDE_ID become Foreign key of any other given table.
Answer 1:
select rs.TRIP_ID as TRIP_ID,cst.FIRST_NAME as FIRST_NAME,cst.LAST_NAME as LAST_NAME, rs.TRIP_DATE as DATE_OD_TRIP from RESERVATION rs
inner join CUSTOMER cst on cs.CUSTOMER_NUM = rs.CUSTOMER_NUM order by cst.LAST_NAME;
----------------------------------------------------------------------------------------------------------------------------------
Answer 2:
-------------------------------------------------------------------------------------------------------------------------------
Answer 3:
select cst.FIRST_NAME as FIRST_NAME,cst.LAST_NAME as LAST_NAME from CUSTOMER cst
inner join ( select CUSTOMER_NUM from RESERVATION where TRIP_DATE='7/23/2016') trp
on cst.CUSTOMER_NUM = trp.CUSTOMER_NUM;
-----------------------------------------------------------------------------------------------------------------------------------
Answer 4:
select rs.RESERVATION_ID as RESERVATION_ID,rs.TRIP_ID as TRIP_ID, rs.TRIP_DATE as TRIP_DATE from RESERVATION rs
inner join ( select TRIP_ID from TRIP where STATE in ('ME')) trp
on rs.TRIP_ID=trp.TRIP_ID;
---------------------------------------------------------------------------------------------------------------------------------------
Answer 5:
----------------------------------------------------------------------------------------------------------------------------------
Answer 6:
----------------------------------------------------------------------------------------------------------------------------------
Answer 7:
---------------------------------------------------------------------------------------------------------------------------------
Answer 8:
select cst.FIRST_NAME as FIRST_NAME, cst.LAST_NAME as LAST_NAME,cst.PHONE as PHONE_NUMBER from CUSTOMER cst
left join RESERVATION rs on cst.CUSTOMER_NUM=rs.CUSTOMER_NUM
where cst.STATE='NJ' or rs.RESERVATION_ID is not null;
---------------------------------------------------------------------------------------------------------------------------------------------
Answer 9:
select cst.FIRST_NAME as FIRST_NAME, cst.LAST_NAME as LAST_NAME,cst.PHONE as PHONE_NUMBER from CUSTOMER cst
left join RESERVATION rs on cst.CUSTOMER_NUM=rs.CUSTOMER_NUM
where cst.STATE='NJ' and rs.RESERVATION_ID is not null;
-------------------------------------------------------------------------------------------------------------------------------------------------
Answer 10:
---------------------------------------------------------------------------------------------------------------------------------------------
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.