数据库练习题(基础)

#一、基本表的定义与删除

T1.

用 SQL 语句创建如下三张表：学生（Student）,课程表（Course）,和学生选课表（SC）,这三张表的结构如表 1-1 到表 1-3 所示。

表1-1 Student 表结构

|列名|说明|数据类型|约束| |:-|:-||:-|:-| |Sno|学号|字符串|长度为7，主码| |Sname|姓名|字符串|长度为10，非空| |Ssex|性别|字符串|长度为2，取‘男’或‘女’| |Sage|年龄|整数|取值15~45| |Sdept|所在系|字符串|长度为20 默认为‘计算机系’|

1
2
3
4
5
6


create table Student(
Sno varchar(7) primary key,
Sname varchar(10) not null,
Ssex varchar(2) check(Ssex='男' or Ssex='女'),
Sage int check(Sage>=15 and Sage <=45),
Sdept varchar(20) default('计算机系'));

表1-2 Course 表结构

|列名|说明|数据类型|约束| |:-|:-||:-|:-| |Cno|课程号|字符串|长度为10，主码| |Cname|课程名|字符串|长度为20，非空| |Ccredit|学分|整数|取值大于0| |Semster|学期|整数|取值大于0| |Cperiod|学时|整数|取值大于0|

1
2
3
4
5
6
7


create table Course(
Cno varchar(10) primary key,
Cname varchar(20) not null,
Ccredit int check(Ccredit>0),
Semster int check(Semster >0),
Cperiodint check(Cperiod>0)
);

表1-3 SC 表结构

表1-2 Course 表结构

|列名|说明|数据类型|约束| |:-|:-||:-|:-| |Sno|学号|字符串|长度为7，主码，参照Student的外码| |Cno|课程名|字符串|长度为10，主码，参照Course| |Grade|成绩|整数|取值0~100|

1
2
3
4
5
6
7


create table SC(
Sno varchar(7),
Cno varchar(10),
Grade int check(Grade >=0 and Grade <=100),
primary key(Sno,Cno),
foreign key (Sno) references Student(Sno),
foreign key (Cno) references Course(Cno));

PS:

外码(键): 一个关系模式(r1)可能在它的属性中包含另一个关系模式(r2)的主码,这个属性在 r1 上称作参照 r2 的外码。关系 r1 称为外码依赖的参照关系，关系 r2 称为外码的被参照关系。FK 一定来自另一个表的 PK，FK 是 PK 的子集。

如果外键或者主键要求命名，请使用以下语法

1
2


constraint pk_Student_Sno primary key (Sno)
constraint fk_Student_Sno foreign key (Sno) references Student(Sno)//SC表，参照表后边的属性可以省略

已有表，添加外键

1
2
3
4


alter table SC
add constraint fk_Student_Sno 
foreign key (Sno) 
references Student(Sno);

#二、修改表结构

T2.

为 SC 表添加“选课类别”列，此列的定义为 XKLB char(4)

alter table SC add XKLB char(4);

T3.

将新添加的 XKLB 的类型修改为 char(6)

alter table SC alter column XKLB char(6);

T4.

删除 Course 表的 Cperiod 列

1

alter table Course drop column Cperiod;

T5.

重命名 Student 表的 Ssex 列为 sex

1

EXEC sp_rename 'Student.Ssex','sex';

#三、数据查询功能

表3-1 Student表数据

Sno	Sname	Ssex	Sage	Sdept
9512101	李勇	男	19	计算机系
9512102	刘晨	男	20	计算机系
9512103	王敏	女	20	计算机系
9521101	张立	男	22	信息系
9521102	吴宾	女	21	信息系
9521103	张海	男	20	信息系
9531101	钱小平	女	18	数学系
9531102	王大力	男	19	数学系

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


insert into Student
values
('9512101','李勇','男',19,'计算机系'),
('9512102','刘晨','男',20,'计算机系'),
('9512103','王敏','女',20,'计算机系'),
('9521101','张立','男',22,'信息系'),
('9521102','吴宾','女',21,'信息系'),
('9521103','张海','男',20,'信息系'),
('9531101','钱小平','女',18,'数学系'),
('9531102','王大力','男',19,'数学系');

表3-2 Course 表数据

Cno	Cname	Ccredit	Semster
C01	计算机文化学	3	1
C02	VB	2	3
C03	计算机网络	4	7
C04	数据库基础	6	6
C05	高等数学	8	2
C06	数据结构	5	4

