SQL query statements with the highest score, lowest score, and average score

For SQL queries with the highest score, lowest score, and average score, take the Student score as an example. * structure Student table Student (S #, Sname, Sage, Ssex) -- S # Student ID, sname Student name, Sage year of birth, Ssex Student gender-2. course curriculum Course (C #, Cname, T #) -- C # -- Course No., Cname Course name, T # instructor no. * queries the highest score of each subject, the highest score of SQL query, the lowest score, and the average score
// Take the student score as an example.
/*
Structure

Student table
Student (S #, Sname, Sage, Ssex) -- S # Student ID, Sname Student name, Sage year of birth, Ssex Student gender
-- 2. Curriculum
Course (C #, Cname, T #) -- C # -- Course No., Cname Course name, T # instructor No.

*/

The highest score, lowest score, and average score of each subject are displayed in the following format: course ID, course name, highest score, lowest score, average score, pass rate, medium rate, excellent rate, and excellent rate.
-- Pass> = 60, medium: 70-80, excellent: 80-90, excellent:> = 90
-- Method 1
Select m. C # [course No.], m. Cname [Course name],
Max (n. score) [highest score],
Min (n. score) [minute score],
Cast (avg (n. score) as decimal (18, 2) [average score],
Cast (select count (1) from SC where C # = m. C # and score> = 60) * 100.0/(select count (1) from SC where C # = m. C #) as decimal () [pass rate (%)],
Cast (select count (1) from SC where C # = m. C # and score> = 70 and score <80) * 100.0/(select count (1) from SC where C # = m. C #) as decimal () [medium rate (%)],
Cast (select count (1) from SC where C # = m. C # and score> = 80 and score <90) * 100.0/(select count (1) from SC where C # = m. C #) as decimal () [excellent rate (%)],
Cast (select count (1) from SC where C # = m. C # and score> = 90) * 100.0/(select count (1) from SC where C # = m. C #) as decimal () [excellent rate (%)]
From Course m, SC n
Where m. C # = n. C #
Group by m. C #, m. Cname
Order by m. C #
-- Method 2
Select m. C # [course No.], m. Cname [Course name],
(Select max (score) from SC where C # = m. C #) [highest score],
(Select min (score) from SC where C # = m. C #) [odds score],
(Select cast (avg (score) as decimal () from SC where C # = m. C #) [average score],
Cast (select count (1) from SC where C # = m. C # and score> = 60) * 100.0/(select count (1) from SC where C # = m. C #) as decimal () [pass rate (%)],
Cast (select count (1) from SC where C # = m. C # and score> = 70 and score <80) * 100.0/(select count (1) from SC where C # = m. C #) as decimal () [medium rate (%)],
Cast (select count (1) from SC where C # = m. C # and score> = 80 and score <90) * 100.0/(select count (1) from SC where C # = m. C #) as decimal () [excellent rate (%)],
Cast (select count (1) from SC where C # = m. C # and score> = 90) * 100.0/(select count (1) from SC where C # = m. C #) as decimal () [excellent rate (%)]
From Course m
Order by m. C #