給定一個場景，學生選課系統(tǒng)為例，大家很熟悉。

主要關系：

學生（學號、姓名、年齡、性別）

教師（教師ID，教師姓名）

課程（課程ID,課程名稱,任教教師ID）

成績（學生ID,課程ID，成績）

創(chuàng)建關系表：

--學生:Student(SID,SName,SAge,SSex)--學生表（學號、姓名、年齡、性別）--性別,0表示男,1表示女----IF EXISTS(SELECT OBJECT_ID('Student')) /*此處永遠為true，原因是OBJECT_ID返回具體ID，或者NULL*/--使用下列語句，如果沒有，什么都不返回，也就不存在IF EXISTS(SELECT id FROM sysobjects WHERE name='Student')    DROP Table StudentCreate table Student(    SID nvarchar(20) PRimary key not null,    SName nvarchar(20),    SAge int,    SSex bit)--教師：Teacher(TID,TName)--教師表（教師ID，教師姓名）IF EXISTS(SELECT id FROM sysobjects WHERE name='Teacher') Drop table Teacher GOCreate table Teacher(    TID nvarchar(20) primary key not null,    TName nvarchar(20) not null,    )--課程：Course(CID,CName,TID)--課程表（課程ID,課程名稱,任教教師ID）IF EXISTS(SELECT id FROM sysobjects WHERE name='Course') BEGIN    DROP Table CourseENDCreate table Course(    CID  nvarchar(20)  primary key not null,    CName nvarchar(50) not null,    TID nvarchar(20))IF EXISTS(SELECT id FROM sysobjects WHERE name='SC') DROP TABLE SC--成績：SC(SID,CID,Score)--成績表（學生ID,課程ID，成績）Create table SC(    SID nvarchar(20) not null,    CID nvarchar(20) not null,    Score int)alter table SC add constraint PK_SC primary key(SID,CID)

這里僅僅是個例子，針對不同的題目，可以預置適當?shù)臄?shù)據(jù)進行檢測。

/*預置數(shù)據(jù)*/DELETE FROM StudentINSERT INTO Student(SID,SName,SAge,SSex) VALUES('S001','Tom','20','0')INSERT INTO Student(SID,SName,SAge,SSex) VALUES('S002','Lucy','21','1')INSERT INTO Student(SID,SName,SAge,SSex) VALUES('S003','Jim','18','0')INSERT INTO Student(SID,SName,SAge,SSex) VALUES('S004','Brush','20','0')INSERT INTO Student(SID,SName,SAge,SSex) VALUES('S005','Kim','22','1')INSERT INTO Student(SID,SName,SAge,SSex) VALUES('S006','Fka','20','0')INSERT INTO Student(SID,SName,SAge,SSex) VALUES('S007','Cidy','17','1')INSERT INTO Student(SID,SName,SAge,SSex) VALUES('S008','YouNi','19','0')GODELETE FROM TeacherINSERT INTO Teacher(TID,TName) VALUES('T001','張三')INSERT INTO Teacher(TID,TName) VALUES('T002','李四')INSERT INTO Teacher(TID,TName) VALUES('T003','王五')GODELETE FROM CourseINSERT INTO Course(CID,CName,TID) VALUES('C01','英語','T001')INSERT INTO Course(CID,CName,TID) VALUES('C02','體育','T002')INSERT INTO Course(CID,CName,TID) VALUES('C03','數(shù)學','T003')GODELETE FROM SCINSERT INTO SC(SID,CID,Score) VALUES('S001','C01','78')INSERT INTO SC(SID,CID,Score) VALUES('S001','C02','60')INSERT INTO SC(SID,CID,Score) VALUES('S001','C03','97')INSERT INTO SC(SID,CID,Score) VALUES('S002','C01','56')INSERT INTO SC(SID,CID,Score) VALUES('S003','C01','55')INSERT INTO SC(SID,CID,Score) VALUES('S004','C01','55')GO

