How to Write Basic SQL Queries for Data Retrieval and Manipulation

Table of Contents

1. What are SQL queries?

2. Types of SQL commands

• DDL Commands – Create, Drop, Alter, Truncate
• DML Commands – Insert, Update, Delete 
• DCL Commands – Grant, Revoke
• TCL Commands - Commit, Rollback, Savepoint
• DQL Commands – Select

3. Conclusion 

What are SQL queries? 

A SQL query is a request you send to a database to retrieve, update, or manipulate data. It's like asking the database a question and getting the specific information you need in return. For example, if you want to see all the customers in a database who live in a certain city, you’d write a SQL query to pull just that data. SQL queries are written in a specific syntax that the database understands, allowing you to interact with the data stored within it.

Types of SQL Commands

There are 5 types of SQL Commands:

1. DDL: Data Definition Language

2. DML: Data Manipulation Language

3. DCL: Data Control Language

4. TCL: Transaction Control Language

5. DQL: Data Query Language

While initially this might seem like jargon to you, let’s expand on each of them further 

A. Data Definition Language (DDL)

In a SQL database, data is stored in tables. DDL defines the structure of a table itself, rather than the data within it. Here are the DDL commands and their syntax

1. CREATE TABLE 

As the name suggests, this command creates an empty table in the database, with the columns defined in the syntax:

CREATE TABLE               table_name (column_1 datatype,

                         column_2 datatype,

                         column_3 datatype);

‍

2. ALTER TABLE 

This command adjusts the structure of the table by adding, removing or renaming columns or renaming the table itself

-- Add a column

ALTER TABLE table_name

ADD column_name datatype;

-- Remove a column

ALTER TABLE table_name

DROP COLUMN column_name;

-- Rename a column

ALTER TABLE table_name

CHANGE COLUMN old_name new_name;

--Renaming the table

ALTER TABLE old_table_name

RENAME TO new_table_name;

DROP TABLE – This command deletes a table entirely

DROP TABLE table_name;

‍

3. TRUNCATE – Not to be confused with  DROP TABLE, this command deletes all the entries (rows) in a table while retaining the structure of the table 

TRUNCATE TABLE table_name;

‍

B. Data Manipulation Language (DML)

DML commands are used to enter, modify or delete data in a table. Here are DML commands and their syntax: 

1. INSERT

INSERT Statements are used to add a new row to a table.

INSERT INTO table_name 

(column_1, column_2, column_3)

VALUES

(value_1, value_2, value_3);

‍

2. UPDATE

Update statements allow you to edit rows in a table.

UPDATE table_name 

SET column_name = value

WHERE condition;

3. DELETE

Delete statements remove rows from a table.

DELETE FROM table_name

WHERE some_column = value;

‍

NOTE: The WHERE clause in the UPDATE and DELETE statements is very important. Without it, every single record in the table will be edited to the same value or deleted.

‍

C. Data Control Language (DCL)

DCL commands are used solely by database administrators to control the level of access of different users of the database.

1. GRANT

GRANT command gives users access to a database.

GRANT SELECT, UPDATE ON My_TABLE TO FIRST_USER, SECOND_USER;

2. REVOKE

It is used to take back permissions from the user.

REVOKE SELECT, UPDATE ON My_TABLE TO FIRST_USER, SECOND_USER;

‍

D. Transaction Control Language (TCL)

In a SQL Server, commands can be set to auto-commit. This means that after each operation or transaction—like inserting, updating, or deleting data—the changes are immediately saved and cannot be undone. When auto-commit mode is not enabled, TCL commands are used to manually commit transactions.

1. COMMIT  - Saves all changes made 

           COMMIT;

2. ROLLBACK - Undoes all changes which haven’t yet been committed 

        ROLLBACK;

3. SAVEPOINT - Sets a point within a transaction to which you can later roll back if needed, without affecting the entire transaction.

SAVEPOINT savepoint_name;

‍

E. Data Query Language (DQL)

This category contains just a single command, but this command is the most widely used.

SELECT - The SELECT command is to get data from the database.

--Select a single column

SELECT column_name FROM table_name;

--Select multiple columns

SELECT column1, column2 FROM table_name;

--Select all columns of a table

SELECT * FROM table_name;

‍

CONCLUSION

This guide illustrates the basic SQL queries, but there’s a whole lot more – from clauses and functions to views, triggers and stored procedures. We have it all in our SQL course². By understanding how to retrieve and manipulate data with simple SQL commands, beginners can unlock powerful insights from their datasets. Whether you're exploring data, generating reports, or setting up databases, these commands will serve as a crucial building block as you progress. Utilizing platforms that allow you to practice SQL queries³ online can greatly enhance your learning experience.