No.2: Mastering SQL Server : Introduction to Transact-SQL

Welcome to the second installment of our SQL Server series. In No.1, we covered installation and environment setup. In this post, No.2, we dive into the core of database manipulation: Transact-SQL Basics.

We will use the ShopDB sample database created in the “Preparation” section. Each step includes detailed explanations to help you understand not just “how” to write the code, but “why” it works.

In this beginner-friendly SQL Server tutorial, you will learn how to use SELECT, WHERE, JOIN, GROUP BY, UNION ALL, and CASE with a sample database called ShopDB.

Who is this for? This guide is designed for SQL Server beginners who can connect to a SQL Server instance with SSMS and want to start writing basic queries.

---

---

STEP 1. Overview of SQL & Setup

In this step, we outline what SQL is and how it interacts with the database engine.

In this step, you will learn:

• What SQL is and how it communicates with the database engine.
• The difference between system databases and user databases.
• How to create, alter, and drop databases and tables.
• How to prepare the ShopDB sample environment used in later steps.

1.1 What is SQL?

SQL (Structured Query Language) is the standard language for communicating with relational databases. Whether you are using SQL Server, Oracle, or MySQL, the core concepts remain the same.

The Basic Flow:

1. Client: Sends a request (SQL Statement) to the server.
2. SQL Server: Processes the request (Searching, Adding, Updating).
3. Client: Receives the result set or confirmation.

Note: Transact-SQL (T-SQL)
While this guide focuses on standard SQL (ANSI-SQL), SQL Server uses its own dialect called T-SQL. T-SQL adds powerful extensions to the standard language, such as variables, error handling, and procedural programming capabilities.

1.2 Environment

Ensure you have SQL Server Management Studio (SSMS) connected to your SQL Server instance.
Please refer to “No.1: Mastering SQL Server : Setup SQL Server” to install SSMS and connect to the SQL Server instance.

1.3 What is a Database? (System vs User)

A database in SQL Server is a structured collection of data. It consists of tables, views, stored procedures, and other objects.

In SQL Server, databases are broadly categorized into two types: System Databases (created by default) and User Databases (created by you).

SQL Server Instance  
   └─ Databases  
       ├─ System Databases  
       │  ├─ master      ── (Stores system-level metadata and configuration settings)  
       │  ├─ tempdb      ── (Provides temporary storage for query processing)  
       │  ├─ model       ── (Template database used for creating new databases)  
       │  └─ msdb        ── (Stores SQL Server Agent jobs and backup history)  
       │
       └─ User Databases  
           ├─ ShopDB     ── (User-defined database for this tutorial)  
           └─ AnotherDB  ── (Other user-created databases)

System Databases Overview:

• master: Maintains system-wide configuration data, instance settings, and login information.
• tempdb: Used for temporary storage, sorting, and internal operations. It is recreated every time SQL Server starts.
• model: Acts as a template for newly created databases. New databases inherit their structure from this.
• msdb: Contains job scheduling data, backup history, and SQL Agent metadata.

1.4 Creating / Deleting / Altering Databases

Now, let’s create a User Database to store our data.

Creating a Database

-- Syntax: CREATE DATABASE [DatabaseName]
CREATE DATABASE ShopDB;

Altering a Database
You can modify database properties, such as its name.

-- Syntax: ALTER DATABASE [OldName] MODIFY NAME = [NewName]
ALTER DATABASE ShopDB MODIFY NAME = StoreDB;

Deleting a Database

-- Syntax: DROP DATABASE [DatabaseName]
DROP DATABASE StoreDB;

1.5 What is a Table? & Creation

A Table is the fundamental unit of storage in SQL Server. It consists of rows and columns where each column represents a specific field (e.g., Name, Price), and each row represents a single record.

Just like databases, tables contain system objects (metadata) and user objects (your data).

User Database (e.g., ShopDB)
    ├─ System Tables  
    │  ├─ sys.objects      ── (Stores metadata about all objects in the database)  
    │  └─ sys.tables       ── (Stores metadata about user-defined tables)  
    │
    └─ User Tables  
        ├─ dbo.Products   ── (Stores product catalog data)  
        ├─ dbo.Customers  ── (Stores customer information)  
        └─ dbo.Orders     ── (Stores order details)

