时间戳

首页 > 技术文章 > 数据库基础实验

数据库基础实验

rongrongrong 2021-09-24 16:41 原文

一、数据库、表的建立及数据输入:　　　　　　 

Student表：

字段名

类型

长度

含义

Sno

varchar

9

学号，主键

Sname

varchar

20

姓名

Ssex

varchar

2

性别

Sage

int

 

年龄

Sdept

varchar

20

系部

Course表：

字段名

类型

长度

含义

Cno

varchar

4

课程号、主键

Cname

varchar

40

课程名

Cpno

varchar

4

先行课

Ccredit

int

 

学分

     SC表:

字段名

类型

长度

含义

Sno

varchar

9

学号、外键

Cno

varchar

4

课程号、外键

Grade

Int

 

成绩

根据以上表的结构，输入以下内容：

Student:

学号

Sno

姓名

Sname

性别

Ssex

年龄

Sage

所在系

Sdept

200215121

李勇

男

20

CS

200215122

刘晨

女

19

CS

200215123

王敏

女

18

MA

200215125

张立

男

19

IS

Course:

课程号Cno

课程名Cname

先行课Cpno

学分Credit

1

数据库

5

4

2

数学

 

2

3

信息系统

1

4

4

操作系统

6

3

5

数据结构

7

4

6

数据处理

 

2

7

PASCAL语言

6

4

SC:

学号Sno

课程号Cno

成绩Grade

200215121

1

92

200215121

2

85

200215121

3

88

200215122

2

90

200215122

3

80

 

建表： 

create table Student(
    Sno char(9)	   	PRIMARY KEY,
    Sname char(20),
    Ssex char(2),
    Sage int,
    Sdept char(20),
);

create table SC(
    Sno char(9),
    Cno char(4),
    Grade int,
)

CREATE  TABLE   Course(
    Cno    CHAR(4)     PRIMARY KEY,
    Cname  CHAR(40),            
    Cpno     CHAR(4),                                         
    Credit  SMALLINT,
)　　

插入数据：

insert into
	Student (Sno, Sname, Ssex, Sage, Sdept)
	values ('200215121','李勇','男',20,'CS'),
		   ('200215122','刘晨','女',19,'CS'),
	       ('200215123','王敏','女',18,'MA'),
	       ('200215125','张立','男',19,'IS');
insert into
	Course (Cno, Cname, Cpno, Credit)
	values ('1','数据库','5',4),
		   ('2','数学','',2),
	       ('3','信息系统','1',4),
	       ('4','操作系统','6',3),
		   ('5','数据结构','7',4),
		   ('6','数据处理','',2),
		   ('7','PASCAL语言','6',4); 
insert into
	SC (Sno, Cno, Grade)
	values ('200215121','1',92),
		   ('200215121','2',85),
	       ('200215121','3',88),
	       ('200215122','2',90),
		   ('200215122','3',80);

　　

实验一：

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

select Sno, Sname 
from Student　　

2.查询全体学生的姓名、学号、所在系 

select Sno, Sname, Sdept
from Student　　

3.查询全体学生的详细记录  

select * from Student　　

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

select Sno, 2021 - Sage as BrithYear
from Student　　

5.查询全体学生的姓名、出生年份和所在的院系，要求用小写字母表示所有系名。

select Sno, 2021 - Sage as BrithYear, lower(Sdept)
from Student

6.查询选修了课程的学生学号   

select Sno
from SC
group by Sno　　

7.查询计算机科学系全体学生的名单  

select *
from Student
where Sdept = 'CS'　　

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

select Sname, Sage
from Student
where Sage < 20　　

9.查询考试成绩有不及格的学生的学号  

select Sno
from SC
where Grade < 60　　

10查询年龄在20~30岁之间的学生的姓名、系别和年龄。

select Sname, Sdept, Sage
from Student
where Sage <= 30 and Sage >= 20

11.查询既不是计算机科学系、数学系，也不是信息系的学生的姓名和性别

select Sname, Ssex
from Student
where Sdept not in ('CS','MA','IS')　　

12.查询学号为200215121的学生的详细情况  

select *
from Student,Course
where Sno = '200215121'　　

13.查询所有姓刘的学生的姓名、学号和性别  

select Sname,Sno,Ssex
from Student
where Sname like '刘%'　　

