SQL语句练习以及答案 (1)

作者：叁念

---

SQL语句练习以及答案

---

1.准备工作：

使用以下语句做好作业环境：

部门表dept（部门序号，部门名，部门位置）员工表emp（员工编号，名字，职业，直接上级编号，受雇日期，月薪，补贴，部门序号） --创建使用数据库： create database scott; use scott； --创建表格： CREATE TABLE DEPT  (DEPTNO INT,     DNAME VARCHAR(20),     LOC VARCHAR(20),     CONSTRAINT PK_DEPT PRIMARY KEY(DEPTNO));  CREATE TABLE EMP   (EMPNO int,     ENAME VARCHAR(20),     JOB VARCHAR(20),     MGR int,     HIREDATE DATE,     SAL int,     COMM int,     DEPTNO int,     CONSTRAINT PK_EMP PRIMARY KEY(EMPNO),    CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO)    REFERENCES DEPT (DEPTNO));--插入数据Insert into DEPT (DEPTNO,DNAME,LOC) values (10,'ACCOUNTING','NEW YORK');Insert into DEPT (DEPTNO,DNAME,LOC) values (20,'RESEARCH','DALLAS');Insert into DEPT (DEPTNO,DNAME,LOC) values (30,'SALES','CHICAGO');Insert into DEPT (DEPTNO,DNAME,LOC) values (40,'OPERATIONS','BOSTON');INSERT INTO `emp` VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20);INSERT INTO `emp` VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-2-20', 1600, 300, 30);INSERT INTO `emp` VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-2-22', 1250, 500, 30);INSERT INTO `emp` VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-4-2', 2975, NULL, 20);INSERT INTO `emp` VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-9-28', 1250, 1400, 30);INSERT INTO `emp` VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-5-1', 2850, NULL, 30);INSERT INTO `emp` VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-6-9', 2450, NULL, 10);INSERT INTO `emp` VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-4-19', 3000, NULL, 20);INSERT INTO `emp` VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);INSERT INTO `emp` VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-9-8', 1500, 0, 30);INSERT INTO `emp` VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-5-23', 1100, NULL, 20);INSERT INTO `emp` VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-3', 950, NULL, 30);INSERT INTO `emp` VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-3', 3000, NULL, 20);INSERT INTO `emp` VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-1-23', 1300, NULL, 10);

2.练习题以及答案：

