Structured Query Language

This is part 4 and last part of our Programming Paradigms series, click here to see the previous part if you missed it!

What’s the most common subset of a declarative style?

Structured Query Language (SQL). SQL is a domain-specific language designed for managing and manipulating relational databases. Those “relations” refer to the fact that in such databases data is organised in tables that are related to each other, e.g. by using primary keys. SQL style refers to the process of writing and executing queries and statements against the database. We can also write scripts in SQL. It’s widely used in database management systems (DBMS) such as MySQL, PostgreSQL, Oracle Database, SQL Server, and SQLite.

SQL programming involves constructing SQL statements that manipulate, retrieve, and manage data stored in a relational database. These statements are executed against the database using tools, interfaces, or programming languages that support SQL connectivity and integration. SQL’s declarative nature allows developers to focus on specifying the desired result rather than the step-by-step instructions to achieve it.

There’s a lot of programming languages that support SQL, including Java, Python, C++, and Go.

A little more about SQL

Each programming style has its key characteristics. In the case of SQL which by convention is a very domain-specific language, these are primarily its subsets that are based on the types of command types it offers:

Data Definition Language (DDL) - defines and manages the structure of the database.

This includes statements like CREATE, ALTER, and DROP, and it can be run against tables, indexes, views, constraints, and other database objects.

Data Manipulation Language (DML) - manipulates or modifies the data within the database tables.

This can be achieved by using statements like INSERT, UPDATE, and DELETE. INSERT adds data, UPDATE modifies it, and DELETE removes it.

Data Query Language (DQL) - or retrieving data by using queries.

The SELECT statement is fundamental here, and it can be used for not only getting data but also combined with other statements and parameters for filtering, sorting, joining, and aggregating data.

Data Control Language (DCL) - for database security and user access control over the its tables.

We can have many users who can have different privileges to data, even within one database. Keywords like GRANT and REVOKE come into play here for granting or revoking those privileges.

Transaction Control Language (TCL) - for managing transactions, which ensures data integrity.

Statements like COMMIT and ROLLBACK are used manage SQL transactions. We can have a set of statements that need to be executed, e.g. if something breaks in the middle of an operation, we'd' to bring back the previous state and roll back to the previous state.

SQL, with its various subsets, provides a comprehensive way to handle database management, security, and data integrity. In addition to that, we have a number of key concepts for SQL that also should be mentioned here:

Data Modeling - relational databases are about managing relations of data within.

Within a database, we have tables, and there are relations between them. This is achieved by using entity-relationship modeling and normalization techniques. During that, we design the structure of such databases by using schemas, and by using primary and foreign keys we establish relationships between tables.

Data Modeling - relational databases are about managing relations of data within.

Within a database, we have tables, and there are relations between them. This is achieved by using entity-relationship modeling and normalization techniques. During that, we design the structure of such databases by using schemas, and by using primary and foreign keys we establish relationships between tables.

Indexes and Performance Optimization - enhancing query performance by using indices.

A set of data can be manipulated to make it more efficient. For example, when we have a large set of data, we can optimize it so the SELECT statement runs faster. Indexes can be used for that - they are an additional set of data or identifiers that optimize query operations/execution plans.

Query Optimization - other tuning techniques besides indexes.

Queries can be analyzed, rewritten e.g. by using join tables to cimbine data, all of which optimizes the query performance.

Constraints and Data Integrity - database designer defines data validation and presentation rules.

Constraints ensure data integrity. Constraints and validation rules include things like primary keys, foreign keys, unique constraints, and NOT NULL constraints. We can also define explicitly what kind of data should be in each column, how long a string should be, and more.

Views and Stored Procedures - data encapsulation and reusability.

Views of data stored in tables can be created to optimize data retrieval. Stored procedures can be defined for performing predefined operations.

Joins and Relationships - used for getting data from multiple tables.

Usually, in one database, there are multiple tables. Joins are used to retrieve data based on relationships driven by keys. There are a few joins types like inner join, outer join, and self join - referencing different styles of relationships that are similar to what we mentioned already in the OOP blog post, like many-to-many relationships and one-to-many relationships. For example, one table can relate to many others, or many fields can relate to many fields.

Aggregation and Grouping - performing calculations and summarizing data.

