数据库上机报告.doc

_数据库SQL上机实验报告书姓名： 学号： 班级： 实验一：设计数据库、数据表并编程实现建立student表create table Student(sno char(9) Primary key,sname char(10)constraint c1 not null,sbirthday DateTime,ssex char(2)constraint c2 check(ssex in (男,女),sclass char(20),sremark char(100),adress char(40),zipcode char(6),phone char(15),email char(40);建立course表：create table Course(cno char(6)primary key,cname char(20),cpno char(6),ctime Numeric(2),credit Numeric(2),Foreign key(cpno)References Course(cno);建立score表：create table Score(sno char(9),cno char(6),score Numeric(3),Primary key(sno,cno),Foreign key (sno)References Student(sno),Foreign key (cno)References Course(cno);建立Teacher表：create table Teacher(tno char(7)primary key,tname char(10),tsex char(2),tbirthday DateTime,position char(12),department char(16),tamount Numeric(7,2),experience char(200);建立Teachering 表create table Teaching(tno char(7),cno char(6),tdate Datetime,classroom char(10),sclass char(20), Primary key(tno,cno),Foreign key(tno)References Teacher(tno),Foreign key(cno)References Course(cno),) 实验二：设计数据插入、修改、删除、查询和视图等操作并编程实现（1）根据以下给定的部分数据表信息，分别对student, course, score, teacher, teching 表进行数据插入。Student表插入语句：Insert into Student (sno,sname, sbirthday ,ssex,sclass, sremark, adress, zipcode, phone, email)Values(011110101,章海潮,1982.02.07,信管系0101)Course表插入语句：Insert intoCourse (cno, cname, cpno, ctime, credit)Values (C001,数据库原理,C005,4,64, )Score 表插入语句：Insert intoScore (sno,cno,score, )Values (0111101,C001,90)Teacher 表插入语句：Insert intoTeacher (tno,tname,tsex,tbirthday,position,department,tamount,experience )Values (T001, 江承基 ,男 ,信息管理系 ,1964.03.21)Teachering 表插入语句：Insert intoTeachering (tno,cno,tdate,classroom,sclass)Values (T001,C005,2012-01-09, 西二405, 信管系0101 )（2）查询全体学生的详细记录；select *from Student left outer join Score on(Student.sno=Score.sno); （3）查询全体学生的学号与姓名；select sno,snamefrom Student （4）查询全体学生的学号、姓名、所属班级，相同班级列在一起；select sno,sname,sclassfrom Studentorder by sclass DESC;（5）查询全体学生的姓名、出生年份；select sname,sbirthdayfrom Student; （6）查询全体学生的姓名及其年龄；select sname, year(getdate()-year(sbirthday)from Student; （7）查询“信管系0101”班全体学生名单； select snamefrom Studentwhere sclass=信管系0101;（8）查询查询所有年龄在27岁以下的学生姓名及其年龄； select sname,year(getDate()-year(sbirthday)from Studentwhere year(getdate()-year(sbirthday)30（9）查询考试成绩有不及格的学生的学号； select Student.snofrom Student,Scorewhere Student.sno=Score.sno and score15 and year(getdate()-year(sbirthday)15 and year(getdate()-year(sbirthday)60（24）求每门课的平均成绩，并把结果存入average表；create table Average(cno char(6)primary key, average Numeric(3)insert into Average(cno,average)select cno,avg(score)from Scoregroup by cno; （25）将学生“马丽鹃”的出生日期改为“1982.8.20”； update Studentset Sbirthday=1982.08.20where sname=马丽鹃（26）将所有学生的zipcode属性列的值填改为“230009”；update Studentset zipcode=230009 （27）将average表中的所有课程的平均成绩置零； update Averageset average=0（28）删除average表中的课程号为c007的平均成绩记录；delete averagefrom Averagewhere cno=c007 （29）删除所有average表中平均成绩记录； alter table Averagedrop column average（30）建立一个临时学生信息表（tstudent），删除该表中的学号前六位为001011的学生记录。 create table tstudent( sno Char(9) primary key, sname Char(10),sbirthday DateTime,ssex Char(2), sclass Char(20),sremark Char(100),address Char(40), zipcode Char(6),phone Char(15), email Char(40) );delete from tstudentwhere sno like 001011%（31）查找“电商系0101”班年龄在27岁以下的学生学号、姓名；select sno,snamefrom Studentwhere year(getdate()-year(sbirthday)60 and max(score)=2)（41）使用自身连接查询每一门课程的间接先行课（即先行课的先行课）select o,c2.cpnofrom course c1,course c2where c1.cpno=o （42）使用复合条件连接查询选修“c001”号课程且成绩在90分以上的所有同学； select student.sno,snamefrom score,studentwhere cno=c001and student.sno=score.sno and score.score90（43）使用复合条件连接查询每个学生选修的课程名及其成绩； select cno,score,snamefrom score,studentwhere student.sno=score.sno（44）查询选修了全部课程的学生； select snamefrom Studentwhere not exists(select*from Coursewhere not exists(select *from scorewhere sno=Student.sno and cno=o)（45）查询至少选修全部学分数为4个学分的课程的学生的学号、姓名；select student.snofrom student,score,coursewhere student.sno=score.sno and o=o group by student.snohaving sum(credit)=4 （46）查选修C005号课程且成绩至少高于选修C003课程的同学的Sno,按sno从高到低排序；select distinct sx.sno descfrom score sxwhere cno=c001 and sx.scoreall(select sy.scorefrom score sywhere cno=c007) （47）查询选修了课程C001或c007的学生学号、姓名；select student.sno,snamefrom score ,studentwhere cno=c001or cno=c007and student.sno=score.sno （48）查询既选修了课程C001又选修了课程c007的所有学生学号、姓名；select student.sno,snamefrom student,score sx,score sywhere o=c001and o=c007 and student.sno=sx.sno and student.sno=sy.sno （49）查询“会计系0102”班的学生及年龄不大于27岁（现有年龄）的学生；select snamefrom studentwhere sclass=会计系0102 and year(getdate()-year(sbirthday)=27 （50）查询选修了课程名为“数据库原理”的学生的学号、姓名、性别、年龄； select sno,sname,ssex,year(getdate()-year(sbirthday)from studentwhere sclass=数据库原理（51）查询其他班中比“信管系0101”班所有学生年龄都小的学生名单；select sx.snamefrom Student sx where sx.sclass !=信管系0101 and year(getdate()-year(sbirthday)90（57）定义一个反映学生年龄的视图，定义视图名为“vbirthday_student”；create view vbirthday_student(sno,sname,sage)asselect sno,sname ,year(getdate()-year(sbirthday)from student （58）将学生表中所有女生记录定义为一个视图，视图名为“vfemale_student”；create view Vfemale_student(sno,sname,ssex)asselect sno,sname,ssexfrom studentwhere ssex= （59）将学生的学号及其平均成绩定义为一个视图，视图名为“vaverage_student”； create view vaverage_student(sno,aveg)asselect sno, Avg(score)from score group by sno（60）删除视图“info_student1”，删除后即重建； use 学生管理goif object_id(info_student1) is not nulldrop view info_student1gocreate view info_student1asselect *from Studentwhere sclass = 信管系0101（61）在“信管系0101”班学生视图中找出年龄小于27岁（现在的年龄）的学生； select sno, snamefrom info_student1where year(getdate()-year(sbirthday)27（62）利用视图查询“信管系0101”班选修了“C001”课程的学生； select snamefrom info_c001_student1（63）通过“信管系0101”班info_student2视图中学号“011111103”的学生姓名改为“潘长江”； update info_student2set sname=潘长江where sno=011111103（64）向“信管系0101”班info_student1视图中插入一个新学生记录，其中：学号：011111136，姓名：张艺谋，性别：男，出生日期：1987.11.9； insert into info_student1values(011111136,张艺谋,1987.11.9,男,)（65）通过视图info_student1删除信管系0101班学号为“011111135”、姓名为“黄健中”的学生记录；delete from info_student1where sno=011111135 and sname=黄建中实验三、数据库存储过程、触发器的建立及编程操作的实现（4）执行命令语句：Exec Exam_Proc 2012213451 , 宋春龙 的结果为（5）建立触发器：货物信息表：create table货物信息表(货物号 char(4) primary key, 货物名 char (20), 规格 char (20), 型号 char(20), 说明 char (20)库存表：create table 库存表(货物号 char(4) primary key, 更新日期 datetime , 库存量 numeric(9,2)入库表：create table 入库表(入库时间 datetime,货物号 char (4), 入库数量 Numeric(9,2), 经办人 char(10),primary key(入库时间,货物号)出库表：create table 出库表(出库时间 datetime, 货物号 char(4), 出库数据 numeric (9,2), 经办人 char (10)primary key (出库时间,货物号)更新出库表：CREATE TRIGGER出库on 出库表after insertas begindeclare hwh char(4),RKL numeric(9,2) select hwh=货物号, RKL=出库数量 from inserted if exists( select * from 库存表 where 货物号=hwh) update 库存表 set 库存量=库存量-RKL where 货物号=hwhelse