ORACLE SUBQUERY/CORRELATED QUERY EXAMPLES

A subquery is a SELECT statement which is used in another SELECT statement. Subqueries are very useful when you need to select rows from a table with a condition that depends on the data of the table itself. You can use the subquery in the SQL clauses including WHERE clause, HAVING clause, FROM clause etc.

The subquery can also be referred as nested SELECT, sub SELECT or inner SELECT. In general, the subquery executes first and its output is used in the main query or outer query.

Types of Sub queries:

There are two types of subqueries in oracle:

• Single Row Subqueries: The subquery returns only one row. Use single row comparison operators like =, > etc while doing comparisions.
• Multiple Row Subqueries: The subquery returns more than one row. Use multiple row comparison operators like IN, ANY, ALL in the comparisons.

SINGLE ROW SUBQUERY EXAMPLES

1. Write a query to find the salary of employees whose salary is greater than the salary of employee whose id is 100?

SELECT EMPLOYEE_ID,
 SALARY
FROM EMPLOYEES
WHERE SALARY >
    (
  SELECT SALARY
  FROM EMPLOYEES
  WHERE EMPLOYEED_ID = 100
  )

2. Write a query to find the employees who all are earning the highest salary?

SELECT EMPLOYEE_ID,
 SALARY
FROM EMPLOYEES
WHERE SALARY =
  (
  SELECT  MAX(SALARY)
  FROM EMPLOYEES
  )

3. Write a query to find the departments in which the least salary is greater than the highest salary in the department of id 200?

SELECT DEPARTMENT_ID,
 MIN(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING MIN(SALARY) > 
  (
  SELECT MAX(SALARY)
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 200
  )

MULTIPLE ROW SUBQUERY EXAMPLES

1. Write a query to find the employees whose salary is equal to the salary of at least one employee in department of id 300?

SELECT EMPLOYEE_ID,
 SALARY
FROM EMPLOYEES
WHERE SALARY IN
  (
  SELECT  SALARY
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 300
  )

2. Write a query to find the employees whose salary is greater than at least on employee in department of id 500?

SELECT EMPLOYEE_ID,
 SALARY
FROM EMPLOYEES
WHERE SALARY > ANY
  (
  SELECT  SALARY
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 500
  )

3. Write a query to find the employees whose salary is less than the salary of all employees in department of id 100?

SELECT EMPLOYEE_ID,
 SALARY
FROM EMPLOYEES
WHERE SALARY < ALL
  (
  SELECT  SALARY
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 100
  )

4. Write a query to find the employees whose manager and department should match with the employee of id 20 or 30? 

SELECT EMPLOYEE_ID,
 MANAGER_ID,
 DEPARTMENT_ID
FROM EMPLOYEES
WHERE (MANAGER_ID,DEPARTMENT_ID) IN
  (
  SELECT MANAGER_ID,
   DEPARTMENT_ID
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID IN (20,30)
  )

5. Write a query to get the department name of an employee?

SELECT EMPLOYEE_ID,
 DEPARTMENT_ID,
 (SELECT DEPARTMENT_NAME
 FROM DEPARTMENTS D
 WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID
 )
FROM EMPLOYEES E

CORRELATED SUBQUERIES EXAMPLES

Correlated sub query is used for row by row processing. The sub query is executed for each row of the main query.

1. Write a query to find the highest earning employee in each department? 

SELECT DEPARTMENT_ID,
 EMPLOYEE_ID,
 SALARY
FROM EMPLOYEES E_0
WHERE 1 = 
  (
  SELECT  COUNT(DISTINCT SALARY)
  FROM EMPLOYEES E_I
  WHERE E_O.DEPARTMENT_ID = E_I.DEPARTMENT_ID
  AND E_O.SALARY <=  E_I.SALARY
  )

2. Write a query to list the department names which have at lease one employee? 

SELECT DEPARTMENT_ID,
 DEPARTMENT_NAME
FROM DEPARTMENTS D
WHERE EXISTS
 (
 SELECT 1
 FROM EMPLOYEES E
 WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID)

3. Write a query to find the departments which do not have employees at all? 

SELECT DEPARTMENT_ID,
 DEPARTMENT_NAME
FROM DEPARTMENTS D
WHERE NOT EXISTS
 (
 SELECT  1
 FROM EMPLOYEES E
 WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID)

Difference between Subquery, Nested Subquery and Correlated Subquery

Query: - Query can be defined as a way to inquire the data from the database. It is used to extract the data from one table or multiple tables depending upon the user needs.

Subquery:-If a sql statement contains another sql statement then the sql statement which is inside another sql statement is called Subquery. It is also known as nested query. The Sql Statement which contains the other sql statement is called Parent Statement.

Nested Subquery:-If a Subquery contains another subquery, then the subquery inside another subquery is called nested subquery.

Correlated Subquery:-If the outcome of a subquery is depends on the value of a column of its parent query table then the Sub query is called Correlated Subquery.

or

A correlated subquery is a subquery that uses values from the outer query, requiring the inner query to execute once for each outer query 

The Oracle database wants to execute the subquery once and use the results for all the evaluations in the outer query. With a correlated subquery, the database must run the subquery for each evaluation because it is based on the outer query’s data.

select

   book_key,

   store_key,

   quantity

from

   sales s

where

  quantity <
   (select max(quantity)

       from sales

       where book_key = s.book_key); 

BOOK_K STOR QUANTITY

------ ---- ----------

B101 S101 1000

B102 S102 10

B102 S103 200

…

B116 S105 100

B101 S105 8000

B109 S109 100

81 rows selected.

In the example above, the subquery references the book_key in the outer query. The value of the book_key changes by row of the outer query, so the database must rerun the subquery for each row comparison. This has a significant performance impact on the execution time of the query, and for that reason, correlated subqueries should be avoided if possible.

The outer query knows nothing about the inner query except its results. For that reason, the outer query cannot reference any columns in the subquery. However, the subquery has access to the outer query and can reference outer query columns, thus the correlated subquery.

DO_KEY built-in

Description

Executes the key trigger that corresponds to the specified built-in subprogram. If no such key trigger exists, then the specified subprogram executes. This behavior is analogous to pressing the corresponding function key.

Syntax

PROCEDURE DO_KEY
(built-in_subprogram_name VARCHAR2);
Built-in Type restricted procedure
Enter Query Mode yes

Parameters

built-in_subprogram_name Specifies the name of a valid built-in subprogram.

Built-in Key Trigger Associated Function Key
BLOCK_MENU Key-MENU [Block Menu]
CLEAR_BLOCK Key-CLRBLK [Clear Block]
CLEAR_FORM Key-CLRFRM [Clear Form]
CLEAR_RECORD Key-CLRREC [Clear Record]
COMMIT_FORM Key-COMMIT [Commit]
COUNT_QUERY Key-CQUERY [Count Query Hits]
CREATE_RECORD Key-CREREC [Insert Record]
DELETE_RECORD Key-DELREC [Delete Record]
DOWN Key-DOWN [Down]
DUPLICATE_ITEM Key-DUP-ITEM [Duplicate Item]
DUPLICATE_RECORD Key-DUPREC [Duplicate Record]
EDIT_TEXTITEM Key-EDIT [Edit]
ENTER Key-ENTER [Enter]
ENTER_QUERY Key-ENTQRY [Enter Query]
EXECUTE_QUERY Key-EXEQRY [Execute Query]
EXIT_FORM Key-EXIT [Exit/Cancel]
HELP Key-HELP [Help]
LIST_VALUES Key-LISTVAL [List]
LOCK_RECORD Key-UPDREC [Lock Record]
NEXT_BLOCK Key-NXTBLK [Next Block]
NEXT_ITEM Key-NEXT-ITEM [Next Item]
NEXT_KEY Key-NXTKEY [Next Primary Key Fld]
NEXT_RECORD Key-NXTREC [Next Record]
NEXT_SET Key-NXTSET [Next Set of Records]
PREVIOUS_BLOCK Key-PRVBLK [Previous Block]
PREVIOUS_ITEM Key-PREV-ITEM [Previous Item]
PREVIOUS_RECORD Key-PRVREC [Previous Record]
PRINT Key-PRINT [Print]
SCROLL_DOWN Key-SCRDOWN [Scroll Down]
SCROLL_UP Key-SCRUP [Scroll Up]
UP Key-UP [Up]


** Built-in: DO_KEY
** Example: Simulate pressing the [Execute Query] key.
*/
BEGIN
Do_Key('Execute_Query');
END;

Queries in SQL ( Oracle ) - Imp

These questions are the most frequently asked in nterviews.

1.     To fetch ALTERNATE records from a table. (EVEN NUMBERED)
select * from emp where rowid in (select decode(mod(rownum,2),0,rowid, null) from emp);

2.     To select ALTERNATE records from a table. (ODD NUMBERED)
select * from emp where rowid in (select decode(mod(rownum,2),0,null ,rowid) from emp);

3.     Find the 3rd MAX salary in the emp table.
select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);

