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(;

Part 3

Contiue from part 2 ---

My question is from where should we start if I have to do  Database testing?

My answer is first understand dataBase schema ( schems means – the way database tables are designed)

Let me give you the answer to understand the database schema.

We  have a Database that is relational database management system(RDBMS).Here Relational means it has a relation with something .That means each table in database has a relation to other table. This relation is possible with the help of Primary Key and foreign key.
primary key is a name of column that must have different values  in every rows of records.

Here is some facts of Primary key 

Primary key is a name of column that must have different values  in every rows of records.

Here is some facts of Primary key

·         Primary key is a name of  column of a table

·         Every row ot primary key column has distinct vlaue

Generally the name of primary key column is table name followd by ID. Like in employee table , primary key would be employeeid , in person table it would be persondid and so on .

Foreign key – Foreign key in Table A is  a primary key  of Table B.

Foreign key allows us to make the relation between tables ( we talked earlier about the relation ) and make the database normalized.

To understand the concept of  primary key and Foreign key , I have created a Db schema for Defect life cycle( As we all are famliar with this) 

(Click on diagram to see bigger )

And hence forth I will use this schema to write SQL and give you the scenrio based sql .Things that are in Green in a rectangle are Table Name and below that its column name.I have also maked a relation ship between the diffenent table by an arrow.Based on the above defination of Primary key and foreign key , I can tell that In Defect table , Defectid is primary key (Table Name followed by ID) and all other id columns are foreign key ( primary key of other table).

In general when ever we see a colum name as ID that might be either primary key or foreign key  and further primary key is table name followed by id.