sql - simple queries

--Create table

CREATE TABLE TableName

(

   Id INT  IDENTITY(1,1) PRIMARY KEY,

   Name Nvarchar(500),

   Age INT

)

--Create View

CREATE VIEW view_name AS SELECT Id,Name,Age

FROM TableName

--Create Stored procedure

CREATE PROCEDURE getInfoFromTable

AS

SELECT * FROM TableName

--Add columns to existing table

ALTER TABLE {TABLENAME}

ADD {COLUMNNAME} {TYPE}

--Remove column from a table

ALTER TABLE TableName

DROP COLUMN Email

--Swap values of 2 columns

UPDATE TableName SET Column1=Column2, Column2=Column1

--Get Id of latest inserted record

INSERT INTO TableName (NAME,Email,Age) VALUES ('Test User','test@gmail.com',30)

SELECT SCOPE_IDENTITY()

--Delete duplicate records

DELETE

FROM TableName

WHERE ID NOT IN

(

SELECT MAX(ID)

FROM TableName

GROUP BY DuplicateColumn)

--Replace a string in a SQL Server Table

UPDATE TableName SET ColumnName = REPLACE(ColumnName, 'OldValue', 'NewValue')

--Left/Right Outer Join

SELECT * FROM Table1 as t1

(Left/Right) OUTER JOIN Table2 as t2

on t1.PrimaryColumn=t2.ForeignKeyColumn

--Full Outer Join

SELECT * FROM Table1 as t1

FULL OUTER JOIN Table2 as t2

on t1.PrimaryColumn=t2.ForeignKeyColumn

--Inner Join

SELECT * FROM Table1 as t1

INNER JOIN Table2 as t2

on t1.PrimaryColumn=t2.ForeignKeyColumn

--Cross Join

SELECT * FROM Table1

CROSS JOIN Table2

--IF ELSE

IF @ValueToCheck=15

   SELECT 'Value is 15' As Result

ELSE

   SELECT 'Value is greater than 15' As Result

--CASE Expression

SELECT CASE Name

       WHEN 'max' THEN 'Name is max'

       WHEN 'jack' THEN 'Name is max'

       ELSE ' You have selected other user'   

       END AS Result

FROM TableName

--Paging Example

SELECT * FROM (SELECT  ROW_NUMBER() OVER(ORDER BY id) AS RowNum,* FROM Country)as Result

WHERE RowNum>=20 AND RowNum<=40

ORDER BY RowNum

--Temporary Tables Example

DECLARE @TempTable TABLE

(      

  Id int primary key,    

  Name varchar(50),

  Email varchar(50)

)

INSERT INTO @TempTable VALUES (1,'Max','max@gmail.com'),

SELECT * FROM @TempTable

--SELECT ITEMS AND CREATE TABLE WUTH THAT ITEMS

SELECT

         a.COL1

       , a.COL2

       INTO dbo.newtable

FROM yourTable a

--source: http://www.codingfusion.com/post/75-Important-queries-in-SQL-Server-every-developer-should-know#.Ws2ZzyibD2M.linkedinhttp://www.codingfusion.com/post/75-Important-queries-in-SQL-Server-every-developer-should-know#.Ws2ZzyibD2M.linkedin