Daniel Calbimonte

SQL Queries in SQL Server – a beginner’s guide

December 15, 2021 by

Introduction

Creating SQL Queries is a straightforward process. This article is made in SQL Server, but most of the content can be applied to Oracle, PostgreSQL, MySQL, MariaDB and other databases with few changes. The SQL queries allow us to send queries to a database. In this article, we will have a fast, practical tutorial about doing your own queries from scratch.

What are the SQL queries?

SQL stands for Structured Query Language. It is the language used by the databases to get the information. We will learn how to query using the SQL language.

SQL Queries in SQL Server

The basis of a query in SQL Server is the SELECT sentence which allows to select the data to be displayed. To start with this, we will use the AdventureWorks database that contains sample tables and views which will allow us to have the same tables and data. We will also be able to work with multiple tables already created.

SQL Queries and the Select Sentence

SQL Queries and the Select Sentence

Let’s start with the SELECT sentence, the select sentence will allow us to get data from a table.

The following query will show all the columns from a table:

Try to use the SELECT statement in one line and the FROM statement in a different line. It is easier to read that way. Select * means to show all the columns from a table. Another way to do the same is the following example:

The square brackets are optional. They could help if the column names have spaces (which is not recommended). You can also select specific column names like this:

The previous example shows the loginid and gender column. As you can see, the data is separated by commas. You can also use aliases to have a shorter name like this:

The previous example uses the alias e for the table Employee. We can also use an alias for the column names like this:

The column alias for Gender is now g. The next example will show the 2 different possible values in the gender column (Male or Female):

Note that DISTINCT is a slow command and if the table has several millions of rows, it could take time to execute and could bog down performance.

Another example is the TOP clause. This clause is used in SQL Server and not used in other databases like Oracle or MySQL. The following example will show the first 10 rows of the table:

If we want to order data by a column, the order by is very useful. The following example will show how to show the BusinessEntityID sorted in descending order.

SQL queries to filter data using the WHERE command

SQL queries to filter data using the WHERE command

The where command is one of the most common clauses used inside the SELECT command. This clause allows filtering data. The following example shows how to check the BusinessEntityID of the employees whose job title is Design Engineer.

Another powerful operator is the LIKE. Like, can help us in a search. The following example shows the BusinessEntityID and the Job Title of the employees whose titles start with Design:

The IN operator is a very common operator also, the following example will show all the employees whose JobTitle are equal to Engineering Manager or Senior Tool Designer:

SQL Queries with aggregate functions and the use or the group by statement

SQL Queries with aggregate functions and the use or the group by statement

In the SQL queries, we need the SUM of the rows, the Average, and other aggregations functions. These functions are often used with the group by and the having statements.

The first example will show the SUM and the average of the subtotal of the SalesOrderHeader tables:

The next example is showing how to get the sum of the orderQty column and the salesorderid from the salesorderdetail table. We are grouping the information by salesorderid and ordering the sum in descendant order:

SQL Queries to get data from multiple tables

One of the most important features of the tables is that you can query multiple tables in a single query. To do that we use the JOINS. There are several types of JOINS. INNER JOIN, OUTER JOIN, LEFT JOIN, RIGHT JOIN. The different types of joins allow to JOIN tables in a different way.

Conclusion

In this article, we learned the SQL queries used in SQL Server to get data. We just saw the basics, but T-SQL is a complex job that requires a lot of knowledge to have a good performance. However, we learned the most basic and useful queries.

Daniel Calbimonte
Latest posts by Daniel Calbimonte (see all)
Development, T-SQL

About Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams. He also helps with translating SQLShack articles to Spanish View all posts by Daniel Calbimonte

7,646 Views