Top, bottom, and average statements for SQL queries

Source: Internet
Author: User
Keywords Network programming Database synthesis
Tags cname course date example name network network programming programming

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#


Related Article

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.