SQL & T-SQL Beginner's Guide

2. SQL Server Data Types

SQL Server data types grouped by category.

3. SQL Commands Categories

SQL commands are grouped into 5 categories.

4. Constraints

Constraints enforce rules on data in tables.

• PRIMARY KEY: Uniquely identifies each row.
• FOREIGN KEY: Links two tables.
• UNIQUE: Ensures all values are unique.
• NOT NULL: Column cannot be NULL.
• CHECK: Ensures condition is true.
• DEFAULT: Assigns a default value.

CREATE TABLE Employees (
  EmpID INT PRIMARY KEY,
  Name NVARCHAR(50) NOT NULL,
  Email NVARCHAR(100) UNIQUE,
  Age INT CHECK (Age >= 18),
  DeptID INT FOREIGN KEY REFERENCES Departments(DeptID),
  JoinDate DATE DEFAULT GETDATE()
);
        

5. DDL (Data Definition Language) Commands

DDL commands are used to define and modify the structure of database objects such as tables, schemas, and more.

• CREATE: Creates a new table, view, or other database object.
• ALTER: Modifies an existing database object, such as adding or dropping a column.
• DROP: Deletes an existing database object from the database.
• TRUNCATE: Removes all rows from a table, but keeps its structure for future use.

CREATE TABLE Departments (
  DeptID INT PRIMARY KEY,
  DeptName NVARCHAR(50)
);

ALTER TABLE Departments
ADD Location NVARCHAR(100);

DROP TABLE Departments;

TRUNCATE TABLE Departments;

6. CRUD Operations

Insert, read, update, and delete records.

-- Insert
INSERT INTO Employees VALUES (1, 'Alice', 'a@example.com', 25, 1, DEFAULT);

-- Select
SELECT * FROM Employees;

-- Update
UPDATE Employees SET Age = 26 WHERE EmpID = 1;

-- Delete
DELETE FROM Employees WHERE EmpID = 1;
        

7. Joins

Combine rows from multiple tables. Joins are used to retrieve data from two or more tables based on a related column.

• INNER JOIN: Returns records that have matching values in both tables.
• LEFT JOIN: Returns all records from the left table, and the matched records from the right table. Unmatched right table records are NULL.
• RIGHT JOIN: Returns all records from the right table, and the matched records from the left table. Unmatched left table records are NULL.
• FULL JOIN: Returns all records when there is a match in either left or right table. Unmatched records from both tables are NULL.
• CROSS JOIN: Returns the Cartesian product of both tables (every row of the first table with every row of the second table).
• NATURAL JOIN: Returns records with matching column values in both tables, based on all columns with the same name.

SELECT e.Name, d.DeptName
FROM Employees e
INNER JOIN Departments d ON e.DeptID = d.DeptID;

SELECT e.Name, d.DeptName
FROM Employees e
LEFT JOIN Departments d ON e.DeptID = d.DeptID;

SELECT e.Name, d.DeptName
FROM Employees e
RIGHT JOIN Departments d ON e.DeptID = d.DeptID;

SELECT e.Name, d.DeptName
FROM Employees e
FULL JOIN Departments d ON e.DeptID = d.DeptID;

SELECT e.Name, d.DeptName
FROM Employees e
CROSS JOIN Departments d;

SELECT e.Name, d.DeptName
FROM Employees e
NATURAL JOIN Departments d;

8. Set Operations

Set operations combine the results of two or more SELECT queries. The tables must have the same number and type of columns.

• UNION: Combines the result sets of two queries and removes duplicates.
• UNION ALL: Combines the result sets of two queries and includes duplicates.
• INTERSECT: Returns only the rows that are present in both queries.
• EXCEPT: Returns rows from the first query that are not present in the second query.

SELECT Name FROM Employees
UNION
SELECT DeptName FROM Departments;

SELECT Name FROM Employees
UNION ALL
SELECT DeptName FROM Departments;

SELECT Name FROM Employees
INTERSECT
SELECT DeptName FROM Departments;

SELECT Name FROM Employees
EXCEPT
SELECT DeptName FROM Departments;

10. Filtering Concepts

Filtering allows you to retrieve only the rows that match specific criteria using WHERE, LIKE, IN, BETWEEN, and more.

SELECT * FROM Employees WHERE Age > 25;

SELECT * FROM Employees WHERE Name LIKE 'A%';

SELECT * FROM Employees WHERE DeptID IN (1, 2, 3);

SELECT * FROM Employees WHERE Age BETWEEN 20 AND 30;

11. Subquery

A subquery is a query nested inside another query. It can be used in SELECT, INSERT, UPDATE, or DELETE statements.

SELECT Name FROM Employees
WHERE DeptID = (SELECT DeptID FROM Departments WHERE DeptName = 'HR');

SELECT Name FROM Employees
WHERE DeptID IN (SELECT DeptID FROM Departments WHERE Location = 'NY');

SELECT Name FROM Employees e
WHERE Age = (SELECT MAX(Age) FROM Employees WHERE DeptID = e.DeptID);

12. View Concept

A view is a virtual table based on the result of a SELECT query. It can simplify complex queries and enhance security.

CREATE VIEW EmployeeNames AS
SELECT Name FROM Employees;

CREATE VIEW EmpDept AS
SELECT e.Name, d.DeptName
FROM Employees e
JOIN Departments d ON e.DeptID = d.DeptID;

13. Index Concepts

Indexes improve the speed of data retrieval operations on a table at the cost of additional space and maintenance.

CREATE CLUSTERED INDEX idx_emp_id
ON Employees(EmpID);

