Oracle 数据库基础学习 ，七 SQL语句综合练习 - 小猩

Oracle 数据库基础学习 (七) SQL语句综合练习

建表和约束 多表查询

一、多表查询综合练习

1、 列出高于在30部门工作的所有人员的薪金的员工的姓名、部门名称、部门编号、部门人数

分析:

需要的员工信息：

|-emp表：姓名、部门编号

|-dept表：部门名称、部门编号

|-emp表：统计部门人数

确认关联关系：

　　emp.deptno=dept.deptno

1) 查询出在30部门工作的所有人员的薪金

select sal from emp where deptno=30 ;

2) 找出工资大于30部门的员工的姓名、部门编号（返回多行单列数据，在where子句子查询）

select e.ename, e.deptno, e.sal
from emp e 
where e.sal >all (select sal from emp where deptno=30) ;

3) 统计显示部门的名称

select e.deptno, e.sal, d.dname
from emp e, dept d
where sal >all (select sal from emp where deptno=30) and (d.deptno = e.deptno) ;

4) 在emp表中统计出部门的各个人数，子查询中查询部门人数，返回多行多列表

select deptno dno, count(empno)  count
from emp 
group by deptno ;

5) 多行多列表在from子句中查询结果

select e.ename,d.dname, e.deptno, temp.count
from emp e, dept d,(select deptno dno, count(empno) count
                    from emp 
                    group by deptno) temp
where sal >all (select sal from emp where deptno=30) 
and (d.deptno = e.deptno) 
and temp.dno=d.deptno ;

2、 列出与scott从事相同工作的所有员工信息以及部门名称，部门人数，领导姓名

分析：

需要的员工信息：

|-emp表：员工名称，工资

|-dept表：部门名称

|-emp表：统计部门人数

|-emp表：统计领导信息

确认关联关系：

|-部门联系 emp.deptno=dept.deptno

|-领导联系 emp.mgr=memp.empno

1) 查询出scott从事的工作

select job from emp where ename=\'SCOTT\';

2) 与scott从事相同工作的所有员工信息，子查询返回“单行单列“

select e.empno, e.ename, e.sal
from emp e
where e.job=(select job from emp where ename=\'SCOTT\');

3) 加入显示部门名称，加入dept表

select e.empno, e.ename, e.sal, d.dname
from emp e, dept d
where e.job=(select job from emp where ename=\'SCOTT\')
      and (d.deptno = e.deptno);

4) 加入显示部门人数，子查询返回多行多列

select  e.empno, e.ename, e.sal, d.dname , temp.cou
from emp e, dept d, (select deptno dno, count(empno) cou
                     from emp
                     group by deptno) temp
where job=(select job from emp where ename=\'SCOTT\')
      and (d.deptno = e.deptno) 
      and temp.dno=d.deptno;

5) 加入显示领导姓名，使用emp表的自身关联，并消除“SCOTT”

select e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm, d.dname, temp.cou, m.ename
from emp e, dept d, (select deptno dno, count(empno) cou
                     from emp
                     group by deptno) temp, emp m
