Interview Questions for SQL Beginners & experienced

 What is data ?        

1. Any facts need to be stored for future purpose Called data. 

Data cannot give any information.

    Data can structured or unstructured.

      Structured 🡪empname , address .

Unstructured 🡪empphoto , addmap .

1. What is information ?

1. The processed data can be called as information.

This information gives meaning.

2. What is database ?

1. It is a collection of interrelated data, i.e is it is always stored Data along with it’s relations.

3. What is metadata ?

1. It is  data which describes the  properties or characteristics of End user data and context of the data.

4. What is entity ?

1. It is significance about the information need to know.

5. Difference between char and varchar2 ?

char	Varchar2
It is fixed length char	it is dynamic char..datatype
When gives sting lessthan orginal string Blonk pad are generated.	Blank pads are not generated , memory gives return (reuse) when string lessthan orginal length.
Default length is 1. And  max lenth is 2000 bytes.	Min length is 1 byte. Max length is 4000 bytes.

6. What is difference b/w  long and raw ?

long	raw
It is stored char and numbers	It is stored binary data
Max limit is 2gb	Max limt is 2000bytes
Only one long column is valid.	Only one column is valid

7. What is  diff b/w raw and longraw ?

raw	longraw
Max length is 2000 bytes	Max length is 2gb
RAW data can be indexed.	LONG RAW data cannot be indexed

8. What is diff  b/w blob and clob ?

Blob	clob
BLOB strings are not associated with a character set, as with FOR BIT DATA strings.	A CLOB is considered a character string.
BLOBs are used to store large objects in binary format	CLOBs are used to store character data.
Max length is 4gb	Max length is 4gb

1. What is null ?

1. Null  value means the value is unavailable, unassigned, unknown  or inapplicable.

2.  What is order by clause ?

1. It is used to sort the rows.  It is last clause in sql stmt. It default is ascending.

3. Howmany predefined function functions , what are they ?

They are two types of predefined functios. 

Single row functions	Multi row /group /  aggregate functions
They are 5 types Numeric 🡪ceil(),floor(),power(),squr(),greatest(),mod(), Char / strings 🡪replace(),translate(),substr(),instr(),trim(),pad() Date functions 🡪add_months(),months_betwwen(),last_day(),next_day() Date conversions 🡪to_char(),to_date(),to_number() Misllanious functios 🡪nvl(),nvl2(),null if(),coalesce()-> (returns first occrence of  non-null values).	They are  5 types  1) sum()
	2)  Avg()
	3)   Count()
	4)  Max()                                     5)  min()

4. What is diff b/w ceil and floor ?

ceil	floor
It gives higest values of the given number	It returns lowest value of the  given number

5. Diff b/w replace and translate functions ?

replace	translate
it is replace entire string	It is replace char by char
Return string if no match found	Returns null if no match found
Max 3 parameters pass, and optionally 2 (if not give 3rdrd para ,take null in inplace of whatever mentiotn 2ndnd  para)	Max passing 3

6. What is diff b/w substr and instr functions ?

Substr	Instr
It returns to extract the  portion of the string	It returns postion of string
Max pass 3 param and optional is 1	Max pass 4 para and otional is 2
It return null if not match	It return 0(zero) if not match

7. What is  diff b/w pad and trim?

Trim	pad
It removes blank spaces in given string	It is add the blanks spaces in given string

8. What is diff  delete and drop ?

Delete	drop
By using Delete command ,delete data entire table but structure of table is exists.	By using drop command ,entire structures with data also deleted.
No effect on view,synonum,constrains and indexes.	All are remove .when drop command used in table.
Not possible to  get back rows when using of commit command after deleteing.	Not possible to getback entire table  along with using of purge command.
We can get back rows  by using rollback . It is explicit commit.	We can getback entire structure with data by using flashback.  It is implicit commit.
In delete condition is possible. And it slow process.	It cannot possible condition and it is very fast process.

9. What is diff of  truncate and delete?

DELETE	TRUNCATE
By using Delete command ,delete data entire table but structure of table is exists.	Cannot rollback the data.
No effect on view,synonum,constrains and indexes.	No effct.
In delete condition is possible. And it slow process.	Cannot possible. And process is very fast.
We can get back rows  by using rollback . It is explicit commit.	It is implicit command.
It is dml commands	it is ddl commands

