Essential SQL
1,159 FOLLOWERS
This site is all about learning SQL Server. My goal is to help you understand SQL, so you can take your database skills to the next level!
Essential SQL
2y 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
2y 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
2y 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
2y 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
2y 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
2y 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
2y 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
Essential SQL
2y 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
2y 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
2y 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