Post
Ask Question

SQL Commands

Introduction

SQL Server is a powerful relational database management system (RDBMS) developed by Microsoft. It provides a wide range of Structured Query Language (SQL) commands to interact with and manipulate data in the database. SQL commands are categorized into different types based on their functionality. In this article, we will explore the most common types of SQL commands in SQL Server with examples.

In SQL Server, SQL commands can be categorized into four main types: Data Definition Language (DDL) commands, Data Manipulation Language (DML) commands, Data Control Language (DCL) commands, and Transaction Control Language (TCL) commands. Each type serves a specific purpose in interacting with the database. Let's explore each type in more detail.

Data Definition Language (DDL) Commands

Data Definition Language (DDL) commands in SQL Server are used to define and manage the structure of database objects, such as tables, indexes, and views. These commands enable you to create, modify, and delete database objects. Here are some common DDL commands in SQL Server, along with examples.

CREATE Command

By using the "CREATE" command, we can create tables, indexes, views, etc.

The CREATE TABLE command is used to create a new table in the database.

Example

Let's create a table called "Employees" with columns for employee information.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50),
    Salary DECIMAL(10, 2)
);

CREATE INDEX Command

The CREATE INDEX command is used to create an index on one or more columns of a table. Indexes improve query performance by speeding up data retrieval.

Example

Let's create an index on the "EmployeeID" column of the "Employees" table.

CREATE INDEX idx_EmployeeID ON Employees(EmployeeID);

CREATE VIEW Command

The CREATE VIEW command is used to create a virtual table based on the result of a SELECT statement. Views are used to simplify complex queries and provide an abstraction layer over the underlying data.

Example

Let's create a view called "EmployeeDetails" that includes the first name, last name, and department of employees

CREATE VIEW viewEmployeeDetails AS
SELECT FirstName, LastName, Department
FROM Employees;

ALTER Command

By using the "ALTER" command, we can modify existing tables, views, etc.

The ALTER TABLE command is used to modify an existing table by adding, modifying, or deleting columns.

Example

Let's add a new column, "Email" to the "Employees" table

ALTER TABLE Employees
ADD Email VARCHAR(100);

DROP Command

By using the "DROP" command, we can delete existing tables, indexes, views, etc.

The DROP TABLE command is used to delete an existing table and its data permanently from the database.

Example

Let's delete the "Employees" table

DROP TABLE Employees;

DROP INDEX Command

The DROP INDEX command is used to remove an existing index from a table.

Example

Let's drop the previously created index on the "EmployeeID" column

DROP INDEX idx_EmployeeID ON Employees;

DROP VIEW Command

The DROP VIEW command is used to remove an existing view from the database.

Example

Let's drop the "EmployeeDetails" view

DROP VIEW viewEmployeeDetails;

These are some of the fundamental Data Definition Language (DDL) commands in SQL Server. They are essential for defining and managing the database structure and schema.

Data Manipulation Language (DML) Commands

Data Manipulation Language (DML) commands in SQL Server are used to interact with the data stored in the database. These commands allow you to insert, update, delete, and retrieve data from tables. The primary DML commands in SQL Server are:

  • INSERT: Used to add new records (rows) into a table.
  • SELECT: Used to retrieve data from one or more tables. It is also used for filtering, sorting, and aggregating data.
  • UPDATE: Used to modify existing records in a table.
  • DELETE: Used to remove one or more records from a table.

Now, let's look at examples of each of these commands.

INSERT Command

Suppose we have a table named Employees with the following structure.

EmployeeID FirstName LastName Department
1 Rohit Sharma HR
2 Amit Mohanty IT

To insert a new employee into the table, you can use the INSERT command like this.

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department)
VALUES (3, 'Alok', 'Pradhan', 'Marketing');

After executing this command, the Employees table will look like this.

EmployeeID FirstName LastName Department
1 Rohit Sharma HR
2 Amit Mohanty IT
3 Alok Pradhan Marketing


SELECT Command

The SELECT statement allows you to retrieve data from one or more tables. For example.

SELECT EmployeeID, 
       FirstName, 
       LastName, 
       Department
  FROM Employees
 WHERE Department = 'IT';

This query will retrieve all the employees who work in the IT department from the Employees table.

UPDATE Command

Suppose you want to update the department of an employee with EmployeeID 2 to 'Operations'.

UPDATE Employees
   SET Department = 'Operations'
 WHERE EmployeeID = 2;

After executing this command, the Employees table will look like this.

