50個(gè)常用的SQL語句練習(xí)(共10頁)

1、精選優(yōu)質(zhì)文檔-傾情為你奉上基本信息Student(S#,Sname,Sage,Ssex) 學(xué)生表 Course(C#,Cname,T#) 課程表 SC(S#,C#,score) 成績表 Teacher(T#,Tname) 教師表問題： 1、查詢“001”課程比“002”課程成績高的所有學(xué)生的學(xué)號； select a.S# from (select S#,score from SC where C#='001') a,(select S#,score from SC where C#='002') b where a.score>b.score and a.

2、S#=b.S#; 一張表中存在多對多情況的2、查詢平均成績大于60分的同學(xué)的學(xué)號和平均成績； 答案一：select S#,avg(score) from sc group by S# having avg(score) >60; 一對多，對組進(jìn)行篩選答案二：SELECT s ,scr FROM (SELECT sc.S# s,AVG(sc.score) scr FROM sc GROUP BY sc.S#) rsWHERE rs.scr>60 ORDER BY rs.scr DESC 嵌套查詢可能影響效率3、查詢所有同學(xué)的學(xué)號、姓名、選課數(shù)、總成績； 答案一：select Stud

3、ent.S#,Student.Sname,count(C#),sum(score) from Student left Outer join SC on Student.S#=SC.S# group by Student.S#,Sname 如果學(xué)生沒有選課，仍然能查出，顯示總分null（邊界情況）答案二：SELECT student.S#,student.Sname,COUNT(sc.score) 選課數(shù),SUM(sc.score) 總分FROM Student,scWHERE student.S#=sc.S# GROUP BY sc.S#如果學(xué)生沒有選課，sc表中沒有他的學(xué)號，就查不出該學(xué)生

4、，有缺陷！4、查詢姓“李”的老師的個(gè)數(shù)； select count(distinct(Tname) from Teacher where Tname like '李%' 5、查詢沒學(xué)過“葉平”老師課的同學(xué)的學(xué)號、姓名； select Student.S#,Student.Sname from Student where S# not in (select distinct(SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='葉平&#

5、39;); 反面思考Step1：先找學(xué)過葉平老師課的學(xué)生學(xué)號，三表聯(lián)合查詢Step2：在用not in 選出沒學(xué)過的Step3：distinct以防葉平老師教多節(jié)課；否則若某同學(xué)的幾節(jié)課都由葉平教，學(xué)號就會(huì)出現(xiàn)重復(fù)6、查詢學(xué)過“001”并且也學(xué)過編號“002”課程的同學(xué)的學(xué)號、姓名； select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#='001'and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC

6、_2.C#='002' ); 注意目標(biāo)字段S#關(guān)聯(lián)exists subquery 可以用in subquery代替，如下select Student.S#,Student.Sname from Student,Sc where Student.S#=SC.S# and SC.C#='001'and sc.s# in ( select sc_2.s# from sc as sc_2 where sc_2.c#='002' ); 不同之處，in subquery此處就不需要關(guān)聯(lián)了7、 查詢學(xué)過“葉平”老師所教的所有課的同學(xué)的學(xué)號、姓名； select

7、 S#,Sname from Student where S# in (select S# from SC ,Course ,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='葉平' group by S# having count(SC.C#)=(select count(C#) from Course,Teacher where Teacher.T#=Course.T# and Tname='葉平'); 8、查詢課程編號“002”的成績比課程編號“001”課程低

8、的所有同學(xué)的學(xué)號、姓名； （太混亂）Select S#,Sname from (select Student.S#,Student.Sname,score ,(select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#='002') score2 from Student,SC where Student.S#=SC.S# and C#='001') S_2 where score2 <score; 自己寫的另一種方法：SELECT student.S#,student.Sname FROM

9、 student WHERE S# IN( SELECT a.S# FROM (SELECT * FROM sc WHERE C#='001') a , (SELECT * FROM sc WHERE C#='002') b WHERE a.score>b.score AND a.S#=b.S#) ;子查詢的應(yīng)用方式與第1題類似，在一對多關(guān)系表中，如果多組之間需要比較，可以將不同組抽出為幾個(gè)子查詢，再比較。這里的“一”指課程編號。9、查詢所有課程成績小于60分的同學(xué)的學(xué)號、姓名； 初始答案（效率最低）：select S#,Sname from Studen

10、t where S# not in (select Student.S# from Student,SC where Student.S#=SC.S# and score>60); （第二個(gè)select根本不需要聯(lián)合查詢）改進(jìn)簡化版（效率更高）：select S#,Sname from Student where S# not in (select distinct S# from SC where score>60); （從反面思考更簡化）自己寫的另一種方法（效率其次，但有缺陷。邊界情況：沒有學(xué)任何課程的人，查不出來）：SELECT Student.S#,Student.Snam

11、e FROM Student WHERE S# IN (SELECT S# FROM sc GROUP BY S# HAVING MAX(score)<60);In 和not in 去構(gòu)造，有時(shí)候查出來的結(jié)果并不一樣，需要考慮目標(biāo)字段S#是否在幾個(gè)表中都有10、查詢沒有學(xué)全所有課的同學(xué)的學(xué)號、姓名； select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# group by Student.S#,Student.Sname having count(C#) <(select count(C#) fr

12、om Course); 有缺陷，沒有選任何課的人查不出來。因?yàn)槭褂昧岁P(guān)聯(lián)查詢，若存在關(guān)聯(lián)不上的（一張表有，另一張表沒有），就會(huì)遺漏。select student.s#,student.sname from student where student.s# not in (select s# from sc group by s# having count(c#) = (select count(c#) from course);可以查出沒有選任何課的人，單表查詢操作，不涉及關(guān)聯(lián)。11、查詢至少有一門課與學(xué)號為“P1001”的同學(xué)所學(xué)相同的同學(xué)的學(xué)號和姓名； select DISTINCT St

