面试时遇到了一个考SQL语句的题目:
三张表:
Student(学生表): Sid,Sname,Sgrade(年级),Sxi(系)
Course(课程表):Cid(课程编号),Cname(名称),Cscore(分数),Cpass(及格分数)
SC(学生课程表) Sid ,Cid
按要求写出SQL语句:
1.至少同时选修了课程表号为c1和c2的学生编号,姓名2统计出名称为“数据库”的课程在每个年级计算机系的通过人数及及格率
三张表:
Student(学生表): Sid,Sname,Sgrade(年级),Sxi(系)
Course(课程表):Cid(课程编号),Cname(名称),Cscore(分数),Cpass(及格分数)
SC(学生课程表) Sid ,Cid
按要求写出SQL语句:
1.至少同时选修了课程表号为c1和c2的学生编号,姓名2统计出名称为“数据库”的课程在每个年级计算机系的通过人数及及格率
select student.Sid,student.Sname
from sc,student
where sc.sno in(select sno from sc where sc.cno='c1')
and sc.sno in(select sno from sc where sc.cno='c2')
and sc.sno=student.sno
from @student a
join (
select c.sid,d.cname
from @course d
join @sc c
on d.cid = c.cid
where d.cname = 'c2' or d.cname = 'c1'
group by c.sid,d.cname
) s
on s.sid = a.sid
group by a.sid,a.sname
having count(1) > 1
from Student
where sid in(select sid from sc a,sc b where a.sid=b.sid and a.cid=c1 and b.cid=c2)
2、select sum(case when Cscore>Cpass then 1 else 0 end) 通过人数,
sum(case when Cscore>Cpass then 1 else 0 end)/count(*) 及格率
from course,student,sc
where course.cid=sc.cid and student.dis=sc.sid and cname='数据库' and sxi='计算机'
group by sgrade
drop table student
go
create table student(Sid int,Sname varchar(100),Sgrade varchar(100),Sxi varchar(100))
go
insert into student
select 1,'张三','一年级','计算机系' union all
select 2,'李四','二年级','英语系' union all
select 3,'王五','三年级','土木工程系'
go
if object_id('SC') is not null
drop table SC
go
create table SC(Sid int,Cid varchar(10))
go
insert into SC
select 1,'c1' union all
select 1,'c2' union all
select 1,'c3' union all
select 2,'c2' union all
select 2,'c3' union all
select 3,'c1' union all
select 3,'c2'
go
select student.Sid,student.Sname from student inner join(
select sid from sc where cid='c1' or cid='c2' group by sid having count(*)>1) a on student.Sid=a.Sid第二题没有看出这三张表哪张表记录了学生的成绩
能够准确查出第一题的结果,tks