For my SQL class, I have to write SQL queries for a premade database composed of
ID: 3725869 • Letter: F
Question
For my SQL class, I have to write SQL queries for a premade database composed of GUIDE, TRIP, CUSTOMER, and RESERVATION tables.
I have been unable to form working queries for a number of the instructions, I need help writing the queries, I understand how they work but I'm doing something wrong in format.
Complete the following SQL statements using the Colonial Adventures Tours tables:
a. List the trip name of each trip that has the season Summer.
b. List the customer number, first name, and last name of each customer that lives in NJ, NY, or PA. Use an OR operator to create this SQL command.
c. Repeat the same query for customers in NJ, NY, or PA using the IN operator.
d. Write the SQL statement that tells how many trips are in the state of New Hampshire or Massachusetts.
e. List the first and last names of all of the guides that were hired before 12/31/2013.
f. List the reservation IDs for reservations for at least 4 people.
g. List the name and distance of all trips that have MOUNT or MT in their name and have a distance greater than 5 miles.
h. Write the SQL statement that tells how many reservations Colonial currently has where the total amount due (trip price and other fees) is less than $60.
i. Display each season in which trips are offered. Each season should only be listed once.
j. Write a query to display the average distance and average maximum group size by state. For example, it would return that the average distance of a trip in CT is 11 and the average max group size is 6.5
Explanation / Answer
a) SELECT TRIP_NAME FROM TRIP WHERE SEASON='Summer';
b) SELECT CUSTOMER_NUM, FIRST_NAME, LAST_NAME FROM CUSTOMER WHERE STATE ='NJ' or STATE = 'NY' or STATE = 'PA';
c) SELECT CUSTOMER_NUM, FIRST_NAME, LAST_NAME FROM CUSTOMER WHERE STATE IN ('NJ', 'NY', 'PA');
d) SELECT COUNT(*) FROM TRIP WHERE STATE='NH' or STATE = 'MA';
e) SELECT FIRST_NAME, LAST_NAME FROM GUIDE WHERE HIRE_DATE < '2013-12-31';
f) SELECT RESERVATION_ID FROM RESERVATION WHERE NUM_PERSONS>=4;
g) SELECT TRIP_NAME, DISTANCE FROM TRIP WHERE TRIP_NAME LIKE '%MOUNT%' or TRIP_NAME LIKE '%MT%' and DISTANCE > 5;
h) SELECT COUNT(*) FROM RESERVATION R JOIN CUSTOMER C ON R.CUSTOMER_NUM==C.CUSTOMER_NUM WHERE C.LASTNAME='Colonial' && (R.TRIP_PRICE+R.OTHER_FEES)<60;
i) SELECT SEASON, COUNT(*) FROM TRIP GROUP BY SEASON;
j) SELECT AVG(DISTANCE) AS AVG_DISTANCE, AVG(MAX_GRP_SIZE) AS AVG_MAX_GRP_SIZE FROM TRIP GROUP BY STATE;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.