2020/3/25 下午 数据库第四章 自连接

自连接  

查询与“数据结构”学分相同的课程的课程名和学分

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刘晨'