数据库实验四作业及答案.docx

实验4 数据查询一、 实验目的1. 掌握使用Transact-SQL的SELECT语句进行基本查询的方法。2. 掌握使用SELECT语句进行条件查询的方法。3. 掌握嵌套查询的方法。4. 掌握多表查询的方法。5. 掌握SELECT语句的GROUP BY和ORDER BY子句的作业和使用方法。6. 掌握联合查询的操作方法。7. 掌握数据更新语句INSERT INTO、UPDATE、DELETE的使用方法。二、 实验准备1. 了解SELECT语句的基本语法格式和执行方法。2. 了解嵌套查询的表示方法。3. 了解UNION运算符的用法。4. 了解SELECT语句的GROUP BY和ORDER BY子句的作用。5. 了解IN、JOIN等子查询的格式。6. 了解INSERT INTO、UPDATE、DELETE的格式与作用。三、 实验内容及步骤0. 创建studentsdb数据库及其相应表，并录入数据。 启动查询分析器，运行下面链接的代码即可。 创建数据库代码1. 在studentsdb数据库中，使用下列SQL语句将输出什么？（1） SELECT COUNT(*) FROM grade（2） SELECT SUBSTRING(姓名,1,2) FROM student_info （3） SELECT UPPER(kelly) （4） SELECT Replicate(kelly,3) （5） SELECT SQRT(分数) FROM grade WHERE 分数=85 （6） SELECT 2,3,POWER(2,3) （7） SELECT YEAR(GETDATE(),MONTH(GETDATE(),DAY(GETDATE() 2. 在studentsdb数据库中使用SELECT语句进行基本查询。（1） 在student_info表中，查询每个学生的学号、姓名、出生日期信息。 SELECT * FROM student_info （2） 查询学号为0002的学生的姓名和家庭住址。 SELECT 姓名,家庭住址 FROM student_infoWHERE 学号=0002（3） 找出所有男同学的学号和姓名。 SELECT 学号,姓名 FROM student_infoWHERE 性别=男 3. 使用SELECT语句进行条件查询（1） 在grade表中查找分数在8090分为内的学生的学号和分数。SELECT 学号,分数 FROM gradeWHERE 分数 BETWEEN 80 AND 90 （2） 在grade表中查询课程编号为0003的学生的平均分。 SELECT AVG(分数) AS 平均分 FROM gradeWHERE 课程编号=0003 （3） 在grade表中查询学习各门课程的人数。SELECT 课程编号, COUNT(*) AS 人数FROM gradeGROUP BY 课程编号 （4） 将学生按出生日期由大到小排序。SELECT 学号,姓名,出生日期 FROM student_infoORDER BY 出生日期 ASC（5） 查询所有姓“张”的学生的学号和姓名。SELECT 学号,姓名 FROM student_info WHERE 姓名 LIKE 张% 4. 嵌套查询（1） 在student_info表中查找与“刘卫平”性别相同的所有学生的姓名、出生日期。 SELECT 姓名,出生日期FROM student_infoWHERE 性别= (SELECT 性别 FROM student_info WHERE 姓名=刘卫平) （2） 使用IN子查询查找所修课程编号为0002、0005的学生学号、姓名、性别。SELECT 学号,姓名,性别FROM student_infoWHERE student_info.学号 IN (SELECT 学号 FROM grade WHERE 课程编号 IN (0002, 0005) （3） 列出学号为0001的学生的分数比0002号的学生的最低分数高的课程编号和分数。 SELECT 课程编号,分数 FROM gradeWHERE 学号=0001 AND 分数ANY (SELECT 分数 FROM gradeWHERE 学号=0002)（4） 列出学号为0001的学生的分数比0002的学生的最高成绩还要高的课程编号和分数。 SELECT 课程编号,分数FROM gradeWHERE 学号=0001 AND 分数ALL (SELECT 分数 FROM gradeWHERE 学号=0002)5. 多表查询（1） 查询分数在8090范围内的学生的学号、姓名、分数。 SELECT student_info.学号,姓名,分数FROM student_info,gradeWHERE student_info.学号=grade.学号 AND 分数 BETWEEN 80 AND 90（2） 查询学习“C语言程序设计”课程的学生的学号、姓名、分数。SELECT student_info.学号,姓名,分数FROM student_info INNER JOIN grade ON student_info.学号=grade.学号INNER JOIN curriculum ON 课程名称=C语言程序设计（3） 查询所有男同学的选课情况，要求列出学号、姓名、课程名称、分数。select student_info.学号,姓名,课程编号,分数 from student_info left outer join grade on student_info.学号=grade.学号 where 性别=男（4） 查询每个学生的所选课程的最高成绩，要求列出学号、姓名、课程名称、分数。select grade.学号,student_info.姓名,grade.课程编号,grade.分数 from student_info left outer join grade on student_info.学号=grade.学号 where grade.分数=(select MAX(分数) from grade b where b.学号=grade.学号)（5） 查询所有学生的总成绩，要求列出学号、姓名、总成绩，没有选修课程的学生的总成绩为空。提示：使用左外连接。select grade.学号,姓名,sum(分数) from student_info left outer join grade on student_info.学号=grade.学号 group by grade.学号,姓名（6） 为grade表添加数据行：学号0004、课程编号为0006、成绩为76。查询所有课程的选修情况，要求列出课程编号、课程名称、选修人数，curriculum表中没有的课程列值为空。提示：使用右外连接。insert grade values(0004,0006,76)select grade.课程编号,课程名称,count(*) from grade right outer join curriculum on grade.课程编号=curriculum.课程编号 group by grade.课程编号,课程名称6. 使用UNION运算符将student_info表中姓“张”的学生的学号、姓名与curriculum表的课程编号、课程名称返回在一个表中，且列名为u_编号、u_名称，如图1-8所示。select 学号 u_编号,姓名 u_名称 from student_info where 姓名 like 张% union select 课程编号,课程名称 from curriculum图1-8 联合查询结果集7. 数据更新（1） 创建totalgrade表，具有数据列：学号、姓名、总成绩。 CREATE TABLE totalgrade( 学号 char(4) NOT NULL,姓名 varchar(8) NULL,总成绩 decimal(5,2) NULL )（2） 使用INSERT INTO语句通过student_info表更新totalgrade表的学号、姓名列数据。 INSERT INTO totalgrade SELECT 学号,姓名,总成绩=0 FROM student_info（3） 使用UPDATE语句通过grade表更新totalgrade表的中成绩列数据，使totalgrade表中每个学生的总分成绩为grade表中该学生各成绩之和。UPDATE totalgrade SET 总成绩= (SELECT SUM(分数) FROM grade WHERE totalgrade.学号=grade.学号)FROM grade（4） 删除totalgrade表中