蜜汁满满,亚洲成av人片好吊一区二区三区

student(s#,sname,sage,ssex) 學(xué)生表
course(c#,cname,t#) 課程表
sc(s#,c#,score) 成績(jī)表
teacher(t#,tname) 教師表

問題：
1、查詢“001”課程比“002”課程成績(jī)高的所有學(xué)生的學(xué)號(hào)；

代碼如下:

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.s#=b.s#;

2、查詢平均成績(jī)大于60分的同學(xué)的學(xué)號(hào)和平均成績(jī)；

代碼如下:

select s#,avg(score)

from sc

group by s# having avg(score) >60;

3、查詢所有同學(xué)的學(xué)號(hào)、姓名、選課數(shù)、總成績(jī)；

代碼如下:

select student.s#,student.sname,count(sc.c#),sum(score)

from student left outer join sc on student.s#=sc.s#

group by student.s#,sname

4、查詢姓“李”的老師的個(gè)數(shù)；

代碼如下:

select count(distinct(tname))

from teacher

where tname like '李%';

5、查詢沒學(xué)過“葉平”老師課的同學(xué)的學(xué)號(hào)、姓名；

代碼如下:

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='葉平');

6、查詢學(xué)過“001”并且也學(xué)過編號(hào)“002”課程的同學(xué)的學(xué)號(hào)、姓名；

代碼如下:

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_2.c#='002');

7、查詢學(xué)過“葉平”老師所教的所有課的同學(xué)的學(xué)號(hào)、姓名；

代碼如下:

select 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、查詢課程編號(hào)“002”的成績(jī)比課程編號(hào)“001”課程低的所有同學(xué)的學(xué)號(hào)、姓名；

代碼如下:

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;

9、查詢所有課程成績(jī)小于60分的同學(xué)的學(xué)號(hào)、姓名；

代碼如下:

select s#,sname

from student

where s# not in (select student.s# from student,sc where s.s#=sc.s# and score>60);

10、查詢沒有學(xué)全所有課的同學(xué)的學(xué)號(hào)、姓名；

代碼如下:

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#) from course);

11、查詢至少有一門課與學(xué)號(hào)為“1001”的同學(xué)所學(xué)相同的同學(xué)的學(xué)號(hào)和姓名；

代碼如下:

select s#,sname from student,sc where student.s#=sc.s# and c# in select c# from sc where s#='1001';

12、查詢至少學(xué)過學(xué)號(hào)為“001”同學(xué)所有一門課的其他同學(xué)學(xué)號(hào)和姓名；

代碼如下:

select distinct sc.s#,sname

from student,sc

where student.s#=sc.s# and c# in (select c# from sc where s#='001');

13、把“sc”表中“葉平”老師教的課的成績(jī)都更改為此課程的平均成績(jī)；

代碼如下:

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='葉平');

14、查詢和“1002”號(hào)的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)學(xué)號(hào)和姓名；

代碼如下:

select s# from 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表記錄；

代碼如下:

delect sc

from course ,teacher

where course.c#=sc.c# and course.t#= teacher.t# and tname='葉平';

16、向sc表中插入一些記錄，這些記錄要求符合以下條件：沒有上過編號(hào)“003”課程的同學(xué)學(xué)號(hào)、2、
號(hào)課的平均成績(jī)；

代碼如下:

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');

17、按平均成績(jī)從高到低顯示所有學(xué)生的“數(shù)據(jù)庫(kù)”、“企業(yè)管理”、“英語(yǔ)”三門的課程成績(jī)，按如下形式顯示：學(xué)生id,,數(shù)據(jù)庫(kù),企業(yè)管理,英語(yǔ),有效課程數(shù),有效平均分

代碼如下:

select s# as 學(xué)生id

,(select score from sc where sc.s#=t.s# and c#='004') as 數(shù)據(jù)庫(kù)

,(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#='006') as 英語(yǔ)

,count(*) as 有效課程數(shù), avg(t.score) as 平均成績(jī)

from sc as t

group by s#

order by avg(t.score)

18、查詢各科成績(jī)最高和最低的分：以如下形式顯示：課程id，最高分，最低分

代碼如下:

select l.c# as 課程id,l.score as 最高分,r.score as 最低分

from sc l ,sc as r

where l.c# = r.c# and

l.score = (select max(il.score)

from sc as il,student as im

where l.c# = il.c# and im.s#=il.s#

group by il.c#)

and

r.score = (select min(ir.score)

from sc as ir

where r.c# = ir.c#

group by ir.c#

);

19、按各科平均成績(jī)從低到高和及格率的百分?jǐn)?shù)從高到低順序

代碼如下:

select t.c# as 課程號(hào),max(course.cname)as 課程名,isnull(avg(score),0) as 平均成績(jī)

,100 * sum(case when isnull(score,0)>=60 then 1 else 0 end)/count(*) 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ī)和及格率的百分?jǐn)?shù)(用1行顯示): 企業(yè)管理（001），馬克思（002），oo¨ （003），數(shù)據(jù)庫(kù)（004）