10.  What is diff of where  and having clause?

Where	Having
Where clause columns may or maynot using group by clause	Having clause columns must be used in group by clause.
When where condition is false then count function is returns 0(zero)	When having clause is false then count function is  returns ‘no rows selected’.
Where clause select rows before grouping.	Having clause select the rows after  grouping.

11. What is group by clause ?

1. It is operate on set of rows to give one result per group. It is returns summary information for each group. All group by clause columns list may or may not used in select clause. Extra non group functional columns also declared in group by clause.

12. What is diff of length and vsize function?

1. Length function is returns number of characters in given string  where   Vsize function returns number of bytes in  a given string.

13.  what is constraint and howmany cons having?                       

1. Constrains are used to impose of business rules in organization.it is allows to enter only valid data.

They are 5 types  1) primary key.

2)unique key.

3) check.

4) foreign key. 

5) not null.

23) what is diff between primary key and  foreign key?

Primary key	Foreign key
Primary key uniquely identify a record in the table.	Foreign key is used to link with anther table that is primary key in another table.
Primary Key can't accept null values.	Foreign key can accept multiple null value.
. By default, Primary key is clustered index and data in the database table is physically organized in the sequence of clustered index.	Foreign key do not automatically create an index, clustered or non-clustered. You can manually create an index on foreign key.
We can have only one Primary key in a table.	We can have more than one foreign key in a table.
Not having in primary key.	Foreign key having on delete cascade and on delete set null.

24.  What is check constraint ?

1. Check constraint is cannot enter values  until given condition true .

25.  What is diff  b/w primary and unique constraint?

In table only one primary key is valid. In pk 2 database objects are generated.    Primary key cannot accepts duplicates and null values.

  Where unique is number of columns are allowed to apply. Here 2 database objects are created. Unique is cannot allows duplicates but allows null values.

26. What are the joins how many joins having?

Join is mechanism , by using of join to get  the data from more than one table.             Generally one table cannot get complete information by making right decision then we go for using join and get complete information to making a right decision otherwise we can go to sub queries.        

joins having 4 

8i features  like 🡪equi,nonque,outer,self and cartesion join

9i features like 🡪natural,inner,left,right outer and full outer join.

27.  what is equijoin ?

To get matched rows from tables   ,called equijoin  .       

28. what is self join?

Self join is indicates join  the table to itself.  Same table appears from clause and is must followed by table alias. 

By performs self join ina table  must  have a at least 2 columns with same data type and  same values.      

29.  what is diff of case and decode functions?

case	decode
We can check all relational operations like  <,>,!,=,<=,=>.	We can check condition only ‘equality’.
We can write else stmt in case stmt.	We cannot write else stmt.
Here null != null	Here null = null
The case stmts is used for conditional power in sql stmts.	Decode is more powerful  b’coz can change sql statements
Performance is very fast. Compare to decode.	Performance is slow .

30. Is there a limit to the number of arguments that you can have in one DECODE statement? I’m getting an error, “ORA-00939: too many arguments for function”.

Yes, the maximum number of components that you can have in a DECODE function is 255. This includes the expression, search, and result arguments.

The DECODE function returns a value that is the same data type as the first result in the list.
If the first result is NULL, then the return value is converted to VARCHAR2.
If the first result has a data type of CHAR, then the return value is converted to VARCHAR2.
If no matches are found, the default value is returned.
If default is omitted and no matches are found, then NULL is returned.

31.  What is diff. between unique index and unique key?

Unique index	Unique key constraint
One data base object created	Two data base object created
It is not possible	It can gives the reference to the other column

32.  What is the purpose of sub query?

To define the set of to be inserted into target table of an insert or created table stmts.to define one or more values to be assigned to existing rows in update stmts.to define the set of rows to be included in a view or Mview insert or create table stmt.

To get the information by combining the two query’s,placing one query inside the other query.

33. What is the inline view query?

A subquery in the from clause of select statement.

34. What is the diff btwn In and exist ?

In	Exist
In for  big outer query  and small inner query	EXIST for  small outer query  and big inner query
It Must accept col Name	It won’t accept Col Name.
It works with low performance	It works more efficiently.
By using of In filtering criteria in sub query.	Majority of filtering criteria in main query.

35. Which join can give more performance ?

Natural join gives more.

If Natural Join doesn’t exist then self join gives more performance.

