Learn SQL Query Language on SQL Server

By: Koen Verbeeck | Updated: 2023-03-06 | Comments | Related: > TSQL

Problem

I'm thinking about switching careers and moving into the data world. Writing queries with the SQL query language (SQL) seems integral to a job involving data. But it all seems so overwhelming. What is a good way to learn the fundamentals of how to write in SQL for beginners?

Solution

SQL, short for Structured Query Language, is a powerful programming language used to manage relational databases by Programmers for software development, DBAs for data management and Data Scientists for data analysis. It allows users to create, update, and query data stored in databases. The process of getting started with the language can seem overwhelming. However, with the right resources and dedication, anyone can start learning SQL.

This SQL tutorial will cover some steps to help you get started to build your SQL skills. If you want to play around with some data, you can use the AdventureWorksLT sample database in Azure – see more in Sample database in Azure SQL Database – or the AdventureWorks sample database in SQL Server. You can connect to these databases using SQL Server Management Studio (SSMS).

Here are several ways to get started with SQL.

Step 1: Familiarize Yourself with Databases

Before diving into SQL, it is important to understand what databases are and how they work at a high level. A database is a collection of data organized in a specific way. The data can be stored in tables, like Excel spreadsheets, with columns and rows. Each row in a table represents a single record, while each column represents a specific type of data (such as a number, a date, a piece of text, etc.).

It's possible that one or more columns in a table are related to columns in another table. For example, one table can store data about different purchases made by customers. The customer data itself is stored in another table. In the purchase table, there's a link to the customers table so you can match the different purchases to the customers that made them.

The term "relational" in relational database doesn't come from those relationships between tables. It comes from "relation," the original name for tables in relational algebra. Understanding the basics of databases will make it easier to learn SQL. You can find a high-level overview of databases in the article, What is SQL Server?

Step 2: Choose a Database Management System

SQL is used to manage relational databases, but many different database management systems (DBMS) use SQL, including MySQL, Oracle, Microsoft SQL Server, and PostgreSQL. Choose a DBMS suitable for your needs, and then focus on learning SQL for that specific system. Each system typically has its own "dialect" of SQL, a variation of the official SQL standard where some functions or features are included that are optimized for that specific database. In this tip – and, of course, on this entire website – we use SQL Server as the DBMS or any of its variations in the Azure cloud: Azure SQL DB or Azure SQL Managed Instance. The dialect of SQL used in SQL Server is Transact-SQL, commonly abbreviated to T-SQL.

As mentioned earlier, there are free sample databases you can use, and you can either use the free developer edition for SQL Server or the free trial for Azure.

Step 3: Learn the Basics of SQL

Once you have selected a DBMS, it's time to start learning SQL. You can find SQL tutorials right here at MSSQLTips.com:

Start by learning the syntax of SQL, including keywords, clauses, and commands. Next, learn how to create tables and insert data into them and retrieve data from tables using SELECT statements. All these topics are covered in tutorials, and there are also many tips covering the basic aspects of SQL:

A good tip to get started is SQL Server SELECT Examples, as this tip starts with basic examples and then builds to more complex ones. For example, the first query is a straight-forward SELECT statement, retrieving all data from a table:

SELECT *
FROM [Person].[Person];

And it builds to queries using more elaborate features such as GROUP BY, WHERE, and HAVING:

SELECT
     [FirstName]
    ,COUNT(1) AS RowCnt
FROM [Person].[Person]
WHERE [FirstName] LIKE 'Rob%'
GROUP BY [FirstName]
HAVING COUNT(1) >= 20;

Step 4: Practice, Practice, Practice

The key to mastering SQL is to practice using it. Once you've set up your sample database, try to play around with different SQL commands to get a feel for how they work. Try to write SQL statements to retrieve data based on different conditions and update and delete data. In the previous paragraph, a couple of tips are mentioned to get you started. The key is to write as much SQL as you can. The more you write, the better you'll get.

Free Traffic Signs Place-Name Sign photo and picture

Step 5: Learn More Advanced SQL Concepts

Once you have a solid understanding of SQL basics, it's time to dive into more complex queries. Learn about aggregate functions, subqueries, and join operations, which allow you to combine data from multiple tables. The tip, SQL Server SELECT Examples, also includes those topics. Other great resources are:

Also, learn about indexes, which are used to improve the performance of SQL queries. There's a whole tutorial on indexing, but if you're just starting out with SQL, I recommend sticking to the clustered and non-clustered index types. Once you get a good grip on all of these concepts, you can start with window functions, which are an advanced type of aggregation functions. For example, with the following query, we can calculate Year-over-Year growth using the window function LAG.

WITH CTE_PY AS
(
    SELECT
         [Year]                         = YEAR([OrderDate])
        ,[Sales Amount]                 = SUM([SalesAmount])
        ,[Sales Amount Previous Year]   = LAG(SUM([SalesAmount]))
                                            OVER (ORDER BY YEAR([OrderDate]))
    FROM [dbo].[FactResellerSales]
    GROUP BY YEAR([OrderDate])
)
SELECT
     [Year]
    ,[Sales Amount]
    ,[Sales Amount Previous Year]
    ,[YoY Growth]                   = 100.0 * ([Sales Amount] - [Sales Amount Previous Year])
                                        / [Sales Amount Previous Year]
FROM [CTE_PY]
ORDER BY [Year];

Step 6: Get Hands-on Experience

Once you have a good understanding of SQL, it's time to get some hands-on experience. Try to find a real-world project you can work on or participate in a hackathon or coding competition. This will give you the opportunity to apply all the skills you've learned and to work with other people who are also interested in SQL. Some websites even have interesting puzzles you can try to solve with SQL. For example, on the website of Pinal Dave, you can find some puzzles.

Step 7: Stay Up to Date

SQL is constantly evolving, so it's important to stay up to date with the latest features and best practices. Follow blogs and forums related to SQL and database management and participate in online communities to ask questions and share your knowledge. An easy method to stay current is to subscribe to the MSSQLTips.com newsletter. You'll get an overview of the latest articles.

Conclusion

Learning SQL requires a combination of studying, practicing, and hands-on experience. Start with the basics, and then gradually build up your knowledge. Stay motivated and persistent, and you will be able to master SQL in no time. Good luck!

Next Steps

There's a wealth of information about SQL-related topics on MSSQLTips.com. You can find all the categories here.
You can find the T-SQL tips here. Make sure also to browse the tutorials, webcasts, and whitepapers.

About the author

Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2023-03-06

How to Start Learning the SQL Query Language on SQL Server