SQL Query Documentation

Welcome to the comprehensive guide for SQL queries on the Microsoft Developer Network (MSDN). This documentation covers everything from basic query syntax to advanced data manipulation techniques.

Introduction to SQL

Structured Query Language (SQL) is a standard language for managing and manipulating databases. It is used to communicate with a database. SQL is used to perform tasks such as updating data in a database, or retrieving data from a database.

Basic Queries

SELECT Statement

The SELECT statement is used to select data from a database. The data returned is stored in a result table called the result-set.

SELECT column1, column2, ...
FROM table_name;

To select all columns in a table, use the asterisk (*):

SELECT *
FROM table_name;

FROM Clause

The FROM clause specifies the table from which to retrieve data.

WHERE Clause

The WHERE clause is used to extract only those records that fulfill a specified condition.

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example:

SELECT CustomerName, Country
FROM Customers
WHERE Country = 'USA';

ORDER BY Clause

The ORDER BY clause is used to sort the result-set in ascending or descending order.

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column1 ASC|DESC;

ASC - Ascending order (default)
DESC - Descending order

LIMIT Clause

The LIMIT clause restricts the number of records returned by a SELECT statement. (Syntax may vary slightly between SQL dialects, e.g., TOP in SQL Server).

SELECT column1, column2, ...
FROM table_name
LIMIT number;

Example:

SELECT CustomerName
FROM Customers
LIMIT 5;

Data Manipulation Language (DML)

INSERT Statement

The INSERT INTO statement is used to add new records to a table.

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

If you are adding values to all columns of the table, you may not need to specify the column names. However, this is not recommended, as it can make the code difficult to read and is prone to errors if the table structure changes.

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

UPDATE Statement

The UPDATE statement is used to update existing records in a table.

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

Important: Be careful with the UPDATE statement! A missing WHERE clause will update all records in the table.

DELETE Statement

The DELETE statement is used to delete existing records in a table.

DELETE FROM table_name
WHERE condition;

Important: A missing WHERE clause will delete all records in the table.

Advanced Queries

JOIN Operations

JOIN is used to combine rows from two or more tables based on a related column between them.

• 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.
• RIGHT JOIN: Returns all records from the right table, and the matched records from the left table.
• FULL OUTER JOIN: Returns all records when there is a match in either the left or right table.

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name = table2.column_name;

Example (INNER JOIN):

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

Aggregate Functions

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

• COUNT(): Returns the number of rows.
• AVG(): Returns the average value of a numeric column.
• SUM(): Returns the total sum of a numeric column.
• MIN(): Returns the minimum value of a column.
• MAX(): Returns the maximum value of a column.

SELECT COUNT(CustomerID)
FROM Customers;

GROUP BY Clause

The GROUP BY clause groups rows that have the same values in specified columns into summary rows, like "find the number of customers in each country".

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

HAVING Clause

The HAVING clause is used to filter groups based on a specified condition. It is used with GROUP BY.

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

Subqueries

A subquery (or inner query or nested query) is a query within another SQL query.

SELECT column_name
FROM table_name
WHERE column_name IN (SELECT column_name FROM table_name WHERE condition);

Example:

SELECT CustomerName
FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Orders);

Data Definition Language (DDL)

CREATE TABLE

The CREATE TABLE statement is used to create a new table in a database.

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

ALTER TABLE

The ALTER TABLE statement is used to modify an existing table structure.

-- Add a column
ALTER TABLE table_name
ADD column_name datatype;

-- Drop a column
ALTER TABLE table_name
DROP COLUMN column_name;

DROP TABLE

The DROP TABLE statement is used to delete a table from a database.

DROP TABLE table_name;