SQL_Query_Interview_Questions.sql

/* Database table install instructions */
https://dev.mysql.com/doc/employee/en/employees-installation.html

/* Return employee record with max salary */
SELECT *
	FROM employee 
	WHERE salary = (SELECT MAX(salary) FROM employee)

/* Select highest salary in employee table */
SELECT MAX(salary)
	FROM employee

/* Select 2nd highest salary in employee table */
SELECT MAX(salary) 
	FROM employee
	WHERE salary NOT IN(SELECT MAX(salary) FROM employee)

/* select range of employees based on id */
SELECT *
	FROM employee
	WHERE employee_id BETWEEN 2003 AND 2008

/* Return employee  name, highest salary and department */
SELECT e.first_name, e.last_name, e.salary, d.department_name
	FROM employee e
	INNER JOIN department d ON (e.department_id = d.department_id)
	WHERE salary IN (SELECT MAX(salary) FROM employee)

/* Return highest salary, employee name, department name for each department */
SELECT e.first_name, e.last_name, e.salary, d.department_name
	FROM employee e
	INNER JOIN department d ON (e.department_id = d.department_id)
	WHERE salary IN (SELECT MAX(salary) FROM employee GROUP BY department_id)