实验二总结报告

《数据库原理与应用》实验报告实验名称：实验二班 级：软件工程学 号：姓 名：一、实验目的1．掌握使用SQL语句创建和删除数据表，创建各种完整性约束2．掌握使用SQL语句修改表的结构3. 掌握查询语句的使用方法，重点掌握连接查询和嵌套查询二、实验过程1.使用SQL语句建立4个关系，：供应商表S （Sno, Sname, City）零件表P（Pno, Pname, Color, Weight）工程项目表J（Jno, Jname, City）供应情况表 SPJ（Sno, Pno, Jno, QTY）创建S表的语句为：create table s(sno varchar2(20) primary key, sname varchar2(40) unique, city varchar2(10));语句的执行结果为：创建p表的语句为：create table p(pno varchar2(20) primary key, pname varchar2(40), color varchar2(10), weight number check(weight>=1 and weight<=50));语句的执行结果为：创建j表的语句为：create table j(jno varchar2(20) primary key, jname varchar2(40) unique not null, city varchar2(10));语句的执行结果为：创建spj表的语句为：create table spj(sno varchar2(20), pno varchar2(20), jno varchar(20), qty number(5), primary key(sno,pno,jno), foreign key (sno) references s(sno), foreign key (pno) references p(pno), foreign key (jno) references j(jno));语句的执行结果为：2. 用SQL语句完成以下操作（1）给S表增加Sphone和Semail两个属性列，分别用来存放供应商的联系电话和电子信箱。

语句：alter table s add sphone varchar2(15);alter table s add semail varchar2(40);执行结果：（2）删除Jname属性列取值唯一的约束语句：alter table j drop constraint SYS_C0011066;执行结果：（3）将QTY属性列的数据类型修改为Integer型 语句：alter table spj modify qty integer; 执行结果：（4）删除S表中的属性列Semail语句：alter table s drop column semail;执行结果：3.在J表的Jname属性列上创建唯一性索引语句：create unique index sy on j(jname); 执行结果：4.使用EXP命令将创建的四张数据表导出执行结果：5.在创建的S，P，J和SPJ表中完成以下查询：（1）查询所有供应商所在的城市select distinct city from s;（2）查询零件重量在10-20之间（包括10和20）的零件名和颜色select pname,color from p where weight between 10 and 20;（3）查询工程项目的总个数。

select count(distinct jno) from j;（4）查询所有零件的平均重量select avg(weight) from p;（5）查询供应商S3供应的零件号select pno from spj where sno='s3';（6）查询各个供应商号及其供应了多少类零件select sno,count(distinct pno) from spj group by sno;（7）查询供应了2类以上零件的供应商号select sno from spj group by sno having count(distinct pno)>2;（8）查询零件名以“螺”字开头的零件信息select * from p where pname like '螺%';（9）查询工程项目名中最后一个字为“厂”字的工程项目所在的城市select city from j where jname like '%厂';（10）查询给每个工程供应零件的供应商的个数select jno,count(distinct sno) from spj group by jno;（11）查询供应数量在1000—之间（包括1000和）的零件名称。

select pname from p where pno in (select pno from spj group by pno having sum(qty)>=1000 and sum(qty)<=); 6．将实验一中创建的三张表student，course和sc用IMP命令导入，在导入的三张表中完成以下查询：（1）查询“信息管理与信息系统”专业学生的姓名和年龄select sname,trunc((sysdate-birth)/365) sage from student where major='信息管理与信息系统';（2）查询107号课程的最高成绩select max(grade) from sc where cno='107';（3）统计每个专业的学生人数select major,count(major) 人数 from student group by major;（4）统计每门课程的修课人数和考试最高分select cno,count(distinct sno) 人数,max(grade) 最高分 from sc group by cno;（5）查询总成绩超过200分的学生，要求列出学号和总成绩。

select sno,sum(grade) from sc group by sno having sum(grade)>200;（6）查询姓名为田丕龙的学生所学课程的课程名与学分select cname,gredit from course where cno in （select cno from sc where sno in （select sno from student where sname='田丕龙'）);（7）查询选修课程号为“160”或“304”的学生的学号select sno from sc where cno='160' or cno='304';（8）查询选修了课程号为“160”和“304”的学生的学号select x.sno from sc x,sc y where x.sno=y.sno and o='160' and o='304';（9）查询学习全部课程的学生姓名select sname from student where sno in (select sno from sc group by sno having count(cno)= (select count(cno) from course));（10）查询1994年1月1日以前出生的学生的姓名和专业。

select sname,major from student where to_date('1994/01/01','yyyy/mm/dd')-birth>=0;（11）查询选修了“大学英语4”课程且成绩在90分以上的学生姓名select sname from student where sno in (select sno from sc where grade>90 and cno in (select cno from course where cname='大学英语4')); （12）查询选修了5门以上课程的学生学号和姓名select sno,sname from student where sno in (select sno from sc group by sno having count(cno)>5); （13）查询未选修“政治经济学”课程的学生情况select * from student where sno!=all (select sno from sc where cno in (select cno from course where cname='政治经济学'));（14）统计102和378号课程的选课人数及平均成绩。

select cno,count(sno) 人数,avg(grade) from sc group by cno having cno='102' or cno='378';（15）查询比所有“信息管理与信息系统”专业学生年龄都大的学生select * from student where birth< (select min(birth) from student where major='信息管理与信息系统'); （16）将“计算机科学与技术”专业的学生按出生时间先后排序select * from student where major='计算机科学与技术' order by birth asc;三、实验总结。