URGENT NEEDED IN 3 HOURS PLEASE.

aamirchauhan

Main

Home >Homework Answsers >Computer Science homework help

Submission Guidelines

· All work must be submitted on Moodle by the due date.

· The assignment must be in MS Word format, 1.5 spacing, 11-pt Calibri (Body) font and 2.5 cm margins on all four sides of your page with appropriate section headings.

· Reference sources must be cited in the text of the report, and listed appropriately at the end in a reference list using IEEE referencing style.

Part B: (70 marks)

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.

(10 Marks)

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)).

(3 marks for each screen shot & 5 marks for each query= 8 x 5 =40 Marks )

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” .

a. Describe types of anomalies that may occur on the following table that has redundant data.

(10 Marks)

b. Propose a suitable solution to overcome anomalies identified in 3(a).

(10 Marks)