4.     Find the 3rd MIN salary in the emp table.
select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2where e1.sal >= e2.sal);

5.     Select FIRST n records from a table.
select * from emp where rownum <= &n;

6.     Select LAST n records from a table
select * from emp minus select * from emp where rownum <= (select count(*) - &n from emp);

7.     List dept no., Dept name for all the departments in which there are no employees in the department.
select * from dept where deptno not in (select deptno from emp);
alternate solution:  select * from dept a where not exists (select * from emp b where a.deptno = b.deptno);
altertnate solution:  select empno,ename,b.deptno,dname from emp a, dept b where a.deptno(+) = b.deptno and empno is null;

8.     How to get 3 Max salaries ?
select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal <= b.sal) order by a.sal desc;

9.     How to get 3 Min salaries ?
select distinct sal from emp a  where 3 >= (select count(distinct sal) from emp b  where a.sal >= b.sal);

10.  How to get nth max salaries ?
select distinct hiredate from emp a where &n =  (select count(distinct sal) from emp b where a.sal >= b.sal);

11.  Select DISTINCT RECORDS from emp table.
select * from emp a where  rowid = (select max(rowid) from emp b where  a.empno=b.empno);

12.  How to delete duplicate rows in a table?
delete from emp a where rowid != (select max(rowid) from emp b where  a.empno=b.empno);

13.  Count of number of employees in  department  wise.
select count(EMPNO), b.deptno, dname from emp a, dept b  where a.deptno(+)=b.deptno  group by b.deptno,dname;

14.   Suppose there is annual salary information provided by emp table. How to fetch monthly salary of each and every employee?

select ename,sal/12 as monthlysal from emp;

15.  Select all record from emp table where deptno =10 or 40.

select * from emp where deptno=30 or deptno=10;

16.  Select all record from emp table where deptno=30 and sal>1500.

select * from emp where deptno=30 and sal>1500;

17.  Select  all record  from emp where job not in SALESMAN  or CLERK.

select * from emp where job not in ('SALESMAN','CLERK');

18.  Select all record from emp where ename in 'BLAKE','SCOTT','KING'and'FORD'.

select * from emp where ename in('JONES','BLAKE','SCOTT','KING','FORD');

19.  Select all records where ename starts with ‘S’ and its lenth is 6 char.

select * from emp where ename like'S____';

20.  Select all records where ename may be any no of  character but it should end with ‘R’.

select * from emp where ename like'%R';

21.  Count  MGR and their salary in emp table.

select count(MGR),count(sal) from emp;

22.  In emp table add comm+sal as total sal  .

select ename,(sal+nvl(comm,0)) as totalsal from emp;

23.  Select  any salary <3000 from emp table. 

select * from emp  where sal> any(select sal from emp where sal<3000);

24.  Select  all salary <3000 from emp table. 

select * from emp  where sal> all(select sal from emp where sal<3000);

25.  Select all the employee  group by deptno and sal in descending order.

select ename,deptno,sal from emp order by deptno,sal desc;

26.  How can I create an empty table emp1 with same structure as emp?

Create table emp1 as select * from emp where 1=2;

27.  How to retrive record where sal between 1000 to 2000?
Select * from emp where sal>=1000 And  sal<2000

28.  Select all records where dept no of both emp and dept table matches.
select * from emp where exists(select * from dept where emp.deptno=dept.deptno)

29.  If there are two tables emp1 and emp2, and both have common record. How can I fetch all the recods but common records only once?
(Select * from emp) Union (Select * from emp1)

30.  How to fetch only common records from two tables emp and emp1?
(Select * from emp) Intersect (Select * from emp1)

31.   How can I retrive all records of emp1 those should not present in emp2?
(Select * from emp) Minus (Select * from emp1)

32.  Count the totalsa  deptno wise where more than 2 employees exist.
SELECT  deptno, sum(sal) As totalsal
FROM emp
GROUP BY deptno
HAVING COUNT(empno) > 2

