SQL Tutorial

Welcome to the Microsoft SQL Server documentation. This tutorial will guide you through the fundamental concepts and operations of SQL (Structured Query Language), empowering you to manage and query relational databases effectively.

Introduction to Relational Databases

Relational databases store data in tables, which consist of rows and columns. Each table represents an entity, and each row represents a record. Columns define the attributes of the entity. SQL is the standard language used to interact with these databases.

Basic SQL Commands

The core of SQL functionality lies in its commands, often categorized as Data Definition Language (DDL) and Data Manipulation Language (DML).

Data Definition Language (DDL)

DDL commands are used to define and manage database structures:

CREATE DATABASE: Creates a new database.
CREATE TABLE: Creates a new table within a database.
ALTER TABLE: Modifies the structure of an existing table.
DROP TABLE: Deletes a table.
DROP DATABASE: Deletes a database.

Data Manipulation Language (DML)

DML commands are used to manipulate the data within database tables:

SELECT: Retrieves data from one or more tables.
INSERT: Adds new rows of data to a table.
UPDATE: Modifies existing data in a table.
DELETE: Removes rows of data from a table.

The SELECT Statement

The SELECT statement is the most frequently used SQL command. It allows you to retrieve specific columns and rows from your database.

Example: Retrieving All Columns and Rows

To retrieve all data from a table named Customers:

SELECT *
FROM Customers;

Example: Retrieving Specific Columns

To retrieve only the FirstName and Email columns from the Customers table:

SELECT FirstName, Email
FROM Customers;

Filtering Data with WHERE Clause

The WHERE clause is used to filter records based on specified conditions. It is typically used with SELECT, UPDATE, and DELETE statements.

Example: Filtering Customers by City

To select customers who live in 'London':

SELECT CustomerName, ContactName
FROM Customers
WHERE City = 'London';

You can use various operators in the WHERE clause, including:

Comparison operators: =, <, >, <=, >=, <> (or !=)
Logical operators: AND, OR, NOT
Special operators: BETWEEN, LIKE, IN, IS NULL

Sorting Data with ORDER BY

The ORDER BY clause sorts the result set in ascending or descending order. It is used with the SELECT statement.

Example: Sorting Customers by Country

To select customers and sort them alphabetically by country:

SELECT CustomerName, City, Country
FROM Customers
ORDER BY Country ASC;

Use DESC for descending order.

Joining Tables

Databases often store related data in multiple tables. JOIN clauses are used to combine rows from two or more tables based on a related column between them.

Common JOIN Types:

INNER JOIN: Returns records that have matching values in both tables.
LEFT JOIN (or LEFT OUTER JOIN): Returns all records from the left table, and the matched records from the right table.
RIGHT JOIN (or RIGHT OUTER JOIN): Returns all records from the right table, and the matched records from the left table.
FULL JOIN (or FULL OUTER JOIN): Returns all records when there is a match in either the left or the right table.

Example: INNER JOIN

To retrieve customer names and their corresponding order dates:

SELECT Customers.CustomerName, Orders.OrderDate
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single value. Common aggregate functions include:

COUNT(): Counts the number of rows.
SUM(): Calculates the sum of a numeric column.
AVG(): Calculates the average value of a numeric column.
MIN(): Finds the minimum value in a column.
MAX(): Finds the maximum value in a column.

Example: Counting Customers

To count the total number of customers:

SELECT COUNT(CustomerID)
FROM Customers;

Example: Average Order Amount

To calculate the average order amount:

SELECT AVG(OrderAmount) AS AverageOrder
FROM Orders;

GROUP BY Clause

The GROUP BY clause groups rows that have the same values in specified columns into summary rows. It is often used with aggregate functions.

Example: Counting Orders per Customer

To count how many orders each customer has placed:

SELECT CustomerID, COUNT(OrderID) AS NumberOfOrders
FROM Orders
GROUP BY CustomerID;

HAVING Clause

The HAVING clause is used to filter groups based on a specified condition. It is similar to the WHERE clause, but it applies to groups rather than individual rows.

Example: Customers with More Than 5 Orders

To find customers who have placed more than 5 orders:

SELECT CustomerID, COUNT(OrderID) AS NumberOfOrders
FROM Orders
GROUP BY CustomerID
HAVING COUNT(OrderID) > 5;

Conclusion

This tutorial covered the essential SQL concepts. Continue exploring the SQL documentation for advanced topics like subqueries, views, stored procedures, indexes, and database design best practices.