I. Table Creation: (8 Marks) 1. Create a new Database and save it as "FE_studentID" e.g. (FE_22j12345) (1 Mark) 2. Create the following tables using design view and find appropriate primary keys. (5 Marks) StaffName Ahmed Said Salary 500 900 Staffin STF101 STF102 STF103 STF104 STF105 STF106 Staff Job Nurse Doctor Doctor Nurse Doctor Doctor Join Date 3/5/2011 5/4/2012 5/3/2012 5/2/2011 4/6/2013 8/4/2010 Saif Duty Evening Morning Evening Morning Evening Noon 1050 Deptid Dept11 Dept12 Dept11 Dept12 Dept13 Dept12 Saba 450 Hamid 950 Usama 850 DeptCount 00 8 DeptID Dept11 Dept12 Dept13 Dept14 Dept15 6 Clinic DeptChair Humaid Rahima Fatima Ahmed Hamid DeptName Emergency Neurology Pediatric Psychic Cardiology DeptDate 3/7/2005 4/6/2010 5/2/2010 7/4/2015 8/4/2008 12 4 10 3. Create a relationship among both the tables and save it. (2 Marks) 1 Business Studies Departmen UTAS Total Marks:50 (14 Marks) II. QBE Queries: Solve the following queries using QBE. 1. List all staff details of nurses in morning duty. Save as ql. (2 Marks) 2. List department id, department name, join date, name and salary of doctors with salary less than 1000. Save as 22. (3 Marks) 3. List all clinic details where department count is not greater than 10. Save as 43. (2 Marks) 4. Display the total number and total salary of emergency department staff. Save as (3 Marks) 94 5. Remove duplicate values from the duty column of the staff table. Save as q4. (2 Marks) 6. List staff name, job, duty and join date of both morning and noon duties. Save as (2 Marks) 96. III. (20 Marks) SQL Queries: Solve the following queries using SQL 7. Write a query to create a table "Patient with the following fields with appropriate data types. Save as q7. (2 Marks) PID, PName StaffiD Business Studies Department UTAS Total Marks:50 8. Insert the following values in the "Patient" table, using SQL Insert query. Save as q8a, 8b. (2 Marks) PID PName PTI, PT2 Ahmed, Said STFIOI, STFIOS Staff 9. Write a query to add a column DeptName" to the Patient table. Save as 99. (2 Marks) 10. Write a query to increase the count of emergency department by 2. Save as q10. (2 Marks) 11. Write a query to display the average and total salary of neurology department staff. Save as q11. (3 Marks) 12. Write a query to display the minimum and maximum salary of nurses from the staff table. Save as q12. (2 Marks) 13. Write a query to increase the salary of staff in pediatric department by 50. Save as (3 Mark) 913. 3 UTAS Total Marks:50 14. Write a query to display name, job, join date and salary of staff with salaries between 800 and 1000. Save as q14. (2 marks) 15. Write a query to display all details of the clinic table with the department count arranged in ascending order. Save as q15. (2 Marks) IV. Forms & Reports (8 Marks) 1. Create a form using form wizard to display the department id, department name, department count from the clinic table and name, job and salary from the staff table. Arrange it as clinic view and save main and sub form as Department Form and staffsubform. (3 Marks) a. Create a query using QBE to display job, salary, department id, department name and department count of Neurology department. Save the query as report query. b. Create a clinic view report grouped by department name for the above query; also display the minimum and maximum salary