1
2
3
4
5
6
7
8


insert into Course(Cno,Cname,Ccredit,Semster)
values
('C01','计算机文化学',3,1),
('C02','VB',2,3),
('C03','计算机网络',4,7),
('C04','数据库基础',6,6),
('C05','高等数学',8,2),
('C06','数据结构',5,4);

表3-3 SC 表数据

Sno	Cno	Grade	XKLB
9512101	c01	90	必修
9512101	c02	86	选修
9512101	c06		必修
9512102	c02	78	选修
9512102	c04	66	必修
9521102	c01	82	选修
9521102	c02	75	选修
9521102	c04	92	必修
9521102	c05	50	必修
9521103	c02	68	选修
9521103	c06		必修
9531101	c01	80	选修
9531101	c05	95	必修
9531102	c05	85	必修

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


insert into SC 
values
('9512101','c01',90,'必修'),
('9512101','c02',86,'选修'),
('9512101','c06',NULL,'必修'),
('9512102','c02',78,'选修'),
('9512102','c04',66,'必修'),
('9521102','c01',82,'选修'),
('9521102','c02',75,'选修'),
('9521102','c04',92,'必修'),
('9521102','c05',50,'必修'),
('9521103','c02',68,'选修'),
('9521103','c06',NULL,'必修'),
('9531101','c01',80,'选修'),
('9531101','c05',95,'必修'),
('9531102','c05',85,'必修');

T6.

查询全体学生的学号与姓名

select Sno,Sname 
from Student;

T7.

查询全体学生的姓名，学号和所在系

select Sno,Sname,Sdept 
from Student;

T8.

查询全体学生的所有信息（三张表以上的关联查询）

select *
from Student left join SC on Student.Sno=SC.Sno left join Course on SC.Cno=Course.Cno

T9.

查询全体学生的姓名及其出生年份

1
2


select Sname,2018-Sage as '出生年份'
from Student

T10.

查询全体学生的姓名和出生年份，并在出生年份列前加入一个列，此列的每行数据均为“Year of Birth”常量值

1
2


select Sname,'Year of Birth',2018-Sage as '出生年份'
from Student

T11.

在选课表（SC）中查询有哪些学生选修了课程，并列出学生的学号

1
2


select distinct Sno
from SC

T12.

查询计算机系全体学生的姓名

1
2
3


select Sname
from Student
where Sdept='计算机系'

T13.

查询所有年龄在20岁以下的学生的姓名及年龄

1
2
3


select Sname,Sage
from Student 
where Sage<20

T14.

查询考试成绩不及格的学生的姓名

1
2
3


select Sname
from Student,SC 
where Student.Sno=SC.Sno and SC.Grade<60

T15.

查询年龄在20~23岁之间的学生的姓名，所在系和年龄

1
2
3


select Sname,Sdept,Sage
from Student 
where Sage between 20 and 23

T16.

查询年龄不在20~23之间的学生的姓名，所在系和年龄

1
2
3


select Sname,Sdept,Sage
from Student 
where Sage not between 20 and 23

T17.

查询信息系，数学系和计算机系学生的姓名和性别

1
2
3


select Sname,Ssex
from Student 
where Sdept in ('信息系','数学系','计算机系')

T18.

查询既不属于信息系，数学系，也不属于计算机系的学生的姓名和性别

1
2
3


select Sname,Ssex
from Student 
where Sdept not in ('信息系','数学系','计算机系')

T19.

查询学生表中姓“张”的学生的详细信息

1
2
3


select *
from Student 
where Sname like '张%'

T20.

查询学生表中姓“张”，姓“李”和姓“刘”的学生的情况

1
2
3


select *
from Student 
where Sname like '张%' or Sname like '李%' or Sname like '刘%'

使用下边的这种写法更加简便

1
2
3


select *
from Student 
where Sname like '[张李刘]%' 

T21.

查询名字中第2个字为“小”或“大”字的学生的姓名和学号

1
2
3


select Sname,Sno
from Student 
where Sname like '_[小大]%' 

T22.

查询所有不姓“刘”的学生的姓名

1
2
3


select Sname
from Student 
where Sname not like '刘%' 

T23.

从学生表中查询学号的最后一位不是2，3，5的学生的情况

1
2
3


select *
from Student 
where Sno not like '%[235]' 

T24.

查询无考试成绩的学生的学号和相应的课程号

1
2
3


