习题(四)— —行遍历

1.(行遍历问题，面试)一道SQL语句面试题，关于group by
表内容：bs
bsrq       res
2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负

如果要生成下列结果, 该如何写sql语句?
  bsrq     胜 负
2005-05-09 2  2
2005-05-10 1  2
select bsrq,'胜'=sum(case res when '胜' then 1 else 0 end),
'负'=sum(case res when '负' then 1 else 0 end)
from bs group by bsrq

2.一张表(Grade)，里面有3个字段：语文(Chinese)，数学(Math)，英语(English)。其中有3条记录分别表示语文70分，数学80分，英语58分，请用一条sql语句查询出这三条记录并按以下条件显示出来（并写出您的思路）： 
   大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。 
       显示格式： 
       语文              数学                英语 
       及格              优秀                不及格
select 语文=(case when 语文>60 then '及格' end),Math=(case when Math>80 then '优秀' end),English=(case when English<60 then '不及格' end)
 
3.请用一个sql语句得出结果
从table1中取出如table2所列格式数据。
如使用存储过程也可以。

table1
月份mon 部门dep 业绩yj
-------------------------------
一月份      01      10
一月份      02      10
一月份      03      5
二月份      02      8
二月份      04      9
三月份      03      8

table2 （result）

部门dep 一月份      二月份      三月份
--------------------------------------
      01      10        null      null
      02      10         8        null
      03      null       5        8
      04      null      null      9

------------------------------------------
select distinct dep,'一月份'=(select yj from table1 where mon='一月份'),'二月份'=(select yj from table1 where mon='二月份'),'三月份'=(select yj from table1 where mon='三月份') from table1 group by dep

4. 已知一个表(Marks)的结构为：
   姓名 科目 成绩
   sname course    grade
   张三 语文  20 
   张三 数学  30 
   张三 英语  50 
   李四 语文  70 
   李四 数学  60 
   李四 英语  90
  
   怎样通过select语句把他变成以下结构：
   姓名 语文 数学 英语
   张三  20   30   50 
   李四  70   60   90

select distinct sname,Chinese=(select grade from Marks where course='Chinese'),Math=(select grade from Marks where course='Math'),English=(select grade from Marks where course='English') from Marks

5.题目2:

问题描述:
S (SNO,SNAME) 学生关系。SNO 为学号，SNAME 为姓名
C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师
SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩

1. 找出没有选修过“李明”老师讲授课程的所有学生姓名

select sname from S where sno not in(select sco from SC where cno=(select cno from C where cteacher='李明'))

2. 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩

select sname,avg(scgrade) from S         group by scgrade having count>2 where scgrade<60

3. 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名

 select sname from S where sno=(select cno from C where cno=1) and sno=(select cno from C where cno=2)

4. 列出“1”号课成绩比“2”号课成绩高的所有学生的学号???

select sno from SC where grade>(select grade from SC where cno=1 and sno=2)

5. 列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