mysql练习题

题目来源(百度文库)：https://wenku.baidu.com/view/0cf2883b3868011ca300a6c30c2259010202f39c.html

以下SQL直接运行即可。

DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;

##部门表
#DROP IF EXISTS TABLE DEPT;
CREATE TABLE DEPT(
    DEPTNO INT  PRIMARY KEY,##部门编号
    DNAME VARCHAR(14) ,    ##部门名称
    LOC VARCHAR(13)      ##部门地址
    ) ;

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');

##员工表    
#DROP IF EXISTS TABLE EMP;
CREATE TABLE EMP(
    EMPNO INT  PRIMARY KEY,    #员工编号
    ENAME VARCHAR(10),    #员工姓名
    JOB VARCHAR(9),        #员工工作
    MGR INT,         #员工直属领导编号
    HIREDATE DATE,         #入职时间
    SAL DOUBLE,        #工资
    COMM DOUBLE,        #奖金
    DEPTNO INT        #对应dept表的外键
);  
## 添加 部门 和 员工 之间的主外键关系
ALTER TABLE EMP ADD CONSTRAINT FOREIGN KEY EMP(DEPTNO) REFERENCES DEPT (DEPTNO);

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-07-03',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-07-13',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,'1981-01-23',1300,NULL,10);

#工资等级表
#DROP IF EXISTS TABLE SALGRADE;
CREATE TABLE SALGRADE( 
    GRADE INT,    #等级
    LOSAL DOUBLE,    #最低工资
    HISAL DOUBLE );    #最高工资

INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);

#1.查找部门30中员工的详细信息
SELECT * FROM EMP WHERE DEPTNO=30;

#2.找出从事clerk工作的员工的编号、姓名、部门号。
SELECT EMPNO,ENAME,DEPTNO FROM EMP WHERE JOB='CLERK';

#3.检索出奖金多于基本工资的员工信息。
SELECT * FROM EMP WHERE COMM > SAL;

#4.检索出奖金多于基本工资60%的员工信息。
SELECT * FROM EMP WHERE COMM > SAL*0.6;

#5.找出10部门的经理、20部门的职员 的员工信息。
SELECT * FROM EMP WHERE (DEPTNO=10 AND JOB='MANAGER') OR (DEPTNO=20 AND JOB='CLERK');

#6.找出10部门的经理、20部门的职员 或者既不是经理也不是职员但是工资高于2000元的员工信息。
SELECT * FROM EMP WHERE ((DEPTNO=10 AND JOB='MANAGER') OR (DEPTNO=20 AND JOB='CLERK')) OR ((JOB NOT IN ('MANAGER','CLERK')) AND (SAL > 2000));

#7.找出获得奖金的员工的工作。
SELECT * FROM EMP WHERE COMM != 'null';

#8.找出奖金少于100或者没有获得奖金的员工的信息。
SELECT * FROM EMP WHERE (COMM < 100) OR (COMM IS NULL);

#9.找出姓名以A、B、S开始的员工信息。
SELECT * FROM EMP WHERE ENAME LIKE 'A%' OR ENAME LIKE 'B%' OR ENAME LIKE 'S%';

#10.找到名字长度为6个字符的员工信息。
SELECT * FROM EMP WHERE CHARACTER_LENGTH(ENAME) = 6;

#11.名字中不包含R字符的员工信息。
SELECT * FROM EMP WHERE ENAME NOT LIKE '%R%';
SELECT * FROM EMP WHERE NOT LOCATE('R',ENAME);

#12.返回员工的详细信息并按姓名排序。
SELECT * FROM EMP ORDER BY ENAME;

#13.返回员工的信息并按工作降序工资升序排列
SELECT * FROM EMP ORDER BY JOB DESC,SAL;

#14.计算员工的日薪(按30天)。
SELECT ENAME,SAL/30 AS 日薪 FROM EMP;

#15.找出姓名中包含A的员工信息。
SELECT * FROM EMP ENAME WHERE LOCATE('A',ENAME);

# 多表
#1.返回拥有员工的部门名、部门号。
SELECT DEPTNO,DNAME FROM DEPT
WHERE DEPTNO IN
(SELECT DEPTNO FROM EMP GROUP BY DEPTNO);

SELECT DEPT.`DEPTNO`,DEPT.`DNAME` FROM 
EMP LEFT JOIN DEPT ON EMP.`DEPTNO`=DEPT.`DEPTNO`
GROUP BY DEPT.`DEPTNO`;

#2.工资水平多于smith的员工信息。
SELECT * FROM EMP WHERE SAL >
(SELECT SAL FROM EMP WHERE ENAME='SMITH');

#3.返回员工和所属经理的姓名。
SELECT e1.`ENAME`, e2.`ENAME` FROM 
EMP e1 LEFT JOIN EMP e2 ON e1.`MGR`=e2.`EMPNO`;

#4.返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名
SELECT e1.`ENAME`, e1.`HIREDATE`,e2.`ENAME`,e2.`HIREDATE` FROM 
EMP e1 LEFT JOIN EMP e2 ON e1.`MGR`=e2.`EMPNO` 
WHERE e1.`HIREDATE`<e2.`HIREDATE`;

#5.返回员工姓名及其所在的部门名称。
SELECT e.ENAME,d.DNAME FROM 
EMP e LEFT JOIN DEPT d ON e.`DEPTNO`=d.`DEPTNO`;

