A bluff database.

Original works, allow reprint, reprint, please be sure to hyperlink form to indicate the original source of the article, author information and this statement. Otherwise, legal liability will be held. Http://blog.csdn.net/github_27109687/article/details/73810951-Test_huhy Blog A, topic

Student Student Table

Field name	field Type	field meaning
s#	Int	School Number
Sname	varchar	Student Name
Sage	varchar	Student Age
Ssex	varchar	Student gender

Course Timetable

Field name	field Type	field meaning
C#	Int	Course Number
Cname	varchar	Course Name

SC grade Table

Field name	field Type	field meaning
s#	Int	School Number
C#	Int	Course Number
Score	Float	Results

The average score is more than 60 points, surname: ' Wang ' and the course name is: ' Computer ' student's number and average score. Second, answer

The Mysql SQL statement is as follows:

Select T1. s#, ROUND (avrg,2) from (
    Select SC. S#,avg (Sc.score) avrg from RSS. SC SC GROUP by SC. s# 
) T1,rss. Student S,rss. SC Sc,rss. Course c
where s.s#=t1. s# and
    SC. S#=t1. s# and
    C.c#=sc. C # and
  avrg > '% ' and
  s.sname like ' King% ' and
  c.cname = ' computer ';

Third, summary and analysis (1) The analysis first calculates the average score of each student according to the result table, combining into a T1 with student ID and student's average score. Because the condition needs the surname ' King ', then needs the student table sname; the course name is ' Computer ', you need to course table CNAME, the requirement average is greater than 60, then T1 table is necessary, also need SC to show association table. So the T1 table and the three tables in the database do the associated connection.

3. The final link is a large table, as seen in the sketch above. Generally reasonable circumstances, the results table does not appear a student of a course has 2 score points, in order to avoid this situation, finally can add a ' GROUP by T1. s# ' or carry distinct to heavy. The SQL statement to distinct the method is as follows:

Select DISTINCT T1. s#, ROUND (avrg,2) from (
    Select SC. S#,avg (Sc.score) avrg from RSS. SC SC GROUP by SC. s# 
) T1,rss. Student S,rss. SC Sc,rss. Course c
where s.s#=t1. s# and
    SC. S#=t1. s# and
    C.c#=sc. C # and
  avrg > '% ' and
  s.sname like ' King% ' and
  c.cname = ' computer ';

(2) Summary

When you have functions such as count (), AVG (), SUM () in SQL, you should generally consider whether you want to group by. For example, the above average score, such as for each of the above students in the average grade, it is necessary to group by user ID for average results, and if a class in the case of a course, the average grade for the whole class, you do not need to use the group.

If there are omissions or incorrect places, please advise ~ ~ ~ ~