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

来源：互联网发布：linux oracle自动启动编辑：程序博客网时间：2024/05/02 04:36

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

李勇

男

计算机系

9512102

刘晨

男

计算机系

9512103

王敏

女

计算机系

9521101

张立

男

信息系

9521102

吴宾

女

信息系

9521103

张海

男

信息系

9531101

钱小平

女

数学系

9531102

王大力

男

数学系

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

计算机文化学

c02

c01

c03

计算机网络

c04

数据库基础

c01

c05

高等数学

c06

数据结构

c05

c07

网络应用

c03

c08

Web数据库

c04

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

必修

9512101

c02

选修

9512101

c06

必修

9512102

c02

选修

9512102

c04

必修

9521102

c01

选修

9521102

c02

选修

9521102

c04

必修

9521102

c05

必修

9521103

c02

选修

9521103

c06

必修

9531101

c01

选修

9531101

c05

必修

9531102

c05

必修

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 averagefrom 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.Sdeptfrom sc,student where Cno in(select Cno from course where Cname='VB')group by student.Sdept

*//*（6）查询每个学生的修课总学分，并进行如下处理：如果总学分高于10分，则显示‘好学生’；如果总学分在6到10分间，则显示‘一般学生’；如果总学分低于6分，则显示‘不好学生’。*/select Sno,casewhen sum(credit)>10 then '好学生'when sum(credit) between 6 and 10 then '一般学生'else '不好学生'end 级别from sc join course onsc.Cno=course.CnoGroup 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 scgroup by Sno