OCP SQL 课堂实验-CSDN博客

273428389

set long 10000
set pagesize 100
select dbms_metadata.get_ddl('TABLE','EMPLOYEES','HR')
from dual;

insert into t(id,name) values(null,'lcb');
commit;
select id+1 from t;

set linesize 250
set pagesize 200
col last_name format a20
SELECT last_name, job_id, salary, commission_pct
FROM   employees;

select last_name,12*salary*commission_pct
from employees;

SELECT   last_name||job_id AS "Employees"
FROM    employees;

select department_id, manager_id from employees;
select distinct department_id, manager_id,salary from employees;
50,10
50,20

select distinct department_id, manager_id from employees;

select distinct (department_id), manager_id from employees;
select distinct (department_id, manager_id) from employees;
select unique (department_id, manager_id) from employees;
select unique department_id, manager_id from employees;

set pagesize 200
set long 5000
select dbms_metadata.get_ddl('TABLE','T') from dual;

问题：
You need to produce a report where each customer's credit limit has been incremented by $1000.
In the output, the customer's last name should have the heading Name and the incremented credit limit
should be labeled New Credit Limit.
The column headings should have only the first letter of each word in uppercase .
Which statement would accomplish this requirement?
A. SELECT cust_last_name Name, cust_credit_limit + 1000
"New Credit Limit"
FROM customers;
B. SELECT cust_last_name AS Name, cust_credit_limit + 1000
AS New Credit Limit
FROM customers;
C. SELECT cust_last_name AS "Name", cust_credit_limit + 1000
AS "New Credit Limit"
FROM customers;
D. SELECT INITCAP(cust_last_name) "Name", cust_credit_limit + 1000 INITCAP("NEW CREDIT LIMIT")
FROM customers;

View the Exhibit and examine the data in the CUSTOMERS table.
Evaluate the following query:
SQL> SELECT cust_name AS "NAME", cust_credit_limit/2 AS MIDPOINT,MIDPOINT+100 AS
"MAX LOWER LIMIT"
FROM customers;
The above query produces an error on execution.
What is the reason for the error?
A. An alias cannot be used in an expression.
B. The a lias NAME should not be enclosed with in double quotation marks .
C. The MIDPOINT+100 expression gives an error because CUST_CREDIT_LIMIT contains NULL values.
D. The a lias MIDPOINT should be enclosed with in double quotation marks for the
CUST_CREDIT_LIMIT/2 expression .

set head off
set pagesize 5000
select 'alter database datafile '||file_id||' offline;'
from dba_data_files

vi $ORACLE_HOME/sqlplus/admin/glogin.sql
stty erase ^H

select distinct salary,hire_date from employees;
select count(distinct salary) from employees;
select distinct salary,hire_date from employees;
select count(distinct salary||nhire_date) from employees;

SELECT department_name || q'{ Department's [Manager] Id: }'
       || manager_id
       AS "Department and Manager"
FROM departments;

conn / as sysdba; 用sysdba连接到数据库
startup;

conn scott/tiger;
conn hr/hr;
sqlplus / as sysdba

sho user
select owner from dba_tables where table_name='EMPLOYEES';

select *from tab;

第二章试验
select *from nls_database_parameters where parameter like 'NLS%'

SELECT last_name, salary
FROM   employees
WHERE salary BETWEEN 2500 AND 3500 ;
等价于
SELECT last_name, salary
FROM   employees
WHERE salary >=2500 AND salary <=3500 ;

select last_name from employees where last_name between 'A' and 'Z';

SELECT employee_id, last_name, salary, manager_id
FROM   employees
WHERE manager_id IN (100, 101, 201) ;

SELECT employee_id, last_name, salary, manager_id
FROM   employees
WHERE (manager_id =100) or (manager_id =101) or (manager_id =201) ;

SELECT last_name
FROM   employees
WHERE last_name LIKE '_o%' ;

SA_
conn hr/hr;
SELECT employee_id, last_name, job_id
FROM   employees WHERE job_id LIKE 'SA\_%' ESCAPE '\';

SELECT last_name, manager_id
FROM   employees
WHERE manager_id IS NULL ;

