sql语句练习题或面试题

绝地灬酷狼 2022-08-08 06:40 293阅读 0赞

<table style="background:#B3B3B3"> 
 <tbody> 
 <tr> 
 <td> 根据下面表的描述，创建DEPT和EMP表，并运行下列SQL，最终完成后面的练习 </td> 
 </tr> 
 </tbody> 
</table>

**1、   部门表：DEPT**

**2、   雇员表：EMP**

<table> 
 <tbody> 
 <tr> 
 <td style="background:#E6E6E6"> No. </td> 
 <td style="background:#E6E6E6"> 字段名称 </td> 
 <td style="background:#E6E6E6"> 类型 </td> 
 <td style="background:#E6E6E6"> 描述 </td> 
 </tr> 
 <tr> 
 <td> 1 </td> 
 <td> EMPNO </td> 
 <td> BIGINT </td> 
 <td> 雇员编号，由四位数字所组成(主键) </td> 
 </tr> 
 <tr> 
 <td> 2 </td> 
 <td> ENAME </td> 
 <td> VARCHAR(20) </td> 
 <td> 雇员姓名 </td> 
 </tr> 
 <tr> 
 <td> 3 </td> 
 <td> JOB </td> 
 <td> VARCHAR(20) </td> 
 <td> 职位 </td> 
 </tr> 
 <tr> 
 <td> 4 </td> 
 <td> MGR </td> 
 <td> BIGINT </td> 
 <td> 一个雇员对应的领导编号（雇员编号） </td> 
 </tr> 
 <tr> 
 <td> 5 </td> 
 <td> HIREDATE </td> 
 <td> DATE </td> 
 <td> 雇员的雇佣日期 </td> 
 </tr> 
 <tr> 
 <td> 6 </td> 
 <td> SAL </td> 
 <td> DOUBLE(7,2) </td> 
 <td> 基本工资，由两位小数和五位整数所组成 </td> 
 </tr> 
 <tr> 
 <td> 7 </td> 
 <td> COMM </td> 
 <td> DOUBLE(7,2) </td> 
 <td> 奖金/奖金 </td> 
 </tr> 
 <tr> 
 <td> 8 </td> 
 <td> DEPTNO </td> 
 <td> BIGINT </td> 
 <td> 一个雇员所在的部门编号，与dept表的对应 </td> 
 </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
 <tr> 
 <td style="background:#E6E6E6"> DEPT表数据: </td> 
 </tr> 
 <tr> 
 <td> 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'); </td> 
 </tr> 
 <tr> 
 <td style="background:#E6E6E6"> EMP表数据: </td> 
 </tr> 
 <tr> 
 <td> INSERT INTO `EMP` VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20); INSERT INTO `EMP` VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30); INSERT INTO `EMP` VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30); INSERT INTO `EMP` VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20); INSERT INTO `EMP` VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30); INSERT INTO `EMP` VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30); INSERT INTO `EMP` VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10); INSERT INTO `EMP` VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-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-09-08', 1500, 0, 30); INSERT INTO `EMP` VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, NULL, 20); INSERT INTO `EMP` VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30); INSERT INTO `EMP` VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20); INSERT INTO `EMP` VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10); </td> 
 </tr> 
 </tbody> 
</table>

练习题：

