（笔记）数据库原理及应用——综合练习

文章目录综合练习1. 创建数据库和数据库表2. 简单的数据查询3. 复杂的数据查询 综合练习

所有答案都验证过3遍，符合题意且结果正确，如有疑问可留言

1. 创建数据库和数据库表 这么简单自己按照表建 2. 简单的数据查询 查询所有同学的基本信息，包括：学号s_no、班级号class_no、姓名s_name、性别s_sex、出生日期s_birthday。 select s_no,class_no,s_name,s_sex,s_birthday from Student 或 select * from Student 查询所有同学，要求显示其学号s_no、姓名s_name。 select s_no,s_name from Student 查询所有男同学，要求显示其学号s_no、姓名s_name、出生日期s_birthday。 select s_no,s_name,s_birthday from Student where s_sex='男' 查询所有出生日期在“1980-01-01”前的女同学，要求显示其学号s_no、姓名s_name、性别s_sex、出生日期s_birthday。 select s_no,s_name,s_sex,s_birthday from Student where s_sex='女' and s_birthday<'1980-01-01' 查询所有姓“李”的男同学，要求显示其学号s_no、姓名s_name、出生日期s_birthday。 select s_no,s_name,s_birthday from Student Where s_sex='男' and s_name like '李%' 查询所有姓名中含有“一”字的同学，要求显示其学号s_no、姓名s_name。 select s_no,s_name from Student where s_name like '%一%' 查询所有职称不是“讲师”的教师，要求显示其教师号t_no、姓名t_name、职称t_title。 select t_no,t_name,t_title from Teacher where t_title '讲师' 查询虽选修了课程，但未参加考试的所有同学，要求显示出这些同学的学号s_no。 select s_no from Choice where score IS NULL 查询所有考试不及格的同学，要求显示出这些同学的学号s_no、成绩score，并按成绩降序排列。 select s_no,score from Choice where score<60 or score IS NULL order by score DESC 查询出课程号为01001、02001、02003的所有课程，要求显示出课程号course_no、课程名称course_name。（要求用in运算符） select course_no,course_name from Course where course_no in('01001','02001','02003') 查询所有在1970年出生的教师，要求显示其教师号t_no、姓名t_name、出生日期t_birthday。 -- 这里需要注意是使用双%，具体原因我也不清楚，总之用双%结果就没错 select t_no,t_name,t_birthday from Teacher Where t_birthday like '%1970%' 查询出各个课程号course_no及相应的选课人数。 select course_no,COUNT(course_no) AS 选课人数 from Choice GROUP BY course_no 查询出教授两门以上课程的教师号t_no。 -- 注意，这里原表中并没有教授2门以上的教师，可自己加一位教授3门课的教师上去 select t_no from Teaching GROUP BY t_no HAVING COUNT(t_no)>2 查询出选修了01001课程的学生平均分、最低分数及最高分数。 select AVG(score) AS 平均分,MIN(score) AS 最低分数,MAX(score) AS 最高分数 from Choice where course_no='01001' 查询1960年以后出生的，职称为讲师的教师的姓名t_name、出生日期t_birthday，并按出生日期升序排列。 select t_name,t_birthday from Teacher where t_title='讲师' and t_birthday>'1960-12-31' order by ASC 3. 复杂的数据查询 查询所有同学的选课情况及成绩情况，要求显示学生的学号s_no、姓名s_name、课程号course_no和课程的成绩score。 select s.s_no,s_name,course_no,score from Student s left JOIN Choice ch ON s.s_no=ch.s_no order by s_no 查询所有同学的选课及成绩情况，要求显示学生的姓名s_name、课程名称course_name、课程的成绩score，并将查询结果存放到一个新的数据表new_table中。 select s_name,course_name,score into new_table from Student s LEFT JOIN Choice ch ON s.s_no=ch.s_no LEFT JOIN Course c ON ch.course_no=c.course_no -- 结果存在new_table 表中，查询结果 select * from new_table 查询“计算机99-1”班的同学的选课及成绩情况，要求显示学生的学号s_no、姓名s_name、课程号course_no、课程名称course_name、课程的成绩score。 select s.s_no,s_name,ch.course_no,course_name,score from Student s LEFT JOIN Class c ON s.class_no=c.class_no LEFT JOIN Choice ch ON s.s_no=ch.s_no LEFT JOIN Course co ON ch.course_no=co.course_no where class_name='计算机99-1' 查询所有同学的学分情况（假设课程成绩≥60分时可获得该门课程的学分），要求显示学生的学号s_no、姓名s_name、总学分（将该列定名为：total_score）（用JOIN） select s.s_no,s_name,sum(co.course_score) as total_score from Student as s left join Choice as c on c.s_no=s.s_no left join Course as co on co.course_no=c.course_no and c.score>='60' group by s.s_no,s.s_name 查询所有同学的平均成绩及选课门数，要求显示学生的学号s_no、姓名s_name、平均成绩（将该列定名为average_score）、选课的门数（将该列定名为：choice_num）。 select s.s_no,s_name,AVG(score) AS average_score,COUNT(score) AS choice_num from Student s LEFT JOIN Choice ch ON s.s_no=ch.s_no GROUP BY s.s_no,s_name 查询所有选修了课程但未参加考试的所有同学及相应的课程，要求显示学生的学号s_no、姓名s_name、课程号course_no、课程名称course_name。 select s.s_no,s_name,ch.course_no,course_name from Student s inner JOIN Choice ch ON s.s_no=ch.s_no inner JOIN Course c ON ch.course_no=c.course_no where score IS NULL 查询所有选修了课程但考试不及格（假设<60分为不及格）的所有同学及相应的课程，要求显示学生的学号s_no、姓名s_name、姓名s_name、课程号course_no、课程名称course_name、学分course_score。 select s.s_no,s_name,co.course_no,course_name,course_score from Student s inner JOIN Choice ch ON s.s_no=ch.s_no inner JOIN Course co ON ch.course_no=co.course_no where score < 60 查询选修了课程名为“程序设计语言”的所有同学及成绩情况，要求显示学生的姓名s_name、课程的成绩score。（使用ANY） select s_name,score from Student s,Choice ch where s.s_no=ch.s_no AND ch.course_no=ANY( select c.course_no from Course c where course_name='程序设计语言') 查询“计算机系”的所有同学及成绩情况，要求显示学生的学号s_no、姓名s_name、班级名称class_name、课程号course_no、课程名称course_name、课程的成绩score。 select s.s_no,s_name,class_name,co.course_no,course_name,score from Student s left JOIN Choice ch ON s.s_no=ch.s_no left JOIN Class c ON s.class_no=c.class_no left JOIN Course co ON ch.course_no=co.course_no where class_dept='计算机系' 查询所有教师的任课情况，要求显示教师姓名t_name、担任课程的名称course_name。 select t_name,course_name from Teacher t left JOIN Teaching ti ON t.t_no=ti.t_no left JOIN Course co ON ti.course_no=co.course_no 查询所有教师的任课门数，要求显示教师姓名t_name、担任课程的门数（将该列定名为course_number）。 select t_name,count(course_no) AS course_number from Teacher t left JOIN Teaching ti ON t.t_no=ti.t_no group by t.t_no,t_name 查询和“李建国”是同一班级的同学的姓名。（使用子查询） select s_name from Student where class_no=( select class_no from Student where s_name='李建国' ) AND s_name'李建国' 查询没有选修“计算机基础”课程的学生姓名。（用NOT EXISTS） select s_name from Student s where NOT EXISTS ( select * from Course co inner JOIN Choice ch ON co.course_no=ch.course_no where course_name='计算机基础' AND s.s_no=ch.s_no ) 查询主讲“数据库原理与应用”和主讲“数据结构”的教师姓名。（用UNION） select t_name from Teacher t inner JOIN Teaching ti ON t.t_no=ti.t_no inner JOIN Course co ON ti.course_no=co.course_no where course_name='数据库原理与应用' UNION select t_name from Teacher t inner JOIN Teaching ti ON t.t_no=ti.t_no inner JOIN Course co ON ti.course_no=co.course_no where course_name='数据结构' 查询讲授了所有课程的教师的姓名。 本题需要自己插入一位教授所有课程的老师 insert into Teacher values('000011','测试员','男','1970-2-3','讲师') 还需要在任课情况表Teaching添加任课信息 insert into Teaching values ('01001','000011'), ('01002','000011'), ('01003','000011'), ('02001','000011'), ('02002','000011'), ('02003','000011') 做法一：使用双重NOT EXISTS select t_name from Teacher t where NOT EXISTS ( select * from Course co where NOT EXISTS ( select * from Teaching ti where t.t_no=ti.t_no AND co.course_no=ti.course_no ) ) 做法二：使用group by + count。注意，count函数只能用在having中而不能在where中 select t_name from Teacher t,Teaching ti where t.t_no=ti.t_no group by t_name having COUNT(ti.course_no)=( select COUNT(co.course_no) from Course co ) 用T—SQL语句定义存储过程 创建一个能向学生表Student中插入一条记录的存储过程Insert_student，该存储过程需要五个参数，分别用来传递学号、班级、姓名、性别、出生日期五个值。 CREATE PROCEDURE Insert_student ( @s_no CHAR(10), @s_class CHAR(6), @s_name VARCHAR(10), @s_sex CHAR(2), @s_birthday datetime ) AS INSERT INTO Student(s_no,class_no,s_name,s_sex,s_birthday) VALUES (@s_no,@s_class,@s_name,@s_sex,@s_birthday) 写出执行存储过程Insert_student的SQL语句，向数据表Student中插入一个新同学，并提供相应的实参值（实参值由用户自己给出）。 EXEC Insert_student @s_no='000001',@s_class='xx0002',@s_name='测试1', @s_sex='男',@s_birthday='1980-10-03' 创建一个向课程表Course中插入一门新课程的存储过程Insert_course，该存储过程需要三个参数，分别用来传递课程号、课程名、学分，但允许参数“学分”的默认值为2，即当执行存储过程Insert_course时，未给第三个参数“学分“提供实参值时，存储过程将按默认值2进行计算。 CREATE PROCEDURE Insert_course ( @course_no char(5), @course_name char(20), @course_score numeric(6,2)=2 ) AS INSERT INTO Course(course_no,course_name,course_score) VALUES(@course_no,@course_name,@course_score) 执行存储过程Insert_course，向课程数据表Course中插入一门新课程。分两种情况写出相应的SQL命令。第一种：提供三个实参值（三个实参由用户提供）。第二中：只提供两个实参，不提供学分对应的实参。比较两者差异。 EXEC Insert_course @course_no='03001',@course_name='测试课程1',@course_score=6 EXEC Insert_course @course_no='03002',@course_name='测试课程2' select * from Course -- 查询结果为，测试课程1的学分为6，而测试课程2的学分为2 创建一个名称为query_student的存储过程，该存储过程的功能是从数据表Student中根据学号查询某一同学的姓名s_name、班级class_no、性别s_sex、出生日期s_birthday。 GO CREATE PROCEDURE query_student ( @s_no CHAR(10) ) AS SELECT s_name,class_no,s_sex,s_birthday from Student where s_no=@s_no Transact-SQL语句自定义触发器 创建一个向学生表Student中插入一新同学时能自动列出全部同学信息的触发器Display_trigger。 GO CREATE TRIGGER Display_trigger ON Student AFTER INSERT AS SELECT * FROM Student GO 执行存储过程Insert_student，向学生表中插入一新同学，看触发器Display_trigger是否被执行。 EXEC Insert_student @s_no='000002',@s_name='触发器',@s_class='xx0002', @s_sex='男',@s_birthday='1979-07-30'
作者：灰尘小白

数据 数据库原理 数据库