数据库练习题
DROP TABLE DEPT; CREATE TABLE DEPT (DEPTNO INT(2) PRIMARY KEY, DNAME VARCHAR(14) , LOC VARCHAR(13) ) ; DROP TABLE EMP; CREATE TABLE EMP (EMPNO INT(4) PRIMARY KEY, ENAME VARCHAR(10), JOB VARCHAR(9), MGR INT(4), HIREDATE DATE, SAL DECIMAL(7,2), COMM DECIMAL(7,2), DEPTNO INT(2) REFERENCES DEPT(DEPTNO)); 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'); 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-7-13',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-7-13',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); DROP TABLE BONUS; CREATE TABLE BONUS ( ENAME VARCHAR(10) , JOB VARCHAR(9) , SAL INT, COMM INT ) ; DROP TABLE SALGRADE; CREATE TABLE SALGRADE ( GRADE INT, LOSAL INT, HISAL INT ); 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); COMMIT;
SQL练习题一 ——— 单表查询
1.选择部门30中的所有员工；
mysql> SELECT * FROM EMP WHERE DEPTNO=30;
2.列出所有办事员(CLERK)的姓名，编号和部门编号；
mysql> SELECT ENAME,EMPNO,DEPTNO FROM EMP WHERE JOB='CLERK';
3.找出奖金高于工资的员工；
mysql> SELECT ENAME FROM EMP WHERE IFNULL(COMM,0) > SAL;
4.找出奖金高于工资的60%的员工；
mysql> SELECT ENAME FROM EMP WHERE IFNULL(COMM,0) > SAL*0.6;
5.找出部门10中的所有经理(MANAGER)和部门20中所有的办事员(CLERK)的详细资料；
mysql> SELECT * FROM EMP WHERE (JOB='MANAGER' AND DEPTNO=10) OR (JOB='CLERK' AND DEPTNO=20);
6.找出部门10中所有的经理(MANAGER),部门20中所有办事员(CLERK),既不是经理又不是办事员但其工资大于或等于2000的所有员工的详细资料；
mysql> SELECT * FROM EMP WHERE (JOB='MANAGER' AND DEPTNO=10) OR (JOB='CLERK' AND DEPTNO=20) OR JOB NOT IN ('MANAGER','CLERK') AND SAL>=2000;
7.找出收取奖金的员工的不同工作；
mysql> SELECT DISTINCT JOB FROM EMP WHERE IFNULL(COMM,0)>0;
8.找出不收取奖金或者收取的奖金低于100的员工；
mysql> SELECT * FROM EMP WHERE IFNULL(COMM,0)<=100;
9.找出各月倒数第三天受雇的所有员工；
mysql> SELECT * FROM EMP WHERE LAST_DAY(HIREDATE) - 2 = HIREDATE;
10.找出早于12年前受雇的员工；
mysql> SELECT * FROM EMP WHERE DATE_ADD(HIREDATE,INTERVAL 12 YEAR) < NOW();
11.以首字母大写的方式显示所有员工的姓名；
mysql> SELECT CONCAT(UPPER(LEFT(ENAME,1)),LOWER(SUBSTRING(ENAME,2,LENGTH(ENAME)-1))) FROM EMP
12.显示正好为5个字符的员工的姓名；
mysql> SELECT ENAME FROM EMP WHERE LENGTH(ENAME)=5;
13.显示不带有“R”的员工姓名；
mysql> SELECT ENAME FROM EMP WHERE ENAME NOT LIKE '%R%';
14.显示所有员工姓名的前三个字符；
mysql> SELECT LEFT(ENAME,3) FROM EMP;
15.显示所有员工的姓名，用“a”替换所有的“A”；
mysql> SELECT REPLACE(ENAME,'A','a') FROM EMP;
16.显示满10年服务年限的员工的姓名和受雇日期；
mysql> SELECT ENAME,HIREDATE FROM EMP WHERE DATE_ADD(HIREDATE,INTERVAL 10 YEAR) <= NOW();
17.显示员工的详细资料，按姓名排序；
mysql> SELECT * FROM EMP ORDER BY ENAME ASC;
18.显示员工的姓名和受雇日期，根据其服务年限，将最老的员工排在最前面；
mysql> SELECT ENAME,HIREDATE FROM EMP ORDER BY HIREDATE ASC;
19.显示所有员工的姓名、工作和工资，按工作的降序排序，若工作相同则按工资排序；
mysql> SELECT ENAME,JOB,SAL FROM EMP ORDER BY JOB DESC,SAL DESC;
20.显示所有员工姓名、加入公司的年份和月份，按受雇日期所在月排序，若月份相同则将最早年份的员工排在最前面；
mysql> SELECT ENAME,YEAR(HIREDATE) AS '年',MONTH(HIREDATE) AS '月' FROM EMP ORDER BY MONTH(HIRREDATE) ASC,YEAR(HIREDATE) ASC;
21.显示在一个月为30天的情况所有员工的日工资，忽略余数；
mysql> SELECT ENAME,FLOOR(SAL/30) AS '日薪' FROM EMP;
22.找出(任何年份的)2月受聘的所有员工；
mysql> SELECT * FROM EMP WHERE MONTH(HIREDATE)=2;
23.对于每个员工，显示其加入公司的天数；
mysql> SELECT ENAME,DATEDIFF(NOW(),HIREDATE) FROM EMP;
24.显示姓名字段的任何位置包含“A”的所有员工的姓名；
mysql> SELECT ENAME FROM EMP WHERE ENAME LIKE '%A%';
25.以年月日的方式显示所有员工的服务年限；
SELECT ENAME,FLOOR(DATEDIFF(NOW(),HIREDATE)/365) AS '年', FLOOR(DATEDIFF(NOW(),HIREDATE)%365/30) AS '月', FLOOR(DATEDIFF(NOW(),HIREDATE)%365%30) AS '日' FROM EMP;
SQL练习题二 —— 多表关联
1.列出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资、最高工资
mysql> SELECT D.DEPTNO,D.DNAME,COUNT(E.EMPNO),AVG(SAL),MIN(SAL),MAX(SAL) -> FROM EMP E,DEPT D -> WHERE D.DEPTNO=E.DEPTNO -> GROUP BY D.DEPTNO,D.DNAME -> HAVING COUNT(E.EMPNO)>0;
2.列出薪金比SMITH或者ALLEN多的所有员工的编号、姓名、部门名称、其领导姓名
mysql> SELECT E.EMPNO,E.ENAME,D.DNAME,M.ENAME FROM EMP E,DEPT D,EMP M -> WHERE E.SAL>ANY( -> SELECT SAL -> FROM EMP -> WHERE ENAME IN ('SMITH','ALLEN')) -> AND E.DEPTNO=D.DEPTNO -> AND E.MGR=M.EMPNO;
3.列出所有员工的编号、姓名及其直接上级的编号、姓名，显示的结果按领导年工资的降序排列
mysql> SELECT E.EMPNO,E.ENAME,M.EMPNO,M.ENAME,(M.SAL+NVL(M.COMM,0))*12 INCOME -> FROM EMP E,EMP M -> WHERE E.MGR=M.EMPNO -> ORDER BY INCOME DESC;
4.列出雇佣日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数
mysql> SELECT E.EMPNO,E.ENAME,D.DNAME,D.LOC,TEMP.COUNT -> FROM EMP E,DEPT D,EMP M,( -> SELECT DEPTNO DNO,COUNT(EMPNO) COUNT -> FROM EMP -> GROUP BY DEPTNO) TEMP -> WHERE E.MGR=M.EMPNO -> AND E.HIREDATE<M.HIREDATE -> AND E.DEPTNO=D.DEPTNO -> AND E.DEPTNO=TEMP.DNO;
5.列出部门名称和这些部门的员工信息(数量、平均工资)，同时列出那些没有员工的部门
mysql> SELECT D.DNAME,COUNT(E.EMPNO),ROUND(IFNULL(AVG(E.SAL),0)) -> FROM DEPT D,EMP E -> WHERE E.DEPTNO=D.DEPTNO -> GROUP BY D.DNAME;
6.列出所有CLERK的姓名及其部门名称，部门的人数，工资等级
mysql> SELECT E.ENAME,D.DNAME,TEMP.COUNT,S.GRADE FROM EMP E,DEPT D,( SELECT DEPTNO DNO,COUNT(EMMPNO) COUNT FROM EMP GROUP BY DEPTNO) TEMP,SALGRADE S WHERE JOB='CLERK' AND E.DEPTNO=D.DEPTNO AND D.DEPTNO=TEMP.DNO AND E.SAL BETWEEN S.LOSAL AND S.HISAL;
7.列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数及所在部门名称、位置、平均工资
select temp.job,temp.count,d.dname,d.loc,res.avg from dept d,( select e.job job,count(e.empno) count from emp e group by e.job having min(e.sal)>1500) temp, emp e,( select deptno dno,avg(sal) avg from emp group by deptno) res where e.deptno=d.deptno and e.job=temp.job and e.deptno=res.dno;
