SQL Commands Explained: Learn How to Work with Databases Effectively

Types of SQL Commands with Examples

SQL (Structured Query Language) is a powerful language used to interact with relational databases. It allows you to perform various operations on data, such as retrieving, inserting, updating, and deleting information. This article will explore the different types of SQL commands with illustrative examples.

1. Data Definition Language (DDL) Commands

DDL commands are used to define and modify the structure of database objects like tables, indexes, views, and schemas.

• CREATE: This command is used to create new database objects.
  • Example:

                        CREATE TABLE Customers (
                            CustomerID INT PRIMARY KEY,
                            CustomerName VARCHAR(255),
                            City VARCHAR(255)
                        );
                    

• ALTER: This command is used to modify the structure of existing database objects.
  • Example:

                        ALTER TABLE Customers 
                        ADD Column PhoneNumber VARCHAR(20);
                    

• DROP: This command is used to delete database objects.
  • Example:

                        DROP TABLE Customers;
                    

• TRUNCATE: This command is used to remove all rows from a table quickly.
  • Example:

                        TRUNCATE TABLE Customers; 
                    

2. Data Manipulation Language (DML) Commands

DML commands are used to manipulate data within database tables.

• SELECT: This command is used to retrieve data from one or more tables.
  • Example:

                        SELECT CustomerName, City 
                        FROM Customers;
                    

• INSERT: This command is used to add new rows to a table.
  • Example:

                        INSERT INTO Customers (CustomerID, CustomerName, City) 
                        VALUES (1, 'John Doe', 'New York');
                    

• UPDATE: This command is used to modify existing data in a table.
  • Example:

                        UPDATE Customers 
                        SET City = 'Los Angeles' 
                        WHERE CustomerID = 1;
                    

• DELETE: This command is used to remove rows from a table.
  • Example:

                        DELETE FROM Customers 
                        WHERE CustomerID = 1;
                    

3. Data Control Language (DCL) Commands

DCL commands are used to control access to the database.

• GRANT: This command is used to grant privileges to users.
  • Example:

                        GRANT SELECT ON Customers TO user1;
                    

• REVOKE: This command is used to revoke privileges from users.
  • Example:

                        REVOKE SELECT ON Customers FROM user1;
                    

4. Transaction Control Language (TCL) Commands

TCL commands are used to manage transactions in the database.

• COMMIT: This command is used to save the changes made in a transaction.
• ROLLBACK: This command is used to undo the changes made in a transaction.
• SAVEPOINT: This command is used to create a savepoint within a transaction.

5. Other Important SQL Commands

• JOIN: This command is used to combine data from two or more tables.
• WHERE: This command is used to filter data based on specific conditions.
• ORDER BY: This command is used to sort the result set.
• GROUP BY: This command is used to group rows that have the same values.
• HAVING: This command is used to filter groups of rows.

This article provides a basic overview of the different types of SQL commands. By understanding these commands, you can effectively interact with databases and perform a wide range of operations on your data.