Assignment
1. List and describe briefly 4 Built-in functions (aggregate functions) in SQL with examples.
Note: You are required to show how to use these functions inside a SQL query by using an example.
2. You are required to download the database file called "Colonial Adventure Tours" (which is available in Moodle Learning management system), on to your hard drive.
Write SQL queries (do not use QBE) for the following questions and execute the queries using the above database on MS Access. Include screen shots of the outputs and all SQL statements you used to answer following questions (2(a) - 2(e)).
a. List the name, distance and start location for each trip that has the type Hiking and that has the distance greater than 8 miles.
b. List the name of each trip and distance that has the type Paddling or that is located in Poland.
c. Find how many reservations include a trip with a Total price (TripPrice+otherfees ) that is greater than $20 but less than $75.
d. Count the number of trips originate in each state.
e. Create a new table maned "Hiking" that includes TripID, NumPersons from Reservation table and Trip Type, Trip Name columns from Trip table where Trip type is "Hiking" .
3.
a. Describe types of anomalies that may occur on the following table that has redundant data.
b. Propose a suitable solution to overcome anomalies identified in 3(a).