实验四 SQL语言-参考答案

实验四SQL语言

【实验目的】

１．掌握SQL语言的使用方法

２．利用SQL语句实现相关的操作

３．能够独立写出一些较复杂的SQL语句

【实验内容】

1. 建立一个Student表，由Sno（学号）、Sname（姓名）、Ssex（性别）、Sage（年龄）、Sdept （所在系）五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。查询名为“创建Student”。

Create table student(sno char(6) not null unique,sname char(4) unique,ssex char(1),sage smallint,sdept char(20))

2. 建立一个SC （学生选课）表，它由学号Sno、课程号Cno，修课成绩Grade组成，其中(Sno, Cno)为主码。），查询名为“创建SC”。

Create table sc(sno char(6),cno char(3),grade single,primary key (sno,cno))

3. 查询所有姓刘的学生的学号与姓名及其出生年份，查询名为“查找姓刘的学生”。Selelct 学号,姓名，year(出生日期) as 出生年份from 学生表where 姓名like “刘*”

4. 查询选修了计算机基础的所有学生的学号、姓名、成绩，查询结果按分数降序排列，查询名为“选修了计算机基础的学生”。

Select 学生表.学号,学生.姓名,选课成绩表.成绩from 学生表,课程表,选课成绩表where 学生表.学号=选课成绩表.学号and 课程表.课程编号=选课成绩表.课程编号and 课程表.课程名称=”计算机基础” order by 选课成绩表.成绩

6. 查询所有年龄在20岁及20岁以下的学生姓名、年龄，查询名为“20岁及以下的学生”。Select 姓名,年龄from 学生表where year(date())-year(出生日期)<=20

7. 查询考试成绩有不及格的学生的学号和姓名。有多门课程不及格的学生，学号和姓名只显示一次，查询名为“不及格的学生”。

Select distinct 学生表.学号，学生表.姓名from 学生表,选课成绩表where学生表.学号=选课成绩表.学号and 选课成绩表.成绩<60

8. 查询福建或广东的学生的姓名、性别和年龄，查询名为“福建或广东的学生”。

Select 姓名，性别，年龄from 学生表where 简历like “*福建*” or简历like “*广东*”

9. 查询选修了“计算机基础”课程的学生人数，查询名为“选修计算机基础的学生”。Select count(选课成绩表.*) as “选修计算机基础的人数” from 课程表,选课成绩表where

课程表.课程编号=选课成绩表.课程编号and 课程表.课程名称=”计算机基础”

10. 创建一个“雇员”表，字段包括雇员号（主键）、姓名（必填字段）、性别、出生日期、部门、备注字段。

Create table 雇员(雇员号smallint primary key,姓名char(4) not null,性别char(1),出生日期date,部门char(20),备注MEMO)

11. 在“雇员”表中增加一个字段，字段名为“职务”，数据类型为“文本”；将“备注”字段删除；将“雇员号”字段的数据类型改为文本型，字段大小为8。

ALTER TABLE 雇员add 职务char(10)

ALTER TABLE 雇员delete 备注

ALTER TABLE 雇员alter 雇员号char(6)

12. 将一条新记录插入到“雇员”表中。其中雇员号为“0001”，“姓名”为“张磊”，“性别”为“男”，出生日期为1990年1月1日，“部门”为“办公室”。

Insert into 雇员(雇员号,姓名,性别,出生日期,部门) values (“0001”,”张磊”,”男”,#1990-1-1#,”办公室”)

13. 将一条新记录插入到“雇员”表中。其中雇员号为“0002”，“姓名”为“王宏”，“性别”为“男”。

Insert into 雇员(雇员号,姓名,性别) values (“0001”,”张磊”,”男”)

14. 将“雇员”表张磊的出生日期改为1980年1月11日。

update 雇员set 出生日期=#1980-1-11#

15. 将“雇员”表中雇员号为“0002”的记录删除。

Delete from 雇员where 雇员号=”0002”

16. 删除已建立的“雇员”表。

Drop table 雇员

17. 查找并显示“教师”表中的所有字段。

Select * from 教师表

18. 查找1992年参加工作的男教师，并显示“姓名”、“性别”、“学历”、“职称”、“系别”和“联系电话”。

Select 姓名,性别,学历,职称,系别,联系电话from 教师表where 性别=”男”and year(工作时间)=1992

19. 计算每名学生的平均成绩，并按平均成绩降序显示。

Select 学生表.姓名,avg(选课成绩表.成绩) as 平均成绩from 学生表,课程表,选课成绩表where学生表.学生编号= 选课成绩表.学生编号and选课成绩表.课程编号=课程表. 课程编号order by avg(选课成绩表.成绩)

20. 查找学生的选课成绩，并显示“学生编号”、“姓名”、“课程名称”和“成绩”。Select 学生表.学生编号,学生表.姓名,课程表.课程名称,选课成绩.成绩from 学生表,课程表,选课成绩表where 学生表.学号=选课成绩表.学号and 课程表.课程编号=选课成绩表.课程编号

21. 显示“90分以上学生情况”表中所有记录和“学生成绩查询”中80分以下的记录，显示内容为“学生编号”、“姓名”、“成绩”3个字段。

Select * from 90分以上学生情况union Select 学生编号,姓名,成绩from 学生成绩查询where 成绩<80

22.查询并显示“学生”表中高于平均年龄的学生记录。

Select * from 学生表where 年龄>(select avg(年龄) from 学生表)