Writing SQL Queries

This document provides a comprehensive guide to writing SQL (Structured Query Language) queries for data retrieval and manipulation. SQL is the standard language for managing relational databases.

Core Concepts of Querying

At its heart, SQL querying involves selecting data from one or more tables based on specified criteria. The most fundamental statement for this is the SELECT statement.

The SELECT Statement

The SELECT statement is used to query the database and retrieve data that matches criteria you specify. You can select one or more columns from one or more tables.

Basic Syntax:

SELECT column1, column2, ...
FROM table_name;

To select all columns from a table, you can use the asterisk (*) wildcard:

SELECT *
FROM table_name;

Filtering Data with WHERE

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

Syntax:

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

Example: Selecting customers from a specific city

SELECT customer_name, email
FROM customers
WHERE city = 'New York';

Common operators used in the WHERE clause include:

• = : Equal to
• > : Greater than
• < : Less than
• >= : Greater than or equal to
• <= : Less than or equal to
• <> or != : Not equal to
• LIKE : Pattern matching
• IN : Value is in a list
• BETWEEN : Value is between two values

Sorting Results with ORDER BY

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

Syntax:

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

ASC - Ascending order (default)

DESC - Descending order

Example: Sorting products by price

SELECT product_name, price
FROM products
ORDER BY price DESC;

Advanced Querying Techniques

Limiting Results with LIMIT / TOP

To retrieve a limited number of records, you can use the LIMIT clause (common in MySQL, PostgreSQL) or the TOP keyword (common in SQL Server).

Syntax (LIMIT):

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

Syntax (TOP):

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

Distinct Values

The DISTINCT keyword is used to return only unique values.

Syntax:

SELECT DISTINCT column1, column2, ...
FROM table_name;

Working with Aliases

Aliases are used to give a temporary name to a table or a column. This can make queries shorter and more readable, especially when dealing with complex joins.

Column Alias Syntax:

SELECT column_name AS alias_name
FROM table_name;

Table Alias Syntax:

SELECT column_name
FROM table_name AS alias_name;

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 values.
• AVG(): Calculates the average of values.
• MIN(): Finds the minimum value.
• MAX(): Finds the maximum value.

Example: Counting orders

SELECT COUNT(order_id) AS total_orders
FROM orders;

For more detailed information on specific SQL dialects and advanced querying, please refer to the Joins and Aggregate Functions sections.