select Sno,Cno
from SC 
where Grade is null 

T25.

查询所有有考试成绩的学生的学号和课程号

1
2
3


select Sno,Cno
from SC 
where Grade is not null 

T26.

查询计算机系年龄在20岁以下的学生的姓名

1
2
3


select Sname
from Student 
where Sdept='计算机系' and Sage<20

T27.

将学生按年龄升序排序

1
2
3


select *
from Student 
order by Sage asc

T28.

查询选修了课程“c02”的学生的学号及其成绩，查询结果按成绩降序排列

1
2
3
4


select Sno,Grade
from SC
where Cno='C02'
order by Grade desc

T29.

查询全体学生的信息，查询结果按所在系的系名升序排列，同一系的学生按年龄降序排列

1
2
3


select *
from Student
order by Sdept,Sage desc

T30.

统计学生总人数

1
2


select count(*)
from Student

T31.

统计选修了选修课程的学生的人数

1
2
3


select count(distinct Sno)
from SC
where XKLB='选修'

T32.

计算学号为9512101的学生的考试总成绩之和

1
2
3


select sum(Grade)
from SC
where Sno='9512101'

T33.

计算课程“c01”的学生的考试平均成绩

1
2
3


select avg(Grade)
from SC
where Cno='C01'

T34.

查询选修了课程“c01”的学生的最高分和最低分

1
2
3


select max(Grade),min(Grade)
from SC
where Cno='C01'

T35.

统计每门课程的选课人数，列出课程号和人数

1
2
3


select Cno,count(Sno) as '选课人数'
from SC
group by Cno

T36.

查询每名学生的选课门数和平均成绩

1
2
3


select Sno,count(Cno) as '选课门数',avg(Grade) as '平均成绩'
from SC
group by Sno

T37.

查询选修了3门以上课程的学生的学号

1
2
3
4


select Sno
from SC
group by Sno
having count(Cno)>3

T38.

查询选课门数等于或大于4门的学生的平均成绩和选课门数

1
2
3
4


select Sno,avg(Grade) as '平均成绩',count(*) as '选课门数'
from SC
group by Sno
having count(*)>=4

#四、多表查询

T39.

查询每个学生的情况及其选课的情况

1
2


select *
from Student left outer join SC on Student.Sno=SC.Sno

T40.

查询计算机系学生的选课情况，要求列出学生的名字，所修课的课程号和成绩

1
2
3


select Sname,Cno,Grade
from Student,SC
where Student.Sno=Sc.Sno and Sdept='计算机系'

T41.

查询信息系选修VB课程的学生的成绩，要求列出学生姓名，课程名和成绩

1
2
3


select Sname,Cname,Grade
from Student,SC,Course
where Student.Sno=SC.Sno and SC.Cno=Course.Cno and sdept='信息系' and Cname='VB'

1
2
3


select Sname,Cname,Grade 
from Student join SC on Student.Sno=SC.Sno join Course on Course.Cno=SC.Cno 
where Sdept = '信息系' and Cname = 'VB';

T42.

查询所有选修了VB课程的学生的情况，要求列出学生姓名和所在的系

1
2
3


select Sname,Sdept
from Student join SC on Student.Sno=SC.Sno join Course on Course.Cno=SC.Cno 
where Cname='VB'

T43.

查询与刘晨在同一个系学习的学生的姓名和所在系

1
2
3
4
5


select Sname,Sdept
from Student
where Sdept=(select Sdept
             from Student
             where Sname='刘晨')

1
2
3


select s2.Sname,s2.Sdept
from Student as s1,Student as s2
where s1.Sname='刘晨' and s2.Sname !='刘晨' and s1.Sdept=s2.Sdept 

T44.

查询学生的选课情况，包括选修课程的学生和没有修课的学生

1
2


select *
from Student left join SC on Student.Sno=SC.Sno

T45.

查询数学系成绩在80分以上的学生的学号，姓名

1
2
3


select Student.Sno,Sname
from Student join SC on Student.Sno=SC.Sno
where Sdept='数学系' and Grade>80

T46.

查询计算机系考试成绩最高的学生的姓名

1
2
3
4
5
6


select Sname
from Student join SC on Student.Sno=SC.Sno
where Sdept='计算机系' and Grade=(select max(Grade)
                                 from Student join SC on Student.Sno=SC.Sno
								 Group by Sdept
								 having Sdept='计算机系')

#五、嵌套子查询

