SQL语句练习

首先建表

SET FOREIGN_KEY_CHECKS=0;
#建立数据表bonus
drop table if exists `bonus`;
create table `bonus`(
`ENAME` varchar(10) not null default '',
`JOB` varchar(9) default null,
`SAL` bigint(22) default null,
`COMM` bigint(22) default null,
PRIMARY KEY (`ENAME`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

#建立数据表dept
drop table if exists `dept`;
create table `dept`(
`DEPTNO` decimal(2,0) not null default '0',
`DNAME` varchar(14) default null,
`LOC` varchar(13) default null,
PRIMARY KEY (`DEPTNO`),
UNIQUE KEY `PK_DEPT` (`DEPTNO`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
#插入数据
insert into `dept` values('10','ACCOUNTING','NEW YORK');
insert into `dept` values('20','RESEARCH','DALLAS');    
insert into `dept` values('30','SALES','CHICAGO');
insert into `dept` values('40','OPERATIONS','BOSTON');

#建立数据表emp
drop table if exists `emp`;
create table `emp`(
`EMPNO` decimal(4,0) not null default '0',
`ENAME` varchar(10) default null,
`JOB` varchar(9) default null,
`MGR` decimal(4,0) default null,
`HIREDATE` datetime default null,
`SAL` decimal(7,2) default null,
`COMM` decimal(7,2) default null,
`DEPTNO` decimal(2,0) default null,
PRIMARY KEY (`EMPNO`),
UNIQUE KEY `PK_EMP` (`EMPNO`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
#插入数据
INSERT INTO `emp` VALUES ('7369', 'SMITH', 'CLERK', '7902', '1980-12-17 00:00:00', '800.00', null, '20');
INSERT INTO `emp` VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20 00:00:00', '1600.00', '300.00', '30');
INSERT INTO `emp` VALUES ('7521', 'WARD', 'SALESMAN', '7698', '1981-02-22 00:00:00', '1250.00', '500.00', '30');
INSERT INTO `emp` VALUES ('7566', 'JONES', 'MANAGER', '7839', '1981-04-02 00:00:00', '2975.00', null, '20');
INSERT INTO `emp` VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28 00:00:00', '1250.00', '1400.00', '30');
INSERT INTO `emp` VALUES ('7698', 'BLAKE', 'MANAGER', '7839', '1981-05-01 00:00:00', '2850.00', null, '30');
INSERT INTO `emp` VALUES ('7782', 'CLARK', 'MANAGER', '7839', '1981-06-09 00:00:00', '2450.00', null, '10');
INSERT INTO `emp` VALUES ('7788', 'SCOTT', 'ANALYST', '7566', '1987-04-19 00:00:00', '3000.00', null, '20');
INSERT INTO `emp` VALUES ('7839', 'KING', 'PRESIDENT', null, '1981-11-17 00:00:00', '5000.00', null, '10');
INSERT INTO `emp` VALUES ('7844', 'TURNER', 'SALESMAN', '7698', '1981-09-08 00:00:00', '1500.00', '0.00', '30');
INSERT INTO `emp` VALUES ('7876', 'ADAMS', 'CLERK', '7788', '1987-05-23 00:00:00', '1100.00', null, '20');
INSERT INTO `emp` VALUES ('7900', 'JAMES', 'CLERK', '7698', '1981-12-03 00:00:00', '950.00', null, '30');
INSERT INTO `emp` VALUES ('7902', 'FORD', 'ANALYST', '7566', '1981-12-03 00:00:00', '3000.00', null, '20');
INSERT INTO `emp` VALUES ('7934', 'MILLER', 'CLERK', '7782', '1982-01-23 00:00:00', '1300.00', null, '10');

#建立数据表 salgrade
drop table if exists `salgrade`;
create table `salgrade`(
`GRADE` bigint(22) not null default '0',
`LOSAL` bigint(22) default null,
`HISAL` bigint(22) default null,
PRIMARY KEY (`GRADE`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
#插入数据
insert into `salgrade` values ('1','700','1200');
insert into `salgrade` values('2','1201','1400');
insert into `salgrade` values('3','1401','2000');
insert into `salgrade` values('4','2001','3000');
insert into `salgrade` values('5','3001','9999');

建立的表如下

mysql> show tables;
+---------------------+
| Tables_in_couchbase |
+---------------------+
| bonus               |
| dept                |
| emp                 |
| salgrade            |
+---------------------+

mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

mysql> select * from emp;
+-------+--------+-----------+------+---------------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE            | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+---------------------+---------+---------+--------+

mysql> select * from salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

列出至少有一个员工的所有部门的部门编号，部门名称

select dept.deptno,dname from emp,dept where dept.deptno=emp.deptno;
select emp.deptno,dname from emp left join dept on dept.deptno=emp.deptno;

列出薪资比"SMITH"多的所有员工的编号和名称

select empno,ename from emp where sal>(select sal from emp where ename='SMITH');

列出所有员工的姓名和其直接上级的姓名

select table1.ename,table2.ename from emp table1 left join emp table2 on table1.mgr=table2.empno;

列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称

select emp1.empno,emp1.ename,dname from emp emp1 
join emp emp2 on emp1.mgr=emp2.empno and emp1.hiredate<emp2.hiredate
join dept on emp1.deptno=dept.deptno;

列出部门名称和这些部门员工的所有信息，同时列出那些没有员工的部门

select dname,emp.* from dept left join emp on emp.deptno=dept.deptno;

列出所有"CLERK"职位的人名，部门名，以及部门人数

select ename,dname,cou from emp
join dept on dept.deptno=emp.deptno and job='CLERK'
join (select count(*) cou,deptno from emp group by deptno) table1 on table1.deptno=emp.deptno;

列出最低薪资大于1500的各种工作和从事此工作的人数

select job,count(empno) from emp group by job having min(sal)>1500;

列出非主管的工作以及各个工作的平均薪资

select job,avg(sal) from emp where job!='PRESIDENT' group by job;

列出工作为"CLERK"的员工的姓名

select ename from emp where job='CLERK';

列出在部门“SALES”（销售部）工作的员工的姓名

select ename from emp join dept on dept.deptno=emp.deptno and dname='SALES';

列出薪金高于公司平均薪金的所有员工的名字，所在部门，上级领导的名字，公司的工资等级

select table1.ename,dname, emp.ename manager, grade  from
(select ename,sal,deptno,mgr from emp where sal>(select avg(sal) from emp))table1
left join dept on table1.deptno=dept.DEPTNO
left join emp on table1.mgr =emp.empno
left join salgrade on table1.sal >= losal and table1.sal<=hisal;

列出与“SCOTT”从事相同工作的所有员工的名字及部门名称

select table1.ename,dname from dept join
(select ename,deptno from emp where job=(select job from emp where ename='SCOTT') and ename!='SCOTT') table1
on table1.deptno=dept.deptno;

列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金

select ename,sal from emp where sal in (select sal from emp where deptno=30);

列出薪金高于在部门30工作的所有员工的薪金的员工和薪金、部门名称。

select ename,sal,dname from emp join dept on emp.deptno=dept.deptno where sal>(select max(sal) from emp where deptno=30);

列出每个部门工作的部门名称、员工的数量、平均工资和平均服务期限。

select deptno,count(empno),avg(sal),avg(SYSDATE()-hiredate) hiretime from emp group by deptno;

列出所有部门的名称和部门人数。

select dname,table1.ce from (select deptno,count(empno) ce from emp group by deptno)table1 left join dept on table1.deptno=dept.deptno;

列出各种工作的名称，最低工资及从事此工作的雇员姓名。
先列举出一种错误做法

select empno,sal,job from emp order by job;
+-------+---------+-----------+
| empno | sal     | job       |
+-------+---------+-----------+
|  7788 | 3000.00 | ANALYST   |
|  7902 | 3000.00 | ANALYST   |
|  7369 |  800.00 | CLERK     |
|  7876 | 1100.00 | CLERK     |
|  7900 |  950.00 | CLERK     |
|  7934 | 1300.00 | CLERK     |
|  7566 | 2975.00 | MANAGER   |
|  7698 | 2850.00 | MANAGER   |
|  7782 | 2450.00 | MANAGER   |
|  7839 | 5000.00 | PRESIDENT |
|  7499 | 1600.00 | SALESMAN  |
|  7521 | 1250.00 | SALESMAN  |
|  7654 | 1250.00 | SALESMAN  |
|  7844 | 1500.00 | SALESMAN  |
+-------+---------+-----------+
select empno,min(sal),job from emp group by job;
+-------+----------+-----------+
| empno | min(sal) | job       |
+-------+----------+-----------+
|  7788 |  3000.00 | ANALYST   |
|  7369 |   800.00 | CLERK     |
|  7566 |  2450.00 | MANAGER   |
|  7839 |  5000.00 | PRESIDENT |
|  7499 |  1250.00 | SALESMAN  |
+-------+----------+-----------+
select emp.empno,sal from (select empno,min(sal) from emp group by job)table1 join emp on table1.empno=emp.empno;
+-------+---------+
| empno | sal     |
+-------+---------+
|  7788 | 3000.00 |
|  7369 |  800.00 |
|  7566 | 2975.00 |
|  7839 | 5000.00 |
|  7499 | 1600.00 |
+-------+---------+

下面是正确的做法

SELECT ename,sal,job from emp where (sal ,job) in(select min(sal),job from emp GROUP BY job);
+--------+---------+-----------+
| ename  | sal     | job       |
+--------+---------+-----------+
| SMITH  |  800.00 | CLERK     |
| WARD   | 1250.00 | SALESMAN  |
| MARTIN | 1250.00 | SALESMAN  |
| CLARK  | 2450.00 | MANAGER   |
| SCOTT  | 3000.00 | ANALYST   |
| KING   | 5000.00 | PRESIDENT |
| FORD   | 3000.00 | ANALYST   |
+--------+---------+-----------+

列出各个部门的MANAGER(经理)的最低薪金及其名字，工作，部门编号

select ename,sal,job,deptno from emp where (job,sal) in (select job,min(sal) from emp where job='MANAGER' GROUP BY deptno );

列出平均薪资最高的部门及其薪资
错误做法

select deptno,avg(sal) avs from emp group by deptno
having  avs= (select max(table1.avgsal) from (select avg(sal) avgsal from emp group by deptno)table1);
Empty set (0.00 sec)

正确做法1

select deptno,avg(sal) avs from emp group by deptno 
having  ABS(avs- (select max(table1.avgsal) from (select avg(sal) avgsal from emp group by deptno)table1))<0.001;

正确做法2

select deptno,avg(sal) avs from emp group by deptno limit 1;

列出所有员工的年工资，按年薪从低到高排序

select sal*12 ,ename from emp group by sal;

查出某个员工的上级主管，并要求出这些主管中的薪水超过3000

select emp1.ename,emp2.ename from emp emp1 join emp emp2 on emp1.mgr=emp2.empno and emp2.sal>3000;

最后编辑于：2017.12.11 06:56:05

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 159,015评论 4赞 362
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 67,262评论 1赞 292
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 108,727评论 0赞 243
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 43,986评论 0赞 205
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 52,363评论 3赞 287
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 40,610评论 1赞 219
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 31,871评论 2赞 312
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 30,582评论 0赞 198
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 34,297评论 1赞 242
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 30,551评论 2赞 246
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 32,053评论 1赞 260
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 28,385评论 2赞 253
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 33,035评论 3赞 236
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 26,079评论 0赞 8
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 26,841评论 0赞 195
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 35,648评论 2赞 274
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 35,550评论 2赞 270

SQL语句练习

推荐阅读更多精彩内容