Creating a Table (User Table)
In SQL Server, tables belong to a Schema (default is dbo). You must define the column names and their data types.

CREATE TABLE dbo.Products (
    ProductID int PRIMARY KEY,
    ProductName nvarchar(50),
    Price int
);

Deleting a Table

DROP TABLE dbo.Products;

1.6 Preparation (Sample Script)

For the rest of this tutorial (STEP 2 onwards), we will use a sample database named ShopDB.
Please copy the script below and execute it in your Query Editor (SSMS) to create the environment.

-- 1. Create Database
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'ShopDB')
BEGIN
    CREATE DATABASE ShopDB;
END
GO

USE ShopDB;
GO

-- 2. Create Tables
-- Products Table
IF OBJECT_ID('dbo.Products', 'U') IS NOT NULL DROP TABLE dbo.Products;
CREATE TABLE dbo.Products (
    ProductID int PRIMARY KEY,
    ProductName nvarchar(50),
    Price int,
    CategoryID int
);

-- Categories Table
IF OBJECT_ID('dbo.Categories', 'U') IS NOT NULL DROP TABLE dbo.Categories;
CREATE TABLE dbo.Categories (
    CategoryID int PRIMARY KEY,
    CategoryName nvarchar(50)
);

-- Employees Table
IF OBJECT_ID('dbo.Employees', 'U') IS NOT NULL DROP TABLE dbo.Employees;
CREATE TABLE dbo.Employees (
    EmployeeID int PRIMARY KEY,
    FirstName nvarchar(50),
    LastName nvarchar(50),
    Department nvarchar(50),
    Name nvarchar(100) -- For LIKE search practice
);

-- Customers Table
IF OBJECT_ID('dbo.Customers', 'U') IS NOT NULL DROP TABLE dbo.Customers;
CREATE TABLE dbo.Customers (
    CustomerID int PRIMARY KEY,
    CustomerName nvarchar(50),
    City nvarchar(50),
    PhoneNumber nvarchar(20)
);

-- Orders Table
IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL DROP TABLE dbo.Orders;
CREATE TABLE dbo.Orders (
    OrderID int PRIMARY KEY,
    CustomerID int,
    OrderDate date,
    TotalAmount int
);

-- OrderDetails Table
IF OBJECT_ID('dbo.OrderDetails', 'U') IS NOT NULL DROP TABLE dbo.OrderDetails;
CREATE TABLE dbo.OrderDetails (
    OrderID int,
    ProductID int,
    Quantity int
);

-- SalesData Table (For PIVOT practice)
IF OBJECT_ID('dbo.SalesData', 'U') IS NOT NULL DROP TABLE dbo.SalesData;
CREATE TABLE dbo.SalesData (
    Year int,
    Month int,
    SalesAmount int
);

-- Suppliers Table (For UNION practice)
IF OBJECT_ID('dbo.Suppliers', 'U') IS NOT NULL DROP TABLE dbo.Suppliers;
CREATE TABLE dbo.Suppliers (
    SupplierID int,
    City nvarchar(50)
);

-- Students Table (For INSERT/UPDATE practice)
IF OBJECT_ID('dbo.Students', 'U') IS NOT NULL DROP TABLE dbo.Students;
CREATE TABLE dbo.Students (
    ID int PRIMARY KEY,
    Name nvarchar(50),
    EnrollmentDate date
);
GO

-- 3. Insert Sample Data
INSERT INTO dbo.Categories VALUES (1, 'Computers'), (2, 'Smartphones'), (3, 'Accessories');

INSERT INTO dbo.Products VALUES 
(1, 'Laptop X', 1500, 1),
(2, 'Phone 14', 1200, 2),
(3, 'Tablet Pro', 800, 1),
(4, 'Headphones', 200, 3),
(5, 'Charger', 50, 3);

INSERT INTO dbo.Employees VALUES 
(1, 'John', 'Smith', 'Sales', 'John Smith'),
(2, 'Sarah', 'Connor', 'HR', 'Sarah Connor'),
(3, 'Mike', 'Doe', 'Sales', 'Mike Doe');

