Basic Queries

This tutorial introduces fundamental SQL (Structured Query Language) commands for retrieving data from relational databases. Understanding these basic queries is the first step to effectively interacting with your data.

The `SELECT` Statement

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

Selecting All Columns

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

SELECT *
FROM Customers;

This query will return every piece of information about every customer in the Customers table.

Selecting Specific Columns

You can also specify which columns you want to retrieve by listing their names after SELECT, separated by commas:

SELECT FirstName, LastName, Email
FROM Customers;

This query will only return the first name, last name, and email address for each customer.

The `WHERE` Clause

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

Filtering with `WHERE`

Let's say we only want to find customers from a specific city:

SELECT CustomerID, FirstName, LastName, City
FROM Customers
WHERE City = 'London';

This will return the ID, name, and city for all customers whose City is 'London'.

Using Comparison Operators

The WHERE clause supports various comparison operators:

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

SELECT ProductName, Price
FROM Products
WHERE Price > 50.00;

SELECT FirstName, LastName
FROM Customers
WHERE LastName LIKE 'S%';

SELECT CompanyName, Country
FROM Customers
WHERE Country IN ('USA', 'Canada');

Logical Operators

You can combine conditions in the WHERE clause using logical operators:

• AND: Returns true if both conditions are true.
• OR: Returns true if at least one condition is true.
• NOT: Reverses the result of the condition.

SELECT CompanyName, Country, City
FROM Customers
WHERE Country = 'USA' AND City = 'New York';

SELECT CustomerName, Country, OrderDate
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Country = 'Germany' OR OrderDate > '2023-01-01';

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 FirstName, LastName
FROM Customers
ORDER BY LastName ASC;

SELECT FirstName, LastName
FROM Customers
ORDER BY LastName DESC;

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

These basic queries form the foundation for data retrieval. As you progress, you'll learn about more complex operations like joins, aggregations, and subqueries.