oracle第九天：sql练习以及答案

岳半白

　　第 一 章
　　一、你服务的公司要建立一个网络论坛，考虑创建一个存储用户基本信息的表，表名为User_info，包括用户ID、妮称、密码、Email、所在地区、注册日期、用户分数、发帖数。
　　
　　二、上面的创建表命令是DDL还是DML命令，DDL和DML分别代表什么
　　
　　三、显示发帖数超过十万的用户的ID
　　
　　四、显示注册日期在一年以内，发帖数过万的会员ID
　　
　　五、按用户分数列，由大到小显示分数超过1000分的会员ID、妮称，对于分数相同的会员，按发帖数量由大到小显示。
　　
　　六、为所有发帖数超过1万的会员用户分数加50分
　　
　　七、为此用户基本信息表添加一个用户等级列，并按如下规则设置每个会员的等级：
　　初级会员：注册日期在三个月内并且发帖数在100以内的
　　中级会员：注册日期在一年以内并且发帖数在300以内的
　　高级会员：注册日期在二年以内并且发帖数在1000以内的
　　老会员：注册日期在四年以内并且发帖数在3000以内的
　　骨灰会员：发帖数在8000以内的。
　　管理员：发帖数在1万以内
　　超级管理员：发帖数在5万以内
　　
　　八、ID为“AAA”的违返了论坛规则，删除此ID
　　
　　九、显示妮称中包含“奥运”字样会员的ID、妮称
　　
　　十、显示有哪些地区的会员发帖数量在10000以上
　　
　　答案
　　三：select 用户ID from user_info where 发帖数>=100000;
　　四：select 用户ID from user_info where 发帖数>=10000 and 注册日期>=to_date(‘2007-09-08’,’yyyy-mm-dd’);
　　五：select 用户ID，妮称 from user_info order by 用户分数,发帖数量;
　　六：update user_info set 用户分数=用户分数+50 where 发帖数>=10000;
　　七：alter user_info add 等级 varchar2(14);
　　update user_info set 等级=’初级会员’ where 注册日期>=to_date(‘2008-6-7’) and 发帖数<=100;
　　update user_info set 等级=’中级会员’ where 注册日期>=to_date(‘2007-9-7’) and 发帖数<=300;
　　update user_info set 等级=’高级会员’ where 注册日期>=to_date(‘2006-9-7’) and 发帖数<=1000;
　　update user_info set 等级=’老会员’ where 注册日期>=to_date(‘2004-6-7’) and 发帖数<=3000;
　　update user_info set 等级=’骨灰会员’ where 发帖数<=8000;
　　update user_info set 等级=’管理员’ where 发帖数<=10000;
　　update user_info set 等级=’超级管理员’ where 发帖数<=50000;
　　八：delete user_info where 用户ID＝’AAA’;
　　九：select 用户ID，妮称 from user_info where 妮称 like ‘%奥运%’;
　　十：select count(distinct 所在地区) from user_info where发帖数>=10000;
　　
　　
　　第 三 章 连接
　　一、以第一章中论坛用户基本信息表的用户等级列为例，假设表中原来没有此列，现在要添加此列，但是由于论坛刚刚试行会员等级制度，因此，会员等级的显示名称有可能会变化（只是名称上有变化），比如本来是初级会员、中级会员等等，以后有可能改为士卒、将领、大将军等等。但每次变化用户等级的名称时，都需要更新表的所有行，如果表的行比较多，速度将非常慢。有什么方案解决这个问题。
　　
　　二、说出在上例子中，将等级相关信息另设一个表的优点与缺点
　　
　　三、接上例，会员等级的规则以发帖数为准，根据不同的发帖数量，设定会员等级，而且此会员等级的规则也有可能变化。比如本来是发100帖以内是初级用户，200帖是中级用户等等，网站试运行一段时间后，有可能改为发150帖以内是初级用户，500帖以内是中级用户，等等。修改会员等级表满足此要求。
　　
　　答案：
　　一、再建一个用户等级表（表名user_dj），它有两列：等级编号、等级名称，等级编号中存储1级、2级、3级等等，在等级名称中，显示实际的‘初级用户’，‘中级用户’等等。
　　二、缺点：每次需要显示用户的等级时，都需要做两个表的连接，这需要搜索两个表。优点：等级名称的变化将会非常方便。
　　三、将User_dj添加两列：DJGZXX（等级规则下限），DJGZSX（等级规则上限）。
　　
　　第 四 章 多行函数
　　一、仍以第一章中论坛用户基本信息表为例，统计当前总的用户数
　　
　　二、统计上海的会员有多少名
　　
　　三、统计北京最近半年内新增了多少会员
　　
　　四、由大到小排序显示各地区最近一年内注册会员的总发帖数、平均发帖数，不显示总发帖数量少于十万的地区。
　　
　　五、显示最近半年内注册的用户中，发帖数量超过1000的用户所在地区，以及这些地区目前各自总的会员数
　　
　　答案：
　　一：select count(*) from user_info;
　　二：select count(*) from user_info where 所在地区=’上海’;
　　三：select count(*) from user_info where 所在地区=’北京’ and 注册日期>=’2008-3-7’;
　　四：select 所在地区, sum(发帖数), avg(发帖数) from user_info where 注册日期>=’2007-9-7’ group by 所在地区 having sum(发帖数)>100000;
　　五：select 所在地区, count(*) from user_info where 注册日期>=’2008-3-7’ and 发帖数量>=1000 group by 所在地区;
　　
　　第 五 章 子查询
　　一、显示发帖数量最多的地区是哪个
　　
　　二、显示有多少个地区的会员发帖数量超过10000，以及这些地区会员总的发帖数在论坛中总的帖子数量的比例。
　　
　　
　　答案：
　　一：select 所在地区 from user_info group by 所在地区 having sum(发帖数)=(select max(sum(发帖数)) from user_info group by 所在地区);
　　二：select 所在地区,sum(发帖数量), sum(发帖数量)/(select sum(发帖数量) from use_info) from user_info where 发帖数>=10000 group by 所在地区;
　　
　　
　　第 六 章 替换变量与改变输出格式
　　一、如果当前用户中有表T1，下面的语句可以成功执行吗：
　　define aa='from'
　　define bb='t1'
　　select * &aa &bb;
　　
　　二、如果再定义替换变量cc：define cc='select' ，“&cc * &aa &bb;”可以执行成功吗？
　　
　　三、表有NAME列，用两种方法在显示此列时，在标头中显示汉字“姓名”。
　　
　　四、表有Address（地址）列，长度比较长，用命令将此列的显示宽度定为20个汉字。如果你已经将此列的显示宽度定为20个汉字了，对于列长度20个汉字的行，是否只显示前20个汉字？
　　
　　五、对于DEPT（部门）列所有连续重复的行，在显示时除第一个值外，其余重复的值显示为空。
　　
　　答案：
　　一：可以
　　二：不可以，因为语句的第一个单词不能是替换变量
　　三：方法1：select name 姓名 from 某表;
　　方法2：col name heading 姓名
　　select * from 某表;
　　四：col address for a20
　　如果长度超出，将会拐行显示
　　五：break on dept
　　
　　
　　第 七 章 操纵数据
　　一、从论坛用户基本信息表中取出用户ID、用户妮称和发帖数量三列，另外创建一个新表，新表中行数和原表中行一样多。
　　
　　二、回滚段除了可以保证事务可以回滚外，还有什么作用？
　　
　　三、事务如下几条语句：
　　A;
　　B;
　　Savepoint t1;
　　C;
　　D;
　　在此事务执行到语句D时，用户发出了“rollback;”命令，事务将回滚到哪个地方？
　　
　　回答：
　　一：create n_user_info as select 用户ID，用户妮称，发帖数量 from user_info;
　　二：一致读
　　三：回滚到事务开始前的状态
　　
　　第 八 章 数据类型
　　准备工作：
　　drop table tab1;
　　create table tab1(id number(10),name varchar2(20));
　　insert into tab1 values(1,'abc');
　　insert into tab1 values(2,'abc');
　　insert into tab1 values(3,'abc');
　　commit;
　　
　　一、为tab1表添加一个地址列：address，类型为varchar2，长度50个字节。
　　
　　二、将此地址列的默认值改为北京
　　
　　三、表中原有三行，地址列的值为NULL，将地址列值改为北京后，原有这三个NULL值，是否会被修改为北京。
　　
　　四、将姓名列的宽度改为10个字节，这个命令在姓名列不为空的情况下是否可以成功执行
　　
　　五、将ID列的宽度减少为number(5)，这个命令在ID列不为空的情况下是否可以成功执行
　　
　　答案：
　　一：alter table tab1 add address varchar2(50);
　　二：alter table tab1 modify address default ‘北京’;
　　三：不会，默认值只对新插入的行有效
　　四：alter table tab1 modify name varchar2(10);
　　此命令可以成功执行，因为现在Name列的值长度最长的是3个字节。
　　五：alter table tab1 modify id number(5);
　　此命令在ID列不为空的情况下不能执行
　　
　　第 九 章 约束
　　准备工作：
　　drop table tab1;
　　create table tab1(id number(10),name varchar2(20),addr varchar(30),);
　　insert into tab1(id,name) values(1,'abc');
　　insert into tab1(id,name) values(2,'abc');
　　insert into tab1(id,name) values(3,'abc');
　　commit;
　　
　　一、表约束和列约束的区别，只有哪一种约束必须是列级的。
　　
　　二、在Addr列添加非空约束，对于地址原来为空的行，将地址列改为汉字“无”。
　　
　　三、以前面章节中的论坛用户基本信息表为例，它有如下列：用户ID、妮称、密码、Email、所在地区、注册日期、用户分数、发帖数，其中用户ID和Email要求不能重复，请为此两列添加合适的约束。
　　
　　四、为用户基本信息表添加一个等级编号列，另创建一用户等级表，它有如下的列：等级编号、等级、发帖数量。要求等级表中有什么等级编号，用户基本信息表中才能有相应的等级编号。比如说等级表中有1,2,3,4四种等级，用户等级表也只能有这种等级的全部或部分。按照此要求，为这两个表添加合适的约束。
　　
　　五、如果要求删除等级表的相应等级时，也同时删除用户基本信息表中所有此等级的行，该如何设置。
　　
　　六、删除等级表，但保留用户基本信息表中的所有数据。
　　
　　七、论坛要求用户ID可以重复，用户妮称也可以重复，但ID和妮称加起来不能重复，请为此要求添加合适的约束。
　　
　　回答：
　　一：表约束是属于表，它可以包含多列。列约束属于列，它只能包含某一列。
　　二：update 某表 set addr=’汉字’ where addr is null;
　　alter table 某表 modify addr constraint 约束名 not null;
　　三：alter table user_info add constraint 约束名union(用户id);
　　alter table user_info add constraint 约束名union(email);
　　四：alter table user_info add 等级编号 number(5);
　　create table 等级表(等级编号 number(5), 等级 varchar2(50),
　　发帖数量 number(10) , constraint djbh primary key(等级编号) );
　　alter table user_info add constraint ud_djbh foreign key(等级编号) references 等级表(等级编号));
　　五：在User_info上添加外键时，使用on delete cascade，命令如下：
　　create table 等级表(等级编号 number(5), 等级 varchar2(50),
　　发帖数量 number(10) , constraint djbh primary key(等级编号) on delete cascade );
　　六：drop table 等级表 cascade constraints;
　　七：alter table user_info add constraint 约束名union(用户id，妮称);
　　
　　
　　第 十 章 视图
　　仍以论坛用户基本信息表为例，设表名为User_info，它有如下列：用户ID（不能为空）、妮称、密码、Email、所在地区、注册日期、用户分数、发帖数。
　　一、论坛中有一项功能，让普通用户可以按“用户ID”或“妮称”搜索其他会员的信息，但是在论坛用户基本信息表中，有一些特殊的内部管理员用户，这些用户的基本信息不希望被搜索到。而且，在用户搜索其他会员信息时，“密码”列不能被显示出来，如何解决这两点问题。
　　
　　二、我按照“发帖数”列排序创建了一个视图VW1，创建语句如下：
　　create or replace view vw1 as select * from User_info order by 发帖数列;
　　我是否可以向VW1视图中插入行？是否可以进行其他的DML操作
　　
　　三、由于经常需要查询不同的所在地区，我创建了如下视图：
　　create or replace view vw2 as select distinct 所在地区列 from User_info;
　　我可以在此视图中删除地区为某地的行吗？
　　
　　四、我创建了一个视图VM3，创建语句如下：
　　create or replace view vw3 as select 妮称，所在地区，注册日期，发帖数，用户分数 from User_info;
　　我可以向此视图插入行吗？为什么？
　　
　　五、为了使注册日期列的输出结果固定为年－月－日格式，我创建了如下视图：
　　create or replace view vm4 as select 用户ID,to_char(注册日期,'yyyy-mm-dd hh24:mi:ss') rq from User_info;
　　我们可以对此视图做什么样的DML操作？为什么？
　　
　　六、如果基本表被删除了，视图会随之被删除吗？
　　
　　七、如果有表TAB1，它有如下的行：
　　SQL> select * from tab1;
　　ID NAME
　　---------- ----------
　　1 ICOL$
　　2 I_USER1
　　3 CON$
　　4 UNDO$
　　5 C_COBJ#
　　6 I_OBJ#
　　7 PROXY_ROLE
　　8 I_IND1
　　9 I_CDEF2
　　10 I_PROXY_RO
　　已选择10行。
　　我发出如下语句：select rownum, id , name from tab1 where id>=5; 显示结果如下：
　　ROWNUM ID  NAME
　　-------------------- -------- -----------------
　　（_______） 5   C_COBJ#
　　（_______）  6   I_OBJ#
　　（_______）  7   PROXY_ROLE
　　（_______）  8   I_IND1
　　（_______）  9   I_CDEF2
　　（_______）  10   I_PROXY_RO
　　已选择6行。
　　请在括号中的横线上填上ROWNUM列的显示值。
　　
　　八、显示发帖数最多的十名会员的信息
　　
　　思考题：
　　一、显示发帖数量最多的第11到20名会员信息。用两种方法完成此题，并比较哪种方法更好
　　
　　二、向User_info表添加一列，“是否允许其他用户看到我的Email”，列名为find_email。以User_info表为基表创建一个视图，当此列为是时，查询此视图时，将会正常输出用户的Email。如果此列为否，查询视图时，用户Email列将显示为“该用户没有公布Email”。
　　
　　答案：
　　一：创建一个视图，视图选择语句的条件是where 用户类型 <> ‘内部管理员’，另外，视图中不包括“密码”列。用户搜索信息时，只让他们针对此视图进行搜索，这样他们就查不出来“内部管理员”和密码列了。
　　二：不可以。可以更新所有行的所有列（包括排序列），也可以删除。
　　三：不可以。创建视图时回有Dintisct，将不能对视图进行任何DML操作
　　四：不可以。因为用户ID列有非空约束，而视图中不包括此列。对视图插入，将为此列添加NULL值。这将违反用户ID列的非空约束。
　　五：视图包含虚拟列，对视图不可以进行任何DML操作
　　六：不会
　　七：结果依次为1、2、3、4、5、6
　　八：select * from (select * from user_info order by 发帖数 desc) where rownum<=10;
　　
　　思考题一：
　　col col1 noprint
　　select * from (select rownum col1,user_info.* from (select * from user_info order by 发帖数量 desc) where rownum<=20) where col1>=10;
　　还有一种方法，就是先从大到小排序取前20名，再对排序结果从小到大排序取前10名。这种方法的缺点是需要两次排序。
　　
　　思考题二：
　　alter table user_info add find_email varchar2(2);
　　create or replace view vw4 as select 用户ID，妮称，所在地区，注册日期，发帖数，用户分数，case find_email when ‘是’ then email列 when ‘否’ then ‘该用户没有公布Email’ end case 用户Email from User_info;
　　或是用Decode实现：
　　create or replace view vw4 as select 用户ID，妮称，所在地区，注册日期，发帖数，用户分数，decode(find_email,‘是’,email列, ‘否’, ‘该用户没有公布Email’) 用户Email from User_info;
　　
　　第 十一 章 序列、索引和同义词
　　一、在User_info表中添加一个num列，使用序列将此列的值定为2、4、6、8等偶数。并且最大到100，100之后重新从2开始。
　　
　　二、为了提高User_info表中，按用户ID列的查找用户的速度，在用户ID列上创建索引User_info_id。
　　
　　三、请问User_info_id索引中除了记录有对应表User_info中用户ID列的值外，还必须有什么信息？这项信息的作用是什么？
　　
　　四、很多用户经常以妮称列为条件进行等值查寻，因为妮称中可以包含大、小写字母，当查找妮称为“abc”的行，用户希望可以将“ABC”、“Abc”、“ABc”等各种大小写字母的组合都查找出来。因此，最常见的查询妮称的条件形式如下：upper(妮称)=“ABC”，或lower(妮称)＝“abc”。为了提高在妮称列中查询的速度，在妮称列上创建索引。
　　
　　五、删除表会删除相应索引吗？
　　
　　六、UPLOOKING用户下有一个TAB2表，其他的用户要想访问它必须UPLOOKING.TAB2，十分不方便，如何使其它用户也能直接使用TAB2访问UPLOOKING下的TAB2表。
　　
　　七、有一公有同义词TAB2，它代表UPLOOKING下的TAB2表，在USER1用户下也有一TAB2表，在USER1下使用如下的语句：select * from tab2，它将访问哪个用户下的TAB2表。
　　
　　答案：
　　一：create sequence seq1 increment by 2 maxvalue 100 cycle;
　　altet table user_info add num number(4);
　　update user_info set num=seq1;
　　二：create index user_info_id on user_info(用户ID);
　　三：索引中除了列值外，必须还有索引条目对应行的地址。
　　四：create index user_info_nc on user_info(upper(妮称)); 或：
　　create index user_info_nc on user_info(lower(妮称));
　　五：会
　　六：create public synonym tab2 for uplooking.tab2;
　　七：访问的是USER1下的TAB2表。当使用某一串字符访问表时，Oracle将先在当前用户的表中查找是否存在。如果当前用户中不存在，再到公有同义词中查找是否存在，如果也不存在，就会报出“对象不存在”错误。
　　
　　第 十二 章 用户、权限和角色
　　一、创建USER2用户，密码是12345
　　
　　二、将USER2用户的密码改为ABCDE
　　
　　三、如何让USER2用户能够连接数据库
　　
　　四、如何让USER2用户可以创建表
　　
　　五、将能够连接数据库和能够在表空间中创建表的权限授于一个角色，方便以后授于新用户
　　
　　六、对象权限和系统权限的区别是什么
　　
　　七、因应用程序的需求，USER2用户需要能够向USER1用户中的TAB2表中插入行，为USER2分配合适的权限。
　　
　　八、用户USER2将向USER1用户中TAB2表中插入行的权限又授于了UPLOOKING用户。用户USER1将USER2对TAB2插入的权限撤消了，USER2用户授于UPLOOKING的向TAB2插入的权限是否随之被撤消。
　　
　　答案：
　　一：create user user2 identied by “12345”; --注意，密码外的双引号不可省略。如果密码是字符，才可以省略双引号
　　二：alter user user2 identied by ABCDE
　　三：为USER2授予Create session权限：grant create session to user2;
　　四：授予它使用表空间中空间的权限： grant unlimited tablespace to user2;
　　五：create role role1;
　　grant create session, unlimited tablespace to role1;
　　六：
　　七：grant insert on tab2 to user2;
　　八：会
　　(对象权限会级联撤消，系统权限不会级联撤消）