数据库练习题
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练习题一 ——— 单表查询
-
选择部门30中的所有员工;
mysql> SELECT * FROM EMP WHERE DEPTNO=30;
-
列出所有办事员(CLERK)的姓名,编号和部门编号;
mysql> SELECT ENAME,EMPNO,DEPTNO FROM EMP WHERE JOB='CLERK';
-
找出奖金高于工资的员工;
mysql> SELECT ENAME FROM EMP WHERE IFNULL(COMM,0) > SAL;
-
找出奖金高于工资的60%的员工;
mysql> SELECT ENAME FROM EMP WHERE IFNULL(COMM,0) > SAL*0.6;
-
找出部门10中的所有经理(MANAGER)和部门20中所有的办事员(CLERK)的详细资料;
mysql> SELECT * FROM EMP WHERE (JOB='MANAGER' AND DEPTNO=10) OR (JOB='CLERK' AND DEPTNO=20);
-
找出部门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;
-
找出收取奖金的员工的不同工作;
mysql> SELECT DISTINCT JOB FROM EMP WHERE IFNULL(COMM,0)>0;
-
找出不收取奖金或者收取的奖金低于100的员工;
mysql> SELECT * FROM EMP WHERE IFNULL(COMM,0)<=100;
-
找出各月倒数第三天受雇的所有员工;
mysql> SELECT * FROM EMP WHERE LAST_DAY(HIREDATE) - 2 = HIREDATE;
-
找出早于12年前受雇的员工;
mysql> SELECT * FROM EMP WHERE DATE_ADD(HIREDATE,INTERVAL 12 YEAR) < NOW();
-
以首字母大写的方式显示所有员工的姓名;
mysql> SELECT CONCAT(UPPER(LEFT(ENAME,1)),LOWER(SUBSTRING(ENAME,2,LENGTH(ENAME)-1))) FROM EMP;
-
显示正好为5个字符的员工的姓名;
mysql> SELECT ENAME FROM EMP WHERE LENGTH(ENAME)=5;
-
显示不带有“R”的员工姓名;
mysql> SELECT ENAME FROM EMP WHERE ENAME NOT LIKE '%R%';
-
显示所有员工姓名的前三个字符;
mysql> SELECT LEFT(ENAME,3) FROM EMP;
-
显示所有员工的姓名,用“a”替换所有的“A”;
mysql> SELECT REPLACE(ENAME,'A','a') FROM EMP;
-
显示满10年服务年限的员工的姓名和受雇日期;
mysql> SELECT ENAME,HIREDATE FROM EMP WHERE DATE_ADD(HIREDATE,INTERVAL 10 YEAR) <= NOW();
-
显示员工的详细资料,按姓名排序;
mysql> SELECT * FROM EMP ORDER BY ENAME ASC;
-
显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面;
mysql> SELECT ENAME,HIREDATE FROM EMP ORDER BY HIREDATE ASC;
-
显示所有员工的姓名、工作和工资,按工作的降序排序,若工作相同则按工资排序;
mysql> SELECT ENAME,JOB,SAL FROM EMP ORDER BY JOB DESC,SAL DESC;
-
显示所有员工姓名、加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将最早年份的员工排在最前面;
mysql> SELECT ENAME,YEAR(HIREDATE) AS '年',MONTH(HIREDATE) AS '月' FROM EMP ORDER BY MONTH(HIRREDATE) ASC,YEAR(HIREDATE) ASC;
-
显示在一个月为30天的情况所有员工的日工资,忽略余数;
mysql> SELECT ENAME,FLOOR(SAL/30) AS '日薪' FROM EMP;
-
找出(任何年份的)2月受聘的所有员工;
mysql> SELECT * FROM EMP WHERE MONTH(HIREDATE)=2;
-
对于每个员工,显示其加入公司的天数;
mysql> SELECT ENAME,DATEDIFF(NOW(),HIREDATE) FROM EMP;
-
显示姓名字段的任何位置包含“A”的所有员工的姓名;
mysql> SELECT ENAME FROM EMP WHERE ENAME LIKE '%A%';
-
以年月日的方式显示所有员工的服务年限;
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练习题二 —— 多表关联
-
列出至少有一个员工的所有部门编号、名称,并统计出这些部门的平均工资、最低工资、最高工资
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;
-
列出薪金比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;
-
列出所有员工的编号、姓名及其直接上级的编号、姓名,显示的结果按领导年工资的降序排列
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;
-
列出雇佣日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数
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;
-
列出部门名称和这些部门的员工信息(数量、平均工资),同时列出那些没有员工的部门
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;
-
列出所有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;
-
列出最低薪金大于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;
-
列出在部门SALES工作的员工的姓名、基本工资、雇佣日期、部门名称,假定不知道该部门的部门编号
mysql> SELECT E.ENAME,E.SAL,E.HIREDATE,D.DNAME -> FROM EMP E,DEPT D -> WHERE D.DNAME='SALES' -> AND D.DEPTNO=E.DEPTNO;
-
列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,公司的工资等级
mysql> SELECT E.EMPNO,E.ENAME,E.JOB,E.SAL,D.DNAME,M.ENAME,S.GRADE -> FROM EMP E,DEPT D,EMP M,SALGRADE S -> WHERE E.SAL>( -> SELECT AVG(SAL) FROM EMP) -> AND E.DEPTNO=D.DEPTNO -> AND E.MGR=M.EMPNO -> AND E.SAL BETWEEN S.LOSAL AND S.HISAL;
-
列出与SCOTT从事相同工作的所有员工及部门名称,部门人数
mysql> SELECT E.ENAME,E.ENAME,E.JOB,D.DNAME ,TEMP.COUNT FROM EMP E,DEPT D,( SELECT DEPTNO DNO,COUNT(EMPNO) COUNT FROM EMP GROUP BY DEPTNO) TEMP WHERE E.JOB=( SELECT JOB FROM EMP WHERE ENNAME='SCOTT') AND E.ENAME<>'SCOTT' AND E.DEPTNO=D.DEPTNO AND TEMP.DNO=E.DEPTNO;
-
列出公司各个工资等级雇员的数量、平均工资
mysql> SELECT S.GRADE,COUNT(E.EMPNO),AVG(SAL) -> FROM SALGRADE S,EMP E -> WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL -> GROUP BY S.GRADE,S.LOSAL,S.HISAL -> ORDER BY S.GRADE;
-
列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称
mysql> SELECT E.EMPNO,E.ENAME,E.SAL,D.DNAME FROM EMP E,DEPT D WHERE E.SAL>ALL( SELECT SAL FROMM EMP WHERE DEPTNO=30) AND E.DEPTNO=D.DEPTNO;
-
列出在每个部门工作的员工数量、平均工资和平均服务期限
mysql> SELECT D.DEPTNO,D.DNAME,COUNT(E.EMPNO),AVG(E.SAL), AVG(DATEDIFF(SYSDATE(),HIREDATE)/365) YEAR FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO GROUP BY D.DEPTNO,D.DNAME;
-
列出所有员工的姓名、部门名称和工资
mysql> SELECT E.ENAME,D.DNAME,E.SAL -> FROM EMP E,DEPT D -> WHERE E.DEPTNO=D.DEPTNO;
-
列出所有部门的详细信息和部门人数
mysql> SELECT D.DEPTNO,D.DNAME,D.LOC,COUNT(E.EMPNO) -> FROM DEPT D,EMP E -> WHERE E.DEPTNO=D.DEPTNO -> GROUP BY D.DEPTNO,D.DNAME,D.LOC;
-
列出各种工作的最低工资及从事此工作的雇员姓名
mysql> SELECT E.ENAME,E.JOB,E.SAL FROM EMP E,( SELECT MIN(SAL) MIN,JOB FROM EMP GROUP BY JOB) TEMP WHERE E.SAL=TEMP.MIN AND E.JOB=TEMP.JOB;
-
列出各个部门的MANAGER的最低薪金、姓名、部门名称、部门人数
mysql> SELECT E.ENAME,E.SAL,D.DNAME,RES.COUNT FROM DEPT D,EMP E,( SELECT DEPTNO DNO,MIN(SAL)MIN FROM EMP WHERE JOB='MANAGER' GROUP BY DEPTNO) TEMP,( SELECT DEPTNO DNO,COUNT(EMPNO) COUNT FROM EMP GROUP BY DEPTNO) RES WHERE E.DEPTNO=TEMP.DNO AND E.SAL=TEMP.MIN AND E.JOB='MANAGER' AND D.DEPTNO=E.DEPTNO AND RES.DNO=D.DEPTNO;
-
列出所有员工的年工资,所在部门名称,按年薪从低到高排序
mysql> SELECT E.ENAME,E.SAL*12 ALLSAL,D.DNAME FROM EMP E,DEPT D WHERE D.DEPTNO=E.DEPTNO ORDER BY ALLSAL ASC;
-
列出某个员工的上级主管及所在部门名称,并要求出这些主管中的薪水超过3000
mysql> SELECT DISTINCT M.ENAME,D.DNAME,M.SAL FROM EMP E,EMP M,DEPT D WHERE E.MGR=M.EMPNO AND M.DEPTNO=D.DEPTNO AND M.SAL>3000;
-
求出部门名称中带‘S’字符的部门员工的工资合计、部门人数
mysql> SELECT D.DNAME,SUM(E.SAL),COUNT(E.EMPNO) -> FROM DEPT D,EMP E -> WHERE E.DEPTNO=D.DEPTNO -> AND D.DNAME LIKE '%S%' -> GROUP BY D.DNAME;
-
给任职日期超过30年或者87年雇佣的雇员加薪,加薪原则:10部门增长10%,20部门增长20%,30部门增长30%,以此类推
UPDATE EMP SET SAL=(1+DEPTNO/100)*SAL WHERE DATEDIFF(NOW(),HIREDATE)/365>30 OR YEAR(HIREDATE)=1987