Queries Using SQL — SELECT, WHERE and ORDER BY

SQL (Structured Query Language) is the standard language for working with relational databases. In LibreOffice Base you can run SQL directly by opening Create Query in SQL View... (or by switching an existing query to SQL View). The single most important SQL statement for retrieving data is SELECT, usually written with the WHERE and ORDER BY clauses. (By convention SQL keywords are written in capitals and each statement ends with a semicolon.)

1. Retrieving data — SELECT ... FROM. To read data you name the columns you want and the table they come from. The symbol * means 'all columns'.

SELECT * FROM Customer;

SELECT Name, City FROM Customer;

The first query returns every column of every customer; the second returns only the Name and City columns.

2. Filtering rows — the WHERE clause. To keep only the rows that meet a condition, add WHERE. Text values go in single quotes; numbers do not.

SELECT Name, City FROM Customer
WHERE City = 'Pune';

Conditions can use the comparison operators =, <, >, <=, >= and <> (not equal), and can be combined with AND and OR:

SELECT OrderNo, Amount FROM Orders
WHERE Amount > 5000 AND CustID = 3;

3. Sorting the result — the ORDER BY clause. To arrange the output in order, add ORDER BY with the field to sort on, followed by ASC for ascending (the default) or DESC for descending.

SELECT Name, Marks FROM Student
ORDER BY Marks DESC;

This lists students from the highest marks to the lowest. The clauses are written in a fixed order — SELECT first, then FROM, then WHERE, then ORDER BY — and can be used together:

SELECT Name, Marks FROM Student
WHERE Class = 'XII-A' …