MSDN Documentation

SQL Query Concepts

This document provides a comprehensive overview of the fundamental concepts behind constructing and executing SQL (Structured Query Language) queries. Understanding these concepts is crucial for effectively interacting with relational databases.

What are SQL Queries?

SQL queries are commands used to retrieve, manipulate, and manage data stored in relational databases. They form the backbone of data access for a vast array of applications.

Core Components of a SQL Query

Most SQL queries follow a structured pattern, typically involving the following clauses:

• SELECT: Specifies the columns you want to retrieve from the database.
• FROM: Indicates the table(s) from which to retrieve the data.
• WHERE: Filters the records based on specified conditions.
• GROUP BY: Groups rows that have the same values in specified columns into summary rows.
• HAVING: Filters groups based on a specified condition (used in conjunction with GROUP BY).
• ORDER BY: Sorts the result set in ascending or descending order.

Basic Query Structure

A simple query to retrieve data might look like this:

SELECT column1, column2
FROM table_name
WHERE condition;

Filtering Data with WHERE

The WHERE clause is essential for selecting specific rows that meet certain criteria. You can use various operators:

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

Example using WHERE

SELECT ProductName, Price
FROM Products
WHERE Price > 50 AND Category = 'Electronics';

Joining Tables

Often, data is spread across multiple tables. JOIN clauses are used to combine rows from two or more tables based on a related column.

• INNER JOIN: Returns records that have a match 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 left or right table.

Example using INNER JOIN

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

Aggregate Functions

SQL provides aggregate functions to perform calculations on a set of rows and return a single value.

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

Example using Aggregate Functions and GROUP BY

SELECT Category, COUNT(ProductID) AS NumberOfProducts, AVG(Price) AS AveragePrice
FROM Products
GROUP BY Category
HAVING COUNT(ProductID) > 5
ORDER BY Category;

Data Manipulation Language (DML)

Beyond retrieving data, SQL also allows for modifying it:

• INSERT: Adds new records to a table.
• UPDATE: Modifies existing records in a table.
• DELETE: Removes records from a table.

Example using INSERT

INSERT INTO Customers (CustomerName, ContactName, Email)
VALUES ('New Corp', 'Jane Doe', 'jane.doe@newcorp.com');