数据库相关习题（一）

痛定思痛。 2021-09-16 03:48 340阅读 0赞

1. DDL语句以及插入数据练习  
1. 创建一张学生（Student）表，属性如下：  
\`\`\`  
学生编号 SID 整数 主键 自增从1001开始  
学生姓名 SNAME 字符串 长度为20 不为空  
学生年龄 BIRTHDAY 日期  
学生性别 SEX 字符串 长度为1  
\`\`\`  
Create database Data;  
use Data;  
//创建学生  
create table Student(  
sid int primary key auto\_increment,  
sname varchar(20) not null,  
birthday date,  
sex char(1)  
)auto\_increment=1001;  
2. 向学生表插入数据  
\`\`\`  
\+----+-------+------------+------+  
|1001 | 张三  | 1990-10-10 | 男   |  
|1002 | 李四  | 1981-10-10 | 男   |  
|1003 | 王五  | 1981-11-10 | 女   |  
|1004 | 赵六  | 1988-10-10 | 男   |  
|1005 | 孙七  | 1989-01-10 | 女   |  
|1006 | 周八  | 1990-10-10 | 男   |  
|1007 | 张三  | 1990-06-10 | 女   |  
\+----+-------+------------+------+  
\`\`\`  
//向学生表插入数据  
insert into Student(sname,birthday,sex) values('张三','1990-10-10','男');  
insert into Student(sname,birthday,sex) values('李四','1981-10-10','男');  
insert into Student(sname,birthday,sex) values('王五','1981-11-10','女');  
insert into Student(sname,birthday,sex) values('赵六','1988-10-10','男');  
insert into Student(sname,birthday,sex) values('孙七','1989-01-10','女');  
insert into Student(sname,birthday,sex) values('周八','1990-10-10','男');  
insert into Student(sname,birthday,sex) values('张三','1990-06-10','女');  
select \* from Student;

3. 创建教师表（Teacher）  
\`\`\`  
教师编号 TID 整数 主键 自增  
教师姓名 TNAME 字符串 长度为20 不为空  
\`\`\`  
//创建教师表  
create table Teacher(  
tid int primary key auto\_increment,  
tname varchar(20) not null  
);

4. 向教师表插入数据  
\`\`\`  
\+-----+--------+  
|   1 | 叶平   |  
|   2 | 王老师 |  
|   3 | 张老师 |  
|   4 | 李老师 |  
|   5 | 孙老师 |  
\+-----+--------+  
\`\`\`  
//向教师表插入数据  
insert into Teacher(tname) values('叶平');  
insert into Teacher(tname) values('王老师');  
insert into Teacher(tname) values('张老师');  
insert into Teacher(tname) values('李老师');  
insert into Teacher(tname) values('孙老师');  
select \* from Teacher;

5. 创建一张课程表（Course）  
\`\`\`  
课程编号 CID 整数 主键 自增  
课程名称 CNAME 字符串 长度为20 不为空  
教师编号 TID 必须与教师表中的TID相符，不能为空  
\`\`\`  
//创建课程表  
create table Course(  
cid int primary key auto\_increment,  
cname varchar(20) not null,  
tid int not null,  
foreign key(tid) references Teacher(tid)  
);

6. 插入课程数据，注意课程与教师编号的对应关系  
\`\`\`  
\+----------+----------+----------+----------+  
| 课程编号 | 课程名称 | 老师编号 | 老师名称 |  
\+----------+----------+----------+----------+  
|        1 | 企业管理 |        1 | 叶平     |  
|        2 | 马克思   |        2 | 王老师   |  
|        3 | UML      |        3 | 张老师   |  
|        4 | 数据库   |        4 | 李老师   |  
|        5 | 英语     |        5 | 孙老师   |  
|        6 | 语文     |        1 | 叶平     |  
|        7 | 数学     |        2 | 王老师   |  
\+----------+----------+----------+----------+  
\`\`\`  
//插入课程数据  
insert into Course(cname,tid) values('企业管理',1);  
insert into Course(cname,tid) values('马克思',2);  
insert into Course(cname,tid) values('UML',3);  
insert into Course(cname,tid) values('数据库',4);  
insert into Course(cname,tid) values('英语',5);  
insert into Course(cname,tid) values('语文',1);  
insert into Course(cname,tid) values('数学',2);  
select \* from Course;  
7. 创建成绩表（SC）  
\`\`\`  
学生编号 SID  必须与学生表中的学生编号相符，不为空  
课程编号 CID  必须与课程表中的课程编号相符，不为空  
成绩 SCORE 整数 不为空  
课程编号与学生编号应该联合唯一  
\`\`\`  
//创建成绩表  
create table SC(  
sid int not null,  
cid int not null,  
score int not null,  
foreign key(sid) references Student(sid),  
foreign key(cid) references Course(cid),  
primary key(sid,cid)  
);

8. 插入成绩数据  
\`\`\`  
\+----------+----------+----------+----------+------+  
| 学生编号 | 学生姓名 | 课程编号 | 课程名称 | 成绩 |  
\+----------+----------+----------+----------+------+  
|     1001 | 张三     |        1 | 企业管理 |   50 |  
|     1001 | 张三     |        2 | 马克思   |   70 |  
|     1001 | 张三     |        3 | UML      |   80 |  
|     1001 | 张三     |        4 | 数据库   |   90 |  
|     1001 | 张三     |        5 | 英语     |   80 |  
|     1001 | 张三     |        6 | 语文     |   80 |  
|     1001 | 张三     |        7 | 数学     |  100 |  
|     1002 | 李四     |        1 | 企业管理 |   90 |  
|     1002 | 李四     |        4 | 数据库   |   55 |  
|     1002 | 李四     |        6 | 语文     |   90 |  
|     1002 | 李四     |        7 | 数学     |   80 |  
|     1003 | 王五     |        1 | 企业管理 |   59 |  
|     1003 | 王五     |        2 | 马克思   |   70 |  
|     1004 | 赵六     |        1 | 企业管理 |   80 |  
|     1004 | 赵六     |        2 | 马克思   |   70 |  
|     1005 | 孙七     |        1 | 企业管理 |   50 |  
|     1005 | 孙七     |        2 | 马克思   |   50 |  
|     1005 | 孙七     |        3 | UML      |  100 |  
|     1005 | 孙七     |        4 | 数据库   |   30 |  
|     1006 | 周八     |        1 | 企业管理 |   60 |  
|     1007 | 张三     |        1 | 企业管理 |  100 |  
|     1007 | 张三     |        4 | 数据库   |   58 |  
|     1007 | 张三     |        6 | 语文     |   90 |  
|     1007 | 张三     |        7 | 数学     |   80 |  
\+----------+----------+----------+----------+------+  
\`\`\`  
//往CS表中添加数据  
insert into SC(sid,cid,score) values(1001,1,50);  
insert into SC(sid,cid,score) values(1001,2,70);  
insert into SC(sid,cid,score) values(1001,3,80);  
insert into SC(sid,cid,score) values(1001,4,90);  
insert into SC(sid,cid,score) values(1001,5,80);  
insert into SC(sid,cid,score) values(1001,6,80);

insert into SC(sid,cid,score) values(1001,7,100);  
insert into SC(sid,cid,score) values(1002,1,90);  
insert into SC(sid,cid,score) values(1002,4,55);  
insert into SC(sid,cid,score) values(1002,6,90);  
insert into SC(sid,cid,score) values(1002,7,80);  
insert into SC(sid,cid,score) values(1003,1,59);  
insert into SC(sid,cid,score) values(1003,2,70);  
insert into SC(sid,cid,score) values(1004,1,80);  
insert into SC(sid,cid,score) values(1004,2,70);  
insert into SC(sid,cid,score) values(1005,1,50);  
insert into SC(sid,cid,score) values(1005,2,50);  
insert into SC(sid,cid,score) values(1005,3,100);  
insert into SC(sid,cid,score) values(1005,4,30);  
insert into SC(sid,cid,score) values(1006,1,60);  
insert into SC(sid,cid,score) values(1007,1,100);  
insert into SC(sid,cid,score) values(1007,4,58);  
insert into SC(sid,cid,score) values(1007,6,90);  
insert into SC(sid,cid,score) values(1007,7,80);  
select \* from SC;

\#\# 2. 单表查询练习  
1. 查询姓“李”的老师的个数  
select count(tname) from teacher where tname like '李%';  
2. 查询男女生人数个数  
select sex,count(sex) from student group by sex;  
3. 查询同名同姓学生名单，并统计同名人数  
select sname,count(sname) from student group by sname having count(sname)>1;  
4. 1981年出生的学生名单  
select sname from student where extract(year from birthday)=1981;

select \* from student where birthday like '1981%';  
5. 查询平均成绩大于60分的同学的学号和平均成绩  
select sid,avg(score) from cs  group by sid having avg(score)>60;  
6. 求选了课程的学生人数  
select count(distinct sid) from sc where cid is not null;  
7. 查询至少选修两门课程的学生学号  
select sid from sc group by sid having count(cid)>=2;  
8. 查询各科成绩最高和最低的分。以如下形式显示：课程ID，最高分，最低分;  
select cid 课程ID,max(score) 最高分,min(score)  最低分 from sc group by cid;  
9. 统计每门课程的学生选修人数。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列  
select cid,count(sid) from sc group by cid order by count(sid) desc ,cid asc;