36. Diff btwn Union And union all?

Union	Union All
It gives Low Performance as it does accept duplicate values.	It gives high Performance as it doesn’t accept duplicate values.
Null Values are not Ignored by Union	---------------------------

37. What is view?

View is a logical data. It doesn’t store the data in db. It is also known as stored select statement.

View can be also be created within the view.

View which can be created without base table known as force view.

38. Diff  btwn view and materialized view?

View	M.View
It is a Virtual data. Doesn’t stored in db.	It stores in db.
Replace  keyword is used in views	Replace Keyword is not accessable in  mv.
DML Operations can be performed	DML Operations not performed
View get invalid when base table is dropped or d.types changed in Base table.	Even Base table is dropped Mv is valid.
Modification of Base table can immediately effected to views.	Modification of Base table cannot to immediately effected to mviews.

39. What is MV?

MV is a physical db Object which stores the data permanently in db. MV is used to Data warehouses. 

Syntax:

Create Materialized view m_v

 [Immediate /Deffered] refresh[fast/complete /force] 

on [comit/demand] as select…tb;

 If we want to use refresh fast command we must create mv with logon  with same table name or else it throws error.

When we create refresh complete , the performance can get degraded.

When mv create with refresh force  the Materialized view logon created or else it behaves like a refresh complete.

Syntax:

Exec dbms_mview.refresh(tb name);

Indexes:

40. What is a Index? And when idexes are created?

Index is a db obj.  indexes are explicitly  created on table to retrieve the data very fast . indexes are activated when index columns are used in where clause.

When indexes are created .  

1. Column contains  wide range of values.

2. Columns contains large number of null values.

3. One or more columns are frequently used in a where clause or join condition.

4. Table is large and most queries are expected to retrieve less t of rows.

When indexes are not created .

1. Table is small.

2. The columns are frequently not used as condition in a query.

3. The table is updated frequently.

    By default  b-tree indexes are created when primary or unique constraints are applied in columns. Index names also generated by .

🡪 Do not create Btree  index on those columns which contain many repeated values. In this case create BITMAP indexes on these columns.

50) what is bitmap indexes ?

   A)    Bitmap indexes are indicates  each distinct key. Which is used to  stores the rowid’s associated with a key value as a bitmap.                                                                                                                 

 🡪 bitmap indexes are should not be used  for tables involved in online transactions processing  applications due to the internet mechanisms.

🡪  the column contains repeated values have then only created the bitmap index

51) what are the  clusters ,can you explain briefly ?

      A) clusters are database objects , which is used to improve performance of join  query while manipulating the master-details table.

                     🡪A cluster is made up of a group of tables that share the same data blocks. The tables are grouped together because they share common columns and are often used together.

                      🡪 A cluster can contain a maximum of 32 tables.

          🡪 Specify one or more names of columns in the cluster key. You can specify up to 16 cluster key columns. These columns must correspond in both datatype and size to columns in each of the clustered tables, although they need not correspond in name.

        Index clusters

you can create tables in the cluster. However, before any rows can be inserted into the clustered tables, a cluster index must be created.

• In an indexed cluster, Oracle Database stores together rows having the same cluster key value. Each distinct cluster key value is stored only once in each data block,

52) what  are hierarchical queries ?

A) If a table contains hierarchical data, then you can select rows in a hierarchical order using the hierarchical query clause.

Hierarchical_query_clause::=

START WITH specifies the root row(s) of the hierarchy.

CONNECT BY specifies the relationship between parent rows and child rows of the hierarchy. This clause contains a condition that defines a relationship.                                                                                                  Prior : this clause can contain other conditions to further filter the rows selected by the query. 

Level: level is pseudo column , which is automatically assigns numbers  to each level in a tree.           Syntax :    

sql>  select level, colname from <tbmame> <where condition> startwith condition connect by prior parent col= child colname;

 

53) tell me merge concept ?                                        A)                Merge is a dml stmts which is used to trnfer data from source table to target table  when those table are same strctu.  

🡪Generally to avoid the multiple insert and update stmts conditionally using on clause .  

         syntax :

merge into  <tb1> t1 using <tb2> e on (condition) when matched then update set (condtion) ,(condtion), when not matched then values(columns………..)                  

55) what is synonym ?

