ORACLE-FORU - PL/SQL code for SPACE AFTER EACH LETTER

pl/sql program to insert a space after each  letter in a  given string.

 Procedure:

declare
     str varchar(10):='&str';
     len number(2);
      i number(3):=1;
      newstr varchar(20);
 begin
    len:=length(str);
     while i<=len
     loop
        newstr:=newstr||substr(str,i,1)||' ';
         i:=i+1;
     end loop;
      dbms_output.put_line(newstr);
    end;
     /

Execution:

SQL> @e:\plsql\9b.sql
Enter value for str: svcet
old   2:   str varchar(10):='&str';
new   2:   str varchar(10):='svcet';

s v c e t

PL/SQL procedure successfully completed.


Conclusion:
a pl/sql program is successfully executed for to insert a space after each letter.
INSERTING A ROW USING PL/SQL

Read More

ORACLE-FORU - PL/SQL code for LETTER BY LETTER

pl/sql program to print a string in a letter by letter format.

Procedure:

  declare
      str varchar(10):='&str';
      len number(2);
      i number(3):=1;
  begin
      len:=length(str);
      while i<=len
      loop
          Dbms_output.put_line(substr(str,i,1));
           i :=i+1;
     end loop;
   end;
  /

Output:

SQL> @e:\plsql\9a.sql
Enter value for str: svcet
old   2:   str varchar(10):='&str';
new   2:   str varchar(10):='svcet';
s
v
c
e
t

PL/SQL procedure successfully completed.

Conclusion:
       a pl/sql program is successfully executed for printing a string letter by letter.

Read More

ORACLE-FORU - PL/SQL code to check given number is EVEN OR ODD

pl/sql program to find given number is even or odd.

Procedure:

Declare
     n number(4):=&n;
 Begin
     if mod(n,2)=0
    then
       dbms_output.put_line(n||' even number');
    else
      dbms_output.put_line(n||' odd number');
   end if;
end;
/

Execution:

SQL> @e:\plsql\even.sql
Enter value for n: 5
old   2:  n number(4):=&n;
new   2:  n number(4):=5;

5 odd number

PL/SQL procedure successfully completed.

Conclusion:
       a pl/sql program is successfully executed for finding even or odd.

Read More

ORACLE-FORU - PL/SQL code for FIBINOCCI SERIES

pl/sql program to generate fibinocci series.

Procedure:
   declare
      a number(3):=1;
      b number(3):=1;
      c number(3);
      n number(3):=&n;
begin
  Dbms_output.put_line('the fibinocci series is:');
  while a<=n
    loop
        dbms_output.put_line(a);
        c:=a+b;
        a:=b;
        b:=c;
     end loop;
  end;
 /

Execution:
SQL> @e:\plsql\fibi.sql
Enter value for n: 13
old   5:   n number(3):=&n;
new   5:   n number(3):=13;
the fibinocci series is:
1
1
2
3
5
8
13
PL/SQL procedure successfully completed.

Conclusion:
       a pl/sql program is successfully executed for to generate fibinocci series.

Read More

ORACLE-FORU - PL/SQL code for Reverse Of A Number

pl/sql program to generate reverse for given number.

Procedure:

declare
     n number(4):=&n;
     s number(4):=0;
     r number(4);
 begin
     while n>0
     loop
      r:=mod(n,10);
      s:=(s*10)+r;
      n:=trunc(n/10);
     end loop;
   dbms_output.put_line(‘the reverse number is:’);
   dbms_output.put_line(s);
end;
/

Execution:

SQL> @e:\plsql\rev.sql
Enter value for n: 457
old   2:   n number(4):=&n;
new   2:   n number(4):=457;

the reverse number is:
754

PL/SQL procedure successfully completed.


Conclusion:
       a pl/sql program is successfully executed to generate reverse number for given number.

Read More

ORACLE-FORU - PL/SQL code for FACTORIAL OF A NUMBER

pl/sql program to finding factorial of given number.

