. Write SQL INSERT statements that add the data to the EMPLOYEE & ACTIVITY tables based on the information below. Add the SQL statements to the appropriate location within the script file.

. Write SQL INSERT statements that add the data to the EMPLOYEE & ACTIVITY tables based on the information below. Add the SQL statements to the appropriate location within the script file.

Page 1 of 8 EMPLOYEE EmpId EmpName ACTIVITY Has ActId Description ALLOCATION HourlyRate EmpGender Of CategoryCode Status YearsService Swinburne University of Technology Faculty of Business and Law INF 10002/INF60009 Database, Analysis and Design Assignment 2 The assignment is to be done individually 5% of your total marks for this subject. Due Date/Time: 8:30am Monday 2 May, 2016 Submission Requirements x Submit a text file that contains all of the statements required for all tasks of this assignment. x Your script must work with Oracle iSQL Junior x You must submit your assignment via ESP https://esp.ict.swin.edu.au/ by the due date. 1. Section 1: (30 marks) A database analyst has developed the following ER Diagram: 1.1. Download and edit the script file named ASS2_9999999_SQL.TXT Rename the file to ASS2_1234567_SQL.TXT assignment (where 1234567 must be replaced with your student id). 1.2. Write an SQL DROP statements that will drop all the tables. Add these statements to the appropriate location within the script file. 1.3. Write a SQL CREATE TABLE statement to create the EMPLOYEE & ACTIVITY tables. Add the statement to the appropriate location within the script file. Note: x The table must have a primary key. x The following columns data types and sizes must be used empid, actid, yearsservice Number(4) empname, status, description Varchar(30) empgender, categorycode Varchar(1) Page 2 of 8 1.4. Write SQL INSERT statements that add the data to the EMPLOYEE & ACTIVITY tables based on the information below. Add the SQL statements to the appropriate location within the script file. 1.5. Write a SQL CREATE TABLE statement to create the ALLOCATION table. Add the SQL statement to the appropriate location within the script file. Note: x The table must have a primary key constraint matching the requirements of the ERD x The table must have the appropriate foreign key constraint. x The foreign key column must have identical column name, data type and size of the primary key that it refers to x Add any NOT NULL constraints as dictated by the ERD x The following columns data types and sizes must be used x A check constraint named CK_ALLOCATION_RATE must be created to ensure that the HourlyRate value is in the range 0.00 to 299.99 1.6. Write SQL INSERT statements that add the data shown to the ALLOCATION table. Add the SQL statements to the appropriate location within the script file. Employee Details Activity Details Agreed Hourly Rate 1 Clyde 163 Sales and Marketing $45.50 2 Sally 155 Python Coding $30.00 5 Tara 165 Testing $30.00 3 Imogen 163 Sales and Marketing $65.00 5 Tara 155 Python Coding $27.00 5 Tara 151 Web Design $25.00 2 Sally 165 Testing $25.00 1 Clyde 151 Web Design $50.75 7 Kerri 163 Sales and Marketing $40.00 6 Mike 151 Web Design $33.00 1.7. Write a single SQL Select statement to that lists the Employee name, Activity description and hourly rate for each row in the ALLOCATION table. This statement will use data from three tables. You must use inner joins. Activity Id Description Category 151 Web Design A 155 Python Coding A 163 Sales and Marketing B 165 Testing C 171 Documentation C 174 Telephone Support C Employee Id Name Gender Status YearsService 1 Clyde M International 2 2 Sally F Local 9 3 Imogen F International 4 4 James M Local 3 5 Tara F International 6 6 Mike M Local 8 7 Kerri F Local 5 8 Emma F International 3 HourlyRate Number(5,2) Page 3 of 8 1.8. Primary Key / Foreign key constraint testing Write the SQL INSERT statements that attempts to add the data shown to the ALLOCATION table. If you have written your primary key and foreign key constraints correctly, the following data will be rejected by Oracle. Employee Id Activity Id Agreed Hourly Rate 1 163 100 5 155 99 These statements must fail. If they don’t fail, there is a problem with your Primary Key and or Foreign Key constraint clauses in your Create Table statement. 1.9. Check constraint testing Write the SQL INSERT statements that attempts to add the data shown to the ALLOCATION table. If you have written your check constraints correctly, the following data will be rejected by Oracle. Employee Id Activity Id Agreed Hourly Rate 1 155 500 6 171 399 These statements must fail. If they don’t fail, there is a problem with your check constraint clause in your Create Table statement. 1.10. Queries For each of the following tasks, place the SQL statement in ASS2.TXT 1.10.1. Write a single SQL statement that lists the average years of service of all rows in the Employee table. The Heading for the column must be “Avg years of service”. 1.10.2. Write a single SQL statement that lists the total number of rows in the Allocation table. The Heading for the column must be “Total Allocations”. 1.10.3. Write a single SQL statement that uses a Group By clause that counts the total Allocations for each Activity Category. e.g Activity Category Total Allocations A 7 B 2 C 5 ( these values do not necessarily match table data) 1.10.4. Write a single SQL statement that uses a Group By clause that counts the total Allocations for each Gender. e.g Gender Total Allocations M 4 F 11 ( these values do not necessarily match table data) Page 4 of 8 ACTION WeekNo HrsWorked Has EMPLOYEE EmpId EmpName ACTIVITY Has ActId Description ALLOCATION HourlyRate EmpGender Of CategoryCode Status YearsService 1.10.5. Write a single SQL statement that uses the Group By that counts the number of each gender within each status type for all Employees. The list must be in ascending Status Type / Gender sequence. e.g Status Type Gender Count International F 3 International M 4 Local F 1 Local M 2 ( these values do not necessarily match table data) 2. Section 2 (30 marks) The ER Diagram used earlier has now been modified. 2.1. Write the Drop Table statement for the Action table and add it to the other Drop Table statements that you created in section 1.2 above. Note: The tables containing the foreign keys must be dropped first. 2.2. Write Create Table SQL statements for the Action table. Add these statements to the appropriate location within the script file. x The table must have an appropriate primary key. x The table must have the appropriate foreign key constraints. x Each foreign key column must have identical column name datatype and size of the primary key that it refers to x The following columns datatypes and sizes must be used weekno number(2) hrsworked number(4,1) Page 5 of 8 2.3. Write SQL Insert statements to add the following data to the Action table based on the information supplied below (Note: The number of columns and the names of columns below does not match the Action table you have created. You need to decide what data is appropriate for the Action table). Add the SQL statements to the appropriate location within the script file. Employee Details Activity Details Week Number Hours Worked 1 Clyde 163 Sales and Marketing 39 10 1 Clyde 163 Sales and Marketing 40 8 1 Clyde 163 Sales and Marketing 42 6 1 Clyde 151 Web Design 41 5 1 Clyde 151 Web Design 42 5.5 2 Sally 155 Python Coding 39 10 2 Sally 165 Testing 39 15 2 Sally 155 Python Coding 42 10 2 Sally 165 Testing 40 20 2 Sally 155 Python Coding 41 10 5 Tara 155 Python Coding 39 8 5 Tara 155 Python Coding 40 6 5 Tara 155 Python Coding 41 5 5 Tara 151 Web Design 42 11.5 6 Mike 151 Web Design 39 1 6 Mike 151 Web Design 40 1 6 Mike 151 Web Design 41 1 2.4. Testing Primary Key & Foreign Key constraints. Write SQL INSERT statements that attempt to add the data shown to the ACTION table. Add the SQL statements to the appropriate location within the script file. If you have written your primary key and foreign key constraints correctly, the following data will be rejected by Oracle. Employee Id Activity Id Week Number Hours Worked Comment 1 171 43 5 Emp not allocated to activity 10 163 40 2 Emp does not exist 3 155 40 10 Emp not allocated to activity 5 188 39 10 Activity does not exist 1 163 39 2 Data already entered for this combination of Emp/Activity/Week 5 151 42 6 Data already entered for this combination of Emp/Activity/Week All of these statements must fail. If they don’t fail, there is a problem with your Primary Key and/or Foreign Key constraint clauses in your Create Table statement. For each of the following tasks, add an SQL statement to the appropriate location within the script file. 2.5. List Action data. List all rows in the Action table in ascending primary key sequence Show these columns only: Employee Id, Employee Name, Activity Id, Activity Name, WeekNo, HrsWorked This query will require you to join multiple tables with inner joins. Page 6 of 8 2.6. Queries For each of the following tasks, add an SQL statement to the appropriate location within the script file. 2.6.1. Based on rows in the Action table Write the query that shows: Employee Id, Employee Name, Week Number, Activity Number, Hours Worked, Total Pay Note: Total Pay is (HrsWorked * HourlyRate for the Activity) The list must be in Employee ID / Week Number / Activity Number ascending sequence 2.6.2. Based on rows in the Action table Write the query that shows: Employee Id, Employee Name, Week Number, Total Pay Note: Total Pay is (HrsWorked * HourlyRate for the Activity) The list must be in Employee ID / Week Number in ascending sequence 2.6.3. Based on rows in the Action table Write the query that shows: Employee Id, Employee Name, Total Pay Note: Total Pay is (HrsWorked * HourlyRate for the Activity) The list must be in Employee ID ascending sequence 2.6.4. Based on rows in the Action table Display the total pay for each week number: WeekNo, Total Pay Note: Total Pay is (HrsWorked * HourlyRate for the Activity) The list must be in Week No in ascending sequence 2.6.5. Based on rows in the Action table Display the total number of hours and total amount paid by each Activity. Activity Id, Activity Description, Total Hours, Total Pay Note: Total Pay is (HrsWorked * HourlyRate for the Activity) x This list must be displayed in ascending Activity Id sequence x Do not display Activities that have zero hours. 2.6.6. Display every Employee and the total number of hours worked by that Employee. Show these columns: Employee Id, Employee Name, Total Hours x This list must be displayed in ascending Employee Id sequence x You must include all Employees even if they have not worked any hours. Page 7 of 8 SUPERVISOR SupId SupName SupGender {Expertise} Managed By ACTION WeekNo HrsWorked Has EMPLOYEE EmpId EmpName ACTIVITY Has ActId Description ALLOCATION HourlyRate EmpGender Of CategoryCode Status YearsService 3. Section 3 (20 marks) The ER Diagram used earlier has now been modified. 3.1. Write the Drop Table statement for Supervisor table and add it to the other Drop Table statements that you created in section 1.2 above. Note: The tables containing the foreign keys must be dropped first. 3.2. Write Create Table SQL statements for the Supervisor table. Add the SQL statements to the appropriate location within the script file. x The table must have an appropriate primary key. x The table must have the appropriate foreign key constraints. x Each foreign key column must have identical column name datatype and size of the primary key that it refers to x The following columns datatypes and sizes must be used x Use a check constraint named CHK_SUPERVISOR_GENDER to ensure that the gender value must be either M or F. x Use a check constraint named CHK_SUPERVISOR_ID to ensure that the supid is a value in the range 70 to 150. supid number(3) supgender varchar(1) supname, expertise varchar(30) 3.3. Write SQL Insert statements for the additional tables. Add these statements to the appropriate location within the script file. Supervisor Id Supervisor Name Supervisor Gender Areas of Expertise Activities Supervised 71 Sue F Counselling Negotiating 163 72 Fred M Analysis 151 155 73 Mike M Motivation Analysis 163 171 74 Lilly F Negotiating Motivation 163 165 171 75 Tara F Training Counselling Motivation 151 155 171 76 Albert M Analysis Page 8 of 8 4. Section 4 (20 marks) 4.1. Testing Check constraints. Write SQL INSERT statements that attempt to add the data shown to the SUPERVISOR table. Add these statements to the appropriate location within the script file. If you have written your check constraints correctly, the following data must be rejected by Oracle. Supervisor Id Supervisor Name Supervisor Gender 50 Ben M 81 Kurt X 4.2. Queries For each of the following tasks, add a single SQL statement to the appropriate location within the script file. 4.2.1. List the total number of Employees allocated to each Supervisor x Show the Supervisor name and the total number of Employees value x This list must be in ascending Supervisor name sequence. 4.2.2. For each Activity, list every Supervisor expertise associated with that Activity x Show the Activity Name and the Expertise value x This list must be in ascending Activity Name / Expertise sequence. x Ensure that the result set does not contain any duplicate rows. 4.2.3. List only those Employees whose total hours worked is greater than the average hours worked. You must use a subquery in your solution. x Show the Employee id, Employee name and the total hours worked x This list must be in descending total hours worked sequence. 4.2.4. Write a single SQL statement to list every employee id, name and gender for those employees who have worked on the activity that has the highest Total Pay. You must use a subquery in your solution. (Note: Obviously the activity that has the highest pay could change as additional data is inserted. Your query must be able to deal with such changes without the need to alter the SQL code. This note isn’t here to scare you, it’s simply means don’t use code such as …where actid = 151… ). x This list must be in ascending Employee id sequence. 4.2.5. List all employee names & genders and all Supervisor names & genders in a single list. x Indicate which people are Employees and which people are Supervisors. x You must use a union in your solution. x The list must be in ascending name sequence

PLACE THIS ORDER OR A SIMILAR ORDER WITH BEST NURSING TUTORS TODAY AND GET AN AMAZING DISCOUNT

The post . Write SQL INSERT statements that add the data to the EMPLOYEE & ACTIVITY tables based on the information below. Add the SQL statements to the appropriate location within the script file. appeared first on BEST NURSING TUTORS .

Do you need a similar assignment done for you from scratch? We have qualified writers to help you. We assure you an A+ quality paper that is free from plagiarism. Order now for an Amazing Discount!
Use Discount Code "Newclient" for a 15% Discount!

NB: We do not resell papers. Upon ordering, we do an original paper exclusively for you.