SQL Server Basics

This post is for Basics of SQL Server. I will cover below topics in this post

• Create Table
• Data Types
• Variable Declaration
• CURD Operations 
• Joins Operators

Create Table

SQL server gives you the provision of creating table using design mode. Here I am showing the example How to create with query prompt.

CREATE Table TestTable1

(

      Id BIGINT

      ,Column1 VARCHAR(150)

      ,Column2 BIGINT

      ,Column3 DATETIME

)

This example is only for creating the table. While creating the table we can create the primary key, Index or we can the other properties as well.

Data Types

SQL-Server is having so many data types. I have already explain all the data types in this Link

Variable Declarations

In SQL-Server we can declare the variable using below syntax.

DECLARE @Temp VARCHAR(50)

SQL-Server 2012 we can declare and assign the variable at the same time

DECLARE @Temp VARCHAR(50) = 'Rahul Singi'

CURD Operations

Insert Statements:  Below is the example of insert data in table

INSERT INTO [dbo].[TestTable1]

          ([Id]

           ,[Column1]

              ,[Column2]

           ,[Column3])

    VALUES

             (1001

              ,'Rahul Singi'

           ,5001

           ,'02-23-1985')

INSERT INTO [dbo].[TestTable1] VALUES (1002 ,'Rahul Singi' ,5001 ,'02-23-1985')

For More detail you can use this Link

Update Statements:  Below is the example of Update data in table

     UPDATE TestTable1 SET Column1 = 'Gaurav' WHERE Id = 1002

If we will not use WHERE clause in update statement then it will update all the rows of the table. 

     For More detail you can use this Link

. 

     Select Statements: Below is the example of Select data in table. Select statement begins with the list of columns or expressions. At least one expression is required.

SELECT 1

SELECT GETDATE()

SELECT

      [Column1]

      ,[Column2]

      ,[Column3]

FROM

      TestTable1

From portion of the select statement assembles all the data source into a result set, which is then acted upon by the rest of the SELECT statements.

WHERE clause acts upon the record set  assembled by the FROM clause to filter certain rows based upon condition.

     

    For More detail you can use this Link

    Delete Statements: Below is the example of Select data in table

DELETE FROM TestTable1 WHERE Id = 1001

    For More detail you can use this Link

   Join Operators

   

   Inner Join: Inner join will give you the result of common rows from the two tables. Below is the pictorial representation of inner join

Below is the example query:

SELECT

      *

FROM

      TestTable1 T1

INNER JOIN

      TestTable2 T2

      ON

T1.id = T2.Id

    Outer Joins: There are three types of Outer Join.

    Left Outer Join: Left join will give you the result of common rows from the two tables and all the data from Left table. Below is the pictorial representation of Left Join

Below is the example query:

SELECT

      *

FROM

      TestTable1 T1

LEFT JOIN

      TestTable2 T2

      ON

      T1.id = T2.Id

     Right Outer Join: Right join will give you the result of common rows from the two tables and all the data from Right table. Below is the pictorial representation of Right Join

Below is the example query:

SELECT

      *

FROM

      TestTable1 T1

RIGHT JOIN

      TestTable2 T2

      ON

T1.id = T2.Id

     Full Outer Join: Full join will give you the result of all rows. Below is the pictorial representation of Full Join.

Below is the example query:

SELECT

      *

FROM

      TestTable1 T1

FULL OUTER JOIN

      TestTable2 T2

      ON

T1.id = T2.Id

    

    Thx,

    RS