Different Optimization Techniques

Optimizer:
Optimization is the process of choosing the most efficient way to execute an SQL statement. When tuning SQL, it’s very important to understand WHY and WHEN a particular type of optimization should be used.
When a SQL Select, Update, Insert or Delete statement is processed, Oracle must access the data referenced by the statement. The Optimizer portion of Oracle is used to determine an efficient path to reference data so that minimal I/O and Processing time is required. For statement execution the Optimizer formulates execution plans and chooses the most efficient plan before executing a statement.
The Optimizer uses one of two techniques:
Rule Based Approach: Using the rule-based approach, the optimizer chooses an execution plan based on the access paths available and the ranks of these access paths. If there is more than one way to execute an SQL statement, the rule-based approach always uses the operation with the lower rank. Usually, operations of lower rank execute faster than those associated with constructs of higher rank. The rule-based optimizer is no longer being enhanced by Oracle. The rule-based optimizer (RBO) Uses a fixed ranking, therefore, it’s essential to list the correct Table Order in the FROM clause. Normally, the (LAST or Right Most) Table listed in the FROM clause is the driving table. The data distribution and number of records is not taken into account. The RBO execution plan can’t be changed by hints.




Cost Based Approach: Using the cost-based approach, the optimizer determines which execution plan is most efficient by considering available access paths and factoring in information based on statistics in the data dictionary for the schema objects (tables, clusters or indexes) accessed by the statement. The cost-based approach also considers hints, or optimization suggestions placed in a Comment in the statement. By default, the goal of the cost-based approach is the best throughput, or minimal resource use necessary to process all rows accessed by the statement. The cost-based approach uses statistics to estimate the cost of each execution plan. These statistics quantify the data distribution and storage characteristics of tables, columns, indexes, and partitions. These statistics are generated by using the ANALYZE command. The cost-based optimizer was built to make tuning easier by choosing the best paths for poorly written queries. Normally, the (FIRST or Left Most) Table listed in the FROM clause is the driving table. Just opposite of RBO. The CBO decisions are only as good as the type and frequency of analyzes made on the Table and associated Indexes. To Tune SQL Queries using CBO you must also have an understanding of Table Access Methods, Table Join Methods and Hints.
The DRIVING Table


In Oracle the cost-based approach uses various factors in determining which table should be the Driving Table (the table that has the most “restricted” rows). It is the table that drives the query in a multi-table join query. The Driving Table should be listed First in Cost Based and listed Last in Rule Based Optimization. The best thing to remember is that you have control over which table will drive a query through the effective use of the ORDERED hint when using the CBO. The easiest way to determine if your query is using the correct Driving Table is to set the SQL Select to find Zero records. If the return is slow you know the Driving Table is incorrect!

Bitmap/B-Tree Index (Difference, Advantages)

A traditional B-Tree (balanced tree) index stores the key values and pointers in an inverted tree structure. The key to good B-Tree index performance is to build the index on columns having a lot of different values. Oracle describes this as "good selectivity" Oracle is able to quickly bypass rows that do not meet the search criteria when searching through indexes built on columns having a high degree of selectivity.
Conversely, bitmapped indexes perform better when the selectivity of an index is poor. The fewer different values a bitmapped index contains, the better it will perform.
Bitmap indexes, in certain situations, can provide impressive performance benefits. Bitmapped indexes are most appropriate for complex and ad-hoc queries that contain lengthy WHERE clauses on columns that have a limited number of different values (poor selectivity).

Standard B-tree indexes are most effective for columns containing a high number of different values (good selectivity) and bitmapped indexes are most appropriate for columns with a limited number (poor selectivity) of possible values.

EXECUTE_TRIGGER built-in

Description

EXECUTE_TRIGGER executes an indicated trigger.

Syntax

PROCEDURE EXECUTE_TRIGGER
(trigger_name VARCHAR2);
Built-in Type restricted procedure (if the user-defined trigger calls any restricted built-in subprograms)
Enter Query Mode yes
Note: EXECUTE_TRIGGER is not the preferred method for executing a user-named trigger: writing a user-named subprogram is the preferred method.

Parameters

trigger_name Specifies the name of a valid user-named trigger.

Usage Notes

Because you cannot specify scope for this built-in, Form Builder always looks for the trigger starting at the lowest level, then working up.
To execute a built-in associated with a key, use the DO_KEY built-in instead of EXECUTE_TRIGGER. For example, rather than:

Execute_Trigger ('KEY-NEXT-ITEM');

Use instead:

Do_Key('NEXT_ITEM');

Autonomous Transaction

Autonomous Transaction is a new feature in ORACLE. It allows setting up independent transactions that can be called from within other transactions. It lets you suspend the main transaction (without committing or rolling back), perform some DML operations, commit or roll back those operations (without any effect on the main transaction), and then return to the main transaction.
Being independent of the main transaction (almost like a separate session), an autonomous transaction does not see the uncommitted changes from the main transaction. It also does not share locks with the main transaction. As a result, it can get into a deadlock with its parent … something the application developer should watch out for.
As expected, changes committed by an autonomous transaction are visible to other sessions/transactions immediately, regardless of whether the main transaction is committed or not. These changes also become visible to the main transaction when it resumes, provided its isolation level is set to READ COMMITTED (which is the default).

Any of the routines can be marked as autonomous simply by using the following syntax anywhere in the declarative section of the routine (putting it at the top is recommended for better readability):
E.g.
PRAGMA AUTONOMOUS_TRANSACTION;
Here is an example of defining a stored procedure as autonomous:
CREATE PROCEDURE process_ord_line_shipment
(p_order_no number, p_line_no number) AS
PRAGMA AUTONOMOUS_TRANSACTION;
l_char_1 varchar2(100);
BEGIN
...END;

Normalization / De-Normalization

