Basics of SQL Commands

Basics of SQL Commands

16 Mar 2024
Beginner
11.3K Views
16 min read

SQL Commands: An Overview

SQL commands are a set of instructions used to interact with the database like SQL Server, MySql, Oracle, etc. SQL commands are responsible for creating and doing all the manipulation on the database. These are also responsible for giving/taking out access rights to a particular database.

Read More: SQL Server Interview Questions and Answers

SQL Commands Category/Types of SQL Commands

We have different SQL commands for different purposes. We can group Sql Commands into five major categories depending on their functionality.

SQL Commands Category/Types of SQL Commands

1. Data Definition Language (DDL)

These SQL commands are used for creating a table, deleting a table, altering a table, and truncating the table. All the commands of DDL are auto-committed i.e. it permanently saves all the changes in the database.

In this category, we have four commands:

  1. CREATE: It is used to create a new table in the database. Here, you need to specify the name of the table and the name of each column in the table.

    Syntax

    
    CREATE TABLE table_name (
      column_1 datatype, 
      column_2 datatype, 
      column_3 datatype
    );  

    Example

    
    CREATE TABLE Employee
    (
    EmployeeID int,
    EmployeeName varchar(200),
    PhoneNumber int,
    Email varchar(255),
    City varchar(255),
    Country varchar(255)
    );
    
  2. ALTER: It changes the structure of a table by adding, deleting, or modifying columns in an existing table.

    To add a column

    Syntax

    
    ALTER TABLE TableName
    ADD ColumnName Datatype;
    

    To delete a column

    Syntax

    
    ALTER TABLE TableName
    DROP COLUMN ColumnName;
    

    To modify a column

    Syntax

    
    ALTER TABLE TableName
    ALTER COLUMN ColumnName Datatype;
    

    Example

    
    --ADD Column DOB:
    ALTER TABLE Employee
    ADD DOB varchar(10);
     
    --DROP Column DOB:
    ALTER TABLE Employee
    DROP COLUMN DOB;
    
    --Add a column DOB and change the data type to Date.
    ALTER TABLE Employee
    ADD DOB year;
     
    ALTER TABLE Employee
    ALTER DOB date;
    
  3. DROP: This command is used to drop an existing table or a database.

    Syntax

    
    DROP DATABASE DatabaseName;
    

    Example

    
    DROP DATABASE Employee;
    

    Syntax

    
    DROP TABLE TableName;
    

    Example

    
    DROP Table Employee;
    
  4. Read More: Drop all tables, stored procedures, views, and triggers

  5. TRUNCATE: This command deletes the information present in the table or all the rows from the table but does not delete the table.

    Syntax

    
    TRUNCATE TABLE TableName;
    

    Example

    
    TRUNCATE Table Employee;
    
  6. Note

    1. Only with DDL commands, we need to write keywords (like table, procedure, view, index, function) with the syntax of the command.

    2. These commands are used to create/modify the structure of the database object.

2. Data Manipulation Language (DML)

These SQL commands modify the database. The commands of DML are not auto-committed i.e. it can't permanently save all the changes in the database. They can be rolled back.

In this category, we have three commands:

  1. INSERT: It is used to insert new records into the table or data into the row of a table.

    Syntax

    
    INSERT INTO TableName (Column1, Column2, Column3, ...,ColumnN)
    VALUES (value1, value2, value, ...);
    or
    INSERT INTO TableName
    VALUES (Value1, Value2, Value3, ...);
    

    Example

    
    INSERT INTO Employee(EmployeeID, EmployeeName, PhoneNumber, Email, City, Country)
    VALUES ('01', 'Sourav', '9921321789', 'House No 12', 'Jharkhand', 'India');
     
    INSERT INTO Employee
    VALUES ('01', 'Sourav', '9921321789', 'House No 12', 'Jharkhand', 'India');
    
  2. Read More: How to insert values to identity column in SQL Server

  3. UPDATE: This command is used to edit rows or modify the records already present in the table.

    Syntax

    
    UPDATE TableName
    SET Column1 = Value1, Column2 = Value2, ...
    WHERE Condition;
    

    Example

    
    UPDATE Employee
    SET EmployeeNamw = 'Surabhi', City= 'Mumbai'
    WHERE EmployeeID = 7;
    
  4. DELETE: It is used to remove one or more rows from a table.

    Syntax

    
    DELETE FROM TableName WHERE Condition;
    

    Example

    
    DELETE FROM Employee
    WHERE EmployeeName='Devanshi';
    

