Back to Basics: SQL Server Query Basics

Hello and welcome back to another post!

To continue with the Back to Basics series of posts, today I’m going to talk about query basics. How to properly form a query, what every part of the query is, and what they do.

QUERY BASICS

The basic query to retrieve data is formed by SELECT statement, a set of columns and a FROM clause. Let’s see a few examples and go step by step on what those are doing. I’m going to be using the Northwind database, if you don’t have it and want to follow along, you can find it here.

SELECT * FROM Products;

So, what everything does?

• SELECT – This statement will tell the engine what columns you want. We start our queries with SELECT followed by a list of column names. In this case, you see a star (*) there.
• The * ( star ) – The star will tell the engine that you want all the columns available in the table. Doesn’t matter if it’s one, two or three hundred. Bring. Them. All.
• FROM – this is where SQL Server will grab the columns you asked for. This could be a table, a view, derived table, temporary table, a table variable, etc. The from is almost always required, except when you are not pulling data from a table but from a variable, arithmetic expressions, etc.

Let’s look at some more examples:

--Select a subset of columns
SELECT 
   ProductID
,  ProductName
,  UnitPrice 
FROM 
   Products;

--Select a literal value
SELECT 'Hello world!'
SELECT 120.50

-- An arithmetic expression
SELECT 12 * 4

Here’s the results, in the same order the queries are written. Let’s analyze them.

The first query we asked SQL Server for three columns only, and that’s exactly what it retrieved. The order in which you define the columns matters only for presentation, it doesn’t matter if your tables had the columns in a different order.

The second query is using the SELECT statement to print the “Hello world!” message and the number 120.50, you see here we didn’t need to use the FROM since we didn’t want anything from any table.

The third query is just multiplying 12 times 4. You can use T – SQL as a calculator when needed.

FILTERING DATA

We already learned how to form a basic query, but those queries we did only returned the full set of data. What if we need to see just a subset of records depending on a condition? That’s where we need the WHERE clause.

The WHERE clause, as its name implies, will tell the engine that you only need data where the condition you tell it, is met. For example, if you just want to know the product name of the ProductID equals to 1, you would do:

-- Get the product name of product id 1
SELECT 
   ProductName 
FROM 
   Products 
WHERE 
   ProductID = 1;

And, here’s the result to that:

If you see, SQL Server did exactly what I told it to do. I told it to retrieve the ProductName from the Products table, but I ONLY wanted to see the ProductID equals to 1.

But dude, I want to see the ProductName and the Price of products 1, 2, 3, 4 and 5! How can I do this? Well, for this type of conditions we could do it in a couple of different ways. We’d still need the WHERE clause for both. Let’s see.

-- Get the product name and price of product ids 1, 2, 3, 4 and 5
SELECT 
   ProductID
,  ProductName
,  UnitPrice 
FROM 
   Products 
WHERE 
   ProductID IN (1,2,3,4,5);

Here’s the results to that.

You see something new here? The IN operator. What it does is,  helps us to check whether a value is within a list of values. In our case, we wanted to see the ProductIDs from 1 through 5, and according to the results that’s what we have here.

I included the ProductID as part of the result set just to verify that the ProductIDs returned were the ones we told SQL Server to retrieve.

Now, what if you want to see the ProductName and the UnitPrice of products which name starts with the letter “C”? How can we do that? Let’s check it out, for this we would need a different operator.

-- Get the product name and unit price of products which names start with the letter C
SELECT 
   ProductName
,  UnitPrice 
FROM 
   Products 
WHERE 
   ProductName LIKE 'C%';

This is the output we get, and if you analyze the query above, you’ll find two new parts. First, we are using the LIKE operator to filter out characters, and we see how the letter C has the % sign, that is called a wild card. That wild card is telling SQL Server that, what we are looking for has a C at the beginning of the word and any other character or string after it.

We can use this wildcard to search anything withing a string. For example:

• %C% – means, that we are looking for a word that contains the letter C, no matter where it is located.
• %C – is that we want to see words or strings that ends with the letter C.

ORDERING DATA

After we learned how to select columns from a table, and filter out data we don’t want. We will need to sometimes sort the data, and to do this, T – SQL has its own clause, the ORDER BY.

ORDER BY tell SQL Server exactly what column you need your data sorted on. For better understand how this works, let’s see an example. Suppose that you bought several products, and you want to know which is the more expensive. Let’s go to the query.

-- Which of my products is the most expensive
SELECT 
   ProductName
,  UnitPrice 
FROM 
   Products 
WHERE 
   ProductID IN (15, 28, 38, 55, 77, 12, 47, 22, 39, 10) 
ORDER BY 
   UnitPrice DESC;

Analyze the query for a bit, see what’s new and let’s see the results.

See how we included the ORDER BY clause and defined how we wanted to sort the data by giving it the column name, in this case the UnitPrice and we also included the word DESC which means we want the data in DESCENDING order.

WRAP UP AND BEST PRACTICES

So far we’ve seen how to write basic queries, selecting only the columns we want, filtering data out by giving a condition to the query and sorting the results the way we need. In future posts I will talk about aggregates, grouping and more.

Other things worth mentioning is about best practices. I mean, every body has their own best practices regarding naming conventions, indenting, etc. It doesn’t really matter as long as you are consistent. If you notice, I follow a few rules of my own, that I think are good and I’ve seen different people using them as well. Let me show you a few of them in the query below.

-- Few best practices I follow
SELECT 
   ProductName
,  UnitPrice 
FROM 
   Products
WHERE
   ProductID IN (1,2,3,4,5)
ORDER BY
   UnitPrice DESC;

• Upper casing all reserved words – SELECT, FROM, WHERE, ORDER BY
• Type every column in a different line
• If just one column is selected, or a single value, I leave it in the same line as the SELECT
• Start every new line for a column with a comma
• Always end every query or statement with a semicolon
• I try to always use column names in PascalCase unless the naming convention is different

I’m going to stop right here, obviously there a lot more things that I would like to show you, but I want these series of posts short and to the point. In future posts I’ll be talking more about more advanced queries, aggregates, grouping and more. But for now, I hope you liked the post and have found it useful.

As usual, you can find the code of the post in my GitHub. Don’t forget to share and like, and also if you want to receive my weekly news letter, please subscribe in the form located at the lateral bar.

Thanks for reading, see you soon, cheers 🍻!