数据库原理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 and cname in (select cname from C where cname=数据库)7 把选修了“C3”号课程，且成绩低于该门课程的平均成绩的学生成绩提高5%； update SC set grate=grate*1.05 where grate (select avg(grate) from SC where cno=3) and cno=38 把选修了“C2”号课程，且成绩低于该门课程的平均成绩的学生成绩删除掉；delete from SC where grate (select avg(grate) from SC where cno=2) and cno=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 查询每门课程的课程名； select cno,cname from C11 查询无先修课的课程的课程名和学时数； select cname,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 统计选修每门课程的学生人数及各门课程的平均成绩； select cno,COUNT(sno),AVG(grate) from SC group by cno15 查询选修了“1”或“2”号课程的学生学号和姓名； select S.sno,sname from S,SC where S.sno=SC.sno and cno in (1,2)16 查询选修了“1”和“2”号课程的学生学号和姓名； select S.sno,sname from S,SC where S.sno=SC.sno and cno=1 and SC.sno in(select sno from SC where cno=2)17 查询选修了“数据库系统”且成绩在60分以下的学生的学号、姓名和成绩； select S.sno,sname, SC.grate from S,C,SC where S.sno=SC.sno and SC.cno=C.cno and cname=数据库and SC.grate321 查询选修课程成绩至少有一门在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=s1P127（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.s