sql练习(针对Mysql)

191145685

1 //多表与分组练习　　

  2　　
  3 1、返回拥有员工的部门名、部门号。
　　
  4 --不知道这个具体干什么的.通过看部门dept表和emp表可以看出来,员工并不是都是在dept表中存在的地方的.
　　
  5 --dept表中可能有四个部门号,但是员工emp表中可能只占其中的三个部门号码
　　
  6 --题干的需求就是干这个的.(即有的部门可能没有"员工")
　　
  7 select dname,deptno from dept where deptno in (select deptno from emp);
　　
  8 +------------+--------+
　　
  9 | dname      | deptno |
　　
10 +------------+--------+
　　
11 | ACCOUNTING |     10 |
　　
12 | RESEARCH   |     20 |
　　
13 | SALES      |     30 |
　　
14 +------------+--------+
　　
15
　　
16
　　
17 2、工资水平多于smith的员工信息。
　　
18 select * from emp where sal>(select sal from emp where ename = 'smith');
　　
19
　　
20     
　　
21 3、返回员工和所属经理的姓名。
　　
22 --这个感觉很简单就是没有想出来怎么办,看答案才理解了
　　
23 --复用表emp,再加上使用外连接.
　　
24
　　
25 --select ee.ename as eename ,mm.ename as mename from emp ee left join emp mm where ee.mgr=mm.empno;
　　
26 --看完答案理解了之后也写错了,注意外连接是left/right join on
　　
27 select ee.ename as eename ,mm.ename as mename from emp ee left join emp mm on ee.mgr=mm.empno;
　　
28
　　
29 --现在左外连接还有另外的一种写法,就是用加号"+";仔细查了一下,并且纳闷为什么＋的SQL语句外连接在navicate中没有效果.
　　
30 --得知加号的外连接只有在oracle中才是用.
　　
31
　　
32
　　
33 +--------+--------+
　　
34 | eename | mename |
　　
35 +--------+--------+
　　
36 | SMITH  | FORD   |
　　
37 | ALLEN  | BLAKE  |
　　
38 | WARD   | BLAKE  |
　　
39 | JONES  | KING   |
　　
40 | MARTIN | BLAKE  |
　　
41 | BLAKE  | KING   |
　　
42 | CLARK  | KING   |
　　
43 | SCOTT  | JONES  |
　　
44 | KING   | NULL   |
　　
45 | TURNER | BLAKE  |
　　
46 | ADAMS  | SCOTT  |
　　
47 | JAMES  | BLAKE  |
　　
48 | FORD   | JONES  |
　　
49 | MILLER | CLARK  |
　　
50
　　
51
　　
52      
　　
53 4、返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名。
　　
54 --感觉这个也是要复用emp表,确实复用emp表
　　
55  --select ee.ename as eename,mm.ename as mename from emp ee,emp mm where ee.hiredate<mm.hiredate;
　　
56 --这句话没有加上是其经理的条件
　　
57 --select ee.ename as eename,mm.ename as mename from emp ee left join emp mm on ee.mgr = mm.empno and ee.hiredate < mm.hiredate;
　　
58 --上面这句仅仅是把第三句中加上了日期小于的条件.
　　
59 select ee.ename as eename,mm.ename as mename from emp ee ,emp mm where ee.mgr = mm.empno and ee.hiredate < mm.hiredate;
　　
60 --这句为什么不用left join? 第三句用where可不可以?   注意时间老的日期大小是小于时间老的日期的.
　　
61 (select ee.ename as eename ,mm.ename as mename from emp ee left join emp mm where ee.mgr=mm.empno;)第三句的写法.
　　
62
　　
63
　　
64 5、返回员工姓名及其所在的部门名称。
　　
65 --这句有点像第三句,不过这个牵扯到了两个表
　　
66 --select ee.ename as ename, ee.deptno as dptname from emp ee, dept dd where ee.deptno=dd.deptno;
　　
67 --最开始写的,写错了.还是没有把最基本的需求高清,人家要部门名.
　　
68
　　
69 select ename as ename ,dname  from emp ee,dept dd where ee.deptno=dd.deptno;
　　
70 +--------+------------+
　　
71 | ename  | dname      |
　　
72 +--------+------------+
　　
73 | CLARK  | ACCOUNTING |
　　
74 | KING   | ACCOUNTING |
　　
75 | MILLER | ACCOUNTING |
　　
76 | SMITH  | RESEARCH   |
　　
77 | JONES  | RESEARCH   |
　　
78 | SCOTT  | RESEARCH   |
　　
79 | ADAMS  | RESEARCH   |
　　
80 | FORD   | RESEARCH   |
　　
81 | ALLEN  | SALES      |
　　
82 | WARD   | SALES      |
　　
83 | MARTIN | SALES      |
　　
84 | BLAKE  | SALES      |
　　
85 | TURNER | SALES      |
　　
86 | JAMES  | SALES      |
　　
87 +--------+------------+
　　
88
　　
89
　　
90 6、返回从事clerk工作的员工姓名和所在部门名称。
　　
91 select ename ,dname from emp ee, dept dd where ee.job='clerk' and ee.deptno = dd.deptno;
　　
92 +--------+------------+
　　
93 | ename  | dname      |
　　
94 +--------+------------+
　　
95 | MILLER | ACCOUNTING |
　　
96 | SMITH  | RESEARCH   |
　　
97 | ADAMS  | RESEARCH   |
　　
98 | JAMES  | SALES      |
　　
99 +--------+------------+
　　
100
　　
101
　　
102 7、返回部门号及其本部门的最低工资。
　　
103 --这个语句不用where条件进行过滤.
　　
104 --找出部门的最低工资 ----想不起来.提示一下用到了min()函数.
　　
105 --select min(sal) from emp ;
　　
106 --select deptno , min(sal) from deptno ,emp
　　
107 --首先想需求中要用到哪几个表.然后再写.
　　
108 --因为是按照部门为单位的所以用到分组
　　
109 select deptno ,min(sal) from emp group by deptno;
　　
110 +--------+----------+
　　
111 | deptno | min(sal) |
　　
112 +--------+----------+
　　
113 |     10 |     1300 |
　　
114 |     20 |      800 |
　　
115 |     30 |      950 |
　　
116 +--------+----------+
　　
117
　　
118 8、返回销售部(sales)所有员工的姓名。
　　
119 --根据需求用到了emp和dept两个表
　　
120 select ename from emp ee,dept dd where dd.dname='sales' and ee.deptno = (select deptno from dept where dname = 'sales');
　　
121 +--------+
　　
122 | ename  |
　　
123 +--------+
　　
124 | ALLEN  |
　　
125 | WARD   |
　　
126 | MARTIN |
　　
127 | BLAKE  |
　　
128 | TURNER |
　　
129 | JAMES  |
　　
130 +--------+
　　
131
　　
132
　　
133 9、返回工资水平多于平均工资的员工。
　　
134 --这个用到了avg()函数. 用到表emp
　　
135 --select * from emp where sal > avg(sal);
　　
136 --一开始这样写的,但是报错,提示信息为:ERROR 1111 : Invalid use of group function
　　
137 --在这种情况下要把聚合函数放在select语句中
　　
138 select * from emp where sal > (select avg(sal) from emp);
　　
139 +-------+-------+-----------+------+------------+------+------+--------+
　　
140 | EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL  | COMM | DEPTNO |
　　
141 +-------+-------+-----------+------+------------+------+------+--------+
　　
142 |  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975 | NULL |     20 |
　　
143 |  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850 | NULL |     30 |
　　
144 |  7782 | CLARK | MANAGER   | 7839 | 1981-06-09 | 2450 | NULL |     10 |
　　
145 |  7788 | SCOTT | ANALYST   | 7566 | 1987-07-03 | 3000 | NULL |     20 |
　　
146 |  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL |     10 |
　　
147 |  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000 | NULL |     20 |
　　
148 +-------+-------+-----------+------+------------+------+------+--------+
　　
149
　　
150 10、返回与SCOTT从事相同工作的员工。
　　
151 --只用到表emp
　　
152 --select * from emp where job = '(select job from emp where ename = 'scott')';
　　
153 --上一句在语法上是没有问题的,但是有个小问题就是job是varchar字符串类型的,但是在其后是select语句
　　
154 -- 的时候其后是不能加上单引号,或者是单引号的,否则都会报错的.
　　
155 --还要把搜索到的结果去掉scott他本身这条记录.
　　
156
　　
157 select * from emp where job = (select job from emp where ename = 'scott') and ename<> 'scott';
　　
158
　　
159
　　
160
　　
161 11、返回与30部门员工工资水平相同的员工姓名与工资。
　　
162 --这个也只用到了emp表
　　
163
　　
164 select ename ,sal from emp where sal in( select sal from emp where deptno = 30);
　　
165 +--------+------+
　　
166 | ename  | sal  |
　　
167 +--------+------+
　　
168 | ALLEN  | 1600 |
　　
169 | WARD   | 1250 |
　　
170 | MARTIN | 1250 |
　　
171 | BLAKE  | 2850 |
　　
172 | TURNER | 1500 |
　　
173 | JAMES  |  950 |
　　
174 +--------+------+
　　
175
　　
176
　　
177
　　
178 12、返回工资高于30部门所有员工工资水平的员工信息。
　　
179 --从题意中就可以看出要使用到关键字all 也只用到了emp表
　　
180 --all关键字怎么使用.
　　
181 select * from emp where sal>all(select sal from emp where deptno = 30);
　　
182 +-------+-------+-----------+------+------------+------+------+--------+
　　
183 | EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL  | COMM | DEPTNO |
　　
184 +-------+-------+-----------+------+------------+------+------+--------+
　　
185 |  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975 | NULL |     20 |
　　
186 |  7788 | SCOTT | ANALYST   | 7566 | 1987-07-03 | 3000 | NULL |     20 |
　　
187 |  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL |     10 |
　　
188 |  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000 | NULL |     20 |
　　
189 +-------+-------+-----------+------+------------+------+------+--------+
　　
190
　　
191
　　
192 13、返回部门号、部门名、部门所在位置及其每个部门的员工总数。   
　　
193 --需要用到emp和dept两个表
　　
194 select deptno ,dname ,loc ,count(*) from emp ee,dept dd  where ee.deptno = dd.deptno
　　
195
　　
196 14、返回员工的姓名、所在部门名及其工资。
　　
197 --需要用到emp表和dept表.
　　
198 select ee.ename , dd.dname ,ee.sal from emp ee,dept dd where ee.deptno = dd.deptno;
　　
199 +--------+------------+------+
　　
200 | ename  | dname      | sal  |
　　
201 +--------+------------+------+
　　
202 | CLARK  | ACCOUNTING | 2450 |
　　
203 | KING   | ACCOUNTING | 5000 |
　　
204 | MILLER | ACCOUNTING | 1300 |
　　
205 | SMITH  | RESEARCH   |  800 |
　　
206 | JONES  | RESEARCH   | 2975 |
　　
207 | SCOTT  | RESEARCH   | 3000 |
　　
208 | ADAMS  | RESEARCH   | 1100 |
　　
209 | FORD   | RESEARCH   | 3000 |
　　
210 | ALLEN  | SALES      | 1600 |
　　
211 | WARD   | SALES      | 1250 |
　　
212 | MARTIN | SALES      | 1250 |
　　
213 | BLAKE  | SALES      | 2850 |
　　
214 | TURNER | SALES      | 1500 |
　　
215 | JAMES  | SALES      |  950 |
　　
216 +--------+------------+------+
　　
217
　　
218 15、返回员工的详细信息。(包括部门名)
　　
219 --需要用到两个表emp和dept
　　
220 --在用select * 的基础上还要加上部门名这一列,这咋办????
　　
221 --看了答案明白了既然是还要加上部门名并且是详细信息,那么就对这三个表都select* 操作
　　
222 --select * from emp ee,dept dd,salgrade ss where ee.deptno = dd.deptno;
　　
223 --一开始没有加上工资的限制.
　　
224 select * from emp ee,dept dd,salgrade ss where ee.deptno = dd.deptno and sal between losal and hisal;
　　
225
　　
226 +-------+--------+-----------+------+------------+------+------+--------+--------+------------+----------+-------+-------+-------+
　　
227 | EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL  | COMM | DEPTNO | DEPTNO | DNAME      | LOC      | GRADE | LOSAL | HISAL |
　　
228 +-------+--------+-----------+------+------------+------+------+--------+--------+------------+----------+-------+-------+-------+
　　
229 |  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800 | NULL |     20 |     20 | RESEARCH   | DALLAS   |     1 |   700 |  1200 |
　　
230 |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600 |  300 |     30 |     30 | SALES      | CHICAGO  |     3 |  1401 |  2000 |
　　
231 |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250 |  500 |     30 |     30 | SALES      | CHICAGO  |     2 |  1201 |  1400 |
　　
232 |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975 | NULL |     20 |     20 | RESEARCH   | DALLAS   |     4 |  2001 |  3000 |
　　
233 |  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250 | 1400 |     30 |     30 | SALES      | CHICAGO  |     2 |  1201 |  1400 |
　　
234 |  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850 | NULL |     30 |     30 | SALES      | CHICAGO  |     4 |  2001 |  3000 |
　　
235 |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450 | NULL |     10 |     10 | ACCOUNTING | NEW YORK |     4 |  2001 |  3000 |
　　
236 |  7788 | SCOTT  | ANALYST   | 7566 | 1987-07-03 | 3000 | NULL |     20 |     20 | RESEARCH   | DALLAS   |     4 |  2001 |  3000 |
　　
237 |  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL |     10 |     10 | ACCOUNTING | NEW YORK |     5 |  3001 |  9999 |
　　
238 |  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500 |    0 |     30 |     30 | SALES      | CHICAGO  |     3 |  1401 |  2000 |
　　
239 |  7876 | ADAMS  | CLERK     | 7788 | 1987-07-13 | 1100 | NULL |     20 |     20 | RESEARCH   | DALLAS   |     1 |   700 |  1200 |
　　
240 |  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950 | NULL |     30 |     30 | SALES      | CHICAGO  |     1 |   700 |  1200 |
　　
241 |  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000 | NULL |     20 |     20 | RESEARCH   | DALLAS   |     4 |  2001 |  3000 |
　　
242 |  7934 | MILLER | CLERK     | 7782 | 1981-01-23 | 1300 | NULL |     10 |     10 | ACCOUNTING | NEW YORK |     2 |  1201 |  1400 |
　　
243 +-------+--------+-----------+------+------------+------+------+--------+--------+------------+----------+-------+-------+-------+
　　
244
　　
245 16、返回员工工作及其从事此工作的最低工资。
　　
246 --需要用到表emp,很明显需要 用到分组group by.
　　
247 select job ,min(sal) from emp group by job;
　　
248 --原来可以在from前面直接用聚合函数.
　　
249 +-----------+----------+
　　
250 | job       | min(sal) |
　　
251 +-----------+----------+
　　
252 | ANALYST   |     3000 |
　　
253 | CLERK     |      800 |
　　
254 | MANAGER   |     2450 |
　　
255 | PRESIDENT |     5000 |
　　
256 | SALESMAN  |     1250 |
　　
257 +-----------+----------+
　　
258 17、返回不同部门经理的最低工资。
　　
259 --需要用到一个表emp,需要用到group by
　　
260 select deptno , min(sal) from emp where job = 'manager' group by deptno;
　　
261 +--------+----------+
　　
262 | deptno | min(sal) |
　　
263 +--------+----------+
　　
264 |     10 |     2450 |
　　
265 |     20 |     2975 |
　　
266 |     30 |     2850 |
　　
267 +--------+----------+
　　
268
　　
269
　　
270
　　
271 18、计算出员工的年薪，并且以年薪排序。
　　
272 --需要用到一个表emp
　　
273 select sal*12 as ysal from emp order by ysal asc;
　　
274 --上面的语句没有问题,关键是年薪的计算方法,这里还牵扯到奖金的问题.
　　
275
　　
276
　　
277
　　
278 19、返回工资处于第四级别的员工的姓名。
　　
279 --这里需要两个表,一个是emp一个是salgrade
　　
280 --select ename form emp ee,salgrade where grade =4;
　　
281 --这句话仔细一想就可以看出不会有任何结果,where过滤不掉任何东西
　　
282
　　
283 select ename from emp ,salgrade where grade =4 and sal>=losal and sal <= hisal;
　　
284 select ename from emp ,salgrade where grade =4 and sal between losal and  hisal;
　　
285 +-------+
　　
286 | ename |
　　
287 +-------+
　　
288 | JONES |
　　
289 | BLAKE |
　　
290 | CLARK |
　　
291 | SCOTT |
　　
292 | FORD  |
　　
293 +-------+
　　
294
　　
295 20、返回工资为二等级的职员名字、部门所在地、和二等级的最低工资和最高工资
　　
296 --从题意中可知需要用到三个表 emp dept salgrade
　　
297 --二等级的最低工资和最高工资.并不是二等级员工的工资.这个是和第21问的区别.
　　
298 --select ename ,loc ,min(sal) ,max(sal) from emp ee,dept dd, salgrade ss  where grade = 2 and sal between losal and hisal;
　　
299 --二等级的最低工资和最高工资并不是二等级员工的最低工资和最高工资.
　　
300
　　
301 select ename ,loc ,losal,hisal from emp ee,dept dd,salgrade ss where grade = 2 and sal between losal and hisal;
　　
302
　　
303
　　
304 21、返回工资为二等级的职员名字、部门所在地、二等级员工工资的最低工资和最高工资
　　
305 --需要用到salgrade , dept表和emp表
　　
306 mysql> select ename , loc ,min(sal) ,max(sal) from emp ee, dept dd, salgrade ss where grade = 2 and sal between losal and hisal;
　　
307 +-------+----------+----------+----------+
　　
308 | ename | loc      | min(sal) | max(sal) |
　　
309 +-------+----------+----------+----------+
　　
310 | WARD  | NEW YORK |     1250 |     1300 |
　　
311 +-------+----------+----------+----------+
　　
312
　　
313 --这样写是错误的.到底在select后加上聚合函数的应用?????
　　
314
　　
315 --select e.ename,loc,max(m.sal) ,min(m.sal),grade from emp e,emp m ,dept salgrade where e.deptno=dept.deptno and e.sal beteeen losal and hissal
　　
316 --m.sal between losal and hisal and grade = 2 group by e.ename , loc, grade;
　　
317
　　
318
　　
319 select ename , loc ,(select min(sal) from emp,salgrade where sal between losal and hisal and grade = 2) minsal,(select max(sal) from emp,salgrade
　　
320  where sal between losal and hisal and grade = 2) maxsal from emp,salgrade ,dept where emp.deptno = dept.deptno and sal between
　　
321  losal and hisal and grade = 2;
　　
322 +--------+----------+--------+--------+
　　
323 | ename  | loc      | minsal | maxsal |
　　
324 +--------+----------+--------+--------+
　　
325 | WARD   | CHICAGO  |   1250 |   1300 |
　　
326 | MARTIN | CHICAGO  |   1250 |   1300 |
　　
327 | MILLER | NEW YORK |   1250 |   1300 |
　　
328 +--------+----------+--------+--------+
　　
329
　　
330 22.工资等级多于smith的员工信息。
　　
331 --获得员工信息,三个表中有的信息都获取出来,就 用到三个表.
　　
332 --需要用到两个表 emp表和salgrade 表
　　
333 --select * from emp,salgrade where grade >(select sal from emp,salgrade where sal between losal and hisal);
　　
334 --select * from emp ee,salgrade ss,dept dd where grade > (select grade from salgrade, emp  where ename = 'smith' and sal between losal and ss.hisal )
　　
335 --and sal > (select sal from em );
　　
336  select * from emp,salgrade where sal between losal and hisal and grade >(select grade from emp,salgrade where sal between losal
　　
337  and hisal and ename = 'smith');
　　
338 +-------+--------+-----------+------+------------+------+------+--------+-------+-------+-------+
　　
339 | EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL  | COMM | DEPTNO | GRADE | LOSAL | HISAL |
　　
340 +-------+--------+-----------+------+------------+------+------+--------+-------+-------+-------+
　　
341 |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600 |  300 |     30 |     3 |  1401 |  2000 |
　　
342 |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250 |  500 |     30 |     2 |  1201 |  1400 |
　　
343 |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975 | NULL |     20 |     4 |  2001 |  3000 |
　　
344 |  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250 | 1400 |     30 |     2 |  1201 |  1400 |
　　
345 |  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850 | NULL |     30 |     4 |  2001 |  3000 |
　　
346 |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450 | NULL |     10 |     4 |  2001 |  3000 |
　　
347 |  7788 | SCOTT  | ANALYST   | 7566 | 1987-07-03 | 3000 | NULL |     20 |     4 |  2001 |  3000 |
　　
348 |  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL |     10 |     5 |  3001 |  9999 |
　　
349 |  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500 |    0 |     30 |     3 |  1401 |  2000 |
　　
350 |  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000 | NULL |     20 |     4 |  2001 |  3000 |
　　
351 |  7934 | MILLER | CLERK     | 7782 | 1981-01-23 | 1300 | NULL |     10 |     2 |  1201 |  1400 |
　　
352 +-------+--------+-----------+------+------------+------+------+--------+-------+-------+-------+