练习与思考

#1.返回拥有员工的部门名、部门号。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

INSERT INTO DEPT VALUES(10,'财务部','北京'),(20,'市场部','上海'),(30,'销售部','广州'),(40,'运营部','深圳');
CREATE TABLE EMP( -- 员工表
EMPENO INT PRIMARY KEY ,#员工编号
ENAME VARCHAR(10),#员工姓名
JOB VARCHAR(20),#员工工作
MGR INT,#员工直属领导
HIREDATA 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','保洁工作',7902,"1980-12-17",800,NULL,20);
INSERT INTO emp VALUES(7499,'allen','销售工作',7698,'1981-02-20',1600,300,30);
INSERT INTO emp VALUES(7521,'ward','销售工作',7698,'1981-02-22',1250,500,30);
INSERT INTO emp VALUES(7566,'jones','管理工作',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp VALUES(7654,'martin','销售工作',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp VALUES(7698,'blake','管理工作',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp VALUES(7782,'clark','管理工作',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp VALUES(7788,'scott','策划工作',7566,'1987-07-03',3000,NULL,20);
INSERT INTO emp VALUES(7839,'king','大BOSS',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp VALUES(7844,'turner','销售工作',7698,'1981-09-08',1500,0,30);
INSERT INTO emp VALUES(7876,'adams','保洁工作',7788,'1987-07-13',1100,NULL,20);
INSERT INTO emp VALUES(7900,'james','保洁工作',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp VALUES(7902,'ford','策划工作',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp VALUES(7934,'miller','保洁工作',7782,'1981-01-23',1300,NULL,10);
INSERT INTO emp VALUES(7901,'james1','保洁工作',7698,'1981-12-03',950,NULL,20);


CREATE TABLE SALGRADE(
GRADE INT,#等级
losal DOUBLE,#最低工资
hisal DOUBLE   #最高工资
);

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

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

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

#3.返回员工和所属经理的姓名。
SELECT  E.`ENAME` 经理姓名,M.员工姓名  FROM EMP E JOIN (SELECT MGR,ENAME 员工姓名 FROM EMP) M WHERE M.MGR = E.`EMPENO` AND E.`JOB` = '管理工作';
SELECT EM.`ENAME` 经理姓名,EP.`ENAME` 员工姓名  FROM EMP EM,EMP EP WHERE EM.`EMPENO` = EP.`MGR` AND EM.`JOB`= '管理工作';

#4.返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名
SELECT EP.`ENAME`,EM.`ENAME` FROM EMP EM,EMP EP WHERE EM.`MGR` = EP.`EMPENO` AND EM.`HIREDATA` < EP.`HIREDATA` AND EP.`JOB` = '管理工作' ;

#5.返回员工姓名及其所在的部门名称。
SELECT M.`ENAME`,D.`DNAME` FROM EMP M,DEPT D WHERE M.`DEPTNO` = D.`DEPTNO`;

#6.返回从事"保洁工作"的员工姓名和所在部门名称。
SELECT E.`ENAME`,D.`DNAME` FROM EMP E,DEPT D WHERE E.`DEPTNO` = D.`DEPTNO` AND E.`JOB` ='保洁工作' ;

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

#8.返回"销售部"所有员工的姓名。
SELECT * FROM DEPT,EMP WHERE EMP.`DEPTNO` = DEPT.`DEPTNO` AND DEPT.`DNAME` = '销售部';

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

 #如果不包含自己
SELECT E1.* FROM EMP E1,(SELECT EMPENO,JOB FROM EMP WHERE ENAME = 'SCOTT') E2 WHERE E1.`JOB` = E2.JOB AND E1.`EMPENO` = E2.EMPENO;

#11.返回与30部门员工工资相同的员工姓名与工资。
SELECT EMP.`ENAME`FROM EMP WHERE DEPTNO != 30 AND SAL IN (SELECT SAL FROM EMP WHERE DEPTNO = 30);

# 如果要同时显示30部门的员工信息与和其他部门相同的信息
SELECT E1.*,E2.ENAME 30部门的姓名,E2.SAL 30部门的工资 FROM EMP E1,(SELECT * FROM EMP WHERE DEPTNO = 30) E2 WHERE E2.SAL = E1.`SAL` AND E1.`DEPTNO` !=30;

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

SELECT * FROM emp WHERE sal > (SELECT MAX(SAL) FROM EMP WHERE DEPTNO =30);

#13.返回部门号、部门名、部门所在位置及其每个部门的员工总数。
SELECT D.`DEPTNO`,D.`DNAME`,D.`LOC`,COUNT(E.`DEPTNO`) 人数 FROM DEPT D,EMP  E WHERE E.`DEPTNO` = D.`DEPTNO` GROUP BY E.`DEPTNO`; 

#14.返回员工的姓名、所在部门名及其工资。
SELECT EMP.`ENAME`,DEPT.`DNAME`,EMP.`SAL` FROM EMP,DEPT WHERE EMP.`DEPTNO` = DEPT.`DEPTNO`;-- AND EMP.`JOB` NOT IN('管理工作','大BOSS');

#15.返回员工的详细信息。(包括部门名)
SELECT E.*,D.* FROM EMP E,DEPT D WHERE E.`DEPTNO` = D.`DEPTNO`;

#16.返回员工工作及其从事此工作的最低工资。
SELECT MIN(SAL),JOB FROM EMP GROUP BY JOB;-- 从事工资最低的工资
SELECT  E1.`ENAME`,E1.`JOB`,E1.`SAL`,E2.最低的工资 FROM EMP E1  JOIN (SELECT MIN(SAL) 最低的工资,JOB FROM EMP GROUP BY JOB) E2 WHERE E1.`JOB` = E2.JOB ORDER BY SAL DESC;

-- 分组工作 来工作最低的工资 然后和主表的工作链接 

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

#18.返回工资处于第四级别的员工的姓名。
SELECT EM.`ENAME`,EM.`SAL` FROM EMP EM,SALGRADE  S WHERE EM.`SAL` BETWEEN S.`losal`AND S.`hisal` AND S.`GRADE` = 4;

#19.返回工资为二等级的职员名字、部门所在地、和二等级的最低工资和最高工资
SELECT EM.`ENAME`,DE.`LOC` ,S.`losal` 最低工资 ,S.`hisal` 最高工资 FROM EMP EM,DEPT DE,SALGRADE S WHERE EM.`DEPTNO` = DE.`DEPTNO` AND EM.`SAL` BETWEEN S.`losal`AND S.`hisal` AND S.`GRADE` = 2;

#20.工资等级 多于smith的员工信息
-- SELECT s.`GRADE` FROM EMP E,SALGRADE S  WHERE E.`ENAME`= 'smith' AND E.`SAL` BETWEEN S.`losal` AND S.`hisal`; -- 查询smith 等级是 1 

-- 可以显示等级得加员工信息的 用等级做比较
SELECT EM.*,S2.`GRADE` 工资等级 FROM EMP EM ,SALGRADE S2 WHERE EM.`SAL` BETWEEN S2.`losal` AND S2.`hisal` AND S2.`GRADE` > (SELECT S.`GRADE` FROM EMP E,SALGRADE S  WHERE E.`ENAME`= 'smith' AND E.`SAL` BETWEEN S.`losal` AND S.`hisal`); 


-- 只能显示员工的信息 用工资 做比较
SELECT * FROM EMP  WHERE SAL > (SELECT S.`hisal` FROM EMP E,SALGRADE S  WHERE E.`ENAME`= 'smith' AND E.`SAL` BETWEEN S.`losal` AND S.`hisal`);