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 screenshots. The tables in order are CUSTOMER, GUIDE, RESERVATION, TRIP, and TRIP_GUIDES. I cant make any of my queries work, please help with the following portion!

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

SQL queries for the given scenarios is given below.

1) Table Reservation and Customer are joined on common attributes and the result is sorted by Last_name of customer using order by clause.

SELECT r.TRIP_ID,c.FIRST_NAME, c.LAST_NAME,r.TRIP_DATE
FROM RESERVATION AS r
INNER JOIN CUSTOMER AS c
c.CUSTOMER_NUM = r.CUSTOMER_NUM
ORDER BY c.LAST_NAME;

2) Three tables are join namely TRIP, TRIP_GUIDES, GUIDE. In where clause result is filtered out based on the given guide name.

SELECT t.TRIP_NAME
FROM TRIP AS t
INNER JOIN TRIP_GUIDES AS tg
ON tg.TRIP_ID = t.TRIP_ID
INNER JOIN GUIDE AS g
ON g.GUIDE_NUM = tg.GUIDE_NUM
WHERE g.LAST_NAME = 'Unser' AND g.FIRST_NAME = 'Glory';

3) Table CUSTOMER, RESERVATION and TRIP are joined. Date is filtered out in WHERE clause.

SELECT c.FIRST_NAME, c.LAST_NAME, t.START_LOCATION
FROM CUSTOMER AS c
INNER JOIN RESERVATION AS r
ON c.CUSTOMER_NUM = r.CUSTOMER_NUM
INNER JOIN TRIP AS t
ON r.TRIP_ID = t.TRIP_ID
WHERE r.TRIP_DATE = '23-JUL-16';

4) In where clause subquery is used to get the TRIP IDs of state 'ME' and are matched using the IN operator.
Using IN

SELECT RESERVATION_ID, TRIP_ID, TRIP_DATE
FROM RESERVATION
WHERE TRIP_ID IN (SELECT TRIP_ID FROM TRIP WHERE STATE = 'ME');

Using Exists

SELECT RESERVATION_ID, TRIP_ID, TRIP_DATE
FROM RESERVATION
WHERE EXISTS
(SELECT TRIP_ID FROM TRIP WHERE STATE = 'ME');

5) Guide name is fetched who guide for trip type Biking. Join between 3 tables are used.

SELECT g.FIRST_NAME, g.LAST_NAME
FROM GUIDE AS g
INNER JOIN TRIP_GUIDES AS tg
ON tg.GUIDE_NUM = g.GUIDE_NUM
INNER JOIN TRIP AS t
t.TRIP_ID = tg.TRIP_ID
WHERE t.TYPE = 'Biking';

6) DISTINCT keyword is used to get the non duplicate guide name who guide for trip type Biking. Order by is used to sort the result by last name of guide.

SELECT DISTINCT g.FIRST_NAME, g.LAST_NAME
FROM GUIDE AS g
INNER JOIN TRIP_GUIDES AS tg
ON tg.GUIDE_NUM = g.GUIDE_NUM
INNER JOIN TRIP AS t
t.TRIP_ID = tg.TRIP_ID
WHERE t.TYPE = 'Biking'
ODER BY g.LAST_NAME;

7) First name and last name of customers are fetched who are guided by Zach gregory.

SELECT c.FIRST_NAME, c.LAST_NAME
FROM CUSTOMER AS c
INNER JOIN RESERVATION AS r
ON c.CUSTOMER_NUM = r.CUSTOMER_NUM
INNER JOIN TRIP_GUIDE AS tg
ON tg.TRIP_ID = r.TRIP_ID
INNER JOIN GUIDE AS g
g.GUIDE_NUM = tg.GUIDE_NUM
WHERE g.LAST_NAME = 'Gregory' AND g.FIRST_NAME = 'Zach';

8) Customer name are fetched whose state is either 'NJ' or they have done some reservation.

SELECT FIRST_NAME, LAST_NAME, PHONE
FROM CUSTOMER
WHERE State = 'NJ' OR CUSTOMER_NUM IN (SELECT CUSTOMER_NUM FROM RESERVATION);

9)

SELECT FIRST_NAME, LAST_NAME, PHONE
FROM CUSTOMER
WHERE State = 'NJ' AND CUSTOMER_NUM IN (SELECT CUSTOMER_NUM FROM RESERVATION);

10) To get the all the recirds from the left table LEFT JOIN is used.

SELECT t.TRIP_NAME, r.TRIP_DATE
FROM TRIP AS t
LEFT JOIN RESERVATION AS r
ON t.TRIP_ID = r.TRIP_ID;

Note- After running these queries you will get the desired result and screenshot can be taken.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

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