What is SQL Server – Types of SQL Commands

SQL Commands

What is SQL?

 SQL  stands for Structured Query Language. SQL is a language used to store, retrieve, and manipulate data in a Relational Database Management Systems (RDMS)
such as SQL Server, Oracle, MySQL, etc.
SQL allows a user to communicate with the database using commands.

Features of SQL

    • SQL is used to create, truncate, and drop the database and table using SQL commands.
    • SQL can be used to execute the queries against the database to perform various operations.
    • SQL is used to create a function, stored procedure, trigger and view in a database.
    • It provides security and allows a users to set permissions on the tables, stored procedures, and views in the database.

What is a Database?

A database is a place to store the data in an electronic format. It is an organized collection of database objects such as tables, views, functions, stored procedures, triggers, and so on.
It allows a user to easily access or manipulate the data.

SQL Server database

Types of SQL Commands

The following are the 5 types of SQL Commands.

    •  DDL  (Data Definition Language)
    •  DML (Data Manipulation Language)
    •  DCL (Data Control Language)
    •  TCL (Transaction Control Language)
    •  DQL (Data Query Language)

DDL (Data Definition Language)

In SQL,  DDL  are the set of commands used to define database schema. These commands are use to change the structure of database objects.

In simple words, DDL commands are mainly used for creating or modifying a database/table structure and schema.

    • CREATE
    • ALTER
    • SP_RENAME
    • TRUNCATE
    • DROP

CREATE Command In SQL Server

CREATE: The  CREATE  command in SQL Server is used to create a new database object. It can be used to create a new table, view, function, stored procedure, etc. in the database.

Syntax to create a new table in the database:

CREATE TABLE Table_Name (
Column_Name1 Data_Type (Size),
Column_Name2 Data_Type (Size),
Column_NameN Data_Type (Size)
);

Example: The following Create command will create a new table ‘Employee’ in the database.

CREATE TABLE Employee
( 
    Id INT PRIMARY KEY,
 
    Name  VARCHAR(50), 

    Salary DECIMAL(18, 2) 

);

ALTER Command In SQL Server

ALTER: The  ALTER  command in SQL is used to add, modify, or delete, columns or constraints in an existing table.
This command is used to change the structure of an existing table in the database.

Add a new column in an existing table

Syntax to add a new column in an existing table:

ALTER TABLE Table_Name ADD New_Column_Name Data_Type (Size);

Example: The following Alter statement will add a new column in the ‘Employee’ table.

ALTER TABLE Employee ADD City VARCHAR(20) 

Change data type and size of an existing column

Syntax to change the existing column datatype and size:

ALTER TABLE Table_Name ALTER COLUMN Column_Name New_Data_Type (New_Size)

Example: The following Alter statement is used to change the data type and size of an existing column ‘City’.

ALTER TABLE Employee ALTER COLUMN City NVARCHAR (100);

Delete existing column from a table:

Syntax to delete an existing column from a table:

ALTER TABLE Table_Name DROP COLUMN Column_Name

Example: The following Alter statement is used to drop the existing column ‘City’ from the table.

ALTER TABLE Employee DROP COLUMN City;

SP_RENAME Command In SQL Server

In SQL Server  SP_RENAME  is basically a system-defined stored procedure, which can be used to change the column and table name.

Change existing column name

Syntax to change an existing column name from a table:

SP_RENAME ‘Table_Name.Old_Column_Name’,’New_Column_Name’ ;

Example: The following SP_RENAME statement is used to change the existing column name from a table.

SP_RENAME 'Employee.Name','EmployeeName';

Change existing table name

Syntax to change an existing table name:

SP_RENAME ‘Old_Table_Name’,’New_Table_Name’ ;

Example: The following SP_RENAME statement is used to change the old table name by the new name.

SP_RENAME 'Employee','EmployeeDetails' ;

Truncate Command in SQL Server

The  TRUNCATE  command in SQL Server is to delete all the records from a table, but not the table itself. It doesn’t support the ‘where’ clause, that’s why we can’t delete a specific record.

Delete all the data from a table

Syntax to use the truncate command:

TRUNCATE TABLE Table_Name

Example: The following TRUNCATE statement is used to delete all the rows from a table.

TRUNCATE TABLE EmployeeDetails

DROP Command in SQL Server

The  DROP  command in SQL Server is used to remove an object from the database.
If we use drop command on a table, the table including all the records and constraints will be removed from the database.

Remove a table from the database

