sql and plsql difference

sql and pl-sql difference in Oracle

1) We can execute more than one commands at a time in pl-sql,but not allow to execute more than one 

    command in SQL.

2) Code re usability available in pl-sql,but not allow in SQL.

3) Pl-sql provide facility of condition checking,branching and looping.

4) PL-SQL will run faster in the version 10g than it did in previous versions.Oracle has made PL-SQL code 

    run faster without requiring additional work on the part of the programmer.

    In SQL, many new commands  allows you to retrieve information or data more easily than the before.

Read More

Single Row Function in sql

Single Row Function in sql

Single row function divided into 4 types

1) String Function

2) Math Function

3) Date Function

4) Date Conversion Function

String Function of Single Row Function

a) Length():  This function used to count number of characters in given string including spaces.

     Ex: select length('Maha rashtra') from dual

b) initcap(): This function is used to convert first letter of every word into capital letter.

    Ex: select initcap('Maha rashtra') from dual

c) ltrim(): Remove blank spaces from left hand side of string;

   Ex:select ltrim(‘        oracle’) from dual

d) rtrim(): Remove blank spaces from right hand side of string;

    Ex:select ltrim(‘oracle     ’) from dual

e) trim(): Remove blank spaces from left and right hand side of string;

    Ex:select ltrim(‘        oracle        ’) from dual;

f) vsize(): cound number of  bytes occupied by given string

   Ex:select vsize(‘oracle’) from dual

g) lpad: Used to combining  given character from left hand side of string

   Ex:select lpad(‘oracle’,15,’*’) from dual

h) rpad: Used to combining  given character from right hand side of string

   Ex:select rpad(‘oracle’,15,’*’) from dual

i) translate: translate given character with specified character

   Ex:select translate (‘i am from oracle,’o’,t’) from dual

j) replace():replace given character with specified character

  Ex:select translate (‘i am indian’’n’,t’) from dual

k) substr(): Return the part of string from main string

    Ex: select substr (‘india’0,4’) from dual

l) soundex():Used to retrieve the name whose pronounciation is same but spelling are  defferent

   Ex:select * from from names where soundex(name)=sound(‘murthy’);

m) upper(): convert into capital letter

    Ex:select upper(‘indian’) from dual

n) lower(): Convert into small letter

    Ex:select upper(‘INDIAN’) from dual

o) instr(): Returns position of character

   Ex:select instr(‘india’,’d’)

Read More

Difference Between sql and tsql

What is Difference Between sql and tsql

1) SQL is a Structured Query Language.It is developed by IBM.

   

    TSQL is the Transact SQL.It is implementation of SQL Server. Developed by Microsoft.

2) SQL is a query language to operate on sets.

    TSQL is a proprietary procedural language used by Microsoft.

3) SQL is a language for talking to the database,It used to select data,mutable and create database    

    objects,and change the database settings.

    T-SQL is the extention for SQL used by SQL Server.

4) SQL is the set of sqls are submitted individually to the database server.

    T-SQL is the batch program has written where in all commands are submitted to server in a single goes.

5) SQL is a one programming language that are focuses on managing relational database.This is used to  managing and controlling data and very important in business where large amount of information are stored about product.

   T-SQL adds number of features that are not available in SQL. Includes procedural programming  elements and local variable to provide flexible control of how the application flows.

6) SQL is not Turing complete and it is very limited in the scope of what they can do.

   These additions makes T-SQL comply with Tiring completeness test, test that determine the universality of 

    a computing language.

7) In SQL, DELETE and UPDATE commands that are already available.

  

   With T-SQL, the DELETE and UPDATE command both allows the inclusion of a FROM clause which 

    allows the use of JOINs.

Read More

Unlock Table Oracle

Unlock Table Oracle

There have no any command  to unlock database.

After applying transaction such as Commit and Rollback table automatically unlocked.

Commit Transaction Used To Unlock Table:

This command is used to save the transaction explicitly from the movement the user log on to the data to the movement the executing Commit command or the movement to user executing Rollback command to till executing commit command.

Rollback Transaction Used To Unlock Table:

This command is used to discard the transaction from the movement the user log on to the database to till the execute Rollback command or executing Commit command into till executing Rollback command.

Read More

Synonyms In Oracle

Synonyms In Oracle

Synonyms is a database object which it is alternate name or a duplicate name for original base table.

1) Once we create synonyms,the synonym will be created on entire table,but it is not possible to create a synonyms based on the partial table or subset of the table.

2) If we perform any operation on synonyms then same operation automatically affected to corresponding base table and vice-verse.

3)  We cannot create synonyms based on the more than one table.

4)  We can create synonyms up-to user requirement,means n number of times.

5)  We can create a synonyms with a base table or without a base table

6)  Once we drop base table corresponding synonyms will not be dropped but it becomes invalid.

7)  Synonyms will becomes invalid in 2 cases.

       a) When we drop base table

       b) When we change the table name.

8) An invalid synonyms we cannot perform any operation on that table.

9) We can make invalid synonyms as valid synonyms.

Syntax To Create a Private Synonyms:

       create synonym synonym_name for table_name

Syntax To See List Of Synonyms:

       select * from user_s

Syntax To Create a Private Synonyms:

       drop synonym synonym_name

Read More