Articles Cookbook
26 Nov 2016
Michał Kołodziejski

Do it in SQL: Recursive SQL Tree Traversal

In the previous article, I described how to use Common Table Expressions to find the shortest path in a directed graph. That example could be hard to follow, I admit. Let’s do something much more common, something that is implemented on almost every website – a menu. Instead of writing the code, we’ll take advantage of the SQL tree structure writing just one query. We’ll use CTEs for PostgreSQL and the hierarchical query clause for Oracle.
Read more
21 Nov 2016
Patrycja Dybka

SQL JOINs Explained with Venn Diagrams

A SQL JOIN is a method to retrieve data from two or more database tables. This article presents a basic overview of what data from a particular SQL join will look like. A popular way of understanding SQL joins is to visualize them using Venn diagrams, so each example have corresponding Venn diagram, appropriate SELECT statement and the result table. There are a few major kinds of SQL joins:
Read more
18 Nov 2016
Patrycja Dybka

SQL Subqueries

The article describes what a subquery is and what these useful statements look like. We will cover basic examples with the IN, EXISTS, ANY, and ALL operators, look at subqueries in FROM and WHERE clauses, and explore the difference between correlated and nested subqueries. First, let’s start with an example database. To present some of these statements we need to have an example table and fill it with some data.
Read more
25 Oct 2016
Adrian Dembek

SQL JOINs for Beginners

You’re probably already familiar with simple SQL queries, such as SELECT * FROM table. Now you are wondering what to do when you have multiple tables, and you want to join them. Exactly! JOIN is the key. In this SQL JOINs tutorial for beginners, you will learn how to connect data from multiple tables. What are SQL JOINs? Databases usually have more than one table. JOINs are an SQL construction used to join data from two or more tables.
Read more
20 Oct 2016
Patrycja Dybka

How to Tackle SQL NULLs: COALESCE function

It's inevitable that some data in the database has no value what in SQL is represented by the NULL keyword. "No value" here is different from zero, false, or an empty string (but with exceptions! In Oracle database, NULL is the same as string of zero length). During playing with the database, treating NULLs are becoming more and more troublesome, therefore MySQL standard provides help with some of the functions, like COALESCE.
Read more
9 Aug 2016
Jeffrey J. Keller

Window Functions: Part Two: Aggregating Data

In part one of this series on SQL Server window functions, the functionality of the OVER() clause was discussed. This article will focus on aggregate functions. The majority of these can be used in conjunction with OVER. Some, like SUM() and AVG(), are widely used. Others — including VARP() and CHECKSUM_AGG() — are not as well-known, but are potentially quite useful. OVER defines the user-specified range on which a function is applied.
Read more
2 Aug 2016
Patrycja Dybka

The History of Slonik, the PostgreSQL Elephant Logo

Logos are powerful. What better way to remind people of a product than an eye-catching, memorable symbol? With that in mind, today we’ll answer the question ‘Why did PostgreSQL choose an elephant for its logo?’ Every product or company has its logo – something that identifies and encapsulates the essence of their brand. In time, it practically becomes the brand: can you imagine McDonald’s without its golden arches? What if the Coca-Cola logo was suddenly done in purple block print?
Read more
23 Jun 2016
Maria Alcaraz

Your First Steps With the Geography Data Type

Geographical applications are everywhere: GPS and sat nav systems, maps, get-a-taxi apps, real estate portals, etc. Behind each of them is a spatial database storing geographical data, and supporting spatial queries. In this article, we will introduce PostGIS, the main open-source spatial database manager. PostGIS is a spatial database extension for the PostgreSQL relational database. It adds support for geographic objects, allowing location queries to be run in SQL. PostGIS adds two main data types to PostgreSQL: geography and geometry.
Read more
21 Jun 2016
Jeffrey J. Keller

Window Functions in SQL Server: Part One: The OVER() Clause

Window functions were first introduced in standard SQL 2003 and then extended in SQL 2008. Since SQL Server 2005, Microsoft has been steadily improving window functions in the database engine. These functions perform an aggregate operation against a user-defined range of rows (the window) and return a detail-level value for each row. Well-known aggregate functions include SUM, AVG, MIN, MAX, and many others. Recent versions of SQL Server have introduced ranking and analytic functions such as LAG, LEAD, RANK, CUME_DIST, and many others.
Read more
19 May 2016
Maria Alcaraz

Pivot Tables in PostgreSQL Using the Crosstab Function

Some years ago, when PostgreSQL version 8.3 was released, a new extension called tablefunc was introduced. This extension provides a really interesting set of functions. One of them is the crosstab function, which is used for pivot table creation. That's what we'll cover in this article. The simplest way to explain how this function works is using an example with a pivot table. First, we will explain our initial point from a practical perspective, then we'll define the pivot table we want.
Read more