INSERT INTO dbo.Customers VALUES 
(1, 'Alice Corp', 'New York', '123-456-7890'),
(2, 'Bob Ltd', 'Los Angeles', NULL), -- NULL for IS NULL practice
(3, 'Charlie Inc', 'New York', '987-654-3210');

INSERT INTO dbo.Orders VALUES 
(101, 1, '2025-01-10', 3000),
(102, 1, '2025-02-15', 500),
(103, 2, '2025-03-20', 1200);

INSERT INTO dbo.OrderDetails VALUES 
(101, 1, 2), -- 2 Laptops
(102, 5, 10), -- 10 Chargers
(103, 2, 1); -- 1 Phone

INSERT INTO dbo.SalesData VALUES 
(2025, 1, 1000), (2025, 2, 1500), (2025, 3, 2000), (2025, 4, 2500);

INSERT INTO dbo.Suppliers VALUES (1, 'Chicago'), (2, 'New York');

PRINT 'Database ShopDB created and data inserted successfully.';

---

STEP 2. Basic Data Retrieval (SELECT)

In this step, you will learn:

• How to retrieve data from tables using the SELECT statement.
• How to sort data using the ORDER BY clause.
• How to perform simple calculations and string concatenation in queries.
• How to eliminate duplicate values with DISTINCT.

2.1 Retrieving Data with SELECT

In SQL, the SELECT statement allows you to retrieve data from tables. The basic syntax is as follows:

SELECT column_name1, column_name2, ... 
FROM table_name;

After SELECT, you specify the column names you want to retrieve, separated by commas. If you specify an asterisk *, it means “all columns,” allowing you to retrieve every column in the table. The WHERE clause (explained in STEP 3) can be omitted, in which case all rows in the table are retrieved.

Important Note: Using SELECT * in a production environment is generally not recommended. If a new column is added to the table in the future, the application might not handle the extra data correctly, potentially leading to errors.

Example: Retrieve specific columns

-- Get only Product Name and Price
SELECT ProductName, Price
FROM dbo.Products;

Example: Retrieve all columns (*)

-- Get all information about Products
SELECT *
FROM dbo.Products;

Example: Retrieve specific columns (Using Aliases with AS)

-- "AS" allows you to rename the column in the output
SELECT ProductName AS Name, Price AS USD
FROM dbo.Products;

2.2 Sorting Data: ORDER BY

The ORDER BY clause is placed at the end of the SELECT statement to specify the sort column and order. For ascending order, use ASC (default); for descending order, use DESC.

By default, the order of data returned by SQL is not guaranteed. Therefore, if you don’t use “ORDER BY”, the order of query result is random. If you want to display the data in a specific order (e.g., higher price first), use the ORDER BY clause at the end of the statement.

-- Sort by Price from High to Low (Descending)
SELECT ProductName, Price
FROM dbo.Products
ORDER BY Price DESC;

You can sort by multiple columns by separating them with commas. When multiple columns are specified, the system first sorts by the first column, and then sorts any ties using the second column.

-- Sort by CategoryID (Ascending) first, then by Price (Descending)
SELECT ProductName, CategoryID, Price
FROM dbo.Products
ORDER BY CategoryID ASC, Price DESC;

You can also specify the sort order using column numbers (ordinals). The column number corresponds to the sequence of columns listed in the SELECT clause (e.g., 1 is the first column, 2 is the second).

-- Sort by the 2nd column (Price) in descending order
SELECT ProductName, Price
FROM dbo.Products
ORDER BY 2 DESC;

2.3 Arithmetic Operators

You can perform calculations directly within the SELECT statement using operators like +, -, *, and /. This is useful for calculating tax or discounts on the fly without modifying the original data.

-- Calculate Price plus 100
-- "AS PricePlus100" gives a temporary name (alias) to the result column
SELECT ProductName, Price, Price + 100 AS PricePlus100
FROM dbo.Products;

-- Calculate Price including 10% Tax
-- "AS PriceWithTax" gives a temporary name (alias) to the result column
SELECT ProductName, Price, Price * 1.1 AS PriceWithTax
FROM dbo.Products;

Here are the most common ways to use arithmetic operators:

Operator	Usage	Description
+	Number + Number	Adds two numbers together
	Date + Number	Adds specified days to a date
	String + String	Concatenates (joins) two strings
