数据透视表中级班练习三

JLxiangwei

SQL练习题1 .rar (43.49 KB, 下载次数: 203)

2014-5-13 21:36 上传

点击文件名下载附件

先复习，做好后答案直接上传就行



------------------------------------------------------------------------------------
开贴
请参看其他同学的答案，附上解析视频
http://www.excelpx.com/thread-325760-1-1.html

云影

多谢老师指点，已修改{:011:}
练习2

1. SELECT count(*) as Mrli_Count  FROM [tblTeacher$] where teaname like "李%"

复制代码

练习13

1. select Courseid as 课程ID,max(Score) AS 最高分,min(Score) AS 最低分 from[tblScore$] group by Courseid

复制代码

练习19

1. select Stusex,count(*) AS StuSex_count from[tblStudent$] GROUP BY Stusex

复制代码

练习20

1. select StuName,COUNT(*) as Stu_Count from[tblStudent$] group by StuName  having COUNT(*)>1

复制代码

练习21

1. select Courseid,ROUND(Avg(score),2) as Score_Avg from [tblScore$] group by CourseId order by Avg(score) ASC, Courseid DESC

复制代码

练习32

1. select Stuid from[tblscore$] group by Stuid having count(*)>1

复制代码

练习34

1. select Stuid,Courseid,Score from[tblscore$] where Courseid ='004'and Score <60 order by Stuid desc, Score desc

复制代码

车仁静

2

1. SELECT StuId,ROUND(AVG(Score),2) AS AvgScore FROM [tblScore$] GROUP BY StuId HAVING AVG(Score)>60

复制代码

4

1. SELECT COUNT(TeaName) AS Mrli_Count FROM [tblTeacher$] WHERE TeaName LIKE '李%'

复制代码

13

1. SELECT CourseId AS 课程ID,MAX(Score) AS 最高分,MIN(Score) AS 最低分 FROM [tblScore$] GROUP BY CourseId

复制代码

19

1. SELECT Stusex AS StuSex,COUNT(Stusex) AS StuSex_count FROM [tblStudent$] GROUP BY Stusex

复制代码

20

1. SELECT StuName,COUNT(StuName) AS Stu_Count FROM [tblStudent$] GROUP BY StuName HAVING COUNT(StuName)>2

复制代码

21

1. SELECT CourseId,FORMAT(AVG(Score),"#.00") AS Score_Avg FROM [tblScore$] GROUP BY CourseId ORDER BY AVG(Score),CourseId DESC

复制代码

32

1. SELECT StuId FROM [tblScore$] GROUP BY StuId HAVING COUNT(CourseId)>1

复制代码

34

1. SELECT StuId,CourseId,Score FROM [tblScore$] WHERE CourseId="004" AND Score<60 ORDER BY Score DESC

复制代码

天空的雨

SQL练习题1 -天空的雨答.zip (42.03 KB, 下载次数: 14)

2014-5-14 11:24 上传

点击文件名下载附件






2014年5月24日订正：
序号为20的题：查询同名同性学生名单，并统计同名人数？
select StuName as 同名同姓人员名单,(select count(StuName) from[tblStudent$] where StuName= a.StuName) as 同名人数 from[tblStudent$]a group by StuName, Stusex having count(Stuage)>=2

lasharks

2.

1. select * from (select StuId,avg(Score) as AvgScore from [tblScore$] group by stuid) where avgscore>60
   

复制代码

4.

1. select count(*) as li_count from [tblteacher$] where left(teaname,1)="李"
   

复制代码

13.

1. select CourseId,max(score) as 最高分,min(score) as 最低分 from [tblscore$] group by courseid
   

复制代码

19.

1. select StuSex,count(*) as Sex_Count from [tblstudent$] group by stusex
   

复制代码

20.

1. select * from (select stuname,count(*) as name_count from[tblstudent$] group by stuname) where name_count>1
   

复制代码

21.

1. select CourseId,avg(score) as AvgScore from [tblscore$] group by courseid order by avg(score) ,courseid desc
   

复制代码

32.

1. select StuId from (select stuid,count(*) as course_count from [tblscore$] group by stuid) where course_count>=2
   

复制代码

34.

1. select StuId,CourseID,Score from [tblscore$] where Courseid="004" and score < 60 order by score desc
   

复制代码

xdragon

1. SELECT * FROM (SELECT StuId,AVG(Score) AS AvgScore FROM [tblScore$] GROUP BY StuId) WHERE AvgScore>60
   
2. SELECT COUNT(TeaName) AS 个数 FROM [tblTeacher$] WHERE LEFT(TeaName,1)='李'
   
3. SELECT CourseId AS 课程ID,MAX(Score) AS 最高分,MIN(Score) AS 最低分 FROM [tblScore$] GROUP BY CourseId
   
4. SELECT Stusex,COUNT(Stusex) AS StuSex_count FROM [tblStudent$] GROUP BY Stusex ORDER BY StuSex DESC
   
5. SELECT StuName,Stu_Count FROM (SELECT StuName,COUNT(StuName) AS Stu_Count FROM [tblStudent$] GROUP BY StuName) WHERE Stu_Count>1
   
