For those who are in first touch with SQL and it's standard commands... SQL commands in simple words are commands or instructions which we are using with queries to communicate with our database.

With these commands we can do administration for our database as well as use/read data, edit it or copy and delete etc...

It is not uncommon to create our database, tables an relationship between them using some of these commands which we will cover in later part of this article.

In this article I will mainly talk about SQL Server commands which are more or less the same in other database management systems.

SQL Server commands are grouped in these four main logical groups, and they are:

• Data Manipulation Language (DML)
• Data Definition Language (DDL)
• Data Control Language (DCL) 
• Transaction Control Language (TCL)

Using these commands we can define structure of our database, do the insert or update to the data, we can control the access or privileges over our database.


I will explain these commands as they are grouped in this image, so for first I will go with Data Manipulation Language commands group.

Data Manipulation Language - DML

DML Commands are mainly used in similar way and by their name we can realize for what they are mainly used in practise, on next image I will show example of all four commands in real examples:


Short explanation about these commands:

From SELECT part we can see very simple syntax, after we write keyword SELECT with asterisk sign (which replace ALL columns) so with this command in our output we will get all columns from table employee, in case that we don't need or won't all the columns we can list columns that we want to see in output... also in our SELECT command after FROM part we could use WHERE clause to filter output records with some condition.

INSERT command is also very simple, after keyword INSERT we select table name (INTO keyword is optional) after name of table in brackets we can specify which columns we will use in this insert, after that we put keyword VALUES and than in bracket we are typing values that we want to insert. Arrangement of columns in insert part must be the same as in VALUES part so that we insert correct values in correct columns.

UPDATE is very useful when we want to change some data in our record in table, this command has very simple syntax as we can se in image above... after keyword UPDATE we list name of table after that with keyword SET and column that we want to change and than assignment sign with new values (we can combine more than one column)... after that the most important thing is to use WHERE clause to specify criteria for the record that we want to change that can be for example an ID of Person in table.

DELETE is very powerful and simple command, after keyword DELETE we list table name from which one we want to delete the record and after that the most important thing is to use WHERE clause to specify criteria for the record that we want to change that can be for example an ID of Person in table (Same as in UPDATE).

Warning: UPDATE and DELETE without WHERE clause will set same value from update/delete all the records from table!

Data Definition Language - DDL

DDL commands we use for definition and creation objects in database (Table, Procedure, Views...). These commands are mainly used for design and definition the structure of our database.

DDL commands are:

CREATE - we can create new table, database, procedure, view, trigger...
ALTER - usually we use for editing database objects (table, procedure, view...) fo example add or delete column from table
DROP - we use for deleting database objects

Usage for these commands is showed this image:



As for the previous image commands I will wrote some short explanation for DDL commands.

CREATE TABLE will obviously create new table, after two keywords CREATE and TABLE we pick name for new table and in the body of this command we type the columns/attributes for new table... very similar syntax is also for creating new Views, Procedures or Triggers..

ALTER we can use to edit our object, for this example on the last image we have added new column/attribute BirthDate in our table Person.

DROP is used to delete objects from database, we use DROP with keyword for object that we want to create and name of that object.

Data Control Language - DCL

GRANT in first case I gave privileges to user almir to do SELECT, INSERT, UPDATE and DELETE on the table called employees.

REVOKE with this command I can take back privilege to default one... in this case I take back command INSERT on the table employees for user almir.

DENY is specific command.. we can conclude that every user has list of privilege which are denied or granted so command DENY is there to explicitly ban you some privileges on the database objects.

Transaction Control Language - TCL

With TCL commands we can mange and control T-SQL transactions so we can be sure that our transaction are successfully done and that integrity of our database is not violated.

TCL commands are:

BEGIN TRAN - begin of transaction
COMMIT TRAN - commit for completed transaction
ROLLBACK - go back to beginning if something was not right in transaction.



On this image we have simple example of these three commands combined in one trasaction. With BEGIN TRANSACTION obviously we will begin our transaction, as we can see we will update some author with some value of ID... query is more or less clear and the important part is IF-ELSE part of query. If we affect five rows with our first query than we will COMMIT this transaction. Obviously we will not affect five rows, so we will affect one row for this reason we will not go into the IF part but we will go into the ELSE part so our transaction will be returned to beginning state because it will trigger ROLLBACK command and everything that happened in UPDATE it will be undone.

I hope this article was helpful for you, and I hope that this article has explained you a SQL commands groups and commands themselves. This was the simplest way to me to explain these commands for those who have some knowledge about SQL Server commands.

If this article was helpful for you, please share it with your friends or colleagues so that this article can be helpful for them too. Thanks.

Best regards! Almir Vuk | AV Development | http://almirvuk.blogspot.ba/