day27:多表查询例题

--  1、按员工编号升序排列不在10号部门工作的员工信息
    SELECT * FROM emp WHERE deptno !=10 ORDER BY empno;
   
-- 2、查询姓名第二个字母不是”A”且薪水大于800元的员工信息，按薪水降序排列
SELECT * FROM emp WHERE ename NOT LIKE '_A%' AND sal>800 ORDER BY sal DESC;
   
-- 3、求每个部门的平均薪水
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno ;
   
-- 4、求各个部门的最高薪水
SELECT deptno,MAX(sal) FROM emp GROUP BY deptno ;
   
-- 5、求每个部门每个职位的最高薪水
SELECT deptno,MAX(sal),job FROM emp GROUP BY deptno,job ;
 
  
-- 6、求平均薪水大于2000的部门编号
SELECT deptno,AVG(sal) avgSal FROM emp GROUP BY deptno HAVING avgSal>2000;

-- 7、将员工薪水大于1200且部门平均薪水大于1500的部门编号列出来，按部门平均薪水降序排列
SELECT deptno,AVG(sal) avgSal FROM emp WHERE sal>1200 GROUP BY deptno HAVING avgSal>1500 ORDER BY avgSal DESC;

-- 8、求最高薪水的员工信息
SELECT * FROM emp WHERE sal = 
    (SELECT MAX(sal) FROM emp);
 
-- 9、求多于平均薪水的员工信息
#1.查询平均薪水  
SELECT AVG(sal) FROM emp
#2.查询员工信息 sal> 1
SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp);

-- 10、求各个部门薪水最高的员工信息 显示部门名称

1.查询每个部门的最高薪水  
SELECT MAX(sal),deptno FROM emp GROUP BY deptno;
2.查询部门最高薪水员工的信息  
SELECT * FROM emp ,
    (SELECT MAX(sal) maxSal,deptno FROM emp GROUP BY deptno) t
    WHERE emp.deptno = t.deptno AND emp.sal = t.maxSal;
3.显示部门名称 
SELECT * FROM dept,
    (
    SELECT emp.*,t.maxSal FROM emp ,
        (SELECT MAX(sal) maxSal,deptno FROM emp GROUP BY deptno) t
        WHERE emp.deptno = t.deptno AND emp.sal = t.maxSal   
    )  tt
    WHERE dept.deptno = tt.deptno;

1.获取各个部门最高工资 部门名称
SELECT MAX(sal),dept.dname,emp.deptno FROM emp,dept WHERE emp.deptno = dept.deptno GROUP BY emp.deptno
2.求各个部门薪水最高的员工信息 显示部门名称

SELECT * FROM emp,
    (
        SELECT MAX(sal) maxSal,dept.dname,emp.deptno FROM emp,dept WHERE emp.deptno = dept.deptno GROUP BY emp.deptno
    ) t
    WHERE emp.deptno = t.deptno AND emp.sal = t.maxSal;

-- 1.  查询和Scott相同部门的员工姓名和雇用日期

#1. 查询Scott部门编号
SELECT deptno FROM emp WHERE  ename = 'scott'
#2.查询当前部门编号下的其他员工 deptno = 1;
SELECT ename,hiredate FROM emp WHERE ename != 'scott'  AND deptno = 
    (SELECT deptno FROM emp WHERE  ename = 'scott')

      
-- 2.    查询工资比公司平均工资高的员工的员工号，姓名和工资。

   SELECT empno , ename ,sal  FROM emp WHERE sal >(SELECT AVG(sal) FROM emp );
      
-- 3.    查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
  
  SELECT * FROM emp, 
    (
        SELECT AVG(sal) avgSal,deptno FROM emp GROUP BY deptno
    ) t
    WHERE emp.deptno = t.deptno AND emp.sal> t.avgSal;
    

     SELECT * FROM emp e 
      JOIN
     (SELECT deptno ,AVG(sal) AS a FROM emp GROUP BY deptno) t WHERE sal > t.a AND e.deptno = t.deptno;
   

-- 4.    查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
    SELECT * FROM emp WHERE deptno IN 
    (SELECT deptno FROM emp WHERE ename LIKE '%u%') ;
   
-- 5.     查询在部门的loc为 DALLAS 的部门工作的员工的员工号，
   SELECT  e.empno,e.ename 
   FROM emp e 
   WHERE e.deptno IN ( SELECT deptno FROM dept WHERE loc='DALLAS');
   
   
-- 6.     查询管理者是King的员工姓名和工资 
   SELECT * FROM emp WHERE mgr  = (
     SELECT empno FROM emp WHERE ename='king');

