sql

=====================================table sql=====================================

use APC Create TABLE EMPLOYEE ( EmployeeNumber int NOT NULL IDENTITY (1,1), FirstName Char(25) NOT NULL, LastName Char(25) NOT NULL, Department Char(35) NOT NULL DEFAULT 'Human Resources', Phone Char(12) NULL, Email VarChar(100) NOT NULL UNIQUE, CONSTRAINT EMPLOYEE_PK PRIMARY KEY(EmployeeNumber) );

use APC CREATE TABLE DEPARTMENT ( DepartmentName Char(35) NOT NULL, BudgetCode Char(30) NOT NULL, OfficeNumber Char(15) NOT NULL, Phone Char(15) NOT NULL, CONSTRAINT DEPARTMENT_PK PRIMARY KEY(DepartmentName) ); Use APC Create TABLE PROJECT ( ProjectID Int NOT NULL IDENTITY (1000,100), ProjectName Char(50) NOT NULL, Department Char(35) NOT NULL, MaxHours Numeric(8,2) NOT NULL DEFAULT 100, StartDate DateTime NULL, EndDate DateTime NULL, CONSTRAINT ASSIGNMENT_PK PRIMARY KEY(ProjectID) );

Use APC Create TABLE ASSIGNMENT ( ProjectID Int NOT NULL, EmployeeNumber Int NOT NULL, HoursWorked Numeric(6,2) NULL, );

=====================================================================================

===============================data sql================================================

use APC

INSERT INTO EMPLOYEE (FirstName, LastName, Department, Phone, Email) VALUES('Mary', 'Jacobs', 'Administration', '360-285-8110', 'Mary.Jacobs@APC.com'); INSERT INTO EMPLOYEE (FirstName, LastName, Department, Phone, Email) VALUES('Rosalie', 'Jackson', 'Administration','360-285-8120','Rosalie.Jackson@APC.com'); INSERT INTO EMPLOYEE (FirstName, LastName, Department, Phone, Email) VALUES('Richard', 'Bandalone', 'Legal','360-285-8210','Richard.Bandalone@APC.com');

INSERT INTO EMPLOYEE (FirstName, LastName, Department, Phone, Email) VALUES('Tom', 'Caruthers', 'Accounting','360-285-8310','Tom.Caruthers@APC.com');

INSERT INTO EMPLOYEE (FirstName, LastName, Department, Phone, Email) VALUES('Heather', 'Jones', 'Accounting','360-285-8320','Heather.Jones@APC.com');

INSERT INTO EMPLOYEE (FirstName, LastName, Department, Phone, Email) VALUES('Mary', 'Abernathy', 'Finance','360-285-8410','Mary.Abernathy@APC.com');

INSERT INTO EMPLOYEE (FirstName, LastName, Department, Phone, Email) VALUES('George', 'Smith', 'Human Resources','360-285-8510','Geroge.Smith@APC.com');

INSERT INTO EMPLOYEE (FirstName, LastName, Department, Phone, Email) VALUES('Tom', 'Jackson', 'Production','360-287-8610','Tom.Jackson@APC.com');

INSERT INTO EMPLOYEE (FirstName, LastName, Department, Phone, Email) VALUES('George', 'Jones', 'Production','360-287-8620','George.Jones@APC.com');

INSERT INTO EMPLOYEE (FirstName, LastName, Department, Phone, Email) VALUES('Ken', 'Numoto', 'Marketing','360-287-8710','Ken.Numoto@APC.com'); Use APC

INSERT INTO DEPARTMENT VALUES( 'Administration', 'BC-100-10','BLDG01-300','360-285-8100');

INSERT INTO DEPARTMENT VALUES( 'Legal', 'BC-200-10','BLDG01-200','360-285-8200');

INSERT INTO DEPARTMENT VALUES( 'Accounting', 'BC-300-10','BLDG01-100','360-285-8300');

INSERT INTO DEPARTMENT VALUES( 'Finance', 'BC-400-10','BLDG01-140','360-285-8400');

INSERT INTO DEPARTMENT VALUES( 'Human Resources', 'BC-500-10','BLDG01-180','360-285-8500');

INSERT INTO DEPARTMENT VALUES( 'Production', 'BC-600-10','BLDG02-100','360-287-8600');

INSERT INTO DEPARTMENT VALUES( 'Marketing', 'BC-700-10','BLDG02-200','360-287-8700');

INSERT INTO DEPARTMENT VALUES( 'InfoSystems', 'BC-800-10','BLDG02-270','360-287-8800');

Use assignment3

INSERT INTO PROJECT VALUES ('2014 Q3 Product Plan', 'Marketing', 135.00, '10-MAY-14', '15-JUN-14');

INSERT INTO PROJECT VALUES ('2014 Q3 Portfolio Analysis', 'Finance', 120.00, '05-JUL-14', '25-JUL-14');

INSERT INTO PROJECT VALUES ('2014 Q3 Tax Preparation', 'Accounting', 145.00, '10-AUG-14', '15-AUG-14');

INSERT INTO PROJECT VALUES ('2014 Q4 Product Plan', 'Marketing', 150.00, '10-AUG-14', '15-SEP-14');

INSERT INTO PROJECT (ProjectName, Department, MaxHours, StartDate) VALUES ('2014 Q4 Portfolio Analysis', 'Finance', 140.00, '05-OCT-14'); Use APC

INSERT INTO ASSIGNMENT VALUES (1000, 1, 30.0); INSERT INTO ASSIGNMENT VALUES (1000, 8, 75.0); INSERT INTO ASSIGNMENT VALUES (1000, 10, 55.0); INSERT INTO ASSIGNMENT VALUES (1100, 4, 40.0); INSERT INTO ASSIGNMENT VALUES (1100, 6, 25.0); INSERT INTO ASSIGNMENT VALUES (1200, 1, 25.0); INSERT INTO ASSIGNMENT VALUES (1200, 2, 20.0); INSERT INTO ASSIGNMENT VALUES (1200, 4, 45.0); INSERT INTO ASSIGNMENT VALUES (1200, 5, 40.0); INSERT INTO ASSIGNMENT VALUES (1300, 1, 35.0); INSERT INTO ASSIGNMENT VALUES (1300, 8, 80.0); INSERT INTO ASSIGNMENT VALUES (1300, 10, 50.0); INSERT INTO ASSIGNMENT VALUES (1400, 4, 15.0); INSERT INTO ASSIGNMENT VALUES (1400, 5, 10.0); INSERT INTO ASSIGNMENT VALUES (1400, 6, 27.5);

=============================================================================

omit the following question from Part B as there is no data in the PROJECT Table:

Write a stored procedure that takes a project id and reports on which employees worked how many hours on that specific project

Insert the code for your Second Stored Procedure here.

For Part C you may use an audit table similar to the following:

CREATE TABLE EmployeeAudit (

Audit_ID INT Identity(1,1),

Emp_ID INT,

modified_by VARCHAR(64),

Modified_date DATETIME,

Modified_action VARCHAR(64)

)

Q.

part a

1. Write a function that given an employees first and last name, a username is generated for that employee. The username should be in the format of the first name + last initital ,

for example Harry Porter would be 'harryp'.

part b

1.Write a stored procedure that returns employees and departments based on the phone number.

2. Write a stored procedure that takes a project id and reports on which employees worked how many hours on that specific project

part c

Write a trigger that logs updates (UPDATE, INSERT, DELETE) to the employee table. The trigger should record all the previous values in addition to the date and time logs as well as the user that modified the record.