In this article, we learn about SQL commands.

DATA DEFINITION LANGUAGE (DDL): Data definition language is used to define the structure of the database.

DDL COMMANDS:-

CREATE

  • Create is used to create the database, table, index, function, views, store procedure and triggers.
  • Syntax: CREATE DATABASE DatabaseName;

ALTER

  • Alter is used to alter the structure of the database. By using Alter command we can alter the data type of the column and drop the column.
  • Syntax:

ALTER TABLE TableName ADD ColumnName Datatype;

ALTER TABLE TableName DROP COLUMN ColumnName;

DROP

  • Drop is used to deleting an existing table or database.
  • Syntax: DROP DATABASE DatabaseName;

RENAME

  • Rename is used to rename the table existing in the database.
  • SQL Server does not have any statement that directly renames a table. However, it does provide you with a stored procedure named sp_rename that allows you to change the name of a table.
  • Syntax: EXEC sp_rename ‘old_tablename’, ‘new_tablename’;

TRUNCATE

  • Truncate is used to remove all records from a table, including all spaces allocated for the records are removed.
  • Syntax: TRUNCATE TABLE TableName;

COMMENT

  • It is used to add comments to the data dictionary.

There are two ways of comments in SQL Server:

  • Single-Line Comment starts with two hyphens(- -).
  • Multi-Line Comment starts with /* and ends with */.

DATA MANIPULATION LANGUAGE (DML): Data manipulation language is used to manipulate the data.

  • If we enter wrong information in a table then by using DML command we can easily change and rolled back it.

There are two types of DML:

  • Procedural: The user specifies both what data is needed and how to get that data. Example: Relational Algebra.
  • Non-Procedural(Declarative): In this type only what data is needed is specified. It is easier for the user. It does not generate code as efficient as that produced by procedural languages. Example: Tuple Relational Calculus.

DML COMMANDS:-

INSERT

  • Insert is used to insert data into a table.
  • Syntax: INSERT INTO table_name (column1, column2, column3, …)
    VALUES (value1, value2, value3, …);

UPDATE

  • The update is used to update existing data within a table.
  • Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, …
WHERE condition;

DELETE

  • Delete is used to delete records or the entire table. It can be used with the WHERE clause.
  • Syntax: DELETE FROM table_name WHERE condition;

MERGE is used to UPSERT operation (insert or update).

CALL is used to call a PL/SQL or Java subprogram.

LOCK TABLE is used to concurrency control.

EXPLAIN PLAN is used to the interpretation of the data access path.

DATA QUERY LANGUAGE (DQL): The purpose of DQL Command is to get some schema relation based on the query passed to it.

  • DQL consists of only one command over which data selection in SQL.
  • The SELECT command is a combination with other SQL clauses is used to retrieve and fetch data from a database or table based on certain conditions applied by the user.
  • Syntax:

SELECT * FROM table_name;

SELECT column1, column2, … FROM table_name;

DATA CONTROL LANGUAGE (DCL): To control the access of the data stored in the database.

DCL COMMANDS:-

GRANT

  • It gives user’s access privileges to the database.
  • Example: GRANT CREATE TABLE TO USER1

REVOKE

  • Revoke withdraw the user’s access privileges given by using the GRANT command.
  • Example: REVOKE CREATE TABLE FROM USER1

TRANSACTION CONTROL LANGUAGE (TCL): TCL commands deal with the transaction within the database.

TCL COMMANDS:-

  • COMMIT is used to commits a Transaction.
  • ROLLBACK is used to rollbacks a transaction in case any error occurs.
  • SAVEPOINT is used to sets a savepoint within a transaction.
  • SET TRANSACTION is used to specify characteristics for the transaction.

If you are new to SQL Server start with the following must-watch video: -