SELECT last_name, job_id
FROM   employees
WHERE job_id
       IN ('IT_PROG', 'ST_CLERK', 'SA_REP') ;
SELECT last_name, job_id
FROM   employees
WHERE (job_id ='IT_PROG') or (job_id ='ST_CLERK') or (job_id ='SA_REP')

not in语句中null值的问题
drop table t1;
create table t1 (id number,name varchar2(200));
insert into t1 values(1,'aaa');
insert into t1 values(2,'bbb');
insert into t1 values(3,'ccc');
insert into t1 values(500,null);
commit;

drop table t2;
create table t2 (id number,name varchar2(200));
insert into t2 values(1,'aaa');
insert into t2 values(2,'ddd');
commit;

select *from t2 where name not in (select name from t1);

1).　NULL AND TRUE
SQL> SELECT ''NLLL AND TRUE ' FROM dual WHERE null >0 and 1=1;
no rows selected
SQL> SELECT 'NLLL AND TRUE ' FROM dual WHERE null >0 and 1=1;

2). NOT (NULL AND TRUE)
SQL> SELECT ' NOT (NULL AND TRUE)' FROM dual WHERE not (null >0 and 1=1);
还是null

3). NULL AND FALSE
SQL> SELECT ' NULL AND FALSE ' FROM dual WHERE null >0 and 1=2;
2和3互为转换

4).NULL OR TRUE
SQL> SELECT ' NULL OR TRUE ' FROM dual WHERE null >0 or 1=1;
------------------------
NULL OR TRUE
1 rows selected

5).NOT (NULL and FALSE)
SQL> SELECT ' NOT (NULL AND FALSE) ' FROM dual WHERE not (null >0 and 1=2);
------------------------
NOT (NULL AND FALSE)
1 rows selected

算术优先级规则
1 算术运算符
2 连接运算符
3 比较条件
4 IS [NOT] NULL LIKE [NOT] IN
5 [NOT] BETWEEN
6 NOT 逻辑条件
7 AND逻辑条件
8 OR逻辑条件

提高实验(较难)
create table t1 as select *from dba_objects;

create table t2 as select *from t1 where rownum<3000;

select * from t1 where t1.object_id not in (select object_id from t2 where object_id is not null);
alter table t2 modify object_id not null;
select * from t1 where t1.object_id not in (select object_id from t2);
alter table t1 modify object_id not null;
select * from t1 where t1.object_id not in (select object_id from t2);

在学习到not exists时，请回头再看这段代码，先预热
select *from t2 where name not in (select name from t1);

select *from t2 where not exists (select 1 from t1 where nvl(t1.name,t2.name)=t2.name);
select *from t2 where not exists (select 1 from t1 where lnnvl(t1.name<>t2.name));

select last_name, manager_id from employees order by manager_id ;
select last_name, manager_id from employees order by manager_id desc;

select last_name, manager_id from employees order by manager_id nulls first;

SELECT employee_id, last_name, salary, department_id
FROM   employees
WHERE employee_id = &employee_num ;

define column_name=salary
SELECT   employee_id, last_name, job_id, &&column_name
FROM     employees
ORDER BY &column_name ;

问题：
Examine the structure of the SHIPMENTS table:
name Null Type
PO_ID NOT NULL NUMBER(3)
PO_DATE NOT NULL DATE
SHIPMENT_DATE NOT NULL DATE
SHIPMENT_MODE VARCHAR2(30)
SHIPMENT_COST NUMBER(8,2)
You want to generate a report that displays the PO_ID and the penalty amount to be paid if the
SHIPMENT_DATE is later than one month from the PO_DATE. The penalty is $20 per day.
Evaluate the following two queries:
SQL> SELECT po_id, CASE
WHEN MONTHS_BETWEEN (shipment_date,po_date)>1 THEN
TO_CHAR((shipment_date - po_date) * 20) ELSE 'No Penalty' END PENALTY
FROM shipments;
decode(id ,1,'low',2,'medium','high')
SQL>SELECT po_id, DECODE
(MONTHS_BETWEEN (po_date,shipment_date)>1,
TO_CHAR((shipment_date - po_date) * 20), 'No Penalty') PENALTY
FROM shipments;
Which statement is true regarding the above commands?
A. Both execute successfully and give correct results.
B. Only the first query executes successfully but gives a wrong result.
C. Only the first query executes successfully and gives the correct result.
D. Only the second query executes successfully but gives a wrong result.
E. Only the second query executes successfully and gives the correct result.

