oracle study test2a

find current value of sequence
- currval
- nextval



declare total_sal number(9);
begin
  update emp
  set sal=1500
  where name='clark'
  savepoint clark_sal; 
  select sum(sal) into total_sal from emp;
  if total_sal > 20000 rollback;
  end if;
  commit;
end;



lock table customer in exclusive mode no wait
exclusive
share - allows concurrent queries, but disallows updates to table


object privilege for REVOKE?
- alter
- delete, 
- index
- insert
- select
- update

natural join, defined in terms of cartisian product


select name
from authors
where name like '%O\_H%' ESCAPE '\';


select count(birthday)
function sum() & avg() canot be used with date type

drop table table1 cascade constraints
drop statement drops table, data, indexes, contraints, triggers,and privileges


declare 
   num_in_stock number(5)
begin
  select quantity into num_in_stock FROM product
  where product = 'Floppy'
  if num_in_stock > 0 then
  update product set quantity=quantity-1
  where product = 'floppy'
  insert into record
  vlaues('one floppy sale', sysdate)
  end if;
  commit;
end;



select last_nmae, commission_pct
  ( case commission_pct
      when 0.1 then 'low'
      when 0.15 then 'avg'
      when 0.2 then 'high'
    else 'n/a'
    end) commission
   from employees
# return values cannot be null



alter table student modify constraint (name varchar2(25) not null)


sql doesn't allow the use of distinct with count(*)
its possible to use distinct with max, min, but wont make a difference
keyword ALL can be used in place of distinct


select ordid, NLV(TO_CHAR(shipdate), 'Not shipped' ...
# notice ', not "


STORE SET  # generates a *.sql file


- tablespace is parented by database, so tablespace cannot be a part of another db
- datafile is parented by tablespace, so datafile cannot be part of another db
- a datafile may not be removed from a tablespace


right outer join
+
oracle will return NULL for any rows that have no matching rows


select nullif(10,10) from dual;
returns a null if both are equal


scalar subqueries can only return a single column and single row
scalar subqueries cannot be used for:
- default values for columns
- returning clauses
- hash expressions for cluster
- functional index expressions
- check constraints on columns
- when conditions of triggers
- group by and having clauses
- start with and connect by clauses