–	Number – Number	Subtracts one number from another
	Date – Number	Subtracts specified days from a date
	Date – Date	Calculates the difference between two dates
*	Number * Number	Multiplies two numbers
/	Number / Number	Divides one number by another

2.4 String Concatenation

You can join multiple strings together. For example, joining a “First Name” and “Last Name” column into a single “Full Name” column.

-- Using + operator
SELECT FirstName + ' ' + LastName AS FullName 
FROM dbo.Employees;

-- Using CONCAT function (Recommended for SQL Server as it handles NULLs better)
SELECT CONCAT(FirstName, ' ', LastName) AS FullName 
FROM dbo.Employees;

2.5 Eliminating Duplicates: DISTINCT

Sometimes a table contains duplicate values. DISTINCT is useful when you want to retrieve a list of unique data types from a dataset containing duplicates. For example, multiple customers might live in “New York”. To get a list of unique cities (removing duplicates), use the DISTINCT keyword.

-- Get a list of unique cities where customers live
SELECT DISTINCT City
FROM dbo.Customers;

---

STEP 3. Operators in the WHERE Clause

Filtering Rows: The WHERE Clause
Up to this point, we used the SELECT statement without a WHERE clause, which retrieves all rows. By using the WHERE clause, you can specify conditions to filter the data and retrieve only the specific rows you need.

In this step, you will learn:

• How to filter rows with the WHERE clause.
• How to use comparison operators and logical operators such as AND and OR.
• How to search ranges, multiple values, and patterns using BETWEEN, IN, and LIKE.
• How to correctly work with NULL values using IS NULL.

3.1 Comparison Operators

These operators compare column values against a specific value.

Operator	Description
=	Equal to
>, <	Greater than, Less than
>=, <=	Greater/Less than or equal to
<>	Not equal to

-- Find products with a price of 1000 or more
SELECT * FROM dbo.Products 
WHERE Price >= 1000;

3.2 Logical Operators: AND, OR

You can combine multiple conditions.

• AND: All conditions must be true.
• OR: At least one condition must be true.

-- Find products in Category 1 AND price is less than 2000
SELECT * FROM dbo.Products
WHERE CategoryID = 1 AND Price < 2000;

When AND and OR are used in the same statement, AND is evaluated first. If you want OR to be evaluated first, you can use parentheses () to control the order of evaluation.

-- AND and OR in the same statement (AND is evaluated first)
SELECT * 
FROM dbo.Products
WHERE CategoryID = 1 OR CategoryID = 2 AND Price >= 1000;

-- Change the evaluation order using parentheses
SELECT * 
FROM dbo.Products
WHERE (CategoryID = 1 OR CategoryID = 2) AND Price >= 1000;

3.3 Range Search: BETWEEN

Use BETWEEN to search within a range of values (inclusive).

-- Find products priced between 1000 and 2000
SELECT * FROM dbo.Products
WHERE Price BETWEEN 1000 AND 2000;

3.4 Multiple Values: IN

Use IN to specify multiple possible values for a column.

-- Find employees in either Sales or HR department
SELECT * FROM dbo.Employees
WHERE Department IN ('Sales', 'HR');

3.5 Handling NULL: IS NULL

Crucial Note: In databases, “NULL” represents “unknown” or “no data”. It is not the same as zero or an empty string. The = operator compares one value with another; because NULL is not a value, an expression such as PhoneNumber = NULL evaluates to UNKNOWN and does not return any rows. To check whether a value is NULL, you must use IS NULL.

-- Find customers who do NOT have a phone number registered
SELECT * FROM dbo.Customers
WHERE PhoneNumber IS NULL;

3.6 Pattern Matching: LIKE

Use wildcards for fuzzy searches (partial match).

• % : Represents any string of zero or more characters.
• _ : Represents any single character.
• [ ] : Any single character within the specified range [a-f] or set [abcdef].
• [^] : Any single character not within the specified range [^a-f] or set [^abcdef].

-- Find employees whose name starts with 'S'
SELECT * FROM dbo.Employees 
WHERE Name LIKE 'S%';

