1、表Student(Sid,Sname,Sage,Ssex) 學生表 CREATE TABLE student ( sid varchar(10) NOT NULL, sName varchar(20) DEFAULT NULL, sAge datetime DEFAULT '1980-10-12 23:12:36', sSex varchar(10) DEFAULT NULL, PRIMARY KEY (sid) ENGINE=InnoDB DEFAULT CHARSET=utf8;Course(Cid,Cname,Tid) 課程表 CREATE TABLE course (

2、cid varchar(10) NOT NULL, cName varchar(10) DEFAULT NULL, tid int(20) DEFAULT NULL, PRIMARY KEY (cid) ENGINE=InnoDB DEFAULT CHARSET=utf8;SC(Sid,Cid,score) 成績表 CREATE TABLE sc ( sid varchar(10) DEFAULT NULL, cid varchar(10) DEFAULT NULL, score int(10) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3、Teacher(Tid,Tname) 教師表 CREATE TABLE taacher ( tid int(10) DEFAULT NULL, tName varchar(10) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;數據：（MySQL）insert into taacher(tid,tName) values (1,'李老師'),(2,'何以琛'),(3,'葉平');insert into student(sid,sName,sAge,sSex) values ('1001&#

4、39;,'張三豐','1980-10-12 23:12:36','男'),('1002','張無極','1995-10-12 23:12:36','男'),('1003','李奎','1992-10-12 23:12:36','女'),('1004','李元寶','1980-10-12 23:12:36','女'),('1005','

5、李世明','1981-10-12 23:12:36','男'),('1006','趙六','1986-10-12 23:12:36','男'),('1007','田七','1981-10-12 23:12:36','女');insert into sc(sid,cid,score) values ('1','001',80),('1','002',60),('

6、;1','003',75),('2','001',85),('2','002',70),('3','004',100),('3','001',90),('3','002',55),('4','002',65),('4','003',60);insert into course(cid,cName,tid) values ('001',

7、'企業管理',3),('002','馬克思',3),('003','UML',2),('004','數據庫',1),('005','英語',1);問題： 1、查詢“001”課程比“002”課程成績高的所有學生的學號; select a.Sid from (select sid,score from SC where Cid='001') a,(select sid,score from SC where Cid='002'

8、;) b where a.score>b.score and a.sid=b.sid; 2、查詢平均成績大于60分的同學的學號和平均成績; select Sid,avg(score) from sc group by Sid having avg(score) >60; 3、查詢所有同學的學號、姓名、選課數、總成績; select Student.Sid,Student.Sname,count(SC.Cid),sum(score) from Student left Outer join SC on Student.Sid=SC.Sid group by Student.Sid,S

9、name 4、查詢姓“李”的老師的個數; select count(distinct(Tname) from Teacher where Tname like '李%' 5、查詢沒學過“葉平”老師課的同學的學號、姓名; 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=&

10、#39;葉平'); 6、查詢學過“001”并且也學過編號“002”課程的同學的學號、姓名; select Student.Sid,Student.Sname from Student,SC where Student.Sid=SC.Sid and SC.Cid='001'and exists( Select * from SC as SC_2 where SC_2.Sid=SC.Sid and SC_2.Cid='002'); 7、查詢學過“葉平”老師所教的所有課的同學的學號、姓名; select Sid,Sname from Student where

11、 Sid in (select Sid from SC ,Course ,Teacher where SC.Cid=Course.Cid and Teacher.Tid=Course.Tid and Teacher.Tname='葉平' group by Sid having count(SC.Cid)=(select count(Cid) from Course,Teacher where Teacher.Tid=Course.Tid and Tname='葉平'); 8、查詢課程編號“002”的成績比課程編號“001”課程低的所有同學的學號、姓名; Sele

12、ct Sid,Sname from (select Student.Sid,Student.Sname,score ,(select score from SC SC_2 where SC_2.Sid=Student.Sid and SC_2.Cid='002') score2 from Student,SC where Student.Sid=SC.Sid and Cid='001') S_2 where score2 <score; 9、查詢所有課程成績小于60分的同學的學號、姓名; select Sid,Sname from Student wher

13、e Sid not in (select Student.Sid from Student,SC where S.Sid=SC.Sid and score>60); 10、查詢沒有學全所有課的同學的學號、姓名; select Student.Sid,Student.Sname from Student,SC where Student.Sid=SC.Sid group by Student.Sid,Student.Sname having count(Cid) <(select count(Cid) from Course); 11、查詢至少有一門課與學號為“1001”的同學所學相

14、同的同學的學號和姓名; select Sid,Sname from Student,SC where Student.Sid=SC.Sid and Cid in select Cid from SC where Sid='1001' 12、查詢至少學過學號為“001”同學所有一門課的其他同學學號和姓名; select distinct SC.Sid,Sname from Student,SC where Student.Sid=SC.Sid and Cid in (select Cid from SC where Sid='001'); 13、把“SC”表中“葉

15、平”老師教的課的成績都更改為此課程的平均成績; update SC set score=(select avg(SC_2.score) from SC SC_2 where SC_2.Cid=SC.Cid ) from Course,Teacher where Course.Cid=SC.Cid and Course.Tid=Teacher.Tid and Teacher.Tname='葉平'); 14、查詢和“1002”號的同學學習的課程完全相同的其他同學學號和姓名; select Sid from SC where Cid in (select Cid from SC wh

16、ere Sid='1002') group by Sid having count(*)=(select count(*) from SC where Sid='1002'); 15、刪除學習“葉平”老師課的SC表記錄; Delect SC from course ,Teacher where Course.Cid=SC.Cid and Course.Tid= Teacher.Tid and Tname='葉平' 16、向SC表中插入一些記錄，這些記錄要求符合以下條件：沒有上過編號“003”課程的同學學號、2號課的平均成績; Insert SC

17、select Sid,'002',(Select avg(score) from SC where Cid='002') from Student where Sid not in (Select Sid from SC where Cid='002'); 17、按平均成績從高到低顯示所有學生的“數據庫”、“企業管理”、“英語”三門的課程成績，按如下形式顯示： 學生ID,數據庫,企業管理,英語,有效課程數,有效平均分 SELECT Sid as 學生ID ,(SELECT score FROM SC WHERE SC.Sid=t.Sid AND

18、Cid='004') AS 數據庫 ,(SELECT score FROM SC WHERE SC.Sid=t.Sid AND Cid='001') AS 企業管理 ,(SELECT score FROM SC WHERE SC.Sid=t.Sid AND Cid='006') AS 英語 ,COUNT(*) AS 有效課程數, AVG(t.score) AS 平均成績 FROM SC AS t GROUP BY Sid ORDER BY avg(t.score) 18、查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，最低分 SEL

19、ECT L.Cid As 課程ID,L.score AS 最高分,R.score AS 最低分 FROM SC L ,SC AS R WHERE L.Cid = R.Cid and L.score = (SELECT MAX(IL.score) FROM SC AS IL,Student AS IM WHERE L.Cid = IL.Cid and IM.Sid=IL.Sid GROUP BY IL.Cid) AND R.Score = (SELECT MIN(IR.score) FROM SC AS IR WHERE R.Cid = IR.Cid GROUP BY IR.Cid ); 19、

20、按各科平均成績從低到高和及格率的百分數從高到低順序 SELECT t.Cid AS 課程號,max(course.Cname)AS 課程名,isnull(AVG(score),0) AS 平均成績 ,100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分數 FROM SC T,Course where t.Cid=course.Cid GROUP BY t.Cid ORDER BY 100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0

21、 END)/COUNT(*) DESC 20、查詢如下課程平均成績和及格率的百分數(用"1行"顯示): 企業管理（001），馬克思（002），OO&UML （003），數據庫（004） SELECT SUM(CASE WHEN Cid ='001' THEN score ELSE 0 END)/SUM(CASE Cid WHEN '001' THEN 1 ELSE 0 END) AS 企業管理平均分 ,100 * SUM(CASE WHEN Cid = '001' AND score >= 60 THEN 1 E

22、LSE 0 END)/SUM(CASE WHEN Cid = '001' THEN 1 ELSE 0 END) AS 企業管理及格百分數 ,SUM(CASE WHEN Cid = '002' THEN score ELSE 0 END)/SUM(CASE Cid WHEN '002' THEN 1 ELSE 0 END) AS 馬克思平均分 ,100 * SUM(CASE WHEN Cid = '002' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN Cid = '

23、002' THEN 1 ELSE 0 END) AS 馬克思及格百分數 ,SUM(CASE WHEN Cid = '003' THEN score ELSE 0 END)/SUM(CASE Cid WHEN '003' THEN 1 ELSE 0 END) AS UML平均分 ,100 * SUM(CASE WHEN Cid = '003' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN Cid = '003' THEN 1 ELSE 0 END) AS UML及格百

24、分數 ,SUM(CASE WHEN Cid = '004' THEN score ELSE 0 END)/SUM(CASE Cid WHEN '004' THEN 1 ELSE 0 END) AS 數據庫平均分 ,100 * SUM(CASE WHEN Cid = '004' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN Cid = '004' THEN 1 ELSE 0 END) AS 數據庫及格百分數 FROM SC 21、查詢不同老師所教不同課程平均分從高到低顯示 S

25、ELECT max(Z.Tid) AS 教師ID,MAX(Z.Tname) AS 教師姓名,C.Cid AS 課程,MAX(C.Cname) AS 課程名稱,AVG(Score) AS 平均成績 FROM SC AS T,Course AS C ,Teacher AS Z where T.Cid=C.Cid and C.Tid=Z.Tid GROUP BY C.Cid ORDER BY AVG(Score) DESC 22、查詢如下課程成績第 3 名到第 6 名的學生成績單：企業管理（001），馬克思（002），UML （003），數據庫（004） 學生ID,學生姓名,企業管理,馬克思,UML

26、,數據庫,平均成績 SELECT DISTINCT top 3 SC.Sid As 學生學號, Student.Sname AS 學生姓名 , T1.score AS 企業管理, T2.score AS 馬克思, T3.score AS UML, T4.score AS 數據庫, ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) as 總分 FROM Student,SC LEFT JOIN SC AS T1 ON SC.Sid = T1.Sid AND T1.Cid = '

27、;001' LEFT JOIN SC AS T2 ON SC.Sid = T2.Sid AND T2.Cid = '002' LEFT JOIN SC AS T3 ON SC.Sid = T3.Sid AND T3.Cid = '003' LEFT JOIN SC AS T4 ON SC.Sid = T4.Sid AND T4.Cid = '004' WHERE student.Sid=SC.Sid and ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISN

28、ULL(T4.score,0) NOT IN (SELECT DISTINCT TOP 15 WITH TIES ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) FROM sc LEFT JOIN sc AS T1 ON sc.Sid = T1.Sid AND T1.Cid = 'k1' LEFT JOIN sc AS T2 ON sc.Sid = T2.Sid AND T2.Cid = 'k2' LEFT JOIN sc AS T3 ON sc.

29、Sid = T3.Sid AND T3.Cid = 'k3' LEFT JOIN sc AS T4 ON sc.Sid = T4.Sid AND T4.Cid = 'k4' ORDER BY ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) DESC); 23、統計列印各科成績,各分數段人數:課程ID,課程名稱,100-85,85-70,70-60, <60 SELECT SC.Cid as 課程ID, Cname as 課程名稱 ,SUM(C

30、ASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS 100 - 85 ,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS 85 - 70 ,SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS 70 - 60 ,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS 60 - FROM SC,Course where SC.Cid=Course.Cid GR

31、OUP BY SC.Cid,Cname; 24、查詢學生平均成績及其名次 SELECT 1+(SELECT COUNT( distinct 平均成績) FROM (SELECT Sid,AVG(score) AS 平均成績 FROM SC GROUP BY Sid ) AS T1 WHERE 平均成績 > T2.平均成績) as 名次, Sid as 學生學號,平均成績 FROM (SELECT Sid,AVG(score) 平均成績 FROM SC GROUP BY Sid ) AS T2 ORDER BY 平均成績 desc; 25、查詢各科成績前三名的記錄:(不考慮成績并列情況)

32、SELECT t1.Sid as 學生ID,t1.Cid as 課程ID,Score as 分數 FROM SC t1 WHERE score IN (SELECT TOP 3 score FROM SC WHERE t1.Cid= Cid ORDER BY score DESC ) ORDER BY t1.Cid; 26、查詢每門課程被選修的學生數 select cid,count(Sid) from sc group by Cid; 27、查詢出只選修了一門課程的全部學生的學號和姓名 select SC.Sid,Student.Sname,count(Cid) AS 選課數 from SC

33、 ,Student where SC.Sid=Student.Sid group by SC.Sid ,Student.Sname having count(Cid)=1; 28、查詢男生、女生人數 Select count(Ssex) as 男生人數 from Student group by Ssex having Ssex='男' Select count(Ssex) as 女生人數 from Student group by Ssex having Ssex='女' 29、查詢姓“張”的學生名單 SELECT Sname FROM Student WHE

34、RE Sname like '張%' 30、查詢同名同性學生名單，并統計同名人數 select Sname,count(*) from Student group by Sname having count(*)>1; 31、1981年出生的學生名單(注：Student表中Sage列的類型是datetime) select Sname, CONVERT(char (11),DATEPART(year,Sage) as age from student where CONVERT(char(11),DATEPART(year,Sage)='1981' 32、

35、查詢每門課程的平均成績，結果按平均成績升序排列，平均成績相同時，按課程號降序排列 Select Cid,Avg(score) from SC group by Cid order by Avg(score),Cid DESC ; 33、查詢平均成績大于85的所有學生的學號、姓名和平均成績 select Sname,SC.Sid ,avg(score) from Student,SC where Student.Sid=SC.Sid group by SC.Sid,Sname having avg(score)>85; 34、查詢課程名稱為“數據庫”，且分數低于60的學生姓名和分數 Sel

36、ect Sname,isnull(score,0) from Student,SC,Course where SC.Sid=Student.Sid and SC.Cid=Course.Cid and Course.Cname='數據庫'and score <60; 35、查詢所有學生的選課情況; SELECT SC.Sid,SC.Cid,Sname,Cname FROM SC,Student,Course where SC.Sid=Student.Sid and SC.Cid=Course.Cid ; 36、查詢任何一門課程成績在70分以上的姓名、課程名稱和分數; SEL

37、ECT distinct student.Sid,student.Sname,SC.Cid,SC.score FROM student,Sc WHERE SC.score>=70 AND SC.Sid=student.Sid; 37、查詢不及格的課程，并按課程號從大到小排列 select cid from sc where scor e <60 order by Cid ; 38、查詢課程編號為003且課程成績在80分以上的學生的學號和姓名; select SC.Sid,Student.Sname from SC,Student where SC.Sid=Student.Sid and Score>80 and Cid='003' 39、求選了課程的學生人數 select count(*) from sc; 40、查詢選修“葉平”老師所授課程的學生中，成績最高的學生姓名及其成績 select Student.Sname,score from Student,SC,Course C,Teacher where Student.Sid=SC.Sid and SC.Cid=C.Cid and C.Tid=Teacher.Tid and Teacher.Tname='葉平' and SC.score=(select max(score