题目描述

    实现一个  学生  -  班级  - 课程 - 成绩的数据库
1. 初始化完成 不少于 10个学生 的 3个班级
2. 初始化完成 每个学生 需要选择不少于 3门课程(数学 英语 专业课). 同时每个学生 每个课程都有一个成绩 
3. 找到 各个班级数学最好的那个同学. 
4. 找到每个班级 数学最差的那个同学. 
5. 找个每个班级专业课成绩前3名的同学

create table class
(
        classID Varchar(20) ,
        constraint pk_class primary key (classID) 
)default charset utf8;

create table student
(
        stuID Varchar(20) ,
        classID Varchar(20) ,
        stuName Varchar(20) ,
        constraint pk_student primary key (stuID),
        constraint fk_student foreign key (classID) references class(classID)
)default charset utf8;

create table course
(
        courseID Varchar(20) ,
        courseName Varchar(20) ,
        constraint pk_course primary key (courseID)
)default charset utf8;

create table sc
(
        stuID Varchar(20) ,
        courseID Varchar(20) ,
        score int ,
        constraint pk_sc primary key (stuID,courseID),
        constraint fk_sc_stu foreign key (stuID) references student(stuID),
        constraint fk_sc_course foreign key (courseID) references course(courseID)
)default charset utf8;

/*添加数据*/
/*class : A,B,C */
insert into class(classID) values('A');
insert into class(classID) values('B');
insert into class(classID) values('C');

/*student : A(C,M,B,V),B(W,J,J,H),C(G,M,U,Q) */
insert into student(stuID,classID,stuName) values('A001','A','Charles');
insert into student(stuID,classID,stuName) values('A002','A','Mark');
insert into student(stuID,classID,stuName) values('A003','A','Bill');
insert into student(stuID,classID,stuName) values('A004','A','Vincent');

insert into student(stuID,classID,stuName) values('B001','B','William');
insert into student(stuID,classID,stuName) values('B002','B','Jseph');
insert into student(stuID,classID,stuName) values('B003','B','James');
insert into student(stuID,classID,stuName) values('B004','B','Henry');

insert into student(stuID,classID,stuName) values('C001','C','Gary');
insert into student(stuID,classID,stuName) values('C002','C','Martin');
insert into student(stuID,classID,stuName) values('C003','C','Ulrica');
insert into student(stuID,classID,stuName) values('C004','C','Quella');

/* course : Maths , English , Major */
insert into course(courseID,courseName) values('s001','Maths');
insert into course(courseID,courseName) values('s002','English');
insert into course(courseID,courseName) values('s003','Major');

/* sc : stuID , courseID , score */



/*Maths : s0001 */
insert into sc( stuID , courseID , score ) values( 'A001' , 's001' , 81 );
insert into sc( stuID , courseID , score ) values( 'A002' , 's001' , 87 );
insert into sc( stuID , courseID , score ) values( 'A003' , 's001' , 97 );
insert into sc( stuID , courseID , score ) values( 'A004' , 's001' , 59 );

insert into sc( stuID , courseID , score ) values( 'B001' , 's001' , 81 );
insert into sc( stuID , courseID , score ) values( 'B002' , 's001' , 68 );
insert into sc( stuID , courseID , score ) values( 'B003' , 's001' , 75 );
insert into sc( stuID , courseID , score ) values( 'B004' , 's001' , 90 );

insert into sc( stuID , courseID , score ) values( 'C001' , 's001' , 56 );
insert into sc( stuID , courseID , score ) values( 'C002' , 's001' , 50 );
insert into sc( stuID , courseID , score ) values( 'C003' , 's001' , 94 );
insert into sc( stuID , courseID , score ) values( 'C004' , 's001' , 61 );

/* English s002 */
insert into sc( stuID , courseID , score ) values( 'A001' , 's002' , 62 );
insert into sc( stuID , courseID , score ) values( 'A002' , 's002' , 89 );
insert into sc( stuID , courseID , score ) values( 'A003' , 's002' , 78 );
insert into sc( stuID , courseID , score ) values( 'A004' , 's002' , 74 );

insert into sc( stuID , courseID , score ) values( 'B001' , 's002' , 61 );
insert into sc( stuID , courseID , score ) values( 'B002' , 's002' , 95 );
insert into sc( stuID , courseID , score ) values( 'B003' , 's002' , 87 );
insert into sc( stuID , courseID , score ) values( 'B004' , 's002' , 56 );

insert into sc( stuID , courseID , score ) values( 'C001' , 's002' , 95 );
insert into sc( stuID , courseID , score ) values( 'C002' , 's002' , 66 );
insert into sc( stuID , courseID , score ) values( 'C003' , 's002' , 78 );
insert into sc( stuID , courseID , score ) values( 'C004' , 's002' , 58 );

/* Major s003 */
insert into sc( stuID , courseID , score ) values( 'A001' , 's003' , 97 );
insert into sc( stuID , courseID , score ) values( 'A002' , 's003' , 97 );
insert into sc( stuID , courseID , score ) values( 'A003' , 's003' , 87 );
insert into sc( stuID , courseID , score ) values( 'A004' , 's003' , 88 );

insert into sc( stuID , courseID , score ) values( 'B001' , 's003' , 90 );
insert into sc( stuID , courseID , score ) values( 'B002' , 's003' , 65 );
insert into sc( stuID , courseID , score ) values( 'B003' , 's003' , 91 );
insert into sc( stuID , courseID , score ) values( 'B004' , 's003' , 58 );

insert into sc( stuID , courseID , score ) values( 'C001' , 's003' , 87 );
insert into sc( stuID , courseID , score ) values( 'C002' , 's003' , 81 );
insert into sc( stuID , courseID , score ) values( 'C003' , 's003' , 79 );
insert into sc( stuID , courseID , score ) values( 'C004' , 's003' , 56 );