1 、--Table I: Student table student |--Table II: Teacher Table teacher
--School Number: Code int (primary key) starting from 1 |--teacher number: Code int (primary key) starting from 1001
Name: Name varchar (50) |--Course: Lesson char (10) (Chinese, maths, English)
--Gender: Sex char (10) |--Age: Ages int
--Class: Banji char (10) |--Birthday: birth datetime
--Language Teacher number: Yujiao int
--Math teacher number: Yujiao int
--English teacher number: Yujiao int
--Table III: SCORE Table score
--School Number: Code int (foreign key of student's study number)
--Language score: Yufen decimal (18,2)
--Math Score: Shufen decimal (18,2)
--English score: Yingfen decimal (18,2)
--Student table data: Insert three classes of student data, each class of the same subject teachers are the same, at least 5 people per class
--Teacher table data: Chinese teachers, math teachers, English teachers, respectively, three, each corresponding to a class
--Score Table data: Language, mathematics and English scores corresponding to the study number
SQL Server Sub-query