Articles Cookbook
21 Sep 2017
Dorota Wdzięczna

An Illustrated Guide to the SQL Self Join

What is an SQL self join and how does it work? When should it be used? We’ll provide answers to those questions! In SQL, we can combine data from multiple tables by using a JOIN operator. JOIN has several variants; we’ve already discussed CROSS JOIN, INNER JOIN, and OUTER JOIN. Most of the time, these operators join data from two or more different tables. You can practice all the different types of JOINs in our interactive SQL JOINs course.
Read more
4 Sep 2017
Marija Ilic

How to Track Down Duplicate Values in a Table

When it comes to information management, duplicates present one of the most common challenges to data quality. In this article, I'll explain how it is possible to find and distinguish duplicate names with the help of the SQL data programming language. I really like my maiden name. The reason I like it so much is because it's rare. My maiden name (first with last) provided a unique identifier on platforms such as LinkedIn, Facebook, Twitter and similar.
Read more
31 Aug 2017
Aldo Zelen

Common SQL Window Functions: Positional Functions

Positional SQL window functions deal with data's location in the set. In this post, we explain LEAD, LAG, and other positional functions. SQL window functions allow us to aggregate data while still using individual row values. We've already dealt with ranking functions and the use of partitions. In this post, we'll examine positional window functions, which are extremely helpful in reporting and summarizing data. Specifically, we'll look at LAG, LEAD, FIRST_VALUE and LAST_VALUE.
Read more
28 Aug 2017
Jenny Hung

Four Reasons You Must Learn SQL in Data Science

Is SQL important for data science? It certainly is! This language can help you build a foundation for your analytical career. Let’s see how you use SQL in data science. Data science is hot right now. What if you could predict the next market crash? Or contain the spread of Ebola? Or accurately predict a health crisis months or even years before it happens? Data scientists are working hard on these kinds of projects, and they are earning healthy salaries in the process.
Read more
21 Aug 2017
Kamila Ostrowska

5 Reasons Why No One Should Be Afraid to Learn SQL

Think SQL is just for tech experts? Think again. In this article, we'll bust some common myths and show you that anyone can learn SQL. Let's dive in and make SQL simple. In today’s world, even those in non-technical jobs need some technical skills. And you don’t have to be a hard-core nerd to get these skills. Everywhere you look, from marketing to management, a touch of tech knowledge can set you apart.
Read more
17 Aug 2017
Francisco Claria

Referential Constraints and Foreign Keys in MySQL

Foreign keys and referential constraints allow you to set relationships between tables and modify some of the database engine’s actions. This beginner’s guide explains referential integrity and foreign key use in MySQL. One of the most important aspects of database usage is being able to trust the information you store. Database engines provide several features that help you maintain the quality of your data, like defining required columns as NOT NULL and setting an exact data type for each column.
Read more
17 Aug 2017
Dorota Wdzięczna

An Illustrated Guide to the SQL OUTER JOIN

We’ve already discussed the SQL CROSS JOIN and INNER JOIN statements. It’s time to explore another: OUTER JOIN. What is it? How does it work? Let’s find out! If you’ve read our other posts, you know that you can link the data in two or more database tables using one of the many types of SQL join operator. Today, we’ll discuss the three kinds of OUTER JOIN: LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN.
Read more
7 Aug 2017
Aldo Zelen

SQL Window Functions: Partitions and Ranking Functions

You’ve started your mastery of SQL window functions by learning RANK, NTILE, and other basic functions. In this article, we will explain how to use SQL partitions with ranking functions. Mastering SQL window functions (AKA analytical functions) is a bumpy road, but it helps to break the journey into logical stages that build on each other. In the previous Common SQL Functions article, you learned about the various rank functions, which are the most basic form of window functions.
Read more
4 Aug 2017
Ignacio L. Bisso

SQL Window Function Example With Explanations

Interested in how window functions work? Scroll down to see our SQL window function example with definitive explanations! SQL window functions are a bit different; they compute their result based on a set of rowsrather than on a single row. In fact, the “window” in “window function” refers to that set of rows. Window functions are similar to aggregate functions, but there is one important difference. When we use aggregate functions with the GROUP BY clause, we “lose” the individual rows.
Read more
31 Jul 2017
Marek Pankowski

How Recursive Common Table Expressions Work

Recursive Common Table Expressions are immensely useful when you're querying hierarchical data. Let's explore what makes them work. Common Table Expressions (CTEs) are some of the most useful constructions in SQL. Their main purpose is improving query design, which makes queries easier to read. One of the reasons CTEs are so popular is that they let you divide longer queries into shorter subqueries. These are easier to read and edit.
Read more