Microsoft SQL Server - dev-docs

Basic SQL queries and examples for working with Microsoft SQL Server.

→ Official SQL Server T-SQL Documentation

SELECT & WHERE

-- Select specific columns
SELECT FirstName, LastName FROM Employees;

-- Select all columns
SELECT * FROM Employees;

-- Filter with WHERE
SELECT * FROM Employees WHERE Department = 'IT';

-- WHERE with multiple conditions
SELECT * FROM Employees
WHERE Department = 'IT' AND Salary > 50000;

ORDER BY, TOP, LIKE

-- Top 5 highest salaries
SELECT TOP 5 * FROM Employees
ORDER BY Salary DESC;

-- Pattern matching
SELECT * FROM Employees
WHERE FirstName LIKE 'J%';  -- Starts with J

SELECT * FROM Employees
WHERE Email LIKE '%@example.com';  -- Ends with domain

INSERT, UPDATE, DELETE

-- Insert new record
INSERT INTO Employees (FirstName, LastName, Department, Salary)
VALUES ('Alice', 'Smith', 'IT', 70000);

-- Update existing record
UPDATE Employees
SET Salary = 75000
WHERE FirstName = 'Alice';

-- Delete record
DELETE FROM Employees
WHERE FirstName = 'Alice';

CREATE and ALTER TABLE

-- Create new table
CREATE TABLE Departments (
    Id INT PRIMARY KEY,
    Name NVARCHAR(50)
);

-- Alter existing table
ALTER TABLE Employees
ADD HireDate DATE;

-- Drop column
ALTER TABLE Employees
DROP COLUMN HireDate;

Joins

-- INNER JOIN: Only matching records
SELECT e.FirstName, d.Name AS Department
FROM Employees e
INNER JOIN Departments d ON e.DepartmentId = d.Id;

-- LEFT JOIN: All from left table, even if no match
SELECT e.FirstName, d.Name AS Department
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentId = d.Id;

-- RIGHT JOIN: All from right table
SELECT e.FirstName, d.Name AS Department
FROM Employees e
RIGHT JOIN Departments d ON e.DepartmentId = d.Id;

GROUP BY, COUNT, MAX, AVG

-- Count number of employees per department
SELECT DepartmentId, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DepartmentId;

-- Average salary per department
SELECT DepartmentId, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DepartmentId;

-- Maximum salary per job title
SELECT JobTitle, MAX(Salary) AS MaxSalary
FROM Employees
GROUP BY JobTitle;

-- Filter aggregated results with HAVING
SELECT DepartmentId, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DepartmentId
HAVING COUNT(*) > 10;

Subqueries

-- Employees with salary above average
SELECT * FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

-- Latest order per customer
SELECT * FROM Orders o
WHERE OrderDate = (
    SELECT MAX(OrderDate)
    FROM Orders
    WHERE CustomerId = o.CustomerId
);

Other Useful Clauses

-- DISTINCT values
SELECT DISTINCT Country FROM Customers;

-- BETWEEN range
SELECT * FROM Orders
WHERE OrderDate BETWEEN '2024-01-01' AND '2024-12-31';

-- IN clause
SELECT * FROM Employees
WHERE Department IN ('IT', 'HR');

-- IS NULL check
SELECT * FROM Employees
WHERE ManagerId IS NULL;

Indexes & Constraints

-- Create index
CREATE INDEX idx_LastName ON Employees(LastName);

-- Add unique constraint
ALTER TABLE Employees
ADD CONSTRAINT UQ_Email UNIQUE (Email);

-- Add foreign key
ALTER TABLE Employees
ADD CONSTRAINT FK_Department FOREIGN KEY (DepartmentId)
REFERENCES Departments(Id);

Stored Procedures

-- Create procedure
CREATE PROCEDURE GetEmployeesByDept
    @Dept NVARCHAR(50)
AS
BEGIN
    SELECT * FROM Employees
    WHERE Department = @Dept;
END;

-- Execute
EXEC GetEmployeesByDept @Dept = 'IT';

User-Defined Functions

-- Scalar function
CREATE FUNCTION dbo.GetYearOnly (@date DATE)
RETURNS INT
AS
BEGIN
    RETURN YEAR(@date);
END;

-- Use it
SELECT dbo.GetYearOnly(HireDate) FROM Employees;

Transactions

BEGIN TRANSACTION;

UPDATE Accounts SET Balance = Balance - 100 WHERE Id = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE Id = 2;

-- Rollback on error or COMMIT
IF @@ERROR <> 0
    ROLLBACK;
ELSE
    COMMIT;