May 16, 2021 SQLite
SQLite has many built-in functions for working with string or numeric data. H ere are some useful SQLite built-in functions, all of which are case insensitive, which means you can use them in small or capital or mixed forms. For more information, please review SQLite's official documentation:
Serial number | Function & Description |
---|---|
1 |
SQLite Count function
The SQLITE Count aggregation function is used to calculate the number of rows in a database table. |
2 |
SQLITE MAX function
The SQLITE MAX aggregate function allows us to select the maximum of a column. |
3 |
SQLite MIN function
The SQLite Min aggregate function allows us to select a minimum of a column. |
4 |
SQLite AVG function
The SQLite AVG aggregate function calculates the average of a column. |
5 |
SQLite SUM function
The SQLite SUM aggregate function allows for sum of a numeric column. |
6 |
SQLite Random Function
The SQLite Random function returns a pseudo-random integer between -922372036854775808 and +9223372036854775807. |
7 |
SQLite ABS function
The SQLite ABS function returns an absolute value of the numeric parameters. |
8 |
SQLITE UPPER function
The SQLITE UPPER function converts the string to uppercase letters. |
9 |
SQLITE LOWER function
The SQLITE LOWER function converts the string to lowercase letters. |
10 |
SQLITE Length function
The SQLite Length function returns the length of the string. |
11 |
SQLITE SQLITE_VERSION function
The SQLITE SQLITE_VERSION function returns the version of the SQLite library. |
Before we begin to explain these function instances, let's assume that the COMPANY table has the following records:
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
The SQLite COUNT aggregate function is used to calculate the number of rows in a database table. Here are some examples:
sqlite> SELECT count(*) FROM COMPANY;
The SQLite SQL statement above produces the following results:
count(*) ---------- 7
The SQLite MAX aggregation function allows us to select the maximum value for a column. Here are some examples:
sqlite> SELECT max(salary) FROM COMPANY;
The SQLite SQL statement above produces the following results:
max(salary) ----------- 85000.0
The SQLite MIN aggregation function allows us to select the minimum value of a column. Here are some examples:
sqlite> SELECT min(salary) FROM COMPANY;
The SQLite SQL statement above produces the following results:
min(salary) ----------- 10000.0
The SQLite AVG aggregation function calculates the average value of a column. Here are some examples:
sqlite> SELECT avg(salary) FROM COMPANY;
The SQLite SQL statement above produces the following results:
avg(salary) ---------------- 37142.8571428572
The SQLite SUM aggregation function allows the sum to be calculated for a numeric column. Here are some examples:
sqlite> SELECT sum(salary) FROM COMPANY;
The SQLite SQL statement above produces the following results:
sum(salary) ----------- 260000.0
The SQLite RANDOM function returns a pseudo-random integer between -9223372036854775808 and s9223372036854775807. Here are some examples:
sqlite> SELECT random() AS Random;
The SQLite SQL statement above produces the following results:
Random ------------------- 5876796417670984050
The SQLite ABS function returns the absolute value of the numeric argument. Here are some examples:
sqlite> SELECT abs(5), abs(-15), abs(NULL), abs(0), abs("ABC");
The SQLite SQL statement above produces the following results:
abs(5) abs(-15) abs(NULL) abs(0) abs("ABC") ---------- ---------- ---------- ---------- ---------- 5 15 0 0.0
The SQLite UPPER function converts strings to capital letters. Here are some examples:
sqlite> SELECT upper(name) FROM COMPANY;
The SQLite SQL statement above produces the following results:
upper(name) ----------- PAUL ALLEN TEDDY MARK DAVID KIM JAMES
The SQLite LOWER function converts strings to lowercase letters. Here are some examples:
sqlite> SELECT lower(name) FROM COMPANY;
The SQLite SQL statement above produces the following results:
lower(name) ----------- paul allen teddy mark david kim james
The SQLite LENGTH function returns the length of the string. Here are some examples:
sqlite> SELECT name, length(name) FROM COMPANY;
The SQLite SQL statement above produces the following results:
NAME length(name) ---------- ------------ Paul 4 Allen 5 Teddy 5 Mark 4 David 5 Kim 3 James 5
The SQLite sqlite_version returns the version of the SQLite library. Here are some examples:
sqlite> SELECT sqlite_version() AS 'SQLite Version';
The SQLite SQL statement above produces the following results:
SQLite Version -------------- 3.6.20