第三章单行函数
函数可以可以实现以下功能：
⊙ 数据计算
⊙ 修改单个数据项
⊙ 处理成组行的输出
⊙ 指定日期和数字的显示格式
⊙ 转换列数据类型

单行函数的用途
⊙ 用于维护数据操作
⊙ 读输入参数并返回一个的值，返回值可能与引用数据类型不同
⊙ 函数是在每行上操作
⊙ 每行有一行返回值，一个函数里可以嵌套另一个函数
⊙ 可以修改数据类型
⊙ 能嵌套
⊙ 可以接受多个参数
⊙ 可以用在SELECT、WHERE、ORDER BY子句中，也可以嵌套

SELECT employee_id, last_name, department_id
FROM   employees
WHERE last_name = 'higgins';

SELECT employee_id, last_name, department_id
FROM   employees
WHERE LOWER(last_name) = 'higgins';

SELECT INSTR('CORPORATE FLOOR','OR', 3, 2)
"Instring" FROM DUAL;

SELECT employee_id,
TO_CHAR(TRIM(LEADING 0 FROM hire_date))
FROM employees
WHERE department_id = 60;

SELECT employee_id, CONCAT(first_name, last_name) NAME,
       job_id, LENGTH (last_name),
       INSTR(last_name, 'a') "Contains 'a'?"
FROM   employees
WHERE SUBSTR(job_id, 4) = 'REP';

Oracle 内部使用数字存储日期: 世纪,年,月,日,小时,分钟,秒。
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select sysdate,trunc(sysdate) from dual;
create table t(d date) ;
insert into t select trunc(sysdate) from dual;
commit;
select d,dump(d) from t;
默认的日期格式是 DD-MON-RR.
RR主要解决2000年问题。

提高实验(较难),to_char函数在第四章讲到，先预热
drop table t;
create table t
as
select created from all_objects;

insert into t select * from t;

执行几次
/
commit;

create index ind_created on t(created);
exec dbms_stats.gather_table_stats( user, 'T' );
set timing on

第1种方法：
select count(*)
from t
where to_char(created,'yyyy') = '2005';

第二种方法：
select count(*)
from t where trunc(created,'y') = to_date('01-jan-2005','dd-mon-yyyy');

第三种方法
select count(*) from t
where created >= to_date('01-jan-2005','dd-mon-yyyy')
and created < to_date('01-jan-2006','dd-mon-yyyy');

SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS
FROM   employees
WHERE department_id = 90;

Months_between，如果第一个日期迟，返回正数，如果第一个早，返回负数。
select months_between(sysdate,'2012-09-01 00:00:00') from dual;

select add_months(sysdate,6) from dual;

NEXT_DAY是可以获得下一个指定礼拜的日期
select next_day(sysdate,'friday') from dual;

LAST_DAY是可以计算这个月的最后一天
select last_day(sysdate) from dual;

问题
Which two statements are true regarding single row functions? (Choose two.)
A. They accept only a single argument.
B. They can be nested only to two levels.
C. Arguments can only be column values or constants.
D. They always return a single result row for every row of a queried table.
E. They can return a data type value different from the one that is referenced.

第四章类型转换
隐式转换
显式转换
select employee_id,first_name
from employees
where hire_date>'01-JAN-90';

select '900.12'+25.01 from dual;

select employee_id,to_char(hire_date,'MM/YY') month_hired
from employees
where last_name='Higgins';

中国人习惯写法
select employee_id,to_char(hire_date,'YYYY-MM-DD') month_hired
from employees
where last_name='Higgins';

select employee_id,to_char(hire_date,'fmYYYY-MM-DD') month_hired
from employees
where last_name='Higgins';

