YouTip

SQLite has many built-in functions for handling string or numeric data. Below are some useful SQLite built-in functions, and all functions are case-insensitive, meaning you can use them in lowercase, uppercase, or mixed case. For more details, please refer to the official SQLite documentation: | Number | Function & Description | | --- | --- | | 1 | **SQLite COUNT Function** The SQLite COUNT aggregate function is used to count the number of rows in a database table. | | 2 | **SQLite MAX Function** The SQLite MAX aggregate function allows us to select the maximum value of a certain column. | | 3 | **SQLite MIN Function** The SQLite MIN aggregate function allows us to select the minimum value of a certain column. | | 4 | **SQLite AVG Function** The SQLite AVG aggregate function calculates the average value of a certain column. | | 5 | **SQLite SUM Function** The SQLite SUM aggregate function allows calculating the sum of a numeric column. | | 6 | **SQLite RANDOM Function** The SQLite RANDOM function returns a pseudo-random integer between -9223372036854775808 and +9223372036854775807. | | 7 | **SQLite ABS Function** The SQLite ABS function returns the absolute value of a numeric argument. | | 8 | **SQLite UPPER Function** The SQLite UPPER function converts a string to uppercase letters. | | 9 | **SQLite LOWER Function** The SQLite LOWER function converts a string to lowercase letters. | | 10 | **SQLite LENGTH Function** The SQLite LENGTH function returns the length of a string. | | 11 | **SQLite sqlite_version Function** The SQLite sqlite_version function returns the version of the SQLite library. | Before we start explaining these function examples, let's assume the COMPANY table has the following records: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ----------1 Paul 32 California 20000.02 Allen 25 Texas 15000.03 Teddy 23 Norway 20000.04 Mark 25 Rich-Mond 65000.05 David 27 Texas 85000.06 Kim 22 South-Hall 45000.07 James 24 Houston 10000.0 ## SQLite COUNT Function The SQLite COUNT aggregate function is used to count the number of rows in a database table. Here is an example: sqlite> SELECT count(*) FROM COMPANY; The above SQLite SQL statement will produce the following result: count(*)----------7 ## SQLite MAX Function The SQLite MAX aggregate function allows us to select the maximum value of a certain column. Here is an example: sqlite> SELECT max(salary) FROM COMPANY; The above SQLite SQL statement will produce the following result: max(salary)-----------85000.0 ## SQLite MIN Function The SQLite MIN aggregate function allows us to select the minimum value of a certain column. Here is an example: sqlite> SELECT min(salary) FROM COMPANY; The above SQLite SQL statement will produce the following result: min(salary)-----------10000.0 ## SQLite AVG Function The SQLite AVG aggregate function calculates the average value of a certain column. Here is an example: sqlite> SELECT avg(salary) FROM COMPANY; The above SQLite SQL statement will produce the following result: avg(salary)----------------37142.8571428572 ## SQLite SUM Function The SQLite SUM aggregate function allows calculating the sum of a numeric column. Here is an example: sqlite> SELECT sum(salary) FROM COMPANY; The above SQLite SQL statement will produce the following result: sum(salary)-----------260000.0 ## SQLite RANDOM Function The SQLite RANDOM function returns a pseudo-random integer between -9223372036854775808 and +9223372036854775807. Here is an example: sqlite> SELECT random() AS Random; The above SQLite SQL statement will produce the following result: Random-------------------5876796417670984050 ## SQLite ABS Function The SQLite ABS function returns the absolute value of a numeric argument. Here is an example: sqlite> SELECT abs(5), abs(-15), abs(NULL), abs(0), abs("ABC"); The above SQLite SQL statement will produce the following result: abs(5) abs(-15) abs(NULL) abs(0) abs("ABC")---------- ---------- ---------- ---------- ----------5 15 0 0.0 ## SQLite UPPER Function The SQLite UPPER function converts a string to uppercase letters. Here is an example: sqlite> SELECT upper(name) FROM COMPANY; The above SQLite SQL statement will produce the following result: upper(name)----------- PAUL ALLEN TEDDY MARK DAVID KIM JAMES ## SQLite LOWER Function The SQLite LOWER function converts a string to lowercase letters. Here is an example: sqlite> SELECT lower(name) FROM COMPANY; The above SQLite SQL statement will produce the following result: lower(name)----------- paul allen teddy mark david kim james ## SQLite LENGTH Function The SQLite LENGTH function returns the length of a string. Here is an example: sqlite> SELECT name, length(name) FROM COMPANY; The above SQLite SQL statement will produce the following result: NAME length(name)---------- ------------Paul 4Allen 5Teddy 5Mark 4David 5Kim 3James 5 ## SQLite sqlite_version Function The SQLite sqlite_version function returns the version of the SQLite library. Here is an example: sqlite> SELECT sqlite_version() AS 'SQLite Version'; The above SQLite SQL statement will produce the following result: SQLite Version--------------3.6.20