sql笔试题(1)

--查询各部门工资超过3000的员工数
SELECT department.deptName,COUNT(*)
FROM employee,department
WHERE employee.departId=department.departId AND employee.income>3000
GROUP BY employee.departId

SELECT department.deptName,COUNT(*)
FROM department
LEFT JOIN employee
ON department.departId=employee.departId
WHERE employee.income>3000
GROUP BY department.departId;

--查询各部门工资最高的人
SELECT *
FROM employee e,
(SELECT departid,MAX(income) AS maxIncome
FROM employee
GROUP BY departid) t
WHERE e.departid=t.departid
AND e.income=t.maxIncome;

SELECT departid,MAX(income),employee.name
FROM employee
WHERE employee.departId=001
ORDER BY income