SQL Server - Most Important SQL Commands

SELECT - Get data from a Table

Example

select * from table1; 

select Column1,Column2,Column3 from table1;

UPDATE - update data in a Table

Example

update table1 set Column2='Value2',Column3='Value3' where Column1='Value1'

DELETE - delete data from a Table

Example

update table1 where Column1='Value1'

INSERT INTO - inserts new data into a Table

Example

insert into table1 (Column2,Column3) values ('Value2','Value3')

CREATE DATABASE - create a new DataBase

Example

create database DataBaseName;

ALTER DATABASE - modifies a DataBase

Example

ALTER DATABASE  OLDDataBaseName MODIFY NAME = newDBName

CREATE TABLE - create a new Table

Example

CREATE TABLE table_name (column_name1 data_type(size),column_name2 data_type(size),column_name3 data_type(size),....);

CREATE TABLE table1(column1 int,column2 varchar(255),column3 varchar(255));

ALTER TABLE - modifies a Table

Now we want to add a column named "Column4" in the "table1" table. We use the following SQL statement:

Example

ALTER TABLE table1 ADD column4 decimal(25,3)

Drop Column Example

ALTER TABLE table1 DROP COLUMN column4

DROP TABLE - delete a table

Example

DROP TABLE table1

Join Queries

INNER JOIN

The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables.

Example:

select table1.Column1,table1.Column2,table2.Column1 from table1 inner join table2 on table1.PK_Id=table2.FK_Id

LEFT JOIN

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). 

Example:

select table1.Column1,table1.Column2,table2.Column1 from table1 left join table2 on table1.PK_Id=table2.FK_Id

RIGHT JOIN

The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1).

Example:

select table1.Column1,table1.Column2,table2.Column1 from table1 right join table2 on table1.PK_Id=table2.FK_Id

LEFT OUTER JOIN

In some databases LEFT JOIN is called LEFT OUTER JOIN.

Example:

select table1.Column1,table1.Column2,table2.Column1 from table1 left outer join table2 on table1.PK_Id=table2.FK_Id

RIGHT OUTER JOIN

In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

Example:

select table1.Column1,table1.Column2,table2.Column1 from table1 right outer join table2 on table1.PK_Id=table2.FK_Id

FULL OUTER JOIN

The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2).  The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.

Example:

select table1.Column1,table1.Column2,table2.Column1 from table1 Full outer join table2 on table1.PK_Id=table2.FK_Id