MySQL【基础】复杂条件多表查询

1. 查询出 教学部 年龄大于20岁,并且工资小于40000的员工,按工资倒序排列.(要求:分别使用多表联合查询和内连接查询)

#1.多表联合查询方式:
SELECT * FROM person p1,dept d2 where p1.did = d2.did
and d2.dname = '教学部'
and age > 20
and salary < 40000
ORDER BY salary DESC;

#2.内连接查询方式:
SELECT * FROM person p1 INNER JOIN dept d2 ON p1.did= d2.did 
    and d2.dname='教学部' 
    and  age>20 
    and salary <40000 
ORDER BY salary DESC;    

 2.查询每个部门中最高工资和最低工资是多少,显示部门名称

SELECT MAX(salary),MIN(salary),dept.dname from 
person LEFT JOIN dept on person.did = dept.did
GROUP BY person.did;

 结果为：