Procedure:

 declare
       i number(4):=1;
       n number(4):=&n;
       f number(4):=1;
 begin
    for i in 1..n
    loop
       f:=f*i;
     end loop;
    Dbms_output.put_line('the factorial of '||n||' is:'||f);
    end;
   /

Output:

SQL> @e:\plsql\fact.sql
Enter value for n: 5
old   3:   n number(4):=&n;
new   3:   n number(4):=5;
the factorial of 5 is:120

PL/SQL procedure successfully completed.


Conclusion:
       a pl/sql program is successfully executed for finding factorial of a given number

Read More

ORACLE-FORU - PL/SQL code for PRIME NUMBER

pl/sql program to check weather given number is Prime or not.

Procedure:

  declare
       num number;
       i number:=1;
       c number:=0;
  begin
        num:=&num;
       for i in 1..num
       loop
          if((mod(num,i))=0)
           then
              c:=c+1;
         end if;
      end loop;
     if(c>2)
     then
         dbms_output.put_line(num||' not a prime');
     else
        dbms_output.put_line(num||' is prime');
     end if;
  end;
   /

Execution:

SQL> @e:\plsql\prime.sql
Enter value for num: 5
old   6:  num:=&num;
new   6:  num:=5;
5 is prime


Conclusion:
       A pl/sql program is successfully executed to check the given number is prime or not.

Read More

ORACLE-FORU - PL/SQL code for MULTIPLES OF 5

To write a pl/sql program for finding Multiples of 5.

Procedure:

declare
     i number(3):=5;
     n number(3):=&n;
Begin
     Dbms_output.put_line('the multiples of 5 are:');
     while i<=n
       loop
          dbms_output.put_line(i);
          i:=i+5;
     end loop;
  end;
  /

Output:

SQL> @e:\sqlpl\2.sql
Enter value for n: 20
old   3:  n number(3):=&n;
new   3:  n number(3):=20;
the multiplus of 5 are:
5
10
15                                                                                      
20

PL/SQL procedure successfully completed.

Conclusion:
        A pl/sql program is successfully executed for finding Multiples of 5.

Read More

ORACLE-FORU - PL/SQL Code for Sum of two number

To write a pl/sql program to find sum of two numbers.
Procedure:

Declare

i number(3);
j number(3);
k number(3);

           Begin
                     i:=&i;
                     j:=&j;
                     k:=i+j;

                     Dbms_output.put_line('the sum of '||i||','||j||'is:'||k);

           end;
Execution:
SQL> @e:\sqlpl\add.sql
Enter value for i: 4
old 6: i:=&i;
new 6: i:=4;
Enter value for j: 4
old 7: j:=&j;
new 7: j:=4;
the sum of 4,4 is:8

PL/SQL procedure successfully completed.
Conclusion:

A pl/sql program is successfully executed for finding sum of two numbers.

Read More

ORACLE-FORU - Oracle all Important Functions with Examples

Oracle Functions

                                             
ASCII                                

Returns the ASCII value (NUMBER) that represents the specified character.
 ASCII(SINGLE_STRING)

 ASCII('t') - 116

 ASCII('T') - 84
 ASCII('T2') - 84


ASCIISTR                            

Converts a string in any character set to an ASCII string using the database                                                   character set.
  ASCIISTR(STRING)
  ASCIISTR('A B C Ä Ê') - 'A B C \00C4 \00CA'
 ASCIISTR('A B C Õ Ø') - 'A B C \00D5 \00D8'
 ASCIISTR('A B C Ä Ê Í Õ Ø') - 'A B C \00C4 \00CA \00CD \00D5\00D8'

CHR                                    

Returns the character based on the NUMBER code.
This is opposite of the ascii function.
 CHR(NUMBER_CODE)

 CHR(116) - 't'
 CHR(84) - 'T'

CONCAT                           

Allows you to concatenate two strings together.
CONCAT(STRING1, STRING2)
CONCAT('Tech on',' the Net') - 'Tech on the Net'
CONCAT('a', 'b') - 'ab'

Concat ||                             