13、udent.S#,Sname from Student,SC where Student.S#=SC.S# and C# in (select C# from SC where S#='P1001') ; (存在性用in即可）沒有排除自身，把結(jié)果中的P1001自己去掉12、查詢至少學(xué)過學(xué)號為“P1001”同學(xué)所有一門課的其他同學(xué)學(xué)號和姓名； select distinct SC.S#,Sname from Student,SC where Student.S#=SC.S# and C# in (select C# from SC where S#='001')

14、AND Student.s# != 'P1001' (綠色為補(bǔ)充，排除P1001本身) 13、把“SC”表中“葉平”老師教的課的成績都更改為此課程的平均成績； （有錯(cuò)誤，很混亂）update SC set score=(select avg(SC_2.score) from SC SC_2 where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='葉平'); mysql報(bào)錯(cuò)，可能其他數(shù)據(jù)庫能這么寫題目特點(diǎn)，

15、把一張表的值查出來再插到這張表中，但實(shí)際不允許，會(huì)報(bào)錯(cuò)。自己寫：UPDATE SC SET score=(SELECT AVG(R.score) FROM(SELECT score,C# FROM SC) R WHERE R.C#=sc.C# GROUP BY R.C#)WHERE sc.C# IN (SELECT C# FROM Course,Teacher WHERE Course.T#=Teacher.T# AND Teacher.Tname='葉平')14、查詢和“1002”號的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)學(xué)號和姓名； （錯(cuò)誤,條件不夠）select S# from

16、 SC where C# in (select C# from SC where S#='1002') group by S# having count(*)=(select count(*) from SC where S#='1002'); 自己寫：15、刪除學(xué)習(xí)“葉平”老師課的SC表記錄； (delete后能加表名嗎？)Delete SC from course ,Teacher where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='葉平' 自己寫：delete from sc

17、 where C# in (select C# from Course,Teacher where Course.T#=Teacher.T# and Tname=葉平)；16、向SC表中插入一些記錄，這些記錄要求符合以下條件：沒有上過編號“003”課程的同學(xué)學(xué)號、2號課的平均成績； (省略不看了)Insert SC select S#,'002',(Select avg(score) from SC where C#='002') from Student where S# not in (Select S# from SC where C#='002&

18、#39;); 17、按平均成績從高到低顯示所有學(xué)生的“數(shù)據(jù)庫”、“企業(yè)管理”、“英語”三門的課程成績，按如下形式顯示： 學(xué)生ID,數(shù)據(jù)庫,企業(yè)管理,英語,有效課程數(shù),有效平均分 SELECT S# AS 學(xué)生ID ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='004') AS 數(shù)據(jù)庫 ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='001') AS 企業(yè)管理 ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='00

19、6') AS 英語 ,COUNT(*) AS 有效課程數(shù), AVG(t.score) AS 平均成績 FROM SC AS t GROUP BY S# ORDER BY avg(t.score) 自己寫：SELECT S# AS 學(xué)號ID,(SELECT score FROM sc WHERE sc.S#=t.S# AND C#='001') AS 語文,(SELECT score FROM sc WHERE sc.S#=t.S# AND C#='002') AS 數(shù)學(xué),(SELECT score FROM sc WHERE sc.S#=t.S# AND

20、 C#='003') AS 英語,(SELECT score FROM sc WHERE sc.S#=t.S# AND C#='004') AS 政治,COUNT(C#) AS 有效課程數(shù),AVG(score) AS 課程平均分FROM SC AS t GROUP BY S# ORDER BY AVG(t.score) DESC;必須要有自關(guān)聯(lián)，否則返回不止1行18、查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，最低分 SELECT DISTINCT L.C# AS 課程ID, Cname AS 課程名, L.score AS 最高分, R.sc

21、ore AS 最低分 FROM SC AS L ,SC AS R ,courseWHERE L.C#= R.C# AND course.C#=L.C#AND L.score = (SELECT MAX(IL.score) FROM SC AS IL WHERE L.C# = IL.C# GROUP BY IL.C#) AND R.Score = (SELECT MIN(IR.score) FROM SC AS IR WHERE R.C# = IR.C# GROUP BY IR.C#); 自己寫：SELECT C# AS課程號,(SELECT MAX(score) FROM sc WHERE s

22、c.C#=t.C# GROUP BY C#) AS 最高分,(SELECT MIN(score) FROM sc WHERE sc.C#=t.C# GROUP BY C#) AS 最低分FROM sc t GROUP BY t.C# ;19、按各科平均成績從低到高和及格率的百分?jǐn)?shù)從高到低 （問題不清，且運(yùn)行不出 ）SELECT t.C# AS 課程號,max(course.Cname)AS 課程名,isnull(AVG(score),0) AS 平均成績 ,100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*

23、) AS 及格百分?jǐn)?shù) FROM SC T,Course where t.C#=course.C# GROUP BY t.C# ORDER BY 100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC 20、查詢?nèi)缦抡n程平均成績和及格率的百分?jǐn)?shù)(用"1行"顯示): 企業(yè)管理（001），馬克思（002），OO&UML （003），數(shù)據(jù)庫（004） SELECT SUM(CASE WHEN C# ='001' THEN score ELSE 0 END)/SUM

