RECENT ASSIGNMENT

Create and query databases using SQL
Value: 15%
Submission method options
Alternative submission method
Task
Part A – SQL Queries (24 marks)
Use the hospital database (hosp2010.sql) from the Interact Resources site. Answer the following queries from that database.

You are required to submit:
1. The appropriate SQL statements for each query, which should be copied from your SQL code in MySQL and pasted into your submission file
2. Screenshots of the resultant tables which are to be pasted into your submission file immediately after the SQL code for that query.
Typing or manually drawing the results is NOT acceptable.

Question 1 (4 marks)

Find out what actual operations were performed by the surgeon 103. List the surgeon code, the code of the actual operation and the date the operation was performed. Show the operation date by the day of the week, the day of the month, the month and the year (ie: Tuesday, 7 October, 2014). Sort the list in most recent date of operation.

Question 2 (4 marks)

Find out which patients have not been discharged yet, listing their last name and the date they were admitted. Show the admission date by the day of the month, month and the full year (ie: 6/12/2001).

Question 3 (4 marks)

List all the persons who were admitted to the Flemming Ward prior to 1995. Show the persons first and last name, ward name and the date of their admission. Show the admission date by the day of the month, month and the full year (ie: 6/12/2001).

Question 4 (5 marks)

For the patients who have been discharged, show the number of days that they stayed at a ward. Also show the patients ID and last name, ward code and the date of admission. Show the admission date by the day of the week, the day of the month, the month and the year (ie: Tuesday, 7 October, 2014). Sort the list in most recent date of admission.

Question 5 (7 marks)
A. List the first name and surname of male patients from NSW or ACT (i.e. the postcode is in the 2000's), who have had an operation and recovered in either the Pasteur or the Fleming ward. Mention them only once if they recovered in the same ward more than once. (4 marks)
B. After executing your query, explain the logic of your SQL statement/s (for example, why/how you joined tables and for what reason, etc.), and do this for each part of this query. (3 marks)

Part B – DDL (16 marks)

Question 1 (2 marks)

Add a new attribute (field) ‘FullName’ to the STAFF table with appropriate data type and realistic number of characters.

Question 2 (4 marks)

Add the following staff names into the new field you have added above:

103 Dean Matthews
114 Ray Bones
115 Francess Martinez
116 Tammy Son

Question 3 (2 marks)

Delete the Nurse Francess Martinez from the staff table

Question 4 (8 marks)

Write the SQL DDL to create the database that contains each of the relations shown in the ERD below:

Make sure to add the necessary data type and size for each of the attributes used.

You will need to provide:
• Your DDL code for each table that you create; (6 marks)
• A screenshot showing each table that is created. (2 marks)