|| operator allows you to concatenate 2 or more strings together.
 STRING1 || STRING2 || STRINGn
 'Tech on' || ' the Net' - 'Tech on the Net'
 'a' || 'b' || 'c' || 'd' - 'abcd'


CONVERT                           

Converts a string from one character set to another
CONVERT(STRING1 , CHAR_SET_TO , [CHAR_SET_FROM ] )
CONVERT('A B C D E Ä Ê Í Õ Ø', 'US7ASCII', 'WE8ISO8859P1') - 'A B  C D E A E I ? ?'

INITCAP                             

 Returns first character in each word to uppercase and the rest to lowercase.
 INITCAP(STRING)
 INITCAP('tech on the net') - 'Tech On The Net'
 INITCAP('GEORGE BURNS') - 'George Burns'

INSTR                                  

 It returns the location of a substring in a string.
 INSTR(STRING1,STRING2 [,STRING_POSITION  [,nth_APPEARANCE]])
 string1 is the string to search.
  string2 is the substring to search for in string1.
  start_position is the position in string1 where the search will start. This                                                           argument is optional. If omitted, it defaults to 1. The first position in the string                                                 is 1. If the start_position is negative, the function counts backstart_position                                                   number of characters from the end of string1 and then searches towards the                                                   beginning of string1.
  nth_appearance is the nth appearance of string2. This is optional. If omitted, it                                              defaults to 1.
Note: If string2 is not found in string1, then the instr Oracle function will return 0.
INSTR('Tech on the net','e') - 2 the first occurrence of 'e'
INSTR('Tech on the net','e',1,1) - 2 the first occurrence of 'e'
INSTR('Tech on the net','e',1,2) - 11 the second occurrence of 'e'
INSTR('Tech on the net','e',1,3) - 14 the third occurrence of 'e'
INSTR('Tech on the net','e',-3,2) -

LENGTH                              

Returns the length of the specified string.

LENGTH(STRING)             

string1 is NULL, then the function returns NULL.

LENGTH(NULL) - NULL

 LENGTH('') - NULL

 LENGTH('Tech on the Net') - 15

LENGTH('Tech on the Net ') - 16 

LOWER                               

Converts all letters in the specified string to lowercase. If there are characters                                                in the string that are not letters, they are unaffected by this function.

 LOWER(STRING)

LOWER('Tech on the Net') - 'tech on the net'

LOWER('GEORGE BURNS 123 ') - 'george burns 123 '

LPAD                                   

Function pads the left-side of a string with a specific set of characters (when                                                  string1 is not null).
LPAD( STRING1, PADDED_LENGTH [,PAD_STRING])
string1 is the string to pad characters to (the left-hand side).
padded_length is the number of characters to return. If the padded_length is smaller than the original string, the lpad function will truncate the string to the size of padded_length.
pad_string is optional. This is the string that will be padded to the left-hand side of string1. If this parameter is omitted, the lpad function will pad spaces to the left-side of string1.
LPAD('tech', 7) - ' tech'
LPAD('tech', 2) - 'te'
LPAD('tech', 8, '0') - '0000tech'
LPAD('tech on the net', 15, 'z')- 'tech on the net'
LPAD('tech on the net', 16, 'z')- 'ztech on the net'

LTRIM                                     

 Function removes all specified characters from the left-hand side of a string.

LTRIM( STRING1 [, TRIM_STRING])
string1 is the string to trim the characters from the left-hand side.
trim_string is the string that will be removed from the left-hand side of string1.
If this parameter is omitted, the ltrim function will remove all leading spaces from string1.
LTRIM(' tech') - 'tech'
LTRIM(' tech', ' ') - 'tech'
LTRIM('000123', '0') - '123'
LTRIM('123123Tech', '123') - 'Tech'
LTRIM('123123Tech123', '123') - 'Tech123'
LTRIM('xyxzyyyTech', 'xyz') - 'Tech'
LTRIM('6372Tech', '0123456789') - 'Tech'

