Instructions: Write SQL codes for each of the questions listed below. Use the ER
ID: 3836249 • Letter: I
Question
Instructions:
Write SQL codes for each of the questions listed below.
Use the ERD on the second page to identify the table names and attribute names (make any relevant assumptions about the data types).
Total Value = 10 points
Submit this assignment as a Word document.
Questions:
Show the names and duration of all tours that have the word “Ski” anywhere in the name. (1 point)
For each tour show the tour name and the cost, for all trips that cost between $500 and $1000. (1 point)
For each travel group, show the names of all customers in the group, where the group_name has “Penn State” in it. (1 point)
What is the cost of the most expensive tour? (1 point)
Prompt the user to enter the tour city (for example “Philadelphia”) and show the names and costs of all the tours listed for that city. (2 point)
Write the code to add two new rows of data to the Sales table. (1 point)
Show the names and startdate of all tours that were sold on April 1, 2014. (1 point)
Show the first name, last name and phone number of every customer that booked a tour to the city of Harrisburg. (2 point)
Explanation / Answer
1. Show the names and duration of all tours that have the word “Ski” anywhere in the name
Ans):- select tour_name, duration from tours where tour_name like '%Ski%';
2.For each tour show the tour name and the cost, for all trips that cost between $500 and $1000
Ans):- select tour_name,tour_cost from tours where tour_cost between 500 and 1000;
3.For each travel group, show the names of all customers in the group, where the group_name has “Penn State” in it
Ans:)- select group_name,customer_name from tours where group_name='Penn State';
4. What is the cost of the most expensive tour
Ans):- select max(tour_cost) from tours;