A) Sysnonym is a database ob, which provides security on table name  and  owner name.  i.e  it     hide the object name and another schema name.

55) what are publc and private synonym ?   

A) Specify PUBLIC to create a public synonym. Public synonyms are accessible to all users. However each user must have appropriate privileges on the object in order to use the synonym.

If you omit this PUBLIC clause, then the synonym is private and is accessible only within its schema. A private synonym name must be unique in its schema.

56) what is sequence ,and we can create sequence with cycle ?

A) Sequence is a independent database object, which is used to generates sequence numbers automatically.

Syntax :

sql> create sequence  (seq name)   start with n increment n min value n max value n cycle/no cycle cache/no cache;

57) What is curval and nextval?

Curval returns current value of sequence section.

Nextval returns next value of the sequence but every time sequence should start with next value.

58)How many types of locks?

DB has 2 types of  locks. They are rowlevel and table level.

Locking set of rows by using for update clause. This clause used in select statement only.

Table level locks: (3 types)

Shared mode : One  User can lock the table at share mode and other do not perform any kind of DML Operation except select.

Share update mode: If one user lock the table still it will allow the other user are allowed to perform DML operations on the record except the record access to the user.

Exclusive mode: Locking one user in exclusive mode then it wont allow the other user to perform any kind of operation.

Note:  If 1user lock the table it wont allow the other user to lock the same table.

Wait and no wait:  Wheneva we lock the any specific record in a table, we need to give wait or no wait option along with lock operation.

---Analytical Functions(8I features)---

59) What is the diff between rank and dense rank?

Rank : Wenever we use rank on particular column, It jus skips the next consecutive number i.e if it has same value allocate same number.

Dense rank: It do not skip the consecutive number i.e even it has same values allocate same to it and allocate next number to next value as per sequence.

60) What is virtual column?

Virtual column is a table which is used to store expression directly into a table column.

Syntax:

                 sql> Create table<table.name>(col1  data type,col2 datatype generated always as (col1+col2) virtual);

61) Diff betwn rownum and ROWID?

Rownum: ROWNUM is temporary value. ROWNUM only has meaning within the context of a single SQL statement, a way of referencing rows within a fetched result set.

ROWID:  ROWID is permanent value. It is used to fetch a row . it is a physical address of rowids.

62) Diff between rollup and cube?

Rollup: It is used with group by clause to  display the summarized date. ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns

Cube: CUBE generates a result set that shows aggregates for all combinations of values in the selected columns.

63)Diff btwn Materialized view and snap shot?

MV	SNAPSHOT
This view has a query rewrite refresh and commit permissions.	No permissions.
Execution time is less.	Execution time is more.
Convertion is not possible.	It is converted to MV by using refresh command
If base table was effected with changes then immediately MV gets change.	No effect occurs.

64) What is a partition?

Partition tables are used to improve the performance of applications in backup and recovery process. This is used to data warehouse applications. These tables are created based on key column.

Partitions are 3types:  1. Range   2.list     3.hash

When to Partition a Table??

• Tables greater than 2 GB should always be considered as candidates for partitioning.

• Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month’s data is updatable and the other 11 months are read only.

• When the contents of a table need to be distributed across different types of storage devices.

65)What are the diff. b/w range ,list ,hash ?

Definition: A table that is partitioned by range is partitioned in such a way that each partition contains rows for which the partitioning expression value lies within a given range.

 Creating range partitioned table

Definition: List partitioning enables you to explicitly control how rows map to partitions by specifying a list of discrete values for the partitioning key in the description for each partition.

 Creating list partitioned table.

Definition: Hash partitioning maps data to partitions based on a hashing algorithm that Oracle applies to the partitioning key that you identify.

Creating hash partitioned table.

 

65) what is pivot and unpivote ? give syntax :

A) it 11g features, which is used to convert rows as columns and also display aggregate functional value in tabular form .

This function gives high performance compare to decode conversion function.

Sql>  select  * from (select col1…. from <tbname>)pivot(<group functions> for <col name> in(values...))

Topnth salaries

Sql> select level, max(sal) from emp where level=&level connected by prior  sal > sal group by level; 

Sql>select rownum,empno,ename,sal from (select * from emp order by sal desc) group by rownum,empno,ename,sal having rownum=’&n’;

Sql> with nth  as (select ename,sal,dense_rank()over(order by sal desc ) as r1 from emp) select ename,sal from nth where  r1=&n;

