Have these two tables, requirements:
(1) the query to cno='3-105 that the highest and lowest points, as well as the students name
(2) query the average score of all courses, show all course name, course number peace prize
Is not very good!
Urgent for bosses to solve!!! Grateful!

CodePudding user response:

Brushed the is predestined friends, doing exercises, do the first question, first single course and minimum points corresponding to the student,

 
The select s.s no, s.s name, Arthur c. no, Arthur c. name, sc. The score from StudentInfo s, StudentScore sc, CourseInfo c 
Where s.s no=sc. Sno and sc. Cno=Arthur c. no and FIND_IN_SET (sc) score, (
Select CONCAT (Max (score), ', ', MIN (score)) from StudentScore where cno='3-105 
)) 

Knowledge: table LianZha, Max and Min minimum value function, maximum CONCAT string concatenation function, FIND_IN_SET function

Stick the query results:

 
Sno sname cno cname score 

103 Lu Jun 3-105 introduction to computer 92 
109 an introduction to wang fang 3-105 computer 76 

CodePudding user response:

The second question is quite simple, first join query add GROUP BY can,

 
The select Arthur c. no, Arthur c. name, avg (sc) score) as avg_score from CourseInfo c, StudentScore sc 
Where Arthur c. no=sc. Cno GROUP BY Arthur c. no, Arthur c. name 

Results:

 
Cno cname avg_score 

Introduction to computer 85.333333 3-105 
3-245 OS 76.333333 
6-166 digital circuit 81.666667