Transact-SQL Quick Reference

Transact-SQL (T-SQL) is Microsoft's proprietary extension to SQL. This reference covers the most commonly used statements and functions in SQL Server 2000.

Data Definition

-- Create a table
CREATE TABLE Employees (
    EmployeeID  int           IDENTITY(1,1) PRIMARY KEY,
    FirstName   varchar(50)   NOT NULL,
    LastName    varchar(50)   NOT NULL,
    Email       varchar(100)  NULL,
    HireDate    datetime      NOT NULL DEFAULT GETDATE(),
    Salary      money         NULL,
    DeptID      int           NULL REFERENCES Departments(DeptID)
)

-- Add a column
ALTER TABLE Employees ADD Phone varchar(20) NULL

-- Create an index
CREATE INDEX IX_Employees_LastName ON Employees (LastName)

-- Create a unique constraint
ALTER TABLE Employees ADD CONSTRAINT UQ_Email UNIQUE (Email)

Data Manipulation

-- Insert
INSERT INTO Employees (FirstName, LastName, Email, DeptID)
VALUES ('John', 'Smith', 'jsmith@corp.flamenet.io', 1)

-- Update
UPDATE Employees SET Salary = 55000 WHERE EmployeeID = 1

-- Delete
DELETE FROM Employees WHERE EmployeeID = 1

-- Select with JOIN
SELECT e.FirstName, e.LastName, d.DeptName
FROM Employees e
INNER JOIN Departments d ON e.DeptID = d.DeptID
WHERE e.HireDate >= '2001-01-01'
ORDER BY e.LastName

-- Aggregate functions
SELECT DeptID, COUNT(*) AS EmpCount, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DeptID
HAVING COUNT(*) > 5

Common Functions