Normalization: It's the process of efficiently organizing data in a database. There are two goals of the normalization process: eliminate redundant data (for example, storing the same data in more than one table) and ensure data dependencies make sense (only storing related data in a table). Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored.
1. Eliminate Repeating Groups - Make a separate table for each set of related attributes, and give each table a primary key.
2. Eliminate Redundant Data - If an attribute depends on only part of a multi-valued key, remove it to a separate table.
3. Eliminate Columns Not Dependent On Key - If attributes do not contribute to a description of the key, remove them to a separate table.
4. Isolate Independent Multiple Relationships - No table may contain two or more 1:n or n:m relationships that are not directly related.
5. Isolate Semantically Related Multiple Relationships - There may be practical constrains on information that justify separating logically related many-to-many relationships.
1st Normal Form (1NF)
Def: A table (relation) is in 1NF if
1. There are no duplicated rows in the table.
2. Each cell is single-valued (i.e., there are no repeating groups or arrays).
3. Entries in a column (attribute, field) are of the same kind.
Note: The order of the rows is immaterial; the order of the columns is immaterial.
Note: The requirement that there be no duplicated rows in the table means that the table has a key (although the key might be made up of more than one column—even, possibly, of all the columns).
2nd Normal Form (2NF)
Def: A table is in 2NF if it is in 1NF and if all non-key attributes are dependent on all of the key.
Note: Since a partial dependency occurs when a non-key attribute is dependent on only a part of the (composite) key, the definition of 2NF is sometimes phrased as, "A table is in 2NF if it is in 1NF and if it has no partial dependencies."
3rd Normal Form (3NF)
Def: A table is in 3NF if it is in 2NF and if it has no transitive dependencies.
Boyce-Codd Normal Form (BCNF)
Def: A table is in BCNF if it is in 3NF and if every determinant is a candidate key.
4th Normal Form (4NF)
Def: A table is in 4NF if it is in BCNF and if it has no multi-valued dependencies.
5th Normal Form (5NF)
Def: A table is in 5NF, also called "Projection-Join Normal Form" (PJNF), if it is in 4NF and if every join dependency in the table is a consequence of the candidate keys of the table.
Domain-Key Normal Form (DKNF)
Def: A table is in DKNF if every constraint on the table is a LOGICAL consequence of the definition of keys and domains.

De-Normalization:
Denormalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access. You may apply Denormalization in the process of deriving a physical data model from a logical form.

List Item Types

Poplist: The poplist style list item appears initially as a single field (similar to a text item field). When the end user selects the list icon, a list of available choices appears.
Tlist: The Tlist style list item appears as a rectangular box, which displays a fixed number of values. When the Tlist contains values that cannot be displayed (due to the displayable area of the item), a vertical scroll bar appears, allowing the end user to view and select undisplayed values.
Combo Box: The combo box style list item combines the features found in poplists and text items. It displays fixed values and can accept a user-entered value.

Different Database Triggers

Database triggers are PL/SQL, Java, or C procedures that run implicitly whenever a table or view is modified or when some user actions or database system actions occur. Database triggers can be used in a variety of ways for managing your database. For example, they can automate data generation, audit data modifications, enforce complex integrity constraints, and customize complex security authorizations.
Row Triggers
A row trigger is fired each time the table is affected by the triggering statement.
For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for each row affected by the UPDATE statement.
If a triggering statement affects no rows, a row trigger is not executed at all.
Row triggers are useful if the code in the trigger action depends on data provided by the triggering statement or rows that are affected.
Statement Triggers
A statement trigger is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects (even if no rows are affected).
For example, if a DELETE statement deletes several rows from a table, a statement-level DELETE trigger is fired only once, regardless of how many rows are deleted from the table.
Statement triggers are useful if the code in the trigger action does not depend on the data provided by the triggering statement or the rows affected.
For example, if a trigger makes a complex security check on the current time or user, or if a trigger generates a single audit record based on the type of triggering statement, a statement trigger is used.

BEFORE vs. AFTER Triggers
When defining a trigger, you can specify the trigger timing.
That is, you can specify whether the trigger action is to be executed before or after the triggering statement.
BEFORE and AFTER apply to both statement and row triggers.
BEFORE Triggers BEFORE triggers execute the trigger action before the triggering statement. This type of trigger is commonly used in the following situations:
BEFORE triggers are used when the trigger action should determine whether the triggering statement should be allowed to complete. By using a BEFORE trigger for this purpose, you can eliminate unnecessary processing of the triggering statement and its eventual rollback in cases where an exception is raised in the trigger action.
BEFORE triggers are used to derive specific column values before completing a triggering INSERT or UPDATE statement.

AFTER Triggers AFTER triggers execute the trigger action after the triggering statement is executed. AFTER triggers are used in the following situations:
AFTER triggers are used when you want the triggering statement to complete before executing the trigger action.
If a BEFORE trigger is already present, an AFTER trigger can perform different actions on the same triggering statement.

Combinations
Using the options listed in the previous two sections, you can create four types of triggers:
BEFORE statement trigger Before executing the triggering statement, the trigger action is executed.
BEFORE row trigger Before modifying each row affected by the triggering statement and before checking appropriate integrity constraints, the trigger action is executed provided that the trigger restriction was not violated.
AFTER statement trigger After executing the triggering statement and applying any deferred integrity constraints, the trigger action is executed.
AFTER row trigger After modifying each row affected by the triggering statement and possibly applying appropriate integrity constraints, the trigger action is executed for the current row provided the trigger restriction was not violated. Unlike BEFORE row triggers, AFTER row triggers lock rows.

New Database Triggers
Startup, Shutdown, Logon, Logoff, Alter, Create, Drop

Various Cursor Attributes

SQL%ROWCOUNT: Number of rows affected by most recent SQL statement.
SQL%FOUND: Boolean attribute that evaluates to TRUE if most recent SQL statement affects one or more rows.
SQL%NOTFOUND: Boolean attribute that evaluates to TRUE if most recent SQL statement does not affect any row.
SQL%ISOPEN: Always evaluates to FALSE because P/L SQL closes implicit cursors immediately after they are executed.

P/L SQL Tables / Arrays

PL/SQL tables are declared in the declaration portion of the block. A table is a composite datatype in PL/SQL. PL/SQL tables can have one column and a primary key neither of which can be named. The column can be any scalar type but primary key should be a BINARY_INTEGER datatype.
Rules for PL/SQL Tables:
1. A loop must be used to insert values into a PL/SQL Table
2. You cannot use the Delete command to delete the contents of PL/SQL Table. You must assign an empty table to the PL/SQL table being deleted.

Diff b/w Package Spec & Body

Packages provide a method of encapsulating and storing related procedures, funtions and other package constructs as a unit in the database. They offer increased functionality (for example, global package variables can be declared and used by any procedure in the package). They also improve performance (for example, all objects of the package are parsed, compiled, and loaded into memory once).
Package specification contains declarations of public constructs where as the package body contains definitions of all those public constructs and declarations & definitions of private constructs.

Column Mode Property in Reports

