由 dawei 副标题#e#
sql 题目一
表结构
1、表名：g_cardapply
字段（字段名/类型/长度）：
apply_no varchar8; //申请单号（关键字）
apply_date date; //申请日期
state varchar2; //申请状态
2、表名：g_cardapplydetail
字段(字段名/类型/长度)：
apply_no varchar8; //申请单号（关键字）
name varchar30; //申请人姓名
idcard varchar18; //申请人身份证号
state varchar2; //申请状态
其中，两个表的关联字段为申请单号。
题目
1、查询身份证号码为440401430103082的申请日期
2、查询同一个身份证号码有两条以上记录的身份证号码及记录个数
3、将身份证号码为440401430103082的记录在两个表中的申请状态均改为07
4、删除cardapplydetail表中所有姓李的记录
建表
create table g_cardapply(
apply_no varchar2(8),apply_date date not null,state varchar2(2) not null,constraint PK_APPLY_NO PRIMARY KEY (apply_no)
);
create table g_cardapplydetail(
apply_no varchar2(8),apply_name varchar2(30) not null,idcard char(18) not null,constraint PK_APPLY_DETAIL_NO PRIMARY KEY (apply_no),constraint FK_CARDAPPLY_DETAIL foreign key (apply_no) references g_cardapply(apply_no)
);
-- oracle字符串转日期，使用to_date函数
insert into g_cardapply values('00000010',to_date('2011-11-12','yyyy-mm-dd'),'01');
insert into g_cardapply values('00000011',to_date('2011-11-13','01');
insert into g_cardapply values('00000012',to_date('2011-11-14','02');
insert into g_cardapply values('00000013',to_date('2011-11-15','03');
insert into g_cardapply values('00000014',to_date('2011-11-16','03');
insert into g_cardapplydetail values('00000010','mary','440401430103082','01');
insert into g_cardapplydetail values('00000011','david','440401430103083','01');
insert into g_cardapplydetail values('00000012','02');
insert into g_cardapplydetail values('00000013','mike','440401430103084','03');
insert into g_cardapplydetail values('00000014','03');
commit;
求解
1、查询身份证号码为440401430103082的申请日期
select apply_date from g_cardapply a,g_cardapplydetail b where a.apply_no=b.apply_no and b.idcard='440401430103082';
或
select apply_date from g_cardapply a inner join g_cardapplydetail b on a.apply_no=b.apply_no and b.idcard='440401430103082';
2、查询同一个身份证号码有两条以上记录的身份证号码及记录个数
select idcard,count(idcard) as records from g_cardapplydetail group by idcard having count(idcard)>=2;
3、将身份证号码为440401430103082的记录在两个表中的申请状态均改为07
update g_cardapplydetail set state='07' where idcard='440401430103082';
-- 子查询
update g_cardapply set state='07' where apply_no in (
select apply_no from g_cardapplydetail where idcard='440401430103082'
);
commit;
4、删除cardapplydetail表中所有姓李的记录
-- 模糊查询 delete from g_cardapplydetail where apply_name like '李%';
sql 题目二
有一个工厂，非常繁忙，同时在进行多个订单的生产任务。每个订单都有自己的订单编号(WORKORDER_ ID),每个订单要生产的物品要拆分成多个工序，这些工序并行进行，每个工序都有自己的编号STEP_ NBR,测试数据如下:
create table projects(
workorder_id varchar2(10) not null,step_nbr int not null,step_status char(1) not null,constraint PK_PROJECTS PRIMARY KEY (workorder_id,step_nbr)
);
-- C-完成 W-等待
insert into projects values('ww023','C');
insert into projects values('ww023',1,'W');
insert into projects values('ww023',2,'W');
insert into projects values('ww024','W');
insert into projects values('ww025','C');
insert into projects values('ww025','C');
C-完成 W-等待
请编写SQL语句，找出STEP_ NBR=0，其 STEP STATUS=‘C‘，同时本订单其它工序STEP_ STATUS均为W的订单，比如对以上数据的分析结果就是：
WORKORDER_ID
ww023
要求：至少实现2种写法(多写更好)，语句越短越好。
一解：
-- 条件1: where step_nbr=0 and step_status='c' -- 条件2: 'W’= ALL (select step_status from XXX where step_nbr> = 1)
select workorder_id from projects p where p.step_nbr=0 and p.step_status='C' and 'W'= ALL (
select step_status from projects where step_nbr>=1 and workorder_id=p.workorder_id
);
二解：
select workorder_id from projects p where step_status='C' group by workorder_id having sum(step_nbr)=0;
sql 题目三
Northwind商贸公司，业务日益发展，公司OA系统正不断推出新版本以紧跟公司的发展。在OA系统中，有一员工角色表，情况如下：
陈城
W
刘海
D
刘海
O
田亮
O
王晓刚
D
张玲
S
张天明
D
张天明
O
其中： W–搬运工人 D–主任 O–高级职员 S–秘书
OA开发组的SQL程序员张明得到了上级的一个任务：领导要求得到的高级职员信息表如下：
刘海
B
田亮
O
王晓刚
D
张天明
B
要求：
1）只列出主任和高级职员的信息
2）如果即是高级职员又是主任，用B表示其角色,其它信息不用再显示 （只一条记录）。
你能不能用单条SQL语句帮助张明实现这个查询？
建表
create table roles(
emp_name varchar2(20) not null,emp_role char(1) not null,constraint pk_roles primary key(emp_name,emp_role)
);
-- 英文名
insert into roles values('Mary','W');
insert into roles values('David','D');
insert into roles values('David','O');
insert into roles values('Mike','O');
insert into roles values('Kate','D');
insert into roles values('Lucy','S');
insert into roles values('Nick','D');
insert into roles values('Nick','O');
-- 中文名
insert into roles values('陈城','W');
insert into roles values('刘海','D');
insert into roles values('刘海','O');
insert into roles values('田亮','O');
insert into roles values('王晓刚','D');
insert into roles values('张玲','S');
insert into roles values('张天明','D');
insert into roles values('张天明','O');
求解
一解：
select emp_name,'B' combine_role from roles where emp_role in ('D','O')
group by emp_name having count(*)=2
union
select emp_name,max(emp_role) combine_role from roles where emp_role in ('D','O')
group by emp_name having count(*)=1;
二解：
select emp_name,case when count(*)=1 then max(emp_role) else 'B' end as emp_role
from roles where emp_role in ('D','O') group by emp_name;
或
select emp_name,case when count(*)=2 then 'B' else max(emp_role) end as emp_role
from roles where emp_role in ('D','O') group by emp_name;
三解：
select emp_name,substr('DOB',sum(instr('DO',emp_role)),1) combine_role from roles
where emp_role in ('D','O')group by emp_name;
-- instr：返回第二个参数在第一个参数中第一次出现的位置
sql 题目四
最近，经过你的努力，你得到了一份工作，成为了百货公司的一位经理。 到位后，你发现你的销售数据库中有两张表，一个是商店促销时间的日历，另一个是在促销期间的销售额列表。你需要编写一个查询，告诉我们在每次促销中哪位职员的销售额最高，这样可以给那个职员发绩效奖金。
找出在各次促销活动中，销售量最高的销售员。
请编制一条SQL来完成这个查询。(尽量考虑多种写法)
建表
-- 商店促销时间的日历
create table promotions (
promo_name varchar2(50) not null primary key,-- 促销活动名称
start_date date not null,-- 开始时间
end_date date not null,-- 终止时间
check(start_date<=end_date)
);
-- 促销期间的销售额表 （注意：该表只是保存促销期间的销售额）
create table sales
(
ticket_nbr int not null primary key,--销售票据编号 （自增）
clerk_name varchar2(20) not null,--销售员姓名
sale_date date not null,--销售日期
sale_amount number(9,2) not null --销售金额
);
insert into promotions values('spring sales',to_date('2009-2-1','yyyy/mm/dd'),to_date('2009-2-15','yyyy/mm/dd'));
insert into promotions values('worker sale',to_date('2009-5-1',to_date('2009-5-4','yyyy/mm/dd'));
insert into promotions values('children sale',to_date('2009-6-1','yyyy/mm/dd'));
insert into promotions values('national day sale',to_date('2009-10-1',to_date('2009-10-7','yyyy/mm/dd'));
create sequence seq_nbr;
insert into sales values(seq_nbr.nextval,30);
insert into sales values(seq_nbr.nextval,'tom',73);
insert into sales values(seq_nbr.nextval,110);
insert into sales values(seq_nbr.nextval,to_date('2009-2-2',190);
insert into sales values(seq_nbr.nextval,92);
insert into sales values(seq_nbr.nextval,to_date('2009-2-3',130);
insert into sales values(seq_nbr.nextval,90);
insert into sales values(seq_nbr.nextval,to_date('2009-2-4',70);
insert into sales values(seq_nbr.nextval,9);
insert into sales values(seq_nbr.nextval,to_date('2009-2-5',88);
insert into sales values(seq_nbr.nextval,to_date('2009-2-13',50);
insert into sales values(seq_nbr.nextval,170);
insert into sales values(seq_nbr.nextval,to_date('2009-2-14',270);
insert into sales values(seq_nbr.nextval,67.5);
insert into sales values(seq_nbr.nextval,280.5);
insert into sales values(seq_nbr.nextval,113);
insert into sales values(seq_nbr.nextval,to_date('2009-5-2',35.5);
insert into sales values(seq_nbr.nextval,125);
insert into sales values(seq_nbr.nextval,to_date('2009-5-3',93);
insert into sales values(seq_nbr.nextval,167);
insert into sales values(seq_nbr.nextval,123.5);
insert into sales values(seq_nbr.nextval,200);
insert into sales values(seq_nbr.nextval,2);
insert into sales values(seq_nbr.nextval,110.5);
insert into sales values(seq_nbr.nextval,213);
insert into sales values(seq_nbr.nextval,1123);
insert into sales values(seq_nbr.nextval,780);
insert into sales values(seq_nbr.nextval,310);
insert into sales values(seq_nbr.nextval,to_date('2009-10-2',139);
insert into sales values(seq_nbr.nextval,1110.5);
insert into sales values(seq_nbr.nextval,998);
insert into sales values(seq_nbr.nextval,to_date('2009-10-3',120);
insert into sales values(seq_nbr.nextval,to_date('2009-10-4',10);
insert into sales values(seq_nbr.nextval,234);
insert into sales values(seq_nbr.nextval,to_date('2009-10-5',to_date('2009-10-6',23);
insert into sales values(seq_nbr.nextval,10.5);
insert into sales values(seq_nbr.nextval,'王海','刘万理','高春梅',10.5);
求解
#p#副标题#e##p#分页标题#e#
一解：
select p.promo_name,s.clerk_name,sum(s.sale_amount) top_sale
from promotions p inner join sales s on s.sale_date between p.start_date and p.end_date
group by p.promo_name,p.start_date,p.end_date
having sum(s.sale_amount) > all(
select sum(s2.sale_amount) from sales s2
where s.clerk_name <> s2.clerk_name and s2.sale_date between p.start_date and p.end_date
group by s2.clerk_name
);
二解：
SELECT s1.clerk_name,p.promo_name,p.end_date,SUM(s1.sale_amount)
FROM sales s1 INNER JOIN promotions p ON s1.sale_date BETWEEN p.start_date AND p.end_date
group by s1.clerk_name,p.end_date
HAVING SUM(s1.sale_amount)>= ALL(
SELECT SUM(s2.sale_amount) FROM sales s2
WHERE s2.sale_date BETWEEN p.start_date AND p.end_date
GROUP BY s2.clerk_name
);
sql 题目五
财务咨询顾问的收入统计问题
金太阳财务咨询服务公司，聘请了一些财务顾问提供财务操作咨询服务，这些顾问都以兼职形式为公司服务，公司将按小时为顾问工作计算薪酬。具体表信息如下：
a. 顾问信息表 (consultant)
create table consultant(
cst_id int primary key,cst_name varchar2(30) not null
);
insert into consultant values(1,'david');
insert into consultant values(2,'henry');
insert into consultant values(3,'mary');
insert into consultant values(4,'kent');
b. 顾问费率表 (billings)
不同级别的顾问，在不同的时期，每小时的收费是不同的，公司会根据情况上调或者下调顾问的小时佣金待遇。
create table billings(
cst_id int not null,bill_date date not null,bill_rate int not null,constraint PK_BILLING PRIMARY KEY(cst_id,bill_date),constraint FK_BILLING_EMP FOREIGN KEY (cst_id) references consultant(cst_id)
);
insert into billings values(1,to_date('2010-1-1',50);
insert into billings values(2,60);
insert into billings values(3,70);
insert into billings values(4,40);
insert into billings values(1,to_date('2011-1-1',60);
insert into billings values(4,45);
c. 顾问工作情况记录表 (hoursworked)
create table hoursworked(
list_id int primary key,cst_id int not null,work_date date not null,bill_hrs decimal(5,2) not null,constraint FK_HW_EMP FOREIGN KEY (cst_id) references consultant(cst_id)
);
insert into hoursworked values(1,to_date('2010-7-1',3);
insert into hoursworked values(2,to_date('2010-8-1',5);
insert into hoursworked values(3,2);
insert into hoursworked values(4,to_date('2011-7-1',4);
insert into hoursworked values(5,3,to_date('2011-8-1',3.5);
insert into hoursworked values(6,4,to_date('2010-9-1',10);
insert into hoursworked values(7,6);
题目
我们编写一个SQL查询语句，显示顾问的名字以及其总的顾问费用。
求解
select c.cst_id,e.cst_name,sum(d.bill_rate*c.bill_hrs) total_fee from (
select a.cst_id,a.work_date,max(b.bill_date) bill_date,a.bill_hrs
from billings b inner join hoursworked a
on a.cst_id=b.cst_id and a.work_date>=b.bill_date
group by a.cst_id,a.bill_hrs
) c,billings d,consultant e
where c.cst_id=d.cst_id and c.bill_date=d.bill_date and c.cst_id=e.cst_id
group by c.cst_id,e.cst_name
order by c.cst_id;
sql 题目六
ABC在线销售公司业务系统
表结构
1、表名：t_category (商品类别表)
字段(字段名/类型/长度)：
类别编号 category_id INT
类别名称 category_name VARCHAR2(30)
2、表名：t_goods (商品表)
字段(字段名/类型/长度)：
商品编号 goods_no CHAR(3)
商品名称 goods_name VARCHAR2(30)
商品价格 goods_price number(7,2)
所属类别 goods_category INT
点击次数 goods_click_num INT
3、 表名: t_saleinfo (销售信息表)
字段(字段名/类型/长度)：
销售流水号 sid INT
商品编号 goods_no CHAR(3)
销售日期 sale_date date
销售数量 quantity INT
销售金额 amount number(10,2)
题目
1、查询酒类商品的总点击量
2、查询各个类别所属商品的总点击量,并按降序排列
3、查询所有类别中最热门的品种(点击量最高),并按点击量降顺序排列
4、查询茅台的销售情况,按日期升序排列
格式如下:
商品编号 商品名称 销售日期 销售数量 销售金额 累计数量 累计金额
1 茅台 2011-12-1 10 7000 10 7000
1 茅台 2011-12-2 15 10500 25 17500
建表
create table t_category(
category_id int primary key,category_name varchar2(30)
);
create table t_goods(
goods_no char(3) primary key,goods_name varchar2(30) not null,goods_price number(7,goods_category int not null,goods_click_num int default 0,constraint FK_GOODS_CATEGORY FOREIGN KEY (goods_category) references t_category(category_id)
);
create table t_saleinfo(
sid int primary key,goods_no char(3) not null,sale_date date not null,quantity int not null,amount number(10,constraint FK_SALEINFO_GOODS FOREIGN KEY (goods_no) references t_goods(goods_no)
);
-- 增加类别数据
insert into t_category values(1,'酒类');
insert into t_category values(2,'服装');
insert into t_category values(3,'书籍');
-- 商品数据
insert into t_goods values('G01','贵州茅台',550.56,128);
insert into t_goods values('G02','福建老酒',5.43,24);
insert into t_goods values('G03','泸州老窖',90.56,67);
insert into t_goods values('G04','剑南春',80.56,88);
insert into t_goods values('G05','七匹狼夹克',350.56,348);
insert into t_goods values('G06','七匹狼衬衫',105.43,908);
insert into t_goods values('G07','七匹狼男长裤',130.50,167);
insert into t_goods values('G08','七匹狼领带',280.00,388);
insert into t_goods values('G09','J2EE开发',50.50,236);
insert into t_goods values('G10','STRUTS应用',24.50,654);
insert into t_goods values('G11','ORACLE 11G',100.50,145);
insert into t_goods values('G12','dotnet技术',80.00,988);
-- 销售数据
insert into t_saleinfo values(1,'G01',to_date('2008-1-1','yyyy-MM-dd'),50,50*550.56);
insert into t_saleinfo values(2,to_date('2008-1-2',25,25*550.56);
insert into t_saleinfo values(3,to_date('2008-1-3',31,31*550.56);
insert into t_saleinfo values(4,to_date('2008-1-4',43,43*550.56);
insert into t_saleinfo values(5,to_date('2008-1-5',55,55*550.56);
insert into t_saleinfo values(6,to_date('2008-1-6',102,102*550.56);
insert into t_saleinfo values(7,'G11',82,82*100.5);
insert into t_saleinfo values(8,to_date('2008-1-7',202,202*100.5);
求解
#p#副标题#e##p#分页标题#e#
1、查询酒类商品的总点击量
select '酒类' category_name,sum(goods_click_num) total_click from t_goods
where goods_category in (
select category_id from t_category
where category_name='酒类'
);
2、查询每个类别所属商品的总点击量，并按降序排列
select a.goods_category,b.category_name,sum(a.goods_click_num) total_click from t_goods a inner join t_category b on a.goods_category = b.category_id group by goods_category,category_name order by sum(a.goods_click_num) desc;
3、 查询所有类别中最热门的品种(点击量最高),并按点击量降顺序排列
select c.category_id,c.category_name,b.goods_no,b.goods_name,a.max_click
from (
select goods_category,max(goods_click_num) max_click from t_goods
group by goods_category
)a,t_goods b,t_category c
where a.goods_category=b.goods_category
and a.max_click=b.goods_click_num
and c.category_id=a.goods_category
order by a.max_click desc;
4、查询茅台的销售情况,按日期升序排列
select d.goods_no,d.goods_name,e.category_name,c.*
from
(
select max(a.goods_no) goods_no,a.sale_date,a.quantity day_quantity,max(a.amount) day_amount,sum(b.quantity) total_quantity,sum(b.amount) total_amount
from t_saleinfo a inner join t_saleinfo b
on a.goods_no=b.goods_no and a.goods_no=(
select goods_no from t_goods where goods_name='贵州茅台'
) and a.sale_date>=b.sale_date
group by a.sale_date,a.quantity
order by a.sale_date
)c,t_goods d,t_category e
where c.goods_no=d.goods_no and d.goods_category=e.category_id;