14.查询某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。   

select Sno
from SC
where Grade is null

15.查询所有成绩的学生的学号和课程号

select Sno, Cno
from SC
where Grade is not null

16.查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列   

select Sno, Grade
from SC
where Cno = '3'
order by Grade Desc　　

17查询选修课程的学生人数  

select count(distinct Sno)
from SC　　

18查询选修了1号课程的学生最高分数  

select MAX(Grade)
from SC
where Cno = '1'　　

19.求各个课程及相应的选课人数   

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

实验二：

1、查询所有年龄在18~22岁(包括18岁和22岁)之间的学生姓名(Sname)及年龄

    （用 BETWEEN  AND 实现）

select Sname, Sage
from Student
where Sage between 18 and 22

2、查询年龄在18~22岁(包括18岁和22岁)之间的学生姓名(Sname)及年龄

     （不用 BETWEEN  AND 实现）

select Sname, Sage
from Student
where  18<= Sage and Sage <= 22

3、查询年龄不在18-22岁之间的学生姓名(Sname)及年龄(Sage)。

select Sname, Sage
from Student
where  18 > Sage and Sage > 22

4、查询自动化系、数学和计算机系学生的学号(Sno)、姓名(Sname)和性别(Ssex)。

select Sname,Sno,Ssex
from Student
where Sdept in ('CS','MA','IS')

5、 查询既不是信息系、数学系、也不是计算机系的学生的姓名(Sname)和性别(Ssex)。

select Sname,Sno,Ssex
from Student
where Sdept not in ('CS','MA','IS')

6、查询所有姓刘的学生的姓名(Sname)、学号(Sno)和性别(Ssex)。其命令为：

select Sname,Sage
from Student
where Sname like '刘%'

7、查询姓“刘”且全名为4个汉字的学生的姓名(Sname)和所在系(Sdept)。

select Sname,Sno,Ssex
from Student
where rtrim(Sname) like '刘___'

8、查询所有不姓刘的学生姓名(Sname)和年龄(Sage)。

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

9、查询课程名为“DB_设计”的课程号(Cno)和学分(Credits)。

select Cno,Credit
from Course
where Cname like 'DB\_' escape'\'

10、查询以"DB_"开头，且倒数第2个汉字字符为“设”的课程的详细情况。

select *
from Course
where Cname like 'DB\_' escape'\' and Cname like '%设_'

11、假设某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。试查询缺少成绩的学生的学号(Sno)和相应的课程号(Cno)。

select Sno,Cno
from SC
where Grade is null

12、查询所有有成绩的学生学号(Sno)和课程号(Cno)。

select Sno,Cno
from SC
where Grade is not null

13、查询选修了C03号课程的学生的学号(Sno)和成绩(Grade)，并按成绩降序排列。

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

14、查询全体学生情况，查询结果按所在系的系名(Sdpet)升序排列，同一系中的学生按年龄(Sage)降序排列。

select *
from Student 
order by Sdept asc, Sage desc

15、查询学生总人数。

select count(Sno)
from Student

16、查询选修了课程的学生人数。其命令为：

select count(distinct Sno)
from SC

17、计算选修C01号课程的学生平均成绩。其命令为：

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

18、查询选修C01号课程的学生最高分数。其命令为：

select max(Grade)
from SC
where Cno = '1'

19、求各个课程号(Cno)及相应的选课人数。其命令为：

select Cno,count(Cno)
from SC
group by Cno

20、查询选修了3门或3门以上课程的学生学号(Sno)。

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

21、查询每个学生及其选修课程的情况。

select Student.*,SC.*
from Student,SC
where Student.Sno = SC.Sno

22、查询每个学生的学号(Sno)、姓名(Sname)、选修的课程名(Cname)及成绩(Grade)。

select Student.Sno,Student.Sname,SC.Cno,SC.Grade
from Student,SC
where Student.Sno = SC.Sno

23、查询选修了编号为“C02”的课程的学生姓名(Sname)和所在系(Sdept)。

select Sname,Sdept
from Student
where Sno in 
(	select Sno
	from SC
	where Cno = '2'
)

24、查询与“李伟”在同一个系学习的学生学号(Sno)、姓名(Sname)和系名(Sdept)。

