Get Free Tips
MSSQLTips.com
Home » T-SQL in One Hour
TSQL

T-SQL in One Hour

written by Hadi Fadlallah 0 comments

Problem

Writing effective and precise SQL queries is a basic competency for database administrators and developers. The shift from general SQL concepts to T-SQL mastery is difficult for many students and even seasoned professionals. This article is a part of the “X in one hour” series that I am writing for MSSQLTips. It aims to bridge the gap by providing a concise and hands-on guide to T-SQL fundamentals.

Solution

Microsoft’s SQL extension for SQL Server is called Transact-SQL, or T-SQL. It is an effective language for relational database management and querying. We’ll walk you through the fundamental ideas of T-SQL development in this tip, covering the basics from simple SELECT statements to subqueries and joins.

The AdventureWorks2017 database was used to illustrate each. All screenshots are taken from the SQL Server Management Studio tool.

T-SQL in One Hour
T-SQL in One Hour

SQL was initially named SEQUEL meaning structured English query language. It is a descriptive programming language where developers write a query that describe the output they need from the data. To learn SQL, you should read the query as if it is a simple English query. Basically, the data retrieval query contains at least a SELECT and a FROM clause. The first is to specify the required column(s) and the second is to specify the source table(s).

Basic Query

To begin, let’s retrieve all country names from the AdventureWorks2017 database:

--MSSQLTips.com (SQL)
SELECT Name
FROM Person.CountryRegion;

This query lists the names of all countries stored in the CountryRegion table.

the names of all countries stored in the CountryRegion table

As shown in the screenshot above 238 rows were returned.

To check if the result contains duplicate values we can eliminate the duplicate country names using DISTINCT.

--MSSQLTips.com (SQL)
SELECT DISTINCT Name
FROM Person.CountryRegion;

The DISTINCT keyword ensures that the result contains only unique country names.

The DISTINCT keyword ensures that the result contains only unique country names

Since both queries returned the same number of rows, we are ensured that there are no duplicate values returned in the original query.

Filtering Results

The WHERE clause allows you to apply conditions that filter unwanted rows. For instance, to find customers with names starting with “A”:

--MSSQLTips.com (SQL)
SELECT FirstName, LastName
FROM Person.Person
WHERE FirstName LIKE 'A%';

This returns customers whose first names start with the letter ‘A’.

customers whose first names start with the letter ‘A’.

To exclude rows with NULL values in a specific column we can do this:

--MSSQLTips.com (SQL)
SELECT *
FROM Person.Person
WHERE MiddleName IS NOT NULL;

This is useful when filtering incomplete records.

filtering out incomplete records

The wildcard * in the SELECT clause means that the output should contain all available columns.

Sorting and Limiting Results

You can organize query results with ORDER BY and restrict the number of rows with TOP.

For instance, if you’d like to list employees by the date they were hired:

--MSSQLTips.com (SQL)
SELECT BusinessEntityID, HireDate
FROM HumanResources.Employee
ORDER BY HireDate ASC;

This query arranges employees starting from the earliest hire date.

employees ordered from the earliest to latest hire date

If you only need the first three entries from that sorted list, you can add TOP 3:

--MSSQLTips.com (SQL)
SELECT TOP 3 BusinessEntityID, HireDate
FROM HumanResources.Employee
ORDER BY HireDate ASC;

This way, you’ll see only the three longest-serving employees.

TOP 3 limits the results to only the first three records after sorting

Joining Tables

Very often, the data you need is spread across different tables. A JOIN lets you connect them through a common column.

For example, to display employee names together with their job titles:

--MSSQLTips.com (SQL)
SELECT p.FirstName, p.LastName, e.JobTitle
FROM HumanResources.Employee e
INNER JOIN Person.Person p ON e.BusinessEntityID = p.BusinessEntityID;

This produces a list of employees with their corresponding job titles.

list of employee names alongside their job titles

If you want to include all customers regardless of whether they’re linked to a store, you can use a LEFT JOIN:

--MSSQLTips.com (SQL)
SELECT c.CustomerID, s.Name AS StoreName
FROM Sales.Customer c
LEFT JOIN Sales.Store s ON c.StoreID = s.BusinessEntityID;

That way, customers without a store will still appear, but will show NULL in the StoreName column..

all customers, even those without associated stores

Aggregations and Grouping

SQL also allows you to summarize data using aggregate functions. For example:

--MSSQLTips.com (SQL)
SELECT COUNT(*) AS TotalCustomers,
       AVG(TotalDue) AS AvgDue
FROM Sales.SalesOrderHeader;

This query returns the total number of rows along with the average order value.

the total number of orders and their average amount

To see how much each customer has spent:

