sql整理之习题..

/*
1.查出至少有一个员工的部门,显示部门编号,部门名称,部门位置,部门人数.
*/


/*内连接:*/
/*方言*/
SELECT e.dmtid 部门编号, d.dmtname 部门名称, d.location 部门位置 , count(e.dmtid) 部门人数
FROM employee e, dmt d
WHERE e.dmtid=d.dmtid
GROUP BY d.dmtid;


/*标准*/
SELECT e.dmtid 部门编号, d.dmtname 部门名称, d.location 部门位置 , count(e.dmtid) 部门人数
FROM employee e INNER JOIN dmt d
WHERE e.dmtid=d.dmtid
GROUP BY d.dmtid;


/*自然:可读性低,因为不知道判断的是哪个字段(哪一列)*/
SELECT e.dmtid 部门编号, d.dmtname 部门名称, d.location 部门位置 , count(e.dmtid) 部门人数
FROM employee e NATURAL JOIN dmt d
GROUP BY d.dmtid;


/*因为内连接有一个特写,不符合判断条件的数据,不会被查询出来,也可以按照下面的方式写*/
/*第一步*/
SELECT * FROM dmt;


SELECT dmtid,count(dmtid) FROM employee e GROUP BY dmtid;

/*第二步,方言式内连接,会自动将不满足条件的数据去除(因为如果employee表中的数据,与dmt表中无匹配的dmtid,那么对于的dmtid就属于无人员部门*/
SELECT * FROM dmt,(SELECT dmtid,count(dmtid) FROM employee e GROUP BY dmtid) c1;

/*第三步:给表dmt设定别名d,通过设定判断条件,去除笛卡尔积*/
SELECT * FROM dmt d,(SELECT dmtid,count(dmtid) FROM employee e GROUP BY dmtid) c1
WHERE d.dmtid=c1.dmtid;

/*第四步:通过表别名.列名别名的方式,去除重复的dmtid列*/
SELECT d.*, c1.cnt
FROM dmt d ,(SELECT dmtid,count(dmtid) cnt FROM employee) c1
WHERE d.dmtid=c1.dmtid;


/*2,列出工资比德莱文高的所有员工**/
/*先获得德莱文的工资,然后使用子查询的方式,将单行单列的结果,作为where的判断条件*/
SELECT * FROM employee e WHERE pay>(SELECT pay FROM employee WHERE name='德莱文');


/*3,列出所有员工的姓名及其直接上级的姓名
列: 员工姓名,上级姓名
表: emp e1,emp e2  e1表示员工表,e2表示上级表
条件: 员工的managerId=上级的id


*/
/*第一步:一次查询是查询不出来的,所以查这个表两次,通过给不同的别名来区分,e1代表员工,e2代表上级*/
SELECT * FROM employee e1, employee e2;
/*第二步:所以条件为员工的上级id要等于上级的id*/
SELECT e1.name 员工名,e2.name 上级名 FROM employee e1, employee e2 WHERE e1.managerId=e2.id;
/*第三步:但是需要所有员工,这里只能查询到有上级的员工,但是拳头公司没有上级,所以没显示出来*/
/*如果排序后面没有指定e1.id而是简单的id的话,会报错:Column 'id' in order clause is ambiguous,意思:列id在排序中不明确,也就是不知道要使用哪个表的id列*/
SELECT e1.name 员工名,ifnull(e2.name,'无敌') 上级名 FROM employee e1 LEFT JOIN employee e2 on e1.managerId=e2.id ORDER BY e1.id DESC ;

/*4,列出受雇日期早于直接上级的所有员工的编号,姓名,部门名称
列: 编号,姓名,部门名称
表: 员工表e,上级表m dmt表d
条件: e.hiredate<m.hiredate
所以这句话涉及到三张表
思路;
1)先不查部门名称,只查部门编号.
列:e.id,e.name,e.dmtid
表:employee e,employee m
条件:e.managerId=m.id,e.hiredate<m.hiredate
*/
/*标准写法*/
SELECT e.id 编号,e.name 姓名,d.dmtname 部门名称
FROM employee e LEFT JOIN employee m
ON e.managerId=m.id LEFT JOIN dmt d ON e.dmtid=d.dmtid
WHERE e.hiredate<m.hiredate ;

/*方言mysql可以使用的*/
SELECT e.id 编号,e.name 姓名,d.dmtname 部门名称
FROM employee e,employee m,dmt d
WHERE e.managerId=m.id and e.dmtid=d.dmtid AND e.hiredate<m.hiredate;

