表s:学生
s(sno,sname)
sno:学号
sname:学生姓名表c:课程
c(cno,cname,cteacher)
cno:课程号
cname:课程名
cteacher:授课老师表sc:选课
sc(sno,cno,scgrade)
sno:学号
cno:课程名
scgrade:成绩查询:
1,两门及两门以上不及格的学生
2,cno为1和2的两门课程都选修过的学生
s(sno,sname)
sno:学号
sname:学生姓名表c:课程
c(cno,cname,cteacher)
cno:课程号
cname:课程名
cteacher:授课老师表sc:选课
sc(sno,cno,scgrade)
sno:学号
cno:课程名
scgrade:成绩查询:
1,两门及两门以上不及格的学生
2,cno为1和2的两门课程都选修过的学生
sql statement:
Select * from s where sno in ( select sno from
( select sno, cno from sc where scgrad < 60 ) as tempsc
group by sno
having count( cno ) >= 2 )
Select * from s where sno in
( select sno from
( select sno, cno from sc where cno = 1 or cno = 2 ) as tempsc
group by sno
having count( cno ) = 2 )
( select sno from
( select sno as sno1, cno from sc where cno = 1 or cno = 2 ) as tempsc
group by sno
having count( cno ) = 2 ) and cno=1) as t1
left join
(Select sno as sno2,cno as cno2,scgrade as scgrade2 from sc where sno in
( select sno from
( select sno, cno from sc where cno = 1 or cno = 2 ) as tempsc
group by sno
having count( cno ) = 2 ) and cno=2) as t2 on t2.sno2 = t1.sno1) as t3 left join s on s.sno = t3.sno1
select t3.*,s.sname,(t3.scgrade1+t3.scgrade2)/2 as pgrade from (select * from (Select sno as sno1,cno as cno1,scgrade as scgrade1 from sc where sno in
( select sno from
( select sno, cno from sc where cno = 1 or cno = 2 ) as tempsc
group by sno
having count( cno ) = 2 ) and cno=1) as t1
left join
(Select sno as sno2,cno as cno2,scgrade as scgrade2 from sc where sno in
( select sno from
( select sno, cno from sc where cno = 1 or cno = 2 ) as tempsc
group by sno
having count( cno ) = 2 ) and cno=2) as t2 on t2.sno2 = t1.sno1) as t3 left join s on s.sno = t3.sno1