Learn SQL – A tutorial with coding examples

The syntax of SQL is based on relational algebra, which makes the programming language different from other languages. Familiarising yourself with the syntax through practical examples can help you to learn SQL effectively.

What is SQL syntax?

In programming, syntax refers to how a programming language is written. The syntax determines the basic code constructs and how to link them together. Understanding the syntax is a fundamental requirement for reading and writing code in programming languages.

The most important syntax constructs in SQL are SQL statements, which may also contain clauses. Both are commonly referred to as ‘SQL commands’, even though, from a technical standpoint, this is not entirely accurate. These aren’t the only SQL syntax constructs though. Below you’ll find a table that provides you with an overview of the SQL syntax constructs.

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 an 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 display of results makes this approach best suited for learning and creating complex queries. 2. 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.

How to set up a product management system using SQL

The easiest way to learn a programming language is to write and execute code yourself. In this tutorial, we’ll create a mini database and execute queries against it. To do this, we’ll use the online SQL interpreter from the website sql.js. To follow the tutorial, go to the website and replace the SQL code that has already been entered with the code from our examples. Execute the code piece by piece to have the results displayed.

Set up an SQL database

In this example, we’re going to build a commercial product management system for a store. These are the requirements:

• There are several products, and we have a certain amount of each product in stock.
• Our customer base includes many clients and customers.
• Orders placed by customers may contain several products.
• For each order, we store the order date and data of the person placing the order as well as the products that were ordered and the amount ordered.

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

1. Create model
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 modelling tools. We collect information about the system to be modelled to derive entities and relations. This step is often realised as an Entity Relationship (ER) diagram.

What entities are there and how are they related? Entities are classes of things. In our product management system example, the entities are products, customers and orders. For each entity, a table is needed. Due to the specifics of the relational model, additional tables are added to model the relationships. Recognising this and implementing it properly requires experience.

A central question that needs to be answered is how the entities are related to one another. Here we need to consider both directions of a relationship and distinguish between singular and plural. Here’s an example using cars and car owners:

1. One owner can potentially own several cars.
2. A car can only belong to one owner.

Three possible relationship patterns emerge between the two entities:

Implement products

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

Define schema

The central SQL command for defining database tables is CREATE TABLE. This command allows you to create a table with a name and specify column properties. At the same time, data types and, if necessary, restrictions on 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 );

Add datasets

Now, we’ll 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’ll write a simple query. We use the SELECT FROM command and output the complete table:

SELECT * FROM Products;

Now, we’ll write a slightly more complex query that calculates the total value of the products that we have in stock:

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

Implement additional tables

Next, we’ll create the remaining tables we need. We’ll follow the same steps that we used for the Products table. First, we create the Customers table:

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

Then, we enter 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 if it worked, we’ll 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 );

Now, we’ll enter three sample purchase orders. For the first value of the records, we’ll assign an ID as the primary key. The second value is for existing customer IDs, which function as the 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');

To test it, we’ll issue the orders:

SELECT * FROM Orders;

Finally, we need a table for the products 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’ll 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 );

Now, we enter a few products that have been ordered. We’ll choose the IDs of the orders and products so that there is an order with two products and another order 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 this, we’ll issue the products that were ordered:

SELECT * FROM OrderItems;

Write 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 tables that contain the customer data and orders. While doing this, we’ll name the columns and set a matching customer ID as a JOIN condition. Keep in mind that we use qualified identifiers to distinguish between the columns of the two tables:

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’ll 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;