The ltrim function may appear to remove patterns, but this is not the case as demonstrated in the following example.
LTRIM ('xyxzyyyTech', 'xyz')
It actually removes the individual occurrences of 'x', 'y', and 'z', as opposed to the pattern of 'xyz'.
The ltrim function can also be used to remove all leading numbers as demonstrated in the next example.
LTRIM ('6372Tech', '0123456789')
In this example, every number combination from 0 to 9 has been listed in the trim_string parameter. By doing this, it does not matter the order that the numbers appear in string1, all leading numbers will be removed by the ltrim function.


REPLACE                                      

Function replaces a sequence of characters in a string with another set                                                         of characters.
REPLACE(STRING1,STRINGTO_REPLACE [,REPLACEMENT_STRING])
string1 is the string to replace a sequence of characters with another set of characters.
string_to_replace is the string that will be searched for in string1.
replacement_string is optional. All occurrences of string_to_replace will be replaced with replacement_string in string1. If the replacement_string parameter is omitted, the replace function simply removes all occurrences of string_to_replace, and returns the resulting string.
REPLACE('123123tech', '123') - 'tech'
REPLACE('123tech123', '123') - 'tech'
REPLACE('222tech', '2', '3') - '333tech'
REPLACE('0000123', '0') - '123'
REPLACE('0000123', '0', ' ') - ' 123'
REPLACE ('123tech', '231', '555') - '123tech'

RPAD                                          

Function pads the right-side of a string with a specific set of characters                                                         (when string1 is not null).
RPAD (STRING1, PADDED_LENGTH [,PAD_STRING])
string1 is the string to pad characters to (the right-hand side).
padded_length is the number of characters to return. If the padded_length is smaller than the original string, the rpad function will truncate the string to the size of padded_length.
pad_string is optional. This is the string that will be padded to the right-hand side of string1. If this parameter is omitted, the rpad function will pad spaces to the right-side of string1.
RPAD('tech', 7) - 'tech '
RPAD('tech', 2) - 'te'
RPAD('tech', 8, '0') - 'tech0000'
RPAD('tech on the net', 15, 'z') - 'tech on the net'
RPAD('tech on the net', 16, 'z') - 'tech on the netz'

RTRIM                                    

 Function removes all specified characters from the right-hand side of a                                                          string.
RTRIM( STRING1 [,TRIM_STRING] )
string1 is the string to trim the characters from the right-hand side.
trim_string is the string that will be removed from the right-hand side of string1. If this parameter is omitted, the rtrim function will remove all trailing spaces from string1.

RTRIM('tech ') - 'tech'
RTRIM('tech ', ' ') - 'tech'
RTRIM('123000', '0') - '123'
RTRIM('Tech123123', '123') - 'Tech'
RTRIM('123Tech123', '123') - '123Tech'
RTRIM('Techxyxzyyy', 'xyz') - 'Tech'
RTRIM('Tech6372', '0123456789') - 'Tech'
The rtrim function may appear to remove patterns, but this is not the case as demonstrated in the above example.
RTRIM ('Techxyxzyyy', 'xyz')
It actually removes the individual occurrences of 'x', 'y', and 'z', as opposed to the pattern of 'xyz'.
RTRIM ('Tech6372', '0123456789')
The rtrim function can also be used to remove all trailing numbers as demonstrated in the next example.

In this example, every number combination from 0 to 9 has been listed in the trim_string parameter. By doing this, it does not matter the order that the numbers appear in string1, all trailing numbers will be removed by the rtrim function.

SOUNDEX                                   

Function returns a phonetic representation (the way it sounds) of a string.
SOUNDEX( STRING )
The Soundex algorithm is as follows:
1. The soundex return value will always begin with the first letter of string1.
2. The soundex function uses only the first 5 consonants to determine the NUMERIC portion of the return value, except if the first letter of string1 is a vowel.
The soundex function is not case-sensitive. What this means is that both uppercase and lowercase characters will generate the same soundex return value.
SOUNDEX('tech on the net') - 'T253'
SOUNDEX('TECH ON THE NET') - 'T253'
SOUNDEX('apples') - 'A142'
SOUNDEX('apples are great') - 'A142'
SOUNDEX('applus') - 'A142'

