Question

数据库SQL题目

1.用sql语句，写出取出数据库系统时间，格式为2012-01-02
2.用一条sql语句，查询出每门课都大于80分的学生姓名，以及每科的平均成绩？
name kecheng score

张三 语文 71

张三 数学 85

李四 语文 76

李四 数学 90

王五 语文 80

王五 数学 97

王五 英语 88

3.写一段sql语句
year month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1992 1 2.1
1992 2 2.2
1992 3 2.3
查成下面这样的结果
year m1 m2 m3
1991 1.1 1.2 1.3
1992 2.1 2.2 2.3
求助

Answer 1

第二题是说查询每科都大于80分的这个人的平均成绩还是说所有科目所有人的平均成绩？如果是后者用一个Sql语句得到结果不行吧？有高人解答吗？如果是前者，还有可能。
借用楼上的两句：
1. select convert(varchar(10),getdate(),120)
2. select name,avg(score) from table_score a
where not exists ( select 1 from table_score b where a.name = b.name and b.score <= 80 )
group by name
3.select year,sum(m1) as m1,sum(m2) as m2,sum(m3) as m3 from (
select year,case month when 1 then amount else 0 end as m1,case month when 2 then amount else 0 end as m2,case month when 3 then amount else 0 end as m3 from tabe) group by year

Answer 2

1、select convert(varchar(50),getdate(),23)
2、select t0.name from table_name t0 where (select t1.score from table_name t1 where t1.kecheng='语文')>=80 and (select t2.score from table_name t2 where t2.kecheng='数学')>=80 and (select t3.score from table_name t3 where t3.kecheng='英语')>=80
平均分：
select t0.kecheng,sum(t0.score)/(select count(t1.score) from table_name t1 where t1.kecheng=t0.kecheng) as avg from table_name t0 group by t0.kecheng
3、select t0.year,(select sum(t1.amount) from table_name t1 where t1.month=1 and t1.year=t0.year) as m1,(select sum(t2.amount) from table_name t2 where t2.month=2 and t2.year=t0.year) as m2,(select sum(t3.amount) from table_name t3 where t3.month=3 and t1.year=t0.year) as m3 from table_name t0 group by t0.year

Answer 3

1. select convert(varchar(10),getdate(),120)
2. select name,avg(score) from table_score a
where not exists ( select 1 from table_score b where a.name = b.name and b.score <= 80 )
group by name
3. 使用 pivot

Answer 4

http://www.cnblogs.com/zhangzt/archive/2010/07/29/1787825.html