爱华网数据库原理与应用实验报告参考答案
实验1 数据库的建立修改与删除
实验2 表结构的建立修改删除及完整性约束条件定义 实验3 数据查询与更新
完成以下实验报告：
《数据库原理与应用》实验报告
一、实验题目
实验3 数据查询与更新 二、实验环境
操作系统：Windows XP。
数据库管理系统：MS SQL Server 2000或MS SQL Server 2008。 三、实验目的
1．掌握SELECT语句的语法、语义及语用。 2．掌握基于单表的查询方法。 3．掌握基于多表的查询方法。 4．掌握相关与不相关的嵌套查询。 5．掌握集合查询。
6．掌握插入(INSERT)、修改(UPDATE)和删除(DELETE)语句的使用。 四、实验内容 (一) 规定内容 1. 单表查询 USE ST
--[3_1] 查询全体学生的详细记录。 select * from student
--[3_2] 查询选修了课程的学生学号。 SELECT DISTINCT sno FROM sc
--[3_3] 将“学生”表中的sno、sname这2列合并为1列snosname输出(不改变表中存储的内容)，其余列不变。
select sno+sname snosname,ssex,sage,sdept from student
--[3_4] 查询年龄不在20~23岁之间的学生姓名、系别和年龄。 方法1：
SELECT sname,sdept,sage FROM student
WHERE sage NOT BETWEEN 20 AND 23; 方法2：
SELECT sname,sdept,sage FROM student
WHERE sage<20 or sage>23;
--[3_5] 查询计算机科学系(cs)、数学系(ma)和信息系(is)学生的姓名和性别。 SELECT sname,ssex FROM student
WHERE sdept IN ( 'cs','ma','is' );
--[3_6] 查询所有姓“刘”学生的姓名、学号和性别。 SELECT sname,sno,ssex FROM student
WHERE sname LIKE '刘%';
--[3_7] 查询名字中第2个字为"阳"字的学生的姓名和学号。 SELECT sname,sno FROM student
WHERE sname LIKE '_阳%';
--[3_8] 某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。 --查询缺少成绩的学生的学号和相应的课程号。 SELECT sno,cno FROM sc WHERE grade IS NULL;
--[3_9] 查询计算机系年龄在20岁以下的学生姓名。 SELECT sname FROM student
WHERE sdept='cs' AND sage<20;
--[3_10] 查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。 SELECT sno,grade FROM sc
WHERE cno='3'
ORDER BY grade DESC;
--[3_11] 查询全体学生情况，查询结果按所在系升序排列，同一系中的学生按性别降序排列。
SELECT * FROM student
ORDER BY sdept,sage DESC; --使用集函数
--[3_12] 查询学生总人数。
SELECT COUNT(*) 学生总人数 FROM student; --[3_13] 查询选修了课程的学生人数。
SELECT COUNT(DISTINCT sno) 选课人数 --注:用DISTINCT以避免重复计算学生人数 FROM sc;
--[3_14] 计算1号课程的最高、最低及平均成绩。
SELECT MAX(grade) '1号课程最高分', MAX(grade) '1号课程最低分',
AVG(grade) '1号课程平均成绩'
FROM sc
WHERE cno='1'
--[3_15] 查询学生200215121选修课程的平均成绩。 SELECT AVG(grade) 学生200215121平均成绩 FROM sc
WHERE sno='200215121'
--[3_16] 查询学生200215122选修课程的总学分数。 SELECT SUM(ccredit) 学生200215122学分 FROM sc,course
WHERE sc.cno=course.cno AND sno='200215122'; --使用GROUP BY子句分组
--[3_17] 求各个课程号及相应的选课人数。 SELECT cno 课程号,COUNT(sno) 选课人数 FROM sc
GROUP BY cno;
--使用HAVING短语筛选最终分组结果
--[3_18] 查询选修了3门(含3)以上课程的学生学号。 SELECT sno FROM sc
GROUP BY sno
HAVING COUNT(*)>=3 --也可为:COUNT(cno)>=3
--[3_19] 查询有2门以上课程是80分以上的学生的学号及(80分以上的)课程数SELECT sno 学号,COUNT(*) '80分以上的课程数' FROM sc
WHERE grade>=80
GROUP BY sno HAVING COUNT(*)>=2 2. 连接查询 --自然连接
--[3_20] 查询每个学生的学号、姓名、课号及成绩。 SELECT student.sno,sname,cno,grade FROM student,sc
WHERE student.sno = sc.sno --左外连接
--[3_21] 查询每个学生的学号、姓名、课号及成绩(包括没有选修课程的学生)。方法1：
SELECT student.sno,sname,cno,grade FROM student,sc
where student.sno *= sc.sno 方法2：
SELECT student.sno,sname,cno,grade
FROM student LEFT JOIN sc ON student.sno = sc.sno 运行结果：
sno sname cno grade --------- -------- ---- -------
200215121 李勇 1 97.0 200215121 李勇 2 40.0 200215121 李勇 3 93.0 200215122 刘晨 2 95.0 200215122 刘晨 3 55.0 200215123 王敏 NULL NULL 200215124 张立 NULL NULL
--[3_22] 查询所有学生选修课程的成绩。包括没有选课的学生。列出学号、姓名、课号、课名、成绩。
SELECT student.sno,sname,sc.cno,cname,grade FROM sc JOIN course ON sc.cno=course.cno RIGHT JOIN student ON student.sno = sc.sno 运行结果：
sno sname cno cname grade --------- -------- ---- ---------------- ------- 200215121 李勇 1 数据库 97.0 200215121 李勇 2 数学 40.0 200215121 李勇 3 信息系统 93.0 200215122 刘晨 2 数学 95.0 200215122 刘晨 3 信息系统 55.0 200215123 王敏 NULL NULL NULL 200215124 张立 NULL NULL NULL
--自身连接: 一个表与其自己进行连接，称为表的自身连接 --[3_23] 查询每一门课的间接先修课（即先修课的先修课） SELECT FIRST.cno 课号,SECOND.cpno 间接先修课 FROM course FIRST,course SECOND WHERE FIRST.cpno = SECOND.cno --复合条件连接
--[3_24] 查询选修2号课程且成绩在90分以上的所有学生的学号、姓名。SELECT student.sno, sname FROM student,sc
WHERE student.sno = sc.sno /* 连接谓词*/ AND sc.cno='2' AND sc.grade > 90 /* 其他限定条件*/ --多表连接
--[3_25] 查询每个学生的学号、姓名、课名及成绩。 SELECT student.sno,sname,cname,grade FROM student,sc,course
WHERE student.sno = sc.sno and sc.cno = course.cno 运行结果：
sno sname cname grade --------- -------- ---------------- ----- 200215121 李勇 数据库 97.0 200215121 李勇 数学 40.0 200215121 李勇 信息系统 93.0 200215122 刘晨 数学 95.0
百度搜索“爱华网”,专业资料、生活学习,尽在爱华网!
