数据库概念第二版,实验二 参考答案

实验二 SQL Server 2000 查询分析器的使用 1 建立数据库文件 2 在数据库文件中建立表 修改表 主码 完整性约束的使用 3 查询操作 2 实验数据及具体要求 1 对于教学数据库的三个基本表 学生关系 Student SNO SNAME SSEX SAGE SDEPT 学习关系 SC SNO CNO GRADE 课程关系 Course CNO CNAME TEACHER studentstudent snosno snamesname ssexssex sagesage sdeptsdept 95001李勇 男20CS 95002刘晨 女19IS 95003王敏 女18MA 95004张立 男19IS scsc snosno cnocno gradegrade 95001 192 95001 285 95001 388 95002 290 95002 380 coursecourse cnocnocnamecnamecpnocpno creaditcreadit 1 数据库 54 2 数学 2 3 信息系统 14 4 操作系统 63 5 数据结构 74 6 数据处理 2 7 PASCAL 语言6 4 创建数据库文件 create database jxgl 创建学生关系 Student SNO SNAME SSEX SAGE SDEPT use jxgl CREATE TABLE Student Sno CHAR 5 PRIMARY KEY Sname CHAR 20 Ssex CHAR 2 check ssex 男 or ssex 女 Sage INT check sage between 15 and 35 Sdept CHAR 15 check sdept in CS IS MA PH 在 student 表中插入记录 insert into student values 95001 李勇 男 20 CS 创建学习关系 SC SNO CNO GRADE CREATE TABLE SC Sno CHAR 9 Cno CHAR 4 Grade SMALLINT check Grade between 0 and 100 PRIMARY KEY Sno Cno 主码由两个属性构成 必须作为表级完整性进行定义 FOREIGN KEY Sno REFERENCES Student Sno 表级完整性约束条件 Sno 是外码 被参照表是 Student FOREIGN KEY Cno REFERENCES Course Cno 表级完整性约束条件 Cno 是外码 被参照表是 Course 试用 SQL 的查询语句表达下列查询 1 使用查询分析器建立三个表并输入数据 2 查询选修了课程的学生人数 Select count distinct sno from sc 加 distinct 去掉重复值后计数 3 给 CS 系的学生开设 5 号课程 建立选课信息 成绩暂空 INSERT INTO sc SELECT student sno o NULL AS Expr1 FROM student CROSS JOIN course WHERE student sdept CS AND o 5 4 将 95001 学生选修 3 号课程的成绩改为该课的平均成绩 UPDATE sc SET grade SELECT AVG grade FROM sc WHERE cno 3 WHERE sno 95001 5 查询计算机系 CS 选修了两门及以上课程的学生的学号 姓名 Select sno sname From student Where sdept CS and sno in select sno from sc where student sno sc sno Group by sc sno having count 2 6 查询 student 表与 sc 表基于学号 sno 的等值连接 自然连接 select from student sc where student sno sc sno 或 SELECT FROM student INNER JOIN sc ON student sno sc sno 7 查询课程之先修课的先修课 自身连接 SELECT o second cpno FROM course first INNER JOIN course second ON first cpno o 8 查询学生及其选修课程 成绩等情况 不管是否选修均需列出学生信息 select student sno sname ssex sage sdept cno grade from student sc where student sno sc sno 或 SELECT student sno student sname student ssex student sage student sdept o sc grade FROM student LEFT OUTER JOIN sc ON student sno sc sno 9 查询性别为男 课程成绩及格的学生信息及课程号 成绩 select student cno grade from student sc where ssex 男 and student sno sc sno and grade 60 SELECT student o AS Expr1 sc grade AS Expr2 FROM student INNER JOIN sc ON student sno sc sno WHERE student ssex 男 AND sc grade 60 10 查询与 张立 在同一个系学习的学生信息 SELECT FROM student WHERE sdept IN SELECT sdept FROM student WHERE sname 张立 AND sname 张立 或 SELECT s1 FROM student s1 student s2 WHERE s2 sname 张立 AND s1 sname 张立 and s1 sdept s2 sdept 或 SELECT s1 FROM student s1 INNER JOIN student s2 ON s1 sdept s2 sdept WHERE s2 sname 张立 AND s1 sname 张立 11 查询选修了课程名为 数据库 的学生学号 姓名和所在系 SELECT sno sname sdept FROM student WHERE sno IN SELECT sno FROM sc WHERE cno IN SELECT cno FROM course WHERE cname 数据库 或 SELECT student sno student sname student sdept FROM student sc course WHERE ame 数据库 and student sno sc sno and o o 或 SELECT student sno student sname student sdept FROM student INNER JOIN sc ON student sno sc sno INNER JOIN course ON o o WHERE ame 数据库 12 查询哪些课程只有女生选修 SELECT DISTINCT cname FROM course WHERE 女 ALL SELECT ssex FROM sc student WHERE sc sno student sno AND o o 或 SELECT DISTINCT cname FROM course WHERE NOT EXISTS SELECT FROM sc student WHERE sc sno student sno AND o o AND student ssex 男 13 查询所有未选修 1 号课程的学生姓名 SELECT sname FROM student WHERE NOT EXISTS SELECT FROM SC WHERE Sno Student Sno AND Cno 1 14 查询平均成绩大于 85 分的学号 姓名 平均成绩 SELECT student sno student sname AVG sc grade AS Expr1 FROM student sc where student sno sc sno GROUP BY student sno student s