SQL Basics: Selecting Data

Welcome to the fundamental lesson on retrieving data from your SQL databases. The SELECT statement is the cornerstone of data querying, allowing you to specify exactly which information you want to see.

The Basic SELECT Statement

To retrieve all columns and all rows from a table, you use the following syntax:

SELECT * FROM your_table_name;

Here:

SELECT * indicates that you want to retrieve all columns.
FROM your_table_name specifies the table from which to retrieve the data.

Selecting Specific Columns

Often, you only need a subset of the columns in a table. You can list the desired column names, separated by commas, after the SELECT keyword.

For example, to get only the 'ProductName' and 'Price' from a table named 'Products':

SELECT ProductName, Price FROM Products;

The WHERE Clause: Filtering Data

The WHERE clause allows you to filter rows based on specific conditions. This is crucial for retrieving only the data that meets your criteria.

Let's say you want to find products with a price greater than 50:

SELECT ProductName, Price FROM Products WHERE Price > 50;

Common comparison operators 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 (Matches any value in a list)
BETWEEN (Matches a range of values)

Using Logical Operators (AND, OR, NOT)

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

AND: Both conditions must be true.
OR: At least one of the conditions must be true.
NOT: Reverses the result of a condition.

Example: Find products that cost more than 50 AND are in stock:

SELECT ProductName, Price, StockQuantity FROM Products WHERE Price > 50 AND StockQuantity > 0;

Example: Find products that are either in the 'Electronics' or 'Appliances' category:

SELECT ProductName, Category FROM Products WHERE Category = 'Electronics' OR Category = 'Appliances';

Sorting Results: ORDER BY

The ORDER BY clause sorts the result set. By default, it sorts in ascending order (ASC). You can specify DESC for descending order.

Sort products by price in ascending order:

SELECT ProductName, Price FROM Products ORDER BY Price ASC;

Sort products by name in descending order:

SELECT ProductName FROM Products ORDER BY ProductName DESC;

Distinct Values: DISTINCT

The DISTINCT keyword is used to return only unique values. If a column has multiple occurrences of the same value, DISTINCT will return only one.

Get a list of unique product categories:

SELECT DISTINCT Category FROM Products;

Introduction to Aggregates (COUNT, SUM, AVG, MIN, MAX)

Aggregate functions perform a calculation on a set of values and return a single value. They are often used with the GROUP BY clause (covered in a later tutorial).

COUNT()

Counts the number of rows.

SELECT COUNT(*) FROM Customers;

SUM()

Calculates the sum of a numeric column.

SELECT SUM(OrderTotal) FROM Orders;

AVG()

Calculates the average value of a numeric column.

SELECT AVG(Price) FROM Products;

MIN()

Finds the minimum value in a column.

SELECT MIN(OrderDate) FROM Orders;

MAX()

Finds the maximum value in a column.

SELECT MAX(Salary) FROM Employees;

Tip: Always enclose string literals (like category names or text) in single quotes ('). Numeric values do not need quotes.

This covers the fundamental SELECT statement and its essential clauses. Practice these concepts to build a strong foundation for querying your databases.