Introduction to the Course

Familiarize yourself with this course and understand its prerequisites.

We'll cover the following

Relational database management systems increasingly offer support for data formats such as JSON and spatial data, and operations on these data formats. For example, MySQL started supporting JSON from version 5.7.8 onwards. Other database management systems, like PostgreSQL, have progressed similarly. Partly, this development is due to the rise of NoSQL databases such as MongoDB. With these new features being available, the demand for mastering advanced concepts of SQL increases. Hence, this course teaches in-depth knowledge about those concepts using MySQL open-source database.

Prerequisites and intended audience

As we’ll cover advanced concepts in this course, so the learner requires basic knowledge about SQL. This implies an understanding of concepts such as:

  • Table creation
  • Primary and foreign keys
  • Basic data querying and insertion
  • Aggregation and grouping
  • Nested queries
  • Joins
  • Stored procedures

This course fits in very well with people who have previously taken the introductory course on SQL. The outline of this introductory course also gives a rough idea about the expected level of knowledge of SQL.

Even for people who are already familiar with some advanced concepts of SQL, this course effectively demonstrates how these concepts can be applied to MySQL. We can apply this knowledge to practice for a university course, train for an upcoming job, or pay homage to our interests.

Course overview

As part of advanced concepts in SQL, we will learn about intriguing data types like date and time, JSON, and spatial data. Powerful SQL statements such as common table expressions, aggregation, and window functions will help us get the most out of processing these data types. In addition, we will also learn about the powerful concepts of control flow and recursion in SQL.

As such, this course empowers you to master SQL’s intricacies with a curriculum emphasizing relevant advanced concepts. You can apply and practice these sophisticated SQL techniques directly within the MySQL environment, ensuring a hands-on experience that solidifies your learning. This course is designed to help you understand SQL from a practical viewpoint, bridging theoretical knowledge with real-world applications.

To get an idea about this, let us consider the following problem statement: Given an arbitrary binary tree, extract the paths from all leaves to the root. The binary tree below shows that the root node is highlighted in red and the leaf nodes are highlighted in green. To solve the given problem, we need to find all paths from the green nodes to the red node.

Press + to interact
A binary tree with the root highlighted
A binary tree with the root highlighted

Solving this problem without the advanced concept of recursion would be quite challenging, if not impossible, for arbitrary binary trees. So, how would we proceed given the structure of the SQL table BinaryTree below?

Press + to interact
-- Create a table to store an arbitrary binary tree.
CREATE TABLE BinaryTree
(
    id     INT unsigned auto_increment,
    label  TEXT,
    parent INT unsigned,
    PRIMARY KEY (id)
);
-- Recreate the binary tree from the above illustration.
INSERT INTO BinaryTree (label, parent)
VALUES ('A', NULL),
       ('B', 1),
       ('C', 1),
       ('D', 2),
       ('E', 2),
       ('F', 5),
       ('G', 5);
-- Inspect the resulting table.
TABLE BinaryTree;

Running the SQL code snippet above, we can observe three statements being executed. These statements create the table BinaryTree (refer to lines 2–8) that stores the nodes of a binary tree as created through the second statement. Here, the nodes inserted resemble the binary tree as depicted in the illustration above the SQL code snippet (refer to lines 11–18).

Press + to interact
-- Recursively extract the paths from all leaves to the root in the binary tree.
WITH RECURSIVE Paths AS (
    SELECT JSON_ARRAY(id) AS path
    FROM BinaryTree
    WHERE id NOT IN (SELECT DISTINCT parent FROM BinaryTree WHERE parent IS NOT NULL)
    UNION
    SELECT JSON_ARRAY_APPEND(path, '$', parent) AS path
    FROM Paths
             JOIN BinaryTree ON path ->> '$[last]' = id
)
SELECT JSON_EXTRACT(path, '$[0 to last-1]') AS `Path from Leaf to Root`
FROM Paths
WHERE JSON_VALUE(path, '$[last]' RETURNING SIGNED) IS NULL;

After creating the table BinaryTree, the recursive query above performs all the magic. At the moment, it is not important to understand the syntactic nuances of this query but to get its gist. We can mentally separate the query into three parts:

  • We extract the leaf nodes from BinaryTree (lines 3–5).
  • We recursively find the node’s parent most recently added to a path (lines 9–11).
  • We format the computed paths into a sequence of node IDs (lines 15–31).

Eventually, a sufficient number of recursion steps arrive at the root node having constructed a path from a leaf node to the root node. For this example, as we can observe in the output of the SQL code snippet above, we can find four paths from each leaf node to the root. Each path is an array of node IDs beginning with the leaf node and ending with the root.

Similar to this example, this course’s contents are accompanied by interactive code playgrounds, quizzes, and assessments to gain hands-on experience. While exploring these advanced concepts of SQL, we will refrain from diving into theoretical concepts and focus on the practical aspects instead. For people interested in learning more about database design fundamentals, check out the corresponding course on Educative.