SQL练习

最新推荐文章于 2025-03-21 16:08:50 发布

「已注销」

最新推荐文章于 2025-03-21 16:08:50 发布

阅读量1.5k

点赞数

分类专栏： MYSQL

本文链接：https://blog.youkuaiyun.com/FreshK/article/details/116651775

版权

MYSQL 专栏收录该内容

7 篇文章

订阅专栏

这篇博客提供了多个关于SQL的练习题目，涵盖了从基础查询到复杂的联接、子查询和聚合函数的使用，旨在帮助读者加深对SQL语言的理解和应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

EMPNO 员工编号 NOT NULL NUMBER(4)
ENAME 员工名称 VARCHAR2(10)
JOB 工作名称 VARCHAR2(9)
MGR 直接上级编号 NUMBER(4)
HIREDATE 入职时间 DATE
SAL 月薪 NUMBER(7,2)
COMM 补助 NUMBER(7,2)
DEPTNO 部门编号 NUMBER(2)

DEPTNO 部门编号 NOT NULL NUMBER(2)
DNAME 部门名称 VARCHAR2(14)
LOC 所在地 VARCHAR2(13)
注意：薪金=月薪+补助

CREATE TABLE TB_EMPLOYEE(
EMPNO INT NOT NULL,
ENAME VARCHAR(10),
JOB VARCHAR(32),
MGR INT,
HIREDATE DATE,
SAL INT,
COMM INT,
DEPTNO INT
);

CREATE TABLE TB_DEPARTMENT(
DEPTNO INT NOT NULL,
DNAME VARCHAR(14),
LOC VARCHAR(13)
);

INSERT INTO TB_EMPLOYEE VALUES
(7369,‘SMITH’,‘CLERK’,7902,‘1980-12-17’,800.0,NULL,20),
(7499,‘ALLEN’,‘SALESMAN’,7698,‘1981-2-20’,1600.0,300.0,30),
(7521,‘WARD’,‘SALESMAN’,7698,‘1981-2-22’,1250.0,500.0,30),
(7566,‘JONES’,‘MANGER’,7839,‘1981-4-2’,2975.0,NULL,20),
(7654,‘MARTIN’,‘SALESMAN’,7698,‘1981-9-28’,1250.0,1400.0,30),
(7698,‘BLAKE’,‘MANGER’,7839,‘1981-5-1’,2850.0,NULL,30),
(7782,‘CLARK’,‘MANGER’,7839,‘1981-6-9’,2450.0,NULL,10),
(7788,‘SCOTT’,‘ANALYST’,7566,‘1987-4-19’,4000.0,NULL,20),
(7839,‘KING’,‘VICE_PRESIDENT’,1,‘1981-11-17’,35000.0,NULL,10),
(7844,‘TURNER’,‘SALESMAN’,7698,‘1981-9-8’,1500.0,0.00,30),
(7876,‘ADAMS’,‘CLERK’,7788,‘1987-5-23’,1100.0,NULL,20),
(7900,‘JAMES’,‘CLERK’,7698,‘1981-12-3’,950.0,NULL,30),
(7902,‘FORD’,‘ANALYST’,7566,‘1981-12-3’,3000.0,NULL,20),
(7934,‘MILLER’,‘CLERK’,7782,‘1982-1-23’,1300.0,NULL,10),
(102,‘EricHu’,‘Developer’,1455,‘2011-5-26’,5500.0,14.00,10),
(104,‘huyong’,‘PM’,1455,‘2011-5-26’,5500.0,14.00,10),
(105,‘WANGJING’,‘Developer’,1455,‘2011-5-26’,5500.0,14.00,10),
(1,‘DAVID’,‘PRESIDENT’,NULL,‘1980-11-26’,55500.0,4500.00,10),
(6666,‘TOM’,‘MANGER’,7839,‘1990-1-5’,1500.0,400.00,10);

INSERT INTO TB_DEPARTMENT VALUES
(10,‘ACCOUNTING’,‘NEW YORK’),
(20,‘RESEARCH’,‘DALLAS’),
(30,‘SALES’,‘CHICAGO’),
(40,‘OPERATIONS’,‘BOSTON’),
(50,‘50ABC’,‘50def’),
(60,‘Developer’,‘HaiKou’);

1.列出至少有一个雇员的所有部门
select * from TB_DEPARTMENT D WHERE D.DEPTNO IN (SELECT DEPTNO FROM TB_EMPLOYEE);

2、列出薪金比"SMITH"多的所有雇员
SELECT * FROM TB_EMPLOYEE E WHERE (E.SAL+IFNULL(E.COMM,0)) > (SELECT DISTINCT SAL+IFNULL(COMM,0) FROM TB_EMPLOYEE TEMP WHERE TEMP.ENAME = ‘SMITH’);

3、列出所有雇员的姓名及其直接上级的姓名
SELECT E.ENAME 雇员姓名,IFNULL(F.ENAME,E.MGR) 直接上级姓名 FROM TB_EMPLOYEE E,TB_EMPLOYEE F WHERE E.MGR = F.EMPNO;

SELECT E.ENAME 雇员姓名,IFNULL(T.ENAME,E.MGR) 直接上级姓名 FROM TB_EMPLOYEE E LEFT JOIN (SELECT F.ENAME,F.EMPNO FROM TB_EMPLOYEE F) T ON E.MGR = T.EMPNO;

4、列出入职日期早于其直接上级的所有雇员
SELECT E.* FROM TB_EMPLOYEE E,TB_EMPLOYEE F WHERE E.MGR = F.EMPNO AND E.HIREDATE < F.HIREDATE;