/*5,列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门
1)先将后半句去掉
列:*
表:employee e ,dmt d

条件: e.dmtid=d.dmtid

outer 是可以省略的
*/
SELECT * FROM employee e RIGHT OUTER JOIN dmt d ON  e.dmtid=d.dmtid;

/*6,列出所有adc的姓名及其部门名称,部门的人数
列:姓名,部门名称,部门人数(分组+聚合函数count)
表:employee e,dmt d
条件:job='adc'
*/
SELECT e.name 姓名,dd.部门名称,dd.部门人数 FROM employee e INNER JOIN dmt d ON e.dmtid=d.dmtid  INNER JOIN (SELECT e.dmtid,count(e.dmtid) 部门人数,d.dmtname 部门名称 FROM employee e JOIN dmt d ON e.dmtid=d.dmtid GROUP BY dmtid) dd ON e.dmtid=dd.dmtid WHERE e.job='adc' ;
/*先写出下面这两句,然后以拼接就好了*/
SELECT e.name 姓名 FROM employee e INNER JOIN dmt d ON e.dmtid=d.dmtid  WHERE job='adc';
SELECT e.dmtid,count(e.dmtid) 部门人数,d.dmtname 部门名称 FROM employee e JOIN dmt d ON e.dmtid=d.dmtid GROUP BY dmtid;

/*7,列出最低薪金大于15000的各种工作及从事此工作的员工人数
列:job,人数(分组,count)
表:employee e, employee jcount
条件:min(pay)>15000 有聚合函数的条件语句,就用having,写在分组后
分组:job
*/
SELECT DISTINCT e.job 工作,jcount.工作人数 FROM employee e INNER JOIN (SELECT jobs.job,count(jobs.job) 工作人数 FROM employee jobs GROUP BY job) jcount ON e.job=jcount.job HAVING min(pay) > 100;

SELECT job,count(*)
FROM employee e
GROUP BY e.job
HAVING min(e.pay) > 15000;

/*8,列出在德玛西亚工作的员工的姓名,假定不知道德玛西亚的部门编号
列:姓名
表:employee e,dmt(根据部门名称查到部门编号) d
条件:dmtid=德玛西亚的id===> d.dmtname='德玛西亚' d.dmtid=e.dmtid
*/
SELECT e.name FROM employee e  WHERE e.dmtid=(SELECT d.dmtid FROM dmt d  WHERE d.dmtname='德玛西亚');

/*9,列出薪金高于公司平均薪金的所有员工信息,所在部门名称,上级领导,工资等级
列:所有信息 *,部门名称 d.dmtname 上级领导 m.name  工资等级pl.level
*/
SELECT e.*,d.dmtname 部门名称,m.name 上级领导,pl.level
FROM employee e
  LEFT OUTER JOIN dmt d ON e.dmtid=d.dmtid
  LEFT OUTER JOIN employee m ON e.managerId=m.id
  LEFT JOIN paylevel pl ON e.pay BETWEEN pl.lowest and pl.heightest
WHERE e.pay>(SELECT avg(pay) FROM employee);

/*10,列出与盖伦从事相同工作的所有员工及部门名称
列:e.name d.dmtname
表:employee e , dmt d
条件:job=(盖伦的job)
*/

SELECT e.name,d.dmtname FROM employee e INNER JOIN dmt d ON e.dmtid=d.dmtid WHERE job=(SELECT job FROM employee WHERE name='盖伦');
SELECT job,count(*) FROM employee GROUP BY job HAVING job='战士';


/*11,列出薪金高于在部门30工作的所有员工的薪金的员工名称和薪金,部门名称
列:e.name e.pay d.dmtname
表:employee e, dmt d
条件:e.pay > (d.dmtid=30这个部门工作的员工中薪金的最大值)
*/
SELECT e.name,e.pay,d.dmtname FROM employee e INNER JOIN dmt d ON e.dmtid=d.dmtid WHERE e.pay>(SELECT max(pay) FROM employee WHERE dmtid=30);

/*这里的子查询(SELECT pay FROM employee WHERE dmtid=30)会得到一个pay的集合,所以前面可以用all来表示,大于所有,也就是上面大于最大值的意思了*/
SELECT e.name,e.pay,d.dmtname FROM employee e INNER JOIN dmt d ON e.dmtid=d.dmtid WHERE e.pay> ALL (SELECT pay FROM employee WHERE dmtid=30);


/*12,列出每个部门的员工数量,平均工资
首先读到了每个部门:需要分组
列:分组后的数量count(*),分组后的平均值avg(pay)
表:employee
条件:无
*/

SELECT count(*),avg(pay) 员工数量 FROM employee GROUP BY dmtid;

