本章的知识点了解 SQL 语言发展史掌握关系数据库体系结构 三层结构在关系数据库体现 ) 掌握基本表定义 包括修改 删除定义 ) 掌握视图的概念与定义 删除定义理解索引的概念与定义 删除定义总结 SQL 数据定义的特点总结用户数据查询的基本结构掌握 SELECT 子句重复元组的处理掌握 FROM 子句掌握 WHERE 子句理解更名 属性 列 ) 运算理解字符串操作理解元组显示顺序理解分组掌握聚集函数掌握空值处理理解嵌套子查询的概念 表达理解掌握集合成员资格了解集合之间的比较了解集合基数的测试
了解派生关系掌握关系的连接掌握用户数据插入掌握用户数据删除掌握用户数据更新了解通过视图更新用户数据总结 SQL 语言特点需重点理解掌握的内容 : 数据定义 基本表 视图 ) 数据查询 基本结构及其各种变化 ) 数据更新 如何证明 SQL 语句的正确性?
作业参考答案 : /*P148 习题 3 创建表 s p j spj*/ -- 创建之前首先删除可能存在的表 --spj 表参考表 s p j, 应该先删除 drop table spj; drop table s; drop table p; drop table j; create table s sno char2), sname varchar29), status number2), city varchar29) create table p pno char2), pname varchar29), color char3), weight number4) create table j jno char2), jname varchar212), city varchar29) create table spj sno char2), pno char2), jno char2), qty number4) /*P148 习题 4*/ /*1)*/ -- 按照关系代数转换查询 select distinct sno from spj where jno = 'J1'; /*2)*/ -- 按照关系代数转换查询
select distinct sno from spj where jno = 'J1' and pno = 'P1'; /*3)*/ --natural join 连接查询 select distinct sno from spj natural join p where jno = 'J1' and color = ' 红 ' ; --join 连接查询 select distinct sno from spj inner join p on spj.pno = p.pno where jno = 'J1' and color = ' 红 ' ; select distinct sno from spj where jno = 'J1' and pno in select pno from p where color = ' 红 ' select distinct sno from spj, p where spj.pno = p.pno and jno = 'J1' and color = ' 红 ' ; -- 子查询 exists select distinct sno from spj where jno = 'J1' and exists select pno from p where color = ' 红 ' and p.pno = spj.pno /*4)*/ --natural join 连接查询 select distinct jno from j where jno not in select jno from spj natural join p natural join s where city = ' 天津 ' and color = ' 红 ' -- 将 not in 改用 not exists, 下面几种方法的语句同样可以如此改写, 不再一一列出 select distinct jno from j where not exists select jno from spj natural join p natural join s where city=' 天津 ' and color=' 红 ' and spj.jno = j.jno --join 连接查询 select distinct jno from j where jno not in select jno from spj inner join p on spj.pno=p.pno inner join s on spj.sno=s.sno where city = ' 天津 ' and color = ' 红 ' select distinct jno from j where jno not in select jno from spj where sno in select sno from s where city = ' 天津 ') and pno in select pno from p where color = ' 红 ') select distinct jno from j where jno not in select jno from spj,p,s where spj.pno=p.pno and spj.sno=s.sno and city = ' 天津 ' and color = ' 红 ' /*5)*/ -- 按照关系代数转换查询
select distinct jno from spj spjx where not exists select pno from spj where sno = 'S1' and pno not in select pno from spj spjz where spjz.jno = spjx.jno ) select distinct jno from spj spjx where not exists select * from spj spjy where sno = 'S1' and not exists select * from spj spjz where spjx.jno = spjz.jno and spjz.pno = spjy.pno) /*P148 习题 5*/ /*1)*/ select sname,city from s; /*2)*/ select pname,color,weight from p; /*3)*/ select distinct jno from spj where sno = 'S1'; /*4)*/ -- 只按照题意, 不考虑工程使用不同供应商供应的同种零件 -- 只考虑 natural join 查询 select distinct pname,qty from spj natural join p where jno = 'J2'; -- 考虑工程使用不同供应商供应的同种零件, 直接按照零件名分组 select distinct pname,sumqty) as sumqty from spj natural join p where jno = 'J2' group by pname; -- 考虑工程使用不同供应商供应的同种零件, 考虑到零件同名, 按照零件号分组 select pname,sumqty from p natural join select pno, sumqty) as sumqty from spj where jno = 'J2' group by pno) j2sumqty ; /*5)*/ --natural join 查询 select distinct pno from spj natural join s where city = ' 上海 '; --join 查询 select distinct pno from spj inner join s on spj.sno=s.sno where city = ' 上海 '; select distinct pno from spj,s where spj.sno = s.sno and city = ' 上海 '; select distinct pno from spj where sno in select sno from s where city = ' 上海 ' -- 子查询 exists select distinct pno from spj where exists select sno from s where city = ' 上海 'and s.sno = spj.sno /*6)*/ --natural join 查询 select distinct jname from spj natural join s natural join j where s.city = ' 上海 '; --join 查询
select distinct jname from spj inner join s on spj.sno = s.sno inner join j on spj.jno = j.jno where s.city = ' 上海 '; select distinct jname from spj,s,j where spj.jno = j.jno and spj.sno = s.sno and s.city = ' 上海 '; select distinct jname from j where jno in select jno from spj where sno in select sno from s where city = ' 上海 ') -- 子查询 exists select distinct jname from j where existsselect * from spj inner join s on spj.sno = s.sno where city = ' 上海 ' and spj.jno = j.jno /*7)*/ -- 本题不考虑没有使用零件的工程 --natural join 查询 select distinct jno from spj where jno not in select jno from spj natural join s where city = ' 天津 ' --join 查询 select distinct jno from spj where jno not in select jno from spj inner join s on spj.sno = s.sno where city = ' 天津 ' select distinct jno from spj where jno not in select jno from spj, s where spj.sno = s.sno and city = ' 天津 ' select distinct jno from spj where jno not in select jno from spj where sno in select sno from s where city = ' 天津 ') -- 子查询 exists select distinct jno from spj spjx where not exists select * from spj spjy where spjx.jno = spjy.jno and sno in select sno from s where city = ' 天津 ') -- 错误 select distinct jno from spj where sno not in select sno from s where city = ' 天津 ') -- 下面考虑没有使用零件的工程, 按照关系代数转换查询 select distinct jno from j where jno not in select jno from spj inner join s on spj.sno = s.sno where city = ' 天津 /*8)*/ update p set color = ' 蓝 ' where color = ' 红 '; /*9)*/ update spj set sno = 'S3' where sno = 'S5' and pno = 'P6' and jno = 'J4'; /*10)*/ -- 考虑到 spj 参考引用 s, 先删除 spj 内容 delete from spj where sno = 'S2';
delete from s where sno = 'S2'; /*11)*/ insert into spjsno,jno,pno,qty) values's2','j6','p4',200 insert into spj values's2','p4','j6',200 /*P149 习题 11*/ drop view v_sj; create view v_sj as select sno, pno,qty from spj natural join j where jname = ' 三建 '; create view v_sj as select sno, pno,qty from spj inner join j on spj.jno = j.jno where jname = ' 三建 '; create view v_sj as select sno, pno, qty from spj where jno in select jno from j where jname = ' 三建 ' --1) select pno, sumqty) as qty from v_sj group by pno; --2) select * from v_sj where sno = 'S1'; -- 证明第一章 10 -- 定义表 drop table sp; create table sp sno char2), pno char2), primary key sno,pno) drop table pj ; create table pj pno char2), jno char2), primary key pno,jno) drop table sj; create table sj sno char2),
jno char2), primary key sno,jno) -- 插入数据 insert sp select distinct sno,pno from spj; insert sj select distinct sno,jno from spj; insert pj select distinct pno,jno from spj; -- 结果 select sp.sno,sp.pno,sj.jno from sp natural join sj natural join pj order by sp.sno,sp.pno,sj.jno ; -- 结果比较 select sno, pno, jno from spj order by sno, pno, jno ;