SUBSTR                                                      

 Function allows you to extract a substring from a string.

SUBSTR (STRING, START_POSITION [,LENGTH])
string is the source string.
start_position is the position for extraction. The first position in the string is always 1.
length is optional. It is the number of characters to extract. If this parameter is omitted, substr will return the entire string.
Note:
If start_position is 0, then substr treats start_position as 1 (ie: the first position in the string).
If start_position is a positive number, then substr starts from the beginning of the string.
If start_position is a negative number, then substr starts from the end of the string and counts backwards.
If length is a negative number, then substr will return a NULL value.
SUBSTR ('This is a test', 6, 2) - 'is' 

SUBSTR ('This is a test', 6) - 'is a test'
SUBSTR ('TechOnTheNet', 1, 4) - 'Tech'
SUBSTR ('TechOnTheNet', -3, 3) - 'Net'
SUBSTR ('TechOnTheNet', -6, 3) - 'The'
SUBSTR ('TechOnTheNet', -8, 2) - 'On'
SUBSTR ('TechOnTheNet', 7, -2) - null

TRANSLATE


Translate function replaces a sequence of characters in a string with another set of characters. However, it replaces a single character at a time. For example, it will replace the 1st character in the string_to_replace with the 1st character in the replacement_string. Then it will replace the 2nd character in the string_to_replace with the 2nd character in the replacement_string, and so on.
TRANSLATE(STRING1,STRING_TO_REPLACE,REPLACEMENT_STRING)
TRANSLATE('1tech23', '123', '456') - '4tech56'
TRANSLATE('222tech', '2ec', '3it') - '333tith'


TRIM