6. SELECT CourseId,FORMAT(AVG(Score),"0.00") AS Score_Avg FROM [tblScore$] GROUP BY CourseId ORDER BY AVG(Score),CourseId DESC
   
7. SELECT StuId FROM (SELECT StuId,COUNT(CourseId) AS a FROM [tblScore$] GROUP BY StuId) WHERE a>=2
   
8. SELECT StuId,CourseId,Score FROM [tblScore$] WHERE CourseId='004' AND Score<60 ORDER BY Score DESC

复制代码

这个。感觉绕弯路了，麻烦老师看看了

zx_wl

结果2、

1. select StuId,round(sum(score)/count(stuid),2) as AvgSoure from [tblScore$] group by stuid having sum(score)/count(stuid)>60

复制代码

结果4、

1. select count(TeaName) as Mrli_Count from [tblTeacher$] group by TeaName having left(TeaName,1)="李"

复制代码

结果13、

1. select CourseId as 课程ID,max(Score) as 最高分,min( Score) as 最低分 from [tblScore$] group by CourseId

复制代码

结果19、

1. select Stusex,count(StuName) as StuSex_count from [tblStudent$] group by Stusex

复制代码

结果20、

1. select StuName,count(Stuage) as Stu_Count from [tblStudent$] group by StuName having count(Stuage)>1

复制代码

结果21、

1. select CourseId,format(Avg(Score),"0.00") as Score_Avg from [tblScore$] group by CourseId order by Avg(Score)

复制代码

结果32

1. select iif(count(StuID)>1,StuId,null) as StuId from [tblScore$] group by StuId having iif(count(StuID)>1,StuId,null) <> null

复制代码

hsl215

希望向老师帮忙看看写的是否正确，另上课可以旁听不？
2、SELECT STUID,AVG(SCORE) AS MEAN FROM [tblScore$] GROUP BY STUID
4、SELECT COUNT(TEANAME) AS QTY FROM [tblTeacher$] WHERE TEANAME LIKE "李%" GROUP BY TEANAME
13、SELECT CourseId, MIN(Score) AS LOW,MAX(Score) AS HIG FROM [tblScore$] GROUP BY CourseId
19、SELECT Stusex,COUNT(*) AS QTY FROM [tblStudent$] GROUP BY Stusex
20、SELECT StuName,COUNT(*) AS QTY FROM [tblStudent$] GROUP BY StuName
21、SELECT CourseId,AVG(SCORE) AS MEAN FROM [tblScore$] GROUP BY CourseId  ORDER BY AVG(SCORE),CourseId DESC
32、SELECT StuId, COUNT(CourseId) FROM [tblScore$] GROUP BY StuId having COUNT(CourseId)>1
34、SELECT StuId,CourseId,Score FROM [tblScore$] where CourseId="004"  and  Score < 60 ORDER BY Score DESC

xiaoni

交作业：
第一题：select stuid,round(avg(score),2) as avgsoure from [tblscore$] group by stuid having avg(score)>60
第二题：select count(*) as Mrli_count from [tblteacher$] where teaname like "李%"
第三题：select courseid as 课程ID, max(score) as 最高分，min(score) as 最低分 from [tblscore$] group by courseid
第四题：select stusex, count(*) as stusex_count from [tblstudent$] group by stusex
第五题：select stuname,count(stuname) as stuname_count from [tblstudent$] group by stuname having count(stuname)>1
第六题：select CourseID, format(Avg(score),"0.00") as Score_Avg from [tblscore$] group by courseid order by Avg(score),CourseID desc
每七题：select StuId from [tblscore$] group by stuid having count(stuid)>1
第八题：select StuId, CourseId, Score from [tblscore$] where CourseId="004" and Score<60 order by Score desc

午夜洗衣机

向老师,怎么每一题都没有子查询或者联合查询的?
交作业:

1. 2        select StuId,avg(Score) as AvgScore from [tblScore$] group by StuId having avg(Score)>60
   
2. 4        select count(TeaName) as Mrli_count from [tblTeacher$] where TeaName like "李%"
   
3. 13        select CourseID as 课程,Max(Score) as 最高分,min(Score) as 最低分 from [tblScore$] group by CourseID
   
4. 19        select Stusex,count(Stusex) as StuSex_count from [tblStudent$] group by Stusex
   
5. 20        select StuName,count(StuName) as Stu_Count from [tblStudent$] group by StuName having count(StuName&Stusex)>1
   
6. 21        select CourseId,Avg(Score) as Score_Avg from [tblScore$] group by CourseId order by Avg(Score),CourseId desc
   
7. 32        select StuId from [tblScore$] group by StuId having count(StuId&CourseId)>1
   
8. 34        select StuId,CourseId,Score from [tblScore$] where CourseId="004" and Score<60 order by Score desc
   

复制代码

第32题我自己加了一个子查询,查询至少选修两门课程的学生学号和姓名:

1. select StuId,StuName from [tblStudent$] where StuId in(select StuId from [tblScore$] group by StuId having count(StuId&CourseId)>1) group by StuId,StuName

复制代码