位置：首页-资讯-数据库

mysql综合性练习

2019-03-02 10:11

短信预约 信息系统项目管理师 报名、考试、查分时间动态提醒

题目描述

设定有一个数据库，里面有4张表：

学生表（student）
课程表（course）
成绩表（score）
教师信息表（teacher）

表结构如下：

表一_学生表（student）

属性名	数据类型	含义
sno	int	学号(主键)
sname	varchar	学生姓名
ssex	varchar	学生性别
sbirthday	date	学生出生年月
classid	varchar	学生所在班级

表二_课程表（course）

属性名	数据类型	含义
cno	varchar	课程号(主键)
cname	varchar	课程名称
tno	int	教师编号(外键)

表三_成绩表（score）

属性名	数据类型	含义
sno	int	学号(外键)
cno	varchar	课程号(外键)
sdegree	float	成绩

表四_教师表（teacher）

属性名	数据类型	含义
tno	int	编号（主键）
tname	varchar	教师姓名
tsex	varchar	教师性别
tbirthday	date	教师出生年月
professional	varchar	职称
department	varchar	教师所在部门

二、创建数据库和表

先创建数据库mysql_test，并且设置这个数据库的编码字符集为utf-8。

学生表：

create table student(
    sno  int(10),
    sname varchar(10),
    ssex varchar(10),
    sbirthday date,
    classid varchar(10)
);

alter table student add primary key(sno);

教师表：

create table teacher(
    tno int(10),
    tname varchar(12),
    tsex varchar(10),
    tbirthday date,
    professional varchar(20),
    department varchar(20)
);

alter table teacher add primary key(tno);

课程表：

create table course(
    cno varchar(10),
    cname varchar(20),
    tno int(10)
);

alter table course add primary key(cno);
alter table course add constraint fk_course_teacher foreign key(tno) references teacher(tno);

成绩表：

create table score(
    sno int(10),
    cno varchar(10),
    sdegree float(5)
);

alter table score add constraint fk_score_student foreign key(sno) references student(sno);
alter table score add constraint fk_score_course foreign key(cno) references course(cno);

三、添加数据

insert into student (sno,sname,ssex,sbirthday,classid) 
values(108,"曾华","男","1996-9-1","95033"),(105,"匡明","男","1995-10-2","95031"),
(107,"王丽","女","1996-1-23","95033"),(101,"李军","男","1996-2-20","95033"),
(109,"王芳","女","1995-2-10","95031"),(103,"陆君","男","1994-6-3","95031");


insert into teacher(tno,tname,tsex,tbirthday,professional,department) 
values(804,"李诚","男","1978-12-2","副教授","计算机系"),
(856,"张旭","男","1979-3-12","讲师","电子工程系"),
(825,"王萍","女","1982-5-5","助教","计算机系"),
(831,"刘冰","女","1984-8-14","助教","电子工程系");


insert into course(cno,cname,tno) 
values("3-105","计算机导论",825),("3-245","操作系统",804),
("6-166","数字电路",856),("9-888","高等数学",831);


insert into score(sno,cno,sdegree) values
(103,"3-245",86),
(105,"3-245",75),
(109,"3-245",68),
(103,"3-105",92),
(105,"3-105",88),
(109,"3-105",76),
(101,"3-105",64),
(107,"3-105",91),
(108,"3-105",78),
(101,"6-166",85),
(107,"6-166",79),
(108,"6-166",81);

学生表：

教师表：

课程表：

成绩表：

四、SQL题目

1、查询所有学生的姓名,性别及班级编号

2、查询教师所有的部门名称(即不重复的DEPART列)

3、查询student表的所有记录

4、查询score表中成绩在60到80之间的所有记录

5、查询score表中成绩为85,86或88的记录

6、查询student表中班级编号为"95031"或性别为"女"的同学记录

7、查询student表的所有记录,以班级编号降序排列

8、查询score表的所有记录,先按照cno升序排列,若相同则按照sdegree降序排列

9、查询班级编号为"95031"班级的学生人数

10、查询score表中的最高分的学生学号和课程号

11、查询编号为"3-105"的课程平均分

12、查询score表中至少有5名学生选修的并以3开头的课程的平均分数

13、查询每科成绩均在70到90之间的学生编号

注意：having 后面只能跟聚合函数，如avg、min、max、sum等聚合函数

14、查询所有学生的姓名,所选课程编号和成绩

15、查询所有学生的学号,所选课程名和成绩

16、查询所有学生的姓名,所选课程名和成绩

17、查询"95033"班同学所选每个课程的平均分

18、假设使用如下命令建立了一个GRADE表：

CREATE TABLE GRADE(
LOW INT(3),
UPP INT(3),
RANK VARCHAR(1)
);
INSERT INTO GRADE VALUES(90,100,"A");
INSERT INTO GRADE VALUES(80,89,"B");
INSERT INTO GRADE VALUES(70,79,"C");
INSERT INTO GRADE VALUES(60,69,"D");
INSERT INTO GRADE VALUES(0,59,"E");

– 现查询所有同学的学号,所选课程号和等级(RANK列)