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

Question

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.

Explanation / Answer

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

ANSWER:

CREATE FUNCTION [dbo].[fnGetUserName]

(

@firstname nvarchar(50),

@lastname nvarchar(50)

)

RETURNS NVARCHAR(100)

AS

BEGIN

DECLARE @userName nvarchar(100)

IF(@firstname IS NOt NULL AND @lastname is not null)

BEGIN

SELECT @userName = (@firstname + SUBSTRING(@lastname,1,1))

ENd

RETURN @userName

END

SELECT dbo.fnGetUserName('Harry','potter')

2.

ALTER PROCEDURE dbo.p_get_employee_and_department_by_phone

@phoneNumber nvarchar(100)

AS

SELECT FirstName,LastName,Department FROM EMPLOYEE WHERE Phone = @phoneNumber

GO

execute p_get_employee_and_department_by_phone '360-285-8210'

3.

CREATE PROCEDURE dbo.p_get_report_by_project_id

@projectID INT

AS

SELECT EMP.FirstName,ASG.HoursWorked,PRJ.ProjectName FROM EMPLOYEE EMP

JOIN ASSIGNMENT ASG ON ASG.EmployeeNumber = EMP.EmployeeNumber

JOIN PROJECT PRJ ON PRJ.ProjectID = ASG.ProjectID

WHERE PRJ.ProjectID = @projectID

GO

execute p_get_report_by_project_id 1000

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.