测试派 Give testers a home.

1.列出至少有三个员工的所有部门和部门信息
select * from DEPT WHERE DEPT.DEPTNO IN(select DEPTNO from EMP group by DEPTNO HAVING COUNT(EMP.DEPTNO)>=3)

2.列出受雇日期早于直接上级的所有员工的编号，姓名，部门名称
SELECT S.EMPNO,S.ENAME,DEPT.DNAME FROM EMP S LEFT JOIN EMP Y ON Y.EMPNO=S.MGR INNER JOIN DEPT ON DEPT.DEPTNO=S.DEPTNO WHERE Y.HIREDATE>S.HIREDATE
3.列出职位为“CLERK”的姓名和部门名称，部门人数：
SELECT DEPT.DNAME,B.ENAME,B.NUM FROM (SELECT * FROM EMP WHERE JOB='CLERK') A JOIN (SELECT COUNT(EMPNO) NUM,ENAME,DEPTNO FROM EMP GROUP BY DEPTNO) B ON A.DEPTNO=B.DEPTNO
JOIN DEPT ON B.DEPTNO=DEPT.DEPTNO

查询出每门课程都大于80分的学生姓名

select sname from tb_lemon_score group by sname having MIN(score)>80；

给出所有购入物品为两种或两种以上的购物人记录

select 购物人 from 购物单 where 数量>=2 group by 购物人；

1、查询张姓员工的员工信息和所在部门信息。

select * from emp left join dept on dept.id=emp.dept_id where emp.name like '张%'

2、查询张三丰管理了几个员工

select emp.name from emp A join emp on A.id=emp.leader where emp.leader=1

3、查询出所有实习员工（实习员工无部门信息）
select * from emp left join dept on dept.id=emp.dept_id where dept.name is null

1、查询张姓员工的员工信息和所在部门信息。

select * from emp left join dept on dept.id=emp.dept_id where emp.name like '张%'

2、查询张三丰管理了几个员工

select emp.name from emp A join emp on A.id=emp.leader where emp.leader=1

3、查询出所有实习员工（实习员工无部门信息）
select * from emp left join dept on dept.id=emp.dept_id where dept.name is null

select * from emp left join dept on dept.id=emp.dept_id