Essential SQL
by Kris Wenzel
8M ago
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 to get to the data: select LastName, OrderDate, FinalOrderPrice from CustomerOrderSummary where OrderDate = '2022-03-01' What is the easiest way to round these values up to the nearest integer?  Meaning, if the price is 14.36, you want to round up to 15. Solution to Round up to N ..read more
Essential SQL
by Kris Wenzel
8M ago
Recently a student asked me how he could calculate a Moving Median.  It’s not as easy as you think, as SQL doesn’t provide a built-in MEDIAN() function, nor an easy way calculate the “moving” aspect. In this article we’ll calculate a Moving median using SQL Server.  Let’s use the PizzaDB sample database for example data. SQL Moving MEDIAN Like a moving average, a moving median is based on a window of rows with “moves” or slide down the result set.  As you look at the diagram below, you’ll see we’re calculating a five-row moving median. In row Six we’re able to calculate the med ..read more
Essential SQL
by Kris Wenzel
8M ago
How to Get a Date 30 Days into the Future. Problem You want to calculate a date 30 days into the Future using SQL.  We’ll use the AdventureWorks Production.Product table for our example: select ProductID, Name, SellStartDate from Production.Product where productID in(1, 770, 927) Let’s add thirty days to select Product SellStartDates. Solution Use the SQLSERVER DATEADD() function to add 30 days to today’s date. select ProductID, name, SellStartDate, dateadd(day,30,SellStartDate) SellStart30Days from Production.Product where productID in(1, 770, 927) Here is the result. Notice SellSta ..read more
Essential SQL
by Kris Wenzel
8M ago
In this article I’ll show you how to use STRING_SPLIT() to join two tables.  I call this the impossible join. I’ve come across this situation several times and always when dealing with data imported from two different sources.  In most case it has to do with users inputting data inconsistently. Let me show you. Table of contents Watch the Video The Impossible Join The Impossible Join Approach How STRING_SPLIT() Works Using STRING_SPLIT() with INNER JOIN Conclusion Watch the Video Don’t forget to check out the video. I go over the examples and provide more back ground for you to be ..read more
Essential SQL
by Kris Wenzel
8M ago
Sooner or later, you want to know when a column contains in SQL another value.  In this article we’ll go over several ways you can test to see whether one value is contained within an another. What makes this problem different, is that we aren’t looking for an exact match, rather, we’re looking for a value within another. Consider the production description: “Mountain Bike with lightweight frame yet rugged enough to tackle the toughest trails.” What can we use in SQL to test whether the description contains the word “frame?” Table of contents Contains in SQL Video Lesson Use LIKE and ..read more
Essential SQL
by Kris Wenzel
8M ago
Everyone has their favorite DBMS, is your favorite one of the top databases? Though some non-relational products have entered the top ten, the relational paradigm remains firmly entrenched in the database world.  However specialized database types such document store databases and wide column stores are gaining in popularity to become top databases. Top of the Pack (Scores above 900) There three top runners:  Oracle, MySQL, and Microsoft SQL server.  The scores for these three were all above 1000 and clearly separated from the rest of the pack.  The “big three” are kind of ..read more
Essential SQL
by Kris Wenzel
8M ago
Essential SQL
by Kris Wenzel
8M ago
In this video we’ll walk you though how to use the LIKE clause.   We’ll show you how to you can perform partial matches, such as being able to match all names beginning with “B” or ending in “Y.”  We’ll also show you how to use use ranges.  For instance you may want select all names beginning with “A” “B” or “C”. Once you have watched the video check out the sample code below.  I’ve also included a transcript for you to use. Transcript about the LIKE Clause Hey, this is Kris with another SQL Minute. In this episode I want to talk about the LIKE Operator and how you ca ..read more
Essential SQL
by Kris Wenzel
8M ago
In today’s lesson, you’re going to learn how to filter query results using the SQL WHERE clause.  This clause is important as only those records matching the where clause’s conditions are returned in the query results. The objectives of today’s lesson are to: Learn about various condition types, such as Equality, Range, and Membership Comprehensive example with Select, Where, and Order By Successfully complete the exercises Important! Please follow along and do the examples in your database.  If you haven’t already done so, sign up for my Guide to Getting Started with SQL ..read more
Essential SQL
by Kris Wenzel
8M ago
The SQL ORDER BY clause is used to sort your query result in ascending or descending order. Once you have written a query, you’ll naturally want to reorder the results. You can do so using the ORDER BY clause. SQL ORDER BY is versatile. Use the ORDER BY keyword to sort results with a SELECT statement.  You can sort over multiple columns, in both ascending and descending order.  Table of contents Introduction to SQL ORDER BY SQL ORDER BY Two Columns SQL ORDER BY Descending (DESC)  ORDER BY Expression (Calculation) Get TOP Results Additional Resources Import ..read more

OR