数据库实验文档.docx
《数据库实验文档.docx》由会员分享,可在线阅读,更多相关《数据库实验文档.docx(19页珍藏版)》请在冰豆网上搜索。
数据库实验文档
第四次试验
1.
insert
intostudent(sno,sname,ssex,sage,sdept)
values('200515001','赵菁菁','女',23,'CS'),
('200515002','李勇','男',20,'CS');
2.
insert
intosc(sno,cno,grade)
values('200515001','1',75),
('200515002','1',85);
3.
insert
intocourse(cno,cname,cpno,ccredit)
values('1','数据库','5',4),
('2','数学',NULL,2);
4.
updatestudent
setsname='李咏'
wheresdept='CS'andsname='李勇';
5.
updatecourse
setccredit=3
wherecname='数据处理';
6.
updatesc
setgrade+=5
wherecno='1';
7.
updatesc
setgrade+=5
wherecno=(selectcno
fromcourse
wherecname='大学英语');
8.
updatestudent
setsname='王丹丹',ssex='女',sage=20,sdept='MA'
wheresno='200515010';
9.
updatelendingbook
setsname='王婧婧',sdept='MA',counts+=5
wheresno='200515001';
10.
delete
fromstudent
wheresdeptisnull;
11.
delete
fromstudent
wheressex='男'andsage>25;
12.
delete
fromcourse
whereccredit<1;
第五次试验
1.第一种方法:
select*
fromstudent
wheresnamenotlike'刘%';
第二种方法:
select*
fromstudent
wherenotSUBSTRING(sname,1,1)='刘';
2.
select*
fromstudent
wheresnamelike'沈__';
问题是会把两个
字的姓名也显示出来
3.
select*
fromstudent
wheresage<2005-1985;
4.
select
casewhenssex='男'then'男生'
whenssex='女'then'女生'
whenssexnotin('男','女')then'条件不明'
end
as性别,sno学号,sname姓名,sage年龄,sdept院系
fromstudent;
问题是不能解决NULL问题从而导致NULL的显示还是NULL
另一种办法就是这样解决的,新发现的
select
casewhenssex='男'then'男生'
whenssex='女'then'女生'
else'条件不明'
end
as性别,sno学号,sname姓名,sage年龄,sdept院系
fromstudent;
这种办法完全解决了问题,好办法!
5.
select*
fromcourse
wherecnamelike'%数据%';
6.
selectsno学号,sname姓名,ssex性别,sage年龄,sdept院系
fromstudent
wheresnolike'_______[1-4,9]%'orsnolike'________[1-4,9]%'
;
此地方不能用in来解决只能用like和or组合解决
7.
select*
fromsc
wherecno='1'
orderbygradedesc;
8.
selectsno
fromsc
wherecno='1'andsnoin(selectsno
fromsc
wherecno='2');
9.
select*
fromcourse
orderbycpnoasc;
10.
select*
fromstudent
wheresage>(selectavg(sage)
fromstudent)
orderbysagedesc;
11.
selectsno学号,sname姓名,ssexas性别,2005-sage出生年份,sdept院系
fromstudent
orderby出生年份asc;
12.
select
casewhensdept='CS'then'计算机系'
whensdept='IS'then'信息系'
whensdept='MA'then'数学系'
whensdept='EN'then'外语系'
whensdept='CM'then'中医系'
whensdept='WM'then'西医系'
else'院系不明'
end
as院系,sno学号,sname姓名,ssex性别,sage年龄
fromstudent
orderbysdeptdesc;
13.
selectdistinctsdept院系名称,院系规模=(
casewhencount(sno)>=5then'规模很大'
whencount(sno)between4and5then'规模一般'
whencount(sno)between2and4then'规模稍小'
else'规模很小'
end)
fromstudent
wheresdeptisnotnull
groupbysdept
;
14.此题有矛盾,有两种理解:
一种是:
selectsno,cno,grade
fromsc
wheregradebetween70and80
orderbycnodesc,gradedesc;
另一种是:
selectsno,cno,grade
fromsc
wheregradebetween70and80
orderbycno,gradedesc;
15.
selectcount(sno)学生总人数,avg(sage)平均年龄
fromstudent;
16.
selectsno,count(cno)选修课程数
fromsc
groupbysno
havingcount(cno)>3;
17.
selectcount(cno)总人数,max(grade)最高成绩,min(grade)最低成绩,avg(grade)平均成绩
fromsc
groupbycno
orderbycnodesc;
若想验证一下可以在添加一个cno即可!
18.
selectsno,avg(grade)平均成绩
fromsc
groupbysno
havingavg(grade)>(selectavg(grade)
fromsc
wheresno='200515001');
19.
第一种用的不是单表查询(只是为了更符合逻辑):
selecto课程号,ame课程名,x.及格人数,x.及格人数*100/y.总人数及格比率
from(selectcno,COUNT(cno)及格人数
fromsc
wheregrade>=60
groupbycno
)x,(selectcno,COUNT(cno)总人数
fromsc
groupbycno
)y,coursez
whereo=o
ando=o;
第二种是:
selecto课程号,x.及格人数,x.及格人数*100/y.总人数及格比率
from(selectcno,COUNT(cno)及格人数
fromsc
wheregrade>=60
groupbycno
)x,(selectcno,COUNT(cno)总人数
fromsc
groupbycno
)y
whereo=o
;
由于投影关系前两种都会丢失及格率为0的课程于是写出了第三种完美代码:
第三种是(完美):
selectcno课程号,SUM(casewhengrade>=60then1else0end)及格人数,SUM(casewhengrade>=60then1else0end)*100/COUNT(*)及格比率
fromsc
groupbycno;
20.
selectsno
fromsc
groupbysno
havingcount(cno)>=all(selectcount(cno)fromscgroupbysno)
orcount(cno)<=all(selectcount(cno)fromscgroupbysno);
此题出得就存在问题,目标不明确,感觉问的没一点意义!
要是再能完善点就更好啦!
21.
selectsdeptas院系名称,sum(casewhenssex='男'then1else0end)男生人数,sum(casewhenssex='女'then1else0end)女生人数
fromstudent
groupbysdept;
22
selectsno,avg(grade)
fromsc
wheresnoin(selectsno
fromsc
wheregrade<60
groupbysno
havingcount(*)>=2)
groupbysno
;
第六次实验
1.
select*
fromcourse
wherecnamelike'DB_%s__';
2.
selectsname姓名,student.sno学号,o课程号,cname课程名
fromstudent,sc,course
wheresnamelike'_阳%'
andstudent.sno=sc.sno
ando=o;
3.
selectstudent.sno学号,sname姓名,sdept院系,o课程号,grade成绩
fromstudent,sc,course
wherecnamein('数学','大学英语')
andstudent.sno=sc.sno
ando=o;
4.
selectstudent.sno学号,sname姓名,sage年龄,ssex性别,sdept院系,o课程号,cname课程名,cpno先修课,ccredit学分,grade成绩
fromstudent,sc,course
wheregradeisnull
andstudent.sno=sc.sno
andsc.Cno=o;
5.
selectsno,sname,ssex,sage,sdept
fromstudentx
wheresage!
=(selectsage
fromstudenty
wheresname='张力');
此处X,y可以不用!
6.
selectsc.sno学号,sname姓名,avg(grade)平均成绩
fromstudent,sc
wheresc.sno=student.sno
groupbysc.sno,sname
havingavg(grade)>(selectavg(grade)
fromstudent,sc
wheresname='张力'
groupbysc.sno,student.sno
havingsc.sno=student.sno)
;
7.
第一种方法是(缺陷是不符合逻辑思维):
selectstudent.sno学号,sname姓名,sdept院系,sum(ccredit)已修学分
fromstudent,sc,course
wheregrade>=60andsc.sno=student.snoando=o
groupbystudent.sno,sname,sdept;
第二种方法是(符合思维逻辑):
selectx.sno学号,sname姓名,sdept院系,y.已修学分
fromstudentx,(selectsno,SUM(ccredit)as已修学分
fromsc,course
whereo=oandgrade>=60
groupbysno
)y
wherex.sno=y.sno
;
8.
selectsc.sno学号,sname姓名,sdept院系,grade成绩
fromstudent,sc
wheresc.sno=student.snoand
sc.snoin(selectsno
fromsc
groupbysno
havingcount(sno)=1)
;
9.不去张力的程序如下:
selectsc.sno学号,sname姓名,sc.Cno课程号
fromstudent,sc
wheresc.sno=student.snoandsc.Cnoin(selectCno
fromsc
wheresno=(selectsno
fromstudent
wheresname='张力'))
;
去掉张力的程序如下:
selectsc.sno学号,sname姓名,sc.Cno课程号
fromstudent,sc
wheresc.sno=student.snoandsc.Cnoin(selectCno
fromsc
wheresno=(selectsno
fromstudent
wheresname='张力'))andstudent.sno!
=(selectsno
fromstudent
wheresname='张力')
;
10.
第一种是思路很清晰的投影算法(就是代码有点长):
select*
fromstudent
wheresnoin(selectsno
fromsc
wheresnoin(selectsno
fromsc
wherecno=(selectcno
fromcourse
wherecname='数据库')
intersect
selectsno
fromsc
wherecno=(selectcno
fromcourse
wherecname='数据结构')
)
groupbysno
havingcount(cno)=2
);
第二种:
select*
fromstudent
wheresnoin(selectsno
fromsc
wheresnoin(selectsno
fromsc
wherecno=(selectcno
fromcourse
wherecname='数据库')
andsnoin(
selectsno
fromsc
wherecno=(selectcno
fromcourse
wherecname='数据结构'))
)
groupbysno
havingcount(cno)=2
);
11.此题不好之处是基本信息不明确,要是能再添加一些课程名信息就更好啦!
没有课程名的代码:
select*
fromstudent
wheresnoin(selectsno
fromsc
wherecnoin(selectcno
fromcourse
wherecname='数据库'orcname='数据结构'))
;
有课程名的代码为:
由于加上了课程名所以会有重复学号选不同的课程,这是一个弊端,不能解决
selectstudent.sno,sname,ssex,sage,sdept,cname
fromstudent,sc,course
wherestudent.sno=sc.sno
ando=o
andoin(selectcno
fromcourse
wherecnamein('数据库','数据结构'))
;
12.
selecto,cname,sc.sno,sname,grade
fromstudent,sc,course
wherestudent.sno=sc.sno
ando=o;
13.
selectcno,cname
fromcourse
wherecnoin(selectcno
fromsc
groupbycno
havingcount(cno)=1)
;
14.
此题说的包含是有疑点!
一个解释是:
选的课程范围比张向东选的课大于等于;另一个是:
也就是说含有他的一部分课程;此处只写出了后者的情况:
selectsno学号,sname姓名
fromstudent
wheresnoin(selectsno
fromsc
wherecnoin(selectcno
fromsc
wheresno=(selectsno
fromstudent
wheresname='张向东')))
;
15.
selectsno学号,sname姓名
fromstudent
wheresnoin(selectsno
fromsc
wherecno=(selectcno
fromcourse
wherecname='数据结构'))
;
16.
selectsname姓名,sage年龄,sdept院系
fromstudent
wheresagefromstudent
wheresdept='CS')
andsdept!
='CS'
;
17.
用any代码:
此处用any纯是用来表示sdept!
=’CS’这个条件!
这就是any的代码:
select*
fromstudent
wheresage=any(selectsage
fromstudent
wheresdept!
='CS')
andsage<(selectMin(sage)
fromstudent
wheresdept='CS')
;
用all代码:
select*
fromstudent
wheresagefromstudent
wheresdept='CS')
andsdept!
='CS';
18.
连接查询:
select*
fromstudentx,studenty
wherex.sdept=y.sdeptandx.sno=y.snoand
y.sdept=(selectsdept
fromstudent
wheresname='张力');
嵌套查询:
select*
fromstudent
wheresdept=(selectsdept
fromstudent
wheresname='张力');
19.此题有两种理解:
第一种是并集
第一种代码:
select*
fromstudent
wheresdept='CS'
union
select*
fromstudent
wheressex='女';
第二种代码:
select*
fromstudent
wheresdept='CS'orssex='女';
第二种是交集:
第一种代码:
select*
fromstudent
wheresdept='CS'
intersect
select*
fromstudent
wheressex='女';
第二种代码:
select*
fromstudent
wheresdept='CS'andssex='女';
20.
交集代码:
第一种代码:
select*
fromstudent
wheresdept='CS'
intersect
select*
fromstudent
wheresage<=19;
第二种代码:
select*
fromstudent
wheresdept='CS'andsage<=19;
差集代码:
第一种代码:
select*
fromstudent
wheresdept='CS'
except
select*
fromstudent
wheresage<=19;
第二种代码:
select*
fromstudent
wheresdept='CS'andsage>19;
21.
第一种代码:
selectsno
fromsc
wherecno='1'
intersect
selectsno
fromsc
wherecno='2';
第二种代码:
selectsno
fromsc
wherecno='1'andsnoin(selectsno
fromsc
wherecno='2');
22.
select
sno学号,
stuff((select''+cname
from(selectsno,cname
fromsc,course
whereo=o
groupbysno,cname)asx
wheresno=y.snoforxmlpath('')),1,1,'')选修课程
from(selectsno,cname
fromsc,course
whereo=o
groupbysno,cname)y
groupbysno;