What is SQL?

Structured Query Language, SQL for short, is a programming language for storing, retrieving, and manipulating data in database management systems such as Oracle, MySQL, Microsoft SQL Server, PostgreSQL, SQLite, or MariaDB[2].

SQL has evolved significantly since its creation in the 1970s by IBM researchers Raymond Boyce and Donald Chamberlin. Today it is widely used in software development, in fact, SQL was ranked by StackOverflow in 2024 as the third most admired and desired programming language.[3]

History

In the early 1970s, Edgar F. Codd proposed a new way to structure data, what is known as the relational model. Later IBM, started a project called System R, which aimed to create a prototype database system based on Codd’s relational model. During the development of that system, Donald D. Chamberlin and Raymond F. Boyce developed a structured query language called SEQUEL to interact with the System R; Patricia Selinger developed a cost-based optimizer that made relational databases more efficient; Raymond Lorie invented a compiler that could save database query plans for later use. Subsequently, Relational Software (now Oracle) introduced one of the first commercial implementations of SQL.[4]

Let’s delve into its fundamental concepts.

SQL Commands

SQL allows a wide range of operations, such as querying data, updating records, inserting new data, and deleting data. These operations are performed using specific commands, which can be categorized into 4 types:

• Data Definition Language (DDL)

• Data Manipulation Language (DML)

• Data Control Language (DCL)

• Transaction Control Language (TCL)

Data Definition Language (DDL)

DDL commands are used to create a database, modify its structure, and destroy database objects when no longer needed.

Data Manipulation Language (DML)

DML commands allow users to retrieve, add, modify, and delete data within a database.

Data Control Language (DCL)

DCL commands are used to manage access permissions and security settings. These commands are used to protect the database from being corrupted. The degree of protection depends on the implementation.

Transaction Control Language (TCL)

TCL commands help to manage database transactions, ensuring data integrity. These enable the control of changes made by DML statements and allow the grouping of statements into logical transactions.

Having reviewed the core SQL commands, let’s dive into key clauses and features that help you tackle common challenges and troubleshoot effectively.

Before moving forward, let’s define the structure of the database that we’ll use to demonstrate the scenarios.

Views

A view is a virtual table that displays data based on the result set of a SELECT query that runs on one or more tables. Views help simplify complex queries and provide an abstract layer over underlying tables, making data easier to work with.

Suppose you need to calculate the distances between the branches.