write and execute SQL statements to provide answers to the following database queries….

$24.00

Category:

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 in

ascending 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