select to_char(salary,'L99999,999') salary from employees;
select to_number('A','xx') from dual;

SELECT last_name, hire_date
FROM   employees
WHERE hire_date = TO_DATE('May 24, 1999', 'Month DD, YYYY');

SELECT last_name, hire_date
FROM   employees
WHERE hire_date = TO_DATE('May 24, 1999', 'fxMonth DD, YYYY');

补充：
select to_char( to_date(2012,'J'),'Jsp') from dual;

select to_char(to_date('2012-11-21','yyyy-mm-dd'),'day') from dual;

select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual ;

select TO_CHAR(SYSDATE,'DDD'),sysdate from dual ;

select length(case when manager_id is null then 0 else manager_id end) from employees;

lnnvl
sys_op_map_nonnull

oNVL (expr1, expr2)
如果表达式1为空，则返回表达式2
oNVL2 (expr1, expr2, expr3)
如果表达式1为非空，返回表达式2，否则返回表达式3
oNULLIF (expr1, expr2)
如果表达式1等于表达式2，返回null，否则返回表达式1
oCOALESCE (expr1, expr2, ..., exprn)
返回表达式列表中第一个不为空的表达式

col an_sal format '999,999'
SELECT last_name, salary, NVL(commission_pct, 0),
   (salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL
FROM employees;

SELECT last_name, salary, commission_pct,
       NVL2(commission_pct,
            'SAL+COMM', 'SAL') income
FROM   employees WHERE department_id IN (50, 80);

SELECT last_name, employee_id,
COALESCE(TO_CHAR(commission_pct),TO_CHAR(manager_id),
   'No commission and no manager')
FROM employees;

SELECT last_name, job_id, salary,
       CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
                   WHEN 'ST_CLERK' THEN 1.15*salary
                   WHEN 'SA_REP'   THEN 1.20*salary
       ELSE      salary END     "REVISED_SALARY"
FROM   employees;

conn oe/oe;

SELECT cust_last_name,
CASE WHEN credit_limit <=100 THEN 'Low'
WHEN credit_limit <=5000 THEN 'High'
ELSE 'Medium' END
FROM customers;

SELECT AVG(CASE WHEN e.salary > 2000 THEN e.salary
ELSE 2000 END) "Average Salary" from employees e;

提高实验
create or replace function f return number
as
begin
          return 1/0;
end;
/

select nvl(0,f()) from dual;
select nvl2(0,'not null',f()) from dual;
select coalesce(0,f()) from dual;
select decode(0,0,'不执行f()',f()) from dual;

问题：
Evaluate the following two queries:
SQL> SELECT cust_last_name, cust_city
FROM customers
WHERE cust_credit_limit IN (1000, 2000, 3000);
SQL> SELECT cust_last_name, cust_city
FROM customers
WHERE cust_credit_limit = 1000 OR cust_credit_limit = 2000 OR
cust_credit_limit = 3000;
Which statement is true regarding the above two queries?
A. Performance would improve in query 2.
B. Performance would degrade in query 2.
C. There would be no change in performance.
D. Performance would improve in query 2 only if there are null values in the CUST_CREDIT_LIMIT
column.

第五章分组函数
count(distinct )

select distinct salary,hire_date from employees;
select count(distinct salary) from employees;
select distinct salary,hire_date from employees;
如何求有多少唯一的salary,hire_date

SELECT   department_id, job_id, SUM(salary)
FROM     employees
WHERE   department_id > 40
GROUP BY department_id, job_id
ORDER BY department_id;

SELECT   department_id, AVG(salary)
FROM     employees
GROUP BY department_id
HAVING   AVG(salary) > 8000;

select avg (case salary is null then 0 else salary) from employees

第六章连接
SELECT l.city, d.department_name
FROM   locations l JOIN departments d USING (location_id)
WHERE location_id = 1400;

SELECT employee_id, city, department_name
FROM   employees e
JOIN   departments d
ON     d.department_id = e.department_id
JOIN   locations l
ON     d.location_id = l.location_id;

SELECT worker.last_name emp, manager.last_name mgr
FROM   employees worker JOIN employees manager
ON    (worker.manager_id = manager.employee_id);

conn hr/hr;
SELECT e.last_name, e.department_id, d.department_name
FROM   employees e LEFT OUTER JOIN departments d
ON   (e.department_id = d.department_id) ;

SELECT e.last_name, d.department_id, d.department_name
FROM   employees e RIGHT OUTER JOIN departments d
ON    (e.department_id = d.department_id) ;

SELECT e.last_name, d.department_id, d.department_name
FROM   employees e FULL OUTER JOIN departments d
ON   (e.department_id = d.department_id) ;

问题：
Which two statements are true regarding the USING and ON clauses in table joins? (Choose two.)
A. Both USING and ON clauses can be used for equijoins and nonequijoins.
B. A maximum of one pair of columns can be joined between two tables using the ON clause.
C. The ON clause can be used to join tables on columns that have different names but compatible data
types.
D. The WHERE clause can be used to apply additional conditions in SELECT statements containing the
ON or the USING clause.

user_objects

第七章子查询
SELECT employee_id, job_id
FROM   employees
INTERSECT
SELECT employee_id, job_id
FROM   job_history;

select distinct e.employee_id, e.job_id
from employees e,job_history j
where (e.employee_id=j.employee_id) and (e.job_id=j.job_id);

select 1,2,4 from dual
union
select 1,2,3 from dual;

a (1,2,3)
b (1,2)
a minus b
a intersect b

SELECT last_name, job_id
FROM   employees
WHERE job_id =
               (SELECT job_id
                FROM   employees
                WHERE employee_id = 141);

SELECT * FROM dept
WHERE NOT EXISTS
(SELECT null FROM emp
WHERE emp.deptno=dept.deptno);

set autot traceonly
select deptno
from dept
where deptno not in (select deptno from emp);

SELECT * FROM dept
WHERE NOT EXISTS
(SELECT null FROM emp
WHERE nvl(emp.deptno,dept.deptno)=dept.deptno);

1)UNION中两个集合的列的顺序和个数,类型都必须匹配
2)UNION操作合并重复行是基于整行
3)两个NULL值作为重复值
4)默认按UNION的第1列升序排序