The Column Mode property controls how Report Builder fetches and formats data for instances of repeating frames. With Column Mode set to Yes, the next instance of a repeating frame can begin formatting before the previous instance is completed. With Column Mode set to No, the next instance cannot begin formatting before the previous instance is completed. Column Mode is used mainly for master repeating frames or repeating frames that contain fields that may expand vertically or horizontally (i.e., elasticity is Variable or Expand).

Lexical & Bind Parameters in Reports

Lexical Parameters: Lexical references are placeholders for text that you embed in a SELECT statement. You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH.
You cannot make lexical references in a PL/SQL statement. You can, however, use a bind reference in PL/SQL to set the value of a parameter that is then referenced lexically in SQL. Look at the example below.
You create a lexical reference by entering an ampersand (&) followed immediately by the column or parameter name. A default definition is not provided for lexical references. Therefore, you must do the following:
Ø Before you create your query, define a column or parameter in the data model for each lexical reference in the query. For columns, you must enter Value if Null, and, for parameters, you must enter Initial Value. Report Builder uses these values to validate a query with a lexical reference.
Ø Create your query containing lexical references.

Bind Parameters: Bind references (or bind variables) are used to replace a single value in SQL or PL/SQL, such as a character string, number, or date. Specifically, bind references may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH clauses of queries. Bind references may not be referenced in FROM clauses or in place of reserved words or clauses.
You create a bind reference by entering a colon (:) followed immediately by the column or parameter name. If you do not create a column or parameter before making a bind reference to it in a SELECT statement, Report Builder will create a parameter for you by default.

Matrix Reports (Matrix By Groups)

A matrix (cross tab) report contains one row of labels, one column of labels, and information in a grid format that is related to the row and column labels. A distinguishing feature of matrix reports is that the number of columns is not known until the data is fetched from the database.
To create a matrix report, you need at least four groups: one group must be a cross-product group, two of the groups must be within the cross-product group to furnish the "labels," and at least one group must provide the information to fill the cells. The groups can belong to a single query or to multiple queries.

A matrix with group report is a group above report with a separate matrix for each value of the master group.

A nested matrix (crosstab) report is a matrix report in which at least one parent/child relationship appears within the matrix grid.
The new Child Dimension property of the nested group enables you to eliminate empty rows and/or columns in your single-query nested matrix.

Types of Matrix Reports
Simple Matrix Report:
Is a matrix with only two dimensions

Nested Matrix Report: Has multiple dimensions going across and/or down the page
Multi-Query Matrix with Break: Is similar to a nested matrix report in that it has more than two dimensions. Does not display records that do not contain data
Matrix Break Reports: Contains a new matrix for each master record

Flex & Confine Mode in Reports

Confine mode:
a. Switched on by default; change via View® View Options® Layout...
b. It prevents operations which would cause a report not to work e.g. moving a field outside its parent repeating frame
Flex mode:
Moves the object it’s enclosing objects and objects in their push path simultaneously to maintain the same overall relationship in the report. E.g. if you try to move a field outside its repeating frame, the Repeating Frame will grow to accommodate the field and so will any objects around the repeating frame.
Only one object can be moved/resized at one time in flex mode - if you try more than one only one whose control point is clicked on will be done, the other objects will be de-selected.
Objects can be moved/resized horizontally or vertically; not diagonally.

Format Triggers (What are they)

A format trigger is a PL/SQL function executed before an object is formatted. A trigger can be used to dynamically change the formatting attributes of the object. 

Static & Dynamic LOV

The static LOV contains the predetermined values while the dynamic LOV contains values that come at run time

Report Level Triggers (Sequence)

· Before parameter form
· After parameter form
· Before Report
· Between Pages
· After Report

How to attach same LOV to multiple items

We can use the same LOV for 2 columns by passing the return values in global values and using the global values in the code.

Various Block Co-ordination Properties

The various Block Coordination Properties are
a) Immediate
Default Setting. The Detail records are shown when the Master Record are shown.
b) Deffered with Auto Query
Oracle Forms defer fetching the detail records until the operator navigates to the detail block.
c) Deferred with No Auto Query
The operator must navigate to the detail block and explicitly execute a query

Object Groups (Use)

An object group is a container for a group of objects. You define an object group when you want to package related objects so you can copy or subclass them in another module.
Object groups provide a way to bundle objects into higher-level building blocks that can be used in other parts of an application and in subsequent development projects. For example, you might build an appointment scheduler in a form and then decide to make it available from other forms in your applications. The scheduler would probably be built from several types of objects, including a window and canvas, blocks, and items that display dates and appointments, and triggers that contain the logic for scheduling and other functionality. If you packaged these objects into an object group, you could then copy them to any number of other forms in one simple operation.
You can create object groups in form and menu modules. Once you create an object group, you can add and remove objects to it as desired.

Types of Canvases (Stacked/Content Difference)

