课程第13/14次（2017-03-26星期日上下午）

botang · 发表于 2017-3-26 10:37:10

本帖最后由 botang 于 2017-3-26 17:44 编辑

上完1Z0-051第7章：子查询和关联子查询
上完1Z0-051第8章：集操作符
上完1Z0-051第9章：DML
上完1Z0-052第9章：锁
1Z0-051共12章（上完10章），1Z0-052共19章（上完4章），1Z0-053共21章（上完0章）
总共上完全部52章中的14章

select distinct department_id, last_name from employees;
select department_id, last_name , count(*)
from employees
group by department_id, last_name;
create table t05107 ( a number , b varchar2(20), id number ) ;
/*
1 A 100
1 B 200
2 A 300
--2 B 400
*/
select * from t05107 where
a in (select a from t05107 where id in (100,200,300))
and b in ( select b from t05107 where id in (100, 200,300));
select * from t05107 where
(a,b) in ( select a,b from t05107 where id in (100,200,300))
;
---
SELECT * FROM departments
WHERE NOT EXISTS
(SELECT 'X' FROM employees
WHERE employees.department_id=departments.department_id);
SELECT * FROM departments
WHERE manager_id NOT in
(SELECT employees.employee_id FROM employees
WHERE employees.department_id=departments.department_id);
SELECT * FROM departments
where manager_id is null;
SELECT last_name, salary, department_id
FROM employees outer
WHERE salary >
(SELECT AVG(salary)
FROM employees
WHERE department_id =
outer.department_id) ;
SELECT e.last_name, e.salary, a.avgsal ,e.department_id
FROM employees e , (SELECT AVG(salary) avgsal , department_id
FROM employees
group by department_id ) a
WHERE e.department_id=a.department_id and e.salary > a.avgsal
;
----
create table t05107_a ( a number) ;
insert into t05107_a values (1) ;
insert into t05107_a values (2) ;
insert into t05107_a values (3) ;
insert into t05107_a values (4) ;
commit;
select * from t05107_a;
create table t05107_b ( a number) ;
insert into t05107_b values (1) ;
insert into t05107_b values (2) ;
commit;
select * from t05107_b;
insert into t05107_b values (3);
commit;
delete from t05107_a a where exists ( select 'X' from t05107_b b
where a.a=b.a) ;
delete from t05107_a a where a in ( select a from t05107_b b
where a.a=b.a) ;
----
select * from job_history
order by 1,2;
select job_id from employees where employee_id=102;
SELECT employee_id, job_id
FROM employees where employee_id in (101,176,200)
intersect
SELECT employee_id, job_id
FROM job_history where employee_id in (101,176,200)
;
---
SELECT employee_id
FROM employees
MINUS
SELECT employee_id
FROM job_history;
----
SELECT employee_id, job_id
FROM job_history
MINUS
SELECT employee_id, job_id
FROM employees ;
---
SELECT location_id, department_name "Department",
NULL "Warehouse location"
FROM departments
UNION
SELECT location_id, NULL "Department",
state_province
FROM locations;
---
create table t05108_i ( a number ) ;
insert into t05108_i(a) values ( 1);
insert into t05108_i select * from t05108_i;
select count(*) from t05108_i;

复制代码

insert into t05108_i select * from t05108_i;
select count(*) from t05108_i;
select bytes/1024/1024 from user_segments s
where s.segment_name='T05108_I';
select salary from employees where employee_id=100;
create table t05108_d as select * from t05108_i;
select count(*) from t05108_d;
select bytes/1024/1024 from user_segments s
where s.segment_name='T05108_D';
----
select salary from employees where employee_id=100;
---poor
select s.SID ,s.SERIAL#, s.BLOCKING_SESSION,status, s.SQL_HASH_VALUE , s.SQL_ID
from v_$session s
where s.blocking_session is not null;
--killer
select SID, SERIAL#, USERNAME
from V$SESSION where SID in
(select BLOCKING_SESSION from V$SESSION);
alter system kill session '17,21598' immediate;
select sid, serial# from v$session where sid =198;
select * from v_$session s where s.TERMINAL='pts/2';
select * from v_$sql s where s.SQL_ID='64516t36mp029';
select * from (
select * from v_$sql s order by s.APPLICATION_WAIT_TIME desc )
where rownum <=5;
select * from v$lock where sid in (17, 198);

复制代码

SQL> conn hr/oracle_4U
Connected.
SQL> create table t_dead (id number , sal number(8,2) ,mgr number );
Table created.
SQL> insert into t_dead values (100, 1000.00 , 101 );
1 row created.
SQL> insert into t_dead values (200, 2000.00 , 201 );
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL> update t_dead set sal=1000.01 where id=100;
1 row updated.
SQL> update t_dead set sal=2000.01 where id=200;
update t_dead set sal=2000.01 where id=200
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
SQL> select * from t_dead;
ID SAL MGR
---------- ---------- ----------
100 1000.01 101
200 2000 201
SQL> commit;
Commit complete.
SQL> update t_dead set sal=2000.01 where id=200;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from t_dead;
ID SAL MGR
---------- ---------- ----------
100 1000.01 102
200 2000.01 202
SQL>

复制代码