SQL习题笔记

这里主要介绍几道题目用以更加深入的了解SQL语句。在工作面试时乃至在考研时，很多时候都会考到SQL语句。如果这篇文章中的所有的题目都搞明白了，那么应付工作面试笔试或者考研笔试将会更加的游刃有余。

来源：http://blog.csdn.net/ghuil/article/details/40981627

题目01

现有关系数据库如下：
学生(学号，姓名，性别，专业，奖学金)
课程(课程号，名称，学分)
学习(学号，课程号，分数)
用SQL语言实现下列题目：
1. 检索不学课程号为“C135”课程的学生信息，包括学号，姓名和专业。

[sql] view plain copy

1. select 学号, 姓名, 专业  
2. from 学生  
3. where 学号 NOT IN (  
4.     select 学号  
5.     from 学习  
6.     where 课程号="C135");  

2. 检索至少学过课程号为“C135”和“C219”的学生信息，包括学号、姓名和专业。

[sql] view plain copy

1. select 学号, 姓名, 专业  
2. from 学生  
3. where 学号 IN (  
4.     select 学号  
5.     from 学习 x, 学习 y  
6.     where x.学号=y.学号 AND x.课程号='C135' AND y.课程号='C219');  

3. 从学生表中删除成绩出现过0分的所有学生信息。

[sql] view plain copy

1. delete from 学生  
2. where 学号 IN (  
3.     select 学号  
4.     from 学习  
5.     where 分数=0);  

4. 定义“英语”专业学生所学课程的信息视图AAA，包括学号、姓名、课程号和分数。

[sql] view plain copy

1. create view AAA(学号, 姓名, 课程号, 分数)  
2. AS  
3. select 学号, 姓名, 课程号, 分数  
4. from 学生, 学习  
5. where 学生.学号=学习.学号 AND 专业="英语";  

题目02

学校有多名学生，财务处每年要收一次学费。为财务处收学费工作设计一个数据库，包括两个关系：
学生(学号，姓名，专业，入学日期)
收费(学年，学号，学费，书费，总金额)
假设规定属性的类型：学费、书费、总金额为数值型数据；学号、姓名、学年、专业为字符型数据；入学日期为日期型数据。列的宽度自定义。
试用SQL语句定义上述表的结构。(定义应包括主键子句和外键子句)。

[sql] view plain copy

1. create table 学生(  
2.     学号 char(8) primary key,  
3.     姓名 char(25),  
4.     专业 char(50),  
5.     入学日期 date  
6. );  
7. create table 收费(  
8.     学年 char(10),  
9.     学号 char(8),  
10.     学费 numeric(4),  
11.     书费 numeric(5,2),  
12.     总金额 numeric(7,2),  
13.     primary key(学号, 学年),  
14.     foreign key(学号) references 学生(学号)  
15. );  

题目03

设某公司数据库中有关系模式如下：职工（职工号，职工名，性别，年龄）工作（职工号，公司号，工资）公司（公司号，公司名，地址）。1. 写出查询每个公司女职工的平均工资的SQL语句。（提示：求平均值用函数AVG()）

[sql] view plain copy

1. select 公司号, 公司名, AVG(工资)  
2. from 职工, 工作, 公司  
3. where 公司.公司号=工作.公司号 AND 职工.职工号=工作.职工号 AND 性别='女'  
4. group by 公司号;  

2. 试用SQL语句写出下列操作：超过50岁职工的工资增加200元。

[sql] view plain copy

1. update 工作  
2. set 工资=工资+200  
3. where 职工号 IN (  
4.     select 职工号  
5.     from 职工  
6.     where 年龄>=50);  

3. 把对职工表的插入权限授给用户A，并许诺他再将此权限授予其他用户。

[sql] view plain copy

1. grant insert ON 职工 TO A WITH GRANT OPTION;  

题目04

图书出版管理数据库中有两个基本表：
图书 (书号，书名，作者编号，出版社，出版日期)
作者 (作者编号，作者名，年龄，地址)
试用SQL语句写出下列查询：
检索年龄低于作者平均年龄的所有作者的作者名、书名和出版社。

