22 Oct 2020 Ignacio L. Bisso What is a Primary Key in SQL? Primary keys are an important concept in SQL databases. They provide a unique ID for every row in a database table. As an SQL developer, you should know what primary keys are, what data values are allowed in them, and how to create them. Read this article to learn the ABCs of primary keys in 10 minutes. How to Identify Rows in a Table Tables are the main objects in an SQL database, and as you probably know, tables store records or rows. Read more 24 Sep 2020 Ignacio L. Bisso What Is the MySQL OVER Clause? If you want to learn window functions in MySQL, you need to understand the OVER clause. In this article, we use real-world examples to explain what the MySQL OVER clause is, how it works, and why it’s so awesome. In 2018, MySQL introduced a new feature: window functions, which are accessed via the OVER clause. Window functions are a super powerful resource available in almost all SQL databases. They perform a specific calculation (e. Read more 27 Aug 2020 Ignacio L. Bisso How Does SQL GROUP BY Work? Grouping results is a powerful SQL feature that allows you to compute key statistics for a group of records. GROUP BY is one of SQL’s most powerful clauses. It allows you to see data in a new way and find key metrics (like the average, maximal, and minimal values in a group of records). Without GROUP BY, all the results we obtain are oriented to records. With GROUP BY, we can create groups of records and calculate metrics on each group. Read more 30 Jul 2020 Ignacio L. Bisso How to Use Subqueries in INSERT, UPDATE, and DELETE Statements Did you know that subqueries can also be used in UPDATE, INSERT, and DELETE statements? Subqueries in SQL are like building blocks that can be used in a variety of statements, not just SELECT. If you find subqueries useful in SELECT, read this article to find out how useful they can be in other instructions. You will be able to create much more complex and powerful SQL queries in no time! Read more 14 Jul 2020 Ignacio L. Bisso How to Calculate the Difference Between Two Rows in SQL Calculating the difference between two rows in SQL can be a challenging task. It is possible – and there’s more than one way to do it. In this article, I’ll explain how to use the SQL window functions LEAD() and LAG() to find the difference between two rows in the same table. The best way to learn window functions is our interactive Window Functions course. It contains over 200 hands-on exercise that will teach you all window functions concepts, from simple OVER() clause to complex topics such as combining window functions and GROUP BY clause. Read more 21 May 2020 Ignacio L. Bisso How to Number Rows in an SQL Result Set Have you ever needed to add a sequential number to the records in the result of an SQL query? It’s not as simple as it looks! Find out how to do it correctly in this article. To number rows in a result set, you have to use an SQL window function called ROW_NUMBER(). This function assigns a sequential integer number to each result row. However, it can also be used to number records in different ways, such as by subsets. Read more 14 May 2020 Ignacio L. Bisso Beginner’s Guide to the SQL Subquery Subqueries are a powerful SQL resource, allowing us to combine data from multiple tables in a single query. In this article, we’ll teach you everything you need to begin using subqueries. Perhaps the simplest definition of a SQL subquery is “A query inside a query”. Subqueries are so easy to understand that they often appear in the opening chapters of SQL courses. However, there are many variants of subqueries that need to be explained. Read more 8 Apr 2020 Ignacio L. Bisso SQL Window Functions vs. GROUP BY: What’s the Difference? A very common misconception among SQL users is that there is not much difference between SQL window functions and aggregate functions or the GROUP BY clause. However, the differences are very significant. Perhaps the only similar point between GROUP BY and window functions is that both allow you to execute a function (such as AVG, MAX, MIN, or COUNT) on a group of records. I’d say that window functions’ special power is that they allow us to obtain results that otherwise would be almost impossible to achieve. Read more 6 Feb 2020 Ignacio L. Bisso HAVING vs. WHERE in SQL: What You Should Know This article is about SQL’s WHERE and HAVING clauses. Both clauses are part of the foundations of the SQL SELECT command. They have similar uses, but there are also important differences that every person who uses SQL should know. Let’s see what’s behind the HAVING vs. WHERE debate. In this article, we’ll suppose we work for a government social agency supporting people or families whose income is below a certain threshold. Read more 8 Oct 2019 Ignacio L. Bisso SQL Order of Operations SQL is not a traditional programming language in which you write a sequence of instructions in a given order of execution. Instead, SQL is a "declarative" language, which means that by writing a SQL query, you declare what data you expect as a result of the query, but you don't indicate how to obtain it. Six Operations to Order: SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY By using examples, we will explain the execution order of the six most common operations or pieces in an SQL query. Read more «« « 1 2 3 4 5 » »»