When we have a lot of data, aggregation functions functions can be used to calculate or summarize it. Leading examples of such functions are functions like SUM for summing values, COUNT to count the rows or records, AVG for calculating the average, and MAX and MIN for getting the maximum and minimum values, respectively. All those results can be grouped by certain factors.

Subqueries and Nested Queries - performing calculations and summarizing data.

We already know we can use queries, but within those, we can also use subqueries or so-called nested queries. In short, we can embed a query within other queries for data retrieval. Subqueries can be very powerful for breaking down complex queries into manageable parts. They allow writing queries that reference the results of other queries, enabling sophisticated data manipulation and retrieval.

Data Transformation and Manipulation - SQL query within SQL query.

SQL data can be transformed and manipulated by using not only SQL statements, but also built-in string and date functions, case statements, and other conditional logic. Data conversion is also supported in SQL.

Data Import and Export - transferring data for various purposes.

Let's say we have a cluster with many relational database management systems and we want to ensure we don't lose data. There are ways to export data from one database and import it into another - there are fluent statements to achieve that, such as LOAD, INSERT, and EXPORT. Usually DBMSs have more tools and utilities that can be used for achieving that.

Even though SQL is a declarative paradigm, and maybe because of that, there are many options to manipulate, save, transform, and optimize data. On the other hand, it resembles the imperative paradigm, like creating, manipulating relationships, and aggregation. Let’s now see SQL in action!

SQL in action - code examples

Here we prepared a straightforward example of operations performed using SQL, let’s look at the whole thing first:

CREATE TABLE Customers (
  CustomerID INT PRIMARY KEY,
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  Email VARCHAR(100)
);

INSERT INTO Customers (CustomerID, FirstName, LastName, Email)
VALUES (1, 'John', 'Doe', 'john@example.com');

SELECT FirstName, LastName, Email
FROM Customers
WHERE LastName = 'Doe';

UPDATE Customers
SET Email = 'newemail@example.com'
WHERE CustomerID = 1;

DELETE FROM Customers
WHERE CustomerID = 1;

CREATE VIEW HighValueCustomers AS
SELECT FirstName, LastName
FROM Customers
WHERE CustomerID IN (
    SELECT CustomerID
    FROM Orders
    WHERE TotalAmount > 1000
);

EXECUTE sp_GetCustomerOrders @CustomerID = 1;

And now let’s try to understand it step-by-step. As you might already have noticed, it’s divided into subsets of SQL languages but also includes other concepts we already mentined.

Defining a Table - DDL

To create a table we need to use Data Definition Language (DDL):

CREATE TABLE Customers (
  CustomerID INT PRIMARY KEY,
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  Email VARCHAR(100)
);

Using keywords like CREATE, we manage data and explicitly say we want to create a table named Customers:

CREATE TABLE Customers (
  ...
);

We can have as many fields (or columns) in the table as we want. We talked about constraints earlier, and in the exmaple:

• CustomerID is an integer (INT) and a PRIMARY KEY, which can be used to create relations to other tables later on:

  CustomerID INT PRIMARY KEY,
  

• other fields like FirstName, LastName, and Email are VARCHAR and we can define their length by placing the selected value in brackets:

  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  Email VARCHAR(100)
  

But defining a table is just the start. We need to add data to it, right?

Creating Data in Table - DML

For creating data, we use DML (Data Manipulation Language). In this case, it’s the INSERT statement, and we define INTO which table (Customers) we want to insert data, specifying the columns:

INSERT INTO Customers (CustomerID, FirstName, LastName, Email)

…and provide the values - integer which represents customer number one, that will be John Doe and his email:

VALUES (1, 'John', 'Doe', 'john@example.com');

While having this record inserted, we can retrieve this data again using Data Query Language.

Retrieving Data from Table - DQL

In Data Query Language (DQL) the SELECT statement is the key feature, where we can define exactly what we want to retrieve from which table:

SELECT FirstName, LastName, Email
FROM Customers

To make the retrieved data even more constrained we can apply a filter, which is a WHERE clause:

WHERE LastName = 'Doe';

In this case, we want to get only the first name, last name, and email for the customer whose last name is Doe. No other data will be shown in the result.

Updating and Deleting Data in a Table - DML

Let’s say John Doe changes his email. To update this value in his record, we can use Data Manipulation Language (DML). The UPDATE statement together with SET is meant to be used for that. Let’s update the table customers by setting this particular field to a new value:

UPDATE Customers
SET Email = 'newemail@example.com'

But let’s be sure we’re updating only particular recorde there, we don’t want o update all the customers at once! Here again we can combine the above with the WHERE clause:

WHERE CustomerID = 1;

Let’s now imagine John Doe decided to resign from using our services. It’s sad news, but we need to somehow delete his record from our database. For this, we’ll use a DELETE statement, once more together with the WHERE clause:

DELETE FROM Customers
WHERE CustomerID = 1;

These are the basics of SQL subsets. But in our example, there are two other things that reflect a bit more advanced techniques.

Creating a View

Here’s an example of creating a view, which is a special case of a table only to represent combined and transformed data from already existing tables:

CREATE VIEW HighValueCustomers AS
SELECT FirstName, LastName
FROM Customers
WHERE CustomerID IN (
    SELECT CustomerID
    FROM Orders
    WHERE TotalAmount > 1000
);

Again, we use a statement that begins with CREATE, this time defining it as a view with its name and a keyword AS:

CREATE VIEW HighValueCustomers AS
...

We are combining the view creation with a typical SELECT statement FROM a chosen table. We can select all the data we want to see, but in the example we decided to see only the first name and last name:

SELECT FirstName, LastName
FROM Customers

And lastly, for the customers where customer ID is in, here we are using a subquery:

WHERE CustomerID IN (
    SELECT CustomerID
    FROM Orders
    WHERE TotalAmount > 1000
);

The above WHERE definition combines the outer SELECT statement with its inner SELECT statement. In short, we are selecting the first name and last name from the table where the customer ID is in a group of customers whose orders are more than one thousand.

Executing Stored Procedure

Stored procedures are kind of like functions. We can store all of such statements in memory as a stored procedure to speed up the process. We can then execute it with a simple command, for example, to get customer orders:

EXECUTE sp_GetCustomerOrders @CustomerID = 1;

For that, we use the EXECUTE statement on the stored procedure called sp_GetCustomerOrders. Of course, we have to pass any arguments to this procedure that it requires, in this case referencing CustomerID prepended with @ and the value. What’s going on inside is totally hidden; we don’t need to create complex statements just to get the data we want.

What SQL can be used for?

There’s a wide variety of problems that can be solved with a versatile SQL language, of which the most common are:

Data Retrieval and Reporting - SQL is commonly used to query and retrieve data from databases.

It allows to specify complex search criteria, filter data based on conditions, join multiple tables, aggregate data using functions like SUM, COUNT, AVG, etc., and sort the results. This enables users to extract the specific information needed for generating reports, performing analysis, or gaining insights from the database data.

Data Manipulation and Modification - manipulation operations within database tables.

Systems can be very complex, with users coming and going often. SQL supports data manipulation operations such as adding new records, modifying existing ones, and removing unnecessary data. This allows the user to maintain data integrity and quality and perform all necessary data modifications as per changing business requirements.

Database Schema Design and Management - designing and managing the structure of a database.

DDL statements help design and manage the structure of the database, creating tables, defining relationships using keys, and referencing keys in other tables. Constraints can be used to define table structures, which can be changed later Optimization techniques like indexes and views store predefined sets of data from multiple tables, enhancing performance and manageability.

Data Integrity and Constraints - mechanisms to enforce data integrity by defining constraints on tables.

User can specify rules and constraints to ensure data consistency and accuracy, such as enforcing unique values, primary and foreign key relationships, check constraints, and validation rules. Its also worth to mention triggers that automatically executes some other statements. Triggers enforce data integrity by using constraints on tables and specifying rules to ensure data consistency, which can be improved over time. Alll the above provide an additional layer of data quality control, also allowing businesses to adapt and evolve their data structures as needed.

Data Aggregation and Analysis - powerful functions and report generation to get more insights.

Powerful functions like `SUM`, `AVG`, `MAX`, and `MIN`, along with `GROUP BY` clauses, allow users to aggregate and analyze data. It can be used for generating summary reports, performing calculations on grouped data, obtaining statistical insights, and finally making data-driven decisions.

Data Migration and Integration - load, transform and extract database data.

