数据库原理SQL语句查询练习

姓名 龚雨 学号 1305170125 班级 计本1301 年级 2013级 指导教师 张志刚 财经学院信息学院 数据库原理 实验报告实验名称 数据库对象的建立与数据更新查询 实验室 实验日期 一、实验目的及要求1、掌握数据库对象的设计及建立；2、针对给定的实际问题，完成数据库对象的设计，包括各表的结构（属性名、类型、约束等）及表之间的关系，在选定的DBMS上建立数据库表，用SQL命令和可视化环境（可选）分别建立数据库表，体会两种方式的特点；3、熟悉和掌握数据表中数据的插入、修改、删除操作和命令的使用；加深理解表的定义对数据更新的作用；4、对已经建立的数据库进行修改、删除、插入等操作；5、掌握关系数据库的各种查询方法，理解其含义，包括嵌套查询、连接查询等；6、对关系数据库进行查询，包括嵌套查询、连接查询；二、实验环境硬件平台：PC；软件平台：Windows 2000 / MICROSOFT SQLSERVER；三、实验容对象的设计与建立用SQL命令和可视化工具（可选）完成以下操作：1. 创建学生-课程数据库:create database xueshengkecheng12创建学生关系表S，其中Sno是主码学号性别年龄所在系SnoSnameSsexSageSdept create table S(sno varchar(30) primary key,sname varchar(30),ssex varchar(30),sage varchar(30),sdept varchar(30);3创建课程关系表C，其中Cno是主码，Cpno是外码：课程号课程名先行课学分CnoCnameCpnoccreditcreate table C(cno varchar(50) primary key,cname varchar(50),cpno varchar(50),ccredit varchar(50),foreign key (cpno) references C(cno);4创建课程关系表SC，其中(Sno,Cno)是主码，Sno和Cno分别是外码：学号课程号成绩SnoCnoGradecreate table SC(sno varchar(30),cno varchar(50),grate varchar(30),primary key (sno,cno),foreign key(sno)references S(sno),foreign key (cno) references C(cno);5在表S上增加“出生日期”属性列； alter table S add birth varchar(30);6删除表S的“年龄”属性列；alter table S drop column sage;7在表S上，按“Sno”属性列的unique方式建立索引；create unique index Ssno on S(sno);8删除表SC；drop table SC cascade;数据更新：对已建好的各表输入适当的数据并练习数据的删除和修改，注意检测键码约束、外码约束、非空约束、唯一约束、取值约束等各种约束在输入数据及数据的删除、修改时的作用，结合课堂教学体会其含义。具体容：1 将示例数据分别插入表S、C、SC；insert into S values('13051701','夏雨','女','计算机','1995.1.1',20)insert into S values('13051702','皇','男','软件','1996.11.12',19)insert into S values('13051703','王五','男','计算机','1995.8.4',20)insert into S values('13051704','茜','女','计科','1994.3.3',21)insert into S values('13051705','钱呈','男','计科','1993.4.7',22)insert into C values(1,'数据库',null,4)insert into C values(2,'离散',1,3)insert into C values(3,'操作系统',2,4)insert into C values(4,'程序设计',1,5)insert into C values(5,'汇编',4,4)insert into SC values(13051701,2,80)insert into SC values(13051701,4,90)insert into SC values(13051702,3,85)insert into SC values(13051702,5,96)insert into SC values(13051703,1,88)2 在SC中插入某学生的选课信息（如：学号为“S1”，课程号为“c1”，成绩待定）； insert into SC values(13051703,3,null)3 将“CS”系全体学生的成绩置零； update SC set grate=0 where '计算机'=(select sdept from S where S.sno=SC.sno)4 将学号为“S1”的学生的学号修改为“S001” Delete from SC where sno=13051705 delete from S where sno=13051705;insert into S values(1305170005,'钱呈','男','计科','1993.4.7',22)5 把平均成绩大于80分的男生学号和平均成绩存入表SGRADE（SNO，AVGGRADE）； create table SGRATE (sno varchar(30), avggrate float); insert into SGRATE(sno,avggrate) select sno,avg(grate) from SC group by sno having avg(grate)>806 把选修了课程名为“数据结构”的学生的成绩提高10%； update SC set grate=grate*1.1 where grate in (select SC.grate from SC,C where SC.cno=C.cno andame in (selectame from C whereame='数据库')7 把选修了“C3”号课程，且成绩低于该门课程的平均成绩的学生成绩提高5%； update SC set grate=grate*1.05 where grate< (select avg(grate) from SC whereo=3) ando=38 把选修了“C2”号课程，且成绩低于该门课程的平均成绩的学生成绩删除掉；delete from SC where grate< (select avg(grate) from SC whereo=2) ando=29 删除“CS”系全体学生的选课记录； delete from SC where 计算机=(selete sdept from S where S.sno=SC.sno)10 删除学号为“S2”的相关信息；delete from SC where sno=13051701delete from S where sno=1305170111将表S、C、SC中的数据全部删除； Delete from SC; Delete from S; Delete from C;数据查询：在已做过的实验基础上，练习查询语句的使用，包括简单查询、嵌套查询、连接查询、分组查询、排序查询、聚合查询等；练习，体会各种查询的异同及相互之间的转换，体会各种查询的执行过程，为简单综合应用打下良好的基础。具体容，在表S，C，SC上完成以下查询：1 查询学生的基本信息； SELECT * from S2 查询“CS”系学生的基本信息； select * from S where sdept ='计算机'3 查询“CS”系学生年龄不在19到21之间的学生的学号、； select sno ,sname from S where sdept='计算机' and sage not between 19 and 214 找出所有学生中的最大年龄； select MAX(sage) from S 5 找出“CS”系年龄最大的学生，显示其学号、； select sno, sname from S where sdept='计算机' and sage in ( select MAX(sage) from S where sdept='计算机')6 找出各系年龄最大的学生，显示其学号、； select sno,sname ,sdept ,sage from S x where sage=( select max(sage) from S y where y.sdept=x.sdept )7 统计“CS”系学生的人数； select COUNT(sno) from S where(sdept='计算机')8 统计各系学生的人数，结果按升序排列； select sdept, COUNT(sno) from S group by sdept order by COUNT(sno) asc9 按系统计各系学生的平均年龄，结果按降序排列； select sdept,avg(sage) from S group by sdept order by avg(sage) desc10 查询每门课程的课程名； selecto,cname from C11 查询无先修课的课程的课程名和学时数； selectame,ccredit from C where cpno is null12 统计无先修课的课程的学时总数； Select sum(ccredit) from C where cpno is null 13 统计每位学生选修课程的门数、学分及其平均成绩； select SC.sno ,COUNT(SC.cno),avg(SC.grate),sum(C.ccredit)from SC,C where SC.cno=C.cno group by SC.sno14 统计选修每门课程的学生人数及各门课程的平均成绩； selecto,COUNT(sno),AVG(grate) from SC group byo15 查询选修了“1”或“2”号课程的学生学号和； select S.sno,sname from S,SC where S.sno=SC.sno ando in (1,2)16 查询选修了“1”和“2”号课程的学生学号和； select S.sno,sname from S,SC where S.sno=SC.sno ando=1 and SC.sno in(select sno from SC whereo=2)17 查询选修了“数据库系统”且成绩在60分以下的学生的学号、和成绩； select S.sno,sname, SC.grate from S,C,SC where S.sno=SC.sno and SC.cno=C.cno andame='数据库'and SC.grate<6018 查询每位选修了课程的学生信息（显示：学号，课程号，课程名，成绩）； select S.sno,sname,C.cno,cname,grate from S,C,SC where S.sno=SC.sno and SC.cno=C.cno19 查询没有选修课程的学生的基本信息；select * from S where not exists(select * from SC where S.sno=SC.sno)20 查询选修了3门以上课程的学生学号； select sno from SC group by sno having COUNT(cno)>321 查询选修课程成绩至少有一门在80分以上的学生学号； select distinct sno from SC where grate >8022 查询选修课程成绩均在80分以上的学生学号； select sno from SC group by sno having min(grate)>8023 查询选修课程平均成绩在80分以上的学生学号；select sno from SC group by sno having AVG(grate)>80 针对SPJ数据库中的四个表S，P，J，SPJ，完成教材P74-75-习题5中的查询及教材P127-习题5中的查询（可选）。P75： (1)、select sno from SPJ where jno='j1' （2）、select sno from SPJ where jno='j1' and pno='p1' （3）、select sno from SPJ,P where P.pno=SPJ.pno and jno='j1' and P.pno in (select pno from P where color='红' ) （4）、select jno from SPJ,S,P where S.sno=SPJ.sno and SPJ.pno=P.pno and P.pno in(select P.pno from P where color='红') and S.sno in(select S.sno from S where city <>'天津') :（5）、select jno from SPJ where sno='s1'P127（1）、select sname,city from S（2）、select pname,color,weight from P（3） 、select jno from SPJ where sno='s1' （4） 、select pname,qty from SPJ,P where SPJ.pno=P.pno and SPJ.jno='j2'(5) 、select pno from SPJ,S where S.sno=SPJ.sno and S.sno in(select S.sno from S where S.city='' )（6）、select jname from SPJ ,S ,J where S.sno=SPJ.sno and SPJ.jno=J.jno and S.sno in(select S.sno from S where S.city='' )（7）、select distinct SPJ.jno from SPJ ,S ,J where S.sno=SPJ.sno and SPJ.jno=J.jno and S.sno not in(select S.sno from S where S.city='天津' )（8）、update P set color='蓝' where color ='红'（9）、delete from SPJ where sno='s5' and jno='j4' and pno='p6'insert into SPJ values('s3','p6','j4',500)（10）、delete from SPJ where sno ='s2' delete from S where sno='s2'（11）、insert into S values('s2','盛锡',10,'')insert into SPJ values('s2','p4','j6',200)四、实验过程及结果（记录程序执行及调试过程中的问题及解决方法，及实验结果）五、总结（可选）（对上机实践结果进行分析，问题回答，上机的心得体会及改进意见） 在实验中，最开始必须要依赖书本，但随着实验容的深入，渐渐可以脱离课本。不可否认，书上的理论知识指导着实践，但也要记住实践和理论有一定的偏差，所以，要相结合才能做得越来越好。