Function removes all specified characters either from the beginning or the ending of a string.
TRIM ([LEADING | TRAILING | BOTH [TRIM_CHARACTER] FROM STRING1)
leading - remove trim_string from the front of string1.

trailing - remove trim_string from the end of string1.
both - remove trim_string from the front and end of string1.
If none of these are chosen (ie: leading, trailing, both), the trim function will remove trim_string from both the front and end of string1.
trim_character is the character that will be removed from string1. If this parameter is omitted, the trim function will remove all leading and trailing spaces from string1.
string1 is the string to trim.
TRIM(' tech ') - 'tech'
TRIM(' ' FROM ' tech ') - 'tech'
TRIM(LEADING '0' FROM '000123') - '123'
TRIM(TRAILING '1' FROM 'Tech1') - 'Tech'
TRIM(BOTH '1' FROM '123Tech111') - '23Tech'

UPPER


Converts all letters in the specified string to uppercase. If there are characters in the string that are not letters, they are unaffected by this function.
UPPER (STRING)
UPPER('Tech on the Net') - 'TECH ON THE NET'
UPPER('george burns 123 ') - 'GEORGE BURNS 123 '

TO_CHAR


Function converts a number or date to a string.
TO_CHAR(VALUE [,FORMAT_MASK] [,NLS_LANGUAGE])
value can either be a number or date that will be converted to a string.

format_mask is optional. This is the format that will be used to convert value to a string.
nls_language is optional. This is the nls language used to convert value to a string.
Examples :
Query                                                                     Result
TO_CHAR(SYSDATE,'DAY')                           'MONDAY'
TO_CHAR (SYSDATE, 'Day, ddth Month, yyyy') 'Monday, 30th January, 2012'
TO_CHAR (SYSDATE, 'Day, ddth Month, yyyysp') 'Monday, 30th January, two thousand twelve'
TO_CHAR (SYSDATE, 'Day, ddth Month, yyyyspth') 'Monday, 30th January, two thousand twelfth'
TO_CHAR(1210.73, '9999.9')                                   '1210.7'
TO_CHAR(1210.73, '9,999.99')                                '1,210.73'
TO_CHAR(1210.73, '$9,999.00')                              '$1,210.73'
TO_CHAR(21, '000099')                                           '000021'
TO_CHAR(SYSDATE, 'yyyy/mm/dd')                       '2003/07/09'
TO_CHAR(SYSDATE, 'Month DD, YYYY')            'July 09, 2003'
TO_CHAR(SYSDATE, 'FMMonth DD, YYYY')      'July 9, 2003'
TO_CHAR(SYSDATE, 'MON DDth, YYYY')         'JUL 09TH, 2003'
TO_CHAR(SYSDATE, 'FMMON DDth, YYYY')    'JUL 9TH, 2003'
TO_CHAR(SYSDATE, 'FMMon ddth, YYYY')        'Jul 9th, 2003'

Format Mask :

Format                                                                          Meaning
D                      Day of the week (1(sunday) to 7(saturday)) 

DD                   Day of the month (1 to 31)
DDD                Day of the year (1 to 365) 

DAY                Full day for ex. 'Monday', 'Tuesday'.
DY                   Day in three letters for ex. 'MON', 'TUE'.
W                    Week of the month (1 to 5)
WW                Week of the year (1 to 53)
MM                 Month in two digits (1-Jan, 2-Feb,…12-Dec)
MON               Month in three characters like "Jan", "Feb".
MONTH          Full Month like "January", "February".
RM                  Month in Roman Characters (I-XII, I-Jan, II-Feb,…XII-Dec)
Q                     Quarter of the Month
YY                   Last two digits of the year.
YYYY             Full year
YEAR             Year in words like "Nineteen Ninety Nine"
HH                   Hours in 12 hour format
HH12               Hours in 12 hour format
HH24               Hours in 24 hour format
MI                   Minutes
SS                   Seconds
FF                   Fractional Seconds
SSSSS            Milliseconds
J                     Julian Day i.e Days since 1st-Jan-4712BC to till-date
RR                 If the year is less than 50 Assumes the year as 21ST Century.
                      If the year is greater than 50 then assumes the year in 20th Century.

Suffixes :

TH
Returns th, st, rd or nd according to the leading number like 1st, 2nd, 3rd

SP
Spells out the leading number

AM or PM
Returns AM or PM according to the time

SPTH
Returns Spelled Ordinal number. For. Example First, Fourth

TO_DATE


Function converts a string to a date.
TO_DATE(STRING [,FORMAT_MASK] [,NLS_LANGUAGE])
string1 is the string that will be converted to a date.

format_mask is optional. This is the format that will be used to convert string1 to a date.
nls_language is optional. This is the nls language used to convert string1 to a date.
TO_DATE('2003/07/09','yyyy/mm/dd')- JULY 9, 2003
TO_DATE('070903','MMDDYY') - JULY 9, 2003
TO_DATE('20020315','yyyymmdd') - MAR 15, 2002
By default, date will be displayed in session NLS date format.


TO_NUMBER


Function converts a string to a number.
TO_NUMBER(STRING1 [,FORMAT_MASK] [,NLS_LANGUAGE])
string1 is the string that will be converted to a number.
format_mask is optional. This is the format that will be used to convert string1 to a number.

nls_language is optional. This is the nls language used to convert string1 to a number.
TO_NUMBER('1210.73', '9999.99') - 1210.73
TO_NUMBER('546', '999') - 546
TO_NUMBER('23', '99') - 23
Since the format_mask and nls_language parameters are optional, you can simply convert a text string to a numeric
value as follows:
TO_NUMBER('1210.73') - 1210.73
TO_TIMESTAMP
Function converts a string to a timestamp.
TO_TIMESTAMP( STRING1 [,FORMAT_MASK] [,NLSPARAM])
TO_TIMESTAMP('2003/12/13 10:13:18', 'YYYY/MM/DD HH:MI:SS')
- '13-DEC-03 10.13.18.000000000 AM'
TO_TIMESTAMP('2003/DEC/13 10:13:18', 'YYYY/MON/DD HH:MI:SS')
- '13-DEC-03 10.13.18.000000000 AM'

ABS


Returns absolute value of a number
ABS( NUMBER )
ABS(-23) - 23
ABS(-23.65) - 23.65
ABS(23.65) - 23.65
ABS(23.65 * -1) - 23.65


AVG


Function returns average value of an expression.
AVG( EXPRESSION )
Basic Syntax is :
SELECT Avg( expression )
FROM tables
WHERE predicates;

CEIL


Function returns the smallest integer value that is greater than or equal to a number.
CEIL ( NUMBER )
CEIL(32.65) - 33
CEIL(32) - 32

CEIL(-32.65) - -32
CEIL(-32) - -32

COUNT


Function returns number of rows in a query.
COUNT ( EXPRESSION )
Supplier_ID                         Supplier_Name                           State
       1                                           IBM                                    CA
       2                                       Microsoft
       3                                                                                     NVIDIA
COUNT(*) - 3
COUNT(1) - 3
COUNT(SUPPLIER_ID) - 3

COUNT(STATE) - 1 

FLOOR


Function returns the largest integer value that is equal to or less than a number.
FLOOR( NUMBER )
FLOOR(5.9) - 5
FLOOR(34.29) - 34
FLOOR(-5.9) - -6

GREATEST


Function returns the greatest value in a list of expressions.
GREATEST( EXPR1, EXPR2, … EXPR_n )
expr1, expr2, . expr_n are expressions that are evaluated by the greatest function.
If the datatypes of the expressions are different, all expressions will be converted to whatever datatype expr1 is.
If the comparison is based on a character comparison, one character is considered greater than another if it has a higher character set value.
GREATEST(2, 5, 12, 3) - 12
GREATEST('2', '5', '12', '3') - '5'
GREATEST('apples', 'oranges', 'bananas') - 'oranges'
GREATEST('apples', 'applis', 'applas') - 'applis'
Note :
Having a NULL value in one of the expressions will return NULL as the greatest value.

LEAST


Function returns the smallest value in a list of expressions.
LEAST( EXPR1, EXPR2, … EXPR_n )
expr1, expr2, . expr_n are expressions that are evaluated by the least function.
If the datatypes of the expressions are different, all expressions will be converted to whatever datatype expr1 is.
If the comparison is based on a character comparison, one character is considered smaller than another if it has a lower character set value.
Note :

Having a NULL value in one of the expressions will return NULL as the least value.
LEAST(2, 5, 12, 3) - 2
LEAST('2', '5', '12', '3') - '12'
LEAST('apples', 'oranges', 'bananas') - 'apples'
LEAST('apples', 'applis', 'applas') - 'applas'
LEAST('apples', 'applis', 'applas', NULL) - NULL

Read More

Normalization In SQL

Normalization In SQL,MY SQL and Oracle

What is normalization ?

Defination : Normalization is the process of efficiently organizing data in a database.

There are two goals of the normalization process: 

1. eliminating redundant data (for example, storing the same data in more than one table) and 

2. ensuring 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. There are several benefits for using Normalization in Database.

Benefits :
Eliminate data redundancy
Improve performance
Query optimization
Faster update due to less number of columns in one table
Index improvement

There are diff. - diff. types of Normalizations form available in the Database. Lets see one by one.

1. First Normal Form (1NF)

First normal form (1NF) sets the very basic rules for an organized database:
Eliminate duplicative columns from the same table.
Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).
Remove repetative groups
Create Primary Key



