sql 面试题

sql 面试题

原文连接:http://www.itemperor.com/a/SQL/75.html

t1_goods(商品表 )   goods_id    name   price 
t1_sale(销售表 )    sale_id    goods_id   num    
创表语句：  
create table   t1_goods
(
goods_id  varchar(10), 
name  varchar(10),
 price number
);
alter table t1_goods add constraint PK_t1_goods primary key ( GOODS_ID);
comment on column t1_goods.goods_id is '商品id';
comment on column t1_goods.name is '商品名称';
comment on column t1_goods.price is '商品价格';

insert into  t1_goods  values('1','软面抄',3.6);
insert into  t1_goods  values('2','水性笔',1);
insert into  t1_goods  values('3','墨水',3.5);
insert into  t1_goods  values('4','尺子',2);

create table   t1_sale
(
sale_id  varchar(10), 
goods_id  varchar(10),
 num number
);
alter table t1_sale add constraint PK_T1_SALE primary key (SALE_ID, GOODS_ID);
comment on column T1_SALE.SALE_ID is '销售id';
comment on column T1_SALE.GOODS_ID is '商品id';
comment on column T1_SALE.NUM is '销售数量';

insert into  t1_sale  values('S0001','1',10);
insert into  t1_sale  values('S0002','2',2);
insert into  t1_sale  values('S0003','3',30);
insert into  t1_sale  values('S0004','3',24);
-----------------------------------------------------------------------------------------------------------------------
 --1 查询商品表有多少条数据
 select count(*) from  t1_goods;
 
 --2 查询商品表 前10 条数据
 select top  * from  t1_goods; -- sqlserver 写法
 select   * from  t1_goods where rownum<=10;-- oracle   写法
 select   * from  t1_goods limit 10;-- mysql   写法
 
 --3 销售总金额
 select sum(a.price*b.num) from t1_goods a,t1_sale b where a.GOODS_ID = b.GOODS_ID
 
 --4 销售每笔单价平均费用
 select sum(a.price*b.num)/count(*) from t1_goods a,t1_sale b where a.GOODS_ID = b.GOODS_ID
 或者
 select avg(a.price*b.num) from t1_goods a,t1_sale b where a.GOODS_ID = b.GOODS_ID

 --5  备份商品表 到一张新表(t1_test)
  create table  t1_test as select * from   t1_goods --(oracle  写法)
  select * into t1_test  from   t1_goods --(sqlservr    写法)
 
 
 --6  显示销售表按数量排序
 select * from  t1_sale order by  NUM
 
 --7  查询商品名称为墨水的销售情况
 select * from   t1_sale where GOODS_ID ='3'
 
 --8 销售总额金额最大物品 
 select  *  from   t1_sale where GOODS_ID
 =(select GOODS_ID from (
 select b.GOODS_ID, sum(a.price*b.num)
 from t1_goods a,t1_sale b where a.GOODS_ID = b.GOODS_ID group  by b.GOODS_ID  order by  sum(a.price*b.num)  asc ) where rownum=1  )
 
 --9 销售数量最多的 物品
select * from  t1_sale  where  goods_id =
( select goods_id from
( select  goods_id ,sum(num) sl from  t1_sale a group by goods_id order  by sum(num) desc ) where rownum=1)
 

--10 每种商品的 销售数量和总额
select  b.GOODS_ID ,a.name, sum(b.num),sum(a.price*b.num)
 from t1_goods a,t1_sale b where a.GOODS_ID = b.GOODS_ID  group by a.name, b.GOODS_ID