24、(CASE C# WHEN '001' THEN 1 ELSE 0 END) AS 企業(yè)管理平均分 ,100 * SUM(CASE WHEN C# = '001' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '001' THEN 1 ELSE 0 END) AS 企業(yè)管理及格百分?jǐn)?shù) ,SUM(CASE WHEN C# = '002' THEN score ELSE 0 END)/SUM(CASE C# WHEN '002' THEN 1 ELSE

25、 0 END) AS 馬克思平均分,100 * SUM(CASE WHEN C# = '002' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '002' THEN 1 ELSE 0 END) AS 馬克思及格百分?jǐn)?shù) ,SUM(CASE WHEN C# = '003' THEN score ELSE 0 END)/SUM(CASE C# WHEN '003' THEN 1 ELSE 0 END) AS UML平均分 ,100 * SUM(CASE WHEN C#

26、= '003' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '003' THEN 1 ELSE 0 END) AS UML及格百分?jǐn)?shù) ,SUM(CASE WHEN C# = '004' THEN score ELSE 0 END)/SUM(CASE C# WHEN '004' THEN 1 ELSE 0 END) AS 數(shù)據(jù)庫平均分 ,100 * SUM(CASE WHEN C# = '004' AND score >= 60 THEN 1

27、 ELSE 0 END)/SUM(CASE WHEN C# = '004' THEN 1 ELSE 0 END) AS 數(shù)據(jù)庫及格百分?jǐn)?shù) FROM SC 修改后：SELECT SUM(CASE WHEN C# ='001' THEN score ELSE 0 END)/SUM(CASE C# WHEN '001' THEN 1 ELSE 0 END) AS 語文平均分, 100 * SUM(CASE WHEN C# = '001' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHE

28、N C# = '001' THEN 1 ELSE 0 END) AS 語文及格率, SUM(CASE WHEN C# ='002' THEN score ELSE 0 END)/SUM(CASE C# WHEN '002' THEN 1 ELSE 0 END) AS 數(shù)學(xué)平均分, 100 * SUM(CASE WHEN C# = '002' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '002' THEN 1 ELSE 0 END) AS 數(shù)學(xué)及

29、格率, SUM(CASE WHEN C# ='003' THEN score ELSE 0 END)/SUM(CASE C# WHEN '003' THEN 1 ELSE 0 END) AS 英語平均分, 100 * SUM(CASE WHEN C# = '003' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '003' THEN 1 ELSE 0 END) AS 英語及格率, SUM(CASE WHEN C# ='004' THEN score

30、 ELSE 0 END)/SUM(CASE C# WHEN '004' THEN 1 ELSE 0 END) AS 政治平均分, 100 * SUM(CASE WHEN C# = '004' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '004' THEN 1 ELSE 0 END) AS 政治及格率 FROM SC ;21、查詢不同老師所教不同課程平均分，按課程分?jǐn)?shù)從高到低顯示。包括教師ID、教師姓名、課程ID、課程名、平均成績SELECT max(Z.T#) AS 教師ID

31、,MAX(Z.Tname) AS 教師姓名,C.C# AS 課程,MAX(C.Cname) AS 課程名稱,AVG(Score) AS 平均成績 FROM SC AS T,Course AS C ,Teacher AS Z where T.C#=C.C# and C.T#=Z.T# GROUP BY C.C# ORDER BY AVG(Score) DESC Max沒必要加！修改后：SELECT Z.T# AS 教師ID, Z.Tname AS 教師姓名, C.C# AS 課程ID, C.Cname AS 課程名, AVG(Score) AS 平均成績 FROM SC AS T,Course

32、AS C ,Teacher AS Z WHERE T.C#=C.C# AND C.T#=Z.T# GROUP BY C.C# ORDER BY AVG(Score) DESC;22、查詢?nèi)缦抡n程成績第 3 名到第 6 名的學(xué)生成績單：企業(yè)管理（001），馬克思（002），UML （003），數(shù)據(jù)庫（004） 學(xué)生ID,學(xué)生姓名,企業(yè)管理,馬克思,UML,數(shù)據(jù)庫,平均成績 Mysql 不支持top語句SELECT DISTINCT top 3 SC.S# AS 學(xué)生學(xué)號, Student.Sname AS 學(xué)生姓名 , T1.score AS 企業(yè)管理, T2.score AS 馬克思, T3.

33、score AS UML, T4.score AS 數(shù)據(jù)庫, 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.S# = T1.S# AND T1.C# = '001' LEFT JOIN SC AS T2 ON SC.S# = T2.S# AND T2.C# = '002' LEFT JOIN SC AS T3 ON SC.S# = T3.S# AND T

34、3.C# = '003' LEFT JOIN SC AS T4   ON SC.S# = T4.S# AND T4.C# = '004'WHERE student.S#=SC.S# AND ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) NOT IN ( SELECT DISTINCT TOP 15 WITH TIES ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0)

35、 + ISNULL(T4.score,0) FROM sc LEFT JOIN sc AS T1 ON sc.S# = T1.S# AND T1.C# = 'k1' LEFT JOIN sc AS T2 ON sc.S# = T2.S# AND T2.C# = 'k2' LEFT JOIN sc AS T3 ON sc.S# = T3.S# AND T3.C# = 'k3' LEFT JOIN sc AS T4 ON sc.S# = T4.S# AND T4.C# = 'k4' ORDER BY ISNULL(T1.score,0

36、) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) DESC ); 23、統(tǒng)計(jì)列印各科成績,各分?jǐn)?shù)段人數(shù):課程ID,課程名稱,100-85,85-70,70-60, <60 SELECT SC.C# as 課程ID, Cname as 課程名稱 ,SUM(CASE 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