SELECT employee_id, job_id
FROM   employees
UNION
SELECT employee_id, job_id
FROM   job_history;

SELECT employee_id, job_id, department_id
FROM   employees
UNION ALL
SELECT employee_id, job_id, department_id
FROM   job_history
ORDER BY employee_id;

SELECT employee_id, job_id
FROM   employees
INTERSECT
SELECT employee_id, job_id
FROM   job_history;

SELECT distinct employees.employee_id, employees.job_id
FROM   employees,job_history
where employees.employee_id=job_history.employee_id and employees.job_id=job_history.job_id;

SELECT employee_id
FROM   employees
MINUS
SELECT employee_id
FROM   job_history;

SELECT location_id, department_name "Department",
   TO_CHAR(NULL) "Warehouse location"
FROM departments
UNION
SELECT location_id, TO_CHAR(NULL) "Department",
   state_province
FROM locations;

SELECT employee_id, job_id,salary
FROM   employees
UNION
SELECT employee_id, job_id,0
FROM   job_history
ORDER BY 2;

问题：
Which statement is true regarding the INTERSECT operator?
A. It ignores NULL values.
B. Reversing the order of the intersected tables alters the result.
C. The names of columns in all SELECT statements must be identical.
D. The number of columns and data types must be identical for all SELECT statements in the query.

第九章DML
insert 多行插入
create table sales_input_table
(
PRODUCT_ID            NUMBER NOT NULL ,
CUSTOMER_ID           NUMBER NOT NULL ,
WEEKLY_START_DATE     DATE NOT NULL ,
SALES_SUN             NUMBER NOT NULL ,
SALES_MON             NUMBER NOT NULL ,
SALES_TUE             NUMBER NOT NULL ,
SALES_WED             NUMBER NOT NULL ,
SALES_THU             NUMBER NOT NULL ,
SALES_FRI             NUMBER NOT NULL ,
SALES_SAT             NUMBER NOT NULL
);
insert into sales_input_table(PRODUCT_ID,CUSTOMER_ID,WEEKLY_START_DATE,SALES_SUN,SALES_MON,SALES_TUE,SALES_WED,SALES_THU,SALES_FRI,SALES_SAT)
values(111, 222, '01-OCT-00',100, 200, 300, 400, 500, 600, 700);

