数据库第三次作业

2011-2012学年第二学期课程实验报告课程名称：数据库系统原理

实验名称：交互式SQL（二）：数据查询

姓名陈宇放班级软件一班学号1001030020 实验台编号同组同学

实验课表现出勤、表现得分25% 25 实验报告

得分50%

实验总分操作结果得分25% 25

实验目的

1．掌握SELECT语句的基本语法。

2．掌握集合函数的作用及使用方法。

3．熟悉普通连接、外连接和自身连接的概念。

4．能够熟练使用连接查询从多个表中查询数据。

5．能够熟练地使用子查询查询数据。

实验内容（实验步骤和实验结果的简单描述，方便同学自己以后阅读）

--一、简单查询

--1. 查询年龄最小的前个同学的姓名、专业和年龄

SELECT TOP 3 姓名,专业,2012-YEAR(出生日期)年龄

FROM XS

ORDER BY出生日期DESC

--2. 查询XS表中所有同学的学号、姓名和总学分，结果中各列的标题分别指定为num，name和mark。

SELECT学号num,姓名name,总学分mark

FROM XS

--3. 查询XS表中的学生数据来自哪些专业（使用DISTINCT子句消除结果集中的重复行）。

SELECT DISTINCT专业

FROM XS

--4. 查询XS表中专业为“计算机”的同学的情况。

SELECT*

FROM XS

WHERE专业='计算机'

--5. 查询XS表中年出生的学生姓名和专业情况。

SELECT姓名,专业

FROM XS

WHERE YEAR(出生日期)= 1992

--6. 查询XS表中姓“张”或“王”或“李”且单名的学生的情况。

SELECT*

FROM XS

WHERE姓名LIKE'张_'OR姓名LIKE'王_'OR姓名LIKE'李_'

--7. 查询XS表中专业为“计算机”且总学分尚未确定的学生情况。

SELECT*

FROM XS

WHERE专业LIKE'计算机'AND总学分IS NULL

--8. 从XS表中查询学生的基本信息，要求按照总学分从高到低排序，学分相同时，按学号由低到高排序。。

SELECT*

FROM XS

ORDER BY总学分DESC,学号ASC

--二、数据汇总

--1. 求选修了“”课程的学生的平均成绩。

SELECT XS_KC.课程号,AVG(成绩)平均成绩

FROM KC,XS_KC

WHERE XS_KC.课程号=KC.课程号

GROUP BY XS_KC.课程号

HAVING XS_KC.课程号LIKE'101'

--3. 求学号为“”学生的总成绩。

SELECT XS.学号,SUM(成绩)总成绩

FROM XS,XS_KC

WHERE XS.学号=XS_KC.学号

GROUP BY XS.学号

HAVING XS.学号LIKE'4102101'

--4. 求专业为“计算机”的学生的总人数。

SELECT专业,COUNT(专业)专业人数

FROM XS

WHERE专业LIKE'计算机'

GROUP BY专业

--5. 求选修了“”课程的学生的人数。

SELECT课程号,COUNT(学号)选修人数

FROM XS_KC

WHERE课程号LIKE'101'

GROUP BY课程号

--6. 求选修了任意一门课程的学生的人数。

SELECT课程号,COUNT(学号)选修人数

FROM XS_KC

GROUP BY课程号

--7. 统计各个专业的学生数。（按专业分组）

SELECT专业,COUNT(学号)学生数

FROM XS

GROUP BY专业

--8. 统计各个专业的男女生人数。格式如下：

--专业性别人数

--………………

SELECT专业,性别,COUNT(学号)人数

FROM XS

GROUP BY性别,专业

--9. 查找平均成绩在分以上的学生的学号和平均成绩。

SELECT学号,AVG(成绩)平均成绩

FROM XS_KC

GROUP BY学号

HAVING AVG(成绩)> 80

/*提问为什么不能用WHERE?用别名代替列名不能代表列的属性吗?*/

--10. 查找选修的课程中超过门成绩在分以上的学生的学号和成绩高于分的门数。格式如下：--学号成绩高于分的门数

--…………

SELECT学号,COUNT(成绩)成绩高于分的门数

FROM XS_KC

WHERE成绩>= 80

GROUP BY学号

HAVING COUNT(成绩)>= 2

--三、连接查询

--1. 查询每个学生的基本情况及选修的课程情况，格式如下：

--学号姓名专业课程号成绩

--…………………………

SELECT XS.学号,XS.姓名,XS.专业,XS_KC.课程号,XS_KC.成绩

FROM XS,XS_KC

WHERE XS.学号=XS_KC.学号

--2. 查询选修了课程号为的每个学生的姓名及成绩。

SELECT姓名,成绩

FROM XS,XS_KC

WHERE XS.学号=XS_KC.学号AND课程号='101'

--3. 查询“计算机”专业且选修了“计算机基础”课程的学生的学号、姓名及成绩。

SELECT XS.学号,姓名,成绩

FROM XS,KC,XS_KC

WHERE XS.学号=XS_KC.学号AND KC.课程号=XS_KC.课程号

AND专业='计算机'AND课程名='计算机基础'

--4. 查询选修了课程号为的每个学生的基本情况及成绩，若学生未选修号课程，也包括其情况。

WITH NEW_TABLE(成绩,学号)AS

(SELECT成绩,XS.学号

FROM XS,XS_KC

WHERE XS.学号=XS_KC.学号AND课程号='101'

)

SELECT XS.*,NEW_TABLE.成绩

FROM NEW_TABLE RIGHT OUTER JOIN XS ON(XS.学号=NEW_TABLE.学号)

--5. 查询生日相同的学生的姓名和出生日期。格式如下

--姓名姓名出生日期

--………………

--其中：“姓名”和“姓名”中的姓名不能相同。

SELECT S1.姓名姓名,S2.姓名姓名,S1.出生日期