Syntax to use the drop command:

DROP TABLE Table_Name

Example: The following DROP statement is used to delete the ‘EmployeeDetails’ table from the database.

DROP TABLE EmployeeDetails ;

DML (Data Manipulation Language)

The  DML  commands in SQL Server are used to manipulate or modify the data stored in the database. These commands can be used to insert, update, or delete the records from the database.

    • INSERT
    • UPDATE
    • DELETE

INSERT Command In SQL Server

The  INSERT  command in SQL Server is used to add a new record in the table.

Insert a new record into the table

Syntax to use the Insert command:

INSERT INTO Table_Name (Column_Name1, Column_Name2, ColumnN)
VALUES (Value1, Value2, ValueN);

Example: The following INSERT statement is used to add a new row in the ‘EmployeeDetails’ table.

INSERT INTO EmployeeDetails( Id, EmployeeName, Salary)
VALUES (101, 'Shekh Ali', 70000);
insert record into the table

UPDATE Command In SQL Server

The  UPDATE  command in SQL server is used to change or modify the existing records in a table.

Modify existing records in a table

Syntax to use the Update command:

-- To update specific records based on where condition

UPDATE Table_Name
SET Column_Name1 = Value1, Column_Name2 = Value2, ColumnN = ValueN
WHERE condition;

-- To update all the records for the particular columns

UPDATE Table_Name
SET Column_Name1 = Value1, Column_Name2 = Value2, ColumnN = ValueN;

Example: The following UPDATE statement is used to modify an existing record in the ‘EmployeeDetails’ table.

-- Update Salary based on Id

UPDATE EmployeeDetails SET Salary = 80000
WHERE Id = 101;
Update record

We should be careful while updating records in a table.
If we execute the update query without the where clause, all the records for the selected columns will be updated in the table.

DELETE Command In SQL Server

The  DELETE  command in SQL Server is used to delete one or more existing records from a table.

Delete existing records from a table

Syntax to use the Delete command:

-- To delete specic records

DELETE FROM Table_Name WHERE condition;

-- OR

-- To delete all the records

DELETE FROM Table_Name ;

Example: The following DELETE statement is used to delete a specific record from the ‘EmployeeDetails’ table.

DELETE FROM EmployeeDetails WHERE Id = 101 ;

Example: The following DELETE statement will delete all the records from the ‘EmployeeDetails’ table.

DELETE FROM EmployeeDetails ;

DCL (Data Control Language)

The  DCL  commands in SQL Server is mainly deal with the right, permissions, and other security-related issues,
Using DCL commands a user can be allowed to access the information from a database.

We can grant a user with various privileges on single or multiple tables. These permissions can be applicable for a user to use the commands
such as SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, or ALL on a table.

Following are the DCL Commands:

    • GRANT
    • REVOKE

GRANT Command In SQL Server

 GRANT  : Grant command in SQL server is used for granting a user access privileges or other privileges to the database.

Grant privileges on a table

Syntax to use the GRANT command:

GRANT privileges ON object TO user;

For example, If we want to grant a user ‘shekhali’ for the SELECT, INSERT, UPDATE, and DELETE privileges on a table called ‘EmployeeDetails’, we should run the following GRANT statement.

GRANT SELECT, INSERT, UPDATE, DELETE ON EmployeeDetails TO shekhali ;

Grant user to create any table

GRANT CREATE ANY TABLE TO user

Grant user to drop any table

GRANT DROP ANY TABLE TO user

REVOKE Command In SQL Server

 REVOKE  – Withdraws or take back some or all the user’s access privileges to the database given by using the GRANT command.

Revoke privileges on a table

Syntax to use the Revoke command:

REVOKE privileges ON object FROM user;

Example to revoke the privileges (SELECT, INSERT, UPDATE, and DELETE) from a user on a table called ”;

REVOKE SELECT, INSERT, UPDATE, DELETE ON EmployeeDetails FROM shekhali ;

To take back privileges

REVOKE CREATE, DROP TABLE FROM user

TCL (Transaction Control Language)

The  TCL  commands in SQL Server is used to manage the transaction or the changes made by DML statements like INSERT, UPDATE and DELETE in a table.

The following are the TCL commands:

    • COMMIT
    • ROLLBACK
    • SAVEPOINT (SAVE TRANSACTION)

COMMIT Command In SQL Server

 COMMIT  : Commit command in SQL Server is used to save all the changes or transactions permanently into the database.

