数据库技术与应用B-SQL命令题题库.doc

1、SQL 查询命令 题题库 一 、写出 SQL 查询命令 设有一个职工表，表中有若干条记录，表结构定义如下： 编号 姓名 部门 年龄 进厂日期 职称 性别 电话 文本 文本 文本 整型 日期 / 时间 文本 文本 长整型 记录举例： 2006 汪洋 技术部 44 1980/05/06 技术员 男 73979400 编写 SQL 命令完成下面的查询 ： 1、 列出姓名中包含 “ 秋 ” 字的全部男职工的信息： 2、 列出所有除技术部以外的年龄在 40 岁以上的职 工的姓名和电话： 3、 统计进厂迄今已满 25 年或以上的职工人数： 4、 按部门显示职工的平均年龄，显示形式见下表，注意按部门顺序排列

2、： 性别 平均 年龄 财务部 52 技术部 36.2 人事部 52.3 销售部 44.8 5、 显示男职工中年龄最小者的姓名 1、 SELECT * FROM 职工表 WHERE 姓名 LIKE” *秋 *” AND 性别 =” 男 ” 2、 SELECT 姓名 ,电话 FROM 职工表 WHERE 部门 LIKE” !技术部 ” AND 年龄 40 3、 SELECT COUNT(编号 ) FROM 职工表 WHERE YEAR(NOW()-YEAR(进厂日期 )=25 4、 SELECT 性别 ,AVG(年龄 )AS 平均年龄 FROM 职工表 GROUP BY 部门 ORDER BY 部

3、门 5、 SELECT 姓名 FROM 职工表 WHERE 性别 =” 男 ” AND 年龄 =（ SELECT MIN(年龄 ) FROM 职工表） 二、根据主教材第四章所给的商品库和教学库，按照下列所给的每条 SQL 查询语句写出相应的功能。 在名称为商品库 的数据库中包含有商品表 1 和商品表 2，它们的定义分别为： 商品表 1（ 商品代号 char（ 8），分类名 char（ 8），单价 float ，数量 int ） 商品表 2（ 商品代号 char（ 8），产地 char（ 8），品牌 char（ 6） 在名称为教学库的数据库中包含有学生、课程和选课三个表，它们的定义分别为： 学生

4、（ 学生号 char（ 7），姓名 char（ 6），性别 char（ 2），出生日期 datetime，专业 char（ 10） ，年级 int） 课程（ 课程号 char（ 4），课程名 char（ 10），课程学分 int） 选课（ 学生号 char（ 7）， 课程号 char（ 4），成绩 int） 1. select x.商品代号，分类名，数量，品牌 （ 09 年 1 月考） from 商品表 1 x ，商品表 2 y where x.商品代号 = y.商品代号 从商品库 中查询出每一种商品的商品代号、分类名、数量和品牌等信息 2. select distinct 产地 from 商

5、品表 2 从商品库中查询出所有商品的不 同产地 3. select distinct 品牌 （ 07 年 7 月考） from 商品表 2 从商品库中查询出所有商品的不同品牌 4. select count (distinct 产地 ) as 产地数 （ 08 年 7 月考） from 商品表 2 从商品库中查询出所有商品的不同产地的总数 5. select x.商品代号，分类名，数量，品牌 from 商品表 1 x ，商品表 2 y where x.商品代号 = y.商品代号 and （ 品牌 =熊猫 or 品牌 =春兰 ） 从商品库中查询出品牌为熊猫或春兰的所有商品的商品代号、分类名、产地

6、和品牌 6. select 学生 .学生号 , count (* ) as 选课门数 （ 08 年 1 月考、 09.7 考） from 学生 ,选课 where 学生 .学生号 =选课 .学生号 group by 学生 .学生号 从教学库中查询出每个学生选课的门数 7. select distinct x.* from 学生 x, 选课 y, 选课 z where y.学生号 =z.学生号 and y.课程号 all (select avg (单价 ) from 商品表 1 ) 9. 从商品库中查询出数量最多的一种商品。 （ 09 年 7 月考） select * from 商品表 1 wh

7、ere 数量 = some (select max (数量 ) from 商品表 1 ) 10. 从商品库中查询出同一类商品多于一种的所有分类名。 （ 07 年 7 月考） select distinct 分类名 from 商品表 1 group by 分类名 having count(*)1 11. 从商品库中查询出同一产地的商品只有一种的所有商品。 select 商品表 1.* ,产地 from 商品表 1 , 商品表 2 where 商品表 1.商品代号 =商品表 2.商品代号 and 产地 in (select 产地 from 商品表 1 X , 商品表 2 Y where X.商品代

8、号 =y. 商品代号 group by 产地 having count(*)=1 ) 12. 从商 品库中查询出每种商品的总价值，并按降序排列出来。 select *, 单价 *数量 as 总价值 from 商品表 1 order by 总价值 desc 13. 从教学库中查询出至少选修了姓名为 m1 学生所选课程中一门课的全部学生号。 select distinct 学生 .* from 学生 , 选课 where 学生 .学号 =选课 .学号 and 课程号 = any (select 课程号 from 学生 ,选课 where 学生 .学生号 =选课 .学生号 and 姓名 =m1 )

9、14. 从教学库中查询出每门课程被选修的学生人数，并按所选人数的升序排列出课程号、课程名和选课人数。 select 课程 .课程号 , 课程名 , count(课程 .课程号 ) as 人数 from 课程 , 选课 where 课程 .课程号 =选课 .课程号 group by 课程 .课程号 ,课程名 order by 人数 15. 从商品库中查询出不同产地，并按产地所具有的品牌数多少升序排列。 （ 08 年 7 月考） select 产地 ,count (品牌 ) as 品牌数 from 商品表 2 group by 产地 order by 品牌数 16. 从教学库中查询出至少有 5 名

10、学生所选修的全部课程（提示：使用子查询） （ 08 年 1月考） select * from 课程 where exists (select * from 选课 where 课程 .课程号 =选课 .课程号 group by 选课 .课程号 having count(*)=5 ) 四 、根据下面所给的 AAA 数据库，写出下列每条查询 语句的执行结果，或者写出下列每条语句或程序段的功能。 假设存在名为 AAA 的数据库，包括 Students（ 学号 char(8)，姓名 varchar(8)，年龄 int，专业 varchar(20)，入学日期 DateTime）和 Score（学号 cha

11、r(8)，课程名 varchar(10)，成绩 numeric(5,2)）两张表。 1. SELECT * FROM Students WHERE DATEPART（ year，入学日期） =datepart（ year ， GETDATE（） 从 Students 表中查询出所有当年入学的学生记录 2. DECLARE MyNO char（ 8） SET MyNO=20030001 IF (SELECT 专业 from students WHERE 学号 =MyNO)=计算机软件 Begin Select AVG(成绩 ) AS 平均成绩 From scroe Where 学号 = MyNO

12、 End Else Print 学号为 +MyNO +的学生号不存在或不属于软件专业 Go 首先定义一个名为 MyNo 的局部变量，并给它赋初值，如果 MyNo 属于计算机软件专业，则显示出平均成绩，否则显示“学生为 MyNo 的学生不存在或不属于软件专业”。 3. declare a numeric(5,2) （ 09.7 考 ） set a=(select avg(成绩 ) from score) select * from score where 成绩 =a 从 Score 表中查询出大于等于平均成绩的所有记录。 4. declare a numeric(5,2)， b numeric(

13、5,2) （ 08 年 1月考） set a=(select max(成绩 ) from score) set b=(select min(成绩 ) from score) print a- b 求出 Score 表中最高成绩与最低成绩的分数之差 5. declare a char(6) set a=刘亮 if(exists(select * from students where 姓名 =a) print 姓名为 +a+的同学存 在！ else print 姓名为 +a+的同学不存在！ 从 Students 表中查询出姓名为 a 值的学生是否存在，根据不同情况显示出相应信息。 6. decl

14、are a char(8) （ 08 年 7 月考） set a=计算机 select 计算机专业人数 =count（ *） from students Where left（专业， 3） = a 从 Students 表中统计出专业名开头为 a 的值的所有学生人数 7. select year(入 学日期 ) as 入学年份 ,count(*) as 人数 from students group by year(入学日期 ) 从 Students 表中分组统计出每个年份入学的学生人数 8. select month (入学日期 ) as 入学月份 ,count(*) as 人数 （ 09 年

15、 1 月考） from students group by month (入学日期 ) 从 Students 表中分组统计出每个月份入学的学生人数 9. select day(入学日期 ) as 入学日号 ,count(*) as 人数 from students group by day(入学日期 ) 从 Students 表中分组统计出每个日号入学的学生人数 10. create procedure xxk1 （ 09.7 考 ） as begin select * from students x， score y Where x.学号 = y.学号 End 显示出 AAA 数据库中所有学

16、生的记录信息及选课成绩 11.create procedure xxk2 （ 08 年 1 月考） as begin select x.学号 ,x.姓名 ,x.专业 ,count(*) as 门数 from students x,score y where x.学号 =y.学号 group by x.学号 ,x.姓名 ,x.专业 end 显示出 AAA 数据库中每个学生的学号、姓名、专业及选课门数。 12. create procedure xxk3 （ 08 年 7 月考） as begin select 学号， avg（成绩） as 平均成绩 from score group by 学号

17、end 显示出 AAA 数据库中每个学生的平均成绩 13.create procedure xxk4 ( a char(8),b varchar(10),c numeric(5,2) ) as begin update score set 成绩 =c where 学号 =a and 课程名 =b end 修改 Score 表中学号为 a 的值，课程名为 b 的值的学生的成绩为 c 的值。 14. create procedure xxk5（ 07 年 7 月考） ( a char(8),b varchar(10),c numeric(5,2) ) as begin update score insert into score values（ a， b， c） end 向 Score 表中插入学号为 a、课程名为 b、成绩为 c 的学生成绩记录。 15.create procedure xxk6 （ 09 年 1 月考） ( a char(8), b varchar(10) ) as begin delete from score where 学号 = a and 课程名 = b end 从 Score 表中删除学号为 a、课程名为 b 的学生成绩记录。