SQL查询

表结构

学生表s(sno,sname,sex,sdept)

课程表c(cno,cname,credit)

学生选课表sc(sno,cno,gr)

作业表hw(hwid,cno,title)

作业完成情况表shw(sno,hwid,score)

1.查询性别为男，专业为计算机科学与技术的学生姓名与学号

select sno,sname
from s
where sex = '男' and sdept = '计算机科学与技术'

2.查询选了课，但没完成该门课作业的学生学号

select distinct s.sno
from sc
join s on s.sno = sc.sno
join c on  c.cno = sc.cno
where c.cno not in(
	select cno
    from hw
    join shw on shw.hwid = hw.hwid
    where s.sno = shw.sno
)

select distinct s.sno
from sc
join s on s.sno = sc.sno
join c on  c.cno = sc.cno
where not exists(
	select distinct *
    from hw
    join shw on shw.hwid = hw.hwid
    where s.sno = shw.sno and c.cno = hw.cno 
)

3.查询完成了所有学生自身选课的作业的学生学号

select distinct s.sno
from sc x
join s on s.sno = x.sno
join c on  c.cno = x.cno
where not exists(
	select *
	from sc y
	join hw on y.cno = hw.cno 
	where x.sno = y.sno and not exists(
		select *
        from shw
        where shw.hwid = hw.hwid and shw.sno = x.sno
    )
)

4.查询选课门数在五门以上且每门课都完成了所有作业的学生，输出学号及平均成绩，以平均成绩降序排序

在上一题的基础上加上选课门数大于5门的条件

select distinct x.sno,avg(gr) as avgscore
from sc x
join s on s.sno = x.sno
join c on  c.cno = x.cno
where not exists(
	select *
	from sc y
	join hw on y.cno = hw.cno 
	where x.sno = y.sno and not exists(
		select *
		from shw
		where shw.hwid = hw.hwid and shw.sno = x.sno
	)
) and x.sno in (
	select sno
	from sc
	group by sno
	having count(*)>5
)
group by sno
order by avgscore desc