Oakland University

Decision and Information Sciences

 

MIS 3000 – Management Information Systems Summer I 2022

Take-Home Exercise #3

 

Use the Ecotourism.accdb database to develop the following 7 queries. Make sure to view the

SQL version of your QBE query to become familiar with SQL for your upcoming exam.

 

 

Query1. List upcoming trips (i.e., StartDate > Date( ) ) in ascending order of start date. List the

fitness level description for each trip. Sort by destination for trips on the same start date. 86Rows

 

 

 

 

 

2

 

Query2. List number of trips by fitness level.

 

 

 

Query3. List top 4 most popular trips based on number of registrations. Make sure not to hard

code a value (such as >= 8) and use SELECT Top 4 to filter a sorted result.

 

 

 

Query4. List top 5 trips based on total revenue generated.

 

 

 

Query5. List trips with no registrations sorted by StartDate. (21 rows)

 

Hint #1: The following SQL subquery can be used in the QBE criteria:

 

(Select TripNo From Registration)

 

Which basically returns the list of TripNo’s that have/had at least one registration.

 

 

 

 

3

We are obviously interested in those Trips whose TripNo is not in that list. 21 Rows

 

 

 

 

 

 

4

Hint #2: A Left (Outer) Join (as opposed to Inner Join) can also be used to include all rows

from Trip table even if there is no matching row in the Registration table.

 

Filtering for those rows with a Null matching row will give the desired reuslt of trips with no

matching registration record.

 

 

 

SELECT Trip.TripNo, Destination, StartDate, EndDate, Price, FitnessLevelNo

 

FROM Trip LEFT JOIN Registration ON Trip.TripNo = Registration.TripNo

 

WHERE RegistrationNo Is Null

 

ORDER BY StartDate

 

 

 

 

 

 

 

5

Query6. List revenue generated by each customer in Michigan. Sort by last name, then first

name. 17 Rows

 

 

Recall that concatenation operator is &. So, [LastName] & “, “ & [FirstName] is used to

produce Full Name.

 

 

 

 

 

6

Query7. List number of trips by destination and year for those trips scheduled in 2020 or 2021.

 

 

Recall that Year([StartDate]) returns the year value of the date field Startdate.

 

 

 

That’s It!

Order your essay today and save 10% with the discount code ESSAYHELP