子查询测试题

#1.查询最高工资的员工的姓名
SELECT CONCAT(first_name,'-',last_name) AS 'name'
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

#2.查询谁的工资比Abel高
SELECT last_name
FROM employees
WHERE salary>(SELECT salary FROM employees WHERE last_name='Abel');

#3.返回工资最少的员工信息
SELECT * FROM employees
WHERE salary=(SELECT MIN(salary) FROM employees);


#4.查询编号最小，工资最高的员工信息
SELECT * FROM employees
WHERE employee_id = (SELECT MIN(employee_id) FROM employees)
AND salary=(SELECT MAX(salary) FROM employees);

#4.查询每个部门的员工个数

SELECT d.*,(
	SELECT COUNT(*) FROM employees e WHERE e.department_id = d.department_id) AS num
FROM departments d
ORDER BY num DESC;