In my SQL course, using Oracle SQL Developer (3.2.20.09) I have been asked to wr

Question

In my SQL course, using Oracle SQL Developer (3.2.20.09) I have been asked to write multi-table queries for a pre-built database, of which I will attach screen shots. The tables in order are CUSTOMER, GUIDE, RESERVATION, TRIP, and TRIP_GUIDES. I am having trouble producing functioning queries, I would like some help writing the queries.

Complete the following SQL exercises:

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

Columns Data Constraints Grants IStatistics Triggers Flashback IDependencies Details Partitions lIndexes Isqu CUSTOMER NUMLAST NAME FIRST NAME STATEPOSTAL _CODEPHONE 03053 01101 06108 01854 14590 02919 03740 16412 14592 02143 08226 02188 05148 04912 05444 03101 06324 06074 14411 06066 orthfold 9 old Mi11 Rd 2332 South St. Apt 3 132 Main St. #1 164A South Bend Rd 45 Lower Ave 156 Scholar St 76 Cross Rd 32 Sheep Stop 5t 65 Granite St 373 Highland Ave 1282 Ocean Walk 7 Cherry Blo55om St. 18 Ark Ledge Ln 24 Stunp Rd 10 o1d Main St 132 South St. Apt 27 25 Stag Rd 12 Foster St 51 Fredrick St 154 Central St. Londonderry Springfield East Hartford Lowell Wolcott Johnston 603-555-7563 413-555-3212 860-555-0703 781-555-9423 585-555-5321 401-555-4848 603-555-0456 814-555-5521 585-555-0111 857-555-6258 09-555-5231 617-555-6665 802-555-3096 207-555-4507 802-555-3443 603-555-3476 860-555-9876 57-555-5532 585-555-0900 860-555-3234 4 104 Ryan 5 105 Morontoia Laura Somerville Ocean CITY Weymouth Vaccari Murakami 13 119 Gernowski Bretton-Borak iefferson 14 120 Athens 6 122 17 123 18 124 19 125 2D 126 Eairfield outh Windsor Peterson Brown Brianne Vernon

Explanation / Answer

1. This you will get from joining Reservation table & customer table -

SQL--

SELECT R.TRIP_ID , C.FIRST_NAME, C.LAST_NAME, R.TRIP_DATE FROM RESERVATION R JOIN CUSTOMER C WHERE R.CUSTOMER_NUM=C.CUSTOMER_NUM ORDER BY C.LAST_NAME;

2. this you will get by joining three tables GUIDE , TRIP and trip guides-

SQL-

SELECT T.TRIP_NAME FROM TABLE TRIP T JOIN GUIDE G JOIN TRIP_GUIDE O WHERE G.FIRST_NAME='Glory' AND G.LAST_NAME='Unser' AND O.GUIDE_NUM=G.GUIDE_NUM AND O.TRIP_ID=T.TRIP_ID;

3. This you will get from joining Reservation table & customer table -

SQL--

SELECT C.FISRT_NAME, C.LAST_NAME, C.CITY FROM CUSTOMER C JOIN RESERVATION R WHERE R.CUSTOMER_NUM= C.CUSTOMER_NUM AND R.TRIP_DATE='23-JUL-16';

5. this you will get by joining three tables GUIDE , TRIP and trip guides-

SQL--

SELECT G.FIRST_NAME, G.LAST_NAME FROM GUIDE G JOIN TRIP T JOIN TRIP_GUIDE WHERE T.TYPE='Biking' AND G.GUIDE_NUM=O.GUIDE_NUM AND T.TRIP_ID=O.TRIP_ID;

6. SQL--

SELECT DISTINCT G.FIRST_NAME, G.LAST_NAME FROM GUIDE G JOIN TRIP T JOIN TRIP_GUIDE WHERE T.TYPE='Biking' AND G.GUIDE_NUM=O.GUIDE_NUM AND T.TRIP_ID=O.TRIP_ID;

6.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.