oracle study test2c

exclusive lock - allows queries but prohibits any other activity
share - allows queries but prohibits updates


retrieve all student records in all classes
left outer join

UPDATES without WHERE clauses are defined as unqualified UPDATES

syntax for a View
cannot use groupby, distinct, having, avg, sum, etc.


alter tablespace with TEMPORARY clause
oracle performs a checkpoint for all online datafiles
but does not ensure that all files can be written
any offline files may require media recovery before bringing the tablespace online

which of thesee is an iterative control
- for, while, goto


what type of locks are used with admin allows concurrent access to table,and also wants other users to prohibit other users to lock entire table exclusively?
- ROW SHARE UPDATE: allow concurrent access but prohibt users to lock entire table exclusively
- ROW EXCLUSIVE: same as ROW SHARE, but also prohibits locking in share mode
     which are acquired when updating, inserting, or deleting
- EXCLUSIVE allows uery on the locked resource but prohibits any other activity
- SHARE allows queries but prohibits updates to a table



"Binding" involves the use of placeholders and the USING clause, while concatenation adds the values directly to the SQL string.
the two methods to insert program values into sql string are binding & concat
binding is faster
when bindin, the sql string does not contain the the value; 
it contains only a placeholder name
this allows the user the ability to bind different values to the same sql statement
biding is much easier to write and maintain


types of triggers
- BEFORE statement trigger
- BEFORE row trigger
- AFTER statement trigger



REVOKE DELETE ON emp FROM MATT

CREAT VIEW dept_salary_vw AS
SELECT dept_no, salary, last_name
FROM employee
WHERE salary > 5000
with check option;
# cannot insert a row into view with salary > 5000

SELECT ... FOR UPDATE
- can be used to override locks
- cannot be used with DISTINCT and GROUP BY
- cannot be used with set operators and group functions


scalar subqueries
- return a single value
- can be used with CASE
- can b be used with ORDERBY
- can be used with SELECT
- VALUES clause of an INSERT statement
- WHERE clause
- ORDER BY clause
- As a parameter of a function


locking charasteristics of UPDATE, DElETE, INSERT
- acquires exclusive locks on the rows to be moidified, and row exclusive lock on table containing those rows
- other users can query the rows to be mod, but cannot alter.  Can work on other rows
- locks on rows and tables are released on COMMIT
- a rollback to savepoint, releases all locks acquired upto the named savepoint

GRANT option
allows for privileges passed on via WITH GRANT OPTION of GRANT to be revoked sepeartely from the privileges themselves
- revoke fails if it results in an abandoned object, such as a view, unless the CASCADE keyword has been specified
- privileges granted to this user by other users are not affected

MERGE 
- accepts the character string form of two labels
- the MERGE_LABEL function is a utility for merging two labels together
- it accepts the three character specification of a merge format


Sql*plus
to run a script file, issue the command "START filename, or use the @filename
SAVE
EDIT
GET


ALTER table command with the ADD clause in the Employee table


administrator has granted me the privilege Create any table and create procedure
* i can create tables in any schema but drop tables and create proc in my schema

GRANT select,insert,modify ON Employees TO HR WITH GRANT OPTION;


ROLLBACK with SAVEPOINT;
it rolls back just a portion of the transaction


which functions are used in Oracle 9i
Group functions return a result per set of rows, ie. DISTINCT, SUM
Object reference functions: Manage REF values in user-defined types (ie. DEREF and REFTOHEX)
Analytical funcitions, COmplex analytics
Misc functions: a couple dozen functions that do not fit any of the above categories (ie. NVL, USER, BFILENAME, GREATEST)


Which of these are the locking characteristics for explicit locking?
Explicit lock is put by a trigger
* DBA can lock any table
* Other users can lock tables they own or any tables on which they have been granted table privileges
Users can explicitly lock either tables or rows using select ... for update, or a lock statement


write a subquery
* subquery can be used by the following commands
- it can be used to create tables, and insert records
- it can be used to update records
- the subquery can be used to provide values for the conditions in SELECT, WHERE, HAVING IN, UPDATE, DELETE


procedures
CREATE OR REPLACE PROCEDURE truncobj (
  nm IN VARCHAR2,
  tp IN VARCHAR2 := ;'TABLE',
  sch IN VARCHAR2 := NULL)
IS
BEGIN  
   EXECUTE IMMEDIATE
     'TRUNCATE || tp || '' || nvl(sch, USER) || '.' || nm;
END

# WTF?  this is too tough
the following is the query to give all the clients and saleman in the city
SELECT name FROM sales_master
where city = 'new york' intersect
select name from sales_master
where city = 'new york' in 
(select city from client_master c
where c.client_no IN
select client_no from sales_order s
where salesman_no = sales_master.salesman_no)


COMPUTE - sql*plus  for calculating valoues
CHANGE - change characters in the buffer
CLEAR - clear definitions; or clear the screen or buffer
COLUMN - define column formats or characteristics


# turn on constraint
ALTER TABLE table_name
ENABLE CONSTRAINT constraint_NAME


Which of the following is true for DROP FUNCTION?
* it is used to remove standalone stored function from database


which of the following is true for views?
- primary key of the table should be included
- the view can be created using a single table
- aggregate functions cannot be used in a VIEW
- SELECT statement should not have subquery


which of this is true for iSQL*Plus
this is just a web-interface version of SQL*Plus


LEAST_UBOUND - returns char string label, one label which dominates both
GREatet_lbound - DETERMINE LOWEST LABEL OF THE DATA


mergE statement
- merges the data from a select statement into a table
- user can decide when data is updated/inserted via the "ON()" clause
- user can decide what columns are updated
- user can decide what data is inserted.


trusted in the function based index?
QUERY_REWRITE_ENABLED=TRUE
the followingn are requirements to create a function-based index in the user defined schema on his table:
- prereqs for creating a conventional index
- user should have the QUERY REWRITE system privilege
- to use the function-based indexes in queries, the QUERY_REWRITE_ENABLE=TRUE
- QUERY_REWRITE_INTEGRITY set to TRUSTED
- create the index in another schema the user must have the GLOBAL_QUERY_REWRITE privilege
- the table must have EXECUTE object privielge on the functionss) used in the function based index


steps to create a cursor?
- dclare a cursor that specifies the SQL select statement 
- open
- row at a time
- close