oracle实验2数据更新与视图.doc

实验二 数据定义和查询一、实验目的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由供应商代码（SNO）、供应商姓名（SNAME）、供应商所在城市（CITY）组成；零件表P由零件代码（PNO）、零件名（PNAME）、颜色（COLOR）、重量（WEIGHT）组成；工程项目表J由工程项目代码（JNO）、工程项目名（JNAME）、工程项目所在城市（CITY）组成；供应情况表SPJ由供应商代码（SNO）、零件代码（PNO）、工程项目代码（JNO）、供应数量组成（QTY），表示某供应商供应某种零件给某工程项目的数量为QTY。数据如下：-13-S表SNOSNAMECITYS1精益天津S2盛锡北京S3东方红北京S4丰泰盛天津S5为民上海P表PNOPNAMECOLORWEIGHTP1螺母红12P2螺栓绿17P3螺丝刀蓝14P4螺丝刀红14P5凸轮蓝40P6齿轮红30J表JNOJNAMECITYJ1三建北京J2一汽长春J3弹簧厂天津J4造船厂天津J5机车厂唐山J6无线电厂常州J7半导体厂南京SPJ表SNOPNOJNOQTYS1P1J1200S1P1J3100S1P1J4700S1P2J2100S2P3J1400S2P3J2200S2P3J4500S2P3J5400S2P5J1400S3P1J1200S3P3J1200S4P5J1100S4P6J3300S4P6J4200S5P2J4100S5P3J1200S5P6J2200S5P6J4500要求：（1） 创建每个关系的主键，有外键的创建外键。（2）S表中的Sname属性列的取值唯一。（3）P表中weight属性列的取值范围在1-50之间。（4）J表中的Jname取值不能为空并且是唯一的。（5）SPJ表中QTY属性列的数据类型必须为NUMBER(5)。图12用SQL语句完成以下操作：（1）给S表增加Sphone和Semail两个属性列，分别用来存放供应商的联系电话和电子信箱。alter table s add sphone varchar2(20);alter table s add semail varchar2(20);（2） 删除Jname属性列取值唯一的约束。alter table j drop constraint SYS_C0011229;（3） 将QTY属性列的数据类型修改为Integer型。alter table spj modify ( qty integer);（4）删除S表中的属性列Semailalter table s drop column semail;图2.第2小题的全过程3在J表的Jname属性列上创建唯一性索引。图3.在Jname列创建唯一性索引4练习EXP和IMP命令的使用方法，使用EXP命令将数据表导出。图45完成以下查询：（1）查询所有供应商所在的城市。select city from s;/*查询所有供应商所在城市*/图5（2）查询零件重量在10-20之间（包括10和20）的零件名和颜色。select pname,color from p where weight between 10and 20;/*查询零件重量在10-20之间（包括10和20）的零件名和颜色。*/（4） 查询工程项目的总个数。select count (jname) from j;/*查询项目总个数*/图7（5） 查询所有零件的平均重量。select avg(weight) from p;/*查询所有零件的平均重量*/图8（6） 查询供应商S3供应的零件号。select pno from spj where sno like S3;/*查询供应商S3供应的零件号*/图9（7） 查询各个供应商号及其供应了多少类零件。select sno,count(distinct pno)from spj group by sno; /*查询各个供应商号及其供应了多少类零件*/图10（8） 查询供应了2类以上零件的供应商号。SELECT Sno FROM spj GROUP BY Sno HAVING COUNT(pno) =2;/*查询供应了2类以上零件的供应商号*/（9） 查询零件名以“螺”字开头的零件信息。select *from p where pname like 螺%;图12（10） 查询工程项目名中最后一个字为“厂”字的工程项目所在的城市。select city from j where jname like %厂;图13（11） 查询给每个工程供应零件的供应商的个数。select jno,count (distinct sno)from spj group by jno;/*查询给每个工程供应零件的供应商的个数*/图14（12） 查询供应数量在10002000之间（包括1000和2000）的零件名称。select spj.qty,p.pname from p,spj where spj.qty=1000 and spj.qty 200（8）查询姓名为田丕龙的学生所学课程的课程名与学分。select sname,cname,creditfrom student,sc,coursewhere sname = 田丕龙 and o = o and sc.sno = student.sno（9）查询选修课程号为“160”或“304”的学生的学号。select cno,snofrom scwhere cno = 160 or cno = 304（10）查询选修了课程号为“160”和“304”的学生的学号。select a.sno,o,ofrom sc a,sc bwhere a.sno = b.sno and o = 160 and o = 304（11）查询学习全部课程的学生姓名。select snamefrom studentwhere not exists ( select * from course where not exists ( select * from sc where sc.sno = student.sno and o = o ) ) 图17（12）查询1994年1月1日以前出生的学生的姓名和专业。select sname,majorfrom studentwhere trunc(sysdate - birth)/365) 20图18（13） 查询选修了“大学英语4”课程且成绩在90分以上的学生姓名。select snamefrom sc,student,coursewhere sc.sno = student.sno and o = o and cname = 大学英语4 and grade 90（14） 查询选修了5门以上课程的学生学号和姓名。select sno,snamefrom studentwhere sno in (select sno from sc group by sno having count(o) 5 )（15） 查询未选修“政治经济学”课程的学生情况。 select distinct student.*from sc,student,coursewhere sc.sno = student.sno and o = oand cname != 政治经济学（16） 统计102和378号课程的选课人数及平均成绩。 select cno,count(sno),avg(grade)from scgroup by cnohaving cno = 102 or cno = 378（17） 查询比所有“信息管理与信息系统”专业学生年龄都大的学生。 select sno,sname,trunc(sysdate - birth) / 365) agefrom studentwhere trunc(sysdate - birth) / 365) all (select trunc(sysdate - birth) / 365) from student where major = 信息管理与信息系统)and major != 信息管理与信息系统图19（18） 将“计算机科学