Advice for Learning T-SQL SELECT Statement Step By Step
Are there any strategies or advice for learning T-SQL with no prior knowledge?
In this tip, I will share my opinions on how someone can start learning T-SQL from the beginning with no prior knowledge. I will draw from my personal experiences in learning for myself and teaching others.
1. Treat T-SQL as you would any other programming language. The "L" in T-SQL means language. In programming classes we start with a simple example, usually with a "Hello, World" program that simply outputs a message to the screen. The T-SQL shown below will output a message to the screen in SQL Server Management Studio.
select 'Hello, World!' as Greeting
The best way to begin is to start with the SELECT statement. The SELECT statement can be considered a "read-only" operation so you shouldn't delete or change any data unexpectedly.
2. Take baby steps. As you are learning T-SQL, take small steps to make comprehension easier. Start with a small table and a simple statement. In the AdventureWorks2014 sample database from Microsoft, there is a table in the Person schema named StateProvince that has 181 rows and 8 columns. The columns in the table are shown below.
The simple statement shown here returns all of the data (rows and columns) from the table.
select * from AdventureWorks2014.Person.StateProvince
Our next statement returns the count of all of the rows in the table.
select count(*) as CountOfRows from AdventureWorks2014.Person.StateProvince
Next, start adding to the complexity of the queries. Select two of the columns.
select StateProvinceCode, Name from AdventureWorks2014.Person.StateProvince
Add a WHERE clause to restrict the rows returned to those in the United States.
select StateProvinceCode, Name from AdventureWorks2014.Person.StateProvince where CountryRegionCode='US'
Use the GROUP BY clause to get a count of states/provinces by country/region code.
select CountryRegionCode, count(*) as CountByCountryRegionCode from AdventureWorks2014.Person.StateProvince group by CountryRegionCode
Add an ORDER BY clause to sort the results from the previous query by the count, descending.
select CountryRegionCode, count(*) as CountByCountryRegionCode from AdventureWorks2014.Person.StateProvince group by CountryRegionCode order by CountByCountryRegionCode desc
3. Try as much as you can with one table. Introducing the joining of two tables should be done once you are confident in your abilities to query one table. I won't go into joins here, but not doing them correctly can result in no rows returned or more rows returned than expected. This can be discouraging to a beginner. Once you are familiar with the SELECT statement, then move on to the UPDATE, INSERT and DELETE statements.
4. Do not be afraid to experiment.There are differing data types in this table which will allow you to experiment with a variety of functions. The first function we will see here is the DISTINCT function.
select distinct (ModifiedDate) from [Person].[StateProvince]
The GETDATE() function returns the current date and time and the DATEDIFF function as shown here returns the number of days between the ModifiedDate and the current date.
select distinct ModifiedDate, getdate() as CurrentDateTime, datediff(day,ModifiedDate, getdate()) as ElapsedDays from [Person].[StateProvince]
5. Do not get discouraged. Learning the syntax and capabilities of a new language can be time consuming. Learning a new language can also be frustrating when the output is incorrect or if you get errors. Don't give up and keep on trying, because knowledge of T-SQL is the foundation of working with SQL Server.
Check out these other tips and tutorials on T-SQL on MSSQLTips.com.
About the author
View all my tips