--商品價格信息表
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