sql经典习题

           <div class="article-source-link2222">
                版权声明：本文为博主转载文章， 转载自：<a href="https://blog.csdn.net/ghuiL/article/details/40981627">https://blog.csdn.net/ghuiL/article/details/40981627</a>
            </div>
        </span>
                </div>
                                                <link rel="stylesheet" href="https://csdnimg.cn/release/phoenix/template/css/ck_htmledit_views-3019150162.css">
                                    <link rel="stylesheet" href="https://csdnimg.cn/release/phoenix/template/css/ck_htmledit_views-3019150162.css">
            <div class="htmledit_views" id="content_views">

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

题目01

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

select 学号, 姓名, 专业
from 学生
where 学号 NOT IN (
	select 学号
	from 学习
	where 课程号="C135");

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

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

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

delete from 学生
where 学号 IN (
	select 学号
	from 学习
	where 分数=0);

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

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

题目02

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

create table 学生(
	学号 char(8) primary key,
	姓名 char(25),
	专业 char(50),
	入学日期 date
);
create table 收费(
	学年 char(10),
	学号 char(8),
	学费 numeric(4),
	书费 numeric(5,2),
	总金额 numeric(7,2),
	primary key(学号, 学年),
	foreign key(学号) references 学生(学号)
);

题目03

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

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

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

update 工作
set 工资=工资+200
where 职工号 IN (
	select 职工号
	from 职工
	where 年龄>=50);

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

grant insert ON 职工 TO A WITH GRANT OPTION;

题目04

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

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

题目05

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

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

select 学号, 姓名, 专业
from 学生
where 奖学金<=0 AND 学号 IN (
	select 学号
	from 学习
	where 分数>=95);

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

select 学号, 姓名, 专业
from 学生
where 学号 NOT IN (
	select 学号
	from 学习
	where 分数<80);

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

update 学生
set 奖学金=1000
where 奖学金<=0 AND 学号 IN (
	select 学号
	from 学习
	where 分数=100);

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

create view AAA(课程号, 名称, 学分)
AS
select 课程号, 名称, 学分
from 课程
where 课程号 IN (
	select 课程号
	from 学习
	where 分数=100);

题目06

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

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

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

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

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

-- 查询没有一门课程是该学生没有选的
select Sno, Sname, Sage
from Student
where NOT EXISTS (
	select *
	from Course
	where NOT EXISTS (
		select *
		from SC
		where Student.Sno=SC.Sno AND Course.Cno=SC.Cno));

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

select Sno, Sname
from Student
where Sno NOT IN (
	select Sno
	from SC
	where Cno='2');

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

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

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

update SC
set Grade=0
where Sno IN (
	select Sno
	from Student
	where Sdept='CS');

题目07

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

select C#
from SC
where S#='95001' AND G>=60;

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

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

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

-- 没有一门课程是该学生没有选修的
select S#, SN, SD
from S
where NOT EXISTS (
	select *
	from C
	where NOT EXISTS (
		select *
		from SC
		where SC.C#= C.C# AND SC.S#=S.S#
	));

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

select C#, CN
from C
where C.C# NOT IN (
	select SC.C#
	from SC);

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

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

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

-- 首先删除SC中的数据
delete from SC
where SC.C# IN (
	select C.C#
	from C
	where CN='数据结构');
-- 其次删除C的数据
delete from C
where Cname='数据结构';

题目08

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

create table Register(
	id int(8) primary key,
	username varchar(20),
	password varchar(20)
);

题目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的学生信息。

select *
from StudentInfo
where stuId="IIIT003";

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

create unique index stuIndex ON StudentInfo(stuId);

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

create view StuView
AS
select stuId, sName
from StudentInfo;

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

-- 创建存储过程
CREATE PROCEDURE pro_inid_out_sName
@stuIdvarchar(10),sNamevachar(10) OUTPUT
As
Begin
	Select @sName=sName 
	from StudentInfo
	where stuId=@stuId
end
-- 调用存储过程
declare @sNamevarchar(10)
execute pro_inid_out_sName ‘IIIT001’,@sName output

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

参考文献

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