数据库作业 大家忽略

use jxgl;
--1  检索年龄大于23的男学生的学号和姓名
select Sno,Sname
from Student
where Sage>23 and Ssex='男';

--2 检索至少选修一门课程的女学生姓名
select distinct Sname from Student
where Ssex='女' and Sno in (
select Sno from SC
)

--3 检索王同学不学的课程的课程号
select cno from Course
except
select cno from SC where Sno in(
select Sno from Student where Sname like '王%'
)

--4 检索至少选修两门课程的学生学号
select Student.Sno from Student,SC
where Student.Sno =SC.Sno group by Student.Sno having COUNT(*)>=2;



--5  检索全部学生都选修的课程的课程号和课程名
select Course.Cno,Cname
from Course,SC
where Course.Cno=SC.Cno  group by Course.Cno,Cname
having COUNT(*)>=(select COUNT(*) from Student);


--6 检索选修了所有3学分课程的学生学号

select distinct Student.Sno
from Student,SC,Course
where Student.Sno=SC.Sno and SC.Cno=Course.Cno  and Course.Ccredit='3';


--by zhaoyang