T-SQL is the query language used by SQL Server and other database products, so knowing how to write T-SQL queries is an important skill for anyone who works with SQL Server databases. Because it's such an important skill, we recently published the T-SQL 101 series by Bill McEvoy.
If you're new to T-SQL, the 10 lessons in the T-SQL 101 series can help you learn the basics, such as how to write queries that retrieve, add, update, and delete records in tables and how to group and aggregate data that you retrieved with queries so that you can produce informative reports. By the end of the series, you'll even be able to create tables, views, and custom stored procedures.
Even if you're familiar with T-SQL, the lessons can come in handy. In fact, Bill admitted that he keeps a copy of Lesson 5 on his desk at work for those "sleepy days" when he can't remember the difference between an INNER JOIN and a RIGHT OUTER JOIN.
In case you just became a SQL Server Magazine subscriber or you didn't get a chance to read the lessons, here are the links to the 10 lessons and a description of what each lesson covers:
- Lesson 1 starts you on the journey by looking at what T-SQL is, the query tools you use with it, and how to use the SELECT statement. The SELECT statement is what you use to get data out of a database and onto your screen.
- Lesson 2 explores how to use the INSERT, UPDATE, and DELETE statements. With these three statements, you can create queries that add, change, and delete data from tables.
- Lesson 3 delves into how to use SELECT statements to aggregate data. Aggregating data simply means bringing data together and summarizing it. This lesson shows you how to use five aggregate functions—COUNT, MIN, MAX, AVG, and SUM—in SELECT queries.
- Lesson 4 gives you the scoop on how to use the GROUP BY clause in SELECT statements. Grouping data lets you produce detailed and informative reports.
- Lesson 5 discusses how to use JOIN clauses to build queries that retrieve data from two related tables. You'll learn about the differences between the five types of JOIN clauses (INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, and CROSS JOIN) and when to use each one.
- Lesson 6 shows you two techniques you can use to create your own tables. The first technique uses the CREATE TABLE command. The second technique uses the SELECT INTO command.
- Lesson 7 demonstrates how to create and query views. Unlike conventional physical tables, views don’t contain data. A view is merely a stored T-SQL statement that generates a result set when executed. When you query a view, you’re really querying the result set of a previously defined query.
- Lesson 8 explores T-SQL's built-in scalar functions. You'll learn how to use the GETDATE, DATENAME, and DATEADD functions to work with dates as well as how to use the LEFT, RIGHT, SUBSTRING, and REPLACE functions to manipulate strings.
- Lesson 9 introduces you to the CASE function, which lets you quickly transform data from one format to another. The CASE function comes in two varieties: simple and searched. You'll learn about their differences and see examples of how to use each one.
- Lesson 10 shows you how to tuck all the T-SQL statements you learned about in Lessons 1 through 9 inside a container known as a stored procedure. You'll learn not only how to create and execute stored procedures but also why you'd want to.
