Learn SQL

http://urtesting.blogspot.com/

LEARN SQL:

To Know all the table in the database

1. How to get all the tables name of the database:

Select  *  from tab  -- This will give you  all the table name of the database.

when we are not sure the exact name of table use like keyword  to get the table name

Select * from tab where tname like 'A%'

give me the table name that starts with A

desc tab – this will describe the tab table of database.

TNAME                          NOT NULL VARCHAR2(30)                                                                                                                                                                          

TABTYPE                                 VARCHAR2(7)                                                                                                                                                                                  

CLUSTERID                 NUMBER      

2. How to get all the column name of the table:

Desc  tablename

Use desc  to get  all the column name and data types of the column

Or

Select * from tablename where rownum <1

1.       How to get records of the table

By using select statements

Select statements :

Select  * from  tablename

Select * from (Select * from tablename)

Or

Select  *  from tablename where column1 =’…’

Select  column1, column2, column3 from tablename where column1=’’ and column2=’’

3.       How to sort the displayed data ( ascending or descending)

By using order by keyword – default sort is ascending

Select  column1, column2, column3 from tablename where column1=’’ and column2=’’

Order by column1 asc ( asc is default even we don’t put it will work)

Select  column1, column2, column3 from tablename where column1=’’ and column2=’’

Order by 1

Select  column1, column2, column3 from tablename where column1=’…’ and column2=’…’

Order by column1  desc

Select  column1, column2, column3 from tablename where column1=’’ and column2=’’

Order by Lower(column1) asc

Or

Select  column1, column2, column3 from tablename where column1=’’ and column2=’’

Order by Upper (column1) asc

4.       How  do we do primary sort , secondary sort and tertiary sort:

Select  column1, column2, column3 from tablename where column1=’’ and column2=’’

Order by Upper (column1) asc, column2 desc , column3

Or

Select  column1, column2, column3 from tablename where column1=’’ and column2=’’

Order by 1,2,3

( here 1 means first column that we specified in select statements, 2 means second column that we specified in select statement i.e- column2)

5.       How to count the records:

Bu using count key words:

Just add the select count(*) from ( before your query and a

Select  count(*) from (

Put your sql query here

)

i.e

select count(* ) from (

select * from tablename where columnname=’’

)

Or

Select count(*) from table where columnname=’’

Or

Select count(;