SQL-数据定义及查询)

1、?数据库原理?实验报告姓名学号日期 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使用

2、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） 实验总结中写出实验

3、中遇到的问题和解决方案以及实验的收获。4、 实验结果2alter table student add coment char(50);3alter 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),

4、 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(jn

5、o);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,'北京')inse

6、rt 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,c

7、olor,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&#

8、39;,'凸轮','蓝',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

9、.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','无线电厂',

10、9;常州')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,

11、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'

12、;,'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

13、(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',&#

14、39;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

15、 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