面试遇到的sql题目（一）

一、表结构及基础数据

--货品表
create table goods (
goodsid number primary key,
goodsname varchar2(100) not null,
remark varchar2(4000)
)
--进货表
create table sa (
said number primary key,
goodsid number not null,
sa_quantity number

)
--出货表
create table su (
suid number primary key,
goodsid number not null,
su_quantity number

)
--插入goods数据
insert into goods(goodsid,goodsname) values(1,'白加黑')
insert into goods(goodsid,goodsname) values(2,'快克')
insert into goods(goodsid,goodsname) values(3,'感康')
insert into goods(goodsid,goodsname) values(4,'斯达舒')

select * from goods

--插入sa数据
insert into sa values(1,1,10);
insert into sa values(2,2,20);
select * from sa

--插入su数据
insert into su values(1,2,5);
insert into su values(2,4,10);

select * from su

二、问题及参考答案

--1.得出所有货品的进货情况，数量为空时数量为零

                 select g.goodsid,g.goodsname,nvl(sa.sa_quantity,0 ) quantity from
                  goods g left join sa on g.goodsid = sa.goodsid

                

      
--2.列出所有进出货货物的goodsid,goodsname,出货数量，进货数量，数量为空时数量为零
--外连接full 关键字
                方法1.
                select g.goodsid,g.goodsname,nvl(sa.sa_quantity,0 ) sa_quantity ,nvl(su.su_quantity,0 ) su_quantity
                from sa full outer join su on sa.goodsid= su.goodsid
                left outer join goods g on g.goodsid = sa.goodsid or g.goodsid = su.goodsid

               
                方法2.
                select g.goodsid,g.goodsname,nvl(sa.sa_quantity,0 ) sa_quantity ,nvl(su.su_quantity,0 ) su_quantity
                from sa full outer join su on sa.goodsid= su.goodsid
                left outer join goods g on g.goodsid = nvl(sa.goodsid,su.goodsid)

              

                考虑多条数据
                --多条求和
                select goodsid,goodsname,sum( sa_quantity) ,sum(su_quantity)
                from (
                select g.goodsid,g.goodsname,nvl(sa.sa_quantity,0 ) sa_quantity ,nvl(su.su_quantity,0 ) su_quantity
                from sa full outer join su on sa.goodsid= su.goodsid
                left outer join goods g on g.goodsid = nvl(sa.goodsid,su.goodsid)
                       )group by goodsid,goodsname

               

--3.将货品表的remark列更新为对应的货品出货数量（出货）
                update goods set goods.remark = (select su.su_quantity ||'(出货)' from su where goods.goodsid = su.goodsid)
                 where exists (select su.su_quantity ||'(出货)' from su where goods.goodsid = su.goodsid)
                 select * from goods