【ORACLE 】ORACLE OCP 071考点记录01

1.UNION查询语句

例题：
Which two are true about a SQL statement using SET operators such as UNION?
A) The data type of each column returned by the second query must be implicitly convertible to the data type of the corresponding column returned by the first query.
B) The data type of each column returned by the second query must exactly match the data type of the corresponding column returned by the first query.
C) The number, but not names, of columns must be identical for all SELECT statements in the query.
D) The data type group of each column returned by the second query must match the data type group of the corresponding column returned by the first query.
E) The names and numbers of columns must be identical for all SELECT statements in the query.
答案：AC
考点：
UNION查询语句中，union连接的两个 select 查询结果数必须一致，但是对于数据类型并没有强制要求，前提是可隐式转换(implicitly convertible)为同一种类型即可

2.笛卡尔乘积 cross join

例题：
Examine this query:
SELECT 2 FROM dual d1 CROSS JOIN dual d2 CROSS JOIN dual d3;
What is returned upon execution?
A) 0 rows
B) an error
C) 8 rows
D) 6 rows
E) 1 rows
F) 3 rows
考点:
笛卡尔乘积的定义就是，不加任何的where条件，cross join 左边有 M 行，右边有 N 行，得出M*N数量的结果集
如=>select * from student,class
(等价于 select * from student s cross join class c;)

注意：如果cross join加上where s.classid=c.id条件，会产生跟自连接一样的结果：
1 --加上条件，产生跟自连接一样的结果。
2 select * from student s cross join class c where s.classid=c.id;
自连接结果集的cross join连接结果

3.DEFINE 和 SET VERIFY ON/OFF

例题:
Examine this query:
SELECT employee_id, first_name, salary FROM employees WHERE hire_date > ‘&1’;
Which two methods should you use to prevent prompting for a hire date value when this query is executed?
A) Use the DEFINE command before executing the query.
B) Store the query in a script and pass the substitution value to the script when executing it.
C) Replace’ ‘&1’ with '&&1’in the query.
D) Execute the SET VERIFY OFF command before executing the query.
E) Use the UNDEFINE command before executing the query.
F) Execute the SET VERIFY ON command before executing the query.
答案：AB
考点:
1 define定义一下常量，然后用&或者&&符号进行引用,在sqlplus中开启关闭
开启：set define on
关闭：set define off
使用：

2 当在sqlplus中运行的sql语句中有替代变量（以&或&&打头）的时候， set verify(或ver) on/off可以设置是否显示替代变量被替代前后的语句。
用法：

4.ROLLBACK语句回滚范围

例题：
The books table has 100 rows.
Examine this sequence of statements issued in a new session:

INSERT INTO books VALUES (‘ADV112’, ‘Adventures of Tom Sawyer’, NULL, NULL)
SAVEPOINT a;
DELETE FROM books;
ROLLBACK TO SAVEPOINT a;
ROLLBACK;

Which two statements are true?
A) The first ROLLBACK command restores the 101 rows that were deleted and commits the inserted row.
B) The first ROLLBACK command restores the 101 rows that were deleted, leaving the inserted row still to be committed.
C) The second ROLLBACK command does nothing.
D) The second ROLLBACK command undoes the insert.
E) The second ROLLBACK command replays the delete.
答案：BD
考点:
1 savapoint 定义一个保存点，rollback可以将数据库表格状态回退到这个保存点，之前未提交的插入等操作可以继续 commit
2 rollback 如果不加保存点回退，则会回退到最近 commit 的状态

5.MERGE INTO 的使用

我们操作数据库的时候，有时候会遇到insert Or Update这种需求。
如果数据库中存在数据就update，如果不存在就insert。
以前的时候，需要额外select查询一下，如果有数据就update，如果没有数据就insert。
而现在Orcale数据库都提供了 MERGE 方法来处理这种需求。
MERGE 命令使用一条语句从一个或者多个数据源中完成对表的更新和插入数据。
MERGE 语法：
MERGE INTO [your table-name] [rename your table here]
USING ( [write your query here] )[rename your query-sql and using just like a table]
ON ([conditional expression here] AND […]…)
WHEN MATCHED THEN [here you can execute some update sql or something else ]
WHEN NOT MATCHED THEN [execute something else here ! ]

考点：
1.语法是否完整，如 using 后面是否跟了 on 子句
2.如果出现delele，则delete 只能跟 update 一起使用，同时where只能出现一次，如果update 使用了where，delete后面的where就无效了，如：

MERGE INTO new_prices n
USING (SELECT * FROM products WHERE cost > 150) p
ON (n.prod_id = p.prod_id)
WHEN MATCHED THEN
  UPDATE SET n.price = p.cost*.01
  DELETE WHERE (p.cost < 200);

如果上面sql中没有UPDATE SET n.price = p.cost*.01变成：

MERGE INTO new_prices n
USING (SELECT * FROM products WHERE cost > 150) p
ON (n.prod_id = p.prod_id)
WHEN MATCHED THEN
  DELETE WHERE (p.cost < 200);

