Understanding the SQL Subquery

Posted on by Ian

One of the things I love about SQL is just how easy it is to write a simple query that returns a meaningful result set, without having to a whole bunch of complex code. And I imagine most beginners are relieved when they discover this too.

However, while basic queries can go a long way, there’s a whole world of advanced techniques that can take our SQL development to another level. Somewhere in that world, would be the SQL subquery.

In this article, we look at the SQL subquery. We’ll explore what subqueries are, how they work, and when to use them. We’ll also look at some simple examples to demonstrate their use.

Continue reading →

10 Essential Database Concepts that All Beginners MUST Learn

Posted on by Ian

When I first started building websites 25 years ago, everything was static. Our primary building block was HTML, with a bit of CSS and JavaScript sprinkled in for good measure. Our content would be incorporated into the HTML documents, and so there was no separation of functionality and content.

Regardless, I was just happy to be able to build these amazing things that I could publish for the world to see.

But within a few years I had quickly learned about the power of databases and how they could completely transform the websites we were building at the time.

Continue reading →

Time Travel in SQL Server: Using Temporal Tables for Historical Data Analysis

Posted on by Ian

Temporal tables, introduced in SQL Server 2016, provide a powerful mechanism for tracking historical changes to data. This feature is particularly useful for auditing purposes, allowing organisations to maintain a complete history of data modifications without the need for complex triggers or custom logging solutions.

In this article, we’ll explore how to implement and use temporal tables for auditing in SQL Server, along with examples to demonstrate.

Continue reading →

How to Return Values in SQL Without using SELECT

Posted on by Ian

The SQL SELECT statement is possibly the most commonly used SQL statement. It’s often used to return data from a database, but it can also be used to call functions that return data. The SELECT statement can also be used to return static values, such as string literals.

But the SELECT statement isn’t the only way we can return data in SQL. Another way to return values is with the VALUES statement.

Continue reading →

Understanding the RIGHT() Function in PostgreSQL

Posted on by Ian

In PostgreSQL the right() function returns the specified number of rightmost characters in a given string.

We have the option of specifying the number of characters to return from the right or the number of characters to omit from the left. We do this by specifying a positive integer (to return n number of rightmost characters) or a negative integer (to return everything except n leftmost characters).

Continue reading →

A Quick Look at the LEFT() Function in PostgreSQL

Posted on by Ian

In PostgreSQL we can use the left() function to get the specified number of leftmost characters in a given string.

We have the option of specifying the number of characters to return from the left or the number of characters to omit from the right. We do this by specifying a positive integer (to return n number of leftmost characters) or a negative integer (to return everything except n rightmost characters).

Continue reading →