《数据库开发技术》课程之实验二

1 福建工程学院信息科学与工程学院福建工程学院信息科学与工程学院 实验实验报告报告 2013 2014学年第1学期任课老师 蒋建辉 课程名称课程名称数据库开发技术数据库开发技术班级班级座号座号姓名姓名 实验题目实验题目 数据库开发技术 数据库开发技术 课程课程 之实验二之实验二 实验时间实验时间 实验开始日期 2013 10 21 报告提交日期 2013 10 25 实验目的 要求实验目的 要求 实验目的实验目的 1 掌握分组查询 连接查询和集合运算的使用方法 2 掌握子查询及内嵌视图的使用方法 3 掌握多列子查询和相关子查询的使用 4 掌握 TOP N 分析方法 实验设计内容及实现步骤实验设计内容及实现步骤 2 连接查询 集合运算 子查询和连接查询 集合运算 子查询和 TOP N 分析分析 实验用的数据表在本文档的最后面实验用的数据表在本文档的最后面 1 查询使用红色零件的工程名称 考核多表 三表 连接操作 分别使用相关的子查询 带 in 谓词的子查询 带 exists 谓词的子查询和连接查询实现 注意体会不同实现方式的区 别 连接查询 SELECT DISTINCT JNAME FROM P J SPJ WHERE P COLOR 红 AND SPJ PNO P PNO AND SPJ JNO J JNO 三层带 IN 谓词的嵌套查询 SELECT JNAME FROM J WHERE JNO IN SELECT JNO FROM SPJ WHERE PNO IN SELECT PNO FROM P WHERE COLOR 红 带 exists 谓词的子查询 相关子查询 2 查询每一种零件被供应的次数 要求 1 结果显示零件号 零件名称和被供应的次数 select p pno pname count spj pno from p spj where p pno spj pno group by p pno pname order by p pno 3 2 零件表中的所有零件都要统计 显示效果形如下 PNO PNAMECOUNT P P1螺母4 P2螺栓2 P3螺丝刀5 P4螺丝刀0 P5凸轮3 P6齿轮3 P7把手0 已选择 7 行 问题 1 用 GROUP BY P PNO 还是 SPJ PNO Answer P PNO 问题 2 GROUP BY P PNO 和 GROUP BY P PNO PNAME 结果是否一样 Answer 如果要显现零件名则要选择后者 因为 pname 不属于 group by 的表达式 问题 3 用 COUNT SPJ PNO 还是 COUNT P PNO Answer COUNT SPJ PNO 问题 4 如果查询结果不要求显示零件号 只要求显示零件名称和数量 以下这个 SQL 语句 对吗 SELECTPNAME COUNT SPJ PNO FROMP SPJ WHEREP PNO SPJ PNO GROUP BYPNAME Answer 错误 1 零件名不唯一时 则不同型号的但同种零件只显示一种 错误 2 数量计算应该用 SUM QTY 3 查询 student 表中各系学生数占全校学生人数的百分比 SELECT SDEPT COUNT SNO SELECT COUNT SNO FROM STUDENT AS Student FROM STUDENT GROUP BY SDEPT 显示效果形如下 SDEPT Student 4 CS33 3 FL22 2 IS22 2 MA22 2 已选择 4 行 4 查询工资高于本部门平均工资的员工信息 SELECT FROM EMP E SELECT AVG SAL 部门平均工资 DEPTNO FROM EMP GROUP BY DEPTNO S WHERE E SAL S 部门平均工资 AND E DEPTNO S DEPTNO 5 显示与 30 部门任何雇员任何雇员薪水及岗位相匹配的雇员的姓名 部门编号 薪水及佣金 SELECT ENAME DEPTNO JOB FROM EMP WHERE SAL IN SELECT SAL FROM EMP WHERE DEPTNO 30 AND JOB IN SELECT JOB FROM EMP WHERE DEPTNO 30 AND DEPTNO30 6 查询其他系中比计算机科学系某一学生年龄大的学生姓名与年龄 用两种方法实现 SELECT SNAME SAGE FROM STUDENT WHERE SAGE ANY SELECT SAGE FROM STUDENT WHERE SDEPT CS AND SDEPT CS SELECT DISTINCT A SNAME A SAGE FROM STUDENT A STUDENT B WHERE A SDEPT CS AND B SDEPT CS AND A SAGE B SAGE 显示效果形如下 SNAMESAGE 张军21 7 查询至少选修了学生 95002 选修的全部课程的学生学号 不包括 95002 学生本身 SELECT DISTINCT SNO FROM SC A WHERE NOT EXISTS SELECT FROM SC B WHERE B SNO 95002 AND NOT EXISTS SELECT FROM SC C WHERE C SNO A SNO AND C CNO B CNO AND 5 A SNO 95002 显示效果形如下 SNO 95001 8 返回雇员号最小的 10 个员工信息 SELECT FROM SELECT FROM EMP ORDER BY EMPNO WHERE ROWNUM 1 select from select 列 from 表 where 条件 order by 列 desc whererownum 列数 9 查询工资第 3 低的员工的工号 姓名和薪水 假设 SAL 子段作了惟一限制 提示 使用 from 子查询语句 内嵌视图和 ROWNUM 伪列 SELECT FROM SELECT FROM SELECT EMPNO ENAME SAL FROM EMP ORDER BY SAL WHERE ROWNUM 3 ORDER BY SAL DESC WHERE ROWNUM 1 显示效果形如下 EMPNO ENAMESAL 7876 ADAMS1100 已选择 1 行 10 使用 MERGE 语句从 EMP 表向 EMP30 表更新最新改变过的雇员的薪水 并用 INSERT 语句插入不在 EMP30 表中的雇员 实验参考数据 CREATE TABLE STUDENT SNONUMBER 5 PRIMARYKEY SNAMEVARCHAR2 20 SSEXCHAR 2 SAGENUMBER 3 6 SDEPTVARCHAR2 10 CREATE TABLE COURSE CNONUMBER 4 PRIMARYKEY CNAMEVARCHAR2 30 CPNONUMBER 4 CCREDITNUMBER 2 CLIMITNUMBER 4 CREATE TABLE SC SNONUMBER 5 CNONUMBER 4 GRADENUMBER 3 1 PRIMARYKEY SNO CNO INSERT INTO STUDENT VALUES 95001 李明勇 男 20 CS INSERT INTO STUDENT VALUES 95002 刘晨 女 19 IS 7 INSERT INTO STUDENT VALUES 95003 王名 女 18 MA INSERT INTO STUDENT VALUES 95004 张立 男 19 CS INSERT INTO STUDENT VALUES 95005 张军 男 21 MA INSERT INTO STUDENT VALUES 95006 王张凤 女 19 FL INSERT INTO STUDENT VALUES 95007 王敬 女 18 IS INSERT INTO STUDENT VALUES 95008 张名惠 男 19 FL COMMIT INSERT INTO COURSE VALUES 1 数据库 5 5 10 INSERT INTO COURSE VALUES 2 数学 NULL 3 10 INSERT INTO COURSE VALUES 3 信息系统 1 4 12 INSERT INTO COURSE VALUES 4 操作系统 6 4 12 INSERT INTO COURSE VALUES 5 数据结构 7 5 16 INSERT INTO COURSE VALUES 6 数据处理 NULL 3 15 INSERT INTO COURSE VALUES 7 PASCAL 语言 6 2 NULL COMMIT INSERT INTO SC VALUES 95001 5 92 INSERT INTO SC VALUES 95002 3 80 INSERT INTO SC VALUES 95001 1 58 INSERT INTO SC VALUES 95002 2 90 INSERT INTO SC VALUES 95003 3 NULL INSERT INTO SC VALUES 95001 3 70 INSERT INTO SC VALUES 95002 1 84 5 INSERT INTO SC VALUES 95003 2 67 INSERT INTO SC VALUES 95007 2 NULL INSERT INTO SC VALUES 95008 1 34 5 INSERT INTO SC VALUES 95001 2 85 COMMIT create table stuscore cname varchar2 30 grade NUMBER 3 1 sno number 5 insert into stuscore values 数据库 78 95001 insert into stuscore values 信息系统 80 95001 insert into stuscore values 操作系统 90 95001 insert into stuscore values 数据库 79 95002 insert into stuscore values 信息系统 82 95002 insert into stuscore values 操作系统 92 95002 COMMIT 8 INSERT INTO SC VALUES 95007 2 45 INSERT INTO SC VALUES 95007 1 44 INSERT INTO SC VALUES 95007 3 64 INSERT INTO SC VALUES 95007 4 34 INSERT INTO SC VALUES 95007 5 50 INSERT INTO SC VALUES 95007 6 20 DROP TABLE SPJ DROP TABLE S DROP TABLE P DROP TABLE J CREATE TABLE S SNOCHAR 3 PRIMARYKEY SNAME VARCHAR2 10 NOT NULL STATUS CHAR 2 NOT NULL CITYVARCHAR 6 CREATE TABLE P PNOCHAR 3 PRIMARYKEY PNAME VARCHAR2 10 NOT NULL COLOR VARCHAR2 6 NOT NULL WEIGHTNUMBER 5 3 CREATE TABLE J JNOCHAR 3 PRIMARYKEY JNAME VARCHAR2 10 NOT NULL CITYVARCHAR2 6 CREATE TABLE SPJ SNOCHAR 3 REFERE