What is SQL?

Key takeaways

SQL is used to store, retrieve, and manipulate data in relational databases. SQL helps manage data relationships using schemas in RDBMS.
SQL allows querying data effectively using commands like SELECT, enabling data analysis and retrieval from complex datasets.
Primary SQL operations are divided into DDL (CREATE, ALTER, DROP), DML (INSERT, UPDATE, DELETE, SELECT), and DCL (GRANT, REVOKE).

What is Structured Query Language and RDBMS?

Structured Query Language (SQL) is a standard data-oriented language that is primarily used to store, retrieve, and manipulate data using simple code snippets (called queries) in a relational database management system (RDBMS).

An RDBMS organizes data into tables containing rows and columns, with relations between the different entities and variables. The database schema defines these relations, specifying not only the relation between various entities but also the organization of data associated with those entities in the database.

Using SQL, we can create and modify databases, manage data, and ensure database integrity with primary and foreign key constraints.

Get a hands-on practice with SQL operators with the project Build a CRUD Application Using Golang and React.

Components of SQL

SQL consists of several key components that work together to manage and manipulate data in a relational database effectively:

Tables: The core structure of an RDBMS are tables. They organize data into rows (records) and columns (fields). Each table represents an entity, and columns represent its attributes, such as an Employees table with columns like EmployeeID, Name, and Salary.
Indexes: Indexes are used to improve query performance by allowing faster data retrieval. They function like a “table of contents”, helping locate specific rows in a table without scanning the entire dataset. For example, adding an index to the EmployeeID column ensures quick searches for employees by their ID.
Constraints: Constraints enforce rules to ensure the accuracy and integrity of data within a database. Common constraints include primary keys, which uniquely identify rows; foreign keys, which establish relationships between tables; unique constraints, which prevent duplicate values; check constraints, which restrict the range or format of values; and not-null constraints, which ensure that certain columns cannot have empty values. For example, defining EmployeeID as a primary key ensures uniqueness in the Employees table.
Views: Views are virtual tables that display specific data from one or more tables based on a query. They simplify complex queries and improve security by restricting access to sensitive data. For instance, a view called HR_View might display only the Name and Department columns from the Employees table.
Stored procedures: Stored procedures are reusable SQL code blocks designed to perform specific tasks. They help reduce redundancy and improve efficiency by encapsulating frequently used logic. For example, a stored procedure could calculate employee bonuses based on performance metrics.
Triggers: Triggers are automated actions that execute in response to specific events, such as INSERT, UPDATE, or DELETE operations. They are commonly used to enforce business rules or maintain audit logs. For instance, a trigger could automatically update an AuditLog table whenever a salary is updated.
Schemas: A schema defines the logical structure and organization of database objects, such as tables, views, and indexes. Schemas help group and manage related objects, particularly in large databases. For example, a Finance schema might contain tables like Transactions and Budgets, organizing all finance-related data.
Transactions: Transactions ensure that a series of operations are executed as a single, atomic unit, maintaining data consistency even in the event of failures. They adhere to the ACID properties (Atomicity, Consistency, Isolation, Durability). For example, a money transfer operation updates two accounts, and a transaction ensures that either both updates succeed or neither occurs.
Functions: Functions perform calculations or transformations on data, including built-in operations like SUM, AVG, and COUNT, as well as string, date, and mathematical functions. For example, the CONCAT function can be used to combine a first name and last name into a full name.

By understanding these components, you can utilize SQL more effectively to design, query, and maintain relational databases.

Characteristics of SQL

SQL stands out for its unique characteristics that make it widely adopted in database management:

Declarative nature: SQL allows users to define “what” they want to do with data without specifying "how" it is done, simplifying complex operations.
Standardized: SQL adheres to international standards (e.g., ANSI/ISO), ensuring consistency across various RDBMS platforms.
Flexible: It supports complex queries, nested queries, and joins to manage large datasets effectively.
Scalable: SQL databases efficiently handle small—and large-scale data, making them suitable for enterprises and startups alike.
Integration-friendly: SQL can be seamlessly integrated with programming languages like Python, Java, and C#.

SQL commands

SQL commands are grouped into three main categories—DDL, DML, and DCL:

