创建一个用户xiaohong,用xiaohong登录,完成下面的操作:
create user xiaohong identified by xh123 default tablespace users quota 20m on users;
一、创建一个订单表,表结构为(订单号,货号,订货单位,售价,订购量,送货地址),插入6条记录。
create table dingd(ddid number(5),huohao char(4),dhunit varchar2(12),price number(6,2),dgl number(3),shadd varchar2(10));
insert into dingd values(97001,'SL02','阳光公司',800.50,500,'甲地');
insert into dingd values(97002,'DH03','友谊商城',500.88,400,'乙地');
insert into dingd values(97003,'DS01','和平饭店',4000.99,600,'乙地');
insert into dingd values(97004,'DS02','五环实业',5000.99,100,'丙地');
insert into dingd values(97005,'SL02','大地宾馆',800.50,50,'丙地');
insert into dingd values(97006,'DS01','蓝天乐园',4000.99,80,'甲地');
二、创建一张商品表,表结构为(货号,品名,库存量,仓库地点,进价),插入7条记录
create table goods(huohao char(4),gname varchar2(12),stock number(4),ckadd varchar2(6),buyprice number(6,2));
INSERT into goods values('DH03','手机A',1000,'乙地',450.00);
INSERT into goods values('DH04','手机B',800,'甲地',300.00);
INSERT into goods values('SL01','收录机A',1200,'甲地',666.90);
INSERT into goods values('SL02','收录机B',300,'丙地',750.50);
INSERT into goods values('LX88','录像机A',2600,'乙地',2000.00);
INSERT into goods values('DS01','电视机B',3000,'甲地',3050.00);
INSERT into goods values('DS02','电视机C',1200,'丙地',4600.00);
三、实现下面的查询
1. 查询在地点甲仓库所存的货物及库存量
select gname,stock, ckadd from goods where ckadd='甲地';
2. 查找97001号订单所订购的货物库存量
select d.huohao,gname,stock from dingd d join goods g on d.huohao= g.huohao where d.ddid=97001;
3. 查找库存量大于订购量的商品情况
select g.huohao,gname,stock,dgl from goods g, dingd d where g.huohao=d.huohao and stock>dgl;
4. 查看仓库地点和送货地点同在一个城市的商品情况
select g.huohao,gname,ckadd, shadd from goods g join dingd d on g.huohao= d.huohao where ckadd=shadd;
5. 阳光公司所订货物的名称及送货地点
select dhunit,gname,shadd from dingd d, goods g where d.huohao= g.huohao and dhunit='阳光公司';
6. 售价低于进货单价50元的货物
select g.huohao,gname,buyprice,price from dingd d, goods g where d.huohao= g.huohao and buyprice-price=50;
7. 所有接到订单的货物
select * from goods g where huohao in (select huohao from dingd );
8. 没有接到订单的货物
select * from goods where huohao not in(select huohao from dingd);
9. 库存中单价最高的货物
select * from goods where buyprice=(select max(buyprice) from goods);