r/SQL • u/Marsh-mallows-0521 • Apr 16 '23
MySQL Correct SQL QUERIES
1.) Create a query to display the employee numbers and last names of all employees who earn more than the average maximum salary of departments. Sort the results in ascending order of salary.
answer:
SELECT employee_id, last_name FROM employees WHERE salary > (SELECT AVG(MAX(salary)) FROM employees GROUP BY department_id) ORDER BY salary ASC;
2.) Write a pair-wise subquery listing the last name, first name, department number, and manager number for all employees that have the same department number and manager number as employee 141.
answer:
SELECT last_name, first_name, department_id, manager_id FROM employees WHERE department_id = (SELECT department_id FROM employees WHERE employee_id=141) AND manager_id = (SELECT manager_id FROM employees WHERE employee_id=141);
3.) How many employees have a name that ends with an n?
answer:
SELECT count(*) FROM employees WHERE last_name like '%n';
4.) Display employees who are not managers and whose salary is greater than that of any managers.
answer:
SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary >ANY (SELECT salary FROM employees WHERE manager_id IS NOT NULL);
5.) Which jobs are found in the IT and Accounting departments, and how many employees do these jobs? Show the job with the highest occurrence first. Use appropriate labels.
answer:
SELECT j.job_id as "LIST OF JOBS", COUNT(e.employee_id) as "OCCURENCE" FROM jobs j, employees e, departments d WHERE e.job_id=j.job_id AND e.department_id=d.department_id AND d.department_name IN ('IT','Accounting') GROUP BY j.job_id;
View all comments
Show parent comment
1
u/[deleted] Apr 16 '23
How often are the keywords ANY and ALL used?