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 a.姓名,
case 
when b.grade>= 60 then c.ccredit
else o end ccredit
from student a,sc b,course c
where a.sno=b.sno
and b.cno=c.cno再做个汇总就行

Answer 2

select xm,sum(ccredit) from
(select a.姓名 as xm, 
case 
when b.grade>= 60 then c.ccredit 
else o end ccredit 
from student a,sc b,course c 
where a.sno=b.sno 
and b.cno=c.cno) x group by x.xm order by x.xm

Answer 3

--oracle下面
drop table student;
create table student(sno varchar2(10),sname varchar2(20),sex varchar2(2),sage int,sdept varchar2(20));
insert into student values('01','lili','F',23,'计算机');
insert into student values('02','zhangfei','M',23,'计算机');
insert into student values('03','guanyu','M',26,'计算机');drop table course;
create table course(cno varchar2(10),cname varchar2(20),ccredit int);
insert into course values('01','高数',6);
insert into course values('02','物量',6);
insert into course values('03','C语言',12);
insert into course values('04','数字电路',12);
drop table sc;
create table sc(sno varchar2(10),cno varchar2(10),grade int);
insert into sc values('01','01',65);
insert into sc values('01','02',87);
insert into sc values('01','03',69);
insert into sc values('01','04',54);insert into sc values('02','01',65);
insert into sc values('02','02',65);
insert into sc values('02','03',65);
insert into sc values('02','04',65);insert into sc values('03','01',57);
insert into sc values('03','02',98);
insert into sc values('03','03',47);
insert into sc values('03','04',80);select sname 姓名,sum(ccredit) 总学分
from(select a.sname, 
case 
when b.grade>= 60 then c.ccredit 
else 0 end ccredit 
from student a,sc b,course c 
where a.sno=b.sno 
and b.cno=c.cno)
group by sname--sqlserver下面
drop table student;
create table student(sno varchar(10),sname varchar(20),sex varchar(2),sage int,sdept varchar(20));
insert into student values('01','lili','F',23,'计算机');
insert into student values('02','zhangfei','M',23,'计算机');
insert into student values('03','guanyu','M',26,'计算机');drop table course;
create table course(cno varchar(10),cname varchar(20),ccredit int);
insert into course values('01','高数',6);
insert into course values('02','物量',6);
insert into course values('03','C语言',12);
insert into course values('04','数字电路',12);
drop table sc;
create table sc(sno varchar(10),cno varchar(10),grade int);
insert into sc values('01','01',65);
insert into sc values('01','02',87);
insert into sc values('01','03',69);
insert into sc values('01','04',54);insert into sc values('02','01',65);
insert into sc values('02','02',65);
insert into sc values('02','03',65);
insert into sc values('02','04',65);insert into sc values('03','01',57);
insert into sc values('03','02',98);
insert into sc values('03','03',47);
insert into sc values('03','04',80);select sname as 姓名,sum(ccredit) as 总学分
from
(select a.sname, 
case 
when b.grade>= 60 then c.ccredit 
else 0 end ccredit 
from student a,sc b,course c 
where a.sno=b.sno 
and b.cno=c.cno) as c
group by sname

Answer 4

Oracle:CREATE TABLE HR.student
(
  sno    NUMBER,
  sname  VARCHAR2(20)                           NOT NULL,
  ssex   CHAR(1),
  sage   NUMBER(22,3),
  sdept  VARCHAR2(20)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
NOMONITORING;
ALTER TABLE HR.student ADD (
  CONSTRAINT student_PK
 PRIMARY KEY
 (sno));CREATE TABLE HR.course
(
  cno      NUMBER,
  cname    VARCHAR2(50)                         NOT NULL,
  ccredit  NUMBER                               NOT NULL
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
NOMONITORING;
ALTER TABLE HR.course ADD (
  PRIMARY KEY
 (cno));
CREATE TABLE HR.SC
(
  SNO    NUMBER,
  CNO    NUMBER,
  GRADE  NUMBER
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;
ALTER TABLE HR.SC ADD (
  FOREIGN KEY (SNO) 
 REFERENCES HR.STUDENT (SNO),
  CONSTRAINT F_COURSE 
 FOREIGN KEY (CNO) 
 REFERENCES HR.COURSE (CNO));SNO,SNAME,SSEX,SAGE,SDEPT
1,Tom,M,20,IT
2,MAY,F,19,MBACNO,CNAME,CCREDIT
1,C program,6
2,Math,5
3,English,5.5SNO,CNO,GRADE
1,1,80
1,2,75
1,3,55
2,1,65
2,2,60
2,3,95
select (select sname from hr.student where sno=sc.sno) as sname, 
sum(c.ccredit) as credit
from hr.student s, hr.course c, hr.sc
where SC.SNO = S.SNO
and sc.cno = c.cno
and sc.grade >= 60
group by sc.sno
SNAME,CREDIT
Tom,11
MAY,16.5沒有SQL server環境

Answer 5

group by sname可能出現重名的邏輯錯誤

Answer 6

使用标准SQL，然后在程序里对数据集操作，当切换数据库时只需改数据库连接字符串即可

Answer 7

--o or s
select t.sname, sum(t.credit) credit from (
        select s.sno, s.sname, case when sc.grade is null or sc.grade < 60 then 0 else c.cCredit end credit from student s 
               left join sc on s.sno = sc.sno
               left join c on sc.con = c.con) t
        group by t.sno, t.sname

Answer 8

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

Answer 9

对, 有可能出现重名时,把几人的学分算一起的情况;
可以加上学号;select sname, sno, sum(Ccredit) Ccredit
from
(
 select a.sname, a.sno, case when c.grade<60 then 0 else Ccredit end Ccredit
 from student a, course b, sc c
 where a.sno=c.sno
   and c.cno=b.cno
)
group by sname, sno

Answer 10

select d.Sname, c.credit
  from (select a.sno,
               sum(decode(sign(a.grade - 60), -1, 0, b.Ccredit)) credit
          from sc a, course b
         where a.cno = b.cno
         group by a.sno) c,
       student d
 where c.sno = d.sno