English statement: show the current jobs for the employees.

Started with query:
 select employee.firstname, employee.lastname, job.jobtitle from employee, job, jobhistory where (employee.employeeid = jobhistory.employeeid) and (jobhistory.jobcode = job.jobcode) and (jobhistory.endjob is NULL)

Created view:
CREATE VIEW Cur_jobs AS select employee.firstname, employee.lastname, job.jobtitle from employee, job, jobhistory where employee.employeeid = jobhistory.employeeid and jobhistory.jobcode = job.jobcode and jobhistory.endjob is NULL

Select * from cur_jobs
Firstname	lastname	jobtitle
-----------------------------------------------------
NN23              	Scott             	Supervisor        
Alice             	Brown             	Professional-1    
Mary              	Smith             	Professional-1    
Christopher     	Georgopolis     Technologist      
Mike              	Jones             	Technologist      

For the “interesting view” I chose to create a view that answers the question “who has worked for each department”

Started with query:
 select distinct  firstname, lastname, department.departmentname, employee.employeeid from employee, jobhistory, department where (employee.employeeid=jobhistory.employeeid and jobhistory.deptcode = department.deptcode);

Created view 
CREATE VIEW worker_depts AS select distinct  firstname, lastname, department.departmentname, employee.employeeid from employee, jobhistory, department where (employee.employeeid=jobhistory.employeeid and jobhistory.deptcode = department.deptcode);

Select * from worker_depts

Firstname	lastname	department		employeeid
----------------------------------------------------------------------
Alice             	Brown             	Design          	 	22-02
Mary              	Smith             	Design           	 	24-01
NN23              	Scott             	Personnel         	24-22
Christopher       Georgopolis   Production        	23-44
Mike              	Jones             	Production        	24-08
Mary              	Black             	Sales            	 	22-05
Anne              	White             	Shipping          	23-18

Part B: views and computation

Create a view showing current employee info:

Started with select statement
select employee.employeeid, firstname, lastname, salaryammount, (salaryammount * .06) as FICA, (salaryammount * 1.06) as adjusted, getdate() as date, datediff(year, employee.dob, getdate()) as Age from salaryhistory, employee where salaryhistory.employeeid = employee.employeeid and enddate is NULL

created view

CREATE VIEW CurrentEmployeeInfo23 AS 
select employee.employeeid, firstname, lastname, salaryammount, (salaryammount * .06) as FICA, (salaryammount * 1.06) as adjusted, getdate() as date, datediff(year, employee.dob, getdate()) as Age from salaryhistory, employee where salaryhistory.employeeid = employee.employeeid and enddate is NULL

select * from CurrentEmployeeInfo23
employeeid firstname lastname	salary	FICA		adjusted		date			age
---------------------------------------------------------------------------------------------------------------------------------------------------------
24-22	NN23  Scott             	20500.00	1230.000000	21730.000000	2006-04-24 20:43:24.013	41
22-02	AliceBrown             	31000.00	1860.000000	32860.000000	2006-04-24 20:43:24.013	38
24-01	Mary Smith             	32000.00	1920.000000	33920.000000	2006-04-24 20:43:24.013	34
24-08	Mike  Jones             	25300.00	1518.000000	26818.000000	2006-04-24 20:43:24.013	44
23-44	ChristopherGeorgopolis 23937.50  1436.250000	25373.750000	2006-04-24 20:43:24.013	37