该查询可构造嵌套查询实现。

select Sno,Sname,Sdept
from Student
where Sdept in
(	select Sdept
	from Student
	where Sname = '李伟'
)

25、查询选修了课程名为“数据结构”的学生学号(Sno)和姓名(Sname)。

select Sno,Sname
from Student
where Sno in 
(	select Sno
	from SC
	where Cno in
	(
		select Cno
		from Course
		where Cname = '数据结构'
	)
)

26、查询非自动化系的不超过自动化系所有学生的年龄的学生姓名(Sname)和年龄(Sage)。

select Sname,Sage
from Student
where Sage < any
(	select Sage
	from Student
	where Sdept = 'IS'
)and Sdept != 'IS'

 

27、查询所有选修了编号为“C01”课程的学生姓名(Sname)和所在系(Sdept)。

select Sname,Sdept
from Student,SC
where Student.Sno = SC.Sno and Cno = '1'

28、查询计算机科学系的学生或年龄不大于20岁的学生信息。

(select *
from Student
where Sdept = 'CS')
union
(select *
from Student
where Sage <= 20
)

29、查询数学系的学生且年龄不大于20岁的学生的交集，这实际上就是查询数学系中年龄不大于20岁的学生。

(select *
from Student
where Sdept = 'MA')
except
(select *
from Student
where Sage <= 20
)

30、查询数学系的学生与年龄不大于20岁的学生的差集。

本查询的等价说法是，查询数学系中年龄大于20岁的学生。

(select *
from Student
where Sdept = 'MA')
except
(select *
from Student
where Sage <= 20)

　　

实验三：

一、连接查询

1.查询每个学生及其选修课程的情况   

select Student.*, SC.*
from Student,SC
where Student.Sno = SC.Sno

2.查询每一门课的间接选修课   

select Cno,Cpno
from Course

3.查询每个学生的学号、姓名、选修的课程名及成绩

select Student.Sno,Sname,Cno,Grade
from Student,SC
where SC.Sno = Student.Sno

二、子查询及组合查询

1.查询与“刘晨”在同一个系学习的学生  

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

2.查询选修了课程名为“信息系统”的学生学号和姓名

select SC.Sno,Sname,Cno
from Student,SC
where Student.Sno = SC.Sno and Cno in 
(
	select Cno
	from Course
	where Cname = '信息系统'
)

3.找出每个学生超过他选修课程平均成绩的课程号   

select Cno
from SC x
where Grade > 
(
	select avg(Grade)
	from SC y
	where x.Sno = y.Sno
	group by Sno 
)
group by Cno　　

4.查询其他系中比计算机科学系某一个学生年龄小的学生的姓名和年龄

select Sname,Sage
from Student
where Sage < any
(
	select Sage
	from Student
	where Sdept = 'CS'
)and Sdept != 'CS'

5.查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄

select Sname,Sage
from Student
where Sage < all
(
	select Sage
	from Student
	where Sdept = 'CS'
)and Sdept != 'CS'

6.查询所有选修了1号课程的学生姓名

select Sname
from Student,SC
where Cno = '1' and Student.Sno = SC.Sno

7.查询没有选修1号课程的学生姓名

select Sname
from Student,SC
where Student.Sno = SC.Sno
except
(select Sname
from Student,SC
where Cno = '1' and Student.Sno = SC.Sno)

8.查询计算机科学系的学生及年龄不大于19岁的学生

select *
from Student
where Sage <= 19
union
(
	select *
	from Student
	where Sdept = 'CS'
)

9.查询选修了课程1或者选修了课程2的学生

select Student.*
from Student,SC
where Cno = '1' and Student.Sno = SC.Sno
union
(
	select Student.*
	from Student,SC
	where Cno = '2'and Student.Sno = SC.Sno
)

10.查询计算机科学系的学生与年龄不大于19岁的学生的交集

select *
from Student
where Sage <= 19
intersect
(
	select *
	from Student
	where Sdept = 'CS'
)

11.查询既选修了课程1又选修了课程2的学生。

select Student.*
from Student,SC
where Cno = '1' and Student.Sno = SC.Sno
intersect
(
	select Student.*
	from Student,SC
	where Cno = '2'and Student.Sno = SC.Sno
)