-- 7.    查询所有部门的名字，loc，员工数量 和 工资平均值 ()
SELECT d.dname , d.loc ,COUNT(e.ename),AVG(sal)
FROM dept d , emp e
WHERE d.deptno = e.deptno
GROUP BY e.deptno ;

-- 8.查询平均工资最低的部门信息

#1.每个部门的平均工资 
SELECT AVG(sal),deptno FROM emp GROUP BY deptno;
#2.查询出来平均工作最低的部门的编号
SELECT  deptno FROM emp GROUP BY deptno ORDER BY AVG(sal) LIMIT 1;
#查询部门信息 = 2
SELECT * FROM dept WHERE deptno = 
    (
        SELECT  deptno FROM emp GROUP BY deptno ORDER BY AVG(sal) LIMIT 1
    )
    
SELECT * FROM dept WHERE deptno = (
    SELECT deptno  FROM emp GROUP BY deptno HAVING AVG(sal) LIKE
        (SELECT MIN(avgSal) FROM 
            (SELECT AVG(sal) avgSal, deptno FROM emp GROUP BY deptno) t)) 

-- 9.查询平均工资最低的部门信息和该部门的平均工资
#1.每个部门的平均工资
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno
#2.最低工资的部门编号
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno ORDER BY AVG(sal) LIMIT 1;
#3.查询平均工资最低的部门信息和该部门的平均工资
SELECT dept.*,t.avgSal FROM dept  , 
    (SELECT deptno ,AVG(sal) avgSal FROM emp GROUP BY deptno ORDER BY avgSal LIMIT 1) t
    WHERE dept.deptno = t.deptno

SELECT  d.*,tt.ttsal FROM dept d JOIN 
    (SELECT AVG(sal) ttsal,deptno  FROM emp GROUP BY deptno HAVING ttsal LIKE
        (SELECT MIN(avgSal) FROM 
            (SELECT AVG(sal) avgSal, deptno FROM emp GROUP BY deptno) t) )tt
            ON d.deptno = tt.deptno;
-- 10.查询平均工资高于公司平均工资的部门有哪些?    
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal)>
            (SELECT AVG(sal) avgSal FROM emp )
-- 11.查询出公司中所有 mgr 的详细信息.

SELECT * FROM emp WHERE empno IN
    (SELECT DISTINCT mgr FROM emp)    
    
-- 12. 各个部门中 最高工资中最低的那个部门的 最低工资是多少    
#1.每个部门的最高工资  
SELECT MAX(sal),deptno FROM emp GROUP BY deptno;
#2.获取最低工资的部门编号 
SELECT deptno FROM emp GROUP BY deptno ORDER BY MAX(sal) LIMIT 1;
#3.获取该部门的最低工资 
SELECT MIN(sal) FROM emp WHERE deptno = 
    (
        SELECT deptno FROM emp GROUP BY deptno ORDER BY MAX(sal) LIMIT 1
    )
        
SELECT MIN(sal) FROM emp WHERE deptno =    
(SELECT deptno FROM emp GROUP BY deptno HAVING MAX(sal)  =
        (SELECT MIN(t.maxSal)FROM
          (SELECT MAX(sal) maxSal ,deptno FROM emp GROUP BY deptno )  t))
          
          
-- 13.查询部门号大于20 或者 姓名中包含 a的 员工信息
SELECT * FROM emp WHERE deptno>20 OR ename LIKE '%a%';

#union 将多条语句的查询结果 合并成一个结果
SELECT * FROM emp WHERE deptno>20      
UNION
SELECT * FROM emp WHERE ename LIKE '%a%';

#union 将多条语句的查询结果 合并成一个结果
CREATE TABLE us(
    id INT PRIMARY KEY AUTO_INCREMENT,
    uname VARCHAR(100),
    gender VARCHAR(100)
);    
INSERT INTO us VALUES
(NULL,'john','male'),
(NULL,'lucy','female'),
(NULL,'jack','male'),
(NULL,'rose','female');

CREATE TABLE ch(
    id INT PRIMARY KEY AUTO_INCREMENT,
    cname VARCHAR(100),
    sex VARCHAR(100)
);
INSERT INTO ch VALUES
(NULL,'张三','男'),
(NULL,'李四','女'),
(NULL,'王五','男');

#查询所有性别为男的信息 
SELECT  id,cname FROM  ch WHERE sex ='男'
UNION ALL
SELECT id,uname FROM us WHERE gender = 'male';