Question

I am working on multi-table queries for my SQL class using a premade database, which I have included screenshots of. I received assistance and was able to complete a good number of the queries but the bolded ones seem absolutely impossible to do??

How do I write a query for the bolded questions?? I've scoured my textbook and notes and cannot get anything I try to successfully produce results.

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!)

CUSTOMER CUSTOMER_NUM LAST_NAME FIRST_NAME ADDRESS CITY STATE POSTAL_CODE PHONE Northfold 03053 01101 06108 01854 14590 9 Old Mill Rd 2332 South St. Apt 3 Springfield 132 Main St. #1 164A South Bend Rd. Lowell 345 Lower Ave 156 Scholar St 76 Cross Rd 32 Sheep Stop St 10 Greenfield St 65 Granite St 373 Highland Ave 1282 Ocean Walk 7 Cherry Blossom St. Weymouth 18 Ark Ledge Ln 24 Stump Rd 10 Old Main St 132 South St. Apt 27 Manchester LondonderryNH Liam Arnold Sujata 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 Kasuma Goff McLean Morontoia Marchand East HartfordCT MA NY RI Kvl Wolcot Johnston Bath ose 03740 16412 04963 14592 02143 08226 02188 05148 04912 05444 Quinn SC Jean Luc Martha L1 Caron rs ones Vaccari Rom NY 112 Somerville Adam Ocean City Murakami Londonderry VT Athens Cambridge Clement Gernowski Bretton-BorakSiam Hefferson Barnett Busa Peterson Brown 121 rlau rrv Karen Becca 06824 06074 14411 06066 CT Fairfield South Windsor CT Albion Vernon 124 12 Foster St Brianne 154 Central St

Answer 1

As any of the tables doesn't have GUIDE_NUM as foreign key, it is not possible to retrive the information that is asked in bold question.The table TRIP should contain GUIDE_NUM as one of its fields.