--MSSQLTips.com (SQL)
SELECT CustomerID, SUM(TotalDue) AS TotalSpent
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;

Each row represents a customer and the total amount they’ve spent.

customers and the total amount they’ve spent

If you’d like to find customers who have placed more than five orders, you can extend your query with a HAVING clause:

--MSSQLTips.com (SQL)
SELECT CustomerID, COUNT(SalesOrderID) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
HAVING COUNT(SalesOrderID) > 5;
customers with more than five orders

Here, GROUP BY groups the orders by customer, while HAVING filters those groups to keep only the ones with more than five orders.

Subqueries for Complex Conditions

Sometimes a single query isn’t enough, and you need one query to feed into another. These are called subqueries.

Suppose you want to identify customers who placed at least one large order (say, above 1000):

--MSSQLTips.com (SQL)
SELECT CustomerID
FROM Sales.Customer
WHERE CustomerID IN (
    SELECT CustomerID
    FROM Sales.SalesOrderHeader
    WHERE TotalDue > 1000
);

The inner query collects all customers with an order over 1000, and the outer query checks which of those IDs exist in the customer table.

customers with at least one order over 1000

Now, what if you want the opposite. You need to retrieve the customers who never placed an order? That’s where NOT EXISTS is useful:

--MSSQLTips.com (SQL)
SELECT *
FROM Sales.Customer c
WHERE NOT EXISTS (
    SELECT 1 FROM Sales.SalesOrderHeader o
    WHERE o.CustomerID = c.CustomerID
);

This query returns every customer that doesn’t appear in the SalesOrderHeader table.

customers who have never placed an order

Set Operations

T-SQL also gives you tools to compare results between queries. These are UNION, INTERSECT, and EXCEPT.

For instance, to gather all first names that begin with either “A” or “B”:

--MSSQLTips.com (SQL)
SELECT FirstName FROM Person.Person WHERE FirstName LIKE 'A%'
UNION
SELECT FirstName FROM Person.Person WHERE FirstName LIKE 'B%';

UNION combines the two result sets and removes any duplicates. If duplicates are needed, you’d use UNION ALL instead.

retrieve unique names starting with A or B

To keep duplicates we can use UNION ALL instead of UNION.

keeping duplicates

To find names that start with A and end with ‘son’:

--MSSQLTips.com (SQL)
SELECT FirstName FROM Person.Person WHERE FirstName LIKE 'A%'
INTERSECT
SELECT FirstName FROM Person.Person WHERE FirstName LIKE '%son';

INTERSECT returns names that appear in both result sets.

INTERSECT returns names that appear in both result sets

To exclude overlapping names use EXCEPT:

--MSSQLTips.com (SQL)
SELECT FirstName FROM Person.Person WHERE FirstName LIKE 'A%'
EXCEPT
SELECT FirstName FROM Person.Person WHERE FirstName LIKE '%son';

EXCEPT returns names that meet the first condition but not the second.

EXCEPT returns names that meet the first condition but not the second

Inserting and Updating Data

T-SQL allows inserting values directly or selecting from another table.

To insert specific data:

--MSSQLTips.com (SQL)
INSERT INTO HumanResources.Department (Name, GroupName)
VALUES ('Test Department', 'Test Group');
Inserting a data record

Create a subset of customers from the US:

--MSSQLTips.com (SQL)
SELECT * INTO dbo.USCustomers
FROM Sales.Customer c
INNER JOIN Person.Address a ON c.CustomerID = a.AddressID
WHERE a.StateProvinceID IN (SELECT StateProvinceID FROM Person.StateProvince WHERE CountryRegionCode = 'US');
creating a table from a query

To update values using a join:

--MSSQLTips.com (SQL)
UPDATE p
SET EmailPromotion = 1
FROM Person.Person p
INNER JOIN Sales.Customer c ON p.BusinessEntityID = c.PersonID
WHERE c.TerritoryID = 1;

The screenshot below shows that 3428 records were updated.

updating with join

Deleting Unrelated Data

You can use joins to identify and remove irrelevant records. For example, to delete customers with no orders:

--MSSQLTips.com (SQL)
DELETE c
FROM Sales.Customer c
LEFT JOIN Sales.SalesOrderHeader o ON c.CustomerID = o.CustomerID
WHERE o.CustomerID IS NULL;

The screenshot below shows that 701 records were deleted.

delete with join

Next Steps

T-SQL is a robust language for managing SQL Server databases. This tutorial covered the fundamentals in under an hour with practical, real-world examples using AdventureWorks2017. To deepen your learning, we recommend exploring the following articles on MSSQLTips.com:

Copyright (c) 2006-2025 Edgewood Solutions, LLC All rights reserved
sql server 2025 guide