Sql> select sal from (select  sal,dense_rank()over (order by sal desc) as topnth  from emp)where topnth=&n;

Sql> select sal,ename  from (select  sal,dense_rank()over(order by sal desc) as r_num from emp) where r_num=&n;

Sql> select * from emp where sal in(select unique sal from emp order by sal desc fetch first 3 rows only);

65.1) write query return name from given mail?

Sql> select  substr(‘siva@gmail.com’,1,instr(‘siva@gmail.com’,@’,2,1)-1) from dual;

65.2) return 2 and 5 rows in table?

Sql> select * from (select  rownum  r , emp.*  from emp) where  r in(2,5)  or  r< (select  max(rownum-5) from emp);                                                        

or(last 4 records)

Sql> select * from (select rownum r ,emp.* from emp where rownum <=(select max(rownum) from emp)) where r>10;

66) what is funct ional index ? what is uses?

A) A function-based index allows you to match any WHERE clause in an SQL    statement . 🡪remove unnecessary large-table full-table scans with super-fast index range scans.                                                                                                                                                                                           🡪When you create a functional index, the database server computes the values of the user-defined function and stores them as key values in the index.

You can use a functional index for functions that return values of both user-defined data types (opaque and distinct) and built-in data types. However, you cannot define a functional index if the function returns a simple-large-object data type (TEXT or BYTE).

The benefits of using a function-based index includes:

• function-based indexes are easy to create

• An function-based index can compute complex values without using a trigger

• A single unction-based index can change hundreds of SQL statements plans  

• A function-based-index can be built on an arithmetic expression or expression containing

• PL/SQL, package functions, C callout or SQL built-in functions 

• The SQL optimizer can estimate selectivity more accurately if the expressions are materialized in a function-based index.

🡪CREATE INDEX idx_person_upper_given_names ON person (UPPER(given_names));

🡪CREATE INDEX idx_person_upper_last_name ON person (UPPER(last_name));

67) what id gtt?

A) Temporary tables exist only during the connection that declared them and cannot be referenced outside of that connection. When the connection closes, the rows of the table are deleted, and the in-memory description of the temporary table is dropped. 🡪ON COMMIT DELETE ROWS will delete the rows in the table Tcl command are used.                              🡪 ON COMMIT PRESERVE ROWS   The data will be preserve till the session is exists.

Temporary tables are useful when:

• The table structure is not known before using an application.

• Other users do not need the same table structure.

• Data in the temporary table is needed while using the application.

• The table can be declared and dropped without holding the locks on the system catalog.

Syntax

DECLARE GLOBAL TEMPORARY TABLE table-Name

    { column-definition [ , column-definition ] * }

[ ON COMMIT {DELETE | PRESERVE} ROWS ]  

NOT LOGGED [ON ROLLBACK DELETE ROWS]

68) what is cardinality ?

A) Cardinality refers to a  column contains uniqueness of  data values.  

🡪High cardinality means that the column contains a large percentage of totally unique values.  

🡪 Low cardinality means that the column contains a lot of “repeats” in its data range.

Cardinality also sometimes refers to the relationships between tables. Cardinality between tables can be one-to-one, many-to-one or many-to-many.

69) what data dictionary of comments ?

 A) all_col_comments ,  all_tab_comments  , user_col_comments ,  user_tab_comments.

70) write query  return    ‘ - ’  in place of null  values?

Sql> select ename,empno, sal ,nvl(to_char(comm),’-’) from emp;

71) write query   to return  “supreme authority”  in null place of  mgr column  at name of king ?

  Sql> select empno,ename, sal,job , nvl(to_char(mgr),’suprime authority’) from emp group by sal desc. 

72)  objects  vs data data dictionaries?

Objects                     data dictionary

Table        🡪  dba_table   or   user_tables    or   all_tables.

Views       🡪 user_view.

Mviews   🡪 user_mviews.

Constraints  🡪 user_constraints  or  user_cons_columns.

Index        🡪 user_indexes.

Sequence   🡪 user_sequences.

Columns  🡪  user_tab_columns.

Cluster   🡪  user_clusters.

Synonym  🡪     USER_SYNONYMS.

Partitions  🡪 user_tab_partitions.

73) what is cache?

