--商品价格信息表
create table GOODS(
ID NUMBER,
NAME VARCHAR2(10),
PRICE NUMBER(10,2),
AREA VARCHAR2(10)
);
--添加主键
alter table GOODS
add constraint PK_GOODS primary key (ID);
--添加注视
comment on table GOODS is '商品信息表';
comment on column GOODS.ID is '商品编号';
comment on column GOODS.NAME is '商品名称';
comment on column GOODS.PRICE is '商品价格';
comment on column GOODS.AREA is '销售地区';
--添加数据
insert into GOODS values(11,'苹果','2.5','成都1');
insert into GOODS values(12,'苹果','3.5','成都2');
insert into GOODS values(13,'苹果','1.5','成都3');
insert into GOODS values(14,'苹果','2.0','成都4');
insert into GOODS values(21,'香蕉','1.7','成都1');
insert into GOODS values(22,'香蕉','1.5','成都2');
insert into GOODS values(23,'香蕉','1.6','成都3');
insert into GOODS values(24,'香蕉','2.0','成都4');
--查询某种商品平均价
select NAME,avg(PRICE) from GOODS group by NAME;
--将商品价格更新为同种商品（名称相同）的平均价格
update GOODS A
set A.PRICE =
(
select avg(PRICE) from GOODS where NAME = A.NAME
);
--执行环境oracle