[sql] view plain copy

1. select 作者名, 书名, 出版社  
2. from 图书, 作者  
3. where 图书.作者编号=作者.作者编号 AND 年龄 <= (  
4.     select AVG(年龄)  
5.     from 作者);  

题目05

现有关系数据库如下：学生(学号，姓名，性别，专业，奖学金)课程(课程号，名称，学分)学习(学号，课程号，分数)用SQL语言实现下列题目。 

1. 检索没有获得奖学金、同时至少有一门课程成绩在95分以上的学生信息，包括学号、姓名和专业。

[sql] view plain copy

1. select 学号, 姓名, 专业  
2. from 学生  
3. where 奖学金<=0 AND 学号 IN (  
4.     select 学号  
5.     from 学习  
6.     where 分数>=95);  

2. 检索没有任何一门课程成绩在80分以下的所有学生的信息，包括学号、姓名和专业。

[sql] view plain copy

1. select 学号, 姓名, 专业  
2. from 学生  
3. where 学号 NOT IN (  
4.     select 学号  
5.     from 学习  
6.     where 分数<80);  

3. 对成绩得过满分(100分)的学生，如果没有获得奖学金的，将其奖学金设为1000元。

[sql] view plain copy

1. update 学生  
2. set 奖学金=1000  
3. where 奖学金<=0 AND 学号 IN (  
4.     select 学号  
5.     from 学习  
6.     where 分数=100);  

4. 定义学生成绩得过满分(100分)的课程视图 AAA，包括课程号、名称和学分。

[sql] view plain copy

1. create view AAA(课程号, 名称, 学分)  
2. AS  
3. select 课程号, 名称, 学分  
4. from 课程  
5. where 课程号 IN (  
6.     select 课程号  
7.     from 学习  
8.     where 分数=100);  

题目06

设有学生-课程关系数据库，其数据库关系模式为：学生Student(学号Sno，姓名Sname，所在系Sdept，年龄Sage,性别Ssex)课程Course(课程号Cno,课程名称Cname,先修课号Cpno，学分Ccredit)学生选课SC(学号Sno,课程号Cno,成绩Grade)试用SQL语言分别写出下列查询：1. 查询选修了3号或6号课程的学生的学号。

[sql] view plain copy

1. select Sno  
2. from SC  
3. where Cno='3' OR Cno='6';  

2. 查询至少选修了一门其直接先行课为8号课程的学生学号与姓名。

[sql] view plain copy

1. select Sno, Sname  
2. from SC, Course, Student  
3. where Course.Cno=SC.Cno AND Student.Sno=SC.Sno AND Cpno='8';  

3. 查询选修了全部课程的学生学号、姓名和年龄。

[sql] view plain copy

1. -- 查询没有一门课程是该学生没有选的  
2. select Sno, Sname, Sage  
3. from Student  
4. where NOT EXISTS (  
5.     select *  
6.     from Course  
7.     where NOT EXISTS (  
8.         select *  
9.         from SC  
10.         where Student.Sno=SC.Sno AND Course.Cno=SC.Cno));  

4. 查询没有选修2号课程的学生学号与姓名。

[sql] view plain copy

1. select Sno, Sname  
2. from Student  
3. where Sno NOT IN (  
4.     select Sno  
5.     from SC  
6.     where Cno='2');  

5. 查询选修了3门以上课程并且成绩均及格的学生学号与其平均成绩。

[sql] view plain copy

1. -- 本题未经验证，未必正确  
2. select Sno, AVG(Grade)  
3. from SC x  
4. group by Sno  
5. having COUNT(Cno)>3  
6. where NOT EXISTS (  
7.     select *  
8.     from SC y  
9.     x.Cno=y.Cno AND Grade<60);  

6. 将计算机科学系(CS)全体学生的所有成绩置为零。

[sql] view plain copy

