\n

Database Synchronisation

Database Synchronisation

Synchronization is the process of data uploading or mirroring between two or more databases. When you work with converters you can face some issues attempting to synchronize tables in your databases. Here you can find answers how to overcome them.

Insert Synchronization
Update Synchronization
Insert Synchronization and Update Synchronization together

1.Insert Synchronization

Having some new records in the table in your source database, you have an opportunity to add them in the table in your destination database with the help of our converters from Database Conversion Product Line. Additional records easily and quickly inserted from a source to a destination, in case there are no records with identical Primary key values.

2.Update Synchronization
Changing table in your source database, you would like to adjust your table in a destination database to desired result. Converter will replace the altered records without any problems. It compares the value of records in order to establish identity between two tables.

3.Insert Synchronization and Update Synchronization together

You have modified your table in your source database. For example, you changed some data in existing records and inserted entirely new records in that table. You have to add these values in the table and renew data in your destination database. Insert Synchronization and Update Synchronization together allow you flawlessly to accomplish complex synchronization.

The best article on Datbase synchronization is available at

http://dbconvert.com/synchronization.php

The best database synchronation tool for mysql database is available at

http://www.sitepoint.com/article/mysql-data-sqlyog-job-agent/

 

Joins:

1)These are used to retrieve the data from more than one table.

2)To retrieve the data from more than one table the datatypes of fields which related to different tables need not be same while using the joins

Types of joins:

1):Inner Join

2):Cross Join

3)OuterJoin

     a)Left Outer Join

    b)Right Outer Join

    c)Full Outer Join

4)Natural Join

5)Equi Join

Examples and Description:

1:Emp

         EmployeeID  EmployeeName               
    1                Ramesh
    2                Sukumar
    3                Ravi                                                       
    4                Kalyani           

                                         

2.Products:

   ProductID            EmployeeID        Productname
  1 1                                  2                     Pen
  12                                   3                    Pencil
  1 2                                  3                    Eraser
  1 3                                  6                   Book

1):Inner Join:This join returns all the rows from the both the tables where there is a match.The result set consists of only matched rows.

Syntax:

select E. Employeeid,E.EmployeeName,P.ProductName from Employees E inner join Products on E.EmployeeID=P.EmployeeID

Result:

1)       EmployeeID   EmployeeName          Productname     
    2                  Sukumar                       Pen
    3                   Ravi                             Pencil                          
    3                   Ravi                             Eraser                            

2)Cross Join:Cross join is nothing but retrieving the data from more than one table with out using the condition.

Here two cases are there:

a)select E.EmployeeID,E.EmployeeName,P.Productname from Employees E,Products P

Note:(here we are using the cross join defaultly.Means we have not mentioned the any condition here.)

b)select E.EmployeeID,E.EmployeeName,P.Productname from Employees E cross join Products P

Note:this is the syantax of cross join..both queries(a &b)returns the same result) only the difference is Synatx but the o/p is same.

3)Outer Join:In outer join the resulting table may have empty columns.

a)Left Outer Join:Here left means first table.it reurns all the rows from the first table even though it does not have the matchs in Second table.But it returns only the matched rows from the second table.

Syntax:

select E. Employeeid,E.EmployeeName,P.ProductName from Employees E left join Products on E.EmployeeID=P.EmployeeID

Result:

1)       EmployeeID   EmployeeName          Productname     
    2                  Sukumar                       Pen
    3                   Ravi                             Pencil                          
    3                   Ravi                             Eraser        

           1                  Ramesh                        null

           4                  Kalyani                          null

a)Right Outer Join:Here Right means Second table.it returns all the rows from the second table even though it does not have the matchs in First table.But it returns only the matched rows from the First table.

Syntax:

select E. Employeeid,E.EmployeeName,P.ProductName from Employees E right join Products on E.EmployeeID=P.EmployeeID

Result:

1)       EmployeeID   EmployeeName          Productname     
    2                  Sukumar                          Pen
    3                   Ravi                                Pencil                          
    3                   Ravi                                Eraser        

            6                   null                                  Book   

5)Natural JOIN:it eliminates the duplicate values from the output.

6)Equi JOIN:An inner join is called equi-join when all the columns are selected with a *, or natural join otherwise

1)What are the Difference between Char, Varchar and Nvarchar in sql,dataTypes. 

 Char
Fixed Length Character and non Unicode Character data With length of n bytes.
n values starts from 1 to 8000.


Varchar

Variable length and non Unicode character data with length of bytes. n must be values starts 1 through 8000.

NVarchar

Variable length and Unicode character data of n character. Storage bytes are two times the number of character entered.
n values starts 1 through 4000.

 

\n

Joins:

1)These are used to retrieve the data from more than one table.

2)To retrieve the data from more than one table the datatypes of fields which related to different tables need not be same while using the joins

Types of joins:

1):Inner Join

2):Cross Join

3)OuterJoin

     a)Left Outer Join

    b)Right Outer Join

    c)Full Outer Join

4)Natural Join

5)Equi Join

Examples and Description:

1:Emp

         EmployeeID  EmployeeName               
    1                Ramesh
    2                Sukumar
    3                Ravi                                                       
    4                Kalyani           

                                         

2.Products:

   ProductID            EmployeeID        Productname
  1 1                                  2                     Pen
  12                                   3                    Pencil
  1 2                                  3                    Eraser
  1 3                                  6                   Book

1):Inner Join:This join returns all the rows from the both the tables where there is a match.The result set consists of only matched rows.

Syntax:

select E. Employeeid,E.EmployeeName,P.ProductName from Employees E inner join Products on E.EmployeeID=P.EmployeeID

Result:

1)       EmployeeID   EmployeeName          Productname     
    2                  Sukumar                       Pen
    3                   Ravi                             Pencil                          
    3                   Ravi                             Eraser                            

2)Cross Join:Cross join is nothing but retrieving the data from more than one table with out using the condition.

Here two cases are there:

a)select E.EmployeeID,E.EmployeeName,P.Productname from Employees E,Products P

Note:(here we are using the cross join defaultly.Means we have not mentioned the any condition here.)

b)select E.EmployeeID,E.EmployeeName,P.Productname from Employees E cross join Products P

Note:this is the syantax of cross join..both queries(a &b)returns the same result) only the difference is Synatx but the o/p is same.

3)Outer Join:In outer join the resulting table may have empty columns.

a)Left Outer Join:Here left means first table.it reurns all the rows from the first table even though it does not have the matchs in Second table.But it returns only the matched rows from the second table.

Syntax:

select E. Employeeid,E.EmployeeName,P.ProductName from Employees E left join Products on E.EmployeeID=P.EmployeeID

Result:

1)       EmployeeID   EmployeeName          Productname     
    2                  Sukumar                       Pen
    3                   Ravi                             Pencil                          
    3                   Ravi                             Eraser        

           1                  Ramesh                        null

           4                  Kalyani                          null

a)Right Outer Join:Here Right means Second table.it returns all the rows from the second table even though it does not have the matchs in First table.But it returns only the matched rows from the First table.

Syntax:

select E. Employeeid,E.EmployeeName,P.ProductName from Employees E right join Products on E.EmployeeID=P.EmployeeID

Result:

1)       EmployeeID   EmployeeName          Productname     
    2                  Sukumar                          Pen
    3                   Ravi                                Pencil                          
    3                   Ravi                                Eraser        

            6                   null                                  Book   

5)Natural JOIN:it eliminates the duplicate values from the output.

6)Equi JOIN:An inner join is called equi-join when all the columns are selected with a *, or natural join otherwise