USING THE COLONIAL ADVENTURE TOURS DATA COMPLETE THE FOLLOWING QUERIES IN ORACLE

Question

USING THE COLONIAL ADVENTURE TOURS DATA COMPLETE THE FOLLOWING QUERIES IN ORACLE!

Please note you are to submit your Word file snipping your query and results. Please make sure that you label each of your steps!

.

List the trip id, trip name, start location and Distance, type name of guide for each trip that has the type Paddling and that has Lori Stevens as a guide.

List the reservation id, trip id, trip name, start location, trip date and price for reservation for a trip in New Hampshire. (use the IN Operator) order by trip id.

Find the guide last and first name of all guides and the trip name and trip state who can lead a Hiking trip.

List the number and name of each customer that either lives in the state of Maine and currently has a reservation.

Find the trip id, trip name for each trip whose maximum group size is greater than the maximum group size of at least one trip that has the type Biking.

CREATE a PADDLING table with the structure shown on page 189 in your book! (b) Insert into the paddling table the trip id, trip name, state, distance, maximum group size, and season from the trip table for only those trips having type PADDLING.

INSERT a new trip into your new table. The trip id is 43, trip name = lake champion Tour, state = Vermont (VT), the maximum group size is 12, the distance is 16 and the SEASON is Summer.

CHANGE the length of Season column in the Paddling table to 25 characters.

Change the difficulty_level column in the PADDLING table to reject nulls.

Delete the trip in the Paddling with the trip id 23. Delete the Paddling table from the database.

Explanation / Answer

Q1) List the trip id, trip name, start location and Distance, type name of guide for each trip that has the type Paddling and that has Lori Stevens as a guide.

SELECT TRIP_ID, TRIP_NAME, START_LOC, DISTANCE, TRIP_TYPE, GUIDE_NAME FROM TRIP JOIN GUIDE ON TRIP.TRIP_ID = GUIDE.TRIP_ID WHERE TRIP_TYPE = 'Paddling' AND GUIDE_NAME = 'Lori Stevens';

Q2) List the reservation id, trip id, trip name, start location, trip date and price for reservation for a trip in New Hampshire. (use the IN Operator) order by trip id.

SELECT RESERVATION_ID, TRIP_ID, TRIP_NAME, START_LOC, TRIP_DATE, PRICE FROM TRIP ,RESERVATION WHERE TRIP.TRIP_ID IN (SELECT TRIP_ID FROM RESERVATION) ORDER BY TRIP.TRIP_ID ASC;

Q3) Find the guide last and first name of all guides and the trip name and trip state who can lead a Hiking trip

SELECT GUIDE.LAST_NAME, GUIDE.FIRST_NAME, TRIP.TRIP_NAME, TRIP.STATE FROM TRIP JOIN GUIDE ON TRIP.TRIP_ID = GUIDE.TRIP_ID WHERE TRIP.TRIP_TYPE = 'Hiking';

Q4) List the number and name of each customer that either lives in the state of Maine and currently has a reservation

SELECT COUNT(CUST_ID) AS 'NO_OF_CUSTOMERS', NAME FROM CUSTOMER JOIN RESERVATION ON CUSTOMER.CUST_ID = RESERVATION.CUST_ID WHERE CUSTOMER.STATE = 'Maine' GROUP BY (NAME);

Q5) CHANGE the length of Season column in the Paddling table to 25 characters

ALTER TABLE PADDLING MODIFY SEASON VARCHAR2(25);

Q6) Change the difficulty_level column in the PADDLING table to reject nulls.

ALTER TABLE PADDLING MODIFY difficulty_level NOT NULL;

Q7) Delete the trip in the Paddling with the trip id 23. Delete the Paddling table from the database.

DELETE FROM PADDLING WHERE TRIP_ID = 23;

COMMIT;

DROP TABLE PADDLING;

Please let me know in case of any clarifications required. Thanks!

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

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