1）有三张表分别为会员表（member）销售表（sale）退货表（regoods）
（1）会员表有字段memberid（会员id，主键）credits（积分）；
（2）销售表有字段memberid（会员id，外键）购买金额（MNAccount）；
（3）退货表中有字段memberid（会员id，外键）退货金额（RMNAccount）。
2）业务说明
（1）销售表中的销售记录可以是会员购买，也可以是非会员购买。（即销售表中的memberid可以为空）；
（2）销售表中的一个会员可以有多条购买记录；
（3）退货表中的退货记录可以是会员，也可是非会员；
（4）一个会员可以有一条或多条退货记录。
查询需求：分组查出销售表中所有会员购买金额，同时分组查出退货表中所有会员的退货金额，把会员id相同的购买金额-退款金额得到的结果更新到会员表中对应会员的积分字段（credits）
数据集
sale
1001 50.3
1002 56.5
1003 235
1001 23.6
1005 56.2
25.6
33.5
regoods
1001 20.1
1002 23.6
1001 10.1
23.5
10.2
1005 0.8
1）建表
create table member(memberid string,credits double) row format delimited fields terminated by 't'; create table sale(memberid string,MNAccount double) row format delimited fields terminated by 't'; create table regoods(memberid string,RMNAccount double) row format delimited fields terminated by 't';
2）需求SQL
第10题需求分析： 分组查出销售表中所有会员购买金额，同时分组查出退货表中所有会员的退货金额，把会员id相同的购买金额-退款金额得到的结果更新到会员表中对应会员的积分字段（credits） 1.增加新的字段来保存积分 2.利用join...on...方法进行购买和退货的查询 3.通过t1和t2两张表进行on的id相等代表会员id相同 insert into table member select t1.memberid, MNAccount - RMNAccount form ( select memberid, sum(MNAccount) MNAccount from sale where memberid != '' group by memberid )t1 join (select memberid, sum(RMNAcount) RMNAccount from regeoods where memberid != '' group by memberid )t2 on t1.memberid = t2.memberid;
1.用一条SQL语句查询出每门课都大于80分的学生姓名
数据集：
name kecheng fenshu 张三 语文 81 张三 数学 75 李四 语文 76 李四 数学 90 王五 语文 81 王五 数学 100 王五 英语 90
SQL：
--answer1 select distinct name from distinct name from tableName tab where name not in( select distinct name from tableName where fenshu<=80 ) --answer2 select name from tableName group by name having count min(fenshu)>80;
-
学生表 如下:
自动编号 学号 姓名 课程编号 课程名称 分数
1 2005001 张三 0001 数学 69 2 2005002 李四 0001 数学 89 3 2005001 张三 0001 数学 69
删除除了自动编号不同, 其他都相同的学生冗余信息
delete tableName where 自动编号 not in(select min(自动编号) from tablename group by学号, 姓名, 课程编号, 课程名称, 分数)
3.
一个叫team的表，里面只有一个字段name,一共有4条纪录，分别是a,b,c,d,对应四个球队，现在四个球队进行比赛，用一条sql语句显示所有可能的比赛组合.
需求： 显示所有可能的比赛组合 select a.name, b.name from team a, team b where a.name < b.name;
4.面试题：怎么把这样一个 如下数据集
year month amount 1991 1 1.1 1991 2 1.2 1991 3 1.3 1991 4 1.4 1992 1 2.1 1992 2 2.2 1992 3 2.3 1992 4 2.4
查成如下结果：
year m1 m2 m3 m4 1991 1.1 1.2 1.3 1.4 1992 2.1 2.2 2.3 2.4
SQL:
分析：按需求和格式进行操作 select year, (select amount from tb1 t1 where month=1 and tb1.year =t1.year) as b1, (select amount from tb1 t2 where month=2 and tb1.year =t2.year) as b2, (select amount from tb1 t3 where month=3 and tb1.year =t3.year) as b3, (select amount from tb1 t4 where month=4 and tb1.year =t4.year) as b4 from tb1 group by year;
5.说明：复制表(只复制结构,源表名：a新表名：b)
SQL
select * into b from a where 1<>1 (where1=1，拷贝表结构和数据内容)
ORACLE
create table b As Select * from a where 1=2
###“<>”解释： [<>（不等于）(SQL Server Compact) 比较两个表达式。 当使用此运算符比较非空表达式时，如果左操作数不等于右操作数，则结果为 TRUE。 否则，结果为 FALSE。]
6.原表:
courseid coursename score
1 java 70 2 oracle 90 3 xml 40 4 jsp 30 5 servlet 80
为了便于阅读,查询此表后的结果显式如下(及格分数为60): courseid coursename score mark
1 java 70 pass 2 oracle 90 pass 3 xml 40 fail 4 jsp 30 fail 5 servlet 80 pass
SQL
需求分析：就是增加一个字段进行记录成绩是pass还是fail
利用if(,,)语句相当三目运算符即可
select
courseid,
coursenaem,
score,
if(score>=60,"pass","fail") as mark
from
tableName
7.表名：购物信息
购物人 商品名称 数量 A 甲 2 B 乙 4 C 丙 1 A 丁 2 B 丙 5 ……
SQL
需求：给出所有购入商品为两种或两种以上的购物人记录
分析： 按照购物人和商品进行分组并且过滤
给出购物人记录
select
购物人,
商品名称,
数量
from
购物信息
where
购物人
in(
select
购物人
from
购物信息
group by
购物人
having count(*) > =2
);
8.info 表
date result 2005-05-09 win 2005-05-09 lose 2005-05-09 lose 2005-05-09 lose 2005-05-10 win 2005-05-10 lose 2005-05-10 lose
如果要生成下列结果, 该如何写sql语句?
SQL
win lose 2005-05-09 1 3 2005-05-10 1 2 (1) select data,sum(case result="win" then 1 else 0 end) as "win", sum(case when result="lose" then 1 else 0 end) as "lose" from info group by date; (2) select a.date,a.result as win,b.result as lose from (select date,count(result) as result from info where result="win" group by date) as a join (select date,count(result) as result from info where result ="lose" group by date) as b on a.date = b.date;
