各位友友们，帮个忙，帮我看看，万分感谢！
create table student
(
stuno int identity(1,1)primary key,--学生ID
stuname varchar(20)not null --学生姓名
)
create table score
(
scoreid int identity(1,1)primary key,
stuno int not null, --学生号
courseNo int not null,--课程号
scores int not null --成绩
)
create table course
(
courseid int identity(1,1)primary key,
courseNo int not null, --课程ID
courseName varchar(20)not null, --课程名字
teacher varchar(20)not null --任课老师
)
insert into student values('欧文')
insert into student values('杰拉德')
insert into student values('兰帕德')
insert into student values('卡拉格')
insert into score values(1,1,80)
insert into score values(1,2,50)
insert into score values(2,1,40)
insert into score values(2,2,50)
insert into score values(2,3,50)
insert into score values(3,1,63)
insert into course values(1,'化学','李老师')
insert into course values(2,'物理','张老师')
insert into course values(3,'数学','周老师')
查询:查询出两门以上不及格的学员名单;
查询出一号课程成绩大于二号课程成绩的学员名单
查询出没有参加(选修)A课程的学员名单
1.查询出两门以上不及格的学员名单;
这个我是这样写的SQL语句：
select stuname from student
where stuno in
(select stuno from score
where scores<60
group by stuno
having count(scores)>=2)
2. 查询出一号课程成绩大于二号课程成绩的学员名单
这个我是这样写的SQL语句：
select 姓名=st.stuname,
化学=sum(case
when sc.scores is not null and sc.courseno=1 then sc.scores
else 0
end),
物理=sum(case
when sc.scores is not null and sc.courseno=2 then sc.scores
else 0
end),
数学=sum(case
when sc.scores is not null and sc.courseno=3 then sc.scores
else 0
end),
化学大于物理=case
when ((sum(case
when sc.scores is not null and sc.courseno=1 then sc.scores
else 0
end))>(sum(case
when sc.scores is not null and sc.courseno=2 then sc.scores
else 0
end))) then '是'
else '否'
end
FROM score sc,student st
where sc.stuno=st.stuno
group by st.stuno,st.stuname
但觉得好像跟要求不符
第三个要求还是没思路，不知道从哪里下手好
[ 本帖最后由 jess020 于 2008-6-15 19:53 编辑 ]
