SQL written test

Student score Table (Stuscore):
Name: Name course: Subject Score: Score No.: Stuid
Zhang San Mathematics 89 1
Zhang San Languages 80 1
Zhang San English 70 1
John Doe Mathematics 90 2
John Doe Languages 70 2
John Doe English 80 2

1. Calculate the total number of each person and rank (requires display field: Name, overall scores)

Answer: Select Name,sum (Score) as Allscore from Stuscore Group by name ORDER by Allscore

2. Calculate each person's total and rank (required field: number, name, overall)

Answer: SELECT DISTINCT t1.name,t1.stuid,t2.allscore from Stuscore T1, (select Stuid,sum (Score) as Allscore from Stuscore g Roup by Stuid) t2where T1.stuid=t2.stuidorder by T2.allscore Desc

3. Calculate the highest score for each individual (required field: School number, name, course, highest score)

Answer: Select T1.stuid,t1.name,t1.subject,t1.score from Stuscore T1, (select Stuid,max (Score) as Maxscore from Stuscore Group by Stuid) T2where T1.stuid=t2.stuid and T1.score=t2.maxscore

4. Calculate each person's average score (requires display field: study number, name, average score)

Answer: SELECT DISTINCT t1.stuid,t1.name,t2.avgscore from Stuscore T1, (select Stuid,avg (Score) as Avgscore from Stuscore Group by Stuid) T2where T1.stuid=t2.stuid

5. List the students who have the best grades in each course (Required fields: Student number, name, subject, score)

Answer: Select T1.stuid,t1.name,t1.subject,t2.maxscore from Stuscore T1, (select Subject,max (Score) as Maxscore from Stuscore Group by subject) T2where T1.subject=t2.subject and T1.score=t2.maxscore

6. List of the two students who have the best results in each course (Required fields: Student number, name, subject, score)

Answer: SELECT DISTINCT t1.* from Stuscore T1 where t1.id in (select top 2 stuscore.id from stuscore where subject = T1.subject ORDER BY score Desc) Order by T1.subject

7. Statistics are as follows: School Number name Chinese mathematics English score average

Answer: Select Stuid as number, name as name, sum (case when subject=\ ' language \ ' then score else 0 end) as language, sum (case when subject=\ ' math \ ' th En score Else 0 end) as mathematics, sum (case when subject=\ ' English \ ' then score else 0 end) as English, sum (score) as Total, (SUM (Score)/count (*) As average Stuscoregroup by stuid,name order BY total desc

8. List the average scores for each course (Required fields: course, average)

Answer: Select Subject,avg (Score) as Avgscore from Stuscoregroup by subject

9. List the ranking of math scores (required fields: School number, name, score, rank)

Answer:

Declare @tmp table (PM int,name varchar (), score Int,stuid int)
Insert INTO @tmp select Null,name,score,stuid from Stuscore where subject=\ ' math \ ' ORDER BY score Desc
DECLARE @id int
Set @id = 0;
Update @tmp set @[email Protected]+1,[email protected]
SELECT * FROM @tmp

Oracle
Select Dense_rank () over (order BY score Desc) as Row,name,subject,score,stuid from Stuscore where subject=\ ' math \ ' ORDER BY Score desc
MS SQL (best choice)
Select (select COUNT (*) from Stuscore t1 where subject =\ ' math \ ' and T1.score>t2.score) +1 as row, Stuid,name,score from S Tuscore T2 where subject =\ ' math \ ' ORDER BY score Desc

10. List of students with a mathematical score of 2-3 (Requirements display field: Student number, name, subject, score)

Answer: Select t3.* from (select top 2 t2.* from (select Top 3 name,subject,score,stuid from Stuscore where subject=\ ' math \ ' Orde R by score Desc) T2 ORDER by T2.score) T3 ORDER BY T3.score Desc

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

Answer:

Declare @tmp table (PM int,name varchar (), score int,stuid int) insert INTO @tmp select Null,name,score,stuid from Stuscore where subject=\ ' math \ ' ORDER BY score Descdeclare @id intset @id =0;update @tmp set @[email protected]+1,[email protected] * From @tmp where name=\ ' john Doe \ '

12. Statistics are as follows: Failure of course (0-59) Good (60-80) Excellent (81-100)

Answer: Select Subject, (select COUNT (*) from Stuscore where score<60 and Subject=t1.subject) as failed, (select COUNT (*) from S Tuscore where score between and subject=t1.subject) as benign, (select COUNT (*) from Stuscore where score >80 and S Ubject=t1.subject) as excellent from Stuscore T1 GROUP by subject

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

Answer:

DECLARE @s varchar (+) set @s=\ ' \ ' select @s [email protected]+\ ', \ ' +name+\ ' (\ ' +convert (varchar), score) +\ ') \ ' From Stuscore where subject=\ ' math \ ' Set @s=stuff (@s,1,1,\ ' \ ') print \ ' math: \ ' [email protected]

14. Average scores of people who have passed the calculation of the section

Answer: SELECT DISTINCT t1.stuid,t2.avgscore from Stuscore T1, (select Stuid,avg (Score) as Avgscore from Stuscore group by S TUID) T2, (select Stuid from Stuscore where score<60 Group by stuid) T3 where T1.stuid=t2.stuid and T1.stuid!=t3.stuid ;
Select Name,avg (Score) as Avgscore from Stuscore s where (select sum (case i.score>=60 then 1 else 0 end) from Stuscore I where i.name= s.name) =3 GROUP by name

SQL written test