In this article, we learn about SQL commands.
DATA DEFINITION LANGUAGE (DDL): Data definition language is used to define the structure of the database.
- Create is used to create the database, table, index, function, views, store procedure and triggers.
- Syntax: CREATE DATABASE DatabaseName;
- 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.
ALTER TABLE TableName ADD ColumnName Datatype;
ALTER TABLE TableName DROP COLUMN ColumnName;
- Drop is used to deleting an existing table or database.
- Syntax: DROP DATABASE DatabaseName;
- 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 is used to remove all records from a table, including all spaces allocated for the records are removed.
- Syntax: TRUNCATE TABLE TableName;
- 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.
- Insert is used to insert data into a table.
- Syntax: INSERT INTO table_name (column1, column2, column3, …)
VALUES (value1, value2, value3, …);
- The update is used to update existing data within a table.
SET column1 = value1, column2 = value2, …
- 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.
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.
- It gives user’s access privileges to the database.
- Example: GRANT CREATE TABLE TO USER1
- 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.
- 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: -