while ..... loop.................end loop;
Data Types : number, integer, varchar2, char, date, Boolean;
goto procedure name;..............<<procedure name>>..............
exit, rollback, commit, savepoint.
References: This keyword is used to refer two similar fields of different tables.
What are different built in functions ?
CONCATENATE, INITCAP, LENGTH, LOWER, UPPER, LPAD, RPAD, LTRIM
(removes the character specified left side of word), RTRIM, SUBSTR (displays values from
specified location onwards), TRANSLATE (for single character), REPLACE (for more than one
character ),CHR ( prints character of given ASCII value), ASCII (value of given character ).
ABS, CEIL, FLOOR, SQRT, POWER, SIGN, TRUNC, ROUND, EXP, MOD, LOG, LN,
GREATEST, LEAST, SYSDATE, ADD_MONTHS, MONTHS_BETWEEN.
what are emp.sal%type and emp%rowtype ?
First one is to assign a variable of type sal only.
Second is to assign a complete row of type emp table.
What are constraints ?
Constraint Clause : This will constrain a single column or a group of columns in a table.
and are used for maintaining integrity of the database.
Different Constraints are : candidate keys, primary keys, foreign keys, check conditions.
Can specify as a part of column definition, or at the end of the table (if more than one column).
Check Constraint : for validations to fields.
Naming Constraint (table constraints): Can name the constraint else system will name it
by default.
Different types of joins ?
Equi join : where emp.deptno = dept.deptno;
Non Equi join : where e.sal >= g.losal;
Outer join :***********e.deptno(+)=d.deptno;
Different types of Keys ?
Foreign Key : This is a referential integrity constraint and specifies the values of primary
key in other table. and is mentioned as :
DEPTNO number(2) REFERENCES DEPT(DEPTNO);
The clause ON DELETE CASCADE added to REFERENCES clause tells Oracle to delete the
dependent rows when we delete the row in the parent table.
Unique Key : If declared, then does not accept duplicate and also NULL values. And can
have many Unique key fields.
Primary Key : Similar to Unique key but, also maintains an index on primary key and also
used to connect two tables.
Write about cursors ?
Implicit (SQL is implicit by default ) and Explicit cursors.
EXIT WHEN C1%NOTFOUND, IF C1%FOUND THEN, IF C1%ISOPEN THEN,
FOR LOOP in CURSOR will :
1) open the cursor.
2) fetches the records from cursor one by one.
3) keeps the track of number of records in the cursor.
|
|
