SQL 基础练习

-- 部门表

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

数据如下：

image.png

-- 员工表

CREATE TABLE EMP
(
    EMPNO INT  PRIMARY KEY,  -- 员工编号
    ENAME VARCHAR(10),  -- 员工名称
    JOB VARCHAR(9), -- 工作
    MGR DOUBLE,    -- 直属领导编号
    HIREDATE DATE,  -- 入职时间
    SAL DOUBLE, -- 工资
    COMM DOUBLE, -- 奖金
    DEPTNO INT, -- 部门号  -- 外键列
    FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO)
);

数据如下：

image.png

-- 练习
-- 1、查询部门和所属的员工

select *
from DEPT e
    left join EMP em
    on e.DEPTNO = em.DEPTNO;

-- 2、工资水平多于smith的员工信息。

select EMP.*
from EMP
where
      SAL>(select SAL from EMP where ENAME='smith');

-- 3、返回员工和所属上级的姓名。

select e1.ENAME,
       e2.ENAME
from EMP e1,
     EMP e2
where e1.MGR=e2.EMPNO;

-- 4、返回雇员的雇佣日期早于其领导雇佣日期的员工及其领导姓名。

select e1.ENAME,
       e2.ENAME
from EMP e1,
     EMP e2
where e1.MGR=e2.EMPNO
  and e1.HIREDATE>e2.HIREDATE;
;

-- 5、返回从事clerk工作的员工姓名和所在部门名称。

select ENAME,DNAME
from EMP e
    left join DEPT D
        on e.DEPTNO = D.DEPTNO
where JOB='CLERK';

-- 6、返回部门号,部门名称及其本部门的最低工资

select e.DEPTNO,DNAME,mins
from DEPT as e,
(select EMP.DEPTNO,min(SAL) as mins
from EMP
group by EMP.DEPTNO) as ds
where e.DEPTNO=ds.DEPTNO
;

-- 7、返回销售部(sales)所有员工的姓名。

select ENAME
from EMP as e
    left join DEPT d
        on e.DEPTNO = d.DEPTNO
where d.DNAME='sales'
;

-- 8、返回与SCOTT从事相同工作的员工。

select ENAME
from EMP
where JOB=
      (select JOB from EMP where ENAME='SCOTT')
  and ENAME!='SCOTT'
;

-- 9、返回与30部门员工工资水平相同(在最低和最高之间)的员工姓名与工资。

select ENAME,SAL
from EMP
where SAL>(select MIN(SAL) from EMP where DEPTNO='30')
  and SAl<(select max(SAL) from EMP where DEPTNO='30')
;

-- 10、返回工资高于30部门所有员工工资水平的员工信息。

select *
from EMP
where SAL>
      (select SAL
      from EMP
      where DEPTNO='30'
      order by SAL DESC
      limit 0,1);

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

select distinct d.DEPTNO,d.DNAME,d.LOC,dc.ce as '员工总数'
from  DEPT d
    left join
    (select DEPTNO,count(EMPNO) ce
            from EMP
            group by DEPTNO) as dc
    on d.DEPTNO=dc.DEPTNO
;

-- 12、返回员工工作及其从事此工作的最低工资。

• 方法一

select JOB,MIN(SAL) as '最低工资'
from EMP
group by JOB
;

• 方法二

select distinct JOB,SAL
from
     (select JOB,
        SAL,
        rank() over (partition by JOB order by SAL) as rank1
        from EMP
        ) as jb
where rank1=1
;