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\
make this into a 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 distinct firstname, lastname, department.deptcode, employee.employeeid from employee, jobhistory, department where (employee.employeeid=jobhistory.employeeid and jobhistory.deptcode = department.deptcode);
CREATE VIEW worker_depts AS select distinct firstname, lastname, department.deptcode, employee.employeeid from employee, jobhistory, department where (employee.employeeid=jobhistory.employeeid and jobhistory.deptcode = department.deptcode);
select salaryammount * 2 from salaryhistory where endate is NULL
elect 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
the minimum salary for a job must be less than the maximum salary
select minimumsalary, maximumsalary from job where minimumsalary>maximumsalary //do not allow this
Create Trigger JobUpdate23 on job for insert, update AS IF EXISTS (select minimumsalary, maximumsalary from job where minimumsalary>maximumsalary) BEGIN RAISERROR('minimum salary must be less than maximum salary.',15,1) ROLLBACK TRAN END
GRANT SELECT ON cur_jobs TO [cs421-99]