PostgreSQL
Recommended Articles:
- Database Design In Four Steps – Build the Sample PizzaDB
- Database Normalization – in Easy to Understand English
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
-
Understanding SQL COUNT and Other Aggregate Function
We have seen how to use the SQL Count function in a GROUP BY query, but did you know there are several other functions you can use there as well? In this tutorial, let’s look at several other SQL aggregate functions, and how they can be used to summarize your data. For these examples, we’ll
-
Understanding SQL GROUP BY: A Guide to Data Summarization
The SQL Group BY clause is used to summarize your SQL results. Rather than displaying each row in detail, when GROUP BY is added to your query, one row per grouping is returned. Using groups comes in handy when you want to calculate totals. All of the examples in this tutorial are based on PizzaDB.
-
SQL Like
The SQL LIKE operator is used to find specific patterns or values within your data. With its wildcard characters and versatile syntax, you can craft queries that match your precise needs. Whether you are searching for names that start with a certain letter, addresses that contain a specific word, or numbers within a specific range,
-
SQL Conditional Operators
In this lesson we going learn how to use multiple SQL conditional operators with the WHERE clause. You may know of these as logical operators, since they return either TRUE or FALSE. In the past lesson you learned that rows failing to meet the WHERE clause condition are filtered out. The same concept holds to
-
SQL Where Clause
In this lesson we’ll learn how to use the SQL WHERE clause to filter your results. In prior lessons we have talked about using the SELECT statement to return one or more columns, but always it returned every row. What if we want to return a limited set of rows? How can we filter our
-
Select Distinct SQL Tutorial
The SELECT DISTINCT statement is used to return unique rows based on combination of values in the specified columns. When working with data, it’s often helpful to identify unique values rather than displaying duplicates. That’s where the DISTINCT keyword comes in handy! By adding DISTINCT to your SELECT statement, you can remove duplicate records and
-
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
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
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
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
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
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
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
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?
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.
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
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
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
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
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
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
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 HAVING: Filtering Summary Results in SQL
Use SQL HAVING to filter summary results from a GROUP BY. It specifies the search condition for the group or aggregate. SQL HAVING is only used with SELECT. It is mostly used when a GROUP BY is present, if one isn’t there is an implicit single aggregated group. For this example we’ll use the CustomerOrderDetail
-
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
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 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