1- List the number of jobs each employee has had within the company. Identify ea

Question

1- List the number of jobs each employee has had within the company. Identify each employee by surname.

2- List the maximum and minimum salary of current employees and their department name, grouped by department.
3- List all the employees by surname and forenames who are in the same department as Matthew Brownlie
4- List the employee numbers and salary of all current employees who earn more than the employee number 16. In each row show both the employee's salary and the salary of employee 16.
5- List the full names and positions of current employees who have been on any of the courses that Robert
6-List the full names of any employee who started a new job on the same day as Allan Robinson.
7- List all employees by their full names with their positions who are in the same department as Brian Murphy and are older than him.

9- Convert the answer to question 15 to COUNT the occurences. The tables Jobhistory Course
Empcourse
Employee
elect * from department DEPNO DNAME LOCATION HEAD 1accounts flor 3 floor 3 administration floor 2 software design floor 1 2 communications floor 43 5 engineering 7 engineering floor 5

Explanation / Answer

1.
select j.EmpNo, count(j.EmpNo) as "Number of Jobs", e.Surname
from Jobhistory j, Employee e
where e.EmpNo=j.EmpNo
group by j.EmpNo, e.Surname

2.
select d.DName, Max(j.Salary) as "Max Salary", MIN(j.Salary) as "Min Salary"
from jobhistory j, Employee e, Department d
where e.EmpNo=j.EmpNo
and d.DeptNo = e.DeptNo
and j.enddate is null or j.enddate = ''
group by d.DName,j.Salary

3.
select e.surname, e.forenames
from Employee e
where e.DeptNo = (select deptno from employee where e.surname = 'Brownlie' and e.forenames = 'Matthew')

4.
select * from
(
select j.EmpNo, j.Salary as Salary
from jobhistory j
where j.enddate is null or j.enddate = ''
)
where Salary >= (select salary from jobhistor where empno = 16 and enddate is null or enddate = '')

5.
select e.surname,e.Forenames,j.positon
from jobhistory j, employee e, empcourse c
where e.EmpNo=j.EmpNo
and j.EmpNo=c.EmpNo
and c.courseno = (select a.courseno from empcourse a, employee b where b.empno = a.empno and b.forenames = 'Robert')
and j.j.enddate is null or j.enddate = ''

6.
select e.surname,e.Forenames
from jobhistory j, employee e
where e.EmpNo=j.EmpNo
and j.StartDate = (select Min(a.StartDate) from jobhistory a, employee b where b.empno = a.empno and b.surname = 'Robinson' and b.forenames = 'Allan')

7.

Select surname,forenames,position from
(
select e.surname,e.Forenames,j.position,j.salary,e.DOB
from jobhistory j, employee e, department d
where e.EmpNo=j.EmpNo
and d.DeptNo = e.DeptNo
and d.deptNo = (select deptno from employee where surname = 'Murphy' and forenames = Brian')
) where e.dob >= (select dob from employee where surname = 'Murphy' and forenames = Brian')

8.
select e.surname, e.forenames
from jobhistory j, employee e
where e.EmpNo=j.EmpNo
and j.salary = (select salary from jobhistory a, employee b where b.empno = a.empno and b.surname = 'MacCallan' and b.forenames = 'Claire' )
order by e.surnames asc

Note:

Question 9, does not have the information for the question 15 reference.

Let me know if you have any concern or queries for the above posted answers.

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

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