使用SQL进行内外连接和嵌套查询

select
*
from
student
inner join//内连接
sc
on
student.sno=sc.sno

select
*
from
sc
left join//左连接
sc
on
student.sno=sc.sno

SELECT 
Course.Cno,COUNT(SC.Cno)
FROM 
Course 
LEFT JOIN 
SC 
ON 
Course.Cno=SC.Cno
GROUP BY Course.Cno

SELECT Sname
FROM Student
WHERE Sno IN
(SELECT Sno
FROM SC
WHERE Cno='10010')

SELECT Sno,Sname
FROM Student
WHERE Sno IN
(SELECT Sno
FROM SC
WHERE Cno IN
(SELECT Cno
FROM Course
WHERE Cname='计算机基础'))

SELECT Sno,Sname
FROM Student
WHERE Sno IN
(SELECT Sno
FROM SC
WHERE Score>90 AND Cno IN
(SELECT Cno
FROM Course
WHERE Cname='计算机基础'))

SELECT Sno,Sname
FROM Student
WHERE Sdept IN
(SELECT Sdept
FROM Student
WHERE Sname='王磊')

SELECT Sno,Cno
FROM SC x
WHERE Score>=
(SELECT AVG(Score)
FROM SC y
WHERE x.Sno=y.Sno)

--ANY谓词
SELECT Sname,Sage
FROM Student
WHERE Sdept!='计算机系' AND Sage<ANY
(SELECT Sage
FROM Student
WHERE Sdept='计算机系')
--MAX函数
SELECT Sname,Sage
FROM Student
WHERE Sdept!='计算机系' AND Sage<
(SELECT MAX(Sage)
FROM Student
WHERE Sdept='计算机系')

--ALL谓词
SELECT Sname,Sage
FROM Student
WHERE Sdept!='计算机系' AND Sage<ALL
(SELECT Sage
FROM Student
WHERE Sdept='计算机系')
--MIN函数
SELECT Sname,Sage
FROM Student
WHERE Sdept!='计算机系' AND Sage<
(SELECT MIN(Sage)
FROM Student
WHERE Sdept='计算机系')

SELECT Sno,Sname
FROM Student
WHERE EXISTS
(SELECT *
 FROM SC
 WHERE Sno=Student.Sno AND Cno='10001')

SELECT Sname
FROM Student
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE Sno=Student.Sno AND Cno='10001')

SELECT Sno,Sname
FROM Student
WHERE NOT EXISTS
(SELECT *
FROM Course
WHERE NOT EXISTS
(SELECT *
 FROM SC
 WHERE SC.Sno=Student.Sno AND Course.Cno=SC.Cno))