where e.job=(select job 
             from emp 
             where ename=\'SCOTT\') 
      and d.deptno = e.deptno
      and temp.dno=d.deptno 
      and e.mgr=m.empno
      and e.ename<>\'SCOTT\';

3、查询出比 \'SMITH\'或\'ALLEN\'薪资高的员工的编号、姓名、工资、部门名称、领导名称、部门人数、平均工资和最高及最低工资

select e.empno, e.ename, e.sal, d.dname, m.ename, temp.count, temp.avg, temp.max, temp.min
from emp e,dept d,emp m,(select deptno dno, count(empno) count, avg(sal) avg , max(sal) max, min(sal) min
                          from emp
                          group by deptno) temp
where e.sal >any (select sal from emp where ename in (\'SMITH\',\'ALLEN\'))
       --and e.ename<>\'SMITH\'
       --and e.ename<>\'ALLEN\'
       and e.ename not in (\'SMITH\',\'ALLEN\')
       and e.deptno=d.deptno(+)
       and e.mgr=m.empno(+)
       and d.deptno=temp.dno(+);

4、查询出有比直接领导就职时间早的雇员的编号，姓名，部门名称，部门位置和部门人数

select e.empno, e.ename, d.dname, d.loc, temp.count
from emp e, emp m, dept d, (select deptno dno, count(empno) count
                            from emp
                            group by deptno) temp
where e.mgr=m.empno(+)
      and e.hiredate < m.hiredate
      and d.deptno=e.deptno(+)
      and d.deptno=temp.dno(+);

5、列出所有“CLERK”(办事员 )的姓名，部门名称，部门人数和工资等级

select e.ename, d.dname, temp.count, s.grade
from emp e,
       dept d,
       (select deptno dno, count(empno) count from emp group by deptno) temp,
       salgrade s
where e.job = \'CLERK\'
      and d.deptno = e.deptno
      and d.deptno = temp.dno
      and e.sal between s.losal and s.hisal;

二、创建一个超市商品记录数据库，并进行增加，修改，删除，查询等操作，设置约束条件，包括主外键，非空约束等

1、 数据表的建立

--数据删除
DROP TABLE purchase PURGE;
DROP TABLE product PURGE;
DROP TABLE customer PURGE;


--数据表的建立
--1、创建顾客表：
create table customer (
             customerid   varchar2(3),
             c_name       varchar2(20) not null,
             loction      varchar2(30),
             CONSTRAINT pk_customerid PRIMARY KEY(customerid)  --主键设置         
);

--2、创建商品表：
create table product(
             productid    varchar2(3),
             productname  varchar2(20) not null,
             unitprice    number,
             p_category   varchar2(20),
             provider     varchar2(20),
             CONSTRAINT ck_unitprice CHECK (unitprice>0),    --检查设置
             CONSTRAINT pk_productid PRIMARY KEY(productid)  --主键设置          
);

--3、创建购买表：
create table purchase(
             customerid  varchar2(3),
             productid   varchar2(20),
             quantity    number,
             CONSTRAINT ck_quantity CHECK (quantity BETWEEN 0 AND 20),  --检查设置
             CONSTRAINT fk_customerid FOREIGN KEY(customerid) REFERENCES  customer(customerid) ON DELETE CASCADE, --外键的设置
             CONSTRAINT fk_productid  FOREIGN KEY(productid) REFERENCES  product(productid) ON DELETE CASCADE     --外键的设置

); 
--提交事务
commit;

2、 数据的添加

--测试数据
INSERT INTO product(productid, productname, unitprice, p_category, provider) 
VALUES(\'M01\', \'佳洁士\', 8.00, \'牙膏\',\'宝洁\');
INSERT INTO product(productid, productname, unitprice, p_category, provider) 
VALUES(\'M02\', \'高露洁\', 6.50 , \'牙膏\',\'高露洁\');
INSERT INTO product(productid, productname, unitprice, p_category, provider) 
VALUES(\'M03\', \'洁诺\', 5.00, \'牙膏\',\'联合利华\');
INSERT INTO product(productid, productname, unitprice, p_category, provider) 
VALUES(\'M04\', \'舒肤佳\', 3.00, \'香皂\',\'宝洁\');
INSERT INTO product(productid, productname, unitprice, p_category, provider) 
VALUES(\'M05\', \'夏士莲\', 5.00, \'香皂\',\'联合利华\');
INSERT INTO product(productid, productname, unitprice, p_category, provider) 
VALUES(\'M06\', \'雕牌\', 2.50, \'洗衣粉\',\'纳爱斯\');
INSERT INTO product(productid, productname, unitprice, p_category, provider) 
VALUES(\'M07\', \'中华\', 3.50, \'牙膏\',\'联合利华\');
INSERT INTO product(productid, productname, unitprice, p_category, provider) 
VALUES(\'M08\', \'汰渍\', 3.00, \'洗衣粉\',\'宝洁\');
INSERT INTO product(productid, productname, unitprice, p_category, provider) 
VALUES(\'M09\', \'碧浪\', 4.00, \'洗衣粉\',\'宝洁\');


INSERT INTO customer(customerid, c_name, loction) 
VALUES(\'C01\', \'Dennis\', \'海淀\');
INSERT INTO customer(customerid, c_name, loction) 
VALUES(\'C02\', \'John\', \'朝阳\');
INSERT INTO customer(customerid, c_name, loction) 
VALUES(\'C03\', \'Tom\', \'东城\');
INSERT INTO customer(customerid, c_name, loction) 
VALUES(\'C04\', \'Jenny\', \'东城\');
INSERT INTO customer(customerid, c_name, loction) 
VALUES(\'C05\', \'Rick\', \'西城\');


INSERT INTO purchase(customerid, productid, quantity) 
VALUES(\'C01\', \'M01\', 3);
INSERT INTO purchase(customerid, productid, quantity) 
VALUES(\'C01\', \'M05\', 2);
INSERT INTO purchase(customerid, productid, quantity) 
VALUES(\'C01\', \'M08\', 2);
INSERT INTO purchase(customerid, productid, quantity) 
VALUES(\'C02\', \'M02\', 5);
INSERT INTO purchase(customerid, productid, quantity) 
VALUES(\'C02\', \'M06\', 4);
INSERT INTO purchase(customerid, productid, quantity) 
VALUES(\'C03\', \'M01\', 1);
INSERT INTO purchase(customerid, productid, quantity) 
VALUES(\'C03\', \'M05\', 1);
INSERT INTO purchase(customerid, productid, quantity) 
VALUES(\'C03\', \'M06\', 3);
INSERT INTO purchase(customerid, productid, quantity) 
VALUES(\'C03\',  \'M08\',1 );
INSERT INTO purchase(customerid, productid, quantity) 
VALUES(\'C04\', \'M03\', 7);
INSERT INTO purchase(customerid, productid, quantity) 
VALUES(\'C04\', \'M04\', 3);
INSERT INTO purchase(customerid, productid, quantity) 
VALUES(\'C05\', \'M06\', 2);
INSERT INTO purchase(customerid, productid, quantity) 
VALUES(\'C05\', \'M07\', 8);

--提交事务
commit;

3、对数据库进行操作

第一问：查询出购买过宝洁产品的用户详细信息

select  DISTINCT *
from customer
where customerid in (
                  select customerid
                  from  purchase 
                  where productid in (
                                select productid
                                from product
                                where provider=\'宝洁\'
                                ));

第二问：查询出购买了顾客"Dennis"购买过的所有商品的顾客信息

1) 查询出顾客"Dennis"所购买的商品

select productid
from purchase
where customerid in (
            select customerid
            from customer
            where c_name = \'Dennis\');

2) 使用exists()判断行，MINUS集合运算比较出其他顾客与“Dennis”所购买商品的集合，集合为空表示购买过“Dennis”所购买所有商品，集合不为空则没有购买过“Dennis”所购买所有商品。

select *
from customer cu2
where  not exists ((           
                select p1.productid
                from purchase p1
                where p1.customerid in (
                                    select customerid
                                    from customer
                                    where c_name = \'Dennis\'))
                            MINUS
                (select p2.productid
                 from purchase p2
                 where p2.customerid in (
                                    select cu1.customerid
                                    from customer cu1 
　　　　　　　　　　　　　　　　　　　　　where cu1.c_name=cu2.c_name)))
      and c_name<>\'Dennis\';

补充：

a、eixsts()的相关用法：http://www.cnblogs.com/netserver/archive/2008/12/25/1362615.html

b、集合运算union、union all、intersect和minus

①union:连接两个子查询的和，消除重复行

②union all: 连接两个子查询的和，不消除重复行

③intersect：获取两个子查询的结果，值返回同时存在两个子查询的数据行

④minus：返回从第一个子查询的结果，但没有在第二个子查询返回的结果

第三问：查询出牙膏销量最高的供应商

1) 查询出供应牙膏的供应商和商品id

select  provider,productid
from product
where p_category=\'牙膏\' ;

2) 查询出牙膏销量最高的供应商

select temp.provider, sum(p.quantity) sum 
from purchase p, (select  provider,productid
                  from product
                  where p_category=\'牙膏\') temp
where p.productid=temp.productid 
group by temp.provider 
HAVING sum(p.quantity)=(
       select max(sum(p.quantity))
       from purchase p, (select  provider,productid
                         from product
                         where p_category=\'牙膏\') temp
       where p.productid=temp.productid 
       group by temp.provider);

第四问：所有的牙膏商品单价增加10%

UPDATE product SET unitprice=unitprice*1.1 where p_category=\'牙膏\' ;

第五问：删除从未被购买的商品

DELETE FROM product WHERE productid not in (select productid from purchase);