Read More:

3. Data Query Language (DQL)

This SQL command is used to fetch/retrieve data from database tables.

In this category, we have only the SELECT command.

  1. SELECT: These statements are used to fetch data from a database. The data returned is stored in a result table, called the result-set.

    Syntax

    
    SELECT Column1, Column2, ...ColumN
    FROM TableName;
    
    --(*) is used to select all from the table
    SELECT * FROM table_name;
    

    Example

    
    SELECT EmployeeID, EmployeeName
    FROM Employee;
     
    --(*) is used to select all from the table
    SELECT * FROM Employee;
    

4. Transaction Control Language (TCL)

These SQL commands are used to handle changes affecting the data in the database. We use these commands within the transaction or to make a stable point during changes in the database at which we can roll back the database state if required.

In this category, we have three commands:

  1. SAVEPOINT: This command is used to save a transaction temporarily. You can roll the transaction back to a certain point without rolling back the entire transaction.

    Syntax

    
    SAVEPOINT SAVEPOINTNAME;
    

    Example

    
    INSERT INTO Employee VALUES(06, 'Pradnya');
    SAVEPOINT S2;
    
  2. ROLLBACK: This command restores the database to the last committed state.

    Syntax

    
    ROLLBACK;
    

    Example

    
    ROLLBACK TO S2;
    SELECT * FROM Employee;
    
  3. COMMIT: It saves all the transactions to the database.

    Syntax

    
    COMMIT;
    

    Example

    
    INSERT INTO Employee VALUES(05, 'Sakshi');
    COMMIT;
    

5. Data Control Language (DCL)

These SQL commands are used to implement security on database objects like tables, views, stored procedures, etc. It consists of commands which deal with the user permissions and controls of the database system.

In this category, we have two commands:

  1. GRANT: This command permits specific users on specific database objects like table, view, etc.

    Syntax

    
    GRANT PrivilegeName
    ON ObjectName
    TO {UserName |PUBLIC |RoleName}
    [WITH GRANT OPTION];
    

    Here,

    • PrivilegeName: It is the privilege/right/access granted to the user.
    • ObjectName: Name of a database object like TABLE/VIEW/STORED PROC.
    • UserName: Name of the user who is given the access/rights/privileges.
    • PUBLIC: To grant access rights to all users.
    • RoleName: The name of a set of privileges grouped.
    • WITH GRANT OPTION: To give the user access to grant other users with rights.

    Example

    
    GRANT SELECT ON Employee TO user1;
    
  2. REVOKE: This command is used to withdraw the user’s access privileges given by the GRANT command.

    Syntax

    
    REVOKE PrivilegeName 
    ON ObjectName 
    FROM {UserName |PUBLIC |RoleName}
    

    Example

    
    REVOKE SELECT ON Employee_Info TO user1; 
    
Summary

So, here we gave you a complete description of the basic commands in SQL to work on the database. If you want to gain a practical understanding, you can enroll in our SQL Server Course.

Do you Know?

.NET is gaining popularity day by day, especially after the release of .NET 8. .NET 8 is not only a framework version but much more than that. It redefines the way software applications are built and deployed, enabling developers to meet the evolving demands of modern computing.

Therefore, if you want to upskill yourselves and stand out from others consider our following training programs on .NET.

Take our free sqlserver skill challenge to evaluate your skill

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET CHALLENGE

Share Article
Batches Schedule
About Author
Shailendra Chauhan (Microsoft MVP, Founder & CEO at Scholarhat by DotNetTricks)

Shailendra Chauhan is the Founder and CEO at ScholarHat by DotNetTricks which is a brand when it comes to e-Learning. He provides training and consultation over an array of technologies like Cloud, .NET, Angular, React, Node, Microservices, Containers and Mobile Apps development. He has been awarded Microsoft MVP 8th time in a row (2016-2023). He has changed many lives with his writings and unique training programs. He has a number of most sought-after books to his name which has helped job aspirants in cracking tough interviews with ease.
Self-paced Membership
  • 22+ Courses
  • 750+ Hands-On Labs
  • 300+ Quick Notes
  • 55+ Skill Tests
  • 45+ Interview Q&A
  • 10+ Real-world Projects
  • Career Coaching
  • Email Support
Upto 66% OFF
KNOW MORE..

To get full access to all courses

Accept cookies & close this