数据库上机实验学习记录最新Day1117

create table student
(
	Sno char(9) primary key,
	Sname char(20) unique,
	Ssex char(2),
	Sage smallint,
	Sdept char(20)

);

//查询
select Sno,Sname,Ssex,Sage,Sdept from student where Sname='wy';

//插入
insert into student values('3','dh','v','12','jg');
insert into student values('4','fg','n','45','sp');

insert into student values('5','hg','n','25','dp');
insert into student values('6','fg','v','55','ep');
insert into student values('7','rg','n','66','tp');
//删除
delete Student where Sname='hg';


//更新
update student set Sname='zl' where Sname='fg' and Ssex='n';

Day1028

题目要求：

一、在数据库pubs中，创建如下三张表：

1、学生表(student) (

 学号(sno)普通编码定长字符类型，长度7，主码，

 姓名(sname)普通编码定长字符类型，长度8，非空，

 性别(ssex)统一编码定长字符类型，长度1，只能是‘男’或‘女’，

 年龄(sage)微整型，1-150之间，

 所在系(sdept)统一编码可变长字符类型，长度10

）

create table student
(
	Sno char(7) primary key,
	Sname char(8) not null,
	Ssex nchar(1) check(Ssex='M' or Ssex='F'),
	Sage smallint check(Sage>=1 and Sage<=150),
	Sdept nchar(10)
);

 

 

2、课程表(course) (

 课程号(cno)普通编码定长字符类型，长度6，主码，

 课程名(cname)统一编码定长字符类型，长度10，非空，

 先修课程号(cpno)普通编码定长字符类型，长度6，可以空，外码（对应表course）,

 学分(credit)微整型，1-10之间，

 学期(semester)微整型，1-10之间，

）

 

create table course
(
	Cno char(6) primary key,
	Cname nchar(10) not null,
	Cpno char(6) null ,
	credit smallint check(credit>=1 and credit<=10),
	semester smallint check(semester>=1 and semester<=10),
	foreign key(Cpno) references course(Cno)

);

 

 

3、修课表（sc）(

 学号(sno)普通编码定长字符类型，长度7，主码，外码（对应表student）,

 课程号(cno)普通编码定长字符类型，长度6，主码，外码（对应表course）,

 成绩(grade)微整型，0-100之间，

 修课类别（xklb）普通编码定长字符类型，长度4

）

 

create table sc
(
	Sno char(7),
	Cno char(6),
	grade smallint check(grade>=0 and grade<=100),	
	xklb char(4),
	primary key (Sno,Cno),
	foreign key(Sno) references student(Sno),
	foreign key(Cno) references course(Cno)
);

 

 

二、

1、将下表数据输入到表student中；

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

 

insert into student(Sno,Sname,Ssex,Sage,Sdept)
values(9512101,'李勇' ,'M',49,'计算机系')

insert into student(Sno,Sname,Ssex,Sage,Sdept)
values(9512102,'刘晨' ,'M',20,'计算机系')

insert into student(Sno,Sname,Ssex,Sage,Sdept)
values(9512103,'王敏' ,'F',20,'计算机系')

insert into student(Sno,Sname,Ssex,Sage,Sdept)
values(9521101,'张立' ,'M',22,'信息系')

insert into student(Sno,Sname,Ssex,Sage,Sdept)
values(9521102,'吴宾' ,'F',21,'信息系')

insert into student(Sno,Sname,Ssex,Sage,Sdept)
values(9521103,'张海' ,'M',20,'信息系')

insert into student(Sno,Sname,Ssex,Sage,Sdept)
values(9531101,'钱小平' ,'F',18,'数学系')

insert into student(Sno,Sname,Ssex,Sage,Sdept)
values(9531102,'王大力' ,'M',19,'数学系')

 

 

2、将下表数据输入到表course中；

cno	cname	cpno	Credit	Semester
c01	计算机文化学		3	1
c02	VB	c01	2	3
c03	计算机网络		4	7
c04	数据库基础	c01	6	6
c05	高等数学		8	2
c06	数据结构	c05	5	4
c07	网络应用	c03	4	8
c08	Web数据库	c04	4	7

 

insert into course(Cno,Cname,Cpno,credit,semester)
values('c01','计算机文化学' ,null,3,1)

insert into course(Cno,Cname,Cpno,credit,semester)
values('c02','VB' ,'c01',2,3)

insert into course(Cno,Cname,Cpno,credit,semester)
values('c03','计算机网络' ,null,4,7)

insert into course(Cno,Cname,Cpno,credit,semester)
values('c04','数据库基础' ,'c01',6,6)

insert into course(Cno,Cname,Cpno,credit,semester)
values('c05','高等数学' ,null,8,2)

insert into course(Cno,Cname,Cpno,credit,semester)
values('c06','数据结构' ,'c05',5,4)

insert into course(Cno,Cname,Cpno,credit,semester)
values('c07','网络应用' ,'c03',4,8)

insert into course(Cno,Cname,Cpno,credit,semester)
values('c08','Web数据库' ,'c04',4,7)

3、将下表数据输入到表sc中；

.	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	必修

insert into sc(Sno,Cno,grade,xklb)
values(9512101,'c01',90,'必修')

insert into sc(Sno,Cno,grade,xklb)
values(9512101,'c02',86,'选修')

insert into sc(Sno,Cno,grade,xklb)
values(9512101,'c06',null,'必修')

insert into sc(Sno,Cno,grade,xklb)
values(9512102,'c02',78,'选修')

insert into sc(Sno,Cno,grade,xklb)
values(9512102,'c04',66,'必修')

insert into sc(Sno,Cno,grade,xklb)
values(9521102,'c01',82,'选修')

insert into sc(Sno,Cno,grade,xklb)
values(9521102,'c02',75,'选修')

insert into sc(Sno,Cno,grade,xklb)
values(9521102,'c04',92,'必修')

insert into sc(Sno,Cno,grade,xklb)
values(9521102,'c05',50,'必修')
insert into sc(Sno,Cno,grade,xklb)
values(9521103,'c02',68,'选修')

insert into sc(Sno,Cno,grade,xklb)
values(9521103,'c06',null,'必修')

insert into sc(Sno,Cno,grade,xklb)
values(9531101,'c01',80,'选修')

insert into sc(Sno,Cno,grade,xklb)
values(9531101,'c05',95,'必修')

insert into sc(Sno,Cno,grade,xklb)
values(9531102,'c05',85,'必修')

Day1104更新

五、仔细阅读，完成如下查询

计算机系哪些同学选了课程

1）、第一种思考方法

A、  计算机系同学的学号  select sno from student where sdept=’计算机系’

B、  这些学号中选了课的学号 select distinct sno from sc where sno in (A)

C、  整合起来的语句就是：select distinct sno from sc where sno in

               ( select sno from student where sdept=’计算机系’ )

2）、第二种思考方法

A、所有选了课同学的学号  select sno from sc

B、这些学号中是计算机系的同学 select sno from student where sdept=’计算机系’ and  sno in (A)

C、整合起来的语句就是：select sno from student where sdept=’计算机系’ and  sno in (select sno from sc )

 

1、  查询没有选课的同学；

2、  查询选修了‘VB’课程的同学；

3、  查询‘信息系’选修了‘VB’课程的同学；

4、  查询‘信息系’的同学选修了哪些课程；

5、  查询哪些系的同学选修了‘高等数学’；

6、  查询‘女’同学选修了哪些课程；

7、  查询男生哪些同学选修了‘VB’；

8、  查询选修了‘数据库基础’的学生的姓名、所在系；

9、  选修了第6学期开始的课程的学生的学号、姓名和所在系；

10、             查询男生所修的课程的课程名。

 

select distinct sno from sc where sno in 
               ( select sno from student where sdept='计算机系' )

select sno from student where sdept='计算机系' and  sno in (select sno from sc )

/*1、	查询没有选课的同学；*/
select Sno from student where Sno not in(select Sno from sc)

/*2、	查询选修了‘VB’课程的同学；*/
select Sno from student where Sno in (select Sno from sc where Cno in (select Cno from course where Cname='VB'))

/*3、	查询‘信息系’选修了‘VB’课程的同学；*/
select Sno from student where Sdept='信息系'
and Sno in(select Sno from student where Sno in (select Sno from sc where Cno in (select Cno from course where Cname='VB')))

/*4、	查询‘信息系’的同学选修了哪些课程；*/
select Cno from sc where Sno in (select Sno from student where Sdept='信息系')

/*5、	查询哪些系的同学选修了‘高等数学’；*/

select Sdept from student where Sno in (select Sno from  sc where Cno in(select Cno from course where Cname='高等数学') )

/*6、	查询‘女’同学选修了哪些课程；*/
select Cname from course where Cno in(Select Cno from sc where Sno in(select Sno from student where Ssex='F' ))

/*7、	查询男生哪些同学选修了‘VB’；*/
select Sname from student where Ssex='M' and Sno in (select Sno from sc where Cno in(select Cno from course where Cname='VB'))

/*8、	查询选修了‘数据库基础’的学生的姓名、所在系；*/
select Sname,Sdept from student where Sno in(select Sno from sc where Cno in(select Cno from course where Cname='数据库基础'))

/*9、	选修了第6学期开始的课程的学生的学号、姓名和所在系；*/
select Sno,Sname,Sdept from student where Sno in (select Sno from sc where Cno in(select Cno from course where semester=6))

/*10、	查询男生所修的课程的课程名。*/
select Cname from course where Cno in(Select Cno from sc where Sno in(select Sno from student where Ssex='M' ))

四、完成如下查询
（1） 查询计算机系没有选课的学生的姓名和年龄。
（2） 统计‘VB’课程的考试最高分、最低分和平均分。
（3） 统计‘数据库’课程的选课人数。
（4） 统计计算机系学生’vb’课程考试的最高分、最低分、平均分。 
（5） 统计每个系学生’vb’课程考试的最高分、最低分、平均分，列出系名和三个分数。

/*1.查询计算机系没有选课的学生的姓名和年龄。*/
select Sname,Sage from student
 where Sdept='计算机系' and Sno not in(select Sno from sc)

/*2.统计‘VB’课程的考试最高分、最低分和平均分。*/
select max(grade) as Max,min(grade)as Min,avg(grade)as average 
from sc where Cno in(select Cno from course where Cname='VB')

/*（3）	统计‘数据库’课程的选课人数。*/
select count(distinct Sno) from sc 
where Cno in(select Cno from course where Cname='VB') 

/*（4）	统计计算机系学生’vb’课程考试的最高分、最低分、平均分。  */
select max(grade) as Max,min(grade)as Min,avg(grade)as average
from sc where Cno in(select Cno from course where Cname='VB')
and Sno in(select Sno from student where Sdept='计算机系')

/*（5）	统计每个系学生’vb’课程考试的最高分、最低分、平均分，列出系名和三个分数。*/
select max(sc.grade) as Max,min(sc.grade)as Min,avg(sc.grade)as average,student.Sdept
from sc,student where Cno in(select Cno from course where Cname='VB')
group by student.Sdept

 

*/

/*
（6）	查询每个学生的修课总学分，并进行如下处理：如果总学分高于10分，则显示‘好学生’；
	如果总学分在6到10分间，则显示‘一般学生’；如果总学分低于6分，则显示‘不好学生’。
*/


select Sno,
case
when sum(credit)>10 then '好学生'
when sum(credit) between 6 and 10 then '一般学生'
else '不好学生'
end 
级别
from sc 
join course on
sc.Cno=course.Cno
Group by Sno;

/*
7）	统计每个系每个学生的修课门数和平均成绩，如果修课门数大于3并且平均成绩高于90的显示‘优秀’；
	如果修课门数大于3并且平均成绩在80～90，则显示‘较好’；
	对于修课门数小于等于3的并且平均成绩高于90的显示‘较好’；
	如果修课门数小于等于3并且平均成绩在80～90，则显示‘一般’；
	其他情况显示为‘应努力’。
	列出系名、学号、原修课门数和平均成绩以及处理后的显示结果。
*/

select Sno,
case 
when count(Cno)>3 and avg(grade)>90 then '优秀'
when count(Cno)>3 and avg(grade) between 80 and 90 then '较好'
when count(Cno)<=3 and avg(grade)>90 then '较好'
when count(Cno)<=3 and avg(grade)between 80 and 90 then '一般'
else '应努力'
end
级别
from sc
group by Sno