Learn SQL – A tutorial with coding examples

The syntax of SQL is based on re­la­tion­al algebra, which makes the pro­gram­ming language different from other languages. Fa­mil­i­ar­ising yourself with the syntax through practical examples can help you to learn SQL ef­fect­ively.

What is SQL syntax?

In pro­gram­ming, syntax refers to how a pro­gram­ming language is written. The syntax de­term­ines the basic code con­structs and how to link them together. Un­der­stand­ing the syntax is a fun­da­ment­al re­quire­ment for reading and writing code in pro­gram­ming languages.

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

How is SQL code executed?

SQL code exists as source code in text files. The code is only given life by a suitable execution en­vir­on­ment. The source code is read by an SQL in­ter­pret­er and converted into actions of an RDBMS. There are two basic ap­proaches here:

1. Execute SQL code in­ter­act­ively 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 ma­nip­u­la­tion 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 con­tain­ing 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 auto­mat­ing processes and for importing MySQL database backups with MySQL dump.

How to set up a product man­age­ment system using SQL

The easiest way to learn a pro­gram­ming 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 in­ter­pret­er 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 com­mer­cial product man­age­ment system for a store. These are the re­quire­ments:

• 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 re­quire­ments are trans­lated into an abstract de­scrip­tion and then into SQL code:

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

Create model of entities and re­la­tion­ships

The first step takes place on paper or with special modelling tools. We collect in­form­a­tion about the system to be modelled to derive entities and relations. This step is often realised as an Entity Re­la­tion­ship (ER) diagram.

What entities are there and how are they related? Entities are classes of things. In our product man­age­ment system example, the entities are products, customers and orders. For each entity, a table is needed. Due to the specifics of the re­la­tion­al model, ad­di­tion­al tables are added to model the re­la­tion­ships. Re­cog­nising this and im­ple­ment­ing it properly requires ex­per­i­ence.

A central question that needs to be answered is how the entities are related to one another. Here we need to consider both dir­ec­tions of a re­la­tion­ship and dis­tin­guish between singular and plural. Here’s an example using cars and car owners:

1. One owner can po­ten­tially own several cars.
2. A car can only belong to one owner.

Three possible re­la­tion­ship 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 prop­er­ties. At the same time, data types and, if necessary, re­stric­tions 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 cal­cu­lates the total value of the products that we have in stock:

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

Implement ad­di­tion­al 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 re­la­tion­ship 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 un­der­stand the structure of our test database. Now let’s move on to more complex queries that demon­strate the power of SQL. First, let’s write a query that merges data dis­trib­uted 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 iden­ti­fi­ers to dis­tin­guish 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;