自连接
查询与“数据结构”学分相同的课程的课程名和学分
SELECT C2.Cname,C1.Credit
FROM Course C1 JOIN Course C2
ON C1.credit=C2.credit
where C2.Cname=‘数据结构‘
and C2.Cname!=‘数据结构‘
外连接
查询学生的选课情况,包括选了课程的学生和没有选课程的学生
SELECT Student.Sno,Sname,Cno,Grade
FROM Student left join SC
on Student.Sno=SC.Sno
或
SELECT STUDENT.sno,Sname,Cno,Grade
FROM SC right join Student
on Student.Sno=SC.Sno
查询哪些课程没有人选,列出其课程名
SELECT Cname from Couse C left join SC
on C.Cno=SC.Cno
where SC.cno IS null
with ties
select top 3 with ties Sname,Sdept,Grade
from Student S join SC on S.Sno=SC.Sno
where Cname=‘Java‘
Order by Grade DESC
select sno,sname,sdept from Student
where sdept in (
select sdept from Student where sname=‘刘晨‘
select S2.Sname from Student S1 Join Student S2
on S1.Sdept=S2.sdept
where S1.Sname=‘l刘晨‘