你的位置:首页 > 数据库

[数据库]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.salfrom emp e where e.sal >all (select sal from emp where deptno=30) ;

 

3)    统计显示部门的名称

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

 

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

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

 

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

select e.ename,d.dname, e.deptno, temp.countfrom emp e, dept d,(select deptno dno, count(empno) count          from emp           group by deptno) tempwhere 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.salfrom emp ewhere e.job=(select job from emp where ename='SCOTT');

 

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

select e.empno, e.ename, e.sal, d.dnamefrom emp e, dept dwhere 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.coufrom emp e, dept d, (select deptno dno, count(empno) cou           from emp           group by deptno) tempwhere 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.enamefrom emp e, dept d, (select deptno dno, count(empno) cou           from emp           group by deptno) temp, emp mwhere 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.minfrom 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) tempwhere 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.countfrom emp e, emp m, dept d, (select deptno dno, count(empno) count              from emp              group by deptno) tempwhere 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.gradefrom 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 customerwhere customerid in (         select customerid         from purchase          where productid in (                select productid                from product                where provider='宝洁'                ));

 

 

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

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

select productidfrom purchasewhere customerid in (      select customerid      from customer      where c_name = 'Dennis');

 

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

select *from customer cu2where 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)));

 

 

补充:

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,productidfrom productwhere p_category='牙膏' ;

  

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

select temp.provider, sum(p.quantity) sum from purchase p, (select provider,productid         from product         where p_category='牙膏') tempwhere 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);