CREATE NONCLUSTERED INDEX idx_emp_name
ON Employees(Name);

CREATE UNIQUE INDEX idx_emp_email
ON Employees(Email);

14. Functions

SQL functions perform operations on data and return a value. They can be used in SELECT, WHERE, and other clauses.

• Scalar Functions: Return a single value (e.g., UPPER(), LEN(), GETDATE()).
• Aggregate Functions: Operate on a set of values and return a single value (e.g., COUNT(), SUM(), AVG(), MIN(), MAX()).
• String Functions: Manipulate string values (e.g., CONCAT(), SUBSTRING(), REPLACE()).
• Date Functions: Work with date and time values (e.g., GETDATE(), DATEADD(), DATEDIFF()).
• Conversion Functions: Convert data from one type to another (e.g., CAST(), CONVERT()).

SELECT UPPER(Name) FROM Employees;

SELECT AVG(Age) AS AvgAge FROM Employees;

SELECT CONCAT(Name, ' - ', Email) FROM Employees;

SELECT GETDATE() AS Today, DATEDIFF(year, JoinDate, GETDATE()) AS YearsWithCompany FROM Employees;

SELECT CAST(Age AS VARCHAR) + ' years' AS AgeText FROM Employees;

SELECT Name, Age * 12 AS AgeInMonths, Salary + 1000 AS NewSalary
FROM Employees
WHERE Age > 25 AND Salary < 50000;

15. Stored Procedure

A stored procedure is a saved collection of SQL statements that can be executed as a program. It can accept parameters, contain logic, and return results.

CREATE PROCEDURE GetEmployees
AS
BEGIN
  SELECT * FROM Employees;
END;

EXEC GetEmployees;

CREATE PROCEDURE UpdateEmployeeSalary
  @EmpID INT,
  @Increment INT
AS
BEGIN
  UPDATE Employees
  SET Salary = Salary + @Increment
  WHERE EmpID = @EmpID;

  SELECT * FROM Employees WHERE EmpID = @EmpID;
END;

EXEC UpdateEmployeeSalary @EmpID = 1, @Increment = 2000;

16. IF...ELSE

IF...ELSE is used to execute code conditionally in T-SQL. It helps you run different SQL statements based on a condition.

IF EXISTS (SELECT * FROM Employees WHERE Age < 18)
  PRINT 'There are underage employees.';
ELSE
  PRINT 'All employees are adults.';

17. Exception Handling (TRY...CATCH)

TRY...CATCH is used to handle errors in T-SQL. Code in the TRY block runs first; if an error occurs, control moves to the CATCH block.

BEGIN TRY
  -- Code that might cause an error
  INSERT INTO Employees VALUES (NULL, 'Bob', 'b@example.com', 22, 1, DEFAULT);
END TRY
BEGIN CATCH
  PRINT 'An error occurred: ' + ERROR_MESSAGE();
END CATCH;

18. Cursor

A cursor lets you process each row returned by a query one at a time. Useful for row-by-row operations.

DECLARE emp_cursor CURSOR FOR
SELECT Name FROM Employees;

OPEN emp_cursor;
DECLARE @empName NVARCHAR(50);

FETCH NEXT FROM emp_cursor INTO @empName;
WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT @empName;
  FETCH NEXT FROM emp_cursor INTO @empName;
END

CLOSE emp_cursor;
DEALLOCATE emp_cursor;

19. Triggers

A trigger is a special procedure that runs automatically when an event (INSERT, UPDATE, DELETE) happens on a table.

CREATE TRIGGER trg_AfterInsert
ON Employees
AFTER INSERT
AS
BEGIN
  PRINT 'A new employee was added.';
END;

20. Transactions & ACID Properties

A transaction is a group of SQL statements that are executed together. ACID properties ensure data reliability:

• Atomicity: All steps succeed or none do.
• Consistency: Data remains valid before and after the transaction.
• Isolation: Transactions do not affect each other.
• Durability: Once committed, changes are permanent.

BEGIN TRANSACTION;
UPDATE Employees SET Age = Age + 1 WHERE EmpID = 1;

IF @@ERROR <> 0
   ROLLBACK;
ELSE
   COMMIT;

21. Normalization

Normalization is the process of organizing data to reduce redundancy and improve data integrity. Data is split into related tables.

• 1NF (First Normal Form): Each column contains only atomic (indivisible) values, and each row is unique.
• 2NF (Second Normal Form): In 1NF, and all non-key columns are fully dependent on the whole primary key (no partial dependency).
• 3NF (Third Normal Form): In 2NF, and all columns are only dependent on the primary key (no transitive dependency).
• 4NF (Fourth Normal Form): In 3NF, and there are no multi-valued dependencies (no column contains two or more independent sets of data).
• 5NF (Fifth Normal Form): In 4NF, and every join dependency is implied by candidate keys (table cannot be further decomposed without losing data).

-- Example: Splitting employee and department info
CREATE TABLE Departments (
  DeptID INT PRIMARY KEY,
  DeptName NVARCHAR(50)
);

CREATE TABLE Employees (
  EmpID INT PRIMARY KEY,
  Name NVARCHAR(50),
  DeptID INT FOREIGN KEY REFERENCES Departments(DeptID)
);

22. Denormalization

Denormalization is the process of combining tables to improve read performance, even if it means some data is repeated.

-- Example: Combining employee and department info in one table
CREATE TABLE EmployeeInfo (
  EmpID INT PRIMARY KEY,
  Name NVARCHAR(50),
  DeptID INT,
  DeptName NVARCHAR(50)
);