37、- 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.C#=Course.C# GROUP BY SC.C#,Cname;修改后：90,100指定要加單引號變成一個(gè)字符串！SELECT c.C# AS 課程號, c.Cname AS 課程名稱, SUM(CASE WHEN score BETWEEN 90 AND 100 THEN 1 ELSE

38、 0 END) AS '90,100', SUM(CASE WHEN score BETWEEN 75 AND 89 THEN 1 ELSE 0 END) AS '89,75', SUM(CASE WHEN score BETWEEN 60 AND 74 THEN 1 ELSE 0 END) AS '74,60', SUM(CASE WHEN score <60 THEN 1 ELSE 0 END) AS '59,-'FROM sc s,course cWHERE s.C#=c.C# GROUP BY s.C#;24、 查詢

39、學(xué)生平均成績及其名次 SELECT 1+(SELECT COUNT( distinct 平均成績) FROM (SELECT S#,AVG(score) AS 平均成績 FROM SC GROUP BY S# ) AS T1 WHERE 平均成績 > T2.平均成績) as 名次, S# as 學(xué)生學(xué)號,平均成績 FROM (SELECT S#,AVG(score) 平均成績 FROM SC GROUP BY S# ) AS T2 ORDER BY 平均成績 desc; 修改后：SELECT 1+( SELECT COUNT(DISTINCT 平均成績) FROM (SELECT S#,

40、AVG(score) AS 平均成績 FROM SC GROUP BY S#) AS T1 WHERE 平均成績 > T2.平均成績 ) AS 名次, 學(xué)生學(xué)號, 平均成績 FROM ( SELECT S# AS 學(xué)生學(xué)號,AVG(score) 平均成績 FROM SC GROUP BY S# ) AS T2 ORDER BY 平均成績 DESC;25、查詢各科成績前三名的記錄:(不考慮成績并列情況) SELECT t1.S# as 學(xué)生ID,t1.C# as 課程ID,Score as 分?jǐn)?shù) FROM SC t1 WHERE score IN (SELECT TOP 3 score F

41、ROM SC WHERE t1.C#= C# ORDER BY score DESC ) ORDER BY t1.C#; 26、查詢每門課程被選修的學(xué)生數(shù) select C#,count(S#) from sc group by C#; 加課程名：SELECT s.C#,Cname,COUNT(DISTINCTS#) 選課人數(shù)FROM course c,sc s WHERE s.C#=c.C#GROUP BY s.C#;27、查詢出只選修了一門課程的全部學(xué)生的學(xué)號和姓名 select SC.S#,Student.Sname,count(C#) AS 選課數(shù) from SC ,Student w

42、here SC.S#=Student.S# group by SC.S# ,Student.Sname having count(C#)=1; 自己寫：SELECT student.S#,Sname,選課數(shù)FROM student, (SELECT sc.S#,COUNT(sc.C#) 選課數(shù) FROM sc GROUP BY sc.S#) AS 選課統(tǒng)計(jì)WHERE student.S#=選課統(tǒng)計(jì).S# AND 選課數(shù)=1;28、查詢男生、女生人數(shù) Select Ssex, count(Ssex) as 人數(shù)from Studentgroup by Ssex29、查詢姓“張”的學(xué)生名單 SEL

43、ECT Sname FROM Student WHERE Sname like '張%' 30、查詢同名同性別學(xué)生名單，并統(tǒng)計(jì)同名人數(shù) select Sname,count(*) from Student group by Sname having count(*)>1;加性別：SELECT sname,ssex,COUNT(*) 相同人數(shù) FROM student GROUP BY Sname,Ssex HAVING COUNT(*)>1;31、1981年出生的學(xué)生名單(注：Student表中Sage列的類型是datetime) select Sname, CON

44、VERT(char (11),DATEPART(year,Sage) as age from student where CONVERT(char(11),DATEPART(year,Sage)='1981' Mysql中沒有DATEPART，使用DATE_FORMATSELECT S#,Sname,DATE_FORMAT(Sage,'%Y') 出生日期,2016-DATE_FORMAT(Sage,'%Y') 年齡 FROM student;32、查詢每門課程的平均成績，結(jié)果按平均成績升序排列，平均成績相同時(shí)，按課程號降序排列 Select C#

45、,Avg(score) from SC group by C# order by Avg(score),C# DESC ; 優(yōu)先排序 二級排序33、 查詢平均成績大于80的所有學(xué)生的學(xué)號、姓名和平均成績 select Sname,SC.S# ,avg(score) from Student,SC where Student.S#=SC.S# group by SC.S# having  avg(score)>80; 34、查詢課程名稱為“數(shù)學(xué)”，且分?jǐn)?shù)低于60的學(xué)生姓名和分?jǐn)?shù)，若沒選這節(jié)課，用0代替。 Select Sname,score from Student,S

46、C,Course where SC.S#=Student.S# and SC.C#=Course.C# and Course.Cname='數(shù)據(jù)庫'and score ; 原答案并不能顯示成績?yōu)榱愕挠涗洠揪筒淮嬖谠趺磳?？?student left join 。 35、查詢所有學(xué)生的選課情況； SELECT SC.S#, SC.C#, Sname, CnameFROM SC,Student,Course where SC.S#=Student.S# and SC.C#=Course.C# ; 36、查詢?nèi)魏我婚T課程成績在70分以上的姓名、課程名稱和分?jǐn)?shù)； SELECT di

47、stinct student.S#,student.Sname,SC.C#,SC.score FROM student,Sc WHERE SC.score>=70 AND SC.S#=student.S#; 補(bǔ)充后：SELECT Sname,Cname,scoreFROM Student,Course,scoreWHERE SC.S#=Student.S# AND SC.C#=Course.C# AND score>70;37、查詢P1004同學(xué)不及格的課程，并按課程號從大到小排列 select C# 陸華不及格課程 from sc where S#=P1004 score <

48、;60 order by C# ; 38、查詢課程編號為003且課程成績在80分以上的學(xué)生的學(xué)號和姓名； SELECT student.S#,student.Sname FROM student,course,scWHERE SC.S#=Student.S# AND SC.C#=Course.C# AND sc.C#='003' AND score>80; 39、求選了001課程的學(xué)生人數(shù) select count(*) from sc where C#=001; 40、查詢選修“葉平”老師所授課程的學(xué)生中，成績最高的學(xué)生姓名及其成績 正確答案：四表聯(lián)合SELECT St

49、udent.Sname,score 最高分FROM Student,SC,Course C,Teacher WHERE Student.S#=SC.S# AND SC.C#=C.C# AND C.T#=Teacher.T# AND Teacher.Tname='葉平' AND SC.score=(SELECT MAX(score) FROM SC WHERE C#=C.C# ); 說明：sc的別名只能在外側(cè)使用，內(nèi)測使用而外側(cè)不用，只能查出來一門課程，效果并不是等價(jià)的。自己寫的有錯(cuò)誤：人名只能為各組group的第一行，原因：？SELECT Sname,Cname 葉平老師課程,最高分 FROM student,course, (SELECT sc3.S#,