实验交互式SQL实验报告

精品文档试验一 交互式SQL一 实验目的1熟悉数据库的交互式SQL工具。2熟悉通过SQL对数据库进行操作。3完成作业的上机练习。二 实验工具SQL Server 2005利用SQL Server 2005及其交互式查询来熟悉SQL语句。三 实验内容和要求1在SQL Server 2005中建立一个数据库，进行实验所要求的各种操作，所有的SQL操作均在建立的新库里进行；2根据以下要求认真填写实验报告，记录所有的实验用例的SQL语言；3.1 数据定义（1）熟悉基本表的创建、修改及删除。（2）熟悉索引的创建和删除。3.2 数据操作（1）完成各类查询操作（单表查询，连接查询，嵌套查询）等；（2）完成各类更新操作（插入数据，修改数据，删除数据）；3.3 视图的操作视图的定义（创建和删除），查询，更新（注意更新的条件）。3.4 具体操作内容在MS SQL Server中创建学生课程数据库，要求有学生表（Student）、课程表(Course)和选课表（SC），向三个表中分别插入相关数据，再用SQL语句完成一下要求的查询。1.使用SQL语言创建下面的三个表。表一：学生信息表（Student）列名说明数据类型约束Sno学号字符串，长度为10非空Sname姓名字符串，长度为20非空Ssex性别字符串，长度为10非空Sage年龄字符串，长度为10非空Sdept所在系字符串，长度为10非空表二：课程信息表（Course）列 名说 明数据类型约 束Cno课程号整形非空Cname课程名字符串，长度为20非空Cpno先行课整型允许为空Ccredit学分整型非空表三：学生选课信息表（SC）列名说明数据类型约束Sno学号字符串，长度为10主码，引用Student的外码Cno课程号整型主码，引用Course的外码Grade成绩字符串，长度为10 取值01002.在以上的三个表中，分别插入下面的数据；表一：学生信息表（Student）SnoSnameSsexSageSdept200215121李勇男20CS200215122刘晨女19CS200215123王敏立男19IS表二：课程信息表（Course）CnoCnameCpnoCcredit 1数据库542数学23信息系统144操作系统635数据结构746数据处理27PASCAl语言64表三：学生选课信息表（SC）SnoCnoGrade2002151211922002151212852002151213882002151222902002151223803.根据上面的三种表，写出完成如下查询功能的SQL语句：(1)查询全体学生的姓名、出生年份和所在的院系，要求用小写字母表示所有系名(2)查询选修了课程的学生学号(3)查询年龄在2023岁（包括20岁和23岁）之间的学生的姓名、系别、年龄(4) 查询计算机科学系（CS）、数学系（MA）和信息系（IS）学生的姓名和性别(5) 查询以“DB_”开头，且倒数第3个字符为i的课程的详细情况(6) 查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列(7) 查询选修1号课程的学生最高分数、最低分数、平均分数(8) 查询选修了3门课程的学生学号(9) 查询每一门课的间接先修课（即先修课的先修课）(10) 查询选修2号课程且成绩大于等于90分的所有学生(11) 查询与刘晨在同一个系学习的学生(12) 查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄(13) 将一个新学生元组（学号：200215128；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入到Student表中(14) 将学生200215121的年龄改为22岁(15) 删除学号为200215128的学生记录(16) 建立信息系学生的视图(17) 在信息系学生的视图中找出年龄小于20岁的学生(18) 将信息系学生视图is_Student中学号为200215122的学生姓名改为“刘辰”四 实验报告4.1 实验环境：Windows XPMicrosoft SQL server Management Studio 20054.2 实验内容与完成情况：(1) 查询全体学生的姓名、出生年份和所在的院系，要求用小写字母表示所有系名SELECT Sname NAME,Year of Birth: BIRTH,2004-Sage BIRTHDAY,LOWER(Sdept) DEPARTMENT FROM Student;(2)查询选修了课程的学生学号SELECT DISTINCT Sno FROM SC;（3）查询年龄在2023岁（包括20岁和23岁）之间的学生的姓名、系别、年龄SELECT Sname,Sdept,Sage FROM Student WHERE Sage BETWEEN 20 AND 23;（4）查询计算机科学系（CS）、数学系（MA）和信息系（IS）学生的姓名和性别SELECT Sname,Ssex FROM Student WHERE Sdept IN(CS,MA,IS);（5）查询以“DB_”开头，且倒数第3个字符为i的课程的详细情况SELECT * FROM Course WHERE Cname LIKE DB_%_ESCAPE;(6)查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列SELECT * FROM Student ORDER BY Sdept,Sage DESC;(7)查询选修1号课程的学生最高分数、最低分数、平均分数SELECT MAX(Grade) MAX,MIN(Grade) MIN,AVG(Grade) AVG FROM SC WHERE Cno=1;(8)查询选修了3门课程的学生学号SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*)3;(8) )查询每个学生及其选修课程的情况SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM Student,SCWHERE Student.Sno=SC.Sno;(9)查询每一门课的间接先修课（即先修课的先修课）SELECT first.Cno,second.Cpno FROM Course first,Course secondWHERE first.Cpno=second.Cno;查询每一门课的间接先修课（即先修课的先修课）（消除有空的行）SELECT first.Cno,second.Cpno FROM Course first,Course secondWHERE first.Cpno=second.Cno and second.Cpno is not null; (10) 查询选修2号课程且成绩大于等于90分的所有学生select Student.Sno,Sname,Ssex,Sage, Sdept from Student ,SC where Student.Sno=SC.Sno and Cno=2 and Grade=90;select Sno,Sname,Ssex,Sage, Sdept from Student where Sno=(select Sno from Sc where Cno=2 and grade =90);(11) 查询与刘晨在同一个系学习的学生select Sno,Sname,Sdept from Student where Sdept in (select Sdept from Student where Sname=刘晨);select S1.Sno,S1.Sname,S1.sdept from Student S1,student S2 where S1.Sdept=s2.Sdept and S2.Sname=刘晨;select Sno,Sname ,Sdept from Student where Sdept =(select Sdept from student where Sname=刘晨);select Sno,Sname,Sdept from Student S1 where exists (select * from Student S2 where S2.Sdept=S1.Sdept and S2.Sname =刘晨);(12)查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄select Sname ,Sage from Student where Sage all(select Sage from Student where Sdept=CS) and SdeptCS;select Sname ,Sage from Student where Sage (select min(Sage) from Student where Sdept=CS) and SdeptCS;（13）将一个新学生元组（学号：200215128；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入到Student表中insert into Student(Sno,Sname,Ssex,Sdept,Sage)values (200215128,陈冬,男,IS,18);select * from Student;（14）将学生200215121的年龄改为22岁update Student set Sage =22 where Sno=200215121; select * from Student;（15）删除学号为200215128的学生记录delete from Student where Sno=200215128;select * from Student;（16）建立信息系学生的视图create view is_Student as select Sno,Sname,Sage from Student where Sdept=IS;(17)在信息系学生的视图中找出年龄小于20岁的学生select Sno,Sage from is_Student where Sage20 ;（18）将信息系学生视图is_Student中学号为200215122的学生姓名改为“刘辰”update is_Student set Sname=刘辰 where Sno=200215122;select * from is_Student;（19）删除信息系学生视图is_Student中学号为200215125的记录delete from is_Student where Sno=200215125;select * from is_Student;4.3 出现的问题：在写第（9）个查询功能的时候用select first.Cno,second.Cpno from Course first,Course second where first.Cpno=second.Cno;这句SQL语句输出的结果中有的课程没有先修课。4.4 解决方案（列出遇到的问题和解决办法，列出没有解决的问题）：原因：没有排除掉那些没有先修课的课程，所以查询结果中会有空值。 解决方案：这样写就会把没有