3.7.2 - SQL selection, filtering and ordering
SQL retrieves information by naming the fields, table, conditions and ordering required. Work through a complete product table, then practise written queries and explore one query in a Python/SQLite Study demonstration.
Selecting fields and tables
A retrieval query describes the information wanted rather than a loop that visits each record.
SQL means Structured Query Language. SELECT names the fields to retrieve; FROM names their table. Throughout this lesson, Product has the following schema and records. ProductID is its integer primary key; ProductName is text; Price and Stock are integers. Price is measured in whole pounds.
| ProductID | ProductName | Price | Stock |
|---|---|---|---|
| 1 | Notebook | 5 | 12 |
| 2 | Pen | 2 | 30 |
| 3 | Folder | 5 | 0 |
| 4 | Ruler | 3 | 8 |
To retrieve just names and prices:
SELECT ProductName, Price
FROM Product;
The result has two columns, in that requested order, and one row for every product. The query does not change Product. SELECT * retrieves all fields, but specifying fields gives precisely the information requested. Keywords are shown in capitals for readability; they are not sensitive to case in these examples. A semicolon marks the end of a statement.
Filtering records
The WHERE clause restricts the result to records that satisfy a condition.
SELECT ProductName, Stock
FROM Product
WHERE Stock > 0;
Notebook, Pen and Ruler satisfy the condition; Folder does not because its stock is zero. The fields in the condition need not all appear in the result. For example, select names while filtering on Price.
Comparison operators include =, <> (not equal), <, >, <= and >=. Text values need quotes, such as ProductName = 'Pen'. Numeric values do not need quotes. AND requires both conditions to be true; OR requires at least one. Brackets make intended grouping clear when combining conditions.
SELECT ProductName
FROM Product
WHERE Price <= 5 AND Stock > 0;
All three in-stock products meet this price limit. Changing it to Price < 5 removes Notebook because five is not less than five. If the condition were Price <= 5 OR Stock > 0, Folder would be included because it satisfies the price condition even though it is out of stock. The choice of operator therefore changes the meaning, not just the query's appearance.
Continue this lesson.
You’re previewing NovaLearn without an account. Create a free account to read the rest.
Create free account