SQL Query Questions: Query the distribution of scores

Hehe, you're a bunch of questions:

Excellent, good ......(Number of people) at each level:

First of all, let's say your score is on a 100-point scale, excellent i.e. above 85 points, etc...

So: select course, count(*)as number, 'excellent' as category.

from Grades.

where Score 85

Group by Courses.

unionselect course, count(*)as number, 'good' as category.

from Grades.

where score >> 70 and fraction <=85

Group by Courses.

unionselect course, count(*)as number, 'pass' as category.

from Grades.

where score >> 60 and fraction <=70

Group by Courses.

Distribution and percentage:

It will have to be calculated based on the above results.

Let's get a view of the total number of people first: select count(*)as total number of people from score.

Recalculate the percentage:

select Courses, Number of Participants*100 as Percentage from Number of Others at Each Level Group by Course, Category.

The rest of the statistics are similar.

At first glance, it's the title in the book, look for the answer after class, it's all there!

select name,sum(score) as allscore from stuscore group by name order by allscore

Here is the answer to the question:

All right, but if this question is pure, the nesting in the second answer is completely unnecessary and will only reduce the efficiency of execution.

Judging from the execution results, both ways are correct.

But if anyone writes the second type at work, I'm afraid that they won't be beaten out of the ...... by their colleagues

I rubbed it, and what the experts wrote was not right.

select ,,from table name a,select sno,min(score) score from table name group by cno) b

where and

Let's say the table name is sc

Select name, cname as the subject with the lowest score, score from sc, select sname as name, min(score)as score from sc

group by sname)t1

where score.

I understand the meaning of the title:

In the 3-105 course, all records of those students with a higher score than the number 109 score.

select *

from score a

where sno in (

select sno

from score b

where cno='3-105'

and >(select degree from score where sno='109' and cno = '3-105'))

select student number,score from table name where score=(select max(score) from table name );

Query each person's highest score sql:

First, use group by and max to get the highest score and subject, and then check the score table to find records with the same subject and score.

select b.* from (select max(score) t,course from score group by course) a,score b where and

The second way: get the highest score in the same subject first, and then query the score table to find the record with the highest scoreSelect * from score a where score=(select max(score) from score where course=

The third type: in the score table, when the subject is the same, there is no record with a score less than the score of other recordsSelect * from score a where not exists(select * from score where and

select student number,score from table name where score=(select max(score) from table name );

In this blog, there is an information query about the students with the highest scores in each subject, and the sub-query is a single subject, which was not searched before, and it is sent out today, so that everyone can walk less.

Your information is not enough for me to understand how the source gives you the answer, bai is obvious, if the first place has to be and.

There is also a tie for the second place

The above answers from all of you using daotop are all wrong.

Therefore, it is recommended that you give the table structure so that I can help you analyze the problem.

Or you try it like this:

select * from tbl_score where score =

select distinct top 1 score from tbl_score where score not in(

select distinct top 1 score from tbl_score order by score desc

order by score desc

I've tested it on my own provisional table, and it should be enough to check the second place result, including everyone who is tied for second place.

I wonder if that's what the landlord wants?