What Is SQL?

Hello there! In this blog post, I will introduce you to the SQL programming language.

We will discuss what it offers and why it’s crucial for any technical person to master this industry-standard database language. Finally, I will share my suggestions on how to learn SQL step-by-step.

A Primer on Relational Databases

In today’s world, we have different kinds of databases to choose from; they have emerged over time in response to growing requirements. Some sample types are relational databases for structured data, NoSQL databases for unstructured data, time-series databases for time-stamped data, and spatial databases for geographic data.

The list goes on. However, relational databases are still the most widely used database type in the industry. They power countless systems, such as ERPs, websites, mobile apps, and social media platforms. Some popular examples of relational databases are PostgreSQL, MySQL, Microsoft SQL Server, and Oracle Database.

A relational database is one specific type of database, that organizes data into tables with rows and columns, similar to spreadsheets. These tables are interrelated to avoid data duplication.

The typical language for relational databases is SQL.

Introduction to SQL

SQL, which stands for Structured Query Language, is the standard programming language of relational databases. It is a declarative language, which features commands to manage, manipulate, and query contents of relational database systems.

With SQL knowledge, you can cover any relational database task from basic table creation to complex data querying. Since relational databases are the most common type of databases, your skills will surely be useful in any industry in today’s data-driven world.

You can run hand-written SQL code directly against the database, or you can dynamically generate SQL code using another language (like Python, Java, C#, etc.) to add database support to your application.

English-like syntax of SQL makes it a language approachable by anyone with decent computer literacy. Check the following SQL statement:

SELECT emp_name, department, hire_date

FROM employee

WHERE salary > 60000;

This statement is almost readable as pure English. It tells the database engine to find employees with a salary higher than 60K, and return their names, departments & hire dates. This statement could return a result set as demonstrated below.

Easy, right?

SQL’s Sublanguages

The basic example above featured merely a simple query. However, SQL is much more powerful than that. It is built out of several sublanguages, which are similar in syntax but serve a different set of purposes. Let’s briefly go over those sublanguages.

DDL

DDL stands for “data definition language.” It is used to create, modify, or delete the core structure of the database. It features commands that can be used to create schemas (similar to Excel files), tables (similar to Excel sheets), and views (similar to Excel pivot tables), among others. Those structures would later be used to store data. Sample DDL keywords include:

• CREATE TABLE
• ALTER TABLE
• DROP TABLE

DML

DML stands for “data manipulation language.” It is used to create, modify, or delete data entries in tables. Using DML commands, we can insert new customers, modify existing orders, or delete old archive entries throughout the database. Sample DML keywords include:

• INSERT
• UPDATE
• DELETE

TCL

TCL stands for “transaction control language.” Although DML is perfectly usable on its own, we sometimes need to run complex statements that contain multiple subsequent DML commands. TCL is a support mechanism that can be used to ensure data consistency: Either all commands finish successfully, or neither command is realized at all. Sample TCL keywords include:

• COMMIT
• ROLLBACK
• SAVEPOINT

DQL

DQL stands for “data query language” and it is the most widely used SQL sublanguage – to a degree that many folks use the terms DQL and SQL interchangeably. DQL commands enable us to query data from joint database tables and build up result sets–not unlike an Excel pivot table. The SELECT command we saw above was part of DQL. Sample DQL keywords include:

• SELECT
• JOIN
• WHERE

DCL

DCL stands for “data control language.” It is used for user and authorization management, in which we can define who can read/write what. Sample DCL keywords include:

• GRANT
• REVOKE

The totality of these sublanguages builds up the language we call SQL. But don’t let that statement scare you–you don’t have to learn all those sublanguages to enjoy SQL. You can learn about the sublanguage you need when you need it and start applying it then!

For example, many analysts know DQL alone and write DQL statements to build up datasets for their reports. They might never need to learn about other sublanguages.

Advanced SQL Features

Most programming languages have a globally accepted syntax, which is static no matter in which environment you are coding. That’s not necessarily the case with SQL though.

SQL has a predefined set of standards called ANSI SQL–which stands for “American National Standards Institute Structured Query Language.” Although database products are expected to support ANSI SQL, some deviate from it and add new features on top–with special syntax elements unsupported by other databases. Therefore, the portability of SQL code between database systems is not always a simple operation of copying and pasting code. Some adaptation might be required.

That shouldn’t be a big concern though; the adaptation rate is typically low. Once you learn SQL on a popular database, you will mostly be able to apply your knowledge to other database products as well.

How to Learn SQL

The best way to learn SQL is to write SQL!

First, you need a playground database to write SQL code against. Luckily, many vendors offer free editions of their database products, which can be installed locally for educational purposes. You can run through popular alternatives and pick the most suitable one for your computer and operating system.

Or, if you have access to a remote database, that’s equally fine. Just make sure that you are not experimenting on a production system! Here, you could be one wrong statement away from destroying the dataset.

Once you have access to a playground database, you can start getting familiar with SQL commands and run them against the database.

If you are an aspiring programmer in need of a holistic grasp of SQL, you can go through SQL sublanguages in their logical order–which I’ve highlighted above. However, if you only want to focus on an individual sublanguage, such as DQL, that’s also perfectly fine. You can focus on the commands of the sublanguage you want and master them over examples.

SQL: The Practical Guide

I’m happy to share with you my book SQL: The Practical Guide, which can be used as a trusted guide for your SQL journey. It is available on the Rheinwerk Computing web shop here.

This book starts with an introduction to relational databases and guides you through the steps to set up a local playground database. Then, each SQL sublanguage is explained and taught with practical examples. DDL, DML, TCL, DQL, and DCL are all covered. It also offers valuable bonus content, such as entity-relationship diagrams and critical database tips and tricks.

The realistic nature of the examples in the book enables you to grasp the logic and purpose of SQL features in such a way that you will be able to apply them to your daily requirements quickly. Instead of sterile and unrealistic cartoon examples, case studies are based on real-world scenarios, which you are likely to encounter at some point during your development journey. Cases were partially simplified to keep the focus on SQL, but not over-simplified.

I encourage you to check out SQL: The Practical Guide and wish you the best for your SQL journey! And if you like the book, make sure to leave a review online so others can see how it benefited you.