SQL COMMANDS

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: -