(i) Content Canvas (Default Canvas) [A content canvas is the required on each window you create]
(ii) Stack Canvas [you can display more then one stack canvas in a window at the same time]
(iii) Tab Type Window [In Tab canvas that have tab pages and have one or more then tab page]
(iv) Toolbar Canvas [A toolbar canvas often is used to create Toolbar Windows. There are two type of Toolbar window.
a. Horizontal Toolbar Canvas: - Horizontal Toolbar canvases are displayed at the top of the window, Just Under the Main Menu Bar.
b. Vertical Toolbar Canvas: - While vertical Toolbar are displayed along the Left Edge of the window.

Call From/New Form/Open Form (Difference)

Call Form: Runs an indicated form while keeping the parent form active. Form Builder runs the called form with the same Runform preferences as the parent form. When the called form is exited Form Builder processing resumes in the calling form at the point from which you initiated the call to CALL_FORM.
PROCEDURE CALL_FORM (formmodule_name VARCHAR2, display NUMBER, switch_menu NUMBER, query_mode NUMBER, data_mode NUMBER, paramlist_name/id VARCHAR2);

New Form: Exits the current form and enters the indicated form. The calling form is terminated as the parent form. If the calling form had been called by a higher form, Form Builder keeps the higher call active and treats it as a call to the new form. Form Builder releases memory (such as database cursors) that the terminated form was using.
Form Builder runs the new form with the same Runform options as the parent form. If the parent form was a called form, Form Builder runs the new form with the same options as the parent form.
PROCEDURE NEW_FORM (formmodule_name VARCHAR2, rollback_mode NUMBER, query_mode NUMBER, data_mode NUMBER, paramlist_name/id VARCHAR2);

Open Form: Opens the indicated form. Use OPEN_FORM to create multiple-form applications, that is, applications that open more than one form at the same time.
PROCEDURE OPEN_FORM (form_name VARCHAR2, activate_mode NUMBER, session_mode NUMBER, data_mode NUMBER, paramlist_id/name PARAMLIST);

Key-next/Post-Text (Difference)

Post-Text–Item: Fires during the Leave the Item process for a text item. Specifically, this trigger fires when the input focus moves from a text item to any other item.

Key-Next-Item: The key-next is fired as a result of the key action. Key next will not fire unless there is a key event.

Object Libraries (Use/Benefits)

The Object Library provides an easy method of reusing objects and enforcing standards across the entire development organization.
Object Library can be used to:

1. Create, store, maintain, and distribute standard and reusable objects.
2. Rapidly create applications by dragging and dropping predefined objects to your form.

There are several advantages to using object libraries to develop applications:

1. Object libraries are automatically re-opened when you startup Form Builder, making your reusable objects immediately accessible.
2. You can associate multiple object libraries with an application. For example, you can create an object library specifically for corporate standards, and you can create an object library to satisfy project-specific requirements.
3. Object libraries feature Smart Classes-- objects that you define as being the standard. You use Smart Classes to convert objects to standard objects.

Dynamically create LOV/List Item

You can also add list elements individually at runtime by using the ADD_LIST_ELEMENT built-in subprogram, or you can populate the list from a record group at runtime using the POPULATE_LIST built-in. If you populate the list from a record group, be sure that the record group you are using to populate the list contains the relevant values before you call POPULATE_LIST. If the record group is a static record group, it will already contain the appropriate values. Otherwise, you should populate the group at runtime using one of the record group subprograms.

Types of Triggers in Forms and other imp details

Block-processing triggers: - Block processing triggers fire in response to events related to record management in a block. E.g. When-Create-Record, When-Clear-Block, When-Database-Record, When-Remove-Record

Interface event triggers: - Interface event triggers fire in response to events that occur in the form interface. Some of these triggers, such as When-Button-Pressed, fire only in response to operator input or manipulation. Others, like When-Window-Activated, can fire in response to both operator input and programmatic control. E.g. When-Button-Pressed, When-Checkbox-Changed, Key- [all], When-Radio-Changed, When-Timer-Expired, When-Window-Activated, When-Window-Resized

Master-detail triggers: - Form Builder generates master-detail triggers automatically when you define a master-detail relation between blocks. The default master-detail triggers enforce coordination between records in a detail block and the master record in a master block. Unless you are developing your own custom block-coordination scheme, you do not need to define these triggers yourself. Instead, simply create a relation object, and let Form Builder generate the triggers required to manage coordination between the master and detail blocks in the relation. E.g. On-Check-Delete-Master, On-Clear-Details, On-Populate-Details

Message-handling triggers: - Form Builder automatically issues appropriate error and informational messages in response to runtime events. Message handling triggers fire in response to these default messaging events. E.g. On-Error, On-Message

Navigational triggers: - Navigational triggers fire in response to navigational events. For instance, when the operator clicks on a text item in another block, navigational events occur as Form Builder moves the input focus from the current item to the target item. Navigational events occur at different levels of the Form Builder object hierarchy (Form, Block, Record, Item). Navigational triggers can be further sub-divided into two categories: Pre- and Post- triggers, and When-New-Instance triggers. Pre- and Post- triggers fire as Form Builder navigates internally through different levels of the object hierarchy. As you might expect, these triggers fire in response to navigation initiated by an operator, such as pressing the [Next Item] key. However, be aware that these triggers also fire in response to internal navigation that Form Builder performs during default processing. To avoid unexpected results, you must consider such internal navigation when you use these triggers. E.g. Pre-Form, Pre-Block, Pre-Text-Item, Post-Text-Item, Post-Record, Post-Block, Post-Form
When-New-Instance triggers fire at the end of a navigational sequence that places the input focus in a different item. Specifically, these triggers fire just after Form Builder moves the input focus to a different item, when the form returns to a quiet state to wait for operator input. Unlike the Pre- and Post- navigational triggers, the When-New-Instance triggers do not fire in response to internal navigational events that occur during default form processing. E.g. When-New-Form-Instance, When-New-Block-Instance, When-New-Record-Instance, When-New-Item-Instance

Query-time triggers: - Query-time triggers fire just before and just after the operator or the application executes a query in a block. E.g. Pre-Query, Post-Query

Transactional triggers: - Transactional triggers fire in response to a wide variety of events that occur as a form interacts with the data source. E.g. On-Delete, On-Insert, On-Lock, On-Logon, On-Update, Post-Database-Commit, Post-Delete, Post-Forms-Commit, Post-Insert, Post-Update, Pre-Commit, Pre-Delete, Pre-Insert, Pre-Update

Validation triggers: - Validation triggers fire when Form Builder validates data in an item or record. Form Builder performs validation checks during navigation that occurs in response to operator input, programmatic control, or default processing, such as a Commit operation. E.g. When-Validate-Item, When-Validate-Record

2. Sequence of Trigger Fire while Committing

Ø KEY Commit
Ø Pre Commit
Ø Pre/On/Post Delete
Ø Pre/On/Post Update
Ø Pre/On/Post Insert
Ø On commit
Ø Post Database Commit

3. Master-Detail Relation (Triggers/Procedures/Properties)

On-Check-Delete-Master: - Fires when Form Builder attempts to delete a record
in a block that is a master block in a master-detail relation.
On-Clear-Details: - Fires when Form Builder needs to clear records in a block
that is a detail block in a master-detail relation because those records no longer
correspond to the current record in the master block.
On-Populate-Details: - Fires when Form Builder needs to fetch records into a block that is the detail block in a master-detail relation so that detail records are synchronized with the current record in the master block.
(i) Isolated: - Masters Can be deleted when Child is existing
Triggers: - On Populate details Block
On Clear Details Form
Procedure
Check Package Failure
Clear all master Detail
Query Master Detail
(ii) Non- Isolated: - Masters Cannot be deleted when Child is existing.
Triggers: - On Populate details Block
On Check Delete master Block
On Clear Details Form
Procedure
Check Package Failure
Clear all master Detail
Query Master Detail
(iii) Cascading: - Child Record Automatically Deleted when Masters is deleted.
Triggers: - On Populate details Block
Pre Delete Block
On Clear Details Form
Procedure
Check Package Failure
Clear all master Detail
Query Master Detail

4. Dynamically create LOV/List Item

You can also add list elements individually at runtime by using the ADD_LIST_ELEMENT built-in subprogram, or you can populate the list from a record group at runtime using the POPULATE_LIST built-in. If you populate the list from a record group, be sure that the record group you are using to populate the list contains the relevant values before you call POPULATE_LIST. If the record group is a static record group, it will already contain the appropriate values. Otherwise, you should populate the group at runtime using one of the record group subprograms.

5. Object Libraries (Use/Benefits)

The Object Library provides an easy method of reusing objects and enforcing standards across the entire development organization.
Object Library can be used to:

1. Create, store, maintain, and distribute standard and reusable objects.
2. Rapidly create applications by dragging and dropping predefined objects to your form.

There are several advantages to using object libraries to develop applications:

1. Object libraries are automatically re-opened when you startup Form Builder, making your reusable objects immediately accessible.
2. You can associate multiple object libraries with an application. For example, you can create an object library specifically for corporate standards, and you can create an object library to satisfy project-specific requirements.
3. Object libraries feature Smart Classes-- objects that you define as being the standard. You use Smart Classes to convert objects to standard objects.

6. Key-next/Post-Text (Difference)

Post-Text–Item: Fires during the Leave the Item process for a text item. Specifically, this trigger fires when the input focus moves from a text item to any other item.

Key-Next-Item: The key-next is fired as a result of the key action. Key next will not fire unless there is a key event.

7. Call From/New Form/Open Form (Difference)

Call Form: Runs an indicated form while keeping the parent form active. Form Builder runs the called form with the same Runform preferences as the parent form. When the called form is exited Form Builder processing resumes in the calling form at the point from which you initiated the call to CALL_FORM.
PROCEDURE CALL_FORM (formmodule_name VARCHAR2, display NUMBER, switch_menu NUMBER, query_mode NUMBER, data_mode NUMBER, paramlist_name/id VARCHAR2);

New Form: Exits the current form and enters the indicated form. The calling form is terminated as the parent form. If the calling form had been called by a higher form, Form Builder keeps the higher call active and treats it as a call to the new form. Form Builder releases memory (such as database cursors) that the terminated form was using.
Form Builder runs the new form with the same Runform options as the parent form. If the parent form was a called form, Form Builder runs the new form with the same options as the parent form.
PROCEDURE NEW_FORM (formmodule_name VARCHAR2, rollback_mode NUMBER, query_mode NUMBER, data_mode NUMBER, paramlist_name/id VARCHAR2);

Open Form: Opens the indicated form. Use OPEN_FORM to create multiple-form applications, that is, applications that open more than one form at the same time.
PROCEDURE OPEN_FORM (form_name VARCHAR2, activate_mode NUMBER, session_mode NUMBER, data_mode NUMBER, paramlist_id/name PARAMLIST);

8. Types of Canvases (Stacked/Content Difference)

(i) Content Canvas (Default Canvas) [A content canvas is the required on each window you create]
(ii) Stack Canvas [you can display more then one stack canvas in a window at the same time]
(iii) Tab Type Window [In Tab canvas that have tab pages and have one or more then tab page]
(iv) Toolbar Canvas [A toolbar canvas often is used to create Toolbar Windows. There are two type of Toolbar window.
a. Horizontal Toolbar Canvas: - Horizontal Toolbar canvases are displayed at the top of the window, Just Under the Main Menu Bar.
b. Vertical Toolbar Canvas: - While vertical Toolbar are displayed along the Left Edge of the window.

9. Object Groups (Use)

An object group is a container for a group of objects. You define an object group when you want to package related objects so you can copy or subclass them in another module.
Object groups provide a way to bundle objects into higher-level building blocks that can be used in other parts of an application and in subsequent development projects. For example, you might build an appointment scheduler in a form and then decide to make it available from other forms in your applications. The scheduler would probably be built from several types of objects, including a window and canvas, blocks, and items that display dates and appointments, and triggers that contain the logic for scheduling and other functionality. If you packaged these objects into an object group, you could then copy them to any number of other forms in one simple operation.
You can create object groups in form and menu modules. Once you create an object group, you can add and remove objects to it as desired.

10. Various Block Co-ordination Properties

The various Block Coordination Properties are
a) Immediate
Default Setting. The Detail records are shown when the Master Record are shown.
b) Deffered with Auto Query
Oracle Forms defer fetching the detail records until the operator navigates to the detail block.
c) Deferred with No Auto Query
The operator must navigate to the detail block and explicitly execute a query