insert into sales_input_table(PRODUCT_ID,CUSTOMER_ID,WEEKLY_START_DATE,SALES_SUN,SALES_MON,SALES_TUE,SALES_WED,SALES_THU,SALES_FRI,SALES_SAT)
values(222, 333, '08-OCT-00',200, 300, 400, 500, 600, 700, 800);
insert into sales_input_table(PRODUCT_ID,CUSTOMER_ID,WEEKLY_START_DATE,SALES_SUN,SALES_MON,SALES_TUE,SALES_WED,SALES_THU,SALES_FRI,SALES_SAT)
values(333, 444, '15-OCT-00',300, 400, 500, 600, 700, 800, 900);

SELECT * FROM sales_input_table;
PRODUCT_ID CUSTOMER_ID WEEKLY_ST SALES_SUN SALES_MON SALES_TUE SALES_WED SALES_THU SALES_FRI SALES_SAT
---------- ----------- --------- ---------- ---------- ---------- -------------------- ---------- ----------
111 222 01-OCT-00 100 200 300 400 500 600 700
222 333 08-OCT-00 200 300 400 500 600 700 800
333 444 15-OCT-00 300 400 500 600 700 800 900

create table sales
(
PROD_ID            NUMBER NOT NULL ,
CUST_ID           NUMBER NOT NULL ,
TIME_ID           DATE NOT NULL ,
AMOUNT             NUMBER NOT NULL
);

INSERT ALL
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date, sales_sun)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+1, sales_mon)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+2, sales_tue)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+3, sales_wed)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+4, sales_thu)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+5, sales_fri)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+6, sales_sat)
SELECT product_id, customer_id, weekly_start_date, sales_sun,
sales_mon, sales_tue, sales_wed, sales_thu, sales_fri, sales_sat
FROM sales_input_table;

多表插入
drop table small_orders;

CREATE TABLE small_orders
(order_id NUMBER(12) NOT NULL,
customer_id NUMBER(6) NOT NULL,
order_total NUMBER(8,2),
sales_rep_id NUMBER(6)
);

drop table medium_orders;
CREATE TABLE medium_orders AS SELECT * FROM small_orders;

drop table large_orders;
CREATE TABLE large_orders AS SELECT * FROM small_orders;

INSERT ALL
WHEN order_total < 100000 THEN
INTO small_orders
WHEN order_total > 100000 AND order_total < 200000 THEN
INTO medium_orders
ELSE
INTO large_orders
SELECT order_id, order_total, sales_rep_id, customer_id
FROM orders;

隐藏在update后面的约束
定义用于更新其他数据集的数据为源数据，被更新的数据称为目标数据，原数据集合与目标数据之间的关联键，在源数据集中一定唯一。不会存在着目标数据一次更新过程中被更新2次。

drop table test1;
create table test1(id number,name varchar2(5));
insert into test1 values(1,'a1');
insert into test1 values(2,'a2');
insert into test1 values(3,'a3');
insert into test1 values(4,'a4');
insert into test1 values(5,'a5');

drop table test2;
create table test2(id number,name varchar2(5));
insert into test2 values(1,'b1');
insert into test2 values(2,'b2');
insert into test2 values(3,'b3');

把test2的name值更新到ID值相同的test1的name上

commit;
create table t

char(n bytes|char)
varchar2(n bytes|char)
nchar(n)
nvarchar2(n)

number
number(*,2)

CREATE TABLE employees(
employee_id NUMBER(6) primary key,
first_name   VARCHAR2(20),
...
job_id       VARCHAR2(10) NOT NULL,
     );

drop table t;
create table t(id number(10));
insert into t values(1);
savepoint a;
insert into t values(100);
select *from t;
rollback to a;
select * from t;
rollback;

回滚后的状态
⊙数据改变会回滚
⊙还原到以前的状态
⊙锁被释放