以下題目，希望是一種練習題，是對具體SQL查詢方法的具體應用。對于一些復雜查詢，也進行分步求解，希望不只是明白了一道題的解法，培養(yǎng)一種解題思路。

以后遇到類似的問題就能輕易破解。

答案默認隱藏，意在希望讀者在思考之后，再看參考答案。

當然參考答案也不一定完全正確，或許還有更優(yōu)解，如果你發(fā)現(xiàn)了，請?zhí)岢觥?/p>

--1) 最笨的方法--分別得到C01成績單和C02課程的成績單,然后再得到C01課程比C02課程高的學生學號SELECT SID,Score FROM SC WHERE CID='C01' SELECT SID,Score FROM SC WHERE CID='C02' SELECT A.SID FROM (SELECT SID,Score FROM SC WHERE CID='C01') AS AINNER JOIN(SELECT SID,Score FROM SC WHERE CID='C02') AS BON A.SID = B.SID WHERE A.Score>B.Score

SELECT SID,AVG(Score) AS ScoreAverage FROM SC GROUP BY SID HAVING AVG(Score)>60 

--1)通過Group查詢總成績和選課數(shù),然后再聯(lián)表查詢SELECT SID,COUNT(CID) AS CourseCount,SUM(Score) as SumScore FROM SC GROUP BY SIDSELECT Student.SID,SName,CourseCount,SumScore FROM Student LEFT JOIN (SELECT SID,COUNT(CID) AS CourseCount,SUM(Score) as SumScore FROM SC GROUP BY SID) AS B ON Student.SID = B.SID--2）聯(lián)表查詢后再GROUP BySELECT Student.SID,Student.Sname,COUNT(SC.CID),SUM(Score)FROM Student LEFT OUTER JOIN SC ON Student.SID=SC.SIDGROUP BY Student.SID,Sname

SELECT COUNT(DISTINCT(TID)) FROM Teacher WHERE TName LIKE '李%'

--1)查詢沒有學過“張三”老師課的同學的學號,然后再查詢得到學生姓名SELECT SID FROM SC LEFT JOIN Course ON SC.CID = Course.CID LEFT JOIN Teacher ON Course.TID = Teacher.TID WHERE Tname ='張三'SELECT SID,Sname FROM Student WHERE SID NOT IN (SELECT SID FROM SC LEFT JOIN Course ON SC.CID = Course.CID LEFT JOIN Teacher ON Course.TID = Teacher.TID WHERE Tname ='張三')--2）先查詢張三老師的所有課程,然后查詢選擇了張三老師課程的學生ID，最后查詢未選其課程的學生信息SELECT CID FROM Course INNER JOIN Teacher ON Course.TID = Teacher.TID WHERE Teacher.TName='張三'SELECT SID FROM SC LEFT JOIN (SELECT CID FROM Course INNER JOIN Teacher ON Course.TID = Teacher.TID WHERE Teacher.TName='張三') AS TeacherCID ON SC.CID = TeacherCID.CIDSELECT SID,SName FROM Student WHERE SID NOT IN(SELECT SID FROM SC LEFT JOIN (SELECT CID FROM Course INNER JOIN Teacher ON Course.TID = Teacher.TID WHERE Teacher.TName='張三') AS TeacherCID ON SC.CID = TeacherCID.CID)--3)查詢多表,獲取張三老師的課程SELECT Student.SID,Student.SName FROM Student WHERE SID NOT IN (SELECT DISTINCT(SC.SID) FROM SC,Course,Teacher WHERE  SC.CID=Course.CID and Teacher.TID=Course.TID and Teacher.Tname='張三')

--1)查詢有課程不及格的學生IDSELECT DISTINCT(SID) FROM SC WHERE Score<60SELECT SID,AVG(Score) AS ScoreAverage FROM SC GROUP BY SID HAVING  COUNT(SID)>2  AND SID IN (SELECT DISTINCT(SID) FROM SC WHERE Score<60)--2)查詢有兩門以上不及格的學號SELECT SID FROM SC WHERE Score<60 GROUP B