5、列出部门名称和这些部门的雇员,同时列出那些没有雇员的部门
SELECT D.DNAME 部门名称,IFNULL(T.ENAME,‘无雇员’) 雇员 FROM TB_DEPARTMENT D LEFT JOIN (SELECT * FROM TB_EMPLOYEE E) T ON T.DEPTNO = D.DEPTNO;

6、列出所有“CLERK”（办事员）的姓名及其部门名称
SELECT E.ENAME 姓名,D.DNAME 部门名称 FROM TB_EMPLOYEE E,TB_DEPARTMENT D WHERE E.JOB = ‘CLERK’ AND E.DEPTNO = D.DEPTNO;

7、列出各种工作类别的最低薪金，显示最低薪金大于1500的记录
SELECT E.JOB 工作类别,MIN(E.SAL + IFNULL(E.COMM,0)) 最低薪金 FROM TB_EMPLOYEE E GROUP BY E.JOB;

SELECT E.JOB 工作类别,MIN(E.SAL + IFNULL(E.COMM,0)) 最低薪金 FROM TB_EMPLOYEE E GROUP BY E.JOB HAVING MIN(E.SAL + IFNULL(E.COMM,0)) > 1500;

8、列出从事“SALES”（销售）工作的雇员的姓名，假定不知道销售部的部门编号
SELECT E.ENAME 姓名 FROM TB_EMPLOYEE E,TB_DEPARTMENT D WHERE E.DEPTNO = D.DEPTNO AND D.DNAME = ‘SALES’;

9、列出薪金高于公司平均水平的所有雇员
SELECT * FROM TB_EMPLOYEE E WHERE (E.SAL + IFNULL(E.COMM,0)) > (SELECT AVG(T.SAL + IFNULL(T.COMM,0)) FROM TB_EMPLOYEE T);

10、列出与“SCOTT”从事相同工作的所有雇员
SELECT * FROM TB_EMPLOYEE E WHERE E.JOB IN (SELECT T.JOB FROM TB_EMPLOYEE T WHERE T.ENAME = ‘SCOTT’);

11、列出某些雇员的姓名和薪金，条件是他们的薪金等于部门30中任何一个雇员的薪金
SELECT E.ENAME 姓名,(E.SAL + IFNULL(E.COMM,0)) 薪金,E.DEPTNO 部门编号 FROM TB_EMPLOYEE E WHERE (E.SAL + IFNULL(E.COMM,0)) IN (SELECT (T.SAL + IFNULL(T.COMM,0)) FROM TB_EMPLOYEE T WHERE T.DEPTNO = 30);

12、列出某些雇员的姓名和薪金，条件是他们的薪金高于部门30中所有雇员的薪金
SELECT E.ENAME 姓名,(E.SAL + IFNULL(E.COMM,0)) 薪金,E.DEPTNO 部门编号 FROM TB_EMPLOYEE E WHERE (E.SAL + IFNULL(E.COMM,0)) > (SELECT SUM(T.SAL + IFNULL(T.COMM,0)) FROM TB_EMPLOYEE T WHERE T.DEPTNO = 30);

13、列出每个部门的信息以及该部门中雇员的数量
SELECT D.*,COUNT(T.EMPNO) 雇员数量 FROM TB_DEPARTMENT D LEFT JOIN (SELECT * FROM TB_EMPLOYEE E) T ON T.DEPTNO = D.DEPTNO GROUP BY D.DEPTNO;

14、列出所有雇员的雇员名称、部门名称和薪金
SELECT E.ENAME 雇员名称,D.DNAME 部门名称,(E.SAL + IFNULL(E.COMM,0)) 薪金 FROM TB_EMPLOYEE E,TB_DEPARTMENT D WHERE E.DEPTNO = D.DEPTNO;

15、列出从事同一种工作但属于不同部门的雇员的不同组合
SELECT E.ENAME,E.JOB,E.DEPTNO,T.ENAME,T.JOB,T.DEPTNO FROM TB_EMPLOYEE E,TB_EMPLOYEE T WHERE E.JOB = T.JOB AND E.DEPTNO <> T.DEPTNO ORDER BY E.ENAME;

16、列出分配有雇员数量的所有部门的详细信息，即使是分配有0个雇员
SELECT D.* FROM TB_DEPARTMENT D LEFT JOIN (SELECT * FROM TB_EMPLOYEE E) T ON T.DEPTNO = D.DEPTNO GROUP BY D.DEPTNO;

17、列出各种类别工作的最低工资
SELECT E.JOB 工种,MIN((E.SAL + IFNULL(E.COMM,0))) 最低工资 FROM TB_EMPLOYEE E GROUP BY E.JOB;

18、列出各个部门的MANAGER（经理）的最低薪金
SELECT E.DEPTNO 部门编号,D.DNAME 部门名称,MIN((E.SAL + IFNULL(E.COMM,0))) MANAGER（经理）的最低薪金 FROM TB_EMPLOYEE E,TB_DEPARTMENT D WHERE E.DEPTNO = D.DEPTNO AND E.JOB = 'MANGER’GROUP BY E.DEPTNO;

19、列出按年薪排序的所有雇员的年薪
SELECT E.ENAME 雇员姓名,(E.SAL + IFNULL(E.COMM,0))*12 年薪 FROM TB_EMPLOYEE E ORDER BY (E.SAL + IFNULL(E.COMM,0))*12;

20、列出薪金水平处于第四位的雇员
SELECT * FROM TB_EMPLOYEE E ORDER BY (E.SAL + IFNULL(E.COMM,0)) LIMIT 3,1;