SQL试题三

create table product_tab(
       pid varchar(20) not null primary key,
       name varchar(20),
       reice number(5,1),
       type varchar(20)
        );
insert into product_tab(pid,name,reice,type) values('1','冰箱','1500.0','家电');
insert into product_tab(pid,name,reice,type) values('2','苹果','3.0','水果');
insert into product_tab(pid,name,reice,type) values('3','李子','1500.0','水果');
insert into product_tab(pid,name,reice,type) values('4','电视','7.0','家电');
insert into product_tab(pid,name,reice,type) values('5','台灯','6000.0','家电');


create table customer_tab(
       acid varchar(20) not null primary key,
       acname varchar(20),
       acadress varchar2(20)
        );
insert into customer_tab( acid,acname,acadress) values('1','张三','北京');
insert into customer_tab( acid,acname,acadress) values('2','李四','上海');
insert into customer_tab( acid,acname,acadress) values('3','王五','天津');
insert into customer_tab( acid,acname,acadress) values('4','张平','上海');
     
create table order_tab(
       id varchar(20) not null primary key,
       acid varchar(20),
       foreign key(acid) references customer_tab(acid),
        pid varchar2(20),
        foreign key( pid) references product_tab(pid),
      amount number(3)
        );  
insert into order_tab(id,acid,pid,amount) values('1','1','1','12');
insert into order_tab(id,acid,pid,amount) values('2','3','1','1');
insert into order_tab(id,acid,pid,amount) values('3','2','2','3');
insert into order_tab(id,acid,pid,amount) values('4','1','3','1');
insert into order_tab(id,acid,pid,amount) values('5','1','2','4');
insert into order_tab(id,acid,pid,amount) values('6','3','5','4');
commit
------1.查询所有商品的分类及每个分类下商品数量
select  p.type,count(p.type)
from  product_tab p
group by p.type;




-----2.查询所有商品分类，及每个分类中商品名称，所有商品名称显示有同一列中
select type,WMSYS.WM_CONCAT(name) name
from product_tab 
group by type;
-----3.查询所有的商品名称及价格；
select name,reice
from product_tab ;
----4.查询商品分类中商品数量2 个以上的，展示商品的分类名称及商品数量
select type,count(0)
from product_tab 
group by type
having count(0)>2;
----5.查询‘张三’的所有消费记录；
select o.*,p.name
from customer_tab c,order_tab o,product_tab p
where  c.acname='张三'and o.acid=c.acid and o.pid=p.pid
 
----6.查询来自‘上海’的用户的消费记录数；
select count(o.id)
from customer_tab c,order_tab o
where  c.acadress='上海'and o.acid(+)=c.acid
----7查询来自‘上海’的用户的消费总金额；
select sum(o.amount*p.reice)
from customer_tab c,order_tab o,product_tab p
where  c.acadress='上海'and o.acid=c.acid and o.pid=p.pid
----8查询所有用户的消费金额；
select c.acid,c.acname,sum(o.amount*p.reice)
from customer_tab c,order_tab o,product_tab p
where  o.acid=c.acid and o.pid=p.pid
group by c.acid,c.acname
order by c.acid asc
-------用exists的嵌套查询
select o.*
from order_tab o
where  o.pid in(select pid from product_tab)
select o.*
from order_tab o
where  exists(select pid from product_tab)
-----内连接（inner join）
select * from order_tab o
inner join  product_tab p on o.pid=p.pid
----左连接(左外连接)
select * from order_tab o
left join  product_tab p on o.pid=p.pid
----7.1.2修改数据库表
---添加字段
alter table product_tab 
  add( 
      testTimes number(3)
      );
select * from product_tab 
----删除字段
alter table product_tab 
drop ( testimes);
----修改字段=====被修改的字段必须清空
alter table product_tab
modify(
testx  varchar2(15) default '010-110'
);
---禁用----
alter table product_tab
drop unused(
 testTimes
);
alter table product_tab
drop unused columns checkpoint 1000;
-------创建视图
create view myView 
      as 
      select pid,name from product_tab
             where type='家电'
 ------利用视图来操作基表的数据????==提交后再检索视图出现
insert into myView(pid,name) values('8','橘子')
select * from myView
select * from product_tab
---7.2.3修改视图
create or replace view myView
       as 
       select*from customer_tab;
       
----创建复杂视图
create or replace view myView(ID,名称,类型)as 
select
from  
where o


-----7.2.4删除视图r
create sequence seqi
       increment by 2
       start with 1
       maxvalue 1000
       minvalue 1
       cycle 
       cache 10;


select * from seqi
drop sequence seqi
-----9.3创建触发器
create or replace trigger mytri
       before insert on mytri
       for each row
         exception 
           when others then 
             raise;
             end;
----9.3.1insert触发器
create or replace trigger