Top, bottom, and average statements for SQL queries

Top, bottom, and average statements for SQL queries
We'll use it as an example of student achievement.
/*
Structure

Student table
Student (s#,sname,sage,ssex)--s# student number, sname student name, Sage birth date, ssex student gender
--2. Timetable
Marshalling (c#,cname,t#)--c#--course number, Cname course name, t# teacher number

*/

inquires the highest score, the lowest score and the average score in each section: The course ID, course name, highest score, lowest score, average score, pass rate, medium rate, good rate, excellent rate


-Pass for >=60, medium for: 70-80, excellent for: 80-90, excellent for: >=90


--Method 1


Select m.c# [Course number], M.cname [course name],


Max (N.score) [Highest score],


min (n.score) [lowest score],


CAST (AVG (N.score) as Decimal (18,2)) [Average score],


cast (select COUNT (1) from SC where C # = m.c# and score >=) *100.0/(select COUNT (1) from SC where C # = m.c#) as Decim Al (18,2)) [Pass rate (%)],


cast (select COUNT (1) from SC where C # = m.c# and score >= and score <) *100.0/(select COUNT (1) from SC where C # = m.c#) as decimal (18,2) [Medium rate (%)],


Cast ((select count (1) from SC where C # = m.c# and score >= and score <) *100.0/(select COUNT (1) from SC where C # = M.C # as Decimal (18,2)) [Good rate (%)],


cast (select COUNT (1) from SC where C # = m.c# and score >=) *100.0/(select COUNT (1) from SC where C # = m.c#) as Decim Al (18,2)) [excellent rate (%)]


from marshalling m, SC N


where m.c# = n.c#


GROUP by m.c#, M.cname


ORDER BY m.c#


--Method 2


Select m.c# [Course number], M.cname [course name],


(select Max (score) from SC where C # = m.c#) [Highest score],


(select min (score) from SC where C # = m.c#) [min.],


(select CAST (AVG (score) as decimal (18,2)) from SC where C # = m.c#) [average points],


cast (select COUNT (1) from SC where C # = m.c# and score >=) *100.0/(select COUNT (1) from SC where C # = m.c#) as Decim Al (18,2)) [Pass rate (%)],


Cast ((select count (1) from SC where C # = m.c# and score >= and score <) *100.0/(select COUNT (1) from SC where C # = M.C #) as Decimal (18,2) [Medium rate (%)],


cast (select COUNT (1) from SC where C # = m.c# and score >=-score <) *100.0/(select COUNT (1) from SC where C # = m.c#) as decimal (18,2) [Good rate (%)],


cast (select COUNT (1) from SC where C # = m.c# and score >=) *100.0/(select COUNT (1) from SC where C # = m.c#) as Decim Al (18,2)) [excellent rate (%)]


from marshalling M


ORDER BY m.c#