Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Advice for Learning T-SQL SELECT Statement Step By Step


By:   |   Read Comments (1)   |   Related Tips: More > T-SQL

Attend these FREE MSSQLTips webcasts >> click to register


Problem

Are there any strategies or advice for learning T-SQL with no prior knowledge?

Solution

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

Hello, World

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.

StateProvince table

The simple statement shown here returns all of the data (rows and columns) from the table.

select *
from AdventureWorks2014.Person.StateProvince

select all rows and columns

Our next statement returns the count of all of the rows in the table.

select count(*) as CountOfRows
from AdventureWorks2014.Person.StateProvince

select the row count

Next, start adding to the complexity of the queries. Select two of the columns.

select StateProvinceCode, Name 
from AdventureWorks2014.Person.StateProvince

select two columns

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'

add a where clause

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 a group by clause

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

add an order by clause

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]

distinct function

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]

Datediff and getdate functions

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.

Next Steps

Check out these other tips and tutorials on T-SQL on MSSQLTips.com.



Last Update:


signup button

next tip button



About the author
MSSQLTips author Dallas Snider Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, February 05, 2015 - 6:41:21 AM - Frank Pence Back To Top

Thank you for the article it is very informative.  What is the difference between T-SQL and just SQL?  I hear about both, but they appear identical.

Thank you,

Frank

** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.


Learn more about SQL Server tools