数据库实验四作业及答案.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_info
WHERE 学号=0002

（3）       找出所有男同学的学号和姓名。
SELECT 学号,姓名 FROM student_info
WHERE 性别='男'

3.     使用SELECT语句进行条件查询
（1）       在grade表中查找分数在80～90分为内的学生的学号和分数。
SELECT 学号,分数 FROM grade
WHERE 分数 BETWEEN 80 AND 90

（2）       在grade表中查询课程编号为0003的学生的平均分。
SELECT AVG(分数) AS 平均分 FROM grade
WHERE 课程编号=0003

（3）       在grade表中查询学习各门课程的人数。
SELECT 课程编号, COUNT(*) AS 人数
FROM grade
GROUP BY 课程编号

（4）       将学生按出生日期由大到小排序。
SELECT 学号,姓名,出生日期
FROM student_info
ORDER BY 出生日期 ASC

（5）       查询所有姓“张”的学生的学号和姓名。
 SELECT 学号,姓名 FROM student_info WHERE 姓名 LIKE '张%'

4.     嵌套查询
（1）       在student_info表中查找与“刘卫平”性别相同的所有学生的姓名、出生日期。
  SELECT 姓名,出生日期
FROM student_info
WHERE 性别=
          (SELECT 性别
          FROM student_info
          WHERE 姓名='刘卫平')

（2）       使用IN子查询查找所修课程编号为0002、0005的学生学号、姓名、性别。

SELECT 学号,姓名,性别
FROM student_info
WHERE student_info.学号 IN
        (SELECT 学号
         FROM grade
        WHERE 课程编号 IN ('0002', '0005'))

（3）       列出学号为0001的学生的分数比0002号的学生的最低分数高的课程编号和分数。
SELECT 课程编号,分数 FROM grade
WHERE 学号='0001' AND 分数>ANY
(SELECT 分数 FROM grade
WHERE 学号='0002')

（4）       列出学号为0001的学生的分数比0002的学生的最高成绩还要高的课程编号和分数。
SELECT 课程编号,分数
FROM grade
WHERE 学号='0001' AND 分数>ALL
(SELECT 分数
FROM grade
WHERE 学号='0002')

5.     多表查询
（1）       查询分数在80～90范围内的学生的学号、姓名、分数。
  SELECT student_info.学号,姓名,分数
FROM student_info,grade
WHERE 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表中没有总成绩的学生记录。
 delete from totalgrade where 总成绩 is null
四、        实验思考
1．  查询所有没有选修课程的学生信息，返回结果包括学号、姓名、性别。
2．  在student_info表和grade表之间实现交叉连接。
3．  查询每个学生的所选课程的成绩，并列出学号生成分组汇总（总成绩）和明细行（各课成绩）。
提示：使用SELECT语句的COMPUTE选项。