Data Definition Language (DDL) commands affect the schema/structure of the database and do not usually interact with the data inside. These commands define and manage the structure of databases and tables. Commonly used DDL in SQL querying are:
1. CREATE: Creates new databases and tables.
2. ALTER: Modifies the structure of existing tables or databases.
3. DROP: Deletes tables or entire databases.
Data Manipulation Language (DML) operations involve data retrieval and manipulation, allowing users to interact with and modify the data. These commands handle the manipulation of data stored within the tables. Commonly used DML in SQL querying are:
1. INSERT INTO: Adds new data into tables.
2. UPDATE: Modifies existing data.
3. DELETE: Removes records from a table.
4. SELECT: Retrieves data from one or more tables.
Data Control Language (DCL) commands control access to data in the database, defining permissions for users or roles. Commonly used DCL commands are:
1. GRANT: Gives specific privileges to a user or role.
2. REVOKE: Removes specific privileges from a user or role.

Practice building an application with SQL in an actual project by attempting the project Create a Chat Application using Angular, Flask, and Socket.IO.

Code example

Now let’s look at an example of how to use the DDL, DML, and DCL commands in a real-world example. We will create a small employee management system to demonstrate CREATE, ALTER, INSERT, SELECT, UPDATE, DELETE, GRANT, and REVOKE commands.

-- Step 1: Create a database
CREATE DATABASE CompanyDB;
-- Step 2: Use the created database
USE CompanyDB;
-- Step 3: Create a table for employees
CREATE TABLE Employees (
  EmployeeID INT PRIMARY KEY,
  FirstName VARCHAR(100),
  LastName VARCHAR(100),
  Position VARCHAR(50),
  Salary DECIMAL(10, 2),
  Department VARCHAR(50)
);
-- Step 4: Insert employee records into the Employees table
INSERT INTO Employees (EmployeeID, FirstName, LastName, Position, Salary, Department) VALUES
(1, 'Alice', 'Johnson', 'Manager', 75000.00, 'HR'),
(2, 'Bob', 'Smith', 'Developer', 85000.00, 'IT');
-- Step 5: Retrieve all employee records
SELECT * FROM Employees;
-- Step 6: Update an employee's salary (for EmployeeID = 2)
UPDATE Employees
SET Salary = 95000.00
WHERE EmployeeID = 2;
-- Verify the update
SELECT * FROM Employees;
-- Step 7: Delete a record (EmployeeID = 1)
DELETE FROM Employees
WHERE EmployeeID = 1;
-- Verify the deletion
SELECT * FROM Employees;
-- Step 8: Create a user for database access
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password123';
-- Step 9: Grant permissions to the user
GRANT SELECT, INSERT ON CompanyDB.Employees TO 'user1'@'localhost';
-- Step 10: Revoke a permission from the user
REVOKE INSERT ON CompanyDB.Employees FROM 'user1'@'localhost';
-- Step 11: Drop the Employees table
DROP TABLE Employees;
-- Verify the table no longer exists
SHOW TABLES;

Explanation

Line 2: We create a new database named CompanyDB using the CREATE command.
Line 5: We switch to the CompanyDB database to perform subsequent operations.
Lines 8–15: We create a table named Employees with columns for employee details, including EmployeeID, FirstName, LastName, Position, Salary, and Department.
Lines 18–20: We insert two employee records into the Employees table.
Line 23: We retrieve and display all records from the Employees table to verify the insertion.
Lines 26–28: We update the salary of the employee with EmployeeID = 2 from 85000 to 95000.
Lines 31: We display all records to confirm that the salary update was successful.
Lines 34–35: We delete the record of the employee with EmployeeID = 1.
Line 38: We display all records to verify that the deletion was successful.
Line 41: We create a new user before assigning privileges.
Line 44: We give user1 permission to select and insert data into the Employees table.
Line 47: We remove the INSERT permission from user1.
Line 50: We remove the Employees table from the database.
Line 53: We display all tables in the current database to confirm that the Employees table has been dropped.

Try out the linked project to get real-world SQL related experience: Build a Python Airline Reservation System.

Query execution order

Understanding the SQL query execution order is crucial for writing optimized queries. SQL executes queries in the following logical sequence, though it may vary slightly for specific RDBMS optimizations:

FROM: Identifies the source table(s) for the query.
JOIN: Combines rows from multiple tables based on related columns.
WHERE: Filters rows that meet the specified condition(s).
GROUP BY: Groups rows based on column values.
HAVING: Filters groups based on aggregate conditions.
SELECT: Retrieves specific columns or computed values.
ORDER BY: Sorts the result set in ascending or descending order.
LIMIT: Restricts the number of rows returned.

Example

-- Step 1: Create a database
CREATE DATABASE CompanyDB;
-- Step 2: Use the created database
USE CompanyDB;
-- Step 3: Create a table for employees
CREATE TABLE Employees (
  EmployeeID INT PRIMARY KEY,
  FirstName VARCHAR(100),
  LastName VARCHAR(100),
  Position VARCHAR(50),
  Salary DECIMAL(10, 2),
  Department VARCHAR(50)
);
-- Step 4: Insert employee records into the Employees table
INSERT INTO Employees (EmployeeID, FirstName, LastName, Position, Salary, Department) VALUES
(1, 'Alice', 'Johnson', 'Manager', 75000.00, 'HR'),
(2, 'Bob', 'Smith', 'Developer', 85000.00, 'IT'),
(3, 'Alice', 'Williams', 'Developer', 78000.00, 'IT'),
(4, 'Alice', 'Davis', 'Tester', 72000.00, 'IT'),
(5, 'Bob', 'Miller', 'Developer', 90000.00, 'IT'),
(6, 'Bob', 'Moore', 'Manager', 95000.00, 'IT'),
(7, 'Eve', 'Parker', 'Developer', 80000.00, 'IT'),
-- Step 5: Retrieve employee records
SELECT FirstName, COUNT(*)
FROM Employees
WHERE Department = 'IT'
GROUP BY FirstName
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC LIMIT 5;

In this example, the database first filters records (WHERE), groups them (GROUP BY), applies aggregate conditions (HAVING), retrieves the desired columns (SELECT), sorts the results (ORDER BY), and finally limits the output to 5 rows (LIMIT).

Rules and best practices for writing SQL queries

Writing clean and efficient SQL queries is important for both performance and readability. Here are some simple rules and best practices to follow when working with SQL:

Use meaningful names: Always use descriptive and meaningful names for your tables and columns. For example, instead of using generic names like table1 or column1, use names that clearly describe the data, such as Employees for a table or EmployeeID for a column.
Avoid using SELECT * (Select only what you need): While SELECT * is convenient, it’s not the most efficient way to write queries. Instead, specify the columns you need. This reduces the amount of data transferred and speeds up your query.
Use indentation and formatting for readability: SQL queries can get complex, especially when dealing with joins and subqueries. Using proper indentation and line breaks makes your code easier to read and maintain. Here’s an example:

-- Create a database
CREATE DATABASE CompanyDB;
-- Use the created database
USE CompanyDB;
-- Create a table for employees
CREATE TABLE Employees (
  EmployeeID INT PRIMARY KEY,
  FirstName VARCHAR(100),
  LastName VARCHAR(100),
  Position VARCHAR(50),
  Salary DECIMAL(10, 2),
  Department VARCHAR(50)
);
-- Insert employee records into the Employees table
INSERT INTO Employees (EmployeeID, FirstName, LastName, Position, Salary, Department) VALUES
(1, 'Alice', 'Johnson', 'Manager', 75000.00, 'HR'),
(2, 'Bob', 'Smith', 'Developer', 85000.00, 'IT'),
(3, 'Alice', 'Williams', 'Developer', 78000.00, 'IT'),
(4, 'Bob', 'Miller', 'Developer', 90000.00, 'HR'),
(5, 'Eve', 'Parker', 'Developer', 80000.00, 'HR');
-- Retrieve employee records
SELECT FirstName, LastName
FROM Employees
WHERE Department = 'HR'
ORDER BY LastName;