就是错误的

6. 操作符

例题：
Examine the description of the SALES1 table:

SALES2 is a table with the same description as SALES1.
Some sales data is duplicated in both tables.
You want to display the rows from the SALES1 which are not present in the SALES2 table.
Which set operator generates the required output?
A)UNION ALL
B) MINUS
C) INTERSECT
D)SUBTRACT
E)UNION
答案：B
考点：

1.UNION ALL是全集

结果是：

2.UNION 是联集

结果是：

3.INTERSECT 是交集

结果是：

4.MINUS 是差集

结果是

PS:SUBTRACT应该不是 Oracle里面的函数或关键字

7.Oracle 权限

例题：
Which two are true about the WITH GRANT option clause?
A) The grantee can grant the object privilege to any user the database, with or without including this option.
B) It can be used to pass on privileges to the users by the grantee
C) The grantee must have the GRANT ANY OBJECT PRIVILEGE system privilege to use this option.
D) It can be used for system and object privileges
E) It can be used when granting privileges to roles.
F) It cannot be used to pass on privileges to PUBLIC by the grantee.
答案：BC
考点：
本题考查的是
with grant option这个子句表示该用户可以将自己拥有的权限授予给别人，会取消级联
所以A是错的，必须要有with grant option这个权限才能将自身的权限赋给其他用户
B正确，拥有这个权限，就可以传递自身的权限，
C正确，没有with grant option权限，执行
GRANT ANY OBJECT PRIVILEGE to scott 就会报错
D错误，因为只有对象权限授权的时候才有WITH GRANT OPTION选项
E错误，使用grant select on scott.books to lxy with grant option对角色lxy赋权限的时候，会报错：

F错误，登录li用户，li用户具有scott.books表的所有权限，再使用grant all privileges on scott.books to public 是可以执行成功的

权限分类：

1.系统权限

系统权限就是用户在当前的用户架构下所具有的权限。
如：create table，UNLIMITED TABLESPACE等
查询当前用户所具有的权限：
SELECT * FROM user_sys_privs;

2.角色权限

角色就是一类权限的分组，所以给用户分配角色，就是给用户分配权限。
查询当前用户拥有的角色：
SELECT * FROM USER_ROLE_PRIVS;

3.对象权限

对象权限指的是其它拥有用户的对象的权限。其它用户对象的权限包括：SELECT,DELETE,UPDATE,ALTER,INSERT,INDEX,REFERENCES,FLASHBACK,DEBUG,QUERY REWRITE,ON COMMIT REFRESH；
注意：其它用户对象的权限没有drop的权限。
查询当前用户拥有的对象权限：
SELECT * FROM USER_TAB_PRIVS;

PS:授予者 grantee拥有grantor下表的所有权限

4.grant授权

1.授予用户connect、resource角色权限。一般创建应用程序开发用户可以授予这两个角色的权限。
GRANT CONNECT,RESOURCE TO li;
2.授予用户lxy用户li下person表的SELECT,DELETE,UPDATE,INSERT权限，授予具体的对象权限是对于权限严格控制的一种方案。
GRANT SELECT,DELETE,UPDATE,INSERT ON li.person TO lxy;
3.授予用户chenmh用户zhang下person表的所有权限
GRANT ALL PRIVILEGES ON li.person to lxy;

5.REVOKE回收权限

1.回收角色权限

REVOKE CONNECT,RESOURCE FROM lxy;
2.回收系统权限

REVOKE CREATE FROM lxy;
3.回收用户对象权限,回收zhang用户下person表的所有权限，如果是单个授予的权限需要单个的收回

REVOKE ALL PRIVILEGES ON li.person FROM lxy;

更多权限说明参考：https://www.cnblogs.com/chenmh/p/6001977.html

8.Oracle数据字典 DATA DICTIONARY

例题：
Which two are true about the data dictionary?
A) All user actions are recorded in the data dictionary.
B) The data dictionary is constantly updated to reflect changes to database objects, permissions, and data.
C) The sys user owns all base tables and user-accessible views in the data dictionary.
D) Base tables in the data dictionary have the prefix DBA_.
E) All users have permissions to access all information in the data dictionary by default
答案：BC
答案A,数据字典是数据库中最重要的组成部分，他提供了数据库的一些系统信息。（静态信息，常规的信息）（基表）,但是并没有记录用户的所有行为
答案D:查看所有基表语句：select * from v$fixed_table
dba_开头包含数据库实例的所有对象信息
答案E:明显是错误的，不同的用户拥有不同的数据字典访问权限
显示当前用户可以访问的数据字典的视图(不同的用户有不同的权限)
*Select from dict where comments like ‘%grant%;’
官方解释：
about dictionary？
1.The data dictionary is constantly updated to reflect changes to database objects, permissions, and data.
2.The SYS user owns all base tables and user-accessible views in the data dictionary.