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. Format both the SQL and also the Output in Courier New 10 or 11 point. Each question is 2 marks. 1. List the first name, last name of guests (join guest first and last name with a space in between and use the alias Guest Name for the column heading), and email address (for all guests that have an email address only). Sort the output in ascending order by the guest last name. 2. List the room number, rate, and number of beds for all rooms that have 2 beds. 3. List the total amount owing on service charges for each guest grouped by guest last name and the service description. Use the alias "Total Amount" for the sum of the service charges for the guest. Sort the output in ascending order by the guest last name. 4. List the guest last name, first name, mobile, and email for all guests that do not have a mobile phone number recorded in the guests table. Sort the output in ascending order by the guest last name, and then first name. 5. List the guest last name for all guests that have a suburb that has the word 'hill' anywhere in the suburb name. Sort the output ascending order by the guest name. SQL: SELECT lName, position FROM Staff WHERE salary > 20000; Output: +-------+----------+ | lName | position | +-------+----------+ | Brand | Manager | | White | Manager | +-------+----------+ 2 rows in set (0.03 sec) 4 | Page Assignment 2 (week 12) 2015 S2 6. Count the number of rooms grouped by room type. Use the alias "Count of room type" for the count. 7. List the room number and facility type for room numbers 18 or 19. Sort in ascending order of the room number, and also the facility type. 8. List the guest first and last name for all guests that do not have a room booking entered in the database. 9. List the payment method description and sum of payment amounts for all payments which were made after 1-July-2014. 3. Part C (2 marks) Write a page to the department manager 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. Submission: Deliverables for Parts A, B, & C must be witten in a report form a) A soft copy of your assignment documentation report must be zipped and uploaded to AIH Moodle. b) The SQL that can be used to restore your database should also be uploaded to AIH Moodle. You can create the SQL for your database as follows: Use the mysqldump command to create a text version of the database. Use mysqldump to create SQL file that contains a list of SQL statements which can be used to restore/recreate the original database. The syntax is as follows: $ mysqldump -u [uname] -p[pass] [dbname] > [backupfile.sql] 5 | Page Assignment 2 (week 12) 2015 S2 WHERE: You do not need to log on to MySQL. For example from the DOS command prompt, the syntax is: e:\xampp\mysql\bin>mysqldump -u root MyDB > e: \MyDB_bak.sql This will create the SQL file that can be used to restore your database.