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.
- Note: For more information about installing the AdventureWorks database, refer to the following article – Install and configure the AdventureWorks2016 sample database
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:
|
1 2 |
SELECT * FROM [HumanResources].[Employee] |
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:
|
1 2 3 |
SELECT [Employee].* FROM [HumanResources].[Employee] |
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:
|
1 2 |
SELECT [LoginID],[Gender] FROM [HumanResources].[Employee] |
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:
|
1 2 |
SELECT e.Gender FROM [HumanResources].[Employee] e |
The previous example uses the alias e for the table Employee. We can also use an alias for the column names like this:
|
1 2 |
SELECT e.Gender g FROM [HumanResources].[Employee] e |
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):
|
1 2 |
SELECT DISTINCT e.Gender g FROM [HumanResources].[Employee] e |
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:
|
1 2 |
SELECT TOP 10 e.[BusinessEntityID], e.Gender g FROM [HumanResources].[Employee] e |
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.
|
1 2 3 |
SELECT [BusinessEntityID] FROM [HumanResources].[Employee] e ORDER BY [BusinessEntityID] desc |
- Note: For more information about select queries, please refer to this link:Learn SQL: SELECT statement
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.
|
1 2 3 |
select [BusinessEntityID], [JobTitle] from [HumanResources].[Employee] e where JobTitle='Design Engineer' |
- In the example, we used the equal operator. For a complete list of T-SQL operators, refer to this link: Logical Operators (Transact-SQL)
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:
|
1 2 3 |
SELECT [BusinessEntityID], [JobTitle] FROM [HumanResources].[Employee] e WHERE JobTitle LIKE 'Design%' |
- For more information about the LIKE operator, refer to this link:SQL Like logical operator introduction and overview
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:
|
1 2 3 |
SELECT [BusinessEntityID],JobTitle FROM [HumanResources].[Employee] e WHERE JobTitle in ('Engineering Manager','Senior Tool Designer') |
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:
|
1 2 |
SELECT SUM([SubTotal]) AS SUBTOTAL,AVG([SubTotal]) AS AVERAGESUBTOTAL FROM [Sales].[SalesOrderHeader] |
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:
|
1 2 3 4 |
SELECT SUM([OrderQty]) AS Qty, [SalesOrderID] FROM [Sales].[SalesOrderDetail] GROUP BY [SalesOrderID] ORDER BY SUM([OrderQty]) DESC |
- For a list of aggregate functions, refer to this link:Aggregate Functions (Transact-SQL)
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.
- We created a special article for these types of joins. For more information, refer to the following link: SQL multiple joins for beginners with examples
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.
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
- PostgreSQL tutorial to create a user - November 12, 2023
- PostgreSQL Tutorial for beginners - April 6, 2023
- PSQL stored procedures overview and examples - February 14, 2023