<table style="background:#E6E6E6"> 
 <tbody> 
 <tr> 
 <td> 01.查询每个雇员的编号、姓名、职位。 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> 02.查询每个雇员的职位，职位。 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> 03.查询每个雇员的职位，使用DISTINCT消除掉显示的重复行记录。 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> 04.计算出每个雇员的基本年薪，同时查询出雇员的编号、姓名。 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> 05.每个雇员每个月公司会补贴饭食200元，交通补助300元，计算年薪（年薪=(工资+奖金)*12）。 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> 06.查询基本工资高于2000的全部雇员信息。 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> 07.查询出smith的信息。 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> 08.查询出所有不是CLERK的详细信息。 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> 09.查询出所有销售人员(SALESMAN)的基本信息，并且要求销售人员的工资高于1300。 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> 10.查询出工资范围在1500~3000之间的全部雇员信息（包含1500和3000）。 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> 10.1查询出工资范围不在1500~3000之间的全部雇员信息（不包含1500和3000）。 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> 11.查询出所有经理或者是销售人员的信息，并且要求这些人的基本工资高于1500。 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> 12.要求查询出所有在1981年雇佣的雇员信息。 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> 13.查询所有领取奖金的雇员信息（comm不为空）。 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> 14.查询所有领取奖金高于100的雇员信息。 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> 15.查询出雇员编号是7369、7566、9999的雇员信息。 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> 16.查询出所有雇员姓名是以A开头的全部雇员信息。 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> 17.查询出雇员姓名第二个字母是M的全部雇员信息。 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> 18.查询出雇员姓名任意位置上包含字母A的全部雇员信息。 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> 19.查询出所有雇员的信息，要求按照工资排序。 </td> 
 </tr> 
 <tr> 
 <td> SELECT * FROM emp WHERE ENAME LIKE '%A%' </td> 
 </tr> 
 <tr> 
 <td> 20.要求查询所有雇员的信息，按照雇佣日期由先后排序。 </td> 
 </tr> 
 <tr> 
 <td> SELECT * FROM emp ORDER BY HIREDATE </td> 
 </tr> 
 <tr> 
 <td> 21.查询全部雇员信息，按照工资由高到低排序，如果工资相同，则按照雇佣日期由先后排序。 </td> 
 </tr> 
 <tr> 
 <td> SELECT * FROM emp ORDER BY SAL DESC,HIREDATE ASC </td> 
 </tr> 
 <tr> 
 <td> 22.查询部门30中的所有员工。 </td> 
 </tr> 
 <tr> 
 <td> SELECT * FROM emp WHERE DEPTNO='30' </td> 
 </tr> 
 <tr> 
 <td> 23.查询出所有办事员（CLERK）的姓名，编号和部门编号。 </td> 
 </tr> 
 <tr> 
 <td> SELECT ENAME,EMPNO,DEPTNO FROM emp WHERE JOB ='CLERK' </td> 
 </tr> 
 <tr> 
 <td> 24.查询出奖金高于薪金的员工。 </td> 
 </tr> 
 <tr> 
 <td> SELECT * FROM emp WHERE IFNULL(COMM,0)&gt;SAL </td> 
 </tr> 
 <tr> 
 <td> 25.查询出奖金高于薪金的60%的员工。 </td> 
 </tr> 
 <tr> 
 <td> SELECT * FROM emp WHERE IFNULL(COMM,0)&gt;SAL*0.6 </td> 
 </tr> 
 <tr> 
 <td> 26.查询出部门10中所有经理（MANAGER）和部门20中所有办事员（CLERK）的详细资料。 </td> 
 </tr> 
 <tr> 
 <td> SELECT * FROM emp WHERE (DEPTNO='10' AND JOB='MANAGER') OR (DEPTNO='20' AND JOB='CLERK') </td> 
 </tr> 
 <tr> 
 <td> 27.查询出部门10中所有经理，部门20中所有办事员，既不是经理又不是办事员但其薪金大于或等于2000的所有员工的信息。 </td> 
 </tr> 
 <tr> 
 <td> SELECT * FROM emp WHERE (DEPTNO='10' AND JOB='MANAGER') OR (DEPTNO='20' AND JOB='CLERK') OR (JOB&lt;&gt;'MANAGER' AND JOB&lt;&gt;'CLERK' AND SAL&gt;=2000) </td> 
 </tr> 
 <tr> 
 <td> 28.查询出收取奖金的员工的不同工作。 </td> 
 </tr> 
 <tr> 
 <td> SELECT DISTINCT JOB FROM emp WHERE IFNULL(COMM,0)&gt;0 </td> 
 </tr> 
 <tr> 
 <td> 39.查询出不收取奖金或收取的奖金低于100的员工。 </td> 
 </tr> 
 <tr> 
 <td> SELECT * FROM emp WHERE IFNULL(COMM,0)=0 OR IFNULL(COMM,0)&lt;100 </td> 
 </tr> 
 <tr> 
 <td> 40.查询出不带有“R”的员工的姓名。 </td> 
 </tr> 
 <tr> 
 <td> SELECT * FROM emp WHERE ENAME NOT LIKE '%R%' </td> 
 </tr> 
 <tr> 
 <td> 41.查询出每个雇员的姓名、职位、领导姓名。 </td> 
 </tr> 
 <tr> 
 <td> SELECT e.ENAME 雇员姓名,e.JOB 职位,m.ENAME 经理姓名 FROM emp e,emp m WHERE e.MGR=m.EMPNO </td> 
 </tr> 
 <tr> 
 <td> 42.查询出所有员工的编号、姓名及其直接上级的编号、姓名，显示的结果按领导年工资的降序排列。 </td> 
 </tr> 
 <tr> 
 <td> SELECT e.EMPNO 员工编号,e.ENAME 员工姓名,m.EMPNO 上级编号,m.ENAME 上级姓名,(m.SAL+IFNULL(m.COMM,0))*12 FROM emp e,emp m ORDER BY (m.SAL+IFNULL(m.COMM,0))*12 DESC </td> 
 </tr> 
 <tr> 
 <td> 43.查询出在销售部（SALES）工作的员工姓名、基本工资、雇佣日期、部门名称。（不知道销售部编号）。 </td> 
 </tr> 
 <tr> 
 <td> SELECT e.ENAME 员工姓名,e.SAL 基本工资,e.HIREDATE 雇用日期,d.DNAME 部门名称 FROM emp e,dept d WHERE d.DNAME='SALES' AND d.DEPTNO=e.DEPTNO </td> 
 </tr> 
 <tr> 
 <td> 44.查询出所有员工的姓名、部门名称和工资。 </td> 
 </tr> 
 <tr> 
 <td> SELECT e.ENAME 员工姓名,d.DNAME 部门名称,e.SAL 工资 FROM emp e,dept d WHERE e.DEPTNO=d.DEPTNO </td> 
 </tr> 
 <tr> 
 <td> 45.查询出所有员工的年工资，所在部门名称，按年薪从低到高排序。 </td> 
 </tr> 
 <tr> 
 <td> SELECT e.ENAME 姓名,(e.SAL+IFNULL(e.COMM,0))*12 年工资,d.DNAME 部门 FROM emp e,dept d WHERE e.DEPTNO=d.DEPTNO ORDER BY (e.SAL+IFNULL(e.COMM,0))*12 ASC </td> 
 </tr> 
 <tr> 
 <td> 46.查询出某个员工的上级主管及所在部门名称，并要求出这些主管中的薪水超过3000。 </td> 
 </tr> 
 <tr> 
 <td> SELECT e.ENAME 主管姓名,e.JOB 职位,d.DNAME 部门,e.SAL 工资 FROM emp e,dept d WHERE e.DEPTNO=d.DEPTNO AND e.JOB='MANAGER' AND e.SAL&gt;3000 </td> 
 </tr> 
 <tr> 
 <td> 47.查询出公司的最高和最低工资。 </td> 
 </tr> 
 <tr> 
 <td> SELECT MAX(e.SAL) 最高工资,MIN(e.SAL) 最低工资 FROM emp e </td> 
 </tr> 
 <tr> 
 <td> 48.查询出每个部门的人数、平均工资，只显示部门编号。 </td> 
 </tr> 
 <tr> 
 <td> SELECT COUNT(e.ENAME) 部门人数,AVG(e.SAL) 平均工资,d.DEPTNO 部门编号 FROM emp e,dept d WHERE e.DEPTNO=d.DEPTNO GROUP BY d.DEPTNO </td> 
 </tr> 
 <tr> 
 <td> 49.查询出每种职位的最高和最低工资。 </td> 
 </tr> 
 <tr> 
 <td> SELECT MAX(e.SAL) 最高工资,MIN(e.SAL) 最低工资,e.JOB 职位 FROM emp e GROUP BY e.JOB </td> 
 </tr> 
 <tr> 
 <td> 50.查询平均工资高于2000的职位信息，以及从事此职位的雇员人数、平均工资。 </td> 
 </tr> 
 <tr> 
 <td> SELECT e.JOB 职位,COUNT(e.ENAME) 员工人数,AVG(e.SAL) 平均工资 FROM emp e WHERE (SELECT AVG(m.SAL) FROM emp m WHERE e.JOB=m.JOB)&gt;2000 GROUP BY e.JOB </td> 
 </tr> 
 <tr> 
 <td> 51查询出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资、最高工资。 </td> 
 </tr> 
 <tr> 
 <td> SELECT d.DEPTNO 部门编号,d.DNAME 部门名称,AVG(e.SAL) 平均工资,MIN(e.SAL) 最低工资,MAX(e.SAL) 最高工资 FROM emp e,dept d WHERE (SELECT COUNT(e.ENAME) FROM emp)&gt;0 AND e.DEPTNO=d.DEPTNO GROUP BY d.DEPTNO </td> 
 </tr> 
 <tr> 
 <td> 52.查询出部门名称和这些部门的员工信息（数量、平均工资），同时列出那些没有员工的部门。 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 </tr> 
 </tbody> 
</table>