How to learn SQL with examples

SQL can be used to create databases and to query data from existing databases. The language is part of web developers’, data analysts’, and researchers’ standard repertoire. Compared to other programming languages, SQL is pretty unique. We’ll show how the language is structured and how it works.

$1 Domain Names

Register great TLDs for less than $1 for the first year.

Why wait? Grab your favorite domain name today!

Matching email
SSL certificate
24/7/365 support

What is SQL?

SQL stands for structured query language. Developed in the mid-1970s, SQL has become the standard programming language for database applications. SQL goes far beyond the queries that give it its name: the language forms the predominant interface for interaction with relational data or relational database management systems.

To understand SQL, you need to know that the language is based on “relational algebra”, a mathematical theory for structuring data and calculating query results. Many of SQL’s special features are derived from this mathematical basis.

SQL as an interface for controlling RDBMS

SQL is used as an interface to communicate with relational database management systems (RDBMS). Either the SQL code is entered and executed by humans via a text-based interface or integrated into API accesses. Furthermore, graphical user interfaces translate user interactions into SQL code. In either case, SQL code is executed by the RDBMS. A RDBMS consists of several components:

  1. Storage or database engine — manages data on a technical level.
  2. Programming interface(s) API — enables programmatic control of the RDBMS and provides data access.
  3. User interfaces (GUI/CLI) — allow human control of the RDBMS and data access.
  4. The actual database(s) — organize data on logical level.

A RDBMS includes a hierarchy of storage layers, from the database server to the value stored in a field. SQL contains commands to operate on all of these. Here’s a hierarchical overview of RDBMS objects:

RDBMS object Includes
Server Databases
Database Tables
Table Data sets
Data set Fields
Field Typed value

SQL is a domain-specific and declarative language

Compared to most established programming languages, SQL is special. Not only was the language created before the internet age, but it’s also a domain-specific language (DSL). This is the opposite of general-purpose languages, which are suitable in many different scenarios. Well-known examples of the general-purpose languages include C, C++, C#, Java, JavaScript, Python, PHP and others.

Among modern programing languages, CSS is the only other domain-specific language. Interestingly, both SQL and CSS are declarative programming languages. This is where the programmer describes a desired result, and the system that executes the code ensures that this result is achieved. This contrasts the widespread imperative programming, where individual steps to achieve the goals are explicitly defined in the code.

The language scope of SQL is defined in various ISO/IEC standards. Dialects of the different SQL implementations from major vendors deviate from this. That’s why MySQL/MariaDB, PostgreSQL, Oracle SQL and Microsoft SQL Server each have their own commands or command variants.

Where is SQL used?

SQL can be used to control all aspects of an RDBMS. Although SQL is generally referred to as one language, its functionality spans at least five named sub-languages. Each of the five languages includes its own SQL commands. Here are some examples:

SQL sub-language Command Example
Data Definition Language (DDL) Commands for defining the database schema: creating, modifying and deleting database tables; defining primary keys, foreign keys and constraints CREATE TABLE, DROP TABLE
Data Manipulation Language (DML) Data manipulation commands: modify, paste and delete records INSERT, UPDATE
Data Query Language (DQL) Commands to query and prepare data SELECT
Data Control Language (DCL) Commands for rights management GRANT, REVOKE
Transaction Control Language (TCL) Commands for transactional control COMMIT, ROLLBACK

Learn SQL syntax using examples

In general, syntax is the “notation” of a programming language. The syntax determines which types of basic code constructs there are and how they can be linked together. Understanding the syntax is a fundamental requirement for reading and writing code in the respective language. Before we look at SQL syntax in detail, let’s first look at how SQL code is executed.

Tip

Easily learn MySQL with our MySQL tutorial.

How is SQL code executed?

SQL code exists as source code in text files. The code is only given life by a suitable execution environment. The source code is read by a SQL interpreter and converted into actions of an RDBMS. There are two basic approaches here:

  1. Execute SQL code interactively

In this approach, SQL code is entered or copied directly into a text window. The SQL code is executed, and the result is displayed. You can adjust the code and execute it again. The quick sequence of code manipulation and displayed result makes this approach best suited for learning and creating complex queries.

  1. Execute SQL code as script

In this approach, an entire source code file containing SQL code is executed line by line. If necessary, feedback is only sent to the user at the end of the execution. This approach is best suited for automating processes and for importing MySQL database backups with MySQL dump.

Interface Description Examples
Command-line interface (CLI) Text-based interface; SQL code is entered and executed, result displayed in text mysql, psql, mysqlsh
Graphical user interface (GUI) SQL code is entered in text window and/or generated in response to user interaction; SQL code is executed, result displayed as tables phpMyAdmin, MySQL Workbench, HeidiSQL
Application programming interface (API) Allows direct communication with an RDBMS; SQL code is included and executed as a string in code of the programming language; results are available as data structures for further use PHP Data Objects (PDO), Connector/J (Java), Connector/Python, C API

Syntax constructs in SQL

The most important syntax constructs in SQL are SQL statements with contained clauses. Although technically not quite correct, both are generally referred to as “SQL commands”. In addition, there are other syntax constructs:

Term Description Example
Statement Instructs the DBMS to perform an action; ends with a semicolon CREATE TABLE People;
Clause Modifies an instruction; can only occur within instructions WHERE, HAVING
Expression Returns a value when evaluating 6 * 7
Identifier Name of a database object, variable, or procedure; can be qualified or unqualified dbname.tablename / tablename
Predicate Expression that evaluates to TRUE, FALSE, or UNKNOWN Age < 42
Query Special statement; returns found set of records SELECT Name FROM People WHERE Age < 42;
Function Processes one or more values; usually creates a new value UPPER('text') -- gives 'TEXT' back
Comment Used to comment SQL code; ignored by the RDBMS -- Comment up to end of line / /* multiline comment if necessary */
Note

SQL commands like SELECT and CREATE TABLE are usually capitalized. However, SQL isn’t case-sensitive. It’s just a widely used convention.

Using SQL as an example to set up product management

The easiest way to learn a programming language is to write and execute code yourself. As an example, we’ll create a mini database and execute queries against it. For this we’ll use the online SQL interpreter from the sql.js website. Go to the linked site and replace the SQL code entered with the code from our examples. Execute the code piece by piece to get the results displayed.

Basic procedure for setting up an SQL database

As an example, we’ll build a commercial product management system used for a physical or online store. Roughly speaking, these are the requirements:

  • There are several products, each of which we have a certain number of in stock.
  • Our customer base includes many clients and customers.
  • A customer places an order, which may contain several products.
  • For each order, we store the order date and data on the person placing the order as well as which products were ordered and in what quantity.

These requirements are translated into an abstract description and then into SQL code:

  1. Create modal
  2. Define schema
  3. Enter data records
  4. Define queries

Create model of entities and relationships

The first step takes place on paper or with special modeling tools. We collect information about the system to be modeled to derive entities and relations. This step is often realized as an Entity Relationship (ER) diagram.

What entities are there and how are they related? Entities are classes of things. In our product management example, the entities found are product, customer and order. For each entity a table is needed. Due to the specifics of the relational model, additional tables are added to model the relations. Recognizing this and implementing it properly requires experience.

A central question is how the entities relate to one another. Here we need to consider both directions of a relation and distinguish between singular and plural. Here’s an example using the owner-car relation:

  1. “One owner potentially owns several cars”
  2. “A car belongs to precisely one owner”

Three possible patterns of relationships between two entities emerge:

Relationship Entities From the left From the right
1:1 relation Auto:indicator “A car has precisely one indicator” “An indicator belongs to precisely one car”
1:n relation Owner:car “An owner potentially has several cars” “A car belongs to precisely one owner”
m:n relation Car:street “A car drives on multiple roads” “On one road several cars are driving”

Implement products

First, we’ll implement the products table. To do this, we define a schema, enter data records and execute a few simple queries for testing purposes.

Define schema

The central SQL command for defining database tables is CREATE TABLE. The command creates a named table and defines column properties. At the same time, data types and, if necessary, restrictions of the values to be stored, are defined:

DROP TABLE IF EXISTS Products;
CREATE TABLE Products ( product_id int, product_name text, stocked int, price int );
Note

We use a DROP TABLE IF EXISTS statement before the table definition. This removes any existing table and allows the same SQL code to be executed several times without causing error messages.

Add datasets

We create a few test records. We will use the SQL command INSERT INTO as well as the VALUES function to fill the fields:

INSERT INTO Products VALUES (10, 'ABC Product', 74, 1050);
INSERT INTO Products VALUES (20, 'KLM Product', 23, 750);
INSERT INTO Products VALUES (30, 'XYZ Product', 104, 350);

Define queries

To check the state of the products table we write a simple query. We use the SELECT FROM command and output the complete table:

SELECT * FROM Products;

We also write a slightly more complex query that calculates the total value of the stored products:

SELECT product_name AS 'Name', (stocked * price) AS 'Value' FROM Products;

Implementing additional tables

Next, we create the remaining required tables. We’ll follow the same steps as for the products table. First, we create the customer table:

DROP TABLE IF EXISTS Customers;
CREATE TABLE Customers ( customer_id int, customer_name text, contact text );

We maintain data records for two sample customers:

INSERT INTO Customers VALUES (100, 'EDC Customer', 'ED@example.com');
INSERT INTO Customers VALUES (200, 'WVU Customer', 'WV@example.com');

To check, we output the customer table:

SELECT * FROM Customers;

The next step is to create the orders table:

DROP TABLE IF EXISTS Orders;
CREATE TABLE Orders ( order_id int, customer_id int, order_date text );

We maintain three sample purchase orders. Here, we assign an ID as the primary key as the first value of the records. The second value is existing customer IDs as foreign keys. Then we store the date of the order:

INSERT INTO Orders VALUES (1000, 100, '2022-05-03');
INSERT INTO Orders VALUES (1001, 100, '2022-05-04');
INSERT INTO Orders VALUES (1002, 200, '2022-05-08');

For testing we issue the orders:

SELECT * FROM Orders;

Finally, we need a table for the products contained in an order together with their amount. This is an m:n relationship because an order can contain multiple products and a product can appear in multiple orders. We define a table that contains the IDs of orders and products as foreign keys:

DROP TABLE IF EXISTS OrderItems;
CREATE TABLE OrderItems ( orderitem_id int, order_id int, product_id int, count int );

We maintain a few ordered products. We choose the IDs of the orders and products so that there is an order with two products and another with only one product:

INSERT INTO OrderItems VALUES (10001, 1000, 10, 3);
INSERT INTO OrderItems VALUES (10002, 1000, 20, 2);
INSERT INTO OrderItems VALUES (10003, 1002, 30, 12);

To check, we issue the ordered products:

SELECT * FROM OrderItems;

Writing complex queries

If you’ve executed all the code snippets shown so far, you should be able to understand the structure of our test database. Now let’s move on to more complex queries that demonstrate the power of SQL. First, let’s write a query that merges data distributed across multiple tables. We’ll use a SQL JOIN command to join the customers’ data and orders tables. To assist, we name the columns and set a matching customer ID as a JOIN condition. Note that we use qualified identifiers to distinguish between the two tables’ columns:

SELECT customers.customer_name as 'Customer', customers.customer_id, orders.order_id, orders.order_date AS 'Date' FROM Customers JOIN Orders ON Orders.customer_id = Customers.customer_id ORDER BY Customers.customer_id;

Now we use another JOIN command to calculate the total cost of the ordered products:

SELECT OrderItems.order_id, OrderItems.orderitem_id AS 'Order Item', Products.product_name AS 'Product', Products.price AS 'Unit Price', OrderItems.count AS 'Count', (OrderItems.count * Products.price) AS 'Total' FROM OrderItems JOIN Products ON OrderItems.product_id = Products.product_id;

What are the advantages and disadvantages of SQL?

Advantages of SQL

The biggest advantage of SQL is its recognition value and widespread use. In terms of database solutions, the language represents an industry standard. This means, it’s relatively easy to find experienced SQL programmers. And with correctly assigned rights, it’s possible to grant analysts access to the system without them causing errors.

With relational algebra, the language is based on a robust mathematical foundation. This promises optimized data storage but requires a solid understanding of the technology as well as skill in modeling. With a well-designed schema, it’s possible to gain new insights from the data via appropriate queries.

Disadvantages of SQL

A major disadvantage of SQL is the high complexity of the language. While a programming language like Python gets by with fewer than 40 commands, SQL has hundreds of commands and clauses. Plus, many of them are implementation specific.

Building a SQL database requires a few assumptions on the data to be stored. These assumptions are defined in the database schema and ideally ensure the quality of the data. But if the schema is poorly designed, you can expect persistent problems. Changes to the schema during operation can pose a serious challenge.

For SQL databases with many accesses or very large data volumes, performance optimization is anything but trivial. Geographic distribution is considered extremely challenging and requires experienced specialists and sophisticated approaches like partitioning and sharding.

Impedance mismatch as an SQL disadvantage

In programming, object-oriented methodology (OOP) is widely used. What all OOP languages have in common is that data is encapsulated as objects. Typically, an OOP program comprises systems of interconnected objects. In addition to data, objects include “behaviors” based on their methods, which are inherited through class hierarchies.

The relational approach is fundamentally different, as data is distributed across multiple tables, making it impossible to model the behavior of the objects. Instead, queries and constraints on field values are used. The peculiarities of OOP cannot be transferred 1:1 to relational database structures. The phenomenon is known as “impedance mismatch” in electrical engineering.

To bridge the gap between OOP and SQL-based RDBMS, so-called object-relational mappings (ORM) are used. ORM is a translation layer that creates objects on demand and provides them with data from the database or writes data from objects to the database.

What about SQL alternatives?

Relational database management systems (RDBMS) and SQL have been the dominant database technology for decades. Today, a wide variety of alternatives exist with varying degrees of overlap with SQL RDBMS. Let’s look at three common alternatives:

PostgreSQL

PostgreSQL is a powerful object-relational database management system (ORDBMS). In addition to SQL as a query language, core concepts of object-oriented programming are directly supported. This means that object hierarchies, inheritance and object behavior can be used without object-relational mapping (ORM). User-defined and composite data types reduce the complexity of schemas and queries.

SQLite

With SQLite, an embedded SQL database is available under an open-source license. The slimmed-down SQL database is integrated as a library and runs directly in the browser and on mobile devices. SQLite impresses with its low complexity; the entire database is available as a single binary file.

NoSQL

The term NoSQL refers to a family of non-relational DBMS. Instead of modeling data primarily as fields in tables, various approaches are used. Usually, NoSQL solutions are less complex, more scalable and optimize performance. It’s also usually easier to change the schema during operation or to store data flexibly. On the other hand, there may be fewer guarantees with regard to the quality of the data.

Common NoSQL solutions include: