Write an sql drop statements that will drop the all tables

Question

Section 1:

A database analyst has developed the following ER Diagram:

Create a file named ASS2_9999999.sql (where 9999999 must be replaced with your student id).

Write an SQL DROP statements that will drop the all tables. Add these statements to the appropriate location within the script file.

Write a SQL CREATE TABLE statement to create the EMPLOYEE & ACTIVITY tables. Add the statement to the appropriate location within the script file. Note:

• The table must have a primary key.
• The following columns data types and sizes must be used:

empid, actid, yearsservice	Integer(4)
empname, status, description	Varchar(30)
empgender,  categorycode	Varchar(1)

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.

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

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

Write a SQL CREATE TABLE statement to create the ALLOCATION table. Add the SQL statement to the appropriate location within the script file. Note:
• The table must have a primary key constraint matching the requirements of the ERD
• The table must have the appropriate foreign key constraint.
• The foreign key column must have identical column name, data type and size of the primary key that it refers to
• Add any NOT NULL constraints as dictated by the ERD
• The following columns data types and sizes must be used
• 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

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

Write a single SQL Query 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.

Primary Key / Foreign key constraint testing If you have written your primary key and foreign key constraints correctly, the following data will be rejected.

Employee Id	Activity Id	Agreed Hourly Rate
1	163	100
5	155	99

If they don't fail, there is a problem with your Primary Key and or Foreign Key constraint clauses in your Create Table statement.

(Optional) Check constraint testing If you have written check constraints, the following data will be rejected.

Employee Id	Activity Id	Agreed Hourly Rate
1	155	500
6	171	399

Queries For each of the following tasks.

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".

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".

Write a single SQL statement that uses a Group By clause that counts the total Allocations for each Activity Category ( these values do not necessarily match table data)

Write a single SQL statement that uses a Group By clause that counts the total Allocations for each Gender ( these values do not necessarily match table data)

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. ( these values do not necessarily match table data)

Section 2:

The ER Diagram used earlier has now been modified.

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.

Write Create Table SQL statements for the Action table. Add these statements to the appropriate location within the script file.

• The table must have an appropriate primary key.
• The table must have the appropriate foreign key constraints.
• Each foreign key column must have identical column name datatype and size of the primary key that it refers to
• The following columns datatypes and sizes must be used

weekno	number(2)
hrsworked	number(4,1)

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.

Employee Id	Activity Id	Week Number	Hours Worked
1	171	43	5
10	163	40	2
3	155	40	10
5	188	39	10
1	163	39	2
5	151	42	6

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.

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.

Queries For each of the following tasks, add an SQL statement to the appropriate location within the script file.

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

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

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

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

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)
• This list must be displayed in ascending Activity Id sequence
• Do not display Activities that have zero hours.

Display every Employee and the total number of hours worked by that Employee. Show these columns: Employee Id, Employee Name, Total Hours

• This list must be displayed in ascending Employee Id sequence
• You must include all Employees even if they have not worked any hours.

Section 3

The ER Diagram used earlier has now been modified.

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.

Write Create Table SQL statements for the Supervisor table. Add the SQL statements to the appropriate location within the script file.

• The table must have an appropriate primary key.
• The table must have the appropriate foreign key constraints.
• Each foreign key column must have identical column name datatype and size of the primary key that it refers to
• The following columns datatypes and sizes must be used
• Use a check constraint named CHK_SUPERVISOR_GENDER to ensure that the gender value must be either M or F.
• Use a check constraint named CHK_SUPERVISOR_ID to ensure that the supid is a value in the range 70 to 150.

suprid	number(3)
supgender	varchar(1)
supname, expertise	varchar(30)

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		Counselling	163
		F	Negotiating
72	Fred	M	Analysis	151
				155
73	Mike		Motivation	163
		M	Analysis	171
74	Lilly		Negotiating	163
		F	Motivation	165
				171
75	Tara		Training	151
		F	Counselling	155
			Motivation	171
76	Albert	M	Analysis

Section 4

(optional) 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 implemented check constraints, the following data will be rejected.

Supervisor Id	Supervisor Name	Supervisor Gender
50	Ben	M
81	Kurt	X

Queries For each of the following tasks, add a single SQL statement to the appropriate location within the script file.

List the total number of Employees allocated to each Supervisor

• Show the Supervisor name and the total number of Employees value
• This list must be in ascending Supervisor name sequence.

For each Activity, list every Supervisor expertise associated with that Activity

• Show the Activity Name and the Expertise value
• This list must be in ascending Activity Name / Expertise sequence.
• Ensure that the result set does not contain any duplicate rows.

List only those Employees whose total hours worked is greater than the average hours worked. You must use a subquery in your solution.

• Show the Employee id, Employee name and the total hours worked
• This list must be in descending total hours worked sequence.

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... ).

• This list must be in ascending Employee id sequence.

List all employee names & genders and all Supervisor names & genders in a single list.

• Indicate which people are Employees and which people are Supervisors.
• You must use a union in your solution.
• The list must be in ascending name sequence