oracle 10g练习

oracle 的饭后练习：

--顾客表
create table customer(
 customerID number(4) primary key,--顾客编号
 customer_name varchar2(20),--顾客姓名
 customer_add varchar2(40),--顾客地址
 customer_bir date,--顾客生日
 customer_tele varchar2(20)--顾客电话
 );
--插入数据
insert into customer values(1,'陆丰燕','北京西戎线胡同211号','08-5月-79','8769888');
insert into customer values(2,'陈家驹','北京东戎线胡同300号','01-9月-76','8888888');
insert into customer values(3,'王莲良','北京西戎线胡同298号','09-5月-83','6523144');
insert into customer values(4,'元件是','北京西戎线胡同297号','08-4月-62','1234567');
insert into customer values(5,'色那边','北京西戎线胡同200号','08-5月-77','8989898');

--员工表
create table employees(
 empID number(4) primary key,--员工编号
 emp_name varchar2(20),--员工姓名
 hire_date date,--受雇日期
 mgrID number(4),--经理编号
 sal number(7,2),--工资
 comm_rate number(7,2)--佣金比例
 );
--插入数据
insert into employees values (1,'tom','05-8月-99',2,1520,0.04);
insert into employees values (2,'mike','11-2月-78',null,4520,null);
insert into employees values (3,'jack','02-11月-98',1,820,0.03);

--定单表
create table orders(
 orderID number(4) primary key,--定单编号
 order_date date,--定单日期
 customerID number(4) references customer(customerID),--顾客编号
 empID number(4) references employees(empID)--员工编号
 );
--插入数据
--10张订单
insert into orders values(1,'05-7月-05',1,1);
insert into orders values(2,'08-7月-05',2,2);
insert into orders values(3,'15-7月-05',3,3);
insert into orders values(4,'25-8月-05',4,3);
insert into orders values(5,'08-8月-05',5,2);
insert into orders values(6,'03-8月-05',2,1);
insert into orders values(7,'02-7月-05',2,1);
insert into orders values(8,'12-7月-05',1,2);
insert into orders values(9,'6-9月-05',3,3);
insert into orders values(10,'31-10月-05',5,3);

--供应商表
create table provider(
 providerID number(4) primary key,--供应商编号
 provider_name varchar2(20),--供应商名称
 provider_add varchar2(40),--供应商地址
 provider_tel varchar2(20),--供应商电话
 account varchar2(40),--供应商帐号
 email varchar2(40)--供应商电子邮件
 );
--插入数据
insert into provider values
 (11,'中兴','北京','010564578','95588123456789','[e-mail]');
insert into provider values
 (12,'大华','上海','021564578','95599123456789','[e-mail]');
insert into provider values
 (13,'何氏','广州','020564578','95500123456789','[e-mail]');
 
--商品类别表
create table categorys(
 categoryID number(4) primary key,--种类编号
 category_name varchar2(20),--种类名称
 category_desc varchar2(40)--种类描述
 );
--插入数据
insert into categorys values(1,'生鲜','鸡鸭鱼肉，冷冻食品');
insert into categorys values(2,'杂货','厨房用品等');
insert into categorys values(3,'食品','生鲜以外的食品');
insert into categorys values(4,'日用','服装，纺织类');

--商品表
create table products(
 productID number(4) primary key,--商品编号
 product_name varchar2(20),--商品名称
 income_price number(7,2),--进货单价
 providerID number(4) references provider(providerID),--供应商编号
 quantity number(4),--库存数量
 sales_price number(9,2),--出售单价
 categoryID number(4) references categorys(categoryID)--类别编号
 );
--插入数据
insert into products values(1 ,'大宝',80 ,11,10,100,1);
insert into products values(2 ,'冰箱',60 ,12,30,80,3);
insert into products values(3 ,'彩电',30 ,13,20,50,2);
insert into products values(4 ,'牙膏',100,12,100,120,3);
insert into products values(5 ,'洗衣机',20 ,11,20,1000,1);
insert into products values(6 ,'微波炉',90 ,13,10,500,2);
insert into products values(7 ,'手纸',1,12,100,10,3);
insert into products values(8 ,'一次性纸杯',2 ,12,200,20,2);
insert into products values(9 ,'电脑',500,11,5,200,2);
insert into products values(10,'拖鞋',200,11,2,100,3);
insert into products values(11,'明星照片',300,13,100,350,1);
insert into products values(12,'502胶水',100,11,100,150,1);
insert into products values(13,'84消毒液',100,12,300,150,2);
insert into products values(14,'扑克牌',50 ,13,100,100,1);
insert into products values(15,'音响',90 ,11,5,100,3);

--折扣定单表
create table order_detail(
 orderID number(4) references orders(orderID),--定单编号
 productID number(4) references products(productID),--商品编号
 quantity number(4),--数量
 discount number(4,2),--折扣
 primary key(orderID,productID));
