数据库实验——第2部分

这是第二个实验，实验前需要创建的表都在实验指导书上，按要求创建就好了。接下来是一部分实验内容：

--1.列出student表中所有记录的sname、sex和class列。
select student_name, sex, class from student;

--2.显示教师所有的单位即不重复的depart列。
select distinct department from teacher_info;

--3.显示学生表的所有记录。
select * from student;

--4.显示score表中成绩在60到80之间的所有记录。
select * from score where degree >= 60 and degree <= 80;

--5.显示score表中成绩为85，86或88的记录。
select * from score where degree = 85 or degree = 86 or degree = 88;

--6.显示student表中“95031”班或性别为“女”的同学记录。
select * from student where class='95031' or sex = '女';

--7.以class降序显示student表的所有记录。
select * from student order by class desc;

--8.以cno升序、degree降序显示score表的所有记录。
select * from score order by course_id asc, degree desc;

--9.显示“98031”班的学生人数。
select count(*) from student where class='98031';

--10.显示score表中的最高分的学生学号和课程号。
select student_id, course_id from score where degree = (select max(degree) from score);

--11.显示“3-105”号课程的平均分。
select avg(degree) 平均分 from score where course_id='3-105';

--12.显示score表中至少有5名学生选修的并以3开头的课程号的平均分数。
select avg(degree) 平均分 from score where course_id like '3%' group by course_id having count(*) >= 5;

 

 

接上：

--13.显示最低分大于70，最高分小于90的sno列。
select student_id 学号 from score group by student_id having max(degree)<90 and min(degree)>70;

--14.显示所有学生的 sname、 cno和degree列。
select student.student_name 学生姓名, score.course_id 课程编号, score.degree 课程分数 
from student join score on (student.id = score.student_id);

--15.显示所有学生的 sname、 cname和degree列。
select student.student_name 学生姓名, course.course_name 课程名称, score.degree 课程分数 
from student join score on (student.id = score.student_id) join course 
on (score.course_id = course.course_id);

--16.列出“95033”班所选课程的平均分。
select avg(degree) 平均分 from score join student on (score.student_id = student.id)
where student.class = '95033';

--17.显示选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
select student_name 学生姓名 from student join score on (student.id = score.student_id) 
where score.course_id='3-105' and score.degree >
(select degree from score where student_id = 109 and course_id = '3-105');

--18.显示score中选修多门课程的同学中分数为非最高分成绩的记录。
select * from
(select * from score where degree not in
(select max(degree) from score group by score.student_id)) s
where student_id not in
(select student_id from score group by student_id having count(student_id) <= 1)

--19.显示成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
select * from score where degree > 
(select degree from score where student_id = 103 and course_id = '3-105');

--20.显示出和学号为“108”的同学同年出生的所有学生的sno、sname和 birthday列。
select id 学号, student_name 姓名, birthday 生日 from student where 
year(birthday) = (select year(birthday) from student where id = 108);

--21.显示“张旭”老师任课的学生成绩。
select * from score where score.course_id in 
(select course.course_id from course join teacher_info
on (course.course_number = teacher_info.teacher_id) where teacher_info.teacher_name = '张旭');

 

继续接上：

--22.显示选修某课程的同学人数多于5人的老师姓名。
select teacher_name from teacher_info where teacher_id in 
(select course_number from course where course_id in 
(select course_id from score group by course_id having count(student_id) > 5));

--23.显示“95033”班和“95031”班全体学生的记录。
select * from student where class = '95033' or class = '95031';

--24.显示存在有85分以上成绩的课程cno。
select course_id from score where score.degree > 85;

--25.显示“计算机系”老师所教课程的成绩表。
select * from score where course_id in 
(select course_id from course where course_number in
(select teacher_id from teacher_info where department = '计算机系'));

--26.显示“计算机系”和“电子工程系”不同职称的老师的tname和prof。
select teacher_name, prof from teacher_info
where prof in 
(select prof from teacher_info where department = '计算机系') 
and prof not in (select prof from teacher_info where department = '电子工程系');

 

接上：

--27.显示选修编号为“3-105”课程且成绩至少高于“3-245”课程的同学的cno、sno和degree，并按degree从高到低次序排列。
select course_id 课程编号, student_id 学号, degree 成绩 from score where course_id = '3-105' 
and degree > (select min(degree) from score where course_id = '3-245') order by degree desc;

--28.显示选修编号为“3-105”课程且成绩高于“3-245”课程的同学的cno、sno和degree。
select course_id 课程编号, student_id 学号, degree 成绩 from score where course_id = '3-105' 
and degree > (select max(degree) from score where course_id = '3-245');


--29.列出所有任课老师的tname和depart。
select teacher_name 姓名, department 所在系 from teacher_info;

--30.列出所有未讲课老师的tname和depart。
select teacher_name 姓名, department 所在系 from teacher_info where
teacher_id not in (select course_number from course);

--31.列出所有老师和同学的 姓名、性别和生日。
select teacher_name 姓名, sex 性别, birthday 生日 from teacher_info union
select student_name 姓名, sex 性别, birthday 生日 from student;

--32.检索所学课程包含学生“103”所学课程的学生学号。
select student_id from score where course_id in 
(select distinct course_id from score where student_id = '103');

--33.检索选修所有课程的学生姓名。
select student_name from student where id in
(select student_id from score group by student_id having count(*) > (select count(*) from course));