代碼如下:

select sum(case when c# ='001' then score else 0 end)/sum(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 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# = '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ù)庫(kù)平均分

,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 數(shù)據(jù)庫(kù)及格百分?jǐn)?shù)

from sc

21、查詢不同老師所教不同課程平均分從高到低顯示

代碼如下:

select max(z.t#) as 教師id,max(z.tname) as 教師姓名,c.c# as 課程ｉｄ,max(c.cname) as 課程名稱,avg(score) as 平均成績(jī)

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

22、查詢?nèi)缦抡n程成績(jī)第 3 名到第 6 名的學(xué)生成績(jī)單：企業(yè)管理（001），馬克思（002），uml （003），數(shù)據(jù)庫(kù)（004）
[學(xué)生id],[學(xué)生姓名],企業(yè)管理,馬克思,uml,數(shù)據(jù)庫(kù),平均成績(jī)

代碼如下:

select distinct top 3

sc.s# as 學(xué)生學(xué)號(hào),

student.sname as 學(xué)生姓名 ,

t1.score as 企業(yè)管理,

t2.score as 馬克思,

t3.score as uml,

t4.score as 數(shù)據(jù)庫(kù),

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 t3.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) + 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) + isnull(t2.score,0) + isnull(t3.score,0) + isnull(t4.score,0) desc);

23、統(tǒng)計(jì)列印各科成績(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 - 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;

24、查詢學(xué)生平均成績(jī)及其名次

代碼如下:

select 1+(select count( distinct 平均成績(jī))

from (select s#,avg(score) as 平均成績(jī)

from sc

group by s#

) as t1

where 平均成績(jī) > t2.平均成績(jī)) as 名次,

s# as 學(xué)生學(xué)號(hào),平均成績(jī)

from (select s#,avg(score) 平均成績(jī)

from sc

group by s#

) as t2

order by 平均成績(jī) desc;

25、查詢各科成績(jī)前三名的記錄:(不考慮成績(jī)并列情況)

代碼如下:

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

from 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#;

27、查詢出只選修了一門課程的全部學(xué)生的學(xué)號(hào)和姓名

代碼如下:

select sc.s#,student.sname,count(c#) as 選課數(shù)

from sc ,student

where sc.s#=student.s# group by sc.s# ,student.sname having count(c#)=1;

28、查詢男生、女生人數(shù)

代碼如下:

select count(ssex) as 男生人數(shù) from student group by ssex having ssex='男';

select count(ssex) as 女生人數(shù) from student group by ssex having ssex='女'；

29、查詢姓“張”的學(xué)生名單

代碼如下:

select sname from student where sname like '張%';

30、查詢同名同性學(xué)生名單，并統(tǒng)計(jì)同名人數(shù)

代碼如下:

select sname,count(*) from student group by sname having count(*)>1;

31、1981年出生的學(xué)生名單(注：student表中sage列的類型是datetime)

代碼如下:

select sname, convert(char (11),datepart(year,sage)) as age

from student

where convert(char(11),datepart(year,sage))='1981';

32、查詢每門課程的平均成績(jī)，結(jié)果按平均成績(jī)升序排列，平均成績(jī)相同時(shí)，按課程號(hào)降序排列

代碼如下:

select c#,avg(score) from sc group by c# order by avg(score),c# desc ;

33、查詢平均成績(jī)大于85的所有學(xué)生的學(xué)號(hào)、姓名和平均成績(jī)

代碼如下:

select sname,sc.s# ,avg(score)

from student,sc

where student.s#=sc.s# group by sc.s#,sname having avg(score)>85;

34、查詢課程名稱為“數(shù)據(jù)庫(kù)”，且分?jǐn)?shù)低于60的學(xué)生姓名和分?jǐn)?shù)

