20111019数据查询(1)答案

一、实训准备工作
1、执行教务管理系统创建数据库和表的T-SQL脚本，创建教务管理系统数据库和表。

2、执行提供的数据插入SQL脚本，向数据库表中添加示例数据。

二、根据要求完成以下训练内容
要求：以下内容在查询分析器中调试通过后，将题目、所编写的SQL语句、复制到该文档中。

并以学号+姓名重命名（如“1003101张三.doc”）格式上传到网络硬盘上。

1、单表查询
1)查询所有学生的学号、姓名、性别和年龄，按学号字段的值以降序排列查询结果。

（提示：排序）
SELECT StuId,StuName,Sex,YEAR(getdate())-YEAR(Birthday)
FROM Tb_Student
ORDER BY StuId DESC
SELECT StuId,StuName,Sex,DATEDIFF(YEAR,Birthday,getdate())
FROM Tb_Student
ORDER BY StuId DESC
2)查询所有学生的学号、姓名、性别和年龄，按学号字段的值以降序排列查询结果。

同时，查询显示的结果字段使用中文名称描述。

（提示：别名）
SELECT StuId 学号,StuName 姓名,Sex 性别,DATEDIFF(YEAR,Birthday,getdate()) 年龄 FROM Tb_Student
ORDER BY StuId DESC
3)查询所有职称为“副教授”（职称编码为‘T2’）的所有教师的教师编码、姓名、
生日。

（提示：带条件查询）
SELECT TeacherId 教师编码,TeacherName 姓名,Birthday 生日
FROM Tb_Teacher WHERE TitleId='T2'
SELECT TeacherId 教师编码,TeacherName 姓名,CONVERT(CHAR(4),YEAR(Birthday))+'-'+CONVERT(CHAR(2),MONTH(Birthday))+'-'+CON VERT(CHAR(2),DAY(Birthday)) 生日
FROM Tb_Teacher
WHERE TitleId='T2'
4)查询学校所有姓“王”和“陈”的所有学生信息，按姓名和性别排序。

（提示：模
糊查询中通配符的使用）
SELECT * FROM Tb_Student
WHERE StuName LIKE '王%' OR StuName LIKE '陈%' ORDER BY StuName,Sex
SELECT * FROM Tb_Student WHERE StuName LIKE '[王陈]%' ORDER BY StuName,Sex
5)查询所有在“1986-1-1”之后出生的所有学生的基本信息，要求以先以性别升序排
序，再按学号降序方式显示结果。

（提示：带条件、排序）
SELECT * FROM Tb_Student WHERE Birthday>'1986-1-1' ORDER BY SEX,StuId DESC
6)查询统计各个系的总人数，并按系部编码排序。

（提示：分类汇总）
SELECT DeptId,COUNT(*) AS 总人数
FROM Tb_Student
GROUP BY DeptId
Order by DeptId
7)查询所有课程平均成绩高于80分的学生学号。

（提示：分类汇总）
SELECT StuId,A VG(TotalScore)
FROM Tb_GRADE
GROUP BY StuId
HA VING A VG(TotalScore)>80
2、多表查询
1）查询所有教师信息，显示的字段为：系部名称，教工编号，教师姓名，出生日期。

SELECT DeptName AS系部名称,TeacherId AS教工编号,TeacherName AS教师
姓名,Birthday AS出生日期
FROM Tb_Teacher,Tb_Dept
WHERE Tb_Teacher.DeptId=Tb_Dept.DeptId --自然连接
SELECT DeptName AS系部名称,TeacherId AS教工编号,TeacherName AS教师
姓名,Birthday AS出生日期
FROM Tb_Teacher TT,Tb_Dept TD
WHERE TT.DeptId=TD.DeptId --使用表的别名
SELECT DeptName AS系部名称,TeacherId AS教工编号,TeacherName AS教师
姓名,Birthday AS出生日期
FROM Tb_Teacher TT INNER JOIN Tb_Dept TD ON TT.DeptId=TD.DeptId --内连接
2）查询所有担任过班主任的教师信息，显示的字段为：教工编号，教师名称，出生日
期。

--自然连接
SELECT TT.TeacherId AS教工编号,TeacherName AS教师名称,Birthday AS出
生日期
FROM Tb_Teacher TT,Tb_Class TC
WHERE TT.TeacherId=TC.TeacherId
--外连接
SELECT Tb_Teacher.TeacherId 教工编号,Tb_Teacher.TeacherName 教工姓名,Birthday
出生日期
FROM Tb_Teacher LEFT OUTER JOIN Tb_Class
ON Tb_Teacher.TeacherId=Tb_Class.TeacherId
WHERE ClassId IS NOT NULL
3）查询所有已经选课满的课程信息，显示的字段为：课程编号，课程名称，学分，学
时。

SELECT TC.CourseId AS课程编号,CourseName AS课程名称,CourseGrade AS学
分,LessonTime AS学时
FROM Tb_CourseClass TCC,Tb_Course TC
WHERE TCC.CourseId=TC.CourseId AND MaxNumber=SelectedNumber
SELECT TC.CourseId AS课程编号,CourseName AS课程名称,CourseGrade AS学分,LessonTime AS学时
FROM Tb_CourseClass TCC,Tb_Course TC
WHERE TCC.CourseId=TC.CourseId AND FullFlag='F'
拓展：（TOP的使用）
1）查询年龄最大的学生信息。

SELECT top 1 *FROM Tb_Student ORDER BY Birthday
SELECT top 1 *,YEAR(GETDATE())-YEAR(Birthday)AS年龄
FROM Tb_Student
ORDER BY年龄DESC
2）查询教师评价得分最高的5名教师评价信息，显示的字段为：教师编码，评价总分。

SELECT TOP 5
TeacherId,Sum(TeachingAttitude+TeachingContent+TeachingMethod+Spa reInstruction+Homework)AS评价总分
FROM Tb_TeachingEvaluation
GROUP BY TeacherId
ORDER BY评价总分DESC
3）查询“副教授”教师数最多的系部，显示的字段为：系部编号，副教授人数。

SELECT TOP 1 DeptId,COUNT(*)AS副教授人数
FROM Tb_Teacher,Tb_Title
WHERE Tb_Teacher.TitleId=Tb_Title.TitleId AND TitleName='副教授' GROUP BY DeptId
ORDER BY副教授人数DESC。