/*13,查出年份,利润,年度增长比
首先,这特么的是数学题,先百度一下公式:(后一年-前一年)/前一年
列:year 年利润_百万
表:st_year
*/

SELECT *  FROM st_year sy1 INNER JOIN st_year sy2;
SELECT sy1.*,sy2.年利润_百万,concat((sy1.年利润_百万-sy2.年利润_百万)/sy2.年利润_百万* 100 , '%') 增长比  FROM st_year sy1 LEFT OUTER JOIN st_year sy2
  /*是sy1=sy2+1而不是sy1+1=sy2的原因:这样能让格式为:2011 150 100 这样就是大的在前面,可以减去后面的100*/
ON sy1.year=sy2.year+1 ORDER BY sy1.year;

/

/*1. 查出至少有4个员工的部门。显示部门编号、部门名称、部门位置、部门人数。列：d.deptno, d.dname, d.loc, 部门人数表：dept d, emp e条件：e.deptno=d.deptno*/SELECT d.*, z1.cnt FROM dept d, (SELECT deptno, COUNT(*) cnt FROM emp GROUP BY deptno HAVING cnt > 4) z1WHERE d.deptno = z1.deptno;/*2. 列出所有员工的姓名及其直接上级的姓名。列：员工姓名、上级姓名表：emp e, emp m条件：员工的mgr = 上级的empno*/SELECT * FROM emp e, emp mWHERE e.mgr=m.empnoSELECT e.ename, IFNULL(m.ename, 'BOSS') 领导FROM emp e LEFT OUTER JOIN emp mON e.mgr=m.empno;/*3. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。列：e.empno, e.ename, d.dname表：emp e, emp m, dept d条件：e.hiredate<m.hiredate思路：1. 先不查部门名称，只查部门编号!列：e.empno, e.ename, e.deptno表：emp e, emp m条件：e.mgr=m.empno, e.hiredate<m.hireadate*/SELECT e.empno, e.ename, e.deptnoFROM emp e, emp mWHERE e.mgr=m.empno AND e.hiredate<m.hiredate;SELECT e.empno, e.ename, d.dnameFROM emp e, emp m, dept dWHERE e.mgr=m.empno AND e.hiredate<m.hiredate AND e.deptno=d.deptno;/*4. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。列：* 表：emp e, dept d条件：e.deptno=d.deptno*/SELECT *FROM emp e RIGHT OUTER JOIN dept dON e.deptno=d.deptno;/*5. 列出最低薪金大于15000的各种工作及从事此工作的员工人数。列：job, count(*)表：emp e条件：min(sal) > 15000分组：job*/SELECT job, COUNT(*)FROM emp eGROUP BY jobHAVING MIN(sal) > 15000;/*6. 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。列：e.ename表：emp条件：e.deptno=(select deptno from dept where dname='销售部')*/SELECT *FROM emp eWHERE e.deptno=(SELECT deptno FROM dept WHERE dname='销售部');/*7. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。列：* 表：emp e条件：sal>(查询出公司的平均工资)*/SELECT e.*, d.dname, m.ename, s.gradeFROM emp e, dept d, emp m, salgrade sWHERE 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;---------------SELECT e.*, d.dname, m.ename, s.gradeFROM   emp e LEFT  JOIN dept d ON e.deptno=d.deptno        LEFT  JOIN emp m ON e.mgr=m.empno        LEFT  JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisalWHERE e.sal>(SELECT AVG(sal) FROM emp);SELECT * FROM emp;SELECT * FROM dept;SELECT * FROM salgrade;/*8.列出与庞统从事相同工作的所有员工及部门名称。列：e.*, d.dname表：emp e, dept d条件：job=(查询出庞统的工作)*/SELECT e.*, d.dnameFROM emp e, dept dWHERE e.deptno=d.deptno AND job=(SELECT job FROM emp WHERE ename='庞统');/*9.列出薪金高于在部门30工作的所有员工的薪金　的员工姓名和薪金、部门名称。列：e.ename, e.sal, d.dname表：emp e, dept d条件；sal>all (30部门薪金)*/SELECT e.ename, e.sal, d.dnameFROM emp e, dept dWHERE e.deptno=d.deptno AND sal > ALL (SELECT sal FROM emp WHERE deptno=30);

/*

每门功课成绩最好的前两名

列：stuname,subject,stupid,score表：scoreOne条件: score=(查询出各门功课成绩的前两名)

*/

SELECT *FROM scoreOne s1WHERE (SELECT count(1)       FROM scoreOne s2       WHERE s2.subject = s1.subject AND s2.score >= s1.score) <= 2;