12.查询计算机科学系的学生与年龄不大于19岁的学生的差集

select *
from Student
where Sage <= 19
except
(
	select *
	from Student
	where Sdept = 'CS'
)

13.查询选修了课程1但没选修了课程2的学生的学号。

select Sno
from SC
where Cno = '1' and Sno not in
(
	select Sno
	from SC
	where Cno = '2'
)

14.查询没选修了课程1和课程2的学生的学号。

select Sno
from Student
where Sno not in
(
	select Sno
	from SC
	where Cno in ('1','2')
)

15.查询选修了课程，但没选修了课程1和课程2的学生学号。

select distinct Sno
from SC
where Sno not in
(
	select Sno
	from SC
	where Cno = '1' or Cno = '2'
	/*where Cno in ('1','2')*/
)

16.查询选修了全部课程的学生姓名

select Sname
from Student
where not exists
(
	select *
	from Course
	where not exists
	(
		select *
		from SC
		where Sno = Student.Sno and Cno = Course.Cno
	)
)

17.查询没有被学生选修的课程号

select Cno
from Course
where Cno not in
(
	select Cno
	from SC
)

数据操纵与视图实验

一、数据操纵与表的修改实验

1、建立一个关系History_Student，其关系模式与Students完全一样，试将关系Students中的所有元组插入到关系History_Student中去(提示：用插入子查询实现)：

select *
into history_Student
from Student

2、将学号为“200215121”的学生年龄改为22岁，即要修改满足条件的一个元组的属性值。

update history_Student
set Sage=22
where Sno = '200215121'

3、将所有学生的年龄增加1岁。

 

update history_Student
set Sage = Sage + 1

 

4、将计算机系（IS）所有学生的成绩置零。

update history_SC
set Grade = 0
where Sno in
(
	select Sno
	from history_Student
	where Sdept = 'IS'
)

5、删除学号为“200215121”的学生选修的课号为“2”的记录。

delete
from history_SC
where Sno = '200215121' and Cno = '2'

6、删除计算机系（IS）所有学生的选课记录。

delete
from history_SC
where Sno in
(
	select Sno
	from history_Student
	where Sdept = 'IS'
)

7、删除所有学生的记录。

delete
from history_Student

8、将学生姓名的长度改为30个字符。

alter table history_Sname
alter column Sname char(30)

9、删除表History_Student

drop table history_Student

二、视图实验

 

1、建立计算机系（IS）学生的视图，并要求进行修改和插入操作时仍需保证该视图只有计算机系的学生，视图的属性名为Sno，Sname，Sage，Sdept。

 

create view IS_VIEW
as select Sno,Sname,Sage,Sdept
from Student
where Sdept = 'IS'

 

2、建立学生的学号(Sno)、姓名(Sname)、选修课程名(Cname)及成绩(Grade)的视图。

create view VIEW_2
as select SC.Sno,Sname,Cname,Grade
from Student,SC,Course
where Student.sno = SC.Sno
and Course.Cno = SC.Cno

3、定义一个反映学生出生年份的视图。

create view VIEW_3
as select Sname,2021 - Sage as year
from Student

4、在计算机系（IS）的学生视图IS_Student中找出年龄(Sage)小于20岁的学生姓名(Sname)和年龄(Sage)。

create view VIEW_4
as select Sname,Sage
from Student
where Sdept='IS' and Sage < 20

5、在Student_CR视图中查询成绩在85分以上的学生学号(Sno)、姓名(Sname)和课程名称(Cname)。

create view Student_CR
as select SC.Sno,Sname,Cname
from SC,Student,Course
where Student.Sno = SC.Sno
and Course.Cno = SC.Cno
and Grade > 85

6、将计算机系（IS）学生视图IS_Student中学号为200215125的学生姓名改为“黄海”。

update IS_VIEW
set Sname = '黄海'
where Sno = '200215125'

7、 向计算机系学生视图IS_Student中插入一个新的学生记录，其中学号为“S09”，姓名为“王海”，年龄为20岁。

insert 
into IS_VIEW
values('S09','王海',20,'IS')

8、删除计算机系学生视图IS_Student中学号为“S09”的记录。

delete
from IS_VIEW
where Sno = 'S09'

9、删除视图Student_CR。

drop view Student_CR　　

 

底部

推荐阅读