-- Find employees whose second character is 'a'
SELECT * FROM dbo.Employees
WHERE Name LIKE '_a%';

-- Find employees whose name starts with A, B, or C
SELECT * FROM dbo.Employees
WHERE Name LIKE '[ABC]%';

-- Find employees whose name does NOT start with letters A to F
SELECT * FROM dbo.Employees
WHERE Name LIKE '[^A-F]%';

To search for a string that actually contains the wildcard characters % or _ themselves, you can use the ESCAPE clause. For example, if you want to search for the literal string '100%', you must escape the % character so that SQL Server does not treat it as a wildcard.

-- Use ESCAPE to search for the literal string '100%'
SELECT '100%' AS SampleText
WHERE '100%' LIKE '100!%' ESCAPE '!';

---

STEP 4. Adding, Updating, and Deleting Data

These commands are collectively known as DML (Data Manipulation Language). Unlike SELECT, these commands modify the actual data in the database.

In this step, you will learn:

• How to insert new rows with INSERT.
• How to modify existing rows with UPDATE.
• How to remove rows with DELETE.
• Why the WHERE clause is critical to avoid unintended data changes.

4.1 Adding Data: INSERT

To add new rows to a table, use the INSERT statement. You specify the target table and the values for each column.

-- Add a new student to the Students table
INSERT INTO dbo.Students (ID, Name, EnrollmentDate)
VALUES (1, 'John Doe', '2025-04-01');

4.2 Updating Data: UPDATE

To modify existing data, use the UPDATE statement.

Warning: The WHERE clause is critical here. If you omit it, all rows in the table will be updated!

-- Change the name of the student with ID 1
UPDATE dbo.Students
SET Name = 'Johnny Doe'
WHERE ID = 1;

4.3 Deleting Data: DELETE

To remove rows from a table, use the DELETE statement. Like UPDATE, if you omit the WHERE clause, all data will be deleted.

-- Delete the student with ID 1
DELETE FROM dbo.Students
WHERE ID = 1;

---

STEP 5. Aggregate Functions and Table Joins

This is where relational databases truly shine. We will look at how to summarize data and how to connect multiple tables together.

In this step, you will learn:

• How to summarize data using aggregate functions such as SUM, AVG, MAX, MIN, and COUNT.
• How to group rows with the GROUP BY clause.
• How to combine data from multiple tables using INNER JOIN and OUTER JOIN.
• How to aggregate data after joining tables.

5.1 Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single value.

• SUM(): Calculates the total.
• AVG(): Calculates the average.
• MAX() / MIN(): Finds the maximum / minimum value.
• COUNT(): Counts the number of rows.

-- Get statistics for Products
SELECT 
    COUNT(*) AS TotalProducts, 
    AVG(Price) AS AveragePrice,
    MAX(Price) AS MostExpensive
FROM dbo.Products;

5.2 Grouping: GROUP BY

The GROUP BY clause is used to arrange identical data into groups. Imagine you have a basket of mixed fruits and you want to count how many apples, bananas, and oranges you have.

• Without GROUP BY: You just count the total fruit (e.g., 100 items).
• With GROUP BY FruitType: SQL sorts them into “Apple”, “Banana”, “Orange” buckets and counts them separately.

-- Calculate the total price of products FOR EACH Category
-- 1. SQL looks at 'CategoryID' column.
-- 2. It groups rows with CategoryID=1 together, CategoryID=2 together, etc.
-- 3. It runs SUM(Price) on each group.
SELECT CategoryID, SUM(Price) AS TotalValue
FROM dbo.Products
GROUP BY CategoryID;

5.3 Multiple Table Joins (Concept)

In a database, data is split into multiple tables (Normalization). For example, “Products” are in one table, and “Categories” are in another. To view them together, we use JOIN.

The concept is similar to zipping two lists together based on a common value (key).

5.4 Inner Join: INNER JOIN

Concept: “The Intersection” (Both sides must match)
An INNER JOIN returns rows only when there is a match in BOTH tables. If a Product has a CategoryID that doesn’t exist in the Categories table, that Product will be hidden.

-- Join Products and Categories
-- Only shows Products that have a valid Category
SELECT P.ProductName, C.CategoryName, P.Price
FROM dbo.Products AS P
INNER JOIN dbo.Categories AS C
ON P.CategoryID = C.CategoryID;

