Articles Cookbook
6 Dec 2016
Aldo Zelen

SQL Errors: Five Common SQL Mistakes

As you learn SQL, watch out for these common coding mistakes You’ve written some SQL code and you’re ready to query your database. You input the code and …. no data is returned. Instead, you get an error message. Don’t despair! Coding errors are common in any programming language, and SQL is no exception. In this post, we’ll discuss five common mistakes people make when writing SQL. Watch Your Language (and Syntax) The most common SQL error is a syntax error.
Read more
3 Dec 2016
Agnieszka Kozubek-Krycuń

GROUP BY Clause: How Well Do You Know It?

The database can do different kinds of computations: it can add and subtract, multiply and divide, it can do computations on dates, it can count rows and sum row values, and so on. It can also do quite sophisticated statistics. The GROUP BY clause is the most basic way to compute statistics in SQL. It can be quite tough for beginners but it is really powerful. Let's look at the details of the GROUP BY clause, starting with the basics.
Read more
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

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
18 Oct 2016
Magdalena Brych

Should I Learn SQL? 4 Convincing Reasons

There are 250+ computer programming languages in popular use. Why should you learn SQL, or Structured Query Language? Why Should You Learn SQL? Why learn SQL? To answer this question, you should start with reflecting on your professional life. Are you interested in boosting your career? Wondering what skills are worth investing in? If you’re currently in your dream job, still searching for it, or even thinking about starting your own business, it pays to develop your technical skills.
Read more
27 Sep 2016
Aldo Zelen

A Gentle Introduction to Common SQL Window Functions

Mastering SQL analytical functions is a necessity for an aspiring BI/DWH professional. In this article, we'll explore the history of SQL in a BI environment. We'll also introduce some of the more common SQL analytical functions, including RANK, LEAD, LAG, SUM, and others. SQL can be used for business intelligence (BI) applications that combine data retrieval, analysis, and computation. These computations are more complex than what we see in OLTP systems.
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