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!