1. update SC  
2. set Grade=0  
3. where Sno IN (  
4.     select Sno  
5.     from Student  
6.     where Sdept='CS');  

题目07

设有学生-课程关系数据库，其数据库关系模式为：学生S(学号S#，姓名SN，所在系SD，年龄SA)课程C(课程号C#,课程名称CN,先修课号PC#)学生选课SC(学号S#,课程号C#,成绩G)试用SQL语言分别写出下列查询：1. 求学生'95001'(为学号)所选的成绩为60以上的课程号。

[sql] view plain copy

1. select C#  
2. from SC  
3. where S#='95001' AND G>=60;  

2. 求选读了“数据库概论”，并成绩为80或90的学生学号和姓名。

[sql] view plain copy

1. select S#,SN  
2. from S,SC,C  
3. where C.C#=SC.C# AND SC.S#=S.S# AND C.CN='数据库概论' AND (G=90 OR G=80);  

3. 求选修了全部课程的学生学号、姓名及其所在系名。

[sql] view plain copy

1. -- 没有一门课程是该学生没有选修的  
2. select S#, SN, SD  
3. from S  
4. where NOT EXISTS (  
5.     select *  
6.     from C  
7.     where NOT EXISTS (  
8.         select *  
9.         from SC  
10.         where SC.C#= C.C# AND SC.S#=S.S#  
11.     ));  

4. 找出没有学生选修的课程号及课程名称。

[sql] view plain copy

1. select C#, CN  
2. from C  
3. where C.C# NOT IN (  
4.     select SC.C#  
5.     from SC);  

5. 列出选课数超过3门的学生学号、其所修课程数及平均成绩。

[sql] view plain copy

1. select S#, count(C#), AVG(G)  
2. from SC  
3. group by S#  
4. having count(C#)>3;  

6. 删除“数据结构”课程及所有对它的选课情况。

[sql] view plain copy

1. -- 首先删除SC中的数据  
2. delete from SC  
3. where SC.C# IN (  
4.     select C.C#  
5.     from C  
6.     where CN='数据结构');  
7. -- 其次删除C的数据  
8. delete from C  
9. where Cname='数据结构';  

题目08

下表为数据表Register的相关信息，请写出创建该表的语句(PK代表主键)。字段名数据类型长度允许空。
Id(PK) int8
Username varchar20
Password varchar20

[sql] view plain copy

1. create table Register(  
2.     id int(8) primary key,  
3.     username varchar(20),  
4.     password varchar(20)  
5. );  

题目09

根据下表完成任务：(PK代表主键)

stuId(PK)	sName	classId	professional
IIIT001	zhangsan	IIIT20501	software
IIIT002	lisi	IIIT20501	software
IIIT003	wangwu	IIIT20501	software
IIIT004	zhaoliu	IIIT20501	software

表StudentInfo
1. 请找出stuId为IIIT003的学生信息。

[sql] view plain copy

1. select *  
2. from StudentInfo  
3. where stuId="IIIT003";  

2. 在字段stuId上创建索引。

[sql] view plain copy

1. create unique index stuIndex ON StudentInfo(stuId);  

3. 创建视图查询学生的stuId和sName。

[sql] view plain copy

1. create view StuView  
2. AS  
3. select stuId, sName  
4. from StudentInfo;  

4. 创建存储过程，通过输入stuId对应的学生id号码，输出sName（学生姓名）。

[sql] view plain copy

1. -- 创建存储过程  
2. CREATE PROCEDURE pro_inid_out_sName  
3. @stuIdvarchar(10),sNamevachar(10) OUTPUT  
4. As  
5. Begin  
6.     Select @sName=sName   
7.     from StudentInfo  
8.     where stuId=@stuId  
9. end  
10. -- 调用存储过程  
11. declare @sNamevarchar(10)  
12. execute pro_inid_out_sName ‘IIIT001’,@sName output  

全文完。转载请注明出处。如有不对之处请在留言板中指正。

参考文献

王珊. 数据库系统概论，高等教育出版社.