071-新增2

19. Evaluate the commands issued by the DBA:
    1 - CREATE ROLE mgr;
    2 - GREATE CREATE TABLE, SELECT ON oe.orders TO mgr;
    3 - GRANT mgr, create table TO SCOTT;
    Which statement is true regarding the execution of the above commands?
    A) Statement1 would not execute because the WITH GRANT option is missing
    B) Statement 1 would not execute because the IDENTIFIED BY clause is missing.
    C) Statement3 would not execute because role and system privileges cannot be granted together in a single CRANT statement.
    D) Statement 2 would not execute because system privileges and object privileges cannot be granted together in a single GRANT command.

create table 属于系统权限，SELECT ON oe.orders TO mgr 属于角色权限，两者不能放 在一个 grant 语句中；而角色权限，和对象权限可以放在同一个 grant 语句中

20. You need to display the first names of all customers from the CUSTOMERS table that contain the character ‘e’ and have the character ‘a’ in the second last position. Which query would give the required output?
    A) SEIECT cust_ first_name FROM customers WHERE INSTR (cust_ first_name, 'e ’ )<>0 AND SUBSTR(cust_ first_name, -2, 1)=‘a’;
    B)SEIECT cust_ first_name FROM customers WHERE INSTR (cust_ first_name, ‘e ’ )<>’ ’ AND SUBSTR(cust_ first_name, -2, 1)=‘a’;
    C) SEIECT cust_ first_name FROM customers WHERE INSTR (cust_ first_name, 'e ’ ) IS NOT NULL AND SUBSTR(cust_ first_name, -2, 1)=‘a’;
    D)SEIECT cust_ first_name FROM customers WHERE INSTR (cust_ first_name, 'e ’ )<>0 AND SUBSTR(cust_ first_name, LENGTH(cust_ first_name,-2)=‘a’;

要从 CUSTOMERS 表中显示所有 customers 的 first names，名字中要包含 e，并且倒 数第二个字符要包含 a A 选项正确，INSTR 如果不包含字符则返回 0，不等于 0，说明包含了 e 字符，SUBSTR 可以从倒数第二个字符开始截取一个字符。 B 选项不正确，INSTR 的结果<>’’，永远为 false。 C 选项不正确，INSTR 的结果不可能为 null。 D 选项不正确，SUBSTR 不是截取的倒数第二个字符。

21. Examine these SQL statements that are executed in the given order: CREATE TABLE emp (emp_ no NUMBER(2) CONSTRAINT emp_emp_no_pk PRTMARY KEY, ename VARCHAR2 (15), Salary NUMBER(8,2), mgr_ no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp(emp no));
    ALTER TABLE emp DISABLE CONSTRAINT emp_ emp_no_ pk CASCADE;
    ALTER TABLE emp ENABLE CONSTRAINT emp_ emp_ no_ pk;
    What will be the status of the foreign key EMP _MGR _ FK?
    A) It will be enabled and deferred.
    B) It will be enabled and immediate.
    C) It will remain disabled and can be re- enabled manually.
    D) It will remain disabled and can be enabled only by dropping the foreign key constraint and re-creating it.

禁用后又开启主键，外键需要手动开启