Name    State    Country    Phone1              Phone2                  Phone3
John       101           1     488-511-3258      781-896-9897      425-983-9812
Bob       102            1     861-856-6987
Rob       201            2     587-963-8425      425-698-9684
PK [ Phone Nos ]
? ?
ID Name   State   Country          Phone
1      John   101        1        488-511-3258
2      John   101        1        781-896-9897
3      John   101        1        425-983-9812
4      Bob   102         1       861-856-6987
5      Rob   201         2       587-963-8425
6      Rob   201         2       425-698-9684



2. Second Normal Form (2NF)Second normal form (2NF) further addresses the concept of removing duplicative data:

· Meet all the requirements of the first normal form.

· Remove subsets of data that apply to multiple rows of a table and place them in separate tables.

· Create relationships between these new tables and their predecessors through the use of foreign keys.

Remove columns which create duplicate data in a table and related a new table with Primary Key – Foreign Key relationship


ID Name State Country Phone
1     John   101       1       488-511-3258
2     John   101       1       781-896-9897
3     John   101       1       425-983-9812
4     Bob   102        1       861-856-6987
5     Rob   201        2       587-963-8425
6     Rob   201        2      425-698-9684


ID Name State Country                                        PhoneID        ID         Phone
1     John   101      1                                                    1             1          488-511-3258
2     Bob    102     2                                                    2             1         781-896-9897
3     Rob    201     3                                                    3             1         425-983-9812
                                                                                  4             2          587-963-8425
                                                                                  5             3          587-963-8425
                                                                                  6             3          425-698-9684




