Author: Ignacio L. Bisso

Ignacio is a database consultant from Buenos Aires, Argentina. He’s worked for 15 years as a database consultant for IT companies like Informix and IBM. These days, he teaches databases at Sarmiento University and works as a PostgreSQL independent SQL consultant. A proud father of four kids with 54 years in his backpack, Ignacio plays soccer every Saturday afternoon, enjoying every match as if it’s his last one.

How to Become a Data Analyst

Man and woman standing in front of the big screen for data analysis.
Data analyst is a relatively new position available at several companies. It’s also a high-salary specialization without a complex learning curve. Thus, many professionals are looking to make a career switch to this burgeoning field. In this article, I’ll explain what skills you need to become a junior data analyst. We’ll also review some tips for making this career change and see what an entry-level data analyst salary looks like.

SQL INNER JOIN Explained in Simple Words

In this article, I will cover perhaps the most important element of the SQL language. That's right! I'm talking about the SQL INNER JOIN clause. As you know, in a database the data are stored in several tables. In my last article, I covered how to create SQL queries when the data you need is in only one table. But what if the data you need is in two tables?

A Non-Technical Introduction to Learning SQL on Your Lunch Break

Do you think learning SQL will help you in your career? You are right. SQL is one of the easiest computer languages to learn. These days many non-IT employees have SQL skills and use them to extend their professional capacity. Moreover, more and more companies are encouraging their employees in non-IT areas (like sales, advertising, and finances) to learn and use SQL. One of the benefits of empowering employees with SQL skills is that once you answer one data question, the results will generate a new data question, and then perhaps a cascade of further data questions.

How to Install PostgreSQL on Windows in 5 Minutes

Installing database software like PostgreSQL, Oracle, or SQL Server can seem like a complicated task, but it really isn’t! These days, most relational database management systems come with installation wizards that make the process much simpler. In this article, we’ll look at how to install PostgreSQL and test that the installation is working. Overview: PostgreSQL Installation Steps We’ll complete the following tasks: Downloading the PostgreSQL package. Installing and configuring PostgreSQL.

How to Run Your First SQL Query

In this article, I’ll help you write and execute your first SQL query. Let’s jump right in! Running an SQL query for the first time is not a complex task, but it can seem intimidating at first if you’re a complete beginner. But once you get past that initial roadblock, you’ll be able to focus on learning SQL and writing more interesting queries to meet your business needs. The Ingredients You’ll need these three things to run an SQL query:

Exploring Summer Solstice With SQL

Why is June 21 the official start of summer? Let’s see how SQL can help us answer this question. The Summer Solstice Officially, June 21 is recognized as the summer solstice, the longest day of the entire year in terms of daylight. Why? Because on this day, the sun rises early and sets quite late. People in the Northern Hemisphere celebrate the summer solstice with feasts, bonfires, picnics, and traditional dances and songs.

Improving Slow Query Performance: When Runtime Matters

As SQL users, we usually focus on writing queries that return correct results. However, there are more things to consider when you’re writing a query; one of them is query performance. In this article, we’ll look at some examples where query response time is critical. Scene One: 911 Call Center Let’s suppose we’re at a 911 call center, when the phone rings. One of the operators answers the call; a witness reports that a man has been shot.

Converting Subqueries to Joins

Not all queries are alike, especially in terms of performance. In this article, we’ll look at how you can convert SQL subqueries to joins for improved efficiency. When should I use SQL subqueries? Great question! Unfortunately, there’s no concrete answer. SQL beginners tend to overuse subqueries. Typically, once they find that SQL construction works in one situation, they try to apply that same approach to other situations. It’s only natural.

SQL Interview: Tips From Recruiter and Sample Questions with Solutions

Applying for an SQL-related position? You’ll likely have to pass an interview process. But you’ll be much better prepared for the interview if you know what to expect. This article sheds some light on the recruiter’s perspective to better prepare you for your interview. SQL Knowledge and Skills There are different SQL skills that an interview can test. These include: Skills related to any specific SQL database engine (like PostgreSQL, MySQL, or Oracle).

Correlated Subqueries in 5 Minutes

If you’re familiar with the famous Russian nesting doll, then SQL subqueries should be a peace of cake to understand—subqueries are just queries nested within queries. An SQL subquery is often called an “inner” query; the main query is usually called the “outer” query. This article covers everything you need to know about correlated subqueries. What Exactly is a Correlated Subquery? A correlated SQL subquery is just a subquery that is executed many times—once for each record (row) returned by the outer (main) query.

Extracting Data From a String in PostgreSQL

Learn how to extract data from strings in PostgreSQL using the split_part function. Quite often, we’d like to extract parts of a string when working with text values. A common example is when we have a full name and need to retrieve only the last name. In this article, we’ll examine split_part, a PostgreSQL string-related function that can be used to extract a substring. Why Use String Functions in Your Database?

How to Deal with Unwanted Characters in SQL

Unwanted characters in text data can be a bit of a pain, but there’s an easy way to fix them! Sometimes, we’ll find extra 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! The Importance of Standardizing Strings in Your Database When we work with data populated or loaded as free text, the data quality is often quite poor.

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.

The SQL Coalesce Function: Handling Null Values

Though the COALESCE function may seem complex, it’s actually very straightforward and useful. In this short article, we’ll look at several examples of how the COALESCE function can be used to work with NULL values in SQL. The Need for Coalesce Before we dive into the COALESCE function in detail, you should understand how NULL values behave in expressions. Simply put, a value of NULL indicates that there is currently no value for a particular entry in a table column.

How Often Employees Are Running Late: Datetime And Interval Arithmetic in SQL

Computing Tardiness: Date, Time, and Interval Arithmetic in SQL 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:

Learn SQL Views in 30 Minutes

Views aren’t complicated – if you’ve got half an hour, we’ll get you started writing SQL queries using views! Let’s start by answering the question “What is a view in SQL?’. A view is a database object (as is a table, an index, or a stored procedure). Like a table, you can query a view and extract the information in it. It can be used in the FROM clause of a SELECT, and you can reference view columns in clauses like SELECT, WHERE and GROUP BY among other clauses as well.

SQL Window Functions By Explanation

In our previous post, we explained how SQL window functions work by example. We started with some very simple, basic functions. Let’s extend it by explaining subclauses in window functions. SQL window functions are a great way to compute results from a set of rows rather than a single row. As you know from our first article, the “window” in window function refers to the set of rows.

SQL Window Functions by Example

Interested in how SQL window functions work? We use some simple examples to get you started. 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 BYclause, we “lose” the individual rows.