SQL练习

数据库结构

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.返回所有员工的部门名、部门号。

#2.工资水平多于smith的员工信息。

#3.返回员工和所属经理的姓名。

#4.返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名

#5.返回员工姓名及其所在的部门名称。

#6.返回从事clerk工作的员工姓名和所在部门名称。

#7.返回部门号及其本部门的最低工资。

#8.返回销售部(sales)所有员工的姓名。

#9.返回工资水平多于平均工资的员工。

#10.返回与SCOTT从事相同工作的员工。

#11.返回与30部门员工工资水平相同的员工姓名与工资。

#12.返回工资高于30部门所有员工工资水平的员工信息。	

#13.返回部门号、部门名、部门所在位置及其每个部门的员工总数。

#14.返回员工的姓名、所在部门名及其工资。

#15.返回员工的详细信息。(包括部门名)

#16.返回员工工作及其从事此工作的最低工资。

#17.计算出员工的年薪，并且以年薪排序。

#18.返回工资处于第四级别的员工的姓名。

#19.返回工资为二等级的职员名字、部门所在地、和二等级的最低工资和最高工资

#20.工资等级多于smith的员工信息。

参考

#1.返回所有员工的部门名、部门号。
-- SELECT DISTINCT d.DNAME,e.DEPTNO FROM dept d,emp e where e.DEPTNO=d.DEPTNO;

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

#3.返回员工和所属经理的姓名。
-- SELECT e.ENAME mrgName,p.ENAME  FROM emp e ,emp p where e.EMPNO=p.MGR;

#4.返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名
-- SELECT   e.ENAME,p.ENAME FROM emp e ,emp p where e.EMPNO=p.MGR AND e.HIREDATE>p.HIREDATE;

#5.返回员工姓名及其所在的部门名称。
-- SELECT e.ENAME, d.DNAME deptName FROM emp e,dept d where e.DEPTNO=d.DEPTNO;
-- 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 ,dept d where e.JOB='clerk' and e.DEPTNO=d.DEPTNO;
-- SELECT e.ENAME,d.DNAME FROM emp e LEFT JOIN dept d ON e.DEPTNO=d.DEPTNO WHERE e.JOB='CLERK';
#7.返回部门号及其本部门的最低工资。
-- SELECT DEPTNO,MIN(sal) FROM emp GROUP BY DEPTNO;

#8.返回销售部(sales)所有员工的姓名。
-- SELECT e.ENAME FROM emp e ,dept d WHERE e.DEPTNO =d.DEPTNO and d.DNAME='sales';
-- SELECT * FROM emp WHERE DEPTNO=(SELECT DEPTNO FROM dept WHERE dname='SALES');

#9.返回工资水平多于平均工资的员工。
-- SELECT * FROM emp e WHERE e.SAL>(SELECT AVG(SAL) FROM emp );
-- SELECT * FROM emp e,(SELECT avg(sal) avgSal FROM emp) ae WHERE e.SAL>ae.avgSal;

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

#11.返回与30部门员工工资水平相同的员工姓名与工资。
-- SELECT sal FROM emp WHERE DEPTNO=30;
-- SELECT * FROM emp e where e.SAL in(SELECT DISTINCT SAL FROM emp WHERE DEPTNO=30) and e.DEPTNO<>30;

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

#13.返回部门号、部门名、部门所在位置及其每个部门的员工总数。
-- SELECT d.* ,COUNT(e.DEPTNO) FROM emp e,dept d WHERE e.DEPTNO=d.DEPTNO GROUP BY e.DEPTNO;
-- SELECT d.*,count(e.EMPNO) 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,dept d WHERE e.DEPTNO=d.DEPTNO;

#15.返回员工的详细信息。(包括部门名)
-- SELECT e.*,d.DNAME FROM emp e,dept d WHERE e.DEPTNO=d.DEPTNO;

#16.返回员工工作及其从事此工作的最低工资。
-- SELECT e.JOB,min(sal) FROM emp e GROUP BY e.JOB;

#17.计算出员工的年薪，并且以年薪排序。
-- SELECT ENAME,SAL*12 yearSal FROM emp ORDER BY yearSal;

#18.返回工资处于第四级别的员工的姓名。
-- SELECT e.ENAME FROM emp e ,salgrade s WHERE (e.SAL BETWEEN s.LOSAL AND s.HISAL) AND s.GRADE=4 ;
-- SELECT * FROM emp e,salgrade s WHERE e.SAL>=s.LOSAL AND e.SAL<=s.HISAL AND s.GRADE=4;
#19.返回工资为二等级的职员名字、部门所在地、和二等级的最低工资和最高工资
-- SELECT e.ENAME ,d.DNAME,d.LOC,s.LOSAL,s.HISAL 
-- FROM emp e ,dept d,salgrade s 
-- WHERE (e.SAL BETWEEN s.LOSAL AND s.HISAL) AND s.GRADE=2 AND e.DEPTNO=d.DEPTNO;

-- SELECT e.ENAME,d.LOC,s.LOSAL,s.HISAL FROM emp e
-- LEFT JOIN dept d on e.DEPTNO=d.DEPTNO
-- LEFT JOIN salgrade s on e.SAL>=s.LOSAL AND e.SAL<=s.HISAL
-- WHERE s.GRADE=2;
#20.工资等级多于smith的员工信息。
-- SELECT s.GRADE FROM emp e,salgrade s WHERE e.SAL>=s.LOSAL AND e.SAL<=s.HISAL AND e.ENAME='SMITH';
-- 
-- SELECT * FROM emp,salgrade 
-- WHERE SAL>HISAL 
-- and GRADE=(SELECT s.GRADE FROM emp e,salgrade s WHERE e.SAL>=s.LOSAL AND e.SAL<=s.HISAL AND e.ENAME='SMITH');