SQL-数据定义及查询).doc

数据库原理实验报告姓名学号日期 201.04.23实验室A205指导教师设备编号M10实验题目实验单元二 SQL 数据定义及查询一、实验目的1）熟悉SQL数据定义语言，能够熟练的用SQL语句创建数据库、基本表、索引。2）熟悉SQL Server 2000中提供的数据类型和T-SQL数据定义语句CREATE的语法格式。3）熟练的用SQL语句进行单表查询、多表连接查询、嵌套查询、集合查询。二、实验内容1）还原数据库student2）向student表中增加备注列Comment,数据类型是字符型，固定长度50个字符，并且允许该列为空3）把student中的备注列的数据类型改为变长字符型，长度255字符4）使用SQL语句在SPJ数据库中创建S、P、J、SPJ四个关系模式。5）按教材第二章习题（5）的数据，创建S、P、J、SPJ四个关系模式。6）求供应工程J1零件的供应商号码SNO； 7）求供应工程J1零件P1的供应商号码SNO；8）找出使用供应商S1所供应零件的工程号；9）找出工程项目J2使用的各种零件的名称及其数量；10）找出上海厂商供应的所有零件号码；11）在D盘以自己名字建立文件夹，将SPJ数据库备份到该文件夹中。 三、实验要求1） 创建四个表时，首先要规划好字段类型及长度。2） 创建四个表时，要指定主码、外码及对其他字段设置约束。3） 实验报告中要求列出操作步骤以及创建表的SQL语句。4） 实验总结中写出实验中遇到的问题和解决方案以及实验的收获。4、 实验结果2）alter table student add coment char(50);3）alter table student alter column varchar(255);4）创建S、P、J、SPJ四个关系模式：S表：create table s( sno char(9) primary key, sname char(20) , status smallint, city char(20);P表：create table p( pno char(9) primary key, pname char(20) , color char(2), weight smallint);J表：create table j( jno char(9) primary key, jname char(20) , city char(20);SPJ表：create table spj( sno char(9), pno char(9), jno char(9), qty smallint, primary key (sno,pno,jno), foreign key(sno) references s(sno), foreign key(pno) references p(pno), foreign key(jno) references j(jno);5）创建S、P、J、SPJ四个关系模式：S表：insert into dbo.s (sno,sname,status,city)values(s1,精益,20,天津)insert into dbo.s (sno,sname,status,city)values(s2,盛锡,10,北京)insert into dbo.s (sno,sname,status,city)values(s3,东方红,30,北京)insert into dbo.s (sno,sname,status,city)values(s4,丰泰盛,20,天津)insert into dbo.s (sno,sname,status,city)values(s5,为民,30,上海)P表：insert into dbo.p (pno,pname,color,weight)values(p1,螺母,红,12)insert into dbo.p (pno,pname,color,weight)values(p2,螺栓,绿,17)insert into dbo.p (pno,pname,color,weight)values(p3,螺丝刀,蓝,14)insert into dbo.p (pno,pname,color,weight)values(p4,螺丝刀,红,14)insert into dbo.p (pno,pname,color,weight)values(p5,凸轮,蓝,40)insert into dbo.p (pno,pname,color,weight)values(p6,齿轮,红,30)J表：insert into dbo.j (jno,jname,city)values(j1,三建,北京)insert into dbo.j (jno,jname,city)values(j2,一汽,长春)insert into dbo.j (jno,jname,city)values(j3,弹簧厂,天津)insert into dbo.j (jno,jname,city)values(j4,造船厂,天津)insert into dbo.j (jno,jname,city)values(j5,机车厂,唐山)insert into dbo.j (jno,jname,city)values(j6,无线电厂,常州)insert into dbo.j (jno,jname,city)values(j7,半导体厂,南京)SPJ表：insert into dbo.spj (sno,pno,jno,qty)values(s1,p1,j1,200)insert into dbo.spj (sno,pno,jno,qty)values(s1,p1,j3,100)insert into dbo.spj (sno,pno,jno,qty)values(s1,p1,j4,700)insert into dbo.spj (sno,pno,jno,qty)values(s1,p2,j2,100)insert into dbo.spj (sno,pno,jno,qty)values(s2,p3,j1,400)insert into dbo.spj (sno,pno,jno,qty)values(s2,p3,j2,200)insert into dbo.spj (sno,pno,jno,qty)values(s2,p3,j4,500)insert into dbo.spj (sno,pno,jno,qty)values(s2,p3,j5,400)insert into dbo.spj (sno,pno,jno,qty)values(s2,p5,j1,400)insert into dbo.spj (sno,pno,jno,qty)values(s2,p5,j2,100)insert into dbo.spj (sno,pno,jno,qty)values(s3,p1,j1,200)insert into dbo.spj (sno,pno,jno,qty)values(s3,p3,j1,200)insert into dbo.spj (sno,pno,jno,qty)values(s4,p5,j1,100)insert into dbo.spj (sno,pno,jno,qty)values(s4,p6,j3,300)insert into dbo.spj (sno,pno,jno,qty)values(s4,p6,j4,200)insert into dbo.spj (sno,pno,jno,qty)values(s5,p2,j4,100)insert into dbo.spj (sno,pno,jno,qty)values(s5,p3,j1,200)insert into dbo.spj (sno,pno,jno,qty)values(s5,p6,j2,200)insert into dbo.spj (sno,pno,jno,qty)values(s5,p6,j4,500)6）求供应工程J1零件的供应商号码SNO：select distinct snofrom spjwhere jno=j1;7）求供应工程J1零件P1的供应商号码SNO：select distinct snofrom spjwhere jno=j1 and pno=p1;8）找出使用供应商S1所供应零件的工程号：select jnofrom spjwhere sno=s1;9）找出工程项目J2使用的各种零件的名称及其数量：select pname,qtyfrom p,spjwhere jno=j2 and p.pno=spj.pno;10）找出上海厂商供应的所有零件号码：select p