Home > Database > Mysql Tutorial > 子查询以及sql练习

子查询以及sql练习

不言
Release: 2018-05-21 10:25:54
Original
2079 people have browsed it

无详细内容 无 --1列出emp表中各部门的部门号,最高工资,最低工资--先分组,后计算,SELECT e.deptno AS 部门号, MAX(e.sal) AS 最高工资, MIN(e.sal) AS 最低工资 FROM emp AS e GROUP BY e.deptno;--2 列出emp表中各部门job为'CLERK'的员工的最低工资,最

<无详细内容> <无>

--1列出emp表中各部门的部门号,最高工资,最低工资
--先分组,后计算,

SELECT e.deptno AS 部门号, MAX(e.sal)  AS 最高工资, MIN(e.sal) AS 最低工资 FROM emp AS e GROUP BY e.deptno;

--2 列出emp表中各部门job为&#39;CLERK&#39;的员工的最低工资,最高工资

SELECT MIN(e.sal) AS 最低工资 , MAX(e.sal) FROM emp AS e WHERE e.job=&#39;CLERK&#39; GROUP BY e.deptno;

--3 对于emp中最低工资小于2000的部门,列出job为&#39;CLERK&#39;的员工的部门号,最低工资,最高工资

SELECT e.deptno AS 部门号, MIN(e.sal) AS 最低工资 , MAX(e.sal) AS 最高工资 FROM emp AS e WHERE  e.job =&#39;CLERK&#39; AND (SELECT MIN(b.sal) FROM emp b) <2000 GROUP BY e.deptno;

--4 根据部门号由高而低,工资有低而高列出每个员工的姓名,部门号,工资

SELECT e.ename AS 姓名 ,e.deptno AS 部门号, e.sal AS 工资 FROM emp AS e ORDER BY e.deptno DESC ,e.sal 

--5 列出&#39;buddy&#39;所在部门中每个员工的姓名与部门号

SELECT e.ename AS 姓名, e.deptno AS 部门号 FROM emp AS e WHERE e.deptno =(SELECT b.deptno  FROM  emp  AS b WHERE b.ename=&#39;buddy&#39;);

--6 列出每个员工的姓名,工作,部门号,部门名

SELECT e.ename 姓名, e.job AS 工作 , e.deptno AS 部门号 FROM emp AS e , dept AS d WHERE e.deptno=d.deptno  ;

--7列出emp中工作为&#39;CLERK&#39;的员工的姓名,工作,部门号,部门名

SELECT e.ename AS 姓名, e.job AS 工作 ,d.dname AS 部门名 FROM emp AS e , dept AS d WHERE e.deptno =d.deptno AND e.job=&#39;CLERK&#39;;

--8对于emp中有管理者的员工,列出姓名,管理者姓名(管理者外键为mgr)

SELECT a.deptno AS 部门号,a.ename AS 员工,b.ename AS 管理者 FROM emp AS a,emp AS b WHERE a.mgr IS NOT NULL AND a.mgr=b.ename;

--9 对于dept表中,列出所有部门名,部门号,同时列出各部门工作为&#39;CLERK&#39;的员工名与工作

SELECT d.dname AS 部门名, d.deptno AS 部门号 ,e.ename AS 姓名 , e.job AS 工作  FROM  dept AS d ,emp AS e WHERE e.deptno =d.deptno AND e.job=&#39;CLERK&#39;;

--10 对于工资高于本部门平均水平的员工,列出部门号,姓名,工资,按部门号排序

SELECT e.deptno AS 部门号,e.ename AS 姓名, e.sal AS 工资 FROM emp AS e WHERE e.sal >(SELECT AVG(el.sal) FROM emp AS el WHERE el.deptno=e.deptno) ORDER BY e.deptno;

--11对于emp,列出各个部门中工资高于本部门平均工资的员工数和部门号,按部门号排序
 
SELECT COUNT(e.sal) AS 员工数,e.deptno AS 部门号 FROM emp AS e WHERE e.sal >(SELECT AVG(el.sal) FROM emp AS el WHERE e.deptno =el.deptno) GROUP BY e.deptno ORDER BY e.deptno;

--12对于emp中工资高于本部门平均水平,人数多与1人的,列出部门号,人数,平均工资,按部门号排序

SELECT COUNT(a.empno) AS 员工数,a.deptno AS 部门号,AVG(sal) AS 平均工资
FROM emp AS a WHERE (SELECT COUNT(c.empno) FROM emp AS c WHERE c.deptno=a.deptno AND
c.sal>(SELECT AVG(sal) FROM emp AS b WHERE c.deptno=b.deptno))>1
GROUP BY a.deptno ORDER BY a.deptno;
Copy after login


source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template