PostgreSQL

Recommended Articles:

 

If you already have PostgreSQL installed on your computer or in the cloud, then be sure to get the script for PizzaDB so you can try those samples as well.

Latest Posts

  • Master the Art of SQL Query Validation: Best Practices and Essential Tips

    ·

    Master the Art of SQL Query Validation: Best Practices and Essential Tips

    Are you looking to master the art of SQL query validation? Look no further! In this article, we will share the best practices and essential tips to help you become a pro at ensuring the accuracy and reliability of your SQL queries. SQL query validation is a critical step in the development process to prevent

  • Master the Art of Creating Tables in SQL: A Step-by-Step Guide to Database Design

    ·

    Master the Art of Creating Tables in SQL: A Step-by-Step Guide to Database Design

    Are you ready to unlock the power of SQL and become a master of database design? Look no further! In this comprehensive step-by-step guide, we will take you on a journey through the art of creating tables in SQL. Whether you are a beginner or an experienced programmer, this guide will provide you with the

  • Use ROW_NUMBER to Create a Unique ID

    ·

    Use ROW_NUMBER to Create a Unique ID

    Learn how to use the ROW_NUMBER() windows function to generate a unique ID. Problem: You have a table named Teams with information about various sports teams, including their names, points, and conferences. You want to add a unique identifier to each row in the table to track them easily, but you don’t have a unique

  • Compare Two Moving Averages in SQL
    ,

    ·

    Compare Two Moving Averages in SQL

    Problem We want to compare two moving averages in SQL. We’ll use window functions to do this, as it is easier write and manage the query using them. Background Moving averages are a commonly used tool in financial analysis that smooths out the price data by averaging the closing prices of a stock over a

  • Find and Remove Duplicates in SQL
    , ,

    ·

    Find and Remove Duplicates in SQL

    Problem You want to remove duplicates in SQL. You know it is easy to find duplicates using GROUP BY, but how do your remove all but one? Background Here’s an example of how to find duplicate records using a window function in SQL: Suppose we have a table of students that includes columns for student

  • ,

    ·

    How to Find Duplicates using a Windows Function?

    Problem You need to find duplicates and then identify the records by their primary keys. Background Here’s an example of how to find duplicate records using a window function in SQL: Suppose we have a table of students that includes columns for student ID, name, and age. We want to find all the students who

  • Calculate a Moving Average in SQL using A Windows Function

    ·

    Calculate a Moving Average in SQL using A Windows Function

    Problem How do you create a moving average using windows functions in SQL? Background There is no built-in function to create a moving average, but you can use the AVG() window function to do so.  For our example we calculate the 4 week moving average of weekly sales.  Here is the table we’ll use: Note: 

  • Calculate a Running Total in SQL using a Windows Function
    ,

    ·

    Calculate a Running Total in SQL using a Windows Function

    Problem How do you create a running total on a column using SQL? Background There is no built-in function to create a running total, but you can use a windows function to do so. In this case well construction a running total of Weekly Product Sales. For this solution we’ll work with the WinCode WeeklySales

  • Working with ROW_NUMBER, RANK, and DENSE_RANK

    ·

    Working with ROW_NUMBER, RANK, and DENSE_RANK

    Introduction Window functions are an essential tool for data analysis, particularly in the SQL language. They allow you to perform complex calculations, like running totals, moving averages, and ranking values, on a set of data, without the need for subqueries or self-joins. In this article, we’ll explore three popular ranking functions in SQL: ROW_NUMBER(), RANK(),

  • Window Functions vs Group By Queries?
    ,

    ·

    Window Functions vs Group By Queries?

    In this article let’s look at window functions vs GROUP BY queries. We’ll do so by looking at how each statement works and giving examples. Though you can use both GROUP BY and window functions to create summary values, the way SQL includes the results are different. Let’s first look at how GROUP BY does

  • Concatenate Text with SQL and how to protect from NULL.

    ·

    Concatenate Text with SQL and how to protect from NULL.

    In this article I’ll show you three ways you can use SQL to concatenate columns into a single expression.  You’ll see as we go through this exercise that CONCAT_WS comes out on top for handling NULL the best! The examples are based on the PizzaDB, git it here! The goal is to combine the FirstName

  • How to Round Up to Nearest Integer

    ·

    How to Round Up to Nearest Integer

    Problem You want to round up to the nearest integer. Background Suppose you have a list of final sales amount that you want to round to make your sales analysis easier.  For this example, we’ll use data from the PizzaDB sample database.  Here is the query you can use with MS SQL, PostgeSQL, and MySQL

  • How to Avoid SQL Divide by Zero

    ·

    How to Avoid SQL Divide by Zero

    Problem You need to avoid SQL divide by zero errors. Suppose you need to calculate velocity using the formula v = s / t Where v = velocity, s = distance, and t = time.  Here is the sample data we’ll use. select 100 s, 10 t union all select 110 s, 11 t union all select 2 s, 0 t union all select 10 s, 1 t union all select 120 s, 20 t Line Three is going to cause us issues,

  • How to Subtract 30 Days from a Date using SQL

    ·

    How to Subtract 30 Days from a Date using SQL

    Problem Given a date, you want to subtract 30 days in the past using SQL. Said another way, get the date thirty days in the past. We’ll show you to do this using MySQL, PostgreSQL, and SQL Server.  Each uses a different approach, so be sure to read each solution to gain an appreciation of

  • How to Find the Week Number in PostgreSQL, MySQL, and SQL Server

    ·

    How to Find the Week Number in PostgreSQL, MySQL, and SQL Server

    Problem You need to figure out the week number from date, assuming Monday is the first day of the week using PostgreSQL, MySQL, or SQL Server. Background To work through this problem, we’ll use orders for smelt pizza.  For each order date we’ll calculate the week number in PostgreSQL. Our example is based on the

  • Replace using PostgreSQL

    ·

    Replace using PostgreSQL

    Problem You want to replace all occurrences of a substring with a new substring using PostgreSQL, MySQL, or SQL Server. Background The owners of the PizzaDB shops are changing their drinks from Coke to Pepsi.  Let’s help them see what the changes would be, before they make them permanent. We’ll write a query show them

  • Database Design In Four Steps – Build the Sample PizzaDB

    ·

    Database Design In Four Steps – Build the Sample PizzaDB

    In this article we are going to design and create a sample relational database you can use with MySql, PostgreSQL or Microsoft SQL Server.  Though SQL is mostly the same within these products, I’ll be sure to point out differences so you can easily follow along using your favorite DBMS (Database Management System). In our

  • SQL Joins – The Ultimate Guide

    ·

    SQL Joins – The Ultimate Guide

    SQL Joins are important to master. As you progress from a beginner to advanced beginner, you’ll soon need to combine data from more than one table. To do this, you’ll one of several SQL join types.  In this series of articles I’ll show you how to write a query that combines, or joins, data from

  • Data Modeling Principles in Action
    ,

    ·

    Data Modeling Principles in Action

    In this puzzle, we’re going to learn how to do some basic data modeling.  Many of you have expressed an interest in learning more about data modeling and database design.  I figure we could start with a simple case study and let it evolve from there. Solving puzzles is a great way to learn SQL. 

  • Database Normalization – in Easy to Understand English

    ·

    Database Normalization – in Easy to Understand English

    Database normalization is a process used to organize a database into tables and columns. There are three main forms: first normal form , second normal form, and third normal form. The main idea is each table should be about a specific topic and only supporting topics included. Take a spreadsheet containing the information as an