数据库实验

USE dept_emp; 

-- 单表查询
-- 1.选择部门30中的所有员工
    SELECT * FROM `emp_list` WHERE ename ;
-- 2.列出所有办事员的姓名，编号和部门编号
    SELECT emp_list.ename,emp_list.empno,emp_list.deptno
    FROM emp_list
    WHERE job = '办事员';
-- 3.找出佣金高于薪金的员工
    SELECT *
    FROM emp_list
    WHERE mgr + sal > sal;
-- 4.找出没有佣金的员工
    SELECT *
    FROM emp_list
    WHERE emp_list.mgr is null;
-- 5.找出佣金高于薪金的60%的员工
    SELECT *
    FROM emp_list
    WHERE emp_list.mgr > emp_list.sal * 0.6;
-- 6.找出部门10中所有人事部长和部门20中所有办事员的详细资料
    SELECT emp_list.job,emp_list.deptno,emp_list.ename,emp_list.empno,emp_list.comm,emp_list.hiredate
    FROM emp_list
    WHERE job = '人事部长' AND deptno = 10 and 20;
-- 7.找出收取佣金的员工
    SELECT *
    FROM emp_list
    WHERE emp_list.mgr is not null;
-- 8.找出不收取佣金或收取的佣金低于100的员工
    SELECT *
    FROM emp_list
    WHERE emp_list.mgr IS NULL OR emp_list.mgr < 100;
-- 9.找出姓张的员工的信息
    SELECT emp_list.`ename`
    FROM emp_list
    WHERE `ename` LIKE '张%'
-- 10.显示员工的姓名和受雇日期,新的员工排在最前面
    SELECT `ename`, `hiredate`
    FROM emp_list
    ORDER BY hiredate DESC;
-- 11.按工作的降序排序,若工作相同则按薪金排序
    SELECT *
    FROM emp_list
    ORDER BY emp_list.job DESC , emp_list.sal;
-- 12.查出不姓刘的员工的信息
    SELECT emp_list.`ename`
    FROM emp_list
    WHERE `ename` NOT LIKE  '刘%';
-- 13.取出姓李的员工
    SELECT emp_list.`ename`
    FROM emp_list
    WHERE `emp_list`.ename LIKE '李%'
-- 多表查询
-- 1. 查询出每一位雇员的编号、姓名、职位、部门名称、位置
    SELECT emp_list.empno,emp_list.ename,emp_list.job,dept_list.dname
    FROM  emp_list CROSS JOIN dept_list;
-- 2.要求查询出每一位雇员的姓名、职位、领导的姓名
    SELECT ename AS employee_name, job, dname AS leader_name  
    FROM emp_list  
    JOIN dept_list l ON e.leader_id = l.id;
-- 3.查询出每个雇员的编号、姓名、基本工资、职位、领导的姓名、部门名称及位置。
    SELECT emp_list.empno,emp_list.ename,emp_list.mgr,emp_list.job,emp_list.sal,dept_list.dname,dept_list.loc
    FROM emp_list CROSS JOIN dept_list
-- 4.列出在部门"销售部"工作的员工的姓名，假定不知道销售部的部门编号。 
    SELECT emp_list.ename
    FROM emp_list CROSS JOIN dept_list
    WHERE dept_list.dname = (SELECT dept_list.deptno FROM dept_list WHERE dept_list.dname = '销售部');
-- 5.列出与"李兴"从事相同工作的所有员工。
    SELECT emp_list.ename
    FROM emp_list
    WHERE emp_list.job = (SELECT emp_list.job FROM emp_list WHERE ename = '李兴');
-- 6.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。 
    SELECT e.ename,e.sal FROM emp e WHERE e.sal >ALL(SELECT el.sal FROM emp el WHERE deptno=30);
-- 7.列出受雇日期早于其直接上级的所有员工。
    SELECT e.* FROM emp e WHERE ((SELECT el.hiredate FROM emp el WHERE e.mgr=el.empno)-e.hiredate)>0;
-- 分组函数查询
-- 1.按照部门编号分组，求出每个部的人数，平均工资
    SELECT deptno,COUNT(empno),AVG(sal)
    FROM emp
    GROUP BY deptno;
-- 2.按照职位分组，求出每个职位的最高和最低工资
    SELECT job,MAX(sal),MIN(sal)
    FROM emp
    GROUP BY job;
-- 3.计算出每个各职位的平均工资
    SELECT job,AVG(sal)
    FROM emp
    GROUP BY job
-- 4.查询出每个部门的名称、部门的人数、平均工资
    SELECT d.dname,COUNT(e.empno),AVG(e.sal)
    FROM dept d,emp e 
    WHERE e.deptno = d.deptno
    GROUP BY d.dname;
-- left outer join on
    SELECT d.dname,COUNT(e.empno),AVG(e.sal)
    FROM dept d LEFT OUTER JOIN emp e
    ON e.deptno = d.deptno
    GROUP BY d.dname ORDER BY e.empno DESC 
-- 5.要求统计出每个部门的详细信息，并且要求这些部门的平均工资高于2000；
    SELECT d.dname,AVG(e.sal)a
    FROM dept d LEFT OUTER JOIN emp e
    ON d.deptno = e.deptno
    GROUP BY d.dname HAVING a>2000;
-- 6.要求查询出工资比华仔还要高的全部雇员信息
    SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='华仔');
-- 7.要求查询出高于公司平均工资的全部雇员信息
    SELECT * FROM emp WHERE sal>(
    SELECT AVG(sal) FROM emp);
-- 8.查询出每个部门的编号、名称、位置、部门人数、平均工资
    SELECT d.dname,COUNT(e.deptno) c,AVG(e.sal)
    FROM dept d LEFT OUTER JOIN emp e
    ON d.deptno = e.deptno 
    GROUP BY d.dname ORDER BY c;
-- 9.列出至少有一个员工的所有部门。 
    SELECT d.deptno, d.dname FROM dept d WHERE 
    (SELECT COUNT(e.empno) FROM emp e WHERE e.deptno=d.deptno)>1;
-- 等级查询
-- 1.查询每个工资等级各有多少员工
    SELECT s.grade,COUNT(*) 
    FROM emp e JOIN salgrade s 
    ON e.sal BETWEEN s.losal AND hisal 
    GROUP BY s.grade;
-- 2.查询部门中（所有人）的平均工资等级
    SELECT e.deptno,AVG(s.grade) avggrade 
    FROM emp e JOIN salgrade s 
    ON e.sal BETWEEN s.losal AND hisal 
    GROUP BY e.deptno 
    ORDER BY e.deptno;