Querying Data in SQL

This section covers the fundamental concepts and syntax for retrieving data from your SQL databases. The primary statement used for querying data is the SELECT statement.

The SELECT Statement

The SELECT statement is used to query the database and retrieve data that matches criteria that you specify.

Basic Syntax

The simplest form of a SELECT statement retrieves all columns and all rows from a table:

SELECT *
FROM table_name;

To retrieve specific columns, list them after SELECT:

SELECT column1, column2, column3
FROM table_name;

The WHERE Clause

The WHERE clause is used to filter records. It extracts only those records that fulfill a specified condition.

SELECT column1, column2
FROM table_name
WHERE condition;

Example: Retrieve all customers from the 'Customers' table who are located in 'London'.

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

You can use various comparison operators in the WHERE clause:

• = Equal
• > Greater than
• < Less than
• >= Greater than or equal to
• <= Less than or equal to
• <> Not equal (or != in some SQL dialects)
• BETWEEN Between an inclusive range
• LIKE Search for a pattern
• IN Specify multiple possible values for a column

The AND, OR, and NOT Operators

These logical operators are used to filter records based on multiple conditions:

• AND: Displays a record if ALL of the conditions separated by AND are true.
• OR: Displays a record if ANY of the conditions separated by OR is true.
• NOT: Reverses the meaning of the condition.

Example with AND: Retrieve customers from 'USA' who start with the letter 'A'.

SELECT CustomerName, Country
FROM Customers
WHERE Country = 'USA' AND CustomerName LIKE 'A%';

Example with OR: Retrieve customers from 'Germany' or 'France'.

SELECT CustomerName, Country
FROM Customers
WHERE Country = 'Germany' OR Country = 'France';

Example with NOT: Retrieve customers who are NOT from 'Germany'.

SELECT CustomerName, Country
FROM Customers
WHERE NOT Country = 'Germany';

The ORDER BY Clause

The ORDER BY clause is used to sort the result-set in ascending or descending order. By default, it sorts in ascending order (ASC). To sort in descending order, use the DESC keyword.

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

Example: Retrieve all customers, sorted by customer name in ascending order.

SELECT CustomerName, Country
FROM Customers
ORDER BY CustomerName ASC;

Example: Retrieve all products, sorted by price in descending order.

SELECT ProductName, Price
FROM Products
ORDER BY Price DESC;

The 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".

It is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG).

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

Example: Count the number of customers in each country.

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

The HAVING Clause

The HAVING clause is used to filter groups based on a specified condition. It is similar to the WHERE clause, but it operates on grouped data.

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

Example: Find countries with more than 5 customers.

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