分享以前笔试java程序员时的数据库部分的题目

分享以前笔试java程序员时的数据库部分的题目

" 疯狂Java联盟" 有一宗旨是“分享”，那我也与大家分享一点我曾经的面试题吧！我是用mysql做的，也欢迎大家用其他数据库，如oracle等。呵呵....为了抛砖引 玉，献丑一回了.... 1至5题我只是实现并未考虑效率问题，第6题未做.......也请大家补充及斧正
---------------------------------------------

销售流水表(sale_records)
Trace_id      number(10)   交易编号(惟一)
Trace_time    date         交易时间
Sales_id      number(10)   销售人员编号
Branch_id     number(10)   分店编号
Trace_type    number(4)    交易类型(1现金，2信用卡，3优惠券)
Total_amount  number(8)    交易金额

每天300 ~ 400 万记录,所有字段不为空

1.提取2007-8-8交易次数和交易总金额

  交易数  交易总金额


select count(Trace_id) as 交易次数,sum(Total_amount) as 交易总金额
from sale_records
where Trace_time='2007-8-8';
如：
+----------+------------+
| 交易次数 | 交易总金额 |
+----------+------------+
|       14 |      32200 |
+----------+------------+

2.提取2007-8-8，3种付款方式对应的交易次数和交易金额

  现金交易次数 现金交易金额  信用卡交易次数  信用卡交易金额

方法1
select count(Trace_id) as 现金交易次数,sum(Total_amount) as 现金交易金额
from sale_records
where Trace_type=1
union all
select count(Trace_id) as 信用卡交易次数,sum(Total_amount) as 信用卡交易金额
from sale_records
where Trace_type=2;
如：
+--------------+--------------+
| 现金交易次数 | 现金交易金额 |
+--------------+--------------+
|           10 |        28000 |
|            3 |         3200 |
+--------------+--------------+
方法2
select 现金交易次数, 现金交易金额,信用卡交易次数,信用卡交易金额
from
(
select Sales_id, count(Trace_id) as 现金交易次数,sum(Total_amount) as 现金交易金额
from sale_records
where Trace_type=1
)type1,
(
select Sales_id, count(Trace_id) as 信用卡交易次数,sum(Total_amount) as 信用卡交易金额
from sale_records
where Trace_type=2
)type2
where type1.Sales_id=type2.Sales_id;
如：
+--------------+--------------+----------------+----------------+
| 现金交易次数 | 现金交易金额 | 信用卡交易次数 | 信用卡交易金额 |
+--------------+--------------+----------------+----------------+
|           10 |        28000 |              3 |           3200 |
+--------------+--------------+----------------+----------------+




3.提取2007-8-8 销售次数排前 10 销售人员编号和交易次数

  销售人员编号  交易数


select Sales_id as 销售人员编号,count(Trace_id) as 交易数
from sale_records group by Sales_id having Sales_id order by count(Trace_id) desc;
取出来的是排序的统计
+--------------+--------+
| 销售人员编号 | 交易数 |
+--------------+--------+
|        56001 |     10 |
|        56002 |      2 |
|        56003 |      2 |
+--------------+--------+
(mysql中,因为mysql不支持top，故用limit代替)
select Sales_id as 销售人员编号,count(Trace_id) as 交易数
from sale_records group by Sales_id having Sales_id order by count(Trace_id) desc limit 2;
如：
+--------------+--------+
| 销售人员编号 | 交易数 |
+--------------+--------+
|        56001 |     10 |
|        56002 |      2 |
+--------------+--------+  




4.提取2007-8-8 分店的销售金额超过10000的分店编号及交易金额

  分店编号  交易金额

  select Branch_id as 分店编号,sum(Total_amount) as 交易金额
  from sale_records
  group by Branch_id
  having sum(Total_amount)>10000;
如：

+----------+----------+
| 分店编号 | 交易金额 |
+----------+----------+
|     1001 |    19200 |
|     1002 |    13000 |
+----------+----------+

5.提取2007-8-8 分店号1001、1002的交易，交易金额分别是0-99、100-299、300以上的交易次数

  分店编号  0-99交易次数   100-299交易次数  300交易次数


select 分店编号,千元的交易次数,千元至千二元的交易次数 from
(
select Branch_id as 分店编号,count(Trace_id) as 千元的交易次数 from sale_records
where Total_amount=1000
   group by Branch_id
   having Branch_id in (1001,1002)
)q1,
(
select Branch_id as 分店编号2,count(Trace_id) as 千元至千二元的交易次数 from sale_records
where Total_amount>=1000 and Total_amount<=1200
   group by Branch_id
   having Branch_id in (1001,1002)
)q2 where q1.分店编号=q2.分店编号2;

如：
+----------+----------------+------------------------+
| 分店编号 | 千元的交易次数 | 千元至千二元的交易次数 |
+----------+----------------+------------------------+
|     1001 |              8 |                      9 |
|     1002 |              3 |                      3 |
+----------+----------------+------------------------+


6、优化上表（可添加索引，则 sql 语句有何改变等）







--------------------------------
初始数据
--------------------------------
mysql 数据库

create database if not exists sale;
use sale;
create table sale_records
(
  Trace_id      int(10)   primary key   auto_increment,
  Trace_time    date,     
  Sales_id      int(10),
  Branch_id     int(10),
  Trace_type    int(4),
  Total_amount  int(8)
);
alter table sale_records auto_increment=1005600001;
insert into sale_records values(null,'2007-8-8',56001,1001,1,1000);
insert into sale_records values(null,'2007-8-8',56001,1001,2,1200);
insert into sale_records values(null,'2007-8-8',56002,1002,1,1000);
insert into sale_records values(null,'2007-8-8',56001,1001,2,1000);
insert into sale_records values(null,'2007-8-8',56001,1001,1,1000);
insert into sale_records values(null,'2007-8-8',56001,1001,1,1000);
insert into sale_records values(null,'2007-8-8',56003,1002,2,1000);
insert into sale_records values(null,'2007-8-8',56003,1001,1,1000);
insert into sale_records values(null,'2007-8-8',56001,1001,1,1000);
insert into sale_records values(null,'2007-8-8',56002,1002,3,1000);
insert into sale_records values(null,'2007-8-8',56001,1001,1,1000);
insert into sale_records values(null,'2007-8-8',56001,1002,1,10000);
insert into sale_records values(null,'2007-8-8',56001,1001,1,10000);

select * from sale_records;