Learn about SQL with simple syntax

What?

SQL – Structured Query Language

Definition: Storing, Manipulating and retrieving data stored in relational database.

Why?

• Allows us to create a new database, tables
• Allows us to create view, stored procedure, function in the database.
• Allows user to describe data.
• Allows user to access data in relational database management which is call as RDBMS.
• Allows user to set permissions.

How?

The standard SQL commands to interact with Relational Database Management System are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP.

These commands are classified in the following groups.

DDL – Data Definition Language

     1.      CREATE

a.     Create a new table, a view or other object in database.

b.     Syntax:

Go

USE TestExample //Name of the Database

Go

CREATE TABLE [dbo].[Blog]

(

 BlogId int IDENTITY(1,1),

 Name varchar(250),

 Url varchar(max)

);

      2.      ALTER

a.     Edit or modified an existing database object, example table.

b.     Syntax:

GO

USE TestExample

GO

ALTER table [dbo].[Blog]

ALTER column Url varchar(250)

      3.      DROP

a.     Delete or remove entire table, a view or other object in the database.

b.     Syntax:

GO

USE TestExample

GO

ALTER TABLE [dbo].[Blog]

DROP COLUMN Url

DML – DATA MANIPULATION LANGUAGE

     1.      SELECT

a.     Retrieve records of one or more tables.

b.     Syntax:

Select * from [dbo].[Blog];

Select BlogId as "ID",Name as "Name" from [dbo].[Blog];

     2.      INSERT

a.     Add or Insert a new record to the database table.

b.     Syntax:

Insert into [dbo].[Blog] values('Ado.Net Blog');

Insert into [dbo].[Blog](Name) values('Entity Framework');

     3.      UPDATE

a.     Change or Update a record to the database table.

b.     Syntax:

Update [dbo].[Blog] set Name='Entity Framework' where BlogId = 2;

     4.      DELETE

a.     Remove or Delete records to the database table.

b.     Syntax:

Delete from [dbo].[Blog] where BlogId = 3; // Delete single record.

Delete from [dbo].[Blog]; // Delete entire records of the table.

DCL – DATA CONTROL LANGUAGE

     1.      GRANT

a.     Give us to privilege to user.

b.     You can grant on various database object in SQL server. Like Select, Insert, Update, Delete, References, Alter and All. Here object may be Database or Tables and the name of the user that will be granted these privileges.

c.     Syntax:

GRANT privileges ON object TO user;

     2.      REVOKE

a.     Takes back privilege granted from user.

b.     Once you have granted privileges to the user. You may need to revoke some or all of these privileges. For that you can revoke any combination of Select, Insert, Update, Delete, References, Alter or All.

GRANT SELECT ON TestExample To public;

GRANT SELECT, INSERT, UPDATE, DELETE ON TestExample TO sa;