⊙读一致性:确保读到的任何时间的数据是一致性的
⊙改变一个被另一个用户修改过的表,不会有冲突(除非正好是同一行,被锁住),但不做一致性读.
因为修改数据时总是读出最新的数据后,再去修改,所以不需要一致性读.
⊙读一致性确保,读的用户不用等待写的用户
⊙写的用户不用等待读的用户

session1:
conn hr/hr;
SELECT employee_id, salary, commission_pct, job_id
FROM employees
WHERE job_id = 'SA_REP'
FOR UPDATE
ORDER BY employee_id;

session2:
conn hr/hr;
update employees set employee_id=employee_id;

第十章建表和数据类型

select table_name,column_name
from dba_tab_columns
where data_type in ('LONG','LONG RAW')
and owner='SYS'
and table_name like 'DBA%';

create table t(id number primary key,name varchar2(10));

select constraint_name,constraint_type,index_name from user_constraints where table_name='T';

select index_name,index_type,uniqueness from user_indexes where index_name='SYS_C006880';

select column_name from user_ind_columns where index_name='SYS_C006880';

外键约束
set long 5000
set pagesize 100
select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;
select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;

问题：
You need to create a table for a banking application. One of the columns in the table has the following
requirements:
1) You want a column in the table to store the duration of the credit period.
2) The data in the column should be stored in a format such that it can be easily added and subtracted with
DATE data type without using conversion functions.
3) The maximum period of the credit provision in the application is 30 days.
4) The interest has to be calculated for the number of days an individual has taken a credit for.
Which data type would you use for such a column in the table?
A. DATE
B. NUMBER
C. TIMESTAMP
D. INTERVAL DAY TO SECOND
E. INTERVAL YEAR TO MONTH

Examine the structure proposed for the TRANSACTIONS table:
name Null Type
TRANS_ID NOT NULL NUMBER(6)
CUST_NAME NOT NULL VARCHAR2(20)
CUST_STATUS NOT NULL CHAR
TRANS_DATE NOT NULL DATE
TRANS_VALIDITY VARCHAR2
CUST_CREDIT_LIMIT NUMBER
Which statements are true regarding the creation and storage of data in the above table structure?
(Choose all that apply.)
A. The CUST_STATUS column would give an error.
B. The TRANS_VALIDITY column would give an error.
C. The CUST_STATUS column would store exactly one character.
D. The CUST_CREDIT_LIMIT column would not be able to store decimal values.
E. The TRANS_VALIDITY column would have a maximum size of one character.
F. The TRANS_DATE column would be able to store day, month, century, year, hour, minutes, seconds,
and fractions of seconds.

Which two statements are true regarding constraints? (Choose two.)
A. A foreign key cannot contain NULL values.
B. A column with the UNIQUE constraint can contain NULL values.
C. A constraint is enforced only for the INSERT operation on a table.
D. A constraint can be disabled even if the constraint column contains data.
E. All constraints can be defined at the column level as well as the table level.

Which two statements are true regarding constraints? (Choose two.)
A. A table can have only one primary key and one foreign key.
B. A table can have only one primary key but multiple foreign keys.
C. Only the primary key can be defined at the column and table levels.
D. The foreign key and parent table primary key must have the same name.
E. Both primary key and foreign key constraints can be defined at both column and table levels.

Which statements are correct regarding indexes? (Choose all that apply.)
A. When a table is dropped, the corresponding indexes are automatically dropped.
B. A FOREIGN KEY constraint on a column in a table automatically creates a nonunique index.
C. A nondeferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique
index.
D. For each data manipulation language (DML) operation performed, the corresponding indexes are
automatically updated.

第十一章其他schema对象

视图分
⊙ 简单视图
◎简单视图只有单表
◎没有包含分组操作和函数
◎可以在视图上执行更新删除插入操作，这叫可更新视图

⊙ 复杂视图
◎包含一个或多个表
◎可以包含函数和分组数据
◎有的时候不允许在视图上做DML语句，这种视图称为不可更新视图

