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