3. Third Normal Form (3NF)

Third normal form (3NF) goes one large step further:

· Meet all the requirements of the second normal form.

· Remove columns that are not dependent upon the primary key.

Country can be derived from State also… so removing country
ID Name State Country
1    John   101        1
2    Bob   102        1
3    Rob    201        2




4. Fourth Normal Form (4NF)

Finally, fourth normal form (4NF) has one additional requirement:

· Meet all the requirements of the third normal form.

· A relation is in 4NF if it has no multi-valued dependencies.



If PK is composed of multiple columns then all non-key attributes should be derived from FULL PK only. If some non-key attribute can be derived from partial PK then remove it



The 4NF also known as BCNF NF




TeacherID StudentID SubjectID StudentName
101              1001            1               John
101              1002            2               Rob
201              1002            3               Bob
201              1001            2               Rob


TeacherID StudentID SubjectID StudentName
101               1001            1                X
101               1002            2                X
201               1001            3                X
201               1002            2                X

Read More

How to attach a Menu to a oracle form

Here are the following steps to attach Menu to a oracle forms:

1. I create a form named as form1. i save it to c:\form1. A file name form1.fmb created at c drive.
2. now i run the form from within form builder a file generated to c drive with name form1.fmx
3. i create a menu in form builder. save it to drive c with name test. A file test.mmb created there.
4. i compile this menu module using CTRL - T and look again a file named TEST.mmx is there at drive c.
5. now i open the form1.fmb and open the property pallete for form1. Change its property menu module to c:\test.mmx and now run form either from within form builder or forms runtime. I get my menu TEST attached to form1

Read More

MDI Form in Oracle Forms Maximize

For Example:-

In the Form

module 1
trigger
Create New trigger

WHEN-NEW-FORM-INSTANCE
Begin
set_window_property('window1', window_state, maximize);

SET_WINDOW_PROPERTY(FORMS_MDI_WINDOW, WINDOW_STATE, MAXIMIZE);
end;


Window1 is Your Form Window Name

Read More

SQL Functions(ddl,dml,dcl & tcl) with Examples

There are 4 types of sql functions
1.DML
2.DDL
3.DCL
4.TCL

Now lets see one by one

1.DML

DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database.


Examples: SELECT, UPDATE, INSERT statements


Select :- This Sql Statement is used to extract the data from one or combination of tables
Update:- This Sql Statement is used to update the data in a database table.
Delete:- This Sql Statement is used to delete data from the database table.
Insert Into :- This Sql Statement is used to insert data into a database(table)


2.DDL

DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database.


Examples: CREATE, ALTER, DROP statements


Create Table:- This Sql Statements is used to create a Table
Alter Table:- This Sql Statement is used to Alter the table definition like adding any columns or deleting any table column.
Drop table:- This Sql Statement is used to Drop the table.
Create Index:- This Sql Statement is used to Create a Index on a table
Drop Index:- This Sql Statement is used to drop a table from the table


3.DCL

DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.


Examples: GRANT, REVOKE statements
Grant :- This Sql Statement is used to give access rights to the user for the database
Revoke:- This Sql Statement is used to revoke or delete the access rights of some of the users for a given database.


4.TCL

TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database.


Examples: COMMIT, ROLLBACK statements


Commit:- This command is used to save the work done by the user.
Rollback:- This command is used to delete the data till the last committed state of the database.

Read More