A) Cache is a memory area which is used to access sequence value very fast.

 🡪 cache memory area store set of sequence numbers which is used to improves  performance of the sequence. It is optional clause.

The default max value is 20 and default  min value 2.

74) what is cycle and no cycle ?

A)  When you create a sequence with CYCLE option, you are saying that when the sequence reaches its MAXVALUE, it will start over at the MINVALUE. This is not wise if using the sequence for primary key creation. 

          When you create a sequence with NOCYCLE option, you are saying that when the sequence reaches its MAXVALUE, it will NOT start over at the MINVALUE.  When sequence reach max value oracle thrown error.

74) what are the EF CODD rules ?

 1) Information Rule :  all the information in a database should be stored in a table.

2) Guaranteed Access Rule :  Each and every data value must be accessible without any ambiguity.
3) Comprehensive Data Sublanguage Rule : hould be at least one query language that enables the user to perform different functionalities within the database.

Almost all the DBMS and RDBMS applications use one major query language which is Standard Query Language (SQL).

4)View Updating Rule :  all theoretically  updatable views must be  updatable in table.

5) Integrity Independence Rule :

6) High-level Insert, Update and Delete Rule: all dml operations are worked .

75) what are diff b/w nvl() and coalesce()  function ?

nvl	coalesce
it returns  values if 1st expression is null then  2nd expression value is taken	It returns first occurrence of non-null value
Slow process	Performance fast
Pass two parameters (must pass same datatype ) ex:(we can pass first para is number then pass 2nd para also number ) or  1st char is pass then 2nd  parameter pass char only.	Many parameters pass(it is ansi function)

76) howmany subquery  you can written ? 

    A) Total 255 sub queries are written.  

77) write a query for first name,mn ,last name  from given string by using substr function?

Sql> select substr(name, 0, instr(name, ' ')-1) as first_name   ,

                       substr(name, instr(name, ' ')+1,instr(name, ' ', -1, 1) - instr(name, ' ') - 1) as mid_name  ,

          substr(name, instr(name, ' ', -1) + 1) last_name     from temp_1;

78)  what is co- related sub query ?

A) Generally non-co related sub query execute first child query   then   after parent  query executed.

But co-related sub query execute first parent query after then execute child query.

Co-related sub query  child query is executed for each row per parent query table.

Must  alias name are   used in  co-related sub query.

Syntax 

Select * from  table-name  <alias-name > where column-name=(select * from table-name  where    column-name   = aliasname . column -name);

79) why  looking for job change?

A)   “At my current job, I’ve learned skills that I’m ready to bring to the next level. I believe I can make a difference here.”                                                    or

a better opportunity, more challenges, and career growth.

I want to change my current job to get opportunity it term 
of 
new challenge, 
extra responsibility,
new environment, 
better technical future & 
more scope of my professional growth to wide up my 
experience meet the changing world.

80) what is diff b/w round   and  trunc ?

ROUND function used to round the number to the nearest while TRUNC used to truncate/delete the number from some position. Some cases both returns same result.

1.  The ROUND() is used to round a numeric field to the nearest number of decimals specified.

e.g. 56 is nearer to 60 than 50 hence it will return 60.
e.g. If instead of 56 you use 54 then 54 is nearer to 50 and it will return 50.

2. TRUNC() returns a number truncated to a certain number of decimal places. So it always takes the lesser value.
   e.g. If you take the number less than 56 it is 60 so it returns 50.
   e.g. Even if you use 54 it will return 50 only.

81) how to give debug privegles?
      SYS>grant create session,create procedure to scott;

        SYS> grant debug connect session to  scott;

        SYS>grant debug any procedure to scott;

        SYS>grant execute on DBMS_DEBUG_JDWP to scott;

82) WHAT IS TRIGGER ,GIVE SYNTAX?

A) A trigger is a pl/sql block structure which is fired when a DML statements like Insert, Delete, Update is executed on a database table. A trigger is triggered automatically when an associated DML statement is executed.

CREATE [OR REPLACE ] TRIGGER trigger_name 

 {BEFORE | AFTER | INSTEAD OF } 

 {INSERT [OR] | UPDATE [OR] | DELETE} 

 [OF col_name] 

 ON table_name 

 [REFERENCING OLD AS o NEW AS n] 

 [FOR EACH ROW] 

 WHEN (condition)  

 BEGIN 

   --- sql statements  

 END;