Question

SQL ONLY. WRITE CLEAR AND CORRECT ANSWERS.

Consider the following relations (PRIMARY KEYS ARE WRITTEN IN BOLD)
departments (dept_no, dept_name)
dept_emp (emp_no, dept_no, from_date, to_date)
dept_manager (dept_no, emp_no, from_date, to_date)
employees (emp_no, birth_date, first_name, last_name, gender, hire_date)

salaries (emp_no, salary, from_date, to_date)
titles(emp_no, title, from_date, to_date)

Write the following queries in SQL. No duplicates should be printed in any of the answers.

1. List all the titles for which there is at least one employee having the title.

2. Find the current employee(s) (only id) who has/have the most (highest) experience working in a department [across all departments]. Here, we are referring to working only in one department. For example, let's assume there are only two employees: A and B. Now, A has been working in department X for 15 years and B worked in department X for 10 years and is working in department Y for 8 years. Although B has worked for a total of 18 years, he worked for 10 years in one department. So, for this query the output should be the id of A because A has worked for 15

   years in one department which is the highest.

3. Find the names (both first and last) and current salary of all employees who were hired after 31st

   December 1998.

   (Hint: The to_date for current salary is '9999-01-01')

4. Find the id and name (both first and last) of all employees who are currently not in the

   'Development' department and whose first name start with 'Tom'.

5. Find the names (both first and last) and current salaries of all employees who earn more than the

   current average salary of all employees. List them in the descending order based on their salary

   (highest salary first).

6. Some employees have worked in multiple departments. Find the names (both first and last) and

   the number of departments of all the female employees who have worked in more than one

   department.

7. Write SQL query to find out the name and current average salary of the department that has the

   maximum current average salary?

8. Find the employee id of the current managers who currently manages more than 16000

   employees.

9. Find the name and current average salary of the departments whose current average salary is

   more than the current average salary of 'Development' department.

10. Find the name of the employees who currently have the same salary but currently works in

    different department.

SQL ONLY. WRITE CLEAR AND CORRECT ANSWERS.

Answer 1

1. List all the titles for which there is at least one employee having the title.

select title from titles having count(emp_no)>=1

---------------------------
2. Find the current employee(s) (only id) who has/have the most (highest) experience working in a department [across all departments]. Here, we are referring to working only in one department. For example, let's assume there are only two employees: A and B. Now, A has been working in department X for 15 years and B worked in department X for 10 years and is working in department Y for 8 years. Although B has worked for a total of 18 years, he worked for 10 years in one department. So, for this query the output should be the id of A because A has worked for 15 years in one department which is the highest.

SELECT emp_no,EXTRACT(YEAR FROM age(CURRENT_DATE, hire_date)) as year_of_experince FROM employees order by year_of_experince DESC limit 1;

--------------------------------------------

3.Find the names (both first and last) and current salary of all employees who were hired after 31st December 1998. (Hint: The to_date for current salary is '9999-01-01')

SELECT first_name, last_name, salary 
FROM employees join salaries on employees.emp_no=salaries.emp_no
WHERE hire_date > Convert(datetime, '1998-12-31') and to_date='9999-01-01'

-----------------------------------------
4. Find the id and name (both first and last) of all employees who are currently not in the 'Development' department and whose first name start with 'Tom'.

SELECT E.emp_no,E.first_name, E.last_name FROM employees E 
   JOIN dept_emp DE ON E.emp_no = DE.emp_no 
    JOIN departments D on D.dept_no=DE.dept_no
       where DE.dept_name !='Development' and E.first_name like 'Tom%'

---------------------------------------------------

5.Find the names (both first and last) and current salaries of all employees who earn more than the current average salary of all employees. List them in the descending order based on their salary
(highest salary first).

Used the hint given in question 3 for current salary: (Hint: The to_date for current salary is '9999-01-01')

SELECT E.first_name, E.last_name,salary  
FROM employees Join salaries S on S.emp_no = E.emp_no
WHERE salary > 
(SELECT AVG (salary)  
FROM salaries) and to_date='9999-01-01' order by salary DESC;

---------------------------------------------------

6. Some employees have worked in multiple departments. Find the names (both first and last) and the number of departments of all the female employees who have worked in more than one department.

SELECT E.first_name, E.last_name,count(dpt_name)
  FROM employees 
     JOIN dept_emp on E.emp_no=dept_emp.emp_no
         JOIN departments on departments.dept_no = dept_emp.dept_no
             where E.gender="female"
                 GROUP BY E.first_name, E.last_name
                      HAVING COUNT(dpt_name) > 1;

--------------------------------------------------------

7. Write SQL query to find out the name and current average salary of the department that has the maximum current average salary?

SELECT D.dept_name, MAX(AVG(salary)) as max_avg_salary from departments D 
    JOIN dept_emp DE on D.dept_no=DE.dept_no
        JOIN salaries on DE.emp_no=salaries.emp_no where to_date='9999-01-01' order by max_avg_salary DESC limit 1;

---------------------------------------------------------

8. Find the employee id of the current managers who currently manages more than 16000 employees.

select emp_no from dept_manager where dept_no in (select dept_no from dept_emp having count(dept_no)>16000 group by dept_no) and to_date='9999-01-01';

---------------------------------------------------

9.Find the name and current average salary of the departments whose current average salary is more than the current average salary of 'Development' department.

SELECT dept_name,AVG(salary) as avg_salary FROM salaries S 
        JOIN dept_emp DE on S.emp_no=DE.emp_no 
                join departments D on D.dept_no = DE.dept_no
                        where avg_salary >(select AVG(salary) FROM salaries S 
                                                                        JOIN dept_emp DE on S.emp_no=DE.emp_no 
                                                                                        join departments D on D.dept_no = DE.dept_no where D.dept_name='Development' where S.to_date='9999-01-01' group by dept_name) and S.to_date='9999-01-01' group by dept_name;

--------------------------------------------------------

10. Find the name of the employees who currently have the same salary but currently works in different department.

SELECT E.first_name from employees E1 
           join salaries S1 On E.emp_no = S.emp_no
               join salaries S2 on(S1.salary=S2.salary) and (S1.emp_no < > S2.emp_no) where S1.to_date='9999-01-01';