oracle study test2d

char(size) - max 2000
nchar(size)
varchar2(size) - max 4000
nvarchar2(size)


alter tablespace
rename datafile
database must be open
tablespace is taken offline


grant action, implicit commit after any DCL statement
in event of database crash


Resource parameters
- sessions_per_user
- cpu_per_session
- cpu_per_call
- connect_time
- idle_time
- logical_reads_per_session
- logical_reads_per_call
- composite_limit
- private_sga


all these are true:
- alter table employees drop primary key keep index;
- using index (create index emp_pk_idx on employee(empno));
- alter table employees drop constraint empno_pk;


select customer_name,loan_number
from borrower,loan
where loan number>10 and (customer_name = 'John' or customer_name = 'Jack');
# which is executed first: ?  where, from, and, or


CLOB - char large obj up to 4GB
NCLOB - for unicode
BLOB - large binary object
RAW - binary info up to 2000 bytes


check constriant, requires a condition  be true or unknown for every row in table
if a statement causes the condition to be false the statement is rolled back
the CHECK constraint cannot include SYSDATE, UID, USER, or USERENV SQL func


which of these functions returns the first non null value in an expression list?
COALESCE - select coalesce(null,null,'20') from dual;  # RETURNS 20
NULLIF


Which of the following returns a single numeric value?
ASCII
character fnunctions take char, or varchar2 and return a character string
Examples:  UPPER, LTRIM
character functions returning number values is small group of functions that take char arg and return a numeric, ie. LENGTH & ASCII

ALTER TABLE is used to drop a constraint


select last, first
from student
where marks < avg(marks)  # cant do this, have to use subquery to compare



changes you CANNOT make to an existing table
- a column that already contains nulls cannot be changed from null to not null
- a new column cannot be added as not null
- a columns size or datatype cannot be changed until the column does not contain any data


Assume table One contains A,B,C and Two contains A,B
One UNION Two A,B,C
One UNION ALL Two A,A,B,B,C
One INTERSECT Two A,B
One MINUS Two C


large batch jobs
ROLLBACK segments


MANAGE TABLESPACE
- take the tablespace online
- make the tablespace read only
- end a backup


LAtches - used in Oracle to protect internal memory structures
preventing simultaneous access
similar to locks.  Latches do internal memory, locks protect data structures


the following conditions should be met to make tablespace read only
- tablespace must be online
- tablespace must not contain any active rollback segments
- tablespace must not be involved in an open backup


Oralce sorting occurs:
- ORDER BY
- GROUP BY
- when an index is created
- when a merge sort is invoked by SQL optimizer because inadequate indexes exist for a table join


# ?
create tablespace temp
datafile "c:\database\temp.dbf"
temporary;


nvl2(x1,x2,x3) x1,x2,x3 are expressionses it returns
x3 if x1 is null
x2 if x1 is not null


MERGE
- can be used to conditionally insert
- can be used to conditionally update
- can perform the operation in parallel



multi-table inserts
restrictions:
- cannot be performaed on views
- cannot perform a multitable insert via a DB link
- cannot perform multitable insert into nested tables
- sum of all INTO columns cannot exceed 999
- sequences cannot be used in the subquery of the multitable insert


which if this trigger is fired once on behalf of the triggering statement even if the row is not affect?
* statement triggers


what is the characteristic of a latch willing to wait situation
- if latch not avail, the process will wait
- each time, the gets column value from v$latch is incremented
immediate
- if latch request is satisfied, the immediate_gets column is incremented
- otherwise the immediate_misses column is incremented


subqueries
in nested subqueries, they are resolved from inner-most, to outer-most
subqueries return rows with keywords like IN or EXISTS 
subquery cannot use ordering clauses like ORDER BY/ GROUP BY

selecting for update, which is already locked can cause hang
* FOR UPDATE WAIT 40
NOWAIT
select * 
from employees
where empno = 20
for update wait 40;


left outer join
right outer joint
full outer join - matching and unmatching rows from both tables


INSTR is a numeric function since it returns Numberic starting position of a substring.  Other scalar (single row functions), ASCII, CONCAt, INSTR
NVL is a numeric funciton


when does a sort occur?
- sql using the order by clause
- sql using the group by clause
- when an index is created
- when a merge sort is invoked by the sql optimizer because inadequate indexes exist for a table join


these are valued 
- alter table test1 add(constraint test1_pk primary key(col1));
- alter index test1_pk rename to test_pk;
- alter table test1 rename to test;



subqueries can be used by the following commands:
- create tables and insert records
- provide values to select, where, having in, update, delete
- insert records in the target views


immediate latch
- if latch request satisified, the immediate_gets column is incremented
- otherwise, the immediate_misses column is incremented


REVOKE select on jack.product_master from john


valid column names:
american$


30 characters, contain only ($), (_), and (#)


which tablespace is used as the temporary tablespace if "temporary tablespace" is not specified for the user
SYSTEM
there is no TEMP, CACHE tablespace in ORACLE


which is true for triggers?
* a trigger restriction specifies a boolean expression that is true to fire a trigger


CEIL(-84) = -83


which of these is used with GROUP BY to give cross tabulation?
GROUP BY CUBE
GROUP BY ROLLUP - gives automatic subtotals
HAVING - limmits GROUP BY to groups for which the condition is true