SQL server 2000 习题（已整理、含答案）(8)

SELECT ‘平均年龄没有超过19’ 8. SELECT * FROM Students

WHERE DATEPART(year,入学日期) =DATEPART(year,GETDATE()) **从Students表中查询出所有当年（系统时间）入学的学生记录。 9.create procedure xxk3 as begin

select 学号,max(成绩) from score group by 学号 end

***显示出AAA库中每个学生的最高成绩 10. DECLARE @MyNO CHAR(8) SET @MyNO='20030001'

IF (SELECT 专业 FROM Students WHERE 学号=@MyNO)='计算机软件' BEGIN

SELECT AVG(成绩) AS 平均成绩 FROM Score

WHERE 学号=@MyNO END

ELSE

PRINT '学号为' +@MyNO+'的学生不存在或不属于软件专业' GO

**首先定义一个名为@MyNo的局部变量，并给它赋初值，如果@MyNo属于计算机软件专业，则显示出平均成绩，否则显示“学号为@MyNo的学生不存在或不属于软件专业”。 11. declare @a numeric(5,2)

set @a=(select avg(成绩) from score) select * from score

where 成绩>=@a

**从score表中查询出大于等于平均成绩的所有记录。 12. declare @a numeric(5,2),@b numeric(5,2) set @a=(select max(成绩) from score) set @b=(select min(成绩) from score) print @a-@b

**求出score表中最高成绩与最低成绩的分数之差。 13.create procedure xxk2 as begin

select x.学号,x.姓名,x.专业,count(*) as 门数 from students x,score y where x.学号=y.学号

group by x.学号,x.姓名,x.专业

end

***显示出AAA库中每个学生的学号、姓名、专业等信息及选课门数

六、在名称为教学库的数据库中包含有学生、课程和选课三个表，它们的定义分别为： 学生(学生号 char(7),姓名 char(6),性别 char(2),出生日期 datetime, 专业 char(10),年级 int)

课程(课程号 char(4),课程名 char(10),课程学分 int) 选课(学生号 char(7),课程号 char(4),成绩 int) 按分析下列所给程序语句的相应功能。 1．select * from 学生 where exists (

select * from 选课

where 学生.学生号=选课.学生号

group by 选课.学生号 having count(*)=3 )

**从教学库中查询出同时选修了3门课程的全部学生。 2．select 专业,count(*) as 专业人数 from 学生 group by 专业

order by 专业人数 desc

**从教学库中查询出每个专业的学生人数，并按人数多少降序排列。 3．select 专业,性别,count(*) as 人数 from 学生

group by 专业,性别 order by 专业

**从教学库中查询出每个专业每种性别的学生人数，并按专业排列。 4．select x.*,课程名,课程学分,成绩 from 学生 x,课程 y,选课 z

where x.学生号=z.学生号 and y.课程号=z.课程号

**从教学库中查询出每个学生选课的全部情况，并依次按学生号和成绩排序。 5．select * from 学生

where not exists (

select y.课程号

from 学生 x inner join 选课 y on x.学生号=y.学生号

where x.姓名=@a and not exists (

select * from 选课

where 学生.学生号=选课.学生号 and y.课程号=选课.课程号 )

)

***从教学库中查询出选修了姓名为@a的学生的全部选课的所有学生。

七、在名称为商品库的数据库中包含有商品表1和商品表2，它们的定义分别为： 商品表1(商品代号 char(8),分类名 char(8),单价 float,数量 int) 商品表2(商品代号 char(8),产地 char(6),品牌 char(6))。 按照下列所给的每种功能写出相应的查询语句。

1. 查询品牌为”Hair”所用商品的商品代码、数量、单价 Select商品表1.商品代号,数量,单价 from 商品表1,商品表2

where 商品表1.商品代号=商品表2.商品代号 and商品表2.品牌=‘Hair’ 2.商品库中查询出每种商品的总价值，并按降序排列出来。 select *,单价*数量 as 总价值 from 商品表1

order by 总价值 desc

3.从商品库中查询出数量最多的一种商品。 select *

from 商品表1

where 数量=some(select max(数量) from 商品表1)

4.从商品库中查询出同一类商品多于一种的所有分类名。 select distinct 分类名 from 商品表1

group by 分类名 having count(*)>1 5.查询每种品牌商品的总数量及品牌。 Select 品牌,sum(数量) as 总数量 from 商品表1,商品表2

where 商品表1.商品代号=商品表2.商品代号 group by 品牌

6.商品库中查询出同一产地的商品只有一种的所有商品。 select 商品表1.*,产地 from 商品表1,商品表2

where 商品表1.商品代号=商品表2.商品代号 and 产地 in (select 产地 from 商品表1 x,商品表2 y

where x.商品代号=y.商品代号 group by 产地 having count(*)=1)

八、有一个“学生－课程”数据库，数据库中包括三个表： (1) “学生”表Student由学号（Sno）、姓名（Sname）、性别（Ssex）、年龄（Sage）、所在系（Sdept）五个属性组成，可记为： Student(Sno,Sname,Ssex,Sage,Sdept) Sno 为关键字。 (2) “课程”表Course由课程号（Cno）、课程名（Cname）、先修课号（Cpno）、学分（Ccredit）四个属性组成，可记为： Course(Cno,Cname,Cpno,Ccredit) Cno为关键字。 (3) “学生选课”表SC由学号（Sno）、课程号（Cno）、成绩（Grade）三个属性组成，可记为： SC(Sno,Cno,Grade) (SNO, CNO) 为关键字。

根据下列数据库和表，用T-SQL完成下列操作(1题2＇, 2至7题,每题3＇)。 1、查所有选修过课的学生的学号

SELECT DISTINCT Sno FROM SC;

2、 建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成，其中学号属性不能为空，并且其值是唯一的。 CREATE TABLE Student

(Sno CHAR(5) NOT NULL UNIQUE, Sname CHAR(20), Ssex CHAR(1), Sage INT,

Sdept CHAR(15));

3、查考试成绩有不及格的学生的学号 SELECT DISTINCT Sno FROM Course WHERE Grade <60;

4、查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列 SELECT Sno, Grade FROM SC

WHERE Cno='3'

ORDER BY Grade DESC;

5、查询每门课程的学生最高低分数 SELECT CNO,MIN(Grade) FROM SC

GROUP BY CNO;

6、查询与“刘晨”在同一个系学习的学生 SELECT Sno, Sname, Sdept FROM Student

WHERE Sdept IN （SELECT Sdept FROM Student

WHERE Sname='刘晨'）;

7、将计算机科学系全体学生的成绩置零 UPDATE SC SET Grade=0 WHERE ‘CS’= ( SELECT Sdept FROM Student

WHERE Student.Sno=SC.Sno)； 九、 操作题 完成下列操作：

1、查询全体学生的详细记录 SELECT * FROM Student;

2、查询年龄在20至23岁之间的学生的姓名、系别、和年龄 SELECT Sname, Sdept, Sage

FROM Student

WHERE Sage BETWEEN 20 AND 23; 3、计算1号课程的学生平均成绩 SELECT AVG(Grade) FR …… 此处隐藏：3047字，全部文档内容请下载后查看。喜欢就下载吧 ……