SQL (Structured Query Language) is one of the most used and powerful tool that we utilised for managing and retrieving data from relational databases. Whether you’re a budding data enthusiast or a developer, understanding SQL basics is essential. In this post, I’ll cover the fundamental concepts with examples and even throw in some images to make it visually engaging.

Table of Content

What is SQL?

SQL, which stands for Structured Query Language, serves as a standardized language employed for storing, manipulating, and retrieving data within databases. It forms the foundation of numerous well-known database systems, including MySQL, SQL Server, Oracle, and others.

SQL: Type of Statements

Let’s explore the different types of SQL statements. SQL (Structured Query Language) provides several different types of SQL commands to interact with databases. Here are some of key ones:

1. DDL (Data Definition Language):
   • DDL deals with defining and managing the structure of the database schema.
   • These commands create, modify, and delete database objects (e.g., tables, indexes, views).
   • Common DDL commands include:
     • CREATE: Used to create database objects (e.g., tables, indexes, functions, views).
     • DROP: Deletes objects from the database.
     • ALTER: Modifies the structure of existing objects.
     • TRUNCATE: Removes all records from a table.
     • COMMENT: Adds comments to the data dictionary.
     • RENAME: Renames existing objects.
2. DQL (Data Query Language):
   • DQL is used for getting data from schema objects such as table and views.
   • The primary DQL command is the SELECT statement.
   • It retrieves data from tables based on specified conditions.
3. DML (Data Manipulation Language):
   • DML commands manage data within schema objects.
   • These are the commands which helps you to insert, update, or delete data.
   • Common DML commands include:
     • INSERT INTO: Adds new records to a table.
     • UPDATE: Modifies existing records.
     • DELETE FROM: Removes records from a table.
4. DCL (Data Control Language):
   • DCL controls access and permissions to data.
   • These commands grant or revoke privileges to users.
   • Common DCL commands include:
     • GRANT: Provides specific privileges to users.
     • REVOKE: Removes privileges from users.
5. TCL (Transaction Control Language):
   • TCL manages transactions within the database.
   • Transactions ensure data consistency and integrity.
   • Common TCL commands include:
     • COMMIT: Saves changes made during a transaction.
     • ROLLBACK: Reverts changes made during a transaction.
     • SAVEPOINT: Sets a point within a transaction.
     • SET TRANSACTION: Defines transaction properties.

Understanding Database

A database is a structured collection of data, organized in tables. Tables are segregated in Schema. Each table consists of rows (records) and columns (fields). For example, a “Students” table might have columns like “StudentID,” “FirstName,” “LastName,” and “GPA.”

SQL Syntax

Here are syntax for few of the SQL statements that you can explore

SELECT Statement

The SELECT statement is the most commonly used SQL command. It retrieves data from one or more tables. Here’s the basic syntax:

SELECT column1, column2, ... FROM your_table_name;

You can use various clauses like WHERE, ORDER BY, GROUP BY, and HAVING to filter, sort, and aggregate the results.

INSERT Statement

The INSERT statement is one of the DML statements helps to adds new rows to a table. Using it you can only add one record at a time:

INSERT INTO your_table_name (col1, col2, ...) VALUES (value1, value2, ...);

UPDATE Statement

The UPDATE statement is another type DML statements that is used to modifies existing data in a table:

UPDATE your_table_name SET col1 = value1, col2 = value2, ... WHERE condition;

DELETE Statement

The DELETE statement is another DML statement which is used to removes rows from a table:

DELETE FROM your_table_name WHERE condition;

Other Concepts

Other concepts that are required to enhance your understanding of SQL

Joins

Using joins, rows from two or more tables can be combined based on a shared column. Some common join types includes INNER JOIN also refers as just JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

Subqueries

A query nested inside another query is called a subquery. Subqueries can be utilized in conditions as well as with the SELECT, INSERT, UPDATE, and DELETE commands.

Aggregate Functions

Count, SUM, AVG, MAX, and MIN are just a few of the aggregate functions that SQL offers for calculating aggregation on multiple rows from tables.

Database Indexing

Indexes are data objects that store a subset of data from a table to speed up data retrieval. User can create indexes on one or more columns of a table.

This is just the tip of the iceberg when it comes to SQL. As you continue learning and practicing, you’ll explore more advanced concepts like views, stored procedures, triggers, transactions, and database normalization.

Remember, the best way to learn SQL is by practicing with real-world examples and datasets. Many online resources and tutorials provide sample databases to help you practice SQL queries. Mastering these SQL statements is crucial for effective database management. Enhance your skills by exploring tutorials and practice more!

Summary

SQL statements syntax summary with description

In this post we have covered some of the most used SQL statements includes DDL, DCL, DML, TCL and DQL with their syntax. You can use it as a tool for quick refresher, retain important syntax and concepts.

Learn more about other or related topics

• SQL Most Common Tricky Questions
• Oracle Interview Questions
• SQL Interview Questions for Beginner Level
• What is SQL? by AWS