Question

SQL ONLY. WRITE CLEAR AND SIMPLE 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. List all the titles for which there is at least one employee having the title. 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. 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') 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'. 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). 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. Write SQL query to find out the name and current average salary of the department that has the maximum current average salary? Find the employee id of the current managers who currently manages more than 16000 employees. Find the name and current average salary of the departments whose current average salary is more than the current average salary of 'Development' department. Find the name of the employees who currently have the same salary but currently works in different department.

SQL ONLY. WRITE CLEAR AND SIMPLE ANSWERS. READ THE QUESTIONS CAREFULLY. IF AN ANSWER IS WRONG, I WILL DOWNVOTE.

Answer 1

SQL QUERY FOR ABOVE QUESTIONS:

(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_EXPERIENCE FROM EMPLOYEES ORDER BY YEAR_OF_EXPERIENCE 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).

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(DEPT_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'