Introduction to SQL (Structured Query Language)

Structured Query Language (SQL) is the 4^th generation programming language that is used to perform any kind of operation on the database. The operations are like saving, searching or removing the data from the database.

SQL is very important to perform all kind of operations on the database. Without SQL it will be very difficult to manage the database.

Primarily we can categorize the SQL statements are of 2 types.

1. DDL (Data Definition Language): Data definition means managing the data structure of the database. E.g. for data structures in the database are tables, views, stored procedures etc. example for DDL statements are CREATE, ALTER, DROP, and TRUNCATE
2. DML (Data Manipulation Language). Data manipulation means modifying the data in the database. E.g. for DML statements are INSERT, UPDATE, and SELECT

We will discuss about each statement in detail.

DDL (Data Definition Language)

CREATE: It is used to create any object like database, table, user, function etc. in the database.

crate database [databasename];

DROP: For deleting the object from database this is used.

drop database [databasename];

ALTER: To alter any existing permission or role of the user/database we can do using “ALTER” statement

alter table [tablename] add column [columnname] [datatype];

TRUNCATE: To delete all the data from the table without having any impact on the table schema “TRUNCATE” is used. When truncate is used where condition is not applied.

truncate table [tablename];

RENAME: To rename any object in the database, this statement is used.

ALTER TABLE [tablename]
RENAME COLUMN [oldname] TO [newname];

DDL (Data Definition Language)

INSERT: This is used to save the data into a table in row and column wise.

insert into [tablename] (column1, column2, column3)
values (value1, value2, value3)

SELECT: To retrieve the data from table “SELECT” statement is used

select * from [tablename]

UPDATE: For updating the existing data with the new data that is stored in the table “UPDATE” statement is used. e.g. employee table, to update the name of an employee then this is used.

update [tablename] set column=value where [condition]


note: without where condition that statement updates all the rows of the table, so be careful using this statement.

DELETE: To delete any record from table “DELETE” statement is used.

delete from [tablename] where [condition]

note: same as update statement delete also delete all the rows if it is not used without where condition.

MERGE: Merge is the statement used to merge the data between 2 tables. E.g. there are 2 tables in database one is [source] and other one is [target] in order to merge the data of [source] in to [target] then by using merge statement this will be achieved. It compares the 2 tables and inserts, deletes or updates the data based on the data.

MERGE [targettable] AS TARGET
	USING [sourcetable] AS SOURCE 
	ON (TARGET.ID = SOURCE.ID)
	WHEN MATCHED 
		[condition]
	THEN UPDATE
		SET TARGET.colum1 = SOURCE.colum1,
		TARGET.colum2 = SOURCE.colum2
	WHEN NOT MATCHED BY TARGET 
	THEN INSERT (colum1, colum2, colum3)		 
		VALUES (SOURCE.colum1, SOURCE.colum2, SOURCE.colum3)
	WHEN NOT MATCHED BY SOURCE 
	THEN DELETE;

CALL: This statement is used to execute the [stored procedure] in the database.

call [storedprocedurename(paramenter1, paramenter2)];

Hope this gave you the basic understanding of the SQL and their command types.