Note/大学笔记/数据库系统原理/练习题/第二次上机.md

一、上机内容

1. 在studentsdb数据库中使用SELECT语句进行基本查询。

（1）在student_info表中，查询每个学生的学号、姓名、出生日期信息。

select 学号,姓名,出生日期 from student_info;

（2）查询student_info表学号为 0002的学生的姓名和家庭住址。

select 姓名,家庭住址 from student_info where 学号=0002;

（3）查询student_info表所有出生日期在95年以后的女同学的姓名和出生日期。

select 姓名,出生日期 from student_info where 性别='女' and 出生日期>'1995-12-31';

2. 使用select语句进行条件查询。

（1）在grade表中查询分数在70-80范围内的学生的学号、课程编号和成绩。

select 学号,课程编号,分数 from grade where 分数>=70 and 分数<=80;

（2）在grade表中查询课程编号为0002的学生的平均成绩。

select avg(分数) from grade where 课程编号=0002;

（3）在grade表中查询选修课程编号为0003的人数和该课程有成绩的人数。

select count(分数) as 人数 from grade where 课程编号=0003;

（4）查询student_info的姓名和出生日期，查询结果按出生日期从大到小排序。

select 姓名,出生日期 from student_info order by 出生日期 desc;

（5）查询所有姓名“张”的学生的学号和姓名。

select 学号,姓名 from student_info where 姓名 like '张%';

3. 对student_info表，查询学生的学号、姓名、性别、出生日期及家庭住址，查询结果先按照性别的由小到大排序，性别相同的再按学号由大到小排序。

select 学号,姓名,性别,出生日期,家庭住址 from student_info order by 性别,学号 desc;

4. 使用GROUP BY子句查询grade表中各个学生的平均成绩。

select 学号,avg(分数) 平均分 from grade group by 学号;

5. 使用UNION运算符针student_info表中姓“刘”的学生的学号、姓名与姓“张”的学生的学号、姓名返回在一个表中。

select 学号,姓名 from student_info where 姓名 like '刘%'
union
select 学号,姓名 from student_info where 姓名 like '张%';  

6. 嵌套查询

（1）在student_info表中查找与“刘东阳”性别相同的所有学生的姓名、出生日期。

select 姓名,出生日期 from student_info where 性别=(select 性别 from student_info where 姓名='刘东阳');

（2）使用IN子查询查找所修课程编号为0002、0005的学生学号、姓名、性别。

select 学号,姓名,性别 from student_info where 学号 in (select 学号 from grade where 课程编号 in('0002','0005'));

（3）使用ANY子查询查找学号为0001的学生的分数比0002号的学生的最低分数高的课程编号和分数。

select 课程编号,分数 from grade where 学号 = '0001' and 分数 > any (select 分数 from grade where 学号 = '0002');  

（4）使用ALL子查询查找学号为0001的学生的分数比学号为0002的学生的最高成绩还要高的课程编号和分数。

select 课程编号,分数 from grade where 学号 = '0001' and 分数 > all (select 分数 from grade where 学号 = '0002');  

7. 连接查询

（1）查询分数在80-90范围内的学生的学号、姓名、分数。

select st.学号,姓名,分数 from student_info st,grade gr where st.学号 = gr.学号 and 分数 between 80 and 90;

（2）查询学习“数据库原理及应用”课程的学生学号、姓名、分数。

select st.学号,姓名,分数 from student_info st inner join grade gr on st.学号 = gr.学号 inner join curriculum cu on gr.课程编号 = cu.课程编号 where 课程名称 = '数据库原理及应用'; 

select st.学号,姓名,分数 from student_info st,grade gr,curriculum cu where 课程名称 = '数据库原理及应用' and gr.课程编号 = cu.课程编号 and st.学号=gr.学号;

（3）查询每个学生所选课程的最高成绩，要求列出学号、姓名、最高成绩。

select st.学号,姓名,max(分数) 最高成绩 from student_info st,grade gr where st.学号 = gr.学号 group by st.学号;  

（4）使用左外连接查询每个学生的总成绩，要求列出学号、姓名、总成绩，没有选修课程的学生的总成绩为空。

select st.学号,姓名,sum(分数) 总成绩 from student_info st left outer join grade gr on st.学号 = gr.学号 group by st.学号;

（5）为grade表添加数据行：学号为0004、课程编号为0006、分数为76。使用右外连接查询所有课程的选修情况，要求列出课程编号、课程名称、选修人数,curriculum表中没有的课程列值为空。

insert into grade values('0004','0006',76);

select gr.课程编号,课程名称,count(*) 选修人数 from curriculum cu right outer join grade gr on gr.课程编号 = cu.课程编号 group by gr.课程编号;  

二、实验思考

1.查询所有没有选修课程的学生的学号、姓名。

select st.学号,姓名,课程编号 from student_info st left outer join grade gr on gr.学号=st.学号 where 课程编号 is null;

2.查询选修课程的人数。

select count(distinct 学号) 选修课程人数 from grade;

3.查询选课人数大于等于3人的课程编号、课程名称、人数。

select gr.课程编号,cu.课程名称,count(gr.学号) 人数 from grade gr,curriculum cu where gr.课程编号=cu.课程编号 group by gr.课程编号 having count(gr.学号)>=3;

4.在查询的FROM子句中实现表与表之间的连接有哪几种方式？对应的关键字分别是什么？

内连接 inner join
左连接 left join
右连接 right join