How to Round Up to Nearest Integer
Essential SQL
by Kris Wenzel
1y 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
Visit website
Calculate Moving Median in SQL
Essential SQL
by Kris Wenzel
1y 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
Visit website
How to Get a Date 30 Days into the Future using SQL
Essential SQL
by Kris Wenzel
1y 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
Visit website
How to do an Impossible Join with String Split
Essential SQL
by Kris Wenzel
1y 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
Visit website
Contains in SQL
Essential SQL
by Kris Wenzel
1y 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
Visit website
What are the Top Ten Databases?
Essential SQL
by Kris Wenzel
1y 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
Visit website
SQL WHERE LIKE
Essential SQL
by Kris Wenzel
1y ago
In this article you’ll learn to use the LIKE operator in your SQL. In most situations you’ll find yourself using LIKE within the WHERE clause to filter data using patterns such as all values beginning with “S.” Using this phrase allows us perform partial matches of data values and obtain answers to questions which can’t be done with conventional comparisons. The lesson’s objectives are to: learn about the LIKE match condition understand wild cards Important! Please follow along and do the examples in your database.  If you haven’t already done so, sign up for my Guid ..read more
Visit website
How do I use the Like Clause in SQL Server?
Essential SQL
by Kris Wenzel
1y 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
Visit website
SQL WHERE – Guide and Examples
Essential SQL
by Kris Wenzel
1y 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
Visit website
Use SQL ORDER BY to Sort Results
Essential SQL
by Kris Wenzel
1y 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
Visit website

Follow Essential SQL on FeedSpot

Continue with Google
Continue with Apple
OR