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

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.