数据库系统报告二(共16页).doc
精选优质文档-倾情为你奉上数据库系统原理实验报告实验名称： 数据操作语言DML实验 任课教师： 霍纬纲 学 号： 姓 名： 黄帅 完成日期： 一、实验目的：1.建立基本表并进行DML操作。2.数据查询：单表查询、链接查询、嵌套查询、集合查询和统计。3.数据更新：增加、删除、查询和修改功能。二、实验步骤：1.进入Oracle的SQL*PLUS环境，输入：用户名：system 口令：manager 主机字符串：进入Oracle的SQL*PLUS环境，在此所创建的用户对象均属于system用户模式。2.在SQL*PLUS环境中用create insert语句创建基本表S、P、J和SPJ。S(SNO,SNAME,STATUS,CITY)P(PNO,PNAME,COLOR,WEIGHT)J(JNO,JNAME,CITY)SPJ(SNO,PNO,JNO,QTY)create table S(SNO char(3) primary key,SNAME char(12),STATUS char(2),CITY char(10);create table P(PNO char(3) primary key,PNAME char(10),COLOR char(4),WEIGHT int);create table J(JNO char(3) primary key,JNAME char(12),CITY char(10);create table SPJ(SNO char(3),PNO char(3),JNO char(3),QTY int,primary key (SNO,PNO,JNO),foreign key (SNO) references S (SNO),foreign key (PNO) references P (PNO),foreign key (JNO) references J (JNO);3、用Select语句对上述四个基本表进行如下查询（1）求供应工程J1零件的供应商号码SNO；select snofrom spjwhere jno='J1'（2）求供应工程J1零件P1的供应商号码SNO；select snofrom spjwhere jno='J1'and pno='P1'（3）求供应工程J1零件为红色的供应商号码SNO；select snofrom spj,pwhere jno='J1'and p.pno=spj.pnoand color='红'（4）求没有使用天津供应商生产的红色零件的工程号JNO；select jnofrom jwhere not exists(select *from spjwhere spj.jno=j.jnoand sno in(select snofrom swhere city='天津')and pno in(select pnofrom pwhere color='红');（5）求至少用了供应商S1所供应的全部零件的工程号；select jnofrom spj spjzwhere not exists (select *from spj spjxwhere sno='S1'and not exists(select *from spj spjywhere spjy.pno=spjx.pnoand spjy.jno=spjz.jno);（6）找出所有供应商的姓名和所在城市；select sname,cityfrom s;（7）找出所有零件的名称、颜色、重量；select pname,color,weightfrom p;（8）找出使用供应商S1所供应零件的工程号；select jnofrom spjwhere sno='S1'（9）找出工程项目J2使用的各种零件的名称及其数量；select p.pname,spj.qtyfrom p,spjwhere p.pno=spj.pnoand spj.pno='J2'（10）找出上海厂商供应的所有零件号码；select pnofrom spjwhere sno in(select sno from swhere city='上海');（11）找出使用上号产的零件的工程名称；select jnamefrom j,spjwhere j.jno=spj.jnoand spj.sno in(select snofrom swhere city='上海');（12）找出没有使用天津产的零件的工程号码；select jnofrom jwhere not exists(select *from spj,s where spj.jno=j.jnoand spj.sno=s.snoand s.city='天津');（13）列出包含SNO，SNAME，PNO,PNAME,JNO,JNAME,QTY属性的清单；select sno,sname,pno,pname,jno,jname,qtyfrom s,p,j,spj;where spj.sno=s.snoand spj.jno=j.jnoand spj.pno=p.pno;改：select s.sno,sname,p.pno,pname,j.jno,jname,qtyfrom s,p,j,spjwhere spj.sno=s.snoand spj.jno=j.jnoand spj.pno=p.pno;（14）统计各工程项目所用不同零件所用数量；select jno,sum(qty)from spj改：select jno,sum(qty)from spjgroup by jno（15）统计各供应商供应的各种零件数量；select sno,sum(qty)from spjgroup by sno4、用Insert、Delete、和Update语句实现如下数据更新（1）将全部红色零件的颜色改为蓝色；update pset color='蓝'where color='红'select *from p;（2）将工程J3的城市改为上海；update jset city='上海'where j.jno='J3'select *from j;（3）由S5供给J4的零件P6改为由S3供应；update spjset sno='S3'where sno='S5'and jno='J4'and pno='P6'select *from spj（4）从供应商关系中删除S2的元组，并从供应情况关系中删除相应元组；deletefrom spjwhere sno='S2'delete from swhere sno='S2'select *from s;select *from spj;（5）、请将S2向工程项目J6供应200个P4零件的信息加入到供应关系； insert into s values('S2','发的','20','南京');insert into spj values('S2','P4','J6','200');select * from s;select *from spj; （6）、请将S6向项目J8供应500个P7零件的信息加入到供应关系； insert into s values('S6','如果','20','南极');insert into p values('P7','榔头','橙','15');insert into j values('J8','飞机厂','张贵庄');insert into spj values('S6','P7','J8','600');5、用Insert、Delete和Update语句实现如下数据更新create table Student(sno char(5) primary key,sname varchar(10) unique,ssex char(4) not null,sage number(3) default 0,sdept varchar(10);create table Course(cno char(5) primary key,cname varchar2(15) not null,cpno char(5),ccredit number(4),foreign key (cpno) references Course(cno);create table SC(sno char(5),cno char(5),grade number(3),primary key (sno,cno),foreign key (sno) references Student(sno),foreign key (cno) references Course(cno);alter table Student add jiguan varchar2(20);alter table Student add shijian date;alter table Course modify cno char(4);alter table Course modify cpno char(4);alter table SC modify cno char(4);alter table Student drop unique(sname);create unique index Stusname on Student(sname);create unique index Coucname on Course(cname);create index Sgrade on SC(sno asc,grade desc);insert into Student values('95001','李勇','男'20,'cs','北京','27-2月-05');insert into Student values('95002','张三','女',21,'cs','天津','27-2月-05');insert into Student values('95003','李四','女',20,'cs','上海','27-2月-05');insert into Student values('95004','王五','男',20,'cs','海南','27-2月-05');insert into Student values('95005','赵六','男',21,'cs','郑州','27-2月-05');insert into Student values('95006','孟立','女',20,'cs','南通','27-2月-05');insert into Student values('95007','张飞','男',19,'cs','张家港','27-2月-05');insert into Student values('95008','赵云','女',20,'cs','连云港','27-2月-05');insert into Student values('95009','刘备','女',20,'cs','合肥','27-2月-05');insert into Student values('95010','关羽','男',18,'cs','北京','27-2月-05');insert into Course values('001','高数','',1);insert into Course values('004','化学','001',1);insert into Course values('003','C+','004',2);insert into Course values('008','英语读写','003',1);insert into Course values('005','毛概','008',2);insert into Course values('002','物理','005',1);insert into Course values('006','网球','002',2);insert into Course values('007','英语听说','006',2);insert into Course values('010','数字逻辑','006',1);insert into Course values('009','名航概论','010',2);insert into SC values('95001','003',95);insert into SC values('95001','005',78);insert into SC values('95003','002',88);insert into SC values('95005','003',92);insert into SC values('95008','004',91);insert into SC values('95008','002',66);insert into SC values('95006','001',67);insert into SC values('95003','010',87);insert into SC values('95009','009',89);insert into SC values('95010','004',78); （1）、对每一个系，求学生的平均年龄，并把结果存入数据库； create table Sin(sdept char(20),avgage number default 0,primary key(sdept),foreign key(sdept)references Student(sdept);select sdept,avg(sage) as avgagefrom student,scwhere sdept='CS' and student.sno=sc.snogroup by sdept; （2）、将计算机科学系全体学生的成绩置零； update scset grade = 0where 'cs' = (select sdept from Studentwhere Student.sno=sc.sno); （4）、删除某学生的信息及其选课记录。 deletefrom scwhere sno='95010'deletefrom studentwhere sno='95010'select *from sc；（3）、删除计算机科学系所有学生的选课记录； delete from SC;select *from sc；三、实验结果与结论：2.在SQL*PLUS环境中用create insert语句创建基本表S、P、J和SPJ。table S 已创建。table P 已创建。table J 已创建。table SPJ 已创建。1 行已插入。*n在行 54 上开始执行命令时出错:insert into J values('J6','无线电厂','常州')错误报告:SQL 错误: ORA-12899: 列 "SYSTEM"."J"."JNAME" 的值太大 (实际值: 12, 最大值: 10)在行 55 上开始执行命令时出错:insert into J values('J7','半导体厂','南京')错误报告:SQL 错误: ORA-12899: 列 "SYSTEM"."J"."JNAME" 的值太大 (实际值: 12, 最大值: 10)（1）求供应工程J1零件的供应商号码SNO；SNO-S1 S1 S2 S2 S3 S3 S4 S5 （2）求供应工程J1零件P1的供应商号码SNO；SNO-S1 S3 （3）求供应工程J1零件为红色的供应商号码SNO；SNO-S1 S3 （4）求没有使用天津供应商生产的红色零件的工程号JNO；JNO-J6 J2 J7 J5（5）求至少用了供应商S1所供应的全部零件的工程号；JNO-J1 J3 J4 J1 J1 J4 J4 J1 J1 J1 J1 J3 J4 J4 J1 J4 （6）找出所有供应商的姓名和所在城市；SNAME CITY - -精益 天津 盛锡 北京 东方红 北京 丰泰盛 天津 为民 上海 （7）找出所有零件的名称、颜色、重量；PNAME COLOR WEIGHT- - -螺母 红 12 螺楦 绿 17 螺丝刀 蓝 14 螺丝刀 红 14 凸轮 蓝 40 齿轮 红 30（8）找出使用供应商S1所供应零件的工程号；JNO-J1 J3 J4 J1（9）找出工程项目J2使用的各种零件的名称及其数量；未选择任何行（10）找出上海厂商供应的所有零件号码；PNO-P2 P3 P6 P6 （11）找出使用上号产的零件的工程名称；JNAME -三建 一汽 造船厂 造船厂 （12）找出没有使用天津产的零件的工程号码；JNO-J2 J5 J6 J7（13）列出包含SNO，SNAME，PNO,PNAME,JNO,JNAME,QTY属性的清单；在行 1 上开始执行命令时出错:select sno,sname,pno,pname,jno,jname,qtyfrom s,p,j,spj命令出错, 行: 1 列: 27错误报告:SQL 错误: ORA-00918: 未明确定义列00918. 00000 - "column ambiguously defined"*Cause: *Action:在行 3 上开始执行命令时出错:where spj.sno=s.sno错误报告:未知的命令在行 4 上开始执行命令时出错:and spj.jno=j.jno错误报告:未知的命令在行 5 上开始执行命令时出错:and spj.pno=p.pno错误报告:未知的命令改：SNO SNAME PNO PNAME JNO JNAME QTY- - - - - - -S2 盛锡 P5 凸轮 J1 三建 400 S2 盛锡 P3 螺丝刀 J1 三建 400 S3 东方红 P3 螺丝刀 J1 三建 200 S5 为民 P3 螺丝刀 J1 三建 200 S1 精益 P2 螺楦 J1 三建 100 S4 丰泰盛 P2 螺楦 J1 三建 100 S1 精益 P1 螺母 J1 三建 200 S3 东方红 P1 螺母 J1 三建 200 S5 为民 P6 齿轮 J2 一汽 200 S2 盛锡 P5 凸轮 J2 一汽 100 S4 丰泰盛 P2 螺楦 J3 弹簧厂 300 S1 精益 P1 螺母 J3 弹簧厂 100 S4 丰泰盛 P6 齿轮 J4 造船厂 200 S5 为民 P6 齿轮 J4 造船厂 500 S2 盛锡 P3 螺丝刀 J4 造船厂 500 S2 盛锡 P2 螺楦 J4 造船厂 200 S5 为民 P2 螺楦 J4 造船厂 100 S1 精益 P1 螺母 J4 造船厂 700 S2 盛锡 P3 螺丝刀 J5 机车厂 400（14）统计各工程项目所用不同零件所用数量；在行 1 上开始执行命令时出错:select jno,sum(qty)from spj命令出错, 行: 1 列: 7错误报告:SQL 错误: ORA-00937: 不是单组分组函数00937. 00000 - "not a single-group group function"*Cause: *Action:改：JNO SUM(QTY)- -J3 400 J4 2200 J2 300 J1 1800 J5 400（15）统计各供应商供应的各种零件数量；SNO SUM(QTY)- -S1 1100 S3 400 S5 1000 S2 2000 S4 6004、用Insert、Delete、和Update语句实现如下数据更新（1）将全部红色零件的颜色改为蓝色；3 行已更新。PNO PNAME COLOR WEIGHT- - - -P1 螺母 蓝 12 P2 螺楦 绿 17 P3 螺丝刀 蓝 14 P4 螺丝刀 蓝 14 P5 凸轮 蓝 40 P6 齿轮 蓝 30（2）将工程J3的城市改为上海；1 行已更新。JNO JNAME CITY - - -J1 三建 北京 J2 一汽 长春 J3 弹簧厂 上海 J4 造船厂 天津 J5 机车厂 唐山 J6 无线电厂 常州 J7 半导体厂 南京 （3）由S5供给J4的零件P6改为由S3供应；1 行已更新。SNO PNO JNO QTY- - - -S1 P1 J1 200 S1 P1 J3 100 S1 P1 J4 700 S1 P2 J1 100 S2 P3 J1 400 S2 P2 J4 200 S2 P3 J4 500 S2 P3 J5 400 S2 P5 J1 400 S2 P5 J2 100 S3 P1 J1 200 S3 P3 J1 200 S4 P2 J1 100 S4 P2 J3 300 S4 P6 J4 200 S5 P2 J4 100 S5 P3 J1 200 S5 P6 J2 200 S3 P6 J4 500（4）从供应商关系中删除S2的元组，并从供应情况关系中删除相应元组；6 行已删除。1 行已删除。SNO SNAME STATUS CITY - - - -S1 精益 20 天津 S3 东方红 30 北京 S4 丰泰盛 30 天津 S5 为民 30 上海 SNO PNO JNO QTY- - - -S1 P1 J1 200 S1 P1 J3 100 S1 P1 J4 700 S1 P2 J1 100 S3 P1 J1 200 S3 P3 J1 200 S4 P2 J1 100 S4 P2 J3 300 S4 P6 J4 200 S5 P2 J4 100 S5 P3 J1 200 S5 P6 J2 200 S3 P6 J4 500（5）、请将S2向工程项目J6供应200个P4零件的信息加入到供应关系；1 行已插入。SNO SNAME STATUS CITY - - - -S1 精益 20 天津 S2 盛锡 10 北京 S3 东方红 30 北京 S4 丰泰盛 30 天津 S5 为民 30 上海 SNO PNO JNO QTY- - - -S1 P1 J1 200 S1 P1 J3 100 S1 P1 J4 700 S1 P2 J1 100 S2 P3 J1 400 S2 P2 J4 200 S2 P3 J4 500 S2 P3 J5 400 S2 P5 J1 400 S2 P5 J2 100 S3 P1 J1
