SQL Commands: A Simple Guide for Beginners

SQL

SQL stands for Structured Query Language and it is a language that allows us to communicate with databases. SQL commands are instructions that we can use to perform various tasks on the data stored in the database. In this blog post, we will learn about some of the most common and useful SQL commands and how to use them.

Types of SQL Commands

SQL commands can be broadly classified into five categories: Here is a markdown table for Types of PostgreSQL SQL Commands in proper font:

Examples of SQL Commands

Let’s see some examples of SQL commands using a PostgreSQL database. We will use a table called employee that has four columns: id, name, email, and salary. The table looks like this:

DDL Commands

Some of the common DDL commands are:

• CREATE: This command is used to create a new table in the database. For example:

CREATE TABLE employee (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100),
    salary INT
);

This command creates a table called employee with four columns: id, name, email, and salary. The id column is declared as the primary key, which means it cannot have duplicate or null values.

• DROP: This command is used to delete a table from the database. For example:

DROP TABLE employee;

This command deletes the employee table and all its data from the database.

• ALTER: This command is used to modify the structure of an existing table. For example:

ALTER TABLE employee ADD COLUMN department VARCHAR(50);

This command adds a new column called department to the employee table.

DML Commands

Some of the common DML commands are:

• INSERT: This command is used to insert new data into a table. For example:

INSERT INTO employee (id, name, email, salary) VALUES (5, 'Eve', 'eve@example.com', 9000);

This command inserts a new row into the employee table with the values specified.

• UPDATE: This command is used to update existing data in a table. For example:

UPDATE employee SET salary = salary * 1.1 WHERE id = 5;

This command updates the salary of the employee with id 5 by multiplying it by 1.1.

• DELETE: This command is used to delete existing data from a table. For example:

DELETE FROM employee WHERE id = 5;

This command deletes the row from the employee table where id is 5.

DCL Commands

Some of the common DCL commands are:

• GRANT: This command is used to grant permissions to a user or role on a database object. For example:

GRANT SELECT ON employee TO user1;

This command grants permission to user1 to select data from the employee table.

• REVOKE: This command is used to revoke permissions from a user or role on a database object. For example:

REVOKE SELECT ON employee FROM user1;

This command revokes permission from user1 to select data from the employee table.

TCL Commands

Some of the common TCL commands are:

• COMMIT: This command is used to save all the changes made by DML commands in a transaction. For example:

BEGIN; -- start a transaction
UPDATE employee SET salary = salary * 1.1 WHERE id = 4; -- update salary
COMMIT; -- commit changes

This command commits all the changes made in the transaction to the database.

• ROLLBACK: This command is used to undo all the changes made by DML commands in a transaction. For example:

BEGIN; -- start a transaction
UPDATE employee SET salary = salary * 1.1 WHERE id = 4; -- update salary
ROLLBACK; -- rollback changes

This command rolls back all the changes made in the transaction and restores the original state of the database.

• SAVEPOINT: This command is used to create a point in a transaction that can be rolled back to later. For example:

BEGIN; -- start a transaction
UPDATE employee SET salary = salary * 1.1 WHERE id = 4; -- update salary
SAVEPOINT sp1; -- create savepoint sp1
UPDATE employee SET salary = salary * 1.2 WHERE id = 3; -- update salary
ROLLBACK TO sp1; -- rollback to savepoint sp1
COMMIT; -- commit changes

This command creates a savepoint called sp1 in the transaction and rolls back only the changes made after that point.

DQL Command

The only DQL command is:

• SELECT: This command is used to retrieve data from one or more tables in the database. For example:

SELECT name, email FROM employee WHERE salary > 6000;

This command selects the name and email columns from the employee table where the salary is greater than 6000.

Conclusion

In this blog post, we have learned about some of the most common and useful SQL commands and how to use them. SQL commands are categorized into five types: DDL, DML, DCL, TCL, and DQL. Each type of command has a specific purpose and syntax that we need to follow. SQL commands allow us to create, modify, manipulate, control, and query data in databases using simple and powerful statements.

Thanks for reading!