SQL一题，分别用Oracle和SQLServer实现

Question

有3张表，Student表、SC表和Course表 
Student表:学号（Sno）、姓名（Sname）、性别（Ssex）、年龄（Sage）和系名（Sdept） 
Course表:课程号（Cno）、课程名（Cname）和学分（Ccredit）； 
SC表：学号（Sno）、课程号（Cno）和成绩（Grade） 
请使用SQL语句查询学生姓名及其课程总学分 
（注：如果课程不及格，那么此课程学分为0）

Answer 1

SELECT SUM(CASE GRADE WHEN GRADE<60 THEN 0 END)[总学分] FROM  COURSE WHERE CNO IN(SELECT CNO FROM SC S LEFT JOIN STUDENT ST WHERE S.SNO = ST.SNO)

Answer 2

--SQL SERVER
select 姓名=a.Sname,课程名=b.Cname,课程总学分=sum(case when b.Grade>=60 then Ccredit else 0 end)  
from Student a
left join SC b on a.Sno=b.Sno
left join Course c on b.Cno=c.Cno

Answer 3

--modify
select 姓名=a.Sname,课程名=b.Cname,课程总学分=sum(case when b.Grade>=60 then Ccredit else 0 end)  
from Student a
left join SC b on a.Sno=b.Sno
left join Course c on b.Cno=c.Cno
group by a.Sname,b.Cname
order by a.Sname,b.Cname

Answer 4

select A.Sname
,sum(case when C.Grade<60 then 0 else B.Ccredit end) as '总学分'
From Student A inner join SC C on A.Sno=C.Sno
on Course B on C.Cno=B.Cno

Answer 5

--SQL SERVER
--为了避免姓名有重复的，应把学号也加上。
select 学号=a.Sno,姓名=a.Sname,课程名=b.Cname,课程总学分=sum(case when b.Grade>=60 then Ccredit else 0 end)  
from Student a
left join SC b on a.Sno=b.Sno
left join Course c on b.Cno=c.Cno
group by a.Sno,a.Sname,b.Cname
order by a.Sno,a.Sname,b.Cname

Answer 6

declare @Student table(Sno int ,Sname varchar(10),Ssex varchar(11),Sage int,Sdept varchar(11) )
declare @Course table(Cno int,Cname varchar(11), Ccredit int)
declare @sc table(Sno int ,Cno int ,Grade decimal(5,2))insert @Student select 
1   ,     '张三'   ,  '男' ,   25 ,  '计算机系' union all select
2   ,     '李四'  ,   '女' ,   28 ,   '语文系' 
insert @Course select
3   ,    '计算机'   ,  2 union all select
5   ,    'C#'   ,  5    union all select
4   ,    '语文'   ,    1 
insert @sc select
1   ,    3   ,     127 union all select
1   ,    4   ,     59  union all select
1   ,    5   ,     80  union all select
2   ,    3   ,     39  union all select
2   ,    4   ,     80  
select t.*,d.[Ccredit] from @Student t 
join 
(select a.Sno,[Ccredit]=sum(case when c.Grade>60 then Ccredit else 0 end) from @Student a 
join @sc c
on a.Sno=c.Sno
join @Course b
on b.Cno=c.Cno
group by a.Sno)d
on t.Sno=d.Sno
Sno         Sname      Ssex        Sage        Sdept       Ccredit     
----------- ---------- ----------- ----------- ----------- ----------- 
1           张三         男           25          计算机系        7
2           李四         女           28          语文系         1（所影响的行数为 2 行）

Answer 7

select a.sname,
sum(case  b.ccredit when c.grade>=60 then b.ccredit  else  0 end) 
from student a,course b,sc c 
where a.sno=c.sno and b.cno=c.cno 

Answer 8

create table student
(sno int)
insert into student
select 1111
union all
select 2222create table course
(cno int,ccredit int)
insert into course
select 2222,10
union all
select 3333,10create table sc
(sno int,cno int,grade int)
insert into sc
select 1111,2222,59
union all
select 2222,2222,60
union all
select 1111,3333,60
union all
select 2222,3333,60select a.sno,
sum(case   when c.grade>=60 then b.ccredit  else  0 end)credit
from student a,course b,sc c 
where a.sno=c.sno and b.cno=c.cno 
group by a.snodrop table student
drop table course
drop table sc
-------------------------
sno  credit
1111 10
2222 20 写详细了 捞分！！！

Answer 9

楼上，要查总学分，不需要课程名了。select 学号=a.Sno,姓名=a.Sname,课程总学分=sum(case when b.Grade>=60 then Ccredit else 0 end)  
from Student a
left join SC b on a.Sno=b.Sno
left join Course c on b.Cno=c.Cno
group by a.Sno,a.Sname
order by a.Sno,a.Sname

Answer 10

select 学号=a.Sno,姓名=a.Sname,课程名=b.Cname,课程总学分=sum(case when b.Grade>=60 then Ccredit else 0 end)  
from Student a
left join SC b on a.Sno=b.Sno
left join Course c on b.Cno=c.Cno
group by a.Sno,a.Sname,b.Cname
order by a.Sno,a.Sname,b.Cname

Answer 11

if object_id('student') is not null 
  drop table student
create table student
(sno int,name varchar(10))
insert into student
select 1111,'张三'
union all
select 2222,'李四'if object_id('course') is not null 
  drop table course
create table course
(cno int,ccredit int)
insert into course
select 2222,10
union all
select 3333,10
if object_id('sc') is not null 
  drop table sc
create table sc
(sno int,cno int,grade int)
insert into sc
select 1111,2222,59
union all
select 2222,2222,60
union all
select 1111,3333,60
union all
select 2222,3333,60select * from student
select * from course
select * from scselect st.name,sum(case when s.grade<60 then 0 else c.ccredit end) as 学分 from sc s,
course c,student st
where s.cno = c.cno and st.sno = s.sno 
group by st.name/**
name       学分          
---------- ----------- 
李四         20
张三         10（所影响的行数为 2 行）**/

Answer 12

SQL SERVER select s.Sname, 
        sum(case when sc.Grade<60 then 0 else c.Ccredit end) as 總學分 
 from  Student s,Course c,SC
 where s.sno=sc.sno and c.cno=sc.cno
 group by s.SnameOracle  上面的也可以再Oracle中執行,也可以用decode()函數select s.Sname, 
        sum(decode(sign(sc.Grade-60),-1,0,c.Ccredit) as 總學分 
 from  Student s,Course c,SC
 where s.sno=sc.sno and c.cno=sc.cno
 group by s.Sname

Answer 13

oracle:
SELECT A.Sno,A.Sname,A.Sdept, SUM(CASE WHEN NVL(C.Ccredit)<60 THEN 0 ELSE C.Ccredit END) TTL_Ccredit FROM Student A LEFT JOIN SC B ON A.Sno=B.Sno LEFT JOIN Course C ON B.Cno=C.Cno GROUP BY A.Sno,A.Sname,A.Sdeptsql server:
SELECT A.Sno,A.Sname,A.Sdept, SUM(CASE WHEN ISNULL(C.Ccredit)<60 THEN 0 ELSE C.Ccredit END) TTL_Ccredit FROM Student A LEFT JOIN SC B ON A.Sno=B.Sno LEFT JOIN Course C ON B.Cno=C.Cno GROUP BY A.Sno,A.Sname,A.Sdept