Common SQL Window Functions: Ranking Functions

Want to learn how to use SQL window functions?  Ranking functions are a good place to start! Learning about SQL window functions usually comes after you’ve built a foundation in the language, but these powerful functions take your skills up a level. As you master them, you’ll find better ways to solve query problems. When used for business intelligence applications, SQL queries combine data retrieval and advanced computations. These operations are more complex than those used in OLTP systems. In

Continue Reading

Useful SQL Patterns: Date Generator

As you start coding in SQL, you will use some statements and techniques over and over again. We call these “SQL patterns”. This series will look at the most common SQL patterns and consider how to use them. SQL patterns, such as the pivot pattern we discussed last week, can save you a lot of time and effort. Suppose you are asked to get a range of days in a financial quarter, but the only records you have are for

Continue Reading

Useful SQL Patterns: Pivoting

As you start coding in SQL, you will use some statements and techniques over and over again. We call these “SQL patterns”. This series will look at the most common SQL patterns and consider how to use them. Pivoting in SQL refers to taking the data in table rows and making that data into columns. This is very important in reporting, and it’s easy to do when you use the CASE statement. In fact, you will see that pivoting data

Continue Reading

Useful SQL Patterns: Conditional Summarization with CASE

As you start coding in SQL, you will use some statements and techniques over and over again. We call these “SQL patterns”. This series will look at the most common SQL patterns and consider how to use them. Previously, we looked at the SQL pattern of matching NULLs. This is important when you are comparing columns containing NULL values. Today, we’re going to consider another SQL practice: conditional summarization with CASE operator. What is Conditional Summarization? When you are using

Continue Reading

Useful SQL Patterns: Matching Nulls by Masking Nulls

As you start coding in SQL, you will use some statements and techniques over and over again. We call these “SQL patterns”. This series will look at the most common SQL patterns and consider how to use them. In database development, SQL developers often find themselves returning to the same SQL statements. Learning about these now, early in your SQL journey, will help you work more efficiently. Today, in the first post of this series, we will consider the match

Continue Reading

Oracle Top-N Queries for Absolute Beginners

It’s common to run a query using only part of a dataset – for example, the top 100 salespeople in a company. In this article, we’ll see how to use Oracle’s Top-N query method to query just these rows. Top-N queries retrieve a defined number of rows (top or bottom) from a result set. In other words, they find the best or worst of something – the ten best selling cars in a certain region, the five most popular routers,

Continue Reading

GET ACCESS TO EXPERT SQL CONTENT!