1. 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 and answer the questions given...

1.    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 and answer the questions given below.


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 the questions given below (1(a) - 1(e)).
(3 marks for each screen shot and 5 marks for each query=8 x 5 =40)


a.    List the name of the each trip started in “North Newry”.
 (8 Marks)


b.    List the name, distance and start location for each trip that has the type biking and that has the distance greater than 10 miles.
 (8 Marks)


c.    List trip name, number of persons and start location of all trips starting on 18th  June 2016.

Hint (Use Trip table and Reservation table)
 (8 Marks)


d.    How many trips originate in each State?

(8 Marks)


e.    List the trip name, type and maximum group size for all trips that have Susan Keily (KS01) as a guide.     Hint : use TripGuide and Trip tables
(8 Marks)


2.


a.    Explain how the GROUP BY clause works. What is the difference between the WHERE and HAVING clauses?
 (10 Marks)


b.    Determine the Functional Dependencies that exist in the Student table given below.
Student (no,  Sname,  courseno, course_name,  address, course_instructor, Instructor_office)