After saving all the changes, Commit ends the current transaction and releases the transaction locks acquired on the tables.

Commit transaction on a table

Syntax to use the commit command.

COMMIT;

Example: Let’s insert some records in the table.

BEGIN TRANSACTION

INSERT INTO EmployeeDetails(Id,EmployeeName,Salary) VALUES(102,'ALI',50000);
INSERT INTO EmployeeDetails(Id,EmployeeName,Salary) VALUES(103,'Klara',60000);

if(@@ERROR > 0)
BEGIN
  ROLLBACK TRANSACTION;
END
BEGIN
   COMMIT TRANSACTION;
END

Let’s run the above transactions to insert the records.

records inserted

Let’s see the result by using the SELECT statement.

show inserted records

ROLLBACK Command In SQL Server

 ROLLBACK  : The Rollback command in SQL Server is used to undo or restores the database to the last committed state in case of any error.
After the rollback, it releases the transaction locks acquired on the tables.

Rollback transaction on a table

Syntax to use the Rollback command.

ROLLBACK ;

Example: The following transaction will Rollback due to violation of the primary key.

Let’s insert some records with the same ‘Id’ key already exist in the table.

BEGIN TRANSACTION

INSERT INTO EmployeeDetails(Id,EmployeeName,Salary) VALUES(104,'Sam',30000);
INSERT INTO EmployeeDetails(Id,EmployeeName,Salary) VALUES(103,'David',40000);

if(@@ERROR > 0)
BEGIN
  ROLLBACK TRANSACTION;
END
BEGIN
   COMMIT TRANSACTION;
END
Rollback Transaction
Rollback Transaction

SAVE TRANSACTION Command In SQL Server

 SAVEPOINT  : Savepoint command in SQL Server is used to save a transaction temporarily. So, that we can Rollback to a certain state or point whenever required.

Following is the syntax of the savepoint command.

SAVE TRANSACTION Savepoint_Name ;

Example of the savepoint command

BEGIN TRANSACTION 
INSERT INTO EmployeeDetails VALUES(104,'Sameer',30000);

 -- Create a savepoint after the first INSERT 
SAVE TRANSACTION A;

Update EmployeeDetails SET EmployeeName ='Priya' WHERE ID=104;

 -- Create a savepoint after the Update 
SAVE TRANSACTION B;

INSERT INTO EmployeeDetails VALUES(105,'David',40000);

 -- Create a savepoint after the Insert
SAVE TRANSACTION C;

INSERT INTO EmployeeDetails VALUES(106,'John',70000);

 -- Create a savepoint after the Insert
SAVE TRANSACTION D;

-- Rollback to the savepoint A
   ROLLBACK TRANSACTION A;

   COMMIT;

Let’s run the above transaction and see the result by using the SELECT Statement.

save transaction result

In the above result, we can see that the only record for the first insert statement is only committed because the transaction was Rollback to savepoint ‘A’ before the rest of the statements.

DQL (Data Query Language)

 DQL  command in SQL Server is used to fetch the records from a database such as SELECT command.

 SELECT  : The select statement in SQL Server is used to fetch the records from the database. It can be used to access the data from the database based on the condition using the ‘where’ clause.

Syntax to write the select statement.

SELECT expressions FROM Table_Name
WHERE conditions;

Example to write a select statement.

-- To show all the records
SELECT * FROM EmployeeDetails;

-- To show the records based on condition
SELECT * FROM EmployeeDetails WHERE ID=101;

Conclusion

SQL Server provides various DDL(Data Definition Language) commands to create and manipulate the database objects such as CREATE, ALTER, TRUNCATE, and DROP.
Using these commands a user can change the structure of the database objects.

The DML (Data Manipulation Language) commands in SQL Server are used to manipulate or modify the data stored in the database.
These commands can be used to insert, update, or delete the records from the database.

The DCL (Data Control Language) commands in SQL Server is mainly deal with the right, permissions, and other security-related jobs. These commands are GRANT and REVOKE.

The TCL (Transaction Control Language) commands in SQL Server is used to manage the transaction or the changes made by DML statements like INSERT, UPDATE and DELETE in a table.
Such as COMMIT, ROLLBACK, and SAVEPOINT.

In the next article, we will discuss how to CREATE, ALTER, AND DROP DATABASE IN SQL SERVER

Hope you enjoyed this post. Thanks for visiting.

Leave a Reply

Your email address will not be published. Required fields are marked *

13 − two =