Description
After the database is created and populated, write and execute SQL statements to provide
answers to the following database queries. For each query, you should understand what it asks
and determine which table(s) and field(s) should be queried to provide the answer.
1) List all staff members including all their detailed information. Order them by staff last name in ascending order.
2) List the personal data of all alumni. Order them by their current last name in ascending order.
3) Show the detailed information of all events. Order them by the date in descending order and then by location in ascending order.
4) List all event attendants including the event title, type, location, date, alumni student number, current name, address, and comments collected from the events. Order the result by event date in ascending order, by location and event title in ascending order, and by alumnus last name in ascending order.
5) List alumni who have not attended any event. Show student number, current name, address, and citizenship. Order the result by student last name in ascending order.
6) List all alumni in the database and the events they attended. Show student number, current name, address, citizenship, event title, type, location and date. Order the result by student last name in ascending order. There are some alumni who have not attend any event and those alumni must be included in the query result.
7) List all contacts made by staff including the staff employee number, name, title, contact date and time, media used, student numbers, current name, and comment recorded from the contact. Sort the result by contact date and time in descending order, by staff last name in ascending order, and by alumnus last name in ascending order.
8) List the all personal information of alumni whose current last name begins with a specific character of your choice. Order the result by current last name in ascending order.
9) List the all personal information of alumni whose current last name satisfies one of the patterns: (a) having the character ‘a’ at 3rd position, (b) ending with the string ‘er’, or (c) containing the string ‘re’. You may substitute the characters in the patterns based on your sample data to produce meaningful result. Order the result by last name inascending order.
10) List the staff and the number of contacts made by the staff. The staff information should include the staff employee number, name, title and location. Order the result by the number of contacts in descending order.
11) For each staff who has made more than three contacts to alumni, list the staff employee number, name, title, location, and number of contacts. Order the result by the number of contacts in descending order and then by last name in ascending order.
12) List the staff who has made the largest number of contacts. Show employee number, name, title, and the number of contacts.
13) List all alumni and the total number of events they attended. The alumni information should include student number, current name, address, and citizenship. Order the result by the number of attended events in descending order and then by the last name in ascending order.
14) For each alumnus who attended more than two events, list the student number, current name, the number of events attended. Order the result by the number of attended events in descending order and then by the last name in ascending order.
15) Find out which event(s) has (have) the largest number of attendances. The result should include the event number, title, location, date, and the number of attendances. Order the result by number of attendances in descending order, and by event title, location, and in
