Getting Started with SQL

Appendix A. Operators and Functions

This appendix covers common operations and functions used in SQLite as a convenient reference. While this book does not cover all the functionalities of SQLite, the functionalities likely to have an immediate need can be found here. The primary goal is to showcase universal SQL concepts that apply to most platforms, not to teach the nuances of the SQLite platform in detail.

A comprehensive coverage of SQLite’s features can be found at https://www.sqlite.org/docs.html.

Appendix A1 – Literal Expression Queries

You can test operators and functions easily without querying any tables at all. You simply SELECT an expression of literals as in the following query, which will calculate a single value of 12:

SELECT 5 + 7

Any functions and literals, including text strings, can be tested in this manner as well. This query will check if the word 'TONY' is in the string 'TONY STARK', and it should return 1:

SELECT INSTR('TONY STARK', 'TONY')

This is a great way to test operators and functions without using any tables. This appendix will show many examples with this approach, and you can use it for your own experimentation.

Appendix A2 – Mathematical Operators

SQLite has a small set of basic math operators. More advanced tasks are usually done with functions, but here are the five core mathematical operators.

Assume x = 7 and y = 3

Operator	Description	Example	Result
+	Adds two numbers	x + y	10
-	Subtracts two numbers	x - y	4
*	Multiplies two ...