基于emp的单表查询练习题及答案

怼烎@ 2022-10-09 06:27 164阅读 0赞

有关tb\_emp表的结构及数据，请参看博客：[https://hcshow.blog.csdn.net/article/details/101469279][https_hcshow.blog.csdn.net_article_details_101469279]

## 单表查询 ##

*  选择部门30中的雇员

select * 
    from tb_emp
    where deptno =30

*  列出所有办事员的姓名、编号和部门

select empno,ename,dptno

*  找出佣金高于薪金的雇员

select *
    from tb_emp
    where comm > sal

*  找出佣金高于薪金60%的雇员

select *
    from tb_emp
    where comm > sal*0.6

*  检索tb\_emp表中的员工姓名、月收入及部门编号

SELECT ename as 姓名,sal+IFNULL(comm,0) 工资,deptno 部门编号 
    FROM tb_emp;

*  检索tb\_emp表中员工姓名、及雇佣时间

SELECT ename ,hiredate 
    FROM tb_emp;

*  使用distinct去掉重复行。

SELECT DISTINCT deptno 
    from tb_emp;

*  检索tb\_emp表中的员工姓名及全年的收入

SELECT ename 员工,(sal+ifnull(comm,0))*12 总收入 
    from tb_emp;

*  检索月收入大于2000的员工姓名及月收入

SELECT ename , sal+IFNULL(comm,0) 
    FROM tb_emp
    WHERE  sal+IFNULL(comm,0) > 2000;

*  检索以S开头的员工姓名及月收入。

SELECT ename , sal+IFNULL(comm,0) 
    FROM tb_emp
    WHERE  ename LIKE 'S%';

*  检索tb\_emp表中月收入是800的或是1250的员工姓名及部门编号

SELECT ename,deptno,sal
    from tb_emp
    WHERE sal = 800 || sal = 1250

或

SELECT ename,deptno,sal
    from tb_emp
    WHERE sal = 800 OR sal = 1250

*  显示在部门20中岗位CLERK的所有雇员信息

SELECT *
    FROM tb_emp
    WHERE deptno = 20 && job = 'clerk'

或

SELECT *
    FROM tb_emp
    WHERE deptno = 20 and job = 'clerk'

*  显示工资高于2500或岗位为MANAGER的所有雇员信息

SELECT *
    FROM tb_emp
    WHERE sal >2500 AND job ='MANAGER';

*  检索tb\_emp表中有提成的员工姓名、月收入及提成。

SELECT ename,sal,comm
    FROM tb_emp
    WHERE !ISNULL(comm)

或

SELECT ename,sal,comm
    FROM tb_emp
    WHERE NOT ISNULL(comm)

*  检索tb\_emp表中部门编号是30的员工姓名、月收入及提成，并要求其结果按月收入升序、然后按提成降序显示。

SELECT ename,sal,comm,deptno
    FROM tb_emp
    WHERE deptno = 30
    ORDER BY sal ASC , comm DESC

*  查询工资大于1200的员工姓名和工资

SELECT ename,sal
    FROM tb_emp
    WHERE sal>1200

*  查询员工号为7934的员工的姓名和部门号

select ename,deptno
    from tb_emp
    where empno=7934

*  选择工资不在5000到12000的员工的姓名和工资

SELECT ename,sal
    FROM tb_emp
    WHERE sal <5000 OR sal>12000

*  选择雇用时间在1981-02-01到1981-05-01之间的员工姓名，职位(job)和雇用时间,按从早到晚排序.

SELECT ename,job,hiredate
    FROM tb_emp
    WHERE hiredate BETWEEN '1981-02-01' AND '1981-05-01'
    ORDER BY hiredate ASC

*  选择在20或10号部门工作的员工姓名和部门号

SELECT ename,deptno
    FROM tb_emp
    WHERE deptno =20 OR deptno = 10

*  选择在1987年雇用的员工的姓名和雇用时间

SELECT ename,hiredate
    FROM tb_emp
    WHERE YEAR(hiredate) = '1987'

*  选择公司中没有上级的员工姓名及job

SELECT ename,job
    FROM tb_emp
    WHERE ISNULL(mgr)

*  选择公司中不是管理者的员工姓名及job

SELECT ename,job
    FROM tb_emp
    WHERE job != 'MANAGER'

*  选择公司中有奖金 (COMM不为空,且不为0) 的员工姓名，工资和奖金比例,按工资逆排序,奖金比例逆排序.

SELECT ename,comm,sal+comm,comm/(sal+comm)
    FROM tb_emp
    WHERE NOT ISNULL(comm) AND comm != 0
    ORDER BY sal+comm DESC ,comm/(sal+comm) DESC

*  选择员工姓名的第三个字母是a的员工姓名

SELECT * 
    FROM tb_emp
    WHERE ename like '__a%'

*  找出部门10中所有经理和部门20中的所有办事员的详细资料

SELECT * 
    FROM tb_emp
    WHERE (deptno =10 and job = 'manager') OR (deptno =20 and job = 'clerk')

*  找出部门10中所有经理、部门20中所有办事员，既不是经理又不是办事员但其薪金>=2000的所有雇员的详细资料

SELECT * 
    FROM tb_emp
    WHERE (deptno =10 and job = 'manager') OR
    (deptno =20 and job = 'clerk') OR
    (job != 'manager' and job != 'clerk' and sal>2000)

*  找出收取佣金的雇员的不同工作

SELECT DISTINCT job
    FROM tb_emp
    WHERE comm !=0

*  找出不收取佣金或收取的佣金低于100的雇员

select *
    from tb_emp
    where comm = null or comm=0 or comm<100;

*  找出各月倒数第三天受雇的所有雇员

SELECT ename ,hiredate
    FROM tb_emp
    WHERE day(hiredate) = day(LAST_DAY(hiredate))-2

*  找出早于25年之前受雇的雇员

SELECT *
    FROM tb_emp
    WHERE YEAR(hiredate) < YEAR(NOW())-25

*  显示正好为6个字符的雇员姓名length

SELECT *
    FROM tb_emp
    WHERE LENGTH(ename) = 6;

*  显示不带有’R’的雇员姓名not like

SELECT ename
    FROM tb_emp
    WHERE ename not like '%R%'

*  把首字母名字变小

SELECT concat(LOWER(substring(ename,1,1)),SUBSTRING(ename,2,LENGTH(ename)-1))
    FROM tb_emp

*  显示所有雇员的姓名的前三个字符substr

SELECT SUBSTRING(ename,1,3)
    FROM tb_emp

*  显示所有雇员的姓名，用\*替换所有’A’ replace

SELECT REPLACE(ename,'A','*')
    FROM tb_emp

*  显示雇员的详细资料，按姓名排序

select * 
    from tb_emp
    order by ename

*  显示雇员姓名，根据其服务年限，将最老的雇员排在最前面

select ename
    from tb_emp
    order by hiredate asc

*  显示所有雇员的姓名、工作和薪金，按工作的降序顺序排序，而工作相同时按薪金升序

select ename,job,sal
    from tb_emp
    order by job desc,sal asc

*  显示所有雇员的姓名和加入公司的年份和月份，按雇员受雇日所在月降序排序，将最早年份的员工排在最前面ASC

FROM tb_emp
    ORDER BY YEAR(hiredate) asc, MONTH(hiredate) DESC

*  显示在一个月为30天的情况下所有雇员的日薪金

select sal/30
    from tb_emp

*  找出在（任何年份的）2月受聘的所有雇员

select *
    from tb_emp
    where month(hiredate) = 2

*  对于每个雇员，显示其加入公司的天数

SELECT DATEDIFF(NOW(),hiredate)
    FROM tb_emp

*  显示姓名字段的任何位置，包含 “A” 的所有雇员的姓名

select ename
    from tb_emp
    where ename like '%A%'

*  以年、月和日显示所有雇员的服务年限

SELECT YEAR(NOW()) - YEAR(hiredate) 年, (YEAR(NOW()) - YEAR(hiredate)-2)*12 + MONTH(NOW()) +(12-MONTH(hiredate))月, DATEDIFF(NOW(),hiredate)天
    FROM tb_emp;

*  显示所有雇员的姓名以及满10年服务年限的日期

SELECT hiredate , DATE_ADD(hiredate,INTERVAL 10 YEAR)
    FROM tb_emp

*  列出最低薪金大于1500的各种工作。

SELECT  DISTINCT job
    from tb_emp
    where sal >1500

*  列出在每个部门工作的员工数量、平均工资和平均服务期限。

SELECT deptno,avg(sal),COUNT(*),avg(year(NOW())- YEAR(hiredate))
    FROM tb_emp
    group by deptno

*  列出各种工作的最低工资。

SELECT job, MIN(sal)
    FROM tb_emp
    GROUP BY job

*  列出各个部门的MANAGER（经理）的最低薪金。

SELECT deptno,job, MIN(sal)
    FROM tb_emp
    WHERE job = 'manager'
    GROUP BY deptno

*  列出所有员工的年工资,按年薪从低到高排序。

SELECT sal*12 gz
    FROM tb_emp
    ORDER BY gz ASC

## 嵌套查询 ##

*  列出薪金比“SMITH”多的所有员工。

SELECT *
    from tb_emp
    where sal> (SELECT sal
    FROM tb_emp
    where ename= 'smith')

*  列出薪金高于公司平均薪金的所有员工。

SELECT *
    from tb_emp
    WHERE sal>(SELECT avg(sal)
    FROM tb_emp)

*  列出与“SCOTT”从事相同工作的所有员工。

SELECT *
    from tb_emp
    WHERE job = (SELECT job
    from tb_emp
    where ename = 'scott')

*  求出30部门工资的最大值与最小值，然后列出其它部门工资在这个范围的员工的信息

SELECT ename,sal,deptno
    from tb_emp
    WHERE sal BETWEEN (SELECT min(sal)
    FROM tb_emp
    WHERE deptno =30) and (SELECT max(sal)
    FROM tb_emp
    WHERE deptno =30) and deptno != 30

*  列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。

SELECT ename,sal,deptno
    from tb_emp
    where sal> (SELECT max(sal)
    FROM tb_emp
    WHERE deptno =30) and deptno !=30

## 多表查询/连接查询/关联查询 ##

*  列出受雇日期早于其直接上级的所有员工。

SELECT e1.ename,e1.hiredate , e2.hiredate
    FROM tb_emp e1
    JOIN tb_emp e2 ON e1.mgr = e2.empno
    where e1.hiredate < e2.hiredate

*  列出部门名称和这些部门的员工信息。

SELECT tb_dept.dname, tb_emp.*	
    FROM tb_emp 
    JOIN tb_dept ON tb_dept.deptno = tb_emp.deptno -- 连接条件

*  列出所有“CLERK”（办事员）的姓名及其部门名称。

SELECT tb_emp.ename,tb_dept.dname
    FROM tb_emp 
    JOIN tb_dept ON tb_emp.deptno = tb_dept.deptno
    WHERE tb_emp.job = 'clerk'

*  列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。

SELECT emp.ename
    FROM tb_emp AS emp
    JOIN tb_dept dept ON emp.deptno = dept.deptno
    WHERE dept.dname = 'sales'

*  列出所有员工的姓名、部门名称和工资。

SELECT tb_emp.ename,tb_dept.dname,tb_emp.sal
    FROM tb_emp
    JOIN tb_dept ON tb_emp.deptno = tb_dept.deptno

*  列出所有部门的详细信息和部门人数。

SELECT tb_dept.*,t.renShu
    FROM tb_dept
    LEFT JOIN (SELECT deptno, count(*) renShu
    FROM tb_emp
    GROUP BY deptno) t ON tb_dept.deptno = t.deptno

下面是分析过程

SELECT deptno, count(*) renShu
    FROM tb_emp
    GROUP BY deptno
    
    SELECT tb_dept.*,t.renShu
    FROM tb_dept
    JOIN (**) t ON tb_dept.deptno = t.deptno  -- **用来替代上面语句查询得到的表

## 自连接 ##

*  列出所有员工的姓名及其直接上级的姓名。

SELECT e1.ename,e2.ename
    FROM tb_emp e1
    LEFT JOIN tb_emp e2 ON e1.mgr = e2.empno

*  列出受雇日期早于其直接上级的所有员工。

SELECT e1.ename,e1.hiredate , e2.hiredate
    FROM tb_emp e1
    JOIN tb_emp e2 ON e1.mgr = e2.empno
    where e1.hiredate < e2.hiredate

[https_hcshow.blog.csdn.net_article_details_101469279]: https://hcshow.blog.csdn.net/article/details/101469279