实验七参考答案

（一）SQL的定义功能

1．用SQL的定义命令创建一个职工表（zg.dbf）,表结构如下所示。

字段名类型宽度小数点

职工号 C 4

姓名 C 8

性别 C 2

出生日期 d

工资 N 7 2

命令：

create table zg(职工号 c(4),姓名 c(8),性别 c(2),出生日期 d,工资 n(7,2)) 2．修改上题创建的职工表。

（1）增加“职称”字段，类型为字符型，宽度为6。

alter table zg add 职称 c(6)

（2）删除出生日期字段。

alter table zg drop 出生日期

（3）把性别字段的类型改为逻辑型。

alter table zg alter 性别 l

（4）把工资字段改名为实发工资。

alter table zg rename 工资 to 实发工资

（二）SQL的操作功能

1．给课程表（kc）插入一条记录（"2001","英语精读",2," "）

insert into kc(课程号,课程名,学分) values ("2001","英语精读",2)

2．给女同学的入学分数增加10分。（提示：用update 命令）

update xsqk set 入学分数=入学分数+10 where 性别="女"

3．把课程号为“1003”的学分改为3分。（提示：用update 命令）

update kc set 学分=3 where 课程号="1003"

4．给成绩表（cj）中不及格成绩的数据加上删除标记。

delete from cj where 成绩<60

查看是否执行，再去掉删除标记。

Recall all

（三）SQL语言的查询功能

1．查询指定列

（1）查询所有男同学的个人记录。

select * from xsqk where 性别="男"

（2）查询所有同学的学号、姓名、性别、专业和入学分数。

select 学号,姓名,性别,专业,入学分数 from xsqk

2．查询经过计算的列

列出所有同学的学号、姓名、性别、专业和年龄。

select 学号,姓名,性别,专业,2008-year(出生日期) as 年龄 from xsqk 3．去掉重复值

查询成绩表中不重复同学的学号（即去掉重复的学号）。（提示：用Distinct 短语）

select distinct 学号 from cj

4．条件查询

（1）查询计算机科学专业男同学的信息。

select * from xsqk where 专业="计算机科学"and 性别="男"

（2）查询1988年出生的工业工程专业同学的学号、姓名、性别、入学分数和出生日期信息。

select 学号,姓名,性别,入学分数,出生日期 from xsqk;

where year(出生日期)=1988

5．对查询结果进行排序

查询市场营销专业全体学生情况，要求查询结果按入学分数降序排列。

select * from xsqk where 专业="市场营销" order by 入学分数 desc 6．使用统计函数

统计输出每各同学各门课程的期末考试最高分、最低分和平均分并按平均分降序排序。（提示：按学号分组）

select 学号,max(成绩) as 最高分,min(成绩) as 最低分, ;

avg(成绩) as 平均分 from cj order by 平均分 desc group by 学号7．联结查询

（1）查询“市场营销”专业的同学的所有课程成绩。包括同学的学号、姓名、专业、课程号、学期和成绩。（提示：对xsqk、cj两表连接）

select xsqk.学号,xsqk.姓名,xsqk.专业,cj.课程号,cj.学期,cj.成绩 ; from xsqk join cj on xsqk.学号=cj.学号 where 专业="市场营销"

（2）列出所有不及格同学的学号、姓名、专业、课程号、课程名和成绩。（提示：对xsqk、cj、kc三表连接）

select xsqk.学号,xsqk.姓名,xsqk.专业,kc.课程号,kc.课程名,cj.成绩 ; from xsqk join cj join kc on cj.课程号=kc.课程号 ;

on xsqk.学号=cj.学号 where 成绩<60

或者：

select xsqk.学号,xsqk.姓名,xsqk.专业,kc.课程号,kc.课程名,cj.成绩 ; from xsqk join cj on xsqk.学号=cj.学号 ;

join kc on cj.课程号=kc.课程号 ;

where 成绩<60

（3）为“市场营销”专业建立一个包括每个同学学号、姓名、专业和各课程平均分的查询，

按平均分降序排列。（提示：对xsqk、cj两表连接，并按cj表中的学号分组）select xsqk.学号,xsqk.姓名,xsqk.专业,avg(cj.成绩) as 平均分;

from xsqk join cj on xsqk.学号=cj.学号 ;

where 专业="市场营销";

group by cj.学号 ;

order by 平均分 desc

8．嵌套查询

（1）列出“市场营销”专业同学的所有成绩表中的记录，包括学号、课程号、学期、成绩。

（提示：学号作为内外查询连接的条件）

select * from cj ;

where 学号 in (select 学号 from xsqk where 专业="市场营销")

（2）查询成绩表（cj）中至今没有一门课程成绩的同学的信息。包括学号、姓名、性别、专业、出生日期和入学分数等。

（提示：学号作为内外查询连接的条件）

select * from xsqk where 学号 not in (select 学号 from cj)

（3）查询学分最高的课程信息。

（提示：先用max函数在kc表中求出最高学分是多少）

select * from kc where 学分=(select max(学分) from kc)

9．查询结果的不同去向

（1）查询“1001”号课程考试分数前2名的信息。

（用top 2短语在浏览窗口内显示）

select top 2 * from xsqk join cj on xsqk.学号=cj.学号;

where 课程号="1001" order by 成绩 desc

（2）查询“计算机科学”专业的学生信息并将结果存人临时表ls1中。

(使用INTO CURSOR ls1)