11. How to attach same LOV to multiple items

We can use the same LOV for 2 columns by passing the return values in global values and using the global values in the code.

12. Report Level Triggers (Sequence)

· Before parameter form
· After parameter form
· Before Report
· Between Pages
· After Report

13. Static & Dynamic LOV

The static LOV contains the predetermined values while the dynamic LOV contains values that come at run time

14. Format Triggers (What are they)

A format trigger is a PL/SQL function executed before an object is formatted. A trigger can be used to dynamically change the formatting attributes of the object.

15. Flex & Confine Mode in Reports
Confine mode:
a. Switched on by default; change via View® View Options® Layout...
b. It prevents operations which would cause a report not to work e.g. moving a field outside its parent repeating frame
Flex mode:
Moves the object it’s enclosing objects and objects in their push path simultaneously to maintain the same overall relationship in the report. E.g. if you try to move a field outside its repeating frame, the Repeating Frame will grow to accommodate the field and so will any objects around the repeating frame.
Only one object can be moved/resized at one time in flex mode - if you try more than one only one whose control point is clicked on will be done, the other objects will be de-selected.
Objects can be moved/resized horizontally or vertically; not diagonally.
16. Matrix Reports (Matrix By Groups)

A matrix (cross tab) report contains one row of labels, one column of labels, and information in a grid format that is related to the row and column labels. A distinguishing feature of matrix reports is that the number of columns is not known until the data is fetched from the database.
To create a matrix report, you need at least four groups: one group must be a cross-product group, two of the groups must be within the cross-product group to furnish the "labels," and at least one group must provide the information to fill the cells. The groups can belong to a single query or to multiple queries.

A matrix with group report is a group above report with a separate matrix for each value of the master group.

A nested matrix (crosstab) report is a matrix report in which at least one parent/child relationship appears within the matrix grid.
The new Child Dimension property of the nested group enables you to eliminate empty rows and/or columns in your single-query nested matrix.

Types of Matrix Reports
Simple Matrix Report:
Is a matrix with only two dimensions

