作业
-
取得每个部门最高薪水的人员名称
第一步：取得每个部门最高薪水（按照部门编号分组，找出每一组最大值） select deptno,max(sal) maxsal from emp group by deptno;+--------+---------+| deptno | maxsal |+--------+---------+| 20 | 3000.00 || 30 | 2850.00 || 10 | 5000.00 |+--------+---------+第二步：将以上结果当作临时表t，t表和emp e表进行连接， 条件是：t.deptno = e.deptno and t.maxsal = e.salselect e.ename,t.* from (select deptno,max(sal) maxsal from emp group by deptno) t join emp e on t.deptno = e.deptno and t.maxsal = e.sal;+-------+--------+---------+| ename | deptno | maxsal |+-------+--------+---------+| BLAKE | 30 | 2850.00 || SCOTT | 20 | 3000.00 || KING | 10 | 5000.00 || FORD | 20 | 3000.00 |+-------+--------+---------+ -
哪些人的薪水在部门的平均薪水之上
第一步：找出每个部门的平均薪水 select deptno,avg(sal) avgsal from emp group by deptno; +--------+-------------+ | deptno | avgsal | +--------+-------------+ | 20 | 2175.000000 | | 30 | 1566.666667 | | 10 | 2916.666667 | +--------+-------------+第二步：找出比平均薪水高的人 select e.deptno,e.ename,e.sal,t.avgsal from (select deptno,avg(sal) avgsal from emp group by deptno) t join emp e on e.deptno =t.deptno where e.sal>t.avgsal; +--------+-------+---------+-------------+ | deptno | ename | sal | avgsal | +--------+-------+---------+-------------+ | 30 | ALLEN | 1600.00 | 1566.666667 | | 20 | JONES | 2975.00 | 2175.000000 | | 30 | BLAKE | 2850.00 | 1566.666667 | | 20 | SCOTT | 3000.00 | 2175.000000 | | 10 | KING | 5000.00 | 2916.666667 | | 20 | FORD | 3000.00 | 2175.000000 | +--------+-------+---------+-------------+ -
取得部门中（所有人的）平均的薪水等级
select deptno,avg(grade) from emp e join salgrade s on e.sal between losal and hisal group by deptno; +--------+------------+ | deptno | avg(grade) | +--------+------------+ | 20 | 2.8000 | | 30 | 2.5000 | | 10 | 3.6667 | +--------+------------+ 平均的薪水等级：先计算每一个薪水的等级，然后找出薪水等级的平均值。 平均薪水的等级：先计算平均薪水，然后找出每个平均薪水的等级值。 -
不准用组函数（Max），取得最高薪水
第一种方案：降序 select ename,sal from emp order by sal desc limit 1; +-------+---------+ | ename | sal | +-------+---------+ | KING | 5000.00 | +-------+---------+第二种方案：表的自连接 select distinct a.sal from emp a join emp b on a.sal <b.sal; --同一个表中的数据大小相比较，取a中的，a中的小于b中的全部取出来了，则a中最大的就剩下了，然后看哪一个不在这个结果中就是最大的。 select sal from emp where sal not in(select distinct a.sal from emp a join emp b on a.sal <b.sal); +---------+ | sal | +---------+ | 5000.00 | +---------+ -
取得平均薪水最高的部门的部门编号
第一种方案：降序取第一个第一步：找出每个部门的平均薪水select deptno,avg(sal) from emp group by deptno;+--------+-------------+| deptno | avg(sal) |+--------+-------------+| 20 | 2175.000000 || 30 | 1566.666667 || 10 | 2916.666667 |+--------+-------------+第二步：select deptno,avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1;+--------+-------------+| deptno | avgsal |+--------+-------------+| 10 | 2916.666667 |+--------+-------------+第二种方案：maxselect max(avgsal) from (select avg(sal) avgsal from emp group by deptno) t;select deptno,avg(sal) avgsal from emp group by deptno having avgsal =(select max(avgsal) from (select avg(sal) avgsal from emp group by deptno)t);+--------+-------------+| deptno | avgsal |+--------+-------------+| 10 | 2916.666667 |+--------+-------------+ -
取得平均薪水最高的部门的部门名称
第一步：找出每个部门的平均薪水的等级 select d.dname,avg(sal) avgsal from emp e join dept d on e.deptno=d.deptno group by d.dname order by avgsal desc limit 1;+------------+-------------+ | dname | avgsal | +------------+-------------+ | ACCOUNTING | 2916.666667 | +------------+-------------+ -
求平均薪水的等级最低的部门的部门名称
mysql> select deptno,avg(sal) avgsal from emp group by deptno; +--------+-------------+ | deptno | avgsal | +--------+-------------+ | 20 | 2175.000000 | | 30 | 1566.666667 | | 10 | 2916.666667 | +--------+-------------+ 3 rows in set (0.00 sec)mysql> select * from (select deptno,avg(sal) avgsal from emp group by deptno) t join salgrade s on t.avgsal between losal and hisal order by grade limit 1 ; +--------+-------------+-------+-------+-------+ | deptno | avgsal | GRADE | LOSAL | HISAL | +--------+-------------+-------+-------+-------+ | 30 | 1566.666667 | 3 | 1401 | 2000 | +--------+-------------+-------+-------+-------+select dname from dept where deptno =(select deptno from (select deptno,avg(sal) avgsal from emp group by deptno) t join salgrade s on t.avgsal between losal and hisal order by grade limit 1); +-------+ | dname | +-------+ | SALES | +-------+ -
取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高的领导人姓名
先找出是领导的，值为null的排除掉 select distinct mgr from emp where mgr is not null; +------+ | mgr | +------+ | 7902 | | 7698 | | 7839 | | 7566 | | 7788 | | 7782 | +------+ 找出普通员工；即编号不在以上结果中的都是普通员工 select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null); +----------+ | max(sal) | +----------+ | 1600.00 | +----------+ 找出薪资高于以上结果的领导的新明 select ename,sal from emp where sal >(select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null));+-------+---------+ | ename | sal | +-------+---------+ | JONES | 2975.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | | SCOTT | 3000.00 | | KING | 5000.00 | | FORD | 3000.00 | +-------+---------+ -
取得薪水最高的前五名员工
select ename,sal from emp order by sal desc limit 5; +-------+---------+ | ename | sal | +-------+---------+ | KING | 5000.00 | | SCOTT | 3000.00 | | FORD | 3000.00 | | JONES | 2975.00 | | BLAKE | 2850.00 | +-------+---------+ -
取得薪水最高的第六到第十名员工
select ename,sal from emp order by sal desc limit 5,5; +--------+---------+ | ename | sal | +--------+---------+ | CLARK | 2450.00 | | ALLEN | 1600.00 | | TURNER | 1500.00 | | MILLER | 1300.00 | | WARD | 1250.00 | +--------+---------+ -
取得最后入职的5名员工。
select ename,hiredate from emp order by hiredate desc limit 5; +--------+------------+ | ename | hiredate | +--------+------------+ | ADAMS | 1987-05-23 | | SCOTT | 1987-04-19 | | MILLER | 1982-01-23 | | JAMES | 1981-12-03 | | FORD | 1981-12-03 | +--------+------------+ -
取得每个薪水等级有多少员工
select grade,count(grade) from emp e join salgrade s on e.sal between losal and hisal group by grade; +-------+--------------+ | grade | count(grade) | +-------+--------------+ | 1 | 3 | | 3 | 2 | | 2 | 3 | | 4 | 5 | | 5 | 1 | +-------+--------------+ -
面试题
有3个表S(学生表)，C（课程表），SC（学生选课表）
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）
问题：
请用标准SQL语言写出答案，方言也行（请说明是使用什么方言）。
CREATE TABLE SC (SNO VARCHAR(200),CNO VARCHAR(200),SCGRADE VARCHAR(200) );CREATE TABLE S (SNO VARCHAR(200 ),SNAME VARCHAR(200) );CREATE TABLE C (CNO VARCHAR(200),CNAME VARCHAR(200),CTEACHER VARCHAR(200) );INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '1', '语文', '张'); INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '2', '政治', '王'); INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '3', '英语', '李'); INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '4', '数学', '赵'); INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '5', '物理', '黎明'); commit;INSERT INTO S ( SNO, SNAME ) VALUES ( '1', '学生1'); INSERT INTO S ( SNO, SNAME ) VALUES ( '2', '学生2'); INSERT INTO S ( SNO, SNAME ) VALUES ( '3', '学生3'); INSERT INTO S ( SNO, SNAME ) VALUES ( '4', '学生4'); commit;INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '1', '40'); INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '2', '30'); INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '3', '20'); INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '4', '80'); INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '5', '60'); INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '1', '60'); INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '2', '60'); INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '3', '60'); INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '4', '60'); INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '5', '40'); INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '1', '60'); INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '3', '80'); commit;-
1、找出没选过“黎明”老师的所有学生姓名。
--先找出黎明所教的课程号 select cno from c where cteacher='黎明'; --在选课表中选了黎明的课的学生 select distinct sno from sc where cno = (select cno from c where cteacher='黎明'); --查找没有选课的学生的姓名 select sname from s where sno not in(select distinct sno from sc where cno = (select cno from c where cteacher='黎明')); +-------+ | sname | +-------+ | 学生3 | | 学生4 | +-------+ -
2、列出2门以上（含2门）不及格学生姓名及平均成绩。
--找出两门不及格学生的sno select sno from sc where scgrade <60 group by sno having count(sno)>2; +------+ | sno | +------+ | 1 | +------+ --求平均成绩 select sname,avg(scgrade) from sc join s on sc.sno=s.sno group by sname; --加入条件 select sname,avg(scgrade) from sc join s on sc.sno=s.sno where s.sno=(select sno from sc where scgrade <60 group by sno having count(sno)>2) group by sname; +-------+--------------+ | sname | avg(scgrade) | +-------+--------------+ | 学生1 | 46 | +-------+--------------+---另一种方法 selectsc.sno, s.sname, count(*) as studentNum fromsc joins on sc.sno = s.sno wheresc.scgrade < 60 group bysc.sno, s.sname havingstudentNum >= 2; -
3、即学过1号课程又学过2号课所有学生的姓名。
--学过1号课程的学生 select s.sno, s.sname from sc join s on sc.sno = s.sno where cno = 1 --学过2号课的 select sno from sc where cno = 2; --合并：从学过1号课的学生中找学过2号课的 select s.sno, s.sname from sc join s on sc.sno = s.sno where cno = 1 and s.sno in (select sno from sc where cno = 2); +------+-------+ | sno | sname | +------+-------+ | 1 | 学生1 | | 2 | 学生2 | +------+-------+ --另一种方法 select sno,sname from s where sno in (select sno from sc where cno = 2) and s.sno in (select sno from sc where cno = 2); +------+-------+ | sno | sname | +------+-------+ | 1 | 学生1 | | 2 | 学生2 | +------+-------+
-
-
列出所有员工及领导的姓名
--a表是员工表，b表是领导表。连接条件就是员工的领导编号=领导的员工编号。即：a.mgr = b.empno --所有的员工的领导都必须显示出来，所以用左外连接 select a.ename '员工',b.ename '领导' from emp a left join emp b on a.mgr =b.empno;+--------+-------+| 员工 | 领导 |+--------+-------+| SMITH | FORD || ALLEN | BLAKE || WARD | BLAKE || JONES | KING || MARTIN | BLAKE || BLAKE | KING || CLARK | KING || SCOTT | JONES || KING | NULL || TURNER | BLAKE || ADAMS | SCOTT || JAMES | BLAKE || FORD | JONES || MILLER | CLARK |+--------+-------+ --做一个空处理 select a.ename '员工',ifnull(b.ename,'领导自己') '领导' from emp a left join emp b on a.mgr =b.empno;+--------+----------+| 员工 | 领导 |+--------+----------+| SMITH | FORD || ALLEN | BLAKE || WARD | BLAKE || JONES | KING || MARTIN | BLAKE || BLAKE | KING || CLARK | KING || SCOTT | JONES || KING | 领导自己 || TURNER | BLAKE || ADAMS | SCOTT || JAMES | BLAKE || FORD | JONES || MILLER | CLARK |+--------+----------+ -
列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
--早于：小于 select a.empno,a.deptno,a.ename from emp a join emp b on a.mgr = b.empno where a.hiredate < b.hiredate;+-------+--------+-------+| empno | deptno | ename |+-------+--------+-------+| 7369 | 20 | SMITH || 7499 | 30 | ALLEN || 7521 | 30 | WARD || 7566 | 20 | JONES || 7698 | 30 | BLAKE || 7782 | 10 | CLARK |+-------+--------+-------+--再用以上表与部门表连接 select t.empno,t.ename,d.dname from dept d join (select a.empno,a.deptno,a.ename from emp a join emp b on a.mgr = b.empno where a.hiredate < b.hiredate) t on d.deptno = t.deptno;+-------+-------+------------+| empno | ename | dname |+-------+-------+------------+| 7369 | SMITH | RESEARCH || 7499 | ALLEN | SALES || 7521 | WARD | SALES || 7566 | JONES | RESEARCH || 7698 | BLAKE | SALES || 7782 | CLARK | ACCOUNTING |+-------+-------+------------+ -
列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门
select d.dname,e.* from dept d left join emp e on e.deptno = d.deptno;+------------+-------+--------+-----------+------+------------+---------+---------+--------+ | dname | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +------------+-------+--------+-----------+------+------------+---------+---------+--------+ | ACCOUNTING | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | | ACCOUNTING | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | ACCOUNTING | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | | RESEARCH | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | RESEARCH | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | | RESEARCH | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | RESEARCH | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | RESEARCH | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | SALES | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | | SALES | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | SALES | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | SALES | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | | SALES | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | SALES | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | OPERATIONS | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +------------+-------+--------+-----------+------+------------+---------+---------+--------+ -
列出至少有5个员工的所有部门
select dname,count(*) 部门人数 from dept d left join emp e on d.deptno = e.deptno group by dname having 部门人数>=5;+----------+----------+ | dname | 部门人数 | +----------+----------+ | RESEARCH | 5 | | SALES | 6 | +----------+----------+ -
列出薪金比"SMITH"多的所有员工信息
select * from emp where sal>(select sal from emp where ename='SMITH');+-------+--------+-----------+------+------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+--------+-----------+------+------------+---------+---------+--------+ | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | +-------+--------+-----------+------+------------+---------+---------+--------+ -
列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数.
--先找出姓名和部门名称 select ename,dname from emp e join dept d on e.deptno = d.deptno where job='CLERK'; +--------+------------+ | ename | dname | +--------+------------+ | SMITH | RESEARCH | | ADAMS | RESEARCH | | JAMES | SALES | | MILLER | ACCOUNTING | +--------+------------+ --找出部门人数 select deptno,count(*) cc from emp group by deptno; +--------+----+ | deptno | cc | +--------+----+ | 20 | 5 | | 30 | 6 | | 10 | 3 | +--------+----+--表连接 --先找出每个员工所在部门的人数，再找出办事员所在坐在部门 select ename,dname,cc from emp e join (select deptno,count(*) cc from emp group by deptno) t on t.deptno=e.deptno join dept d on e.deptno = d.deptno where job='CLERK'; -
列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数
select job,count(job) from emp group by job having min(sal) >1500; +-----------+------------+ | job | count(job) | +-----------+------------+ | MANAGER | 3 | | ANALYST | 2 | | PRESIDENT | 1 | +-----------+------------+ -
列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部门编号
--找出销售部的部门编号 select deptno from dept where dname='SALES'; --找出部门编号为以上的员工的姓名 select ename from emp where deptno=(select deptno from dept where dname='SALES');+--------+ | ename | +--------+ | ALLEN | | WARD | | MARTIN | | BLAKE | | TURNER | | JAMES | +--------+ -
列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级
select e.ename '员工',d.dname '部门',ifnull(f.ename,'领导自己') '领导',s.grade from emp e join dept d on d.deptno=e.deptno left join emp f on e.mgr=f.empno --这里用左连接，要保证所有的员工都存在 join salgrade s on e.sal between losal and hisal where e.sal >(select avg(sal) from emp); +-------+------------+----------+-------+ | 员工 | 部门 | 领导 | grade | +-------+------------+----------+-------+ | FORD | RESEARCH | JONES | 4 | | SCOTT | RESEARCH | JONES | 4 | | CLARK | ACCOUNTING | KING | 4 | | BLAKE | SALES | KING | 4 | | JONES | RESEARCH | KING | 4 | | KING | ACCOUNTING | 领导自己 | 5 | +-------+------------+----------+-------+ -
列出与"SCOTT"从事相同工作的所有员工及部门名称
--先找出scott所从事工作 select job from emp where ename='SCOTT';+---------+ | job | +---------+ | ANALYST | +---------+ --表连接 select ename,job,dname from emp e join dept d on d.deptno=e.deptno where job=(select job from emp where ename='SCOTT') and ename <> 'SCOTT'; +-------+---------+----------+ | ename | job | dname | +-------+---------+----------+ | FORD | ANALYST | RESEARCH | +-------+---------+----------+ -
列出薪金等于（部门30中员工的薪金）的其他员工的姓名和薪金
--先找出部门30中的员工的薪金 select distinct sal from emp where deptno=30; +---------+ | sal | +---------+ | 1600.00 | | 1250.00 | | 2850.00 | | 1500.00 | | 950.00 | +---------+--找出其他的 select ename,sal from emp where sal in (select distinct sal from emp where deptno=30) and deptno <> 30; Empty set (0.00 sec) -
列出薪金高于（在部门30工作的所有员工的薪金）的员工姓名和薪金.部门名称.
--先找出部门30工作的所有员工的最高薪金 select max(sal) from emp where deptno = 30; +----------+ | max(sal) | +----------+ | 2850.00 | +----------+select ename,sal,dname from emp e join dept d on d.deptno=e.deptno where sal>(select max(sal) from emp where deptno = 30); +-------+---------+------------+ | ename | sal | dname | +-------+---------+------------+ | JONES | 2975.00 | RESEARCH | | SCOTT | 3000.00 | RESEARCH | | KING | 5000.00 | ACCOUNTING | | FORD | 3000.00 | RESEARCH | +-------+---------+------------+ -
列出在每个部门工作的员工数量,平均工资和平均服务期限.
-
在mysql中怎么计算两个日期的“年差”，差了多少年。
TimeStampDiff(间隔日期，前一个日期，后一个日期) timestampdiff(YEAR,hiredate,now()); 间隔类型：SECOND 秒MINUTE 分钟HOUR 小时DAY 天WEEK 星期MONTH 月QUARTER 季度YEAR 年
select dname '部门',count(ename) '员工数量',ifnull(avg(sal),0) '平均薪资',avg(timestampdiff(year,hiredate,now())) '平均服务年限' from dept d left join emp e on d.deptno = e.deptno group by dname;+------------+----------+-------------+--------------+ | 部门 | 员工数量 | 平均薪资 | 平均服务年限 | +------------+----------+-------------+--------------+ | ACCOUNTING | 3 | 2916.666667 | 39.0000 | | RESEARCH | 5 | 2175.000000 | 36.8000 | | SALES | 6 | 1566.666667 | 39.3333 | | OPERATIONS | 0 | 0.000000 | NULL | +------------+----------+-------------+--------------+ -
-
列出所有员工的姓名、部门名称和工资
select ename,dname,sal from emp e join dept d on d.deptno=e.deptno; +--------+------------+---------+ | ename | dname | sal | +--------+------------+---------+ | SMITH | RESEARCH | 800.00 | | ALLEN | SALES | 1600.00 | | WARD | SALES | 1250.00 | | JONES | RESEARCH | 2975.00 | | MARTIN | SALES | 1250.00 | | BLAKE | SALES | 2850.00 | | CLARK | ACCOUNTING | 2450.00 | | SCOTT | RESEARCH | 3000.00 | | KING | ACCOUNTING | 5000.00 | | TURNER | SALES | 1500.00 | | ADAMS | RESEARCH | 1100.00 | | JAMES | SALES | 950.00 | | FORD | RESEARCH | 3000.00 | | MILLER | ACCOUNTING | 1300.00 | +--------+------------+---------+ -
列出所有部门的详细信息和人数
select d.*,count(ename) '人数' from dept d left join emp e on e.deptno=d.deptno group by deptno,dname,loc;+--------+------------+----------+------+ | DEPTNO | DNAME | LOC | 人数 | +--------+------------+----------+------+ | 10 | ACCOUNTING | NEW YORK | 3 | | 20 | RESEARCH | DALLAS | 5 | | 30 | SALES | CHICAGO | 6 | | 40 | OPERATIONS | BOSTON | 0 | +--------+------------+----------+------+ -
列出各种工作的最低工资及从事此工作的雇员姓名
--先找出每个工作以及最低工资 select job,min(sal) minsal from emp group by job; +-----------+---------+ | job | minsal | +-----------+---------+ | CLERK | 800.00 | | SALESMAN | 1250.00 | | MANAGER | 2450.00 | | ANALYST | 3000.00 | | PRESIDENT | 5000.00 | +-----------+---------+ --找出工资为最低工资的人的姓名-表连接 select ename,t.* from emp e join (select job,min(sal) minsal from emp group by job)t on t.job=e.job and e.sal = t.minsal;+--------+-----------+---------+ | ename | job | minsal | +--------+-----------+---------+ | SMITH | CLERK | 800.00 | | WARD | SALESMAN | 1250.00 | | MARTIN | SALESMAN | 1250.00 | | CLARK | MANAGER | 2450.00 | | SCOTT | ANALYST | 3000.00 | | KING | PRESIDENT | 5000.00 | | FORD | ANALYST | 3000.00 | +--------+-----------+---------+ -
列出各个部门的MANAGER(领导)的最低薪金
select deptno,min(sal) from emp where job='MANAGER' group by deptno; +--------+----------+ | deptno | min(sal) | +--------+----------+ | 20 | 2975.00 | | 30 | 2850.00 | | 10 | 2450.00 | +--------+----------+ -
列出所有员工的年工资,按年薪从低到高排序
select ename,sal*12 yearsal from emp order by yearsal; +--------+----------+ | ename | yearsal | +--------+----------+ | SMITH | 9600.00 | | JAMES | 11400.00 | | ADAMS | 13200.00 | | WARD | 15000.00 | | MARTIN | 15000.00 | | MILLER | 15600.00 | | TURNER | 18000.00 | | ALLEN | 19200.00 | | CLARK | 29400.00 | | BLAKE | 34200.00 | | JONES | 35700.00 | | SCOTT | 36000.00 | | FORD | 36000.00 | | KING | 60000.00 | +--------+----------+ -
求出员工领导的薪水超过3000的员工名称与领导名称
select e.ename '员工',f.ename '领导' from emp e join emp f on e.mgr = f.empno where f.sal>3000; +-------+------+ | 员工 | 领导 | +-------+------+ | JONES | KING | | BLAKE | KING | | CLARK | KING | +-------+------+ -
求出部门名称中,带’S’字符的部门员工的工资合计、部门人数
select dname,count(ename) '人数',ifnull(sum(sal),0) '工资总和' from emp e right join dept d on d.deptno =e.deptno where dname like '%S%' group by dname;+------------+------+----------+ | dname | 人数 | 工资总和 | +------------+------+----------+ | RESEARCH | 5 | 10875.00 | | SALES | 6 | 9400.00 | | OPERATIONS | 0 | 0.00 | +------------+------+----------+ -
给任职日期超过30年的员工加薪10%
--先找出任职日期超过30年的员工 update emp set sal =sal*1.1 where timestampdiff(year,hiredate,now())>30;
