Part C
Use the Sydney Dance Academy (SDADB) database that you created in MySQL to design and execute SQL queries that answer the following questions.
Number your answers to each question clearly. The answer to each question must be tabulated as shown in the example below and include the SQL statement and also the output that is produced when you execute the statement in your database. The output includes the records that are listed and also the message that appears when you run the SQL statement.
For example:
Question 1:
|
SQL:
|
SELECT
lName, position
FROM Staff
WHERE salary > 20000;
|
|
Output:
|
+-------+----------+
| lName | position |
+-------+----------+
| Brand | Manager |
| White | Manager |
+-------+----------+
2 rows in set (0.03 sec)
|
Do not use screen captures to display the SQL statement or the output.
You should right-click on the MySQL Command Prompt window; choose Mark and then press the [Enter] key to Copy and then Paste into your Word document that includes the answers to all questions.
Format and indent the clauses in your SQL statements for better readability and understanding as shown in the example above. Statements must be syntactically and semantically correct.
1. List the first name, last name of students (join student’s first and last name with a space in between and use the alias Student Name for the column heading), and email address. Sort the output in ascending order by the Student Lastname.
2. List all the dance styles that has got a rate higher than $12 per class.
3. Retrieve Students’ last name, first name, mobile, and email for all students who do not have a mobile phone number recorded in the Student table. Sort the output in ascending order by the Student last name, and then first name.
4. List all Student last name for all students that have a suburb that has the word ‘hill’ anywhere in the suburb name. Sort the output ascending order by the last name of student.
5. List all the instructors who are currently timetable for 5 or more dance classes.
6. For each student who has been enrolled to dance classes multiple times display the total amount of money they paid.
7. Count the number of students enrolled in each type of Dance style.
8. Display the current timetable of the classes done in Room 1 and 3 at Chatswood location.
9. Display all the students’ information who are currently not enrolled in any of the dance classes.
10. List the payment method description and sum of payment amounts for all payments which were made after 1-July-2017.
3. Part C – Every group member needs to write this part individually and add to the documentation
1. Write a page to the department manager as a personal reflection that describes your experience building the database. You can discuss any challenges / difficulties that you experienced or solutions that you found. Comment on any limitations and / or strengths of your database design. Comment on whether your database meets all the system requirements as specified in Part A Question 4. Avoid making excuses or comments that reflect negativity. Include an acknowledgement of all students you have spoken to about the assignment.
Login | Sign Up