(T43)

T47.

查询成绩大于90分的学生的学号和姓名

1
2
3
4
5


select Sno,Sname
from Student
where Sno in (select Sno
              from SC
              where Grade>90)

T48.

查询选修了“数据库基础”课程的学生的学号和姓名

1
2
3
4
5
6
7


select Sno,Sname
from Student
where Sno in (select Sno
              from SC 
              where Cno in (select Cno
                            from Course
                            where Cname='数据库基础'))

T49.

查询选修了刘晨没有选修的课程的学生的学号和所在系

1
2
3
4
5
6
7
8
9


select Sno,Sdept
from Student
where Sno in (select Sno
              from SC
              where Cno not in (select Cno
                                from SC
                                where Sno=(select Sno
                                           from Student
                                           where Sname='刘晨')))

T50.

查询选修了课程“c02”且成绩高于此课程的平均成绩的学生的学号和成绩

1
2
3
4
5


select Sno,Grade
from SC
where Cno='C02' and Grade>(select avg(Grade)
                           from SC
                           where Cno='C02')

T51.

查询选修了课程“c01”的学生姓名

1
2
3
4
5


select Sname
from Student
where Sno in (select Sno
              from SC
              where Cno='C01')

1
2
3
4
5


select Sname
from Student
where exists (select *
              from SC
              where Sno=Student.Sno and Cno='C01')

PS.一般来说，在SQL中in子查询都可以用exists代替。EXISTS子查询可以看成是一个独立的查询系统，只为了获取真假逻辑值，EXISTS子查询与外查询查询的表是两个完全独立的毫无关系的表，当我们在子查询中添加了Sno关联之后，EXISTS子查询与外查询查询的表就统一了，是二者组合组建的虚表，是同一个表（这样当子查询查询到虚表中当前行的Cno为C01时，则将虚表当前行中对应的Sname查询到了）. 　　重点在于Sno关联之上，添加Sno关联，数据库会先将两张表通过Sno关联组合成一张虚表，所有的查询操作都在这张虚表上完成。

#六、自查询

T52.查询所有成绩大于C01课程最高成绩的学生的学号

1
2
3
4
5


select SC2.Sno
from SC as SC1,SC as SC2
where SC1.Sno=SC2.Sno and SC1.Cno=SC2.Cno and SC2.Grade>all(select Grade
                                                            from SC
                                                            where SC.Cno='C01')

#七、更新数据

T53.

将所有学生的年龄加1

1
2


update Student
set Sage=Sage+1

T54.

将“9512101”学生的年龄改为21岁

1
2
3


update Student
set Sage=21
where Sno='9512101'

T55.

将计算机系学生的成绩加5分

1
2
3
4
5


update SC
set Grade=Grade+5
where Sno in (select Sno
              from Student
              where Sdept='计算机系')

#八、删除数据

T56.

删除所有学生的选课记录

1

DELETE FROM SC

T57.

删除所有不及格学生的选课记录

1
2


delete from SC
where Grade<60

T58.

删除计算机系不及格学生的选课记录

1
2
3
4


delete from SC
where Grade<60 and Sno in (select Sno
                           from Student
                           where Sdept='计算机系')

数据来源于网络，整理@lihui

测试代码：

建表代码

题目代码

测试环境：SQL Server 2017

考试结束了，烤糊了😭...附上题目留与后人吧

2017-2018学年大连理工大学软件学院数据库考试题目说明

20分 10个选择（比较简单，复习注意概念，比如DBS，DB，DBMS都是什么。其中有两个有关事务的题，即第十二章内容，分别是ACID的四个性质内容和结束事务并回滚的语句是什么问题）

50分 4个关系代数，6个SQL，一个5分。考了not exists和not in 相互转换, not exists…except结构, 标量子查询，分组聚集的内容（懵的一匹@_@）

10分范式分解属性闭包和候选码（6分），判断第几范式（2分），BCNF分解（2分）

20分 ER图画ER图（10分），转换成关系模式（10分）。

（回忆版）商店记录商品信息和客户信息，商品信息包括商品编号、名称、单价等，客户信息包括客户编号、客户姓名、电话等。一个顾客可以购买多个商品，一件商品可以购买不同数量，一次性的购物记录在一个购物单里，商品销售时价格可以调整。要求商店可以查询一个客户的消费记录和消费总额。根据题意设计ER图，可以自己添加必要属性。

数据库练习题(基础)

相关文章：