Author: Awais Farooq

In Oracle, you can define procedures that are implicitly executed when an INSERT, UPDATE or DELETE statement is issued against the associated table. These procedures are called database triggers. There are six CREATE TRIGGER statements according to their firing points. Firing Point: BEFORE Firing Point: AFTER

A cursor is a pointer to a private SQL area that stores information about the processing of a SELECT or DML statements like INSERT, UPDATE, DELETE or MERGE. Cursor is a mechanism which facilitates you to assign a name to a SELECT statement and manipulate the information within that SQL statement. How to declare cursor…

A function is a subprogram that is used to return a single value. You must declare and define a function before invoking it. It can be declared and defined at a same time or can be declared first and defined later in the same block. CREATE function in Oracle Syntax You must have define some…

A procedure is a group of PL/SQL statements that can be called by name. The call specification (sometimes called call spec) specifies a java method or a third-generation language routine so that it can be called from SQL and PL/SQL. Create Procedure Syntax Following are the three types of procedures that must be defined to…

Semi-join is introduced in Oracle 8.0. It provides an efficient method of performing a WHERE EXISTS sub-query. A semi-join returns one copy of each row in first table for which at least one match is found. Semi-joins are written using the EXISTS construct. Oracle Semi Join Example Let’s take two tables “departments” and “customer” Departments…

Anti-join is used to make the queries run faster. It is a very powerful SQL construct Oracle offers for faster queries. Anti-join between two tables returns rows from the first table where no matches are found in the second table. It is opposite of a semi-join. An anti-join returns one copy of each row in…

The CROSS JOIN specifies that all rows from first table join with all of the rows of second table. If there are “x” rows in table1 and “y” rows in table2 then the cross join result set have x*y rows. It normally happens when no matching join columns are specified. In simple words you can…

Self Join is a specific type of Join. In Self Join, a table is joined with itself (Unary relationship). A self join simply specifies that each rows of a table is combined with itself and every other row of the table. Syntax Oracle SELF JOIN Example Let’s take a table “customers”. Join this table using…

Oracle Equi join returns the matching column values of the associated tables. It uses a comparison operator in the WHERE clause to refer equality. Syntax Equijoin also can be performed by using JOIN keyword followed by ON keyword and then specifying names of the columns along with their associated tables to check equality. Syntax Oracle…

An outer join is similar to equijoin but it gets also the non-matched rows from the table. It is categorized in Left Outer Join, Right Outer Join and Full Outer Join by Oracle 9i ANSI/ISO 1999 standard. Left Outer Join Left Outer Join returns all rows from the left (first) table specified in the ON…