--1.查询至少有一个员工的部门SELECT *FROM deptWHERE deptno IN (SELECT DEPTNO FROM emp);SELECT *FROM deptWHERE EXISTS (SELECT * FROM emp WHERE dept.deptno = emp.deptno)--2.查询薪水比SMITH高的员工信息SELECT * FROM empWHERE sal >(SELECT sal FROM emp WHERE ename = 'SMITH')--3.查询最低薪水低于1500的所有工作和最低薪水SELECT job,MIN(sal) FROM empWHERE MIN(sal) < 1500    *WHERE 字句中不能有聚合函数,应修改为如下：SELECT job,MIN(sal) FROM empWHERE sal < 1500GROUP BY job    *HAVING 字句中可以使用聚合函数,如下：SELECT job,MIN(sal)FROM empGROUP BY jobHAVING MIN(sal)<1500--4.查询部门编号为10号的员工信息SELECT * FROM empWHERE deptno = 10--5.查询工资在2000到3000之间的员工所有信息SELECT *FROM empWHERE sal BETWEEN 2000 AND 3000--6.查询有补贴的员工信息SELECT *FROM empWHERE comm IS NOT NULL--7.查询是否有员工叫SMITH,KING,OBAMA的？SELECT *FROM empWHERE ename IN ('SMITH','king','OBAMA')    *注意以上king小写也能查出，若要区分大小写，修改如下：SELECT *FROM empWHERE ename COLLATE utf8_binIN ('SMITH','king','OBAMA')--8.查询S开头的员工信息SELECT *FROM empWHERE ename LIKE 'S%'--9.查询第二个字母是A的员工信息SELECT *FROM empWHERE ename LIKE '_A%'--10.查询1982年之后入职的员工信息SELECT *FROM empWHERE hiredate > '1982'11.查询各部门的平均薪资SELECT AVG(sal)FROM empGROUP BY deptno+查询各部门的部门名和平均薪资SELECT dname,AVG(sal)FROM dept LEFT JOIN emp ON dept.deptno = emp.deptnoGROUP BY dept.deptno12.查询各部门各岗位的平均薪资和最高薪资SELECT deptno,job,AVG(sal),MAX(sal)FROM empGROUP BY deptno ,job查询各部门各岗位的部门名，岗位名，平均薪资和最高薪资SELECT dname,job,AVG(sal),MAX(sal)FROM dept LEFT JOIN emp ON dept.deptno = emp.deptnoGROUP BY dname,job13.查询各个部门经理的最低薪水SELECT  deptno, MIN(sal)FROM empWHERE job ='MANAGER'GROUP BY deptno14.查询所有员工的年薪，并按照年薪从高到低排序SELECT empno,sal*12+IFNULL (comm,0) AS 年薪FROM empORDER BY 年薪 DESC15.查询比30部门最高薪资的人薪资更高的所有员工信息SELECT *FROM empWHERE sal >(SELECT MAX(sal) FROM emp WHERE deptno = '30')查询比30部门所有人薪资都高员工信息SELECT *FROM empWHERE sal >ALL (SELECT sal FROM emp WHERE deptno = '30')16.查询每一个员工的姓名，职位以及领导的姓名SELECT a.ename,a.job,b.ename AS 领导FROM emp a LEFT JOIN emp b ON a.mgr = b.empnoSELECT a.ename,a.job,b.ename AS 领导FROM emp a,emp bWHERE a.mgr = b.empno17.查询部门号为10号的部门名，员工名。以及薪资SELECT dname,ename,salFROM emp,deptWHERE emp.deptno = dept.deptno AND emp.deptno = '10'18.查询在销售部工作的员工姓名SELECT enameFROM empWHERE deptno =(SELECT deptno FROM dept WHERE dname = 'SALES')19.查询ford的详细信息，以及领导姓名SELECT a.* ,b.ename 领导FROM emp a,emp bWHERE a.mgr = b.empnoAND a.ename = 'ford'20.查询薪水高于公司的平均水平的所有员工名字以及薪水SELECT ename ,salFROM empWHERE sal >(SELECT AVG(sal) FROM emp )1. 查询各个部门中工资最高的员工信息：名字、部门号、工资SELECT ename,deptno,salFROM empWHERE (sal,deptno) IN(SELECT MAX(sal),deptno FROM emp GROUP BY deptno)2. 查询部门名称和这些部门的员工信息，同时列出那些没有员工的部门，并根据部门号排序SELECT b.deptno,b.dname,a.*FROM dept b LEFT JOIN emp a ON a.deptno = b.deptnoORDER BY b.deptno ASC3. 查询各个管理者属下员工的最低工资，其中最低工资不能低于800，没有管理者的员工不计在内SELECT mgr 管理者,ename 员工,MIN(sal)FROM empWHERE sal >= 800 AND mgr IS NOT NULLGROUP BY mgr4. 哪个部门的平均工资是最高的，列出部门号和平均工资SELECT deptno ,AVG(sal) 平均FROM empGROUP BY deptnoORDER BY 平均 DESC LIMIT 0,1思路：1.求出所有部门的平均工资2.从所有的平均工资找到最高的平均工资3.百所有部门的平均工资和找出最高工资比较，相等的话取出部门ID和平均工资SELECT b.deptno,b.salFROM (SELECT deptno,AVG(sal) AS sal FROM emp GROUP BY deptno) AS bWHERE b.sal = (SELECT MAX(sal) FROM (SELECT AVG( sal) AS sal FROM emp GROUP BY deptno) AS a)