Introduction to SQL

SQL, an acronym for Structured Query Language, is a database query language used to manage and store data in Relational DBMS. The majority of the RDBMS like SQL Server, MySQL, Oracle, and MS Access, uses SQL for there database operational activities. Data within RDBMS gets stored in objects known as tables, whereas a table is a collection of related data containing columns and rows.

With the primary purpose of manipulating data, below are the ways SQL manages the data and objects available within RDBMS.

Data Definition Language [DDL]

These auto-committed commands within DDL correspond to structural changes for a table in a database like Create, Alter, Truncate, Dropping, and Rename.

Command	Description	Sample
Create	Creates a new table in a database or a database.	CREATE DATABASE AdventureWorks; CREATE TABLE Customer( id int, name varchar(50), gender varchar(10), contact varchar(50));
Alter	Alter table structure.	ALTER TABLE Customer ADD( address VARCHAR(200));
Truncate	Delete all rows from the table and initializes the table.	TRUNCATE TABLE Customer;
Drop	Drop/Delete the table from the database.	DROP TABLE Customer;
Rename	Rename a table.	RENAME TABLE Customer to Customer_Info;

Data Manipulation Language [DML]

The DML commands manipulate the data stored in the tables. These commands are not auto-committed and can be handled through the transaction, which is briefed in TCL commands later on.

Command	Description	Sample
Insert	Inserts a new row in the table.	INSERT INTO Customer VALUES(101, 'Adam', ‘male’,’+15525668565’);
Update	Updates one or many rows in the table based on condition.	UPDATE Customer SET name=’John ’WHERE id=101;
Delete	Delete one or many rows in the table based on condition.	DELETE FROM Customer WHERE id=101;
Merge	Merge source and target table/rows.	MERGE target_table USING source_tableON merge_conditionWHEN MATCHED THEN update_statementWHEN NOT MATCHED THEN insert_statementWHEN NOT MATCHED BY SOURCE THEN DELETE;

Transaction Control Language [TCL]

TCL commands provide logical transactions to operations performed on the table through DML statements. TCL commands have the ability to commit changes or rollback data to its original state in case any exception or error occurs during data transition.

Save data permanently to the database.COMMIT;

Command	Description	Sample
Commit
Rollback	Revert back data to its previous state.	ROLLBACK TO savepoint_name;
Savepoint	Temporary save a transaction and can be rollbacked at any time	SAVEPOINT savepoint_name;

Data Control Language [DCL]

DCL command operates in a way to manage database user access over the databases to perform certain operations. Operation varies from database creation, table creation/alteration, creating stored procedures and views, and so on.

Command	Description	Sample
Grant	Grant user permission to perform a specific operation over the database.	GRANT CREATE TABLE TO alexDba;
Revoke	Revoke user rights to perform an operation.	REVOKE CREATE TABLE FROM alexDba;

Data Query Language [DQL]

The most commonly used operations fall under DQL commands. DQL is used to fetch information from database tables based on certain conditions. This can be used in stored procedures, views, functions, and tend to integrate with other commands if required.

Command	Description	Sample
Select	Retrieve a record from one or multiple tables based on condition.	SELECT * FROM Customer WHERE name = 'Adam';

SQL falls under ANSI/ISO standard; however, each RDBMS has there owned propriety extension to SQL standards. To fall in compliance with SQL ANSI standard, RDBMS implements the primary SQL commands like Select, Insert, Update, Delete similarly.

Web API for developers Free Trial Offline SDK

Other useful tutorials: