SQL Sever-基本查询

S(SNO,SNAME,SEX,BIRTHDATE,COLLEGE)
SC(SNO,CNO,GRADE)
C(CNO,CNAME,DESTRIPTION,C_COLLEGE)

--1.查询计算机学院所设课程的课程号和课程名
use jwt
go
select CNO,CNAME
from C
where C_COLLEGE='computer'

--2.查询年龄大于20岁的男学生的学号和姓名
use jwt
go
select SN0,SNAME 
from S
where year(getdate())-year(BIRTHDATE)>20 and SEX='男'

--3.查询学号为S6的学生所学课程的课程名，课程简介和学分
use jwt
go
select CNAME,DESCRIPTION,CREDIT
from SC join C on SC.CNO=C.CNO and SC.SNO='S6'

--4.查询至少选修了C2和C4课程的女学生姓名
use jwt
go
select SNAME
from S join SC on S.SN0=SC.SNO and SEX='女' and CNO ='C2'
union
select SNAME
from S join SC on S.SN0=SC.SNO and SEX='女' and CNO='C4'

--5.查询李小刚同学不学的课程的课程号
use jwt
go
select CNO
from C
except
select CNO
from S join SC on S.SN0=SC.SNO and S.SNAME='李小刚'

--6.查询至少选修两门课程的学生学号
use jwt
go
select SNO,count(CNO)as '课程门数'
from SC
group by SNO
having count(CNO)>=2