oracle study test2b

initcap()


concatenating char strings
  - concat two strings results in another string
  - preserves trailing spaces
  - uses concat as alternativ to vertical bar operator
  - trets zero-length char strings as nulls


convert


before trigger
after trigger


leve of precedence
1. all comparison ops
2. NOT 
3. AND
4. OR



grant privilege
privilege CONNECT nees to be assigned for user to access database
minimum privilege necessary required to access a database
by default, SYSTEM default userr tablespace


declare
begin
/* block1 */
declare
begin
end
/* block2 */
declare 
begin
end


rollback without a savepoint clause
- ends all transactions
- undoes all the changes in the current transactions
- erases all the savepoints in that transactions
- releases the transactions lock

create sequence order_seq
  start with 1
maxvalue 9999 
cycle;  # also nocycle


indexes
  context - for text retrieval, msword, html, xml, plain text
  ctxcat is used to improve mixed query performance, small text fragments, like dates, names
  ctxrule is used to build document classification app.  



grant select, update
on john.product_master
to jack

dml - select, insert, update, delete
ddl - create, alter, drop, rename, truncate
dcl - grant, revoke


subquery used in the FROM clause to eliminiate need to create a new view


spool, spool off
output is *.lst


MERGE can be used to both update and insert
rows that have matchinng keys are updated, others are inserted


lock table emp in row exclusive mode
  - it will wait if the resources are not available
  - row exclusive locks are same as row share
  - these locks are acquired when updating, inserting, deleting



check constraints, limitations
  - condition must be a boolean
  - condition cannot include sysdate, uid, user, or userenv sql
  - condition cannot contain subqueries or sequences


alter table supplier_master
  drop primary key;



CREATE/ALTER/DROP/TRUNCATE, DDL statements with implicit commits
DML do not have implicit
they are eligible for rollback until a commit is issued


normally, an operator recieves a NULL, the result is null
except CONCAt

script is defined as a group of SQL, Sql*plus, Pl/SQL.  
How can they be run?
- enter sql*plus, invoke commands and run interactively
- run a script in "batch mode", specify on command line
- speicify it on the command line with @ sign
  $ sqlplus user/pass@mydb @my_script.sql
two scripts are run too: glogin.sql & login.sql

get - loads external file contents into the buffer
append - appends a line to the buffer
edit - invokes system-default editor to the edit buffer


highest precedence
1. unary +/-, PRIOR
2  / arith ops
3. binary + - arith ops, || char ops
4. all comparison ops


order by desc
order by ascen, default, is alphabetically sorting




user_views - contains definition view
user_tab_views - contains table names for user views



how to run a sql command
- place semi-colon (;) as the end of last clause
- place a forward slash at the sql prompt
- issue a run command at the sql prompt


extneral table
new functionality
any flat file,, appears as real table
and user cannot index an external table


data dictionary
dba_: information useful for DBAs, view all database objects
user_: information on objects owned by a particular user id
all_: information on every object the user has access to


alter table sales_order_details
add constraint order_fkey
foreign key (s_order_no) references sales_order
modify (qty_order(7) not null);

select count(*)
SELECT CTX_QUERY.COUNT_HITS  # rough count


alter sequence
arguments used
- schema - name of the schema to contain the sequence
- increment by
- maxvalue
- minvalue
- nominvalue 

round(months_between(d1, d2))
ROUND(DAYS_BETWEEN)
MONTHS_BETWEEN

CURDATE 
CURRENT_DATE

Mod(10,0) = 0
returns remainder of m divided by n.  If n is 0, returns m


AGV cannot be used in a union
UNIONS
a) no. of columns in all queries should be the same
b) union cannot be used in subquery
c) aggregate functions cannot be used in a union
d) datatype of the columns in each query musts be the same


INSERT INTO employee(ID_NO,name,salary)
  values(employee_id_seq.NEXTVAL, &name, &salary)


SEQUENCE uses the following as default values:
- NOORDER
- INCREMENT BY 1
- CACHE 20


SELECT to_char(hire_date, 'fmDD MONTH YYYY')
  DATE HIRED FROM employee;


the purpose of GRANTS is to grants the ADMIN, DBA, DDL, or RESOURCE roles to users, or grants privileges on a database objects to users.  To grant roles, the user must be logged inot the database as SYSEM, or as a user with DBA/DDL AND ADMIN privileges, or with RESOURCE privileges to GRANT privileges on own objects to other users.  the dba role is recommended as a replacement for DDL role wheneve possible.

ALTER CREATE SEQUENCE order_seq
INCREMENT BY 2
CACHE 40;