sql练习(针对Mysql)
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 ,dnamefrom 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 ddwhere 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 andhisal;
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 sswhere 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
320where sal between losal and hisal and grade = 2) maxsal from emp,salgrade ,dept where emp.deptno = dept.deptno and sal between
321losal 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, empwhere ename = 'smith' and sal between losal and ss.hisal )
335 --and sal > (select sal from em );
336select * from emp,salgrade where sal between losal and hisal and grade >(select grade from emp,salgrade where sal between losal
337and 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 +-------+--------+-----------+------+------------+------+------+--------+-------+-------+-------+
页:
[1]