Articles Cookbook
9 Feb 2018
Ignacio L. Bisso

How to Remove Junk Characters in SQL

Unwanted characters in text data can be a bit of a pain, but there’s an easy way to fix them. Scroll down to learn how to remove junk characters in SQL in the easiest way! Sometimes, we’ll find unwanted characters inside our string data because our SQL queries didn’t work as expected. Moreover, these extra characters may sometimes be invisible, which really complicates things. In this article, we’ll examine some string-related SQL functions that can handle unwanted characters—visible or not!
Read more
31 Jan 2018
Ignacio L. Bisso

How to Solve Capitalization Data Quality Issues

Misspelled names, typos, and text data quality issues in your database? Power up your queries! Use SQL string functions to address data quality issues related to capitalization. Sometimes, our SQL queries don't work as expected because of data quality issues. In this article, we will examine some string-related SQL functions that can correct data quality issues related to capitalization. We'll be using PostgreSQL in our examples, but similar functions are available in most database engines.
Read more
5 Jan 2018
Aldo Zelen

18 Useful Important SQL Functions to Learn ASAP

Learning a new programming language can seem intimidating. Like any other language, a programming language has a large vocabulary that you need to master. In this article, we’ll look at some of the most useful SQL functions that you need to know. Structured Query Language, commonly known as SQL, is the standard language for managing and querying data in relational databases. Born out of the need to efficiently interact with large datasets, SQL has become an indispensable tool for database administrators, data analysts, and developers alike.
Read more
21 Dec 2017
Aldo Zelen

How to Draw a Christmas Tree in SQL

You can use SQL to manipulate all kinds of data, from huge analytical queries to brief single-purpose statements. But you can also use SQL just for fun, without any business requirements stifling your creativity. So, get out your jolly hat and prepare to sing O Christmas Tree as we create some quirky art with plain old SQL. Today, we’re going to generate some holiday-themed ASCII art, just for fun. That’s right.
Read more
15 Dec 2017
Dejan Sarka

Statistics in SQL: Dependencies Between Discrete Variables

In my previous article, we looked at how you can calculate linear dependencies between two continuous variables with covariance and correlation. Both methods use the means of the two variables in their calculations. However, mean values and other population moments make no sense for categorical (nominal) variables. For instance, if you denote "Clerical" as 1 and "Professional" as 2 for an occupation variable, what does the average of 1.5 signify?
Read more
12 Dec 2017
Aldo Zelen

Simplify SQL Code: Recursive Queries in DBMS

Hey SQL users! Are you repeating the same query in every report? Are your queries getting too complicated? Use recursive queries to simplify SQL code! Too many SQL reports can lead to clutter on your desktop and in your head. And is it really necessary to code each of them separately? Ad-hoc queries can share much of the same SQL code with managerial reports and even regulatory reports. Suppose you’ve been writing basic SQL code for a while.
Read more
7 Dec 2017
Ignacio L. Bisso

The SQL Coalesce Function: Handling Null Values

You may already know how to return null values in SQL. Now, we’re going to learn how to do the opposite. Though the SQL COALESCE function may seem complex, it’s actually very straightforward and useful. Let’s look at several examples of how the SQL COALESCE function can be used to work with NULL values in SQL. The Need for Coalesce in SQL Before we dive into the SQL COALESCE function in detail, you should understand how NULL values behave in expressions.
Read more
29 Nov 2017
Kacper Skawina

Analyzing Survey Data: Explore Results Using SQL

Excel is a powerful beast that lets you analyze complex data. Yet, operating on big chunks of data can sometimes be a daunting task. Let's take a look at how SQL can help. Today, we'll tackle a common problem with importing data to an SQL database, using a real-life example. Suppose your company conducted a survey on the most popular programming trends and preferences, striving to meet the expectations of its users.
Read more
27 Nov 2017
Ignacio L. Bisso

SQL Date and Interval Arithmetic: Employee Lateness

Computing Tardiness: Date, Time, and Interval SQL Arithmetic In this article, we’re going to discuss some interesting operations we can perform with date-related data types in SQL. The SQL standard, which most relational databases comply with these days, specifies the date-related data types that must be present in relational databases. The most important of such data types are date, time, timestamp, and interval. Here’s a brief rundown of the differences between these data types:
Read more
13 Nov 2017
Aldo Zelen

SQL Reporting Guide for Business Professionals

Working with the financial aspects of large and small enterprises can be a daunting task for a business professional. In this article, we'll look at several ways of constructing the perfect SQL report. You've probably already heard about SQL from your colleagues or in other areas of your career. If you're here, you've likely concluded that learning SQL will make your professional life easier – and you're right!
Read more