5.5 Grouping and Joins

You can combine JOIN and GROUP BY. First, we join the tables to get readable names, and then we group them to calculate statistics.

-- "Show me the total number of products for each Category Name"
SELECT C.CategoryName, COUNT(P.ProductID) AS ProductCount
FROM dbo.Products AS P
INNER JOIN dbo.Categories AS C
ON P.CategoryID = C.CategoryID
GROUP BY C.CategoryName;

5.6 Outer Join: OUTER JOIN

Concept: “Keep Everything from one side”
Sometimes you want to see ALL customers, even those who haven’t bought anything. An INNER JOIN would hide those “inactive” customers because they have no matching Order.

A LEFT OUTER JOIN keeps all rows from the LEFT table (Customers), and tries to find a match in the right table (Orders). If no match is found, it displays NULL.

-- Show ALL customers. 
-- If they have orders, show the OrderID.
-- If they don't, OrderID will be NULL (but the customer is still listed).
SELECT C.CustomerName, O.OrderID
FROM dbo.Customers AS C
LEFT OUTER JOIN dbo.Orders AS O
ON C.CustomerID = O.CustomerID;

---

STEP 6. UNION ALL and CASE Expression

Here are two more powerful features that are commonly used in real-world SQL Server queries.

In this step, you will learn:

• How to combine result sets from multiple queries using UNION ALL.
• How to categorize data on the fly using the CASE expression.

6.1 UNION ALL

The UNION operator combines the result-set of two or more SELECT statements into a single list.

-- Create a single list of cities from both Customers and Suppliers
SELECT City FROM dbo.Customers
UNION ALL
SELECT City FROM dbo.Suppliers;

6.2 CASE Expression

The CASE expression acts like an “IF-THEN-ELSE” statement inside your SQL query. It allows you to categorize data on the fly.

-- Categorize products based on price range
SELECT ProductName, Price,
    CASE
        WHEN Price < 1000 THEN 'Budget'
        WHEN Price BETWEEN 1000 AND 1400 THEN 'Mid-Range'
        ELSE 'Premium'
    END AS PriceCategory
FROM dbo.Products;

---

STEP 7. Advanced Practice

Finally, let’s look at more complex scenarios often found in real-world reporting.

In this step, you will learn:

• How to join three or more tables to answer complex business questions.
• How to build cross-tab style reports using the PIVOT operator.
• How to simplify complex queries by creating reusable views.

7.1 Joining 3+ Tables

You are not limited to joining two tables. You can chain as many joins as needed. Here, we connect 4 tables to see “Who bought What”.

-- Orders -> Customers -> OrderDetails -> Products
SELECT O.OrderID, C.CustomerName, P.ProductName, D.Quantity
FROM dbo.Orders AS O
JOIN dbo.Customers AS C ON O.CustomerID = C.CustomerID
JOIN dbo.OrderDetails AS D ON O.OrderID = D.OrderID
JOIN dbo.Products AS P ON D.ProductID = P.ProductID;

7.2 Sales Aggregation & Cross Tabulation (PIVOT)

In SQL Server, creating cross-tab reports (like Excel Pivot Tables) is easy with the PIVOT operator. It rotates rows into columns.

-- Summarize SalesAmount by Month (Columns 1 to 4)
SELECT * FROM 
(
    SELECT Year, Month, SalesAmount FROM dbo.SalesData
) AS SourceTable
PIVOT
(
    SUM(SalesAmount)
    FOR Month IN ([1], [2], [3], [4]) -- Specifying months Jan to Apr
) AS PivotTable;

7.3 Views

If you have a complex query that you run often (like the 4-table join above), you can save it as a View. A View acts like a “Virtual Table”.

-- Create the View
CREATE VIEW dbo.V_OrderSummary AS
SELECT C.CustomerName, O.OrderDate, O.TotalAmount
FROM dbo.Customers AS C
JOIN dbo.Orders AS O ON C.CustomerID = O.CustomerID;
GO

-- Now you can query it just like a table!
SELECT * FROM dbo.V_OrderSummary;

---

This concludes the Introduction to Transact-SQL guide.