–1.列出至少有一个员工的所有部门
select deptno from emp group by deptno having count(*)>1;
select count(),deptno from emp group by deptno having count()>1;
–2.列出薪金比“SMITH”多的所有员工。
– 2.1 SMITH的薪金
select sal from emp where ename=‘SMITH’;
– 2.2 比SMITH高的员工
select * from emp;
select * from emp where sal>(select sal from emp where ename=‘SMITh’);
–3.列出所有员工的姓名及其直接上级的姓名。
select ename,(select ename from emp where empno=a.mgr) from emp a;
–4.列出受雇日期早于其直接上级的所有员工。
select ename from emp a where hiredate>(select hiredate from emp where empno=a.mgr);
–5.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门
select dept.dname,emp.* from dept left outer join emp on dept.deptno=emp.deptno;
select dept.dname,emp.* from emp right join dept on emp.deptno=dept.DEPTNO;
–6.列出所有“CLERK”(办事员)的姓名及其部门名称。
select emp.ename,dept.dname from emp,dept where emp.deptno=dept.deptno and emp.job&