The statistics of the students ' course Achievement statistic SQL statement Daquan

Student score Table (Stuscore):

Name: Name	Course: Subject	Score: Score	School Number: Stuid
Tom	Mathematical	89	1
Tom	Chinese	80	1
Tom	English	70	1
John doe	Mathematical	90	2
John doe	Chinese	70	2
John doe	English	80	2

Create a table

  SET  ansi_nulls  on   GO   SET  quoted_identifier  on   GO   SET  Ansi_padding  on   GO   CREATE   TABLE  [ dbo ]. [Stuscore] ([name] [ varchar ] ()  COLLATE  chinese_prc_ci_as  NULL , [subject] [ varchar ]  COLLATE  chinese_prc_ci_as  NULL , [score] [ int ]  null
    , [stuid] [ int ]  NULL )  on  [ PRIMARY ]  GO   SET  Ansi_padding  OFF  

Problem:

1. Calculate the total number of each person and rank (requires display field: Name, overall scores)
2. Calculate each person's total and rank (required field: number, name, overall)
3. Calculate the highest score for each individual (required field: School number, name, course, highest score)
4. Calculate each person's average score (requires display field: study number, name, average score)
5. List the students who have the best grades in each course (Required fields: Student number, name, subject, score)
6. List of the two students who have the best results in each course (Required fields: Student number, name, subject, score)
7. The statistics are as follows:

School Number	Name	Chinese	Mathematical	English	Score	Average score

8. List the average scores for each course (Required fields: course, average)
9. List the ranking of math scores (required fields: School number, name, score, rank)
10. List of students with a mathematical score of 2-3 (Requirements display field: Student number, name, subject, score)
11. Find out the ranking of John Doe's mathematical achievements
12. Statistics are as follows:

Course	Failed (0-59)	Liang (60-80)	Excellent (81-100)

13. The statistics are as follows: Mathematics: Zhang San (50 points), John Doe (90 points), Harry (90 points), Zhao Liu (76 points)

Answer:


1. Calculate each person's total and rank

Select name,sumas fromgroupbyorder by Allscore

2. Calculate each person's total and rank

Select distinct  from  Stuscore T1, (    Select stuid,sumas fromgroup by Stuid) T2 where t1.stuid=t2.stuidorderbydesc

3. Calculate the highest score for each individual

Select  from Stuscore T1, (select stuid,maxas fromgroup  by Stuid) T2where and T1.score=t2.maxscore

4. Calculate the average score for each person

Select distinct  from Stuscore T1, (select stuid,avg. As fromgroup by STUID) T2where t1.stuid=t2.stuid

5. List the students with the best grades in each course

Select   from Stuscore T1, (select subject,maxas fromgroup  by subject) T2where and T1.score=t2.maxscore

6. List of the two students who have the best results in each course

Select distinct  from where inch (selecttopfromwhereorderbydescOrder   by T1.subject

7. School Number name Chinese mathematics English score average

Select  as  as name,sum(case when subject=' language 'then elseEnd as language,sum( case when subject=' math 'then    elseEnd as math,sum( case when subject=' English '  Then Else End  as English,sum as Total score, (sum(score)/count as average from  stuscoreGroupbyorder by Total (DESC)

8. List the average results of each course

Select Subject,avg as from Stuscoregroup by subject

9. List the ranking of math scores

Declare @tmp Table int varchar int int) Insert  into @tmp Select NULL  from where subject=' math 'orderbydescdeclare@id  intset@id= 0; Update @tmp Set @id=@id+1,pm=@idSelectfrom@tmp

Select  Dense_rank  Over (orderbydescas fromwhere subject=' math 'order  bydesc

10. List of students with a math score of 2-3

Select t3.*  from (selecttop 2 t2.* from   (  Selecttopfrom where subject=' math 'order by desc Order  by Order  by desc

11. Find out the ranking of John Doe's mathematical achievements

Declare @tmp Table int varchar int int) Insert  into @tmp Select NULL  from where subject=' math 'orderbydescdeclare@id  intset@id= 0; Update @tmp Set @id=@id+1,pm=@idSelectfrom@tmpwhere name=' John Doe '

12. Failing course (-59) Good (-80) Excellent (-100)

Select Subject, (selectcountfromwhere and as failed, ( Select Count  from where between  and  and  as benign, (selectcountfromwhere and as excellent  from Group  by subject

13. Mathematics: Zhang San (50 points), John Doe (90 points), Harry (90 points), Zhao Liu (76 points)

Declare @s varchar (1000) Set @s='select@s =@s+', '+name+' ('+convert (varchar(), score) +' points 'fromwhere subject=' mathematics ' Set @s=stuff(@s,",")print' math: '+@s

The statistics of the students ' course Achievement statistic SQL statement Daquan