T-SQL Fundamentals

Transact-SQL (T-SQL) is Microsoft's extension of SQL, the standard language for relational databases. T-SQL adds procedural programming features, error handling, and SQL Server-specific functionality on top of standard SQL.

---

Creating a Database and Table

Before querying, let us set up a sample database:

-- Create a database
CREATE DATABASE BookStore;
GO

-- Switch to the new database
USE BookStore;
GO

-- Create a table
CREATE TABLE Books (
    BookId    INT IDENTITY(1,1) PRIMARY KEY,
    Title     NVARCHAR(200)     NOT NULL,
    Author    NVARCHAR(100)     NOT NULL,
    Genre     NVARCHAR(50),
    Price     DECIMAL(8,2)      NOT NULL,
    Published DATE,
    InStock   BIT               DEFAULT 1
);
GO

---

INSERT — Adding Data

-- Insert a single row
INSERT INTO Books (Title, Author, Genre, Price, Published)
VALUES ('The Pragmatic Programmer', 'David Thomas', 'Technology', 49.99, '2019-09-20');

-- Insert multiple rows
INSERT INTO Books (Title, Author, Genre, Price, Published)
VALUES
    ('Clean Code', 'Robert C. Martin', 'Technology', 39.99, '2008-08-01'),
    ('Dune', 'Frank Herbert', 'Science Fiction', 14.99, '1965-08-01'),
    ('1984', 'George Orwell', 'Dystopian', 12.99, '1949-06-08'),
    ('The Art of SQL', 'Stephane Faroult', 'Technology', 34.99, '2006-03-01');

---

SELECT — Reading Data

Basic Queries

-- Select all columns
SELECT * FROM Books;

-- Select specific columns
SELECT Title, Author, Price FROM Books;

-- Filter with WHERE
SELECT Title, Price FROM Books WHERE Genre = 'Technology';

-- Sort results
SELECT Title, Price FROM Books ORDER BY Price DESC;

-- Alias columns
SELECT Title AS BookTitle, Price AS CostGBP FROM Books;

Filtering Operators

Operator	Example
=	WHERE Genre = 'Technology'
<> or !=	WHERE Genre <> 'Fiction'
>, <, >=, <=	WHERE Price >= 20.00
BETWEEN	WHERE Price BETWEEN 10 AND 50
IN	WHERE Genre IN ('Technology', 'Science Fiction')
LIKE	WHERE Title LIKE '%SQL%'
IS NULL / IS NOT NULL	WHERE Published IS NOT NULL
AND / OR	WHERE Price > 20 AND Genre = 'Technology'

---

TOP and OFFSET-FETCH

TOP

Return a limited number of rows:

-- First 3 rows
SELECT TOP 3 Title, Price FROM Books ORDER BY Price DESC;

-- Top 10 percent
SELECT TOP 10 PERCENT Title, Price FROM Books ORDER BY Price DESC;

-- TOP with ties
SELECT TOP 3 WITH TIES Title, Price FROM Books ORDER BY Price DESC;