SQL: SELECT, WHERE, and ORDER BY

Structured Query Language (SQL) is the standard language for interacting with relational databases. SQL allows you to retrieve, insert, update, and delete data. This lesson covers the most fundamental retrieval operations.

---

The SELECT Statement

The SELECT statement retrieves data from one or more tables.

Basic Syntax

SELECT column1, column2, ...
FROM table_name;

Select All Columns

SELECT *
FROM Students;

The asterisk (*) selects every column. In production code, it is better to name specific columns for clarity and efficiency.

Select Specific Columns

SELECT FirstName, Surname, DateOfBirth
FROM Students;

This returns only the three named columns.

Aliases

You can rename columns in the output using AS:

SELECT FirstName AS "First Name", Surname AS "Last Name"
FROM Students;

DISTINCT

To remove duplicate rows from the results:

SELECT DISTINCT FormGroup
FROM Students;

This returns each unique form group only once.

---

The WHERE Clause

The WHERE clause filters rows based on a condition. Only rows that satisfy the condition are returned.

Basic Syntax

SELECT column1, column2
FROM table_name
WHERE condition;

Comparison Operators

Operator	Meaning
=	Equal to
<> or !=	Not equal to
<	Less than
>	Greater than
<=	Less than or equal to
>=	Greater than or equal to

Examples

-- Students in form 10A
SELECT * FROM Students
WHERE FormGroup = '10A';

-- Students born after 1 January 2008
SELECT FirstName, Surname FROM Students
WHERE DateOfBirth > '2008-01-01';

-- Products costing less than 20 pounds
SELECT ProductName, Price FROM Products
WHERE Price < 20.00;

Logical Operators: AND, OR, NOT

You can combine multiple conditions:

-- Students in 10A born after 2007
SELECT * FROM Students
WHERE FormGroup = '10A' AND DateOfBirth > '2007-01-01';

-- Students in 10A or 10B
SELECT * FROM Students
WHERE FormGroup = '10A' OR FormGroup = '10B';

-- Students NOT in 10A
SELECT * FROM Students
WHERE NOT FormGroup = '10A';

BETWEEN

-- Products priced between 10 and 50
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 50;

IN

-- Students in specified form groups
SELECT * FROM Students
WHERE FormGroup IN ('10A', '10B', '10C');

LIKE (Pattern Matching)