-- 1.查询出部门编号为BM01的所有员工 SELECT * FROM emp e WHERE e.deptno = 'BM01'; -- 2.所有销售人员的姓名、编号和部门编号。 SELECT e.empname, e.empno, e.deptno FROM emp e WHERE e.empstation = "销售人员"; -- 3.找出奖金高于工资的员工。 SELECT * FROM emp2 WHERE sal < comm; -- 4.找出奖金高于工资60%的员工。 SELECT * FROM emp2 WHERE comm > ( sal * 0.6 ); -- 5.找出部门编号为10中所有经理,和部门编号为20中所有销售员的详细资料。 select * from emp2 where (deptno = 10 and job = 'MANAGER') or (deptno = 20 and job = 'SALESMAN'); -- 6.找出部门编号为10中所有经理,部门编号为20中所有销售员,还有即不是经理又不是销售员但其工资大或等于20000的所有员工详细资料。 select * from emp2 where (deptno = 10 and job = 'MANAGER') or (deptno = 20 and job = 'SALESMAN') or (job not in ('MANAGER','SALESMAN') and sal >= 2000); -- 7.无奖金或奖金低于1000的员工。 select * from emp2 where comm is null or comm < 1000; -- 8. 查询名字由三个字组成的员工。 -- 注意:一个汉字占三个字节 select * from emp2 e where e.ename like '___'; -- 9.查询2023年入职的员工。 select * from emp2 where YEAR(hiredate) like '2023%'; -- 10. 查询所有员工详细信息,用编号升序排序 select * from emp2 order by empno asc; -- 11. 查询所有员工详细信息,用工资降序排序,如果工资相同使用入职日期升序排序 select * from emp2 e order by e.sal desc,e.hiredate; -- 12.查询每个部门的平均工资 select avg(e.sal) d.deptname from emp2 e,dept d group by d.deptno on e.deptno = d.deptno; select avg(sal),deptno,dept from emp2 group by deptno; -- 13.查询每个部门的雇员数量 select deptno,count(deptno) from emp2 group by deptno; -- 14.查询每种工作的最高工资、最低工资、人数 select max(sal),min(sal),count(job) from emp2 group by job; -- 18.列出工资比ALLEN高的所有员工 select * from emp2 where sal > (select sal from emp2 where ename = 'ALLEN'); -- 19.列出所有员工的姓名及其直接上级的姓名 select e1.ename, e2.ename from emp2 e1,emp2 e2 where e1.mgr = e2.empno -- 20.列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称 select e1.ename, e2.ename from emp2 e1,emp2 e2 where e1.mgr = e2.empno and e1.hiredate < e2.hiredate; -- 21.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门 select deptname,e.* from dept d,emp2 e where count(dept) is null; -- 22.列出所有文员(CLERK)的姓名及其部门名称,部门的人数 select e.ename,d.deptname,d.deptno from emp2 e join dept d where e.job = 'CLERK'; -- 23.列出最低薪金大于1500的各种岗位及从事此岗位的员工人数 SELECT e.job,min(sal) min_salary,count(e.empno) from emp2 e group by e.job having min_salary > 1500; -- 25.列出薪金高于公司平均薪金的所有员工信息,所在部门名称,上级领导,工资等级 -- select d.deptname,e1.ename e2.ename as leadername, -- where emp2 e1 join dept12 d on e.deptno = d.deptno -- left join emp2 b on e.mgr = b.empno#解决员工与领导的一个关联问题 -- join sa select d.dname,e.ename,b.ename as leadername,grade from emp e join dept d on e.deptno=d.deptno left join emp b on e.mgr=b.empno join salgrade s on e.sal between s.losal and s.hisal where e.sal>(select avg(sal) avgsal from emp); +------------+-------+------------+-------+ | dname | ename | leadername | grade | +------------+-------+------------+-------+ | RESEARCH | FORD | JONES | 4 | | RESEARCH | SCOTT | JONES | 4 | | ACCOUNTING | CLARK | KING | 4 | | SALES | BLAKE | KING | 4 | | RESEARCH | JONES | KING | 4 | | ACCOUNTING | KING | NULL | 5 | -- 26.列出与SMITH从事相同工作的所有员工及部门名称 select d.dname,e.* from emp2 e join dept12 d on e.deptno = d.deptno where e.job = (select job from emp2 where ename = "SMITH"); -- 27.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称 # (1)部门30中的员工最高工资 select max(sal) from emp2 where deptno=30; +----------+ | max(sal) | +----------+ | 2850.00 | +----------+ # (2)高于最高工资的其他员工 select deptno,ename,sal from emp2 where sal>(select max(sal) from emp where deptno=30) and deptno!=30; +--------+-------+---------+ | deptno | ename | sal | +--------+-------+---------+ | 20 | JONES | 2975.00 | | 20 | SCOTT | 3000.00 | | 10 | KING | 5000.00 | | 20 | FORD | 3000.00 | +--------+-------+---------+ # (3)连接dept表 select d.dname,t.ename,t.sal from (select deptno,ename,sal from emp2 where sal>(select max(sal) from emp where deptno=30) and deptno!=30) t join dept12 d on t.deptno=d.deptno; +------------+-------+---------+ | dname | ename | sal | +------------+-------+---------+ | RESEARCH | JONES | 2975.00 | | RESEARCH | SCOTT | 3000.00 | | ACCOUNTING | KING | 5000.00 | | RESEARCH | FORD | 3000.00 | +------------+-------+---------+ -- 28.列出在每个部门工作的员工数量、平均工资 select d.deptno,count(ename) num,avg(sal) from emp2 e right join dept12 d on e.deptno = d.deptno group by deptno;
mysql练习1
于 2023-11-19 23:07:45 首次发布