How to use Oracle PL/SQL DENSE_RANK Fucntion

What is Oracle DENSE_RANK Function ?

In Oracle PL/SQL, DENSE_RANK function is a built in analytic function which is used to rank (consecutively) a record within a group of row.

The only difference between the DENSE_RANK() and RANK() functions is that RANK() will assign non-consecutive ranks to the values within a group in the case of a tie.

Example :

SELECT DEPT, STUDENT_ID,TOTAL_MARKS,DENSE_RANK() OVER 

(PARTITION BY DEPT ORDER BY TOTAL_MARKS) RANK

FROM STUDENTS_TABLE

/


      DEPT      STUDENT_ID    TOTAL_MARKS       RANK
   
----------      ----------   ------------      ---------

        100      100001        100               1

        100      100009        200               2
                                              
        101      100005        101               1

        101      100004        101               1
                                                  
        101      100002        102               2 
                             
        101      100006        102               2
 
        101      100007        103               3

7 rows selected.

Viewing Defined Triggers in Oracle

Viewing Defined Triggers

To view all the defined triggers, use:

select name_of_trigger from user_triggers;

For more details on a particular trigger:

select trigger_type, triggering_event, table_name, referencing_names, trigger_body
from user_triggers
where trigger_name = '';

Oracle PL/SQL Character / String Functions :ASCII Function

In Oracle/PLSQL, the ascii function returns the NUMBER code that represents the specified character.
Syntax:

ascii( Character_name )

Character_name is the specified character to retrieve the NUMBER code for. If more than one character is entered, the function will return the value for the first character and ignore all of the characters after the first.

Example:

SELECT ASCII('t') FROM dual;  Output :116
SELECT ASCII('T') FROM dual;  Output :84
SELECT ASCII('Thanks') FROM dual; Output :84

Oracle PL/SQL Tutorial :LAST_DAY, TO_CHAR, TO_DATE

Oracle PL/SQL Tutorial :LAST_DAY, TO_CHAR, TO_DATE

SQL>

SQL> SELECT TO_CHAR(LAST_DAY(TO_DATE('01APR2011','ddMONyyyy')), 
 'Month dd, yyyy') FROM dual;

TO_CHAR(LAST_DAY(T
------------------
  April 01, 2011

SQL>
SQL>

Oracle PL/SQL tutorial :SQL Left Join Example

Before we start let start with the syntax of SQL LEFT JOIN, because this will help to understand the rest of the article quite easily

SQL LEFT JOIN Syntax
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name;

The major part of the above syntax is " LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name;"
Notice the keyword LEFT JOIN , what does this mean ? LEFT JOIN returns all the rows from left table even though the rows in the right table do not exist. To explain it clearly lets take an example:

TABLE A

P_Id	LastName	FirstName	Address	City
1	ABC	DEF	Street 10	City 1
2	GHI	JKL	Street 20	City 2
3	MNO	PQR	Street 30	City 3

TABLE B

O_Id	OrderNo	P_Id
1	778951	3
2	446781	3
3	224561	1
4	245621	1
5	347641	15

Now if we join two tables on the above provided Syntax, i.e.

SELECT TABLE A.LastName, TABLE A.FirstName, TABLE B.OrderNo
FROM TABLE A
LEFT JOIN Orders
ON TABLE A.P_Id=TABLE B.P_Id
ORDER BY TABLE A.LastName ;

Yes, you guessed it correctly that all the rows from the TABLE A will be returned without taking into consideration that those rows exits in the TABLE B

The OUTPUT will be

LastName	FirstName	OrderNo
ABC	DEF	224561
ABC	DEF	245621
MNO	PQR	778951
MNO	PQR	446781
		347641

NOTE: In some places SQL LEFT JOIN is also refered as LEFT OUTER JOIN.

Oracle PL/SQL tutorial :SQL Right Join Example

As you have learned about the SQL LEFT JOIN where all the rows are returned from the left table ever when there is no match from the right table, the same case is with the SQL RIGHT JOIN, but here all the rows from the right side table is returned without matching the left side table. Let start with the syntax:

Syntax:-
SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name;

Now look at the two tables, TABLE A and TABLE B:

TABLE A

P_Id	LastName	FirstName	Address	City
1	ABC	DEF	Street 10	City 1
2	GHI	JKL	Street 20	City 2
3	MNO	PQR	Street 30	City 3

TABLE B

O_Id	OrderNo	P_Id
1	778951	3
2	446781	3
3	224561	1
4	245621	1
5	347641	15

Now we apply the SQL RIGHT JOIN query:

SELECT TABLE A.LastName, TABLE A.FirstName, TABLE B.OrderNo
FROM TABLE A
RIGHT JOIN TABLE B
ON TABLE A.P_Id=TABLE B.P_Id
ORDER BY TABLE A.LastName;

The above query will return all the rows from TABLE A even there is no match with TABLE B. That does not mean that no check for common rows are made. The result of the above query will be:

OUTPUT

LastName	FirstName	OrderNo
ABC	DEF	224561
ABC	DEF	245621
MNO	PQR	778951
MNO	PQR	446781
		347641

NOTE: In some places SQL RIGHT JOIN is also refered as RIGHT OUTER JOIN.