EmployeeID FirstName LastName Department
1 Rohit Sharma HR
2 Amit Mohanty Operations
3 Alok Pradhan Marketing


DELETE Command

To remove an employee with EmployeeID 1 from the Employees table, you can use the DELETE command.

DELETE FROM Employees
 WHERE EmployeeID = 1;

After executing this command, the Employees table will look like this.

EmployeeID FirstName LastName Department
2 Amit Mohanty Operations
3 Alok Pradhan Marketing

These are some basic examples of DML commands in SQL Server. With these commands, you can manage and manipulate the data stored in your database tables effectively.

Data Control Language (DCL) Commands

Data Control Language (DCL) commands are used to control access and permissions on database objects. DCL commands allow database administrators to grant or revoke privileges to users and roles, ensuring data security and integrity. There are two main DCL commands in SQL Server: GRANT and REVOKE.

GRANT Command

The GRANT command is used to provide specific privileges to users or roles on certain database objects. These privileges can include SELECT, INSERT, UPDATE, DELETE, EXECUTE, and more, depending on the actions the user or role should be able to perform on the object. Syntax: GRANT <privilege> ON <object> TO <user_or_role>.

Example

Let's say you have a database called "SalesDB," and you want to grant the SELECT privilege on the "Customers" table to a user named "AmitM".

USE SalesDB;
GRANT SELECT ON Customers TO AmitM;

In the above example, AmitM will be able to execute SELECT queries on the "Customers" table but won't have permissions for other actions like INSERT, UPDATE, or DELETE.

REVOKE Command

The REVOKE command is used to remove specific privileges from users or roles, restricting their access to certain database objects.

Syntax

REVOKE <privilege> ON <object> FROM <user_or_role>

Example

Assuming you want to revoke the UPDATE privilege on the "Products" table from the user "AmitM" in the "SalesDB" database.

USE SalesDB;
REVOKE UPDATE ON Products FROM AmitM;

After this command, AmitM will no longer be able to perform UPDATE operations on the "Products" table.

It's important to use DCL commands carefully, as they directly affect the access rights of users and roles in the database. Always consider security requirements and user roles before applying GRANT or REVOKE commands. Additionally, you need to have appropriate administrative privileges to execute DCL commands successfully.

Transaction Control Language (TCL) Commands

Transaction control commands in SQL Server are used to manage transactions, which are sets of one or more SQL statements that are executed as a single unit of work. Transactions ensure that all the statements within them are either all committed or all rolled back if an error occurs or if the user explicitly chooses to cancel the transaction.

SQL Server provides the following transaction control commands

BEGIN TRANSACTION Command

This command is used to start a new transaction. After executing this command, any SQL statements following it will be part of the same transaction until it is either committed or rolled back.

Example

Let's say we have a table named "Employees" with columns "EmployeeID," "FirstName," and "LastName." We want to update an employee's last name and need to ensure it's done within a transaction.

BEGIN TRANSACTION;
    UPDATE Employees
       SET LastName = 'Mohanty'
     WHERE EmployeeID = 101;

    -- other SQL statements can be executed here within the same transaction
COMMIT;

COMMIT Command

This command is used to permanently save the changes made within the current transaction to the database. Once the COMMIT command is executed, the changes become permanent and cannot be rolled back.

Example

Continuing from the previous example.

BEGIN TRANSACTION;
    UPDATE Employees
       SET LastName = 'Mohanty'
     WHERE EmployeeID = 101;

    -- other SQL statements can be executed here within the same transaction
COMMIT;

ROLLBACK Command

This command is used to undo all the changes made within the current transaction. It brings the database back to the state it was in before the transaction started.

Example

Continuing from the previous example, let's consider an error scenario.

BEGIN TRANSACTION;
    UPDATE Employees
       SET LastName = 'Mohapatra'
     WHERE EmployeeID = 101;

    -- An error occurs, and the transaction needs to be rolled back
ROLLBACK;

In this case, the update to the employee with EmployeeID 101 will be undone, and the data will remain unchanged.

By using these transaction control commands effectively, you can ensure the integrity and consistency of data in your SQL Server database, especially when dealing with complex operations that involve multiple SQL statements that need to be treated as a single unit of work.

Conclusion

SQL Server's diverse range of SQL commands, including DDL, DML, DCL, and TCL, empower users to efficiently manage and manipulate data. Understanding and utilizing these commands are vital for maintaining data integrity and optimizing database operations. Hope this article will help users to understand the Types of SQL Commands. Happy Coding.


Similar Articles