— DROP TABLE IF EXISTS `dept`;
— CREATE TABLE `dept` (
— `deptno` int PRIMARY KEY ,
— `dename` 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 TABLE IF EXISTS emp;
— CREATE TABLE emp (
— empno int PRIMARY KEY,
— ename VARCHAR(10),
— job VARCHAR(9),
— mgr INT,
— hiredare DATE,
— sal DOUBLE,
— comm DOUBLE,
— deptno INT
— );
— 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 TABLE IF EXISTS 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 >0
— 8、找出奖金少于100或没有获得奖金的人
— SELECT * from emp WHERE comm<100 or comm is null
— 9、找出姓名以ABS开头的员工
— SELECT * from emp WHERE ename like ‘a%‘ or ename like ‘b%‘ or ename like ‘s%‘
— 10、找到名字长度为6个字符的员工
— SELECT * from emp WHERE length(ename)=6
— 11、找出名字中不包含r的员工
— SELECT * from emp WHERE ename not like ‘%r%‘
— 12、返回员工的信息，并按照姓名进行排序
— SELECT * from emp ORDER BY ename asc
— 13、按照工作降序、工资升序排列
— SELECT * from emp ORDER BY job desc , sal asc
— 14、计算员工的日薪资
— SELECT ename,sal/30 as ‘avg(sal)‘ from emp
— 15、找出姓名中包含a的员工
— SELECT * from emp WHERE ename LIKE ‘%a%‘
— 16、返回拥有员工的部门名和部门号
— SELECT a.ename,a.deptno,b.dename from emp a LEFT JOIN dept b on a.deptno=b.deptno
— 17、工资水平多于smith的员工信息。
— SELECT *FROM emp WHERE sal > (SELECT sal from emp WHERE ename=‘smith‘)
— 18、返回员工和所属经理的姓名。
— SELECT a.ename,b.ename from emp a LEFT JOIN emp b on a.mgr=b.empno WHERE b.job=‘manager‘
— 19、返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名。
— SELECT a.ename,b.ename from emp a INNER JOIN emp b on a.mgr=b.empno WHERE a.hiredare<b.hiredare
— 20、返回员工姓名及其所在的部门名称。
— SELECT a.ename,b.dename FROM emp a LEFT JOIN dept b on a.deptno=b.deptno
— 21、返回从事clerk工作的员工姓名和所在部门名称。
— SELECT a.ename,b.dename from emp a LEFT JOIN dept b on a.deptno=b.deptno WHERE a.job=‘clerk‘
— 22、返回部门号及其本部门的最低工资。
— SELECT deptno,min(sal) from emp GROUP BY deptno
— 23、返回销售部(sales)所有员工的姓名。
— SELECT a.ename from emp a LEFT JOIN dept b on a.deptno=b.deptno WHERE b.dename=‘sales‘
— 24、返回工资水平多于平均工资的员工。
— SELECT * from emp WHERE sal> (SELECT avg(sal) FROM emp)
— 25、返回与SCOTT从事相同工作的员工。
— SELECT * from emp WHERE job in( SELECT a.job from emp a WHERE a.ename=‘scott‘)
— 26、返回与30部门员工工资水平相同的员工姓名与工资。
— SELECT * from emp WHERE sal in(SELECT sal from emp WHERE deptno=30)
— 27、返回部门号、部门名、部门所在位置及其每个部门的员工总数。
— SELECT a.deptno,b.dename,b.loc,count(*) as cc from emp a LEFT JOIN dept b on a.deptno=b.deptno GROUP BY a.deptno
— 26、返回员工的姓名、所在部门名及其工资。
— SELECT a.ename,b.dename,a.sal from emp a ,dept b WHERE a.deptno=b.deptno
— 27、返回员工的详细信息。(包括部门名)
— SELECT * from emp,dept,salgrade WHERE emp.deptno=dept.deptno AND emp.sal BETWEEN losal AND hisal
— 28、返回员工工作及其从事此工作的最低工资。
— SELECT * ,min(sal) from emp GROUP BY job
— 29、返回不同部门经理的最低工资。
— SELECT * ,min(sal) FROM emp WHERE job=‘manager‘ GROUP BY deptno
— 30、计算出员工的年薪，并且以年薪排序
— SELECT *,sal*12 as nianxin from emp ORDER BY nianxin asc
— 31、返回工资处于第四级别的员工的姓名。
— SELECT * from emp a ,salgrade b WHERE a.sal BETWEEN losal AND hisal AND grade=4
— 32、返回工资为二等级的职员名字、部门所在地、和二等级的最低工资和最高工资
— SELECT * FROM emp a ,salgrade b,dept c WHERE sal BETWEEN losal AND hisal AND grade=2 AND a.deptno=c.deptno
— 33、返回工资为二等级的职员名字、部门所在地、二等级员工工资的最低工资和最高工资
— SELECT ename,loc ,(SELECT min(sal) from emp,salgrade WHERE sal BETWEEN losal and hisal AND grade=2),(SELECT max(sal) from emp,salgrade WHERE sal BETWEEN losal and hisal AND grade=2)
— from emp ,dept ,salgrade WHERE sal BETWEEN losal AND hisal AND grade=2 AND emp.deptno=dept.deptno
— 34、工资等级多于smith的员工信息。
— SELECT * from emp WHERE sal>(SELECT hisal FROM emp ,salgrade WHERE ename=‘smith‘ AND sal BETWEEN losal AND hisal) — select * from emp,salgrade where sal between losal and hisal and grade >(select grade from emp,salgrade where sal between losal
— and hisal and ename = ‘smith‘);