#6.返回从事clerk工作的员工姓名和所在部门名称。
SELECT e.ENAME,d.DNAME FROM 
EMP e LEFT JOIN DEPT d ON e.`DEPTNO`=d.`DEPTNO`
WHERE e.`JOB`='CLERK';

#7.返回部门号及其本部门的最低工资。
SELECT d.`DEPTNO`,MIN(e.SAL) FROM
DEPT d LEFT JOIN EMP e ON d.`DEPTNO`=e.`DEPTNO`
GROUP BY d.`DEPTNO`;

#8.返回销售部(sales)所有员工的姓名。
SELECT ENAME FROM EMP WHERE DEPTNO =
(SELECT DEPTNO FROM DEPT WHERE DNAME='SALES');

SELECT e.ENAME FROM 
EMP e LEFT JOIN DEPT d ON e.`DEPTNO`=d.`DEPTNO`
WHERE d.`DNAME`='SALES';

#9.返回工资水平多于平均工资的员工。
SELECT * FROM EMP 
WHERE SAL >
(SELECT AVG(SAL) FROM EMP);

#10.返回与SCOTT从事相同工作的员工。
SELECT * FROM EMP
WHERE JOB =
(SELECT JOB FROM EMP WHERE ENAME='SCOTT')
AND ENAME != 'SCOTT';

#11.返回与30部门员工工资水平相同的员工姓名与工资。????
SELECT ENAME,SAL FROM EMP
WHERE SAL IN (SELECT SAL FROM EMP WHERE DEPTNO='30');
# and DEPTNO != '30';

#12.返回工资高于30部门所有员工工资水平的员工信息。
SELECT * FROM EMP
WHERE SAL > (SELECT MAX(SAL) FROM EMP WHERE DEPTNO='30');

#13.返回部门号、部门名、部门所在位置及其每个部门的员工总数。
SELECT d.`DEPTNO`,d.`DNAME`,d.`LOC`,COUNT(*) AS 员工总数 FROM
EMP e LEFT JOIN DEPT d ON d.`DEPTNO`=e.`DEPTNO`
GROUP BY d.`DEPTNO`;

SELECT d.`DEPTNO`,d.`DNAME`,d.`LOC`,COUNT(e.`DEPTNO`) AS 员工总数 FROM
DEPT d LEFT JOIN EMP e ON d.`DEPTNO`=e.`DEPTNO`
GROUP BY d.`DEPTNO`;

#14.返回员工的姓名、所在部门名及其工资。
SELECT e.ENAME,d.DNAME,e.SAL FROM
EMP e LEFT JOIN DEPT d ON e.`DEPTNO`=d.DEPTNO;

#15.返回员工的详细信息。(包括部门名)
SELECT e.*,d.DNAME FROM
EMP e LEFT JOIN DEPT d ON e.`DEPTNO`=d.DEPTNO;

#16.返回员工工作及其从事此工作的最低工资。
SELECT JOB,MIN(SAL) FROM EMP GROUP BY JOB;

SELECT e1.ENAME,e1.JOB,e1.SAL,e2.m '职位最低工资' FROM
EMP e1 LEFT JOIN (SELECT JOB,MIN(SAL) AS m FROM EMP GROUP BY JOB) e2 ON e1.`JOB`=e2.JOB;

#17.返回不同部门经理的最低工资。
SELECT DEPTNO,ENAME,JOB,MIN(SAL) FROM EMP WHERE JOB='MANAGER' GROUP BY DEPTNO;

#18.计算出员工的年薪，并且以年薪排序。
SELECT ENAME,SAL*12 AS YSAL FROM EMP ORDER BY YSAL;

#19.返回工资处于第四级别的员工的姓名。
SELECT ENAME,SAL FROM EMP 
WHERE SAL BETWEEN (SELECT LOSAL FROM SALGRADE WHERE GRADE='4')
AND (SELECT HISAL FROM SALGRADE WHERE GRADE='4');

#20.返回工资为二等级的职员名字、部门所在地、和二等级的最低工资和最高工资
SELECT e.ENAME,d.`LOC`,e.SAL,s.LOSAL,s.HISAL FROM
EMP e LEFT JOIN DEPT d ON e.`DEPTNO`=d.DEPTNO 
JOIN (SELECT GRADE,LOSAL,HISAL FROM SALGRADE WHERE GRADE='2' ) s 
WHERE e.`SAL` BETWEEN s.LOSAL AND s.HISAL;

#21.返回工资为二等级的职员名字、部门所在地、二等级员工工资的最低工资和最高工资
SELECT MIN(e.SAL) '最低工资',MAX(e.SAL) '最高工资' FROM
EMP e LEFT JOIN DEPT d ON e.`DEPTNO`=d.DEPTNO 
JOIN (SELECT GRADE,LOSAL,HISAL FROM SALGRADE WHERE GRADE='2' ) s 
WHERE e.`SAL` BETWEEN s.LOSAL AND s.HISAL;

#22.工资等级多于smith的员工信息。
SELECT * FROM EMP 
WHERE SAL >
(SELECT HISAL FROM SALGRADE 
WHERE (SELECT SAL FROM EMP WHERE ENAME='SMITH') 
BETWEEN LOSAL AND HISAL);