代碼如下:

select sname,isnull(score,0)

from student,sc,course

where sc.s#=student.s# and sc.c#=course.c# and course.cname='數(shù)據(jù)庫(kù)'and score <60;

35、查詢所有學(xué)生的選課情況；

代碼如下:

select sc.s#,sc.c#,sname,cname

from sc,student,course

where sc.s#=student.s# and sc.c#=course.c# ;

36、查詢?nèi)魏我婚T課程成績(jī)?cè)?0分以上的姓名、課程名稱和分?jǐn)?shù)；

代碼如下:

select distinct student.s#,student.sname,sc.c#,sc.score

from student,sc

where sc.score>=70 and sc.s#=student.s#;

37、查詢不及格的課程，并按課程號(hào)從大到小排列

代碼如下:

select c# from sc where scor e <60 order by c# ;

38、查詢課程編號(hào)為003且課程成績(jī)?cè)?0分以上的學(xué)生的學(xué)號(hào)和姓名；

代碼如下:

select sc.s#,student.sname from sc,student where sc.s#=student.s# and score>80 and c#='003';

39、求選了課程的學(xué)生人數(shù)

代碼如下:

select count(*) from sc;

40、查詢選修“葉平”老師所授課程的學(xué)生中，成績(jī)最高的學(xué)生姓名及其成績(jī)

代碼如下:

select student.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# );

41、查詢各個(gè)課程及相應(yīng)的選修人數(shù)

代碼如下:

select count(*) from sc group by c#;

42、查詢不同課程成績(jī)相同的學(xué)生的學(xué)號(hào)、課程號(hào)、學(xué)生成績(jī)

代碼如下:

select distinct a.s#,b.score from sc a ,sc b where a.score=b.score and a.c# <>b.c# ;

43、查詢每門功成績(jī)最好的前兩名

代碼如下:

select t1.s# as 學(xué)生id,t1.c# as 課程id,score as 分?jǐn)?shù)

from sc t1

where score in (select top 2 score

from sc

where t1.c#= c#

order by score desc

)

order by t1.c#;

44、統(tǒng)計(jì)每門課程的學(xué)生選修人數(shù)（超過10人的課程才統(tǒng)計(jì)）。要求輸出課程號(hào)和選修人數(shù)，查詢結(jié)果按人數(shù)降序排列，查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號(hào)升序排列

代碼如下:

select c# as 課程號(hào),count(*) as 人數(shù)

from sc

group by c#

order by count(*) desc,c#

45、檢索至少選修兩門課程的學(xué)生學(xué)號(hào)

代碼如下:

select s#

from sc

group by s#

having count(*) > = 2

46、查詢?nèi)繉W(xué)生都選修的課程的課程號(hào)和課程名

代碼如下:

select c#,cname

from course

where c# in (select c# from sc group by c#)

47、查詢沒學(xué)過“葉平”老師講授的任一門課程的學(xué)生姓名

代碼如下:

select sname from student where s# not in (select s# from course,teacher,sc where course.t#=teacher.t# and sc.c#=course.c# and tname='葉平');

48、查詢兩門以上不及格課程的同學(xué)的學(xué)號(hào)及其平均成績(jī)

代碼如下:

select s#,avg(isnull(score,0)) from sc where s# in (select s# from sc where score <60 group by s# having count(*)>2)group by s#;

49、檢索“004”課程分?jǐn)?shù)小于60，按分?jǐn)?shù)降序排列的同學(xué)學(xué)號(hào)

代碼如下:

select s# from sc where c#='004'and score <60 order by score desc;

50、刪除“002”同學(xué)的“001”課程的成績(jī)

代碼如下:

delete from sc where s#='001'and c#='001';