-- Create a database
CREATE DATABASE CompanyDB;
-- Use the created database
USE CompanyDB;
-- Create a table for employees
CREATE TABLE Employees (
  EmployeeID INT PRIMARY KEY,
  FirstName VARCHAR(100),
  LastName VARCHAR(100),
  Position VARCHAR(50),
  Salary DECIMAL(10, 2),
  Department VARCHAR(50)
);
-- Insert employee records into the Employees table
INSERT INTO Employees (EmployeeID, FirstName, LastName, Position, Salary, Department) VALUES
(1, 'Alice', 'Johnson', 'Manager', 75000.00, 'HR'),
(2, 'Bob', 'Smith', 'Developer', 85000.00, 'IT'),
(3, 'Alice', 'Williams', 'Developer', 78000.00, 'IT'),
(4, 'Bob', 'Miller', 'Developer', 90000.00, 'HR'),
(5, 'Eve', 'Parker', 'Developer', 80000.00, 'HR');
-- Retrieve employee records
SELECT e.FirstName, e.LastName
FROM Employees AS e
WHERE e.Department = 'HR';

Avoid using reserved keywords: SQL has reserved keywords, such as SELECT, WHERE, INSERT, etc. It’s best to avoid using these keywords as column or table names to prevent confusion and errors. If you must use them, enclose the name in quotes or backticks, depending on your database.
Be mindful of case sensitivity: SQL is generally case-insensitive for keywords (e.g., SELECT is the same as select), but column and table names can be case-sensitive depending on the database. Stick to a consistent case (usually uppercase for SQL keywords and lowercase for table/column names) to avoid errors.
Comment your code: Use comments to explain complex queries or logic, especially if others will read or maintain your code. Comments can be added using -- for single-line comments or /* */ for multi-line comments.
Use joins wisely: When combining data from multiple tables, use joins. Be sure to specify the correct join type (INNER JOIN, LEFT JOIN, etc.) based on your needs, and always include the join condition to avoid returning incorrect results.
Optimize your queries: For large datasets, it’s important to optimize your queries. Use indexes on frequently queried columns, avoid complex subqueries when possible, and try to filter data early in the query with a WHERE clause.
Test queries on small data first
Before running complex queries on large tables, test them on smaller datasets to ensure they return the expected results and perform efficiently.

By following these best practices, you can ensure that your SQL queries are not only effective but also easier to understand, debug, and maintain.

Benefits and limitations of SQL

SQL is a widely used method for storing and managing data, especially in the context of web applications. Below, we explore some of its key benefits and limitations.

Benefits

Ease of use: Its simple and declarative syntax makes it beginner-friendly.
Data integrity: Built-in constraints and relationships ensure accurate and consistent data.
Complex querying: SQL supports advanced operations like joins, subqueries, and aggregations.
Widespread adoption: Almost every relational database, such as MySQL, PostgreSQL, and SQL Server, uses SQL.

Limitations

Limited for unstructured data: SQL struggles with handling unstructured or semi-structured data like JSON and multimedia files.
Scaling challenges: Horizontal scaling (distributing data across servers) is harder in traditional SQL databases compared to NoSQL.
Vendor-specific extensions: Despite being standardized, some RDBMS vendors have proprietary extensions that may reduce cross-platform compatibility.

SQL is a powerful tool for working with data in relational databases. It helps organize, manage, and retrieve data efficiently using tables, indexes, and constraints. In the real world, SQL is used in various applications, from managing customer data in e-commerce websites to tracking inventory in warehouses or handling financial transactions in banking systems. By building a solid understanding of SQL, you’ll be equipped to create powerful data-driven applications that are both reliable and effective.

Frequently asked questions

Haven’t found what you were looking for? Contact Us

How does SQL ensure data integrity?

SQL ensure data integrity via primary keys, foreign keys, and unique constraints, which prevent duplicate or invalid entries.

Can SQL queries be optimized for performance?

Yes, SQL queries can be optimized using indexes, query tuning techniques, and efficient schema designs to improve database performance.

What is the difference between SQL and NoSQL?

SQL databases are structured and relational, making them ideal for storing data with clear relationships, such as customer details, transaction records, or inventory management. NoSQL databases, on the other hand, are non-relational and better suited for unstructured or large-scale distributed data systems, such as social media feeds, IoT data, or real-time analytics.

Check out our detailed blog SQL vs NoSQL: Choosing a database for your next project.

Free Resources

Learn in-demand tech skills in half the time

PRODUCTS

Mock Interview

New

Courses

Skill Paths

Projects

Assessments