--插入数据
--每个订单至少有2个商品
insert into order_detail values(1,1,10,0.9);
insert into order_detail values(1,2,10,0.9);
insert into order_detail values(1,3,10,0.9);
insert into order_detail values(1,4,10,0.9);
insert into order_detail values(2,1,10,0.9);
insert into order_detail values(2,2,10,0.9);
insert into order_detail values(2,3,10,0.9);
insert into order_detail values(2,4,10,0.9);
insert into order_detail values(3,1,10,0.9);
insert into order_detail values(3,2,10,0.9);
insert into order_detail values(3,3,10,0.9);
insert into order_detail values(3,4,10,0.9);
insert into order_detail values(4,5,10,0.9);
insert into order_detail values(4,6,10,0.9);
insert into order_detail values(4,7,10,0.9);
insert into order_detail values(4,8,10,0.9);
insert into order_detail values(5,9,10,0.9);
insert into order_detail values(5,10,10,0.9);
insert into order_detail values(5,11,10,0.9);
insert into order_detail values(5,12,10,0.9);
insert into order_detail values(5,13,10,0.9);
insert into order_detail values(6,14,10,0.9);
insert into order_detail values(6,15,10,0.9);
insert into order_detail values(6,1,10,0.9);
insert into order_detail values(6,2,10,0.9);
insert into order_detail values(7,3,10,0.9);
insert into order_detail values(7,4,10,0.9);
insert into order_detail values(7,5,10,0.9);
insert into order_detail values(7,6,10,0.9);
insert into order_detail values(8,7,10,0.9);
insert into order_detail values(8,8,10,0.9);
insert into order_detail values(8,9,10,0.9);
insert into order_detail values(8,10,10,0.9);
insert into order_detail values(9,11,10,0.9);
insert into order_detail values(9,12,10,0.9);
insert into order_detail values(9,13,10,0.9);
insert into order_detail values(9,14,10,0.9);
insert into order_detail values(10,15,10,0.9);
insert into order_detail values(10,1,10,0.9);
insert into order_detail values(10,2,10,0.9);
insert into order_detail values(10,3,10,0.9);

--已知一张订单号是2,查询该订单的订单额
select sum(p.sales_price*o.quantity*o.discount) as 订单额
from order_detail o,products p
where o.productid=p.productid and o.orderid=2

--统计7月的总销售额
select sum(p.sales_price*o.quantity*o.discount) as zong
from order_detail o,products p ,(select orderid as sid from orders where order_date>='01-7月-05' 
and order_date<='31-7月-05') s
where o.productid=p.productid and o.orderid=s.sid

--7-8月的所有订单
 select * from orders
where order_date>='01-7月-05' and order_date<='31-8月-05'

--7-8月所有订单的明细
select os.orderid as 订单编号,c.customer_name as 顾客,ps.product_name
 as 商品名称,
ps.sales_price*od.quantity*od.discount as 订单总价
,p.provider_name as 供应商,
os.order_date as 订单日期 from orders os, order_detail od,customer c ,
provider p,products ps,employees em
where order_date>='01-7月-05' and order_date<='31-8月-05'
and os.customerid=c.customerid and os.empid=em.empid
and os.orderid=od.orderid and od.productid=ps.productid
and ps.providerid=p.providerid

 

--把明细和产品表连接，以便于获得价格 
select os.orderid as 订单编号,c.customer_name as 顾客,ps.product_name
 as 商品名称,
ps.sales_price*od.quantity*od.discount as 订单总价
,p.provider_name as 供应商,
os.order_date as 订单日期 from orders os, order_detail od,customer c ,
provider p,products ps,employees em
where os.customerid=c.customerid and os.empid=em.empid

--计算7月的销售利润,包含折扣
select sum(p.sales_price*o.quantity*o.discount)-sum(p.income_price*o.quantity) as zong
from order_detail o,products p ,(select orderid as sid from orders where order_date>='01-7月-05' 
and order_date<='31-7月-05') s
where o.productid=p.productid and o.orderid=s.sid

 

--某顾客7-8月之间，在商场购物多少次(已知：顾客姓名)
select count(os.order_date) from orders os,customer c where
os.customerid=c.customerid and c.customer_name=? and
order_date>='01-7月-05' and order_date<='31-8月-05'

--某顾客7-8月之间，在商场购买的所有商品(已知：顾客姓名)
select ps.product_name as 商品名称,
from orders os, order_detail od,customer c ,
provider p,products ps
where order_date>='01-7月-05' and order_date<='31-8月-05'
and os.customerid=c.customerid and os.empid=em.empid

--某顾客7-8月之间，在商场购物总共花费多少钱(已知：顾客姓名)

 

--7-8月每个类别的商品销售额最多的商品是哪种？

select p.productid as pid,c.category_name,max(sum(or.quantity*or.dicount*p.sales_price))
form products p ,categorys c,order_detail or,orders o
where p.categoryid=c.categoryid,p.prductid=or.productid and ;;;;;
group by p.productid,c.category_name

 

 

--商品id,7-8月的销售数量，销售价格

--计算每个商品的销售数量