综合练习

 Oracle从入门到总裁:​​​​​​https://blog.csdn.net/weixin_67859959/article/details/135209645

编写数据库脚本

-- 删除数据表
drop table purcase purge;
drop table product purge;
drop table customer purge;
-- 创建数据表
create table product(
	productid    varchar2(5),
    productname    varchar2(20)    not null,
    unitprice    number,
    category    varchar2(50),
    provider    varchar2(50)
);
create table customer(
	customerid    varchar2(5),
    name    varchar2(20)    not null,
    location    varchar2(50)
);
create table purcase(
	customerid    varchar2(5),
    productid    varchar2(5),
    quantity    number
);

SQL> -- 删除数据表
SQL> drop table purcase purge;
drop table purcase purge
           *
第 1 行出现错误:
ORA-00942: 表或视图不存在


SQL> drop table product purge;
drop table product purge
           *
第 1 行出现错误:
ORA-00942: 表或视图不存在


SQL> drop table customer purge;

表已删除。

SQL> -- 创建数据表
SQL> create table product(
  2  productid    varchar2(5),
  3      productname    varchar2(20)    not null,
  4      unitprice    number,
  5      category    varchar2(50),
  6      provider    varchar2(50)
  7  );

表已创建。

SQL> create table customer(
  2  customerid    varchar2(5),
  3      name    varchar2(20)    not null,
  4      location    varchar2(50)
  5  );

表已创建。

SQL> create table purcase(
  2  customerid    varchar2(5),
  3      productid    varchar2(5),
  4      quantity    number
  5  );

表已创建。

SQL>

列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数

确定要使用的数据表

emp 表：员工的编号、姓名

dept 表：部门名称、部门位置

emp 表：人数。emp 表：领导

确定已知的关联字段

员工与领导：emp.mgr=memp.empno

员工与部门：emp.deptno = dept.deptno

第一步：emp 表进行自身关联，而后除了设置消除笛卡尔积的条件，还要判断受雇日期

SQL> SELECT e.empno,e.ename
  2  FROM emp e,emp m
  3  WHERE e.mgr=m.empno(+) AND e.hiredate<m.hiredate ;

     EMPNO ENAME
---------- --------------------
      7499 ALLEN
      7521 WARD
      7566 JONES
      7698 BLAKE
      7782 CLARK
      7369 SMITH

已选择 6 行。

 第二步：找到部门信息

SQL> SELECT e.empno,e.ename,d.dname,d.loc
  2  FROM emp e,emp m,dept d
  3  WHERE e.mgr=m.empno(+) AND e.hiredate<m.hiredate
  4  AND e.deptno=d.deptno ;

     EMPNO ENAME                DNAME                        LOC
---------- -------------------- ---------------------------- --------------------------
      7521 WARD                 SALES                        CHICAGO
      7499 ALLEN                SALES                        CHICAGO
      7782 CLARK                ACCOUNTING                   NEW YORK
      7566 JONES                RESEARCH                     DALLAS
      7698 BLAKE                SALES                        CHICAGO
      7369 SMITH                RESEARCH                     DALLAS

已选择 6 行。

第三步：统计部门人数

SQL> SELECT e.empno,e.ename,d.dname,d.loc,temp.count
  2  FROM emp e,emp m,dept d,(
  3  SELECT deptno dno,COUNT(empno) count
  4  FROM emp
  5  GROUP BY deptno) temp
  6  WHERE e.mgr=m.empno(+) AND e.hiredate<m.hiredate
  7  AND e.deptno=d.deptno
  8  AND d.deptno=temp.dno ;

     EMPNO ENAME                DNAME                        LOC                             COUNT
---------- -------------------- ---------------------------- -------------------------- ----------
      7499 ALLEN                SALES                        CHICAGO                             6
      7521 WARD                 SALES                        CHICAGO                             6
      7566 JONES                RESEARCH                     DALLAS                              3
      7698 BLAKE                SALES                        CHICAGO                             6
      7782 CLARK                ACCOUNTING                   NEW YORK                            3
      7369 SMITH                RESEARCH                     DALLAS                              3

已选择 6 行。

列出所有“CLERK”（办事员）的姓名及其部门名称、部门人数、工资等级

确定要使用的数据表

emp 表：姓名

dept 表：部门名称

emp 表：统计部门人数

salgrade 表：得到工资等级

确定已知的关联字段

员工与部门：emp.deptno=dept.deptno

员工与工资等级：emp.sal BETWEEN salgrade.losal AND salgrade.hisal

第一步：找到所有办事员的信息

SQL> SELECT e.ename
  2  FROM emp e
  3  WHERE e.job='CLERK' ;

ENAME
--------------------
SMITH
JAMES
MILLER

 第二步：找到部门名称

SQL> SELECT e.ename,d.dname
  2  FROM emp e ,dept d
  3  WHERE e.job='CLERK' AND e.deptno=d.deptno ;

ENAME                DNAME
-------------------- ----------------------------
MILLER               ACCOUNTING
SMITH                RESEARCH
JAMES                SALES

第三步：统计出部门人数

SQL> SELECT e.ename,d.dname,temp.count
  2  FROM emp e ,dept d,(
  3  SELECT deptno dno,COUNT(empno) count
  4  FROM emp
  5  GROUP BY deptno) temp
  6  WHERE e.job='CLERK' AND e.deptno=d.deptno
  7  AND d.deptno=temp.dno;

ENAME                DNAME                             COUNT
-------------------- ---------------------------- ----------
JAMES                SALES                                 6
MILLER               ACCOUNTING                            3
SMITH                RESEARCH                              3

第四步：找到工资等级

SQL> SELECT e.ename,d.dname,temp.count,s.grade
  2  FROM emp e ,dept d,(
  3  SELECT deptno dno,COUNT(empno) count
  4  FROM emp
  5  GROUP BY deptno) temp,salgrade s
  6  WHERE e.job='CLERK' AND e.deptno=d.deptno
  7  AND d.deptno=temp.dno
  8  AND e.sal BETWEEN s.losal AND s.hisal;

ENAME                DNAME                             COUNT      GRADE
-------------------- ---------------------------- ---------- ----------
MILLER               ACCOUNTING                            3          2
JAMES                SALES                                 6          1
SMITH                RESEARCH                              3          1