建了这几个表，现在要查询每科课程中成绩大于课程平均分的学生信息
显示：课程编号+课程名+学号+学生姓名+科目成绩，快点，在线等20分钟，回答出来的再追加
--系别表
--depno
系别编号主键
--depname系别名
create table department(
depno varchar(10),
depname varchar(50) not null
)
drop table department;
alter table department add constraint pk_department_depno primary key(depno);
--专业表
--majno
专业编号主键
--majname
专业名称
--majcredit所修学分
--depno
所属系别外键
create table major(
majno varchar(10),
majname varchar(50) not null,
majcredit int not null,
depno varchar(10)
)
drop table major;
alter table major add constraint pk_major_majno primary key(majno);
alter table major add constraint fk_major_depno foreign key (depno) references department(depno);
--学生表
--stuno
学生学号主键
--stuname学生姓名
--stusex 学生性别
--stuage 学生年龄
--majno
所属专业外键
--depno
所属系别外键
create table student(
stuno varchar(10),
stuname varchar(50) not null,
stusex varchar(2) not null,
stuage int,
majno varchar(10),
depno varchar(10)
)
drop table student;
alter table student add constraint pk_student_stuno primary key(stuno);
alter table student add constraint fk_student_majno foreign key (majno) references major(majno);
alter table student add constraint fk_student_depno foreign key (depno) references department(depno);
--教师表
--teano
教师工号主键
--teaname
教师姓名
--teaposition教师职称
--depno
所属系别外键
create table teacher(
teano varchar(10),
teaname varchar(50) not null,
teaposition varchar(50) not null,
depno varchar(10)
)
drop table teacher;
alter table teacher add constraint pk_teacher_teano primary key(teano);
alter table teacher add constraint fk_teacher_depno foreign key(depno) references department(depno);
alter table teacher add teasex varchar(2) after teaname;
alter table teacher add teaage int after teaname;
--课程表
--cno
课程编号主键
--cname
课程名称
--tno
授课教师外键
create table course(
cno varchar(10) primary key,
cname varchar(50) not null,
teano varchar(10) not null,
depno varchar(10) not null
)
drop table course;
alter table course add majno varchar(10) after cname;
alter table course add constraint fk_course_majno foreign key (majno) references major(majno);
alter table course add constraint fk_course_teano foreign key(teano) references teacher(teano);
alter table course add constraint fk_course_depno foreign key(depno) references department(depno);
--成绩表
--id
主键
--stuno学生学号外键
--cno
课程编号外键
--cscore 课程成绩
create table score(
id int(5) auto_increment primary key,
stuno varchar(10),
cno varchar(10),
cscore int(3)
)
drop table score;
alter table score add constraint fk_score_stuno foreign key(stuno) references student(stuno);
alter table score add constraint fk_score_cno foreign key(cno) references course(cno);
|