Nested Matrix Report: Has multiple dimensions going across and/or down the page
Multi-Query Matrix with Break: Is similar to a nested matrix report in that it has more than two dimensions. Does not display records that do not contain data
Matrix Break Reports: Contains a new matrix for each master record


17. Lexical & Bind Parameters in Reports

Lexical Parameters: Lexical references are placeholders for text that you embed in a SELECT statement. You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH.
You cannot make lexical references in a PL/SQL statement. You can, however, use a bind reference in PL/SQL to set the value of a parameter that is then referenced lexically in SQL. Look at the example below.
You create a lexical reference by entering an ampersand (&) followed immediately by the column or parameter name. A default definition is not provided for lexical references. Therefore, you must do the following:
Ø Before you create your query, define a column or parameter in the data model for each lexical reference in the query. For columns, you must enter Value if Null, and, for parameters, you must enter Initial Value. Report Builder uses these values to validate a query with a lexical reference.
Ø Create your query containing lexical references.

Bind Parameters: Bind references (or bind variables) are used to replace a single value in SQL or PL/SQL, such as a character string, number, or date. Specifically, bind references may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH clauses of queries. Bind references may not be referenced in FROM clauses or in place of reserved words or clauses.
You create a bind reference by entering a colon (:) followed immediately by the column or parameter name. If you do not create a column or parameter before making a bind reference to it in a SELECT statement, Report Builder will create a parameter for you by default.

18. Column Mode Property in Reports

The Column Mode property controls how Report Builder fetches and formats data for instances of repeating frames. With Column Mode set to Yes, the next instance of a repeating frame can begin formatting before the previous instance is completed. With Column Mode set to No, the next instance cannot begin formatting before the previous instance is completed. Column Mode is used mainly for master repeating frames or repeating frames that contain fields that may expand vertically or horizontally (i.e., elasticity is Variable or Expand).

19. Diff b/w Package Spec & Body

Packages provide a method of encapsulating and storing related procedures, funtions and other package constructs as a unit in the database. They offer increased functionality (for example, global package variables can be declared and used by any procedure in the package). They also improve performance (for example, all objects of the package are parsed, compiled, and loaded into memory once).
Package specification contains declarations of public constructs where as the package body contains definitions of all those public constructs and declarations & definitions of private constructs.

20. P/L SQL Tables / Arrays

PL/SQL tables are declared in the declaration portion of the block. A table is a composite datatype in PL/SQL. PL/SQL tables can have one column and a primary key neither of which can be named. The column can be any scalar type but primary key should be a BINARY_INTEGER datatype.
Rules for PL/SQL Tables:
1. A loop must be used to insert values into a PL/SQL Table
2. You cannot use the Delete command to delete the contents of PL/SQL Table. You must assign an empty table to the PL/SQL table being deleted.



21. Various Cursor Attributes

SQL%ROWCOUNT: Number of rows affected by most recent SQL statement.
SQL%FOUND: Boolean attribute that evaluates to TRUE if most recent SQL statement affects one or more rows.
SQL%NOTFOUND: Boolean attribute that evaluates to TRUE if most recent SQL statement does not affect any row.
SQL%ISOPEN: Always evaluates to FALSE because P/L SQL closes implicit cursors immediately after they are executed.

22. Different Database Triggers

Database triggers are PL/SQL, Java, or C procedures that run implicitly whenever a table or view is modified or when some user actions or database system actions occur. Database triggers can be used in a variety of ways for managing your database. For example, they can automate data generation, audit data modifications, enforce complex integrity constraints, and customize complex security authorizations.
Row Triggers
A row trigger is fired each time the table is affected by the triggering statement.
For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for each row affected by the UPDATE statement.
If a triggering statement affects no rows, a row trigger is not executed at all.
Row triggers are useful if the code in the trigger action depends on data provided by the triggering statement or rows that are affected.
Statement Triggers
A statement trigger is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects (even if no rows are affected).
For example, if a DELETE statement deletes several rows from a table, a statement-level DELETE trigger is fired only once, regardless of how many rows are deleted from the table.
Statement triggers are useful if the code in the trigger action does not depend on the data provided by the triggering statement or the rows affected.
For example, if a trigger makes a complex security check on the current time or user, or if a trigger generates a single audit record based on the type of triggering statement, a statement trigger is used.

BEFORE vs. AFTER Triggers
When defining a trigger, you can specify the trigger timing.
That is, you can specify whether the trigger action is to be executed before or after the triggering statement.
BEFORE and AFTER apply to both statement and row triggers.
BEFORE Triggers BEFORE triggers execute the trigger action before the triggering statement. This type of trigger is commonly used in the following situations:
BEFORE triggers are used when the trigger action should determine whether the triggering statement should be allowed to complete. By using a BEFORE trigger for this purpose, you can eliminate unnecessary processing of the triggering statement and its eventual rollback in cases where an exception is raised in the trigger action.
BEFORE triggers are used to derive specific column values before completing a triggering INSERT or UPDATE statement.

AFTER Triggers AFTER triggers execute the trigger action after the triggering statement is executed. AFTER triggers are used in the following situations:
AFTER triggers are used when you want the triggering statement to complete before executing the trigger action.
If a BEFORE trigger is already present, an AFTER trigger can perform different actions on the same triggering statement.

Combinations
Using the options listed in the previous two sections, you can create four types of triggers:
BEFORE statement trigger Before executing the triggering statement, the trigger action is executed.
BEFORE row trigger Before modifying each row affected by the triggering statement and before checking appropriate integrity constraints, the trigger action is executed provided that the trigger restriction was not violated.
AFTER statement trigger After executing the triggering statement and applying any deferred integrity constraints, the trigger action is executed.
AFTER row trigger After modifying each row affected by the triggering statement and possibly applying appropriate integrity constraints, the trigger action is executed for the current row provided the trigger restriction was not violated. Unlike BEFORE row triggers, AFTER row triggers lock rows.

New Database Triggers
Startup, Shutdown, Logon, Logoff, Alter, Create, Drop

23. List Item Types

Poplist: The poplist style list item appears initially as a single field (similar to a text item field). When the end user selects the list icon, a list of available choices appears.
Tlist: The Tlist style list item appears as a rectangular box, which displays a fixed number of values. When the Tlist contains values that cannot be displayed (due to the displayable area of the item), a vertical scroll bar appears, allowing the end user to view and select undisplayed values.
Combo Box: The combo box style list item combines the features found in poplists and text items. It displays fixed values and can accept a user-entered value.