Structured Query Language (SQL), Explained
· 83 minutes Read more · Published by NolwennSQL stands for Structured Query Language. It is a language used to store, manage, and retrieve information from relational databases.
In other words, SQL allows you to write instructions to a database. You can use it to store new data, update existing data, delete data, search for specific information, and retrieve results.
SQL was developed in the 1970s, based on the relational data model. Oracle was one of the first vendors to offer a commercial SQL-based relational database management system.
What is an SQL system?
SQL is used by relational database management systems, also called RDBMS. An RDBMS is software that stores data in relational databases and lets users interact with that data using SQL.
Examples of RDBMS include:
PostgreSQL
MySQL
Oracle Database
Microsoft SQL Server
Microsoft Access
SQLite
These systems can be slightly different from each other, but they all share the same general idea: they store structured data in tables and allow users to query that data with SQL.
SQL tables
A table is one of the basic elements of a relational database. It consists of rows and columns.
A row represents one record. For example, one customer, one order, or one product.
A column represents one type of information. For example, a customer name, an email address, a date, or a price.
Here is a simple example of a customers table:
Database engineers can create relationships between multiple tables to organize data better and avoid repeating the same information everywhere.
For example, instead of storing all customer information inside every order, we can have one customers table and one orders table. The two tables can then be connected using keys.
SQL statements
SQL statements, also called SQL queries, are valid instructions that a relational database management system can understand.
A SQL statement is built using different SQL elements, such as keywords, table names, column names, conditions, and values.
For example:
SELECT *
FROM customers;
This means: “Show me all columns from the customers table.”
SQL statements must follow correct syntax. If the syntax is wrong, the database will not understand the instruction.
Stored procedures
A stored procedure is a saved collection of one or more SQL statements. Instead of writing the same SQL logic again and again, you can save it inside the database and call it when needed.
For example, a company could create a stored procedure that calculates monthly sales, updates a reporting table, or checks customer activity.
How does SQL work?
When you write and run a SQL query, the database system does several things behind the scenes.
The exact process depends on the database system, but the general idea looks like this:
1. The parser checks the query
First, the database reads the SQL statement. The parser breaks the query into smaller parts and checks whether the syntax is correct.
For example, it checks whether the SQL keywords are used properly, whether the table exists, and whether the columns mentioned in the query are valid. It may also check permissions.
2. The query processor creates a plan
Then, the query processor decides how to execute the query. This is important because there can be several ways to get the same result.
For example, if you ask for all customers in Italy, the database has to decide how to find those rows efficiently. It might scan the full table, or it might use an index if one exists. The database creates an execution plan, which is basically its strategy for answering the query.
3. The storage engine reads or writes the data
Finally, the storage engine interacts with the actual data. It reads data from storage, writes new data, updates existing data, or deletes data depending on the SQL statement.
Once the work is done, the database returns the result to the application or user.
For a SELECT query, the result might be a table of rows.
For an UPDATE or DELETE query, the result might simply confirm that the operation was completed.
What are SQL commands?
SQL commands are specific instructions used to work with data and database structures. They are often grouped into categories:
Data Definition Language, or DDL
DDL commands are used to define or change the structure of a database.
For example, you can create, modify, or delete tables.
Example:
CREATE TABLE customers (
customer_id INT,
name VARCHAR(100),
email VARCHAR(100)
);
This creates a new table called customers. Other DDL commands include ALTER and DROP.
Data Query Language, or DQL
DQL commands are used to retrieve data from a database. The main example is SELECT.
Example:
SELECT name, email
FROM customers;
This retrieves the name and email columns from the customers table.
Data Manipulation Language, or DML
DML commands are used to add, update, or delete data inside tables. Examples include INSERT, UPDATE, and DELETE.
Example:
INSERT INTO customers (customer_id, name, email)
VALUES (1, 'Maya', 'maya@example.com');
This adds a new customer to the customers table.
Data Control Language, or DCL
DCL commands are used to manage access and permissions.
Examples include GRANT and REVOKE.
Example:
GRANT SELECT ON customers TO analyst_user;
This gives a user permission to read data from the customers table.
Transaction Control Language, or TCL
TCL commands are used to manage transactions. A transaction is a group of database operations that should be treated as one unit.
For example, when transferring money between two bank accounts, you do not want only half of the operation to happen. You want both updates to succeed, or both to fail.
Example:
ROLLBACK;
This cancels changes made during the current transaction.
Other TCL commands include COMMIT and SAVEPOINT.
Other Concepts
SQL dialects
SQL is a standard language, but not every database system uses it in exactly the same way. Different database systems have their own versions of SQL. These versions are called SQL dialects.
For example, PostgreSQL, MySQL, Oracle Database, and Microsoft SQL Server all use SQL, but they may have small differences in syntax, functions, data types, and features.
A simple query like this will work in many SQL systems:
SELECT name, email
FROM customers;
But more specific operations may look different depending on the database. For example, limiting the number of results can vary.
In PostgreSQL and MySQL, you can write:
SELECT *
FROM customers
LIMIT 10;
In Microsoft SQL Server, you might write:
SELECT TOP 10 *
FROM customers;
Indexes
An index helps a database find data faster.
Think of an index in a book. Without an index, you might need to read many pages to find a specific topic. With an index, you can jump more quickly to the right page.
A database index works in a similar way. It helps the database locate rows without scanning the entire table every time.
Joins
A join allows you to combine data from multiple tables. In relational databases, data is often separated into different tables to avoid repetition. For example, you might have one table for customers and another table for orders.
The customers table could look like this:
And the orders table could look like this:
If we want to know which customer made which order, we need to combine the two tables. That is where a join comes in.
SELECT customers.name, orders.order_id, orders.amount
FROM customers
JOIN orders
ON customers.customer_id = orders.customer_id;
This query connects the two tables using the customer_id column. The result would look like this:
This makes the data much easier to understand. Instead of only seeing a customer ID in the orders table, we can see the customer name too.
There are different types of joins.
An INNER JOIN returns only the rows where there is a match in both tables.
A LEFT JOIN returns all rows from the first table, even if there is no matching row in the second table.
Conclusion
SQL is one of the most important languages in data management.
It allows users to communicate with relational databases using clear instructions. With SQL, you can create tables, insert data, update records, delete information, manage permissions, and retrieve exactly the data you need.