Data migration and integration are also crucial. SQL plays a vital role in extracting, transforming, and loading data from multiple sources into a target database, ensuring data consistency and integrity during the process. SQL provides all the necessary tools and syntax to handle data transformation and merge data from different systems.

Database Administration and Security - maintaining and securing the database.

SQL provides robust mechanisms for data security - statements like GRANT and REVOKE privileges, defining roles and permissions, and creating and managing users with different privileges over the database ensues that only authorized users can access or modify the data. This is critical for protecting sensitive information and maintaining compliance with regulations.

Quick summary

SQL’s versatility in data querying capabilities, manipulation operations, and database management features makes it an indispensable tool for working with relational databases. It addresses a wide range of data-related challenges and is commonly used in many projects and systems operating everywhere. SQL is essential for almost every nowadays software development activity.

Other programming sub-paradigms

When discussing programming paradigms, we can categorize them into several types, including imperative, declarative, object-oriented, and SQL as part of the declarative paradigm. Beyond these, there are numerous sub-paradigms worth noting:

Imperative sub-paradigms

• The Structured Paradigm is often mentioned, characterized by nested loops and subroutines rather than goto statements, closely related to the imperative paradigm.
• The object-oriented paradigm is contrasted by the Procedural Paradigm, which is governed by procedure calls and goto statements, aligning it with the imperative paradigm.

Declarative sub-paradigms

• The Functional Programming, which relies on function calls and avoids assigning values to variables.
• Logic and Constraint Programming that are controlled by relations and inference rules.
• Dataflow paradigm, including the Flow-driven subset, is based on predefined communication channels and event-driven processes.

Apart from the above, we can often see:

• Functional-level paradigms, which avoid using variables entirely, akin to functional programming.
• Event-driven paradigm that features asynchronous actions managed by emitters and listeners.
• Aspect-oriented programming, which handles cross-cutting concerns transparently.
• Reflective programming, where programs manipulate their own elements.
• Array paradigms offer powerful operations, but also eliminating the need for loops.

Understanding these paradigms and their purposes helps in solving specific problems effectively. When faced with a particular problem, knowing the relevant paradigms can guide us to choose the most effective solution.

Programming Paradigms Summary

Paradigms are not meant to be mutually exclusive

Programming paradigms are interconnected and not mutually exclusive, allowing a single program to feature multiple paradigms. Most programming languages support more than one paradigm, enabling programmers to use the most suitable style for a given job. For example, a warehouse management application might use object-oriented programming (OOP) for the user interface, where objects interact and maintain state during runtime, demonstrating imperative characteristics. Data generated in this way can then be stored using SQL, which operates in a declarative style, managing long-term data storage and manipulation.

Combining paradigms, like OOP and SQL, exemplifies how different styles are integrated. The imperative style manages relationships and interactions in real-time, while the declarative style handles data storage and long-term management. This illustrates the practical blend of paradigms in software development.

Many paradigms, not without a purpose

Categorizing programming paradigms can be challenging due to the historical evolution of programming languages, each developed to address specific problems. Over time, engineers have created various paradigms to solve different issues more efficiently, leading to the diverse landscape we see today.

Understanding these paradigms is crucial for problem-solving in programming. While knowing all paradigms is unnecessary, focusing on the most common ones provides a solid foundation. As you encounter specific problems, exploring relevant paradigms and comparing their pros and cons helps in choosing the most effective approach.

For instance, while it’s possible to manage a warehouse entirely with SQL, combining it with a user interface built using OOP offers more flexibility and ease of maintenance. This highlights the importance of choosing the right tools and paradigms based on the business case and team expertise.

Explore and practice

In summary, understanding the core paradigms — declarative, imperative, object-oriented, and SQL — is essential for any programmer. These basics form the foundation for further exploration into other paradigms and programming concepts, such as data structures and subroutines. As part of continuous learning, expanding knowledge of paradigms and their applications is key to effective problem-solving in programming.

Live discussion

Want to see a live discussion on the topic? Check out our YouTube recording:

Our journey into programming paradigms ends

With this entry, about Structured Query Language, we are finishing the broader topic of programming paradigms. We have discussed the imperative and declarative paradigms, as well as their most common varieties such as object-oriented programming and SQL. Of course, there are many more (see Other programming sub-paradigms section), but for this (junior) level, our journey ends here.

Of course, our programming adventure does not end here - stay tuned because more topics will appear soon!