Consider the following relational schema. 1) Retrieve the first names of employe
ID: 3855271 • Letter: C
Question
Consider the following relational schema.
1) Retrieve the first names of employees who born in '21-JUN-63' and managed no department.
2) Retrieve the first names of employees who have the highest salary.
3) Count the number of employees who managed 'Research' department.
4) Retrieve the emp_nos of employees who worked on more than two departments.
5) Count the number of employees who worked on all departments.
" Consider the following relational schema titles employees emp no NUMERIC5.D) birth date DATE first name CHARACTER VARYING(14) last name CHARACTER VARYING(16) gender CHARACTER(1 hire date DATE title CHARACTER VARYING(E0) from_date DATE to_date DATE salaries salary NUMERIC ,0) from date DATE to date DATE dept manager deptno CHARACTER4) emp-no NUMERIC(B from date DATE to date DATE departments dept no CHARACTER4) dept name CHARACTER VARYING40) dept emp / emp-no NUMERIC(BO) dept no CHARACTERI4 from date DATE to date DATE 1) (10 points) Retrieve the_first names of employees who born in "21-JUN-63' and managed no department 2) (10 points) Retrieve the first names of employees who have the highest salary 3) (10 points) Count the number of employees who managed 'Research' department. 4) (10 points) Retrieve the emp_nos of employees who worked on more than two departments 5) (10 points) Count the number of employees who worked on all departmentsExplanation / Answer
1. Select A.first_name from employees A join dept_emp B on A.emp_no!=B.emp_no where birth_date='21-JUN-63'
2. Select A.first_name from employees A join salaries B on A.emp_no!=B.emp_no where B.Salary in (select MAX(Salary) from salaries)
3. Select count(A.emp_no) As EmployeeCount from employees A join dept_emp B on A.emp_no=B.emp_no join departments C on B.dept_no=C.dept_no where C.dept_name='Research'
4. Select count(A.emp_no) from employees A join dept_emp B on A.emp_no=B.emp_no group by A.emp_no having count(A.emp_no)>2
5. Select count(A.emp_no) from employees A join dept_emp B on A.emp_no=B.emp_no