一、上节课内容回顾:
复习:查询每个部门中没有“佣金(COMM)”员工的最低工资、最高工资和平均工资。
结果只显示平均工资在1500以上的信息,并将查询结果按照部门号降序排序。
select MIN(SAL),MAX(SAL),AVG(SAL) from emp
where comm is NULL
group by deptno
having avg(sal)>1500
order by deptno
二、本节课主要任务:
要求:每道题目给出SQL命令及运行结果截图。
题目1:查询雇员的姓名、编号及所在部门名称;
SELECT ename,empno,dname
FROM emp INNER JOIN dept ON emp.deptno=dept.deptno
题目2:查询所有经理的姓名,所在部门名称、月收入及部门所在位置;
SELECT ename,dname,sal,loc
FROM emp INNER JOIN dept ON emp.deptno=dept.deptno
WHERE job='manager'
题目3:查询每个员工的姓名、所在部门名及对应领导的姓名。
SELECT e1.ename 员工姓名,dname 部门名,e2.ename 经理姓名
FROM emp e1 INNER JOIN emp e2 ON
e1.mgr=e2.empno INNER JOIN dept ON dept.deptno=e1.deptno
题目4:查询每位员工的姓名、编号及工资等级。
SELECT ename,empno,grade
FROM emp,salgrade
WHERE sal BETWEEN salgrade.losal AND salgrade.hisal
练习:
(1)查询部门的名称、所在地及每个部门的人数。
SELECT d.dname,d.loc,COUNT(*)
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY d.dname,d.loc
(2)查询每个部门的部门名称及工作时间最长的员工雇佣时间。
SELECT dname,MIN(hiredate)
FROM emp INNER JOIN dept ON emp.deptno=dept.deptno
GROUP BY dname,loc
(3)查询所有员工及其上层管理者信息,要求查询结果显示员工姓名,
上层领导姓名及所在部门号及部门名,并按照部门号降序排序
SELECT e1.ename 员工姓名,e2.ename 领导姓名, e1.deptno 部门号,dept.dname 部门名
from emp e1 inner join dept on e1.deptno=dept.deptno
inner join emp e2 on e1.deptno=e2.deptno where e1.mgr=e2.empno
order by e1.deptno desc
外连接
准备工作:
首先使用insert语句在emp表中插入新记录(注意没有为deptno列插入值,即它们的值为null)
INSERT INTO emp (empno,ename)
VALUES(8888,'Bob')
1.实现emp表和dept表之间通过deptno列进行左外连接。
SELECT *
from emp LEFT JOIN dept ON emp.deptno=dept.deptno
2.实现emp表和dept表之间通过deptno列进行右外连接。
SELECT *
from emp RIGHT JOIN dept ON emp.deptno=dept.deptno
3.实现emp表和dept表之间通过deptno列进行完全外连接。
SELECT * FROM
emp LEFT JOIN dept on emp.deptno=dept.deptno
union
SELECT * FROM
emp right JOIN dept on emp.deptno=dept.deptno
练习:查询所有部门的名称、所在地及每个部门的人数。
select dname,loc,COUNT(empno) FROM
emp right JOIN dept on emp.deptno=dept.deptno
GROUP BY dname