CREATE or replace VIEW    empvu80
AS SELECT employee_id, last_name, salary
    FROM    employees
    WHERE   department_id = 80;

where employee_name=(select sys_context('userenv','session_user') from dual);

CREATE or replace VIEW    salvu50
AS SELECT employee_id ID_NUMBER, last_name NAME,
            salary*12 ANN_SALARY
    FROM    employees
    WHERE   department_id = 50;

select last_number from user_sequences where sequence_name='A';

select dbms_metadata.get_ddl('VIEW','EMPVU80','HR') from dual;

要想DELETE视图行，需要满足下面的准则:
⊙ 在简单的视图上能直接执行所有DML语句
⊙ 子查询中包含下面语句的将不能进行视图的删除行操作
   ◎   分组函数
   ◎   GROUP BY 语句
   ◎   DISTINCT
   ◎   伪列如:ROWNUM

要想UPDATE视图行，需要满足下面的准则:

子查询包含下面语句将不允许更新
⊙ 分组函数
⊙ GROUP BY
⊙ DISTINCT
⊙ 伪列如ROWNUM
⊙ 表达式列

SEQUENCE特征
⊙SEQUENCE是会自动按递增或者递减生成一个唯一的号，它是整型。
⊙SEQUENCE是对象，我们不同的会话可以去读取该SEQUENCE，所以它是共享对象，它会做为共享对象保存在LIBRARY CACHE中
⊙它常用在主键上生成主码
⊙可以代替应用代码实现唯一值的序列号，而且序列号在并发访问时不存在读取出重复值，它不基于外部的事务。
⊙将SEQUENCE的一批值预分配到内存，这样能更快速访问SEQUENCE，这个就是CACHE的功能。

CREATE SEQUENCE dept_deptid_seq
                INCREMENT BY 10
                START WITH 120
                MAXVALUE 9999
                NOCACHE
                NOCYCLE;

NEXTVAL和CURRVAL的规则
可以使用在下面的情况
⊙   非子查询的SELECT列表中
⊙   INSERT语句中子查询的SELECT列表
⊙   INSERT语句的VALUES子句中
⊙   UPDATE SET子句中

不允许出现NEXTVAL/CURRVAL的地方
⊙   视图的SELECT的列表
⊙   有DISTINCT限制的SELECT列表
⊙   带有GROUP BY,HAVING,ORDER BY子句的SELECT语句中
⊙   DELECT,UPDATE 操作可更新视图中，不允许出现NEXTVAL/CURRVAL
⊙   DEFAULT表达式

问题：
Which two statements are true regarding views? (Choose two.)
A. A subquery that defines a view cannot include the GROUP BY clause.
B. A view that is created with the subquery having the DISTINCT keyword can be updated.
C. A view that is created with the subquery having the pseudo column ROWNUM keyword cannot be
updated.
D. A data manipulation language ( DML) operation can be performed on a view that is created with the
subquery having all the NOT NULL columns of a table.

Which three statements are true regarding views? (Choose three.)
A. Views can be created only from tables.
B. Views can be created from tables or other views.
C. Only simple views can use indexes existing on the underlying tables.
D. Both simple and complex views can use indexes existing on the underlying tables.
E. Complex views can be created only on multiple tables that exist in the same schema.
F. Complex views can be created on multiple tables that exist in the same or different schemas.

Evaluate the following CREATE SEQUENCE statement:
CREATE SEQUENCE seq1
START WITH 100
INCREMENT BY 10
MAXVALUE 200
CYCLE
NOCACHE;
The SEQ1 sequence has generated numbers up to the maximum limit of 200. You issue the following SQL
statement:
SELECT seq1.nextval FROM dual;
What is displayed by the SELECT statement?
A. 1
B. 10
C. 100
D. an error

Which two statements are true regarding indexes? (Choose two.)
A. They can be created on tables and clusters.
B. They can be created on tables and simple views.
C. You can create only one index by using the same columns.
D. You can create more than one index by using the same columns if you specify distinctly different
combinations of the columns.

hr
emp

HR:select *from syn_emp
create public synonym emp for emp;
SCOTT: select *from hr.emp;
select *from syn_emp;