MSDN Documentation

Introduction to SQL Programming

SQL (Structured Query Language) is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS). It is used to operate databases, which include creating, deleting, modifying, and retrieving data.

Transact-SQL (T-SQL)

Transact-SQL (T-SQL) is Microsoft's proprietary extension to SQL used with Microsoft SQL Server. T-SQL adds procedural programming, local variables, various support functions for data processing, character string processing, date processing, etc., that are not available in the standard SQL specification.

Common Data Types

SQL supports a wide range of data types to store different kinds of information. Some common types include:

• INT, BIGINT, SMALLINT, TINYINT: For integer numbers.
• DECIMAL, NUMERIC: For exact numeric values with fixed precision and scale.
• FLOAT, REAL: For approximate floating-point numbers.
• VARCHAR, NVARCHAR: For variable-length character strings.
• CHAR, NCHAR: For fixed-length character strings.
• DATE, TIME, DATETIME, DATETIME2: For date and time values.
• BIT: For boolean values (0 or 1).
• VARBINARY, BINARY: For variable or fixed-length binary data.

SQL Operators

SQL operators are used to perform operations on values. They include:

• Arithmetic operators: +, -, *, /, %
• Comparison operators: =, >, <, >=, <=, <>, !=
• Logical operators: AND, OR, NOT
• String operators: + (concatenation in T-SQL), LIKE, %, _

Querying Data

The core of SQL programming involves retrieving data from databases.

SELECT Statement

Used to fetch data from a database. You can select one or more columns:

SELECT column1, column2
FROM table_name
WHERE condition;

FROM Clause

Specifies the table from which to retrieve the data.

WHERE Clause

Filters records based on a specified condition.

SELECT *
FROM Customers
WHERE Country = 'Germany';

GROUP BY Clause

Groups rows that have the same values in specified columns into summary rows.

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;

HAVING Clause

Used to filter groups based on a specified condition, similar to WHERE but for groups.

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;

ORDER BY Clause

Sorts the result set in ascending or descending order.

SELECT *
FROM Customers
ORDER BY Country ASC, City DESC;

Data Manipulation Language (DML)

INSERT Statement

Used to add new records to a table.

INSERT INTO table_name (column1, column2)
VALUES (value1, value2);

UPDATE Statement

Used to modify existing records in a table.

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

DELETE Statement

Used to remove records from a table.

DELETE FROM table_name
WHERE condition;

Data Definition Language (DDL)

CREATE TABLE Statement

Used to create new tables in a database.

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype
);

ALTER TABLE Statement

Used to modify an existing table structure (add, delete, or modify columns).

ALTER TABLE table_name
ADD column_name datatype;

DROP TABLE Statement

Used to delete an existing table.

DROP TABLE table_name;

Stored Procedures

A stored procedure is a prepared SQL statement or set of SQL statements that can be compiled and stored in a database. They can accept input parameters and return multiple values as output parameters. They improve performance and security.

Functions

Functions are similar to stored procedures but typically return a single value. They can be scalar-valued (return a single value) or table-valued (return a table).

Indexes

Indexes are special lookup tables that the database search engine can use to speed up data retrieval operations. Creating an index on one or more columns of a table builds a data structure that allows the query optimizer to find rows more quickly.

Transactions

A transaction is a sequence of database operations performed as a single logical unit of work. Transactions ensure data integrity and consistency through properties known as ACID (Atomicity, Consistency, Isolation, Durability).

BEGIN TRANSACTION;
-- SQL statements here
COMMIT TRANSACTION;
-- Or ROLLBACK TRANSACTION; if an error occurs

Database Security

SQL security involves controlling access to data and operations. This includes user authentication, authorization, granting and revoking permissions on database objects.