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