Learn SQL – A tutorial with examples

The syntax of SQL is based on relational algebra, which makes the programming language different from other languages. Familiarizing 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.

SQL term Explanation Example
Statement Instructs the DBMS to perform an action; ends with a semicolon CREATE TABLE People;
Clause Modifies a statement; can only occur within statements 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') -- returns 'TEXT'
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. The capitalization of commands is just a widely used convention.

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.

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

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 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 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. Recognizing 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:

Relationship Entities From the left From the right
1:1 relation Auto:indicator A car can only have one indicator. An indicator can only belong to one car.
1:n relation Owner:car An owner can potentially have several cars. A car can only belong to one owner.
m:n relation Car:street A car can drive on multiple roads. Several cars can drive on one road.

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 );
sql
Note

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

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);
sql

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;
sql

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;
sql

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 );
sql

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');
sql

To check if it worked, we’ll output the customer table:

SELECT * FROM Customers;
sql

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 );
sql

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');
sql

To test it, we’ll issue the orders:

SELECT * FROM Orders;
sql

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 );
sql

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);
sql

To check this, we’ll issue the products that were ordered:

SELECT * FROM OrderItems;
sql

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;
sql

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;
sql
Tip

Want to learn more about what SQL is or need help solving a problem related to the database language? Or perhaps you simply want to broaden your knowledge of SQL. Whatever the case may be, you can find a number of articles on SQL and MySQL in our Digital Guide:

We use cookies on our website to provide you with the best possible user experience. By continuing to use our website or services, you agree to their use. More Information.