Please enable JavaScript.
Coggle requires JavaScript to display documents.
SQLite Database, Aggregate functions, Clauses, Definitions, Corresponding…
-
Aggregate functions
- COUNT(): Returns the total number of rows that match the query.
-
-
-
- SUM(): Returns the sum of the values for all rows in the selected column.
- AVG(): Returns the mean value—average—of all the values in the selected column.
- MIN(): Returns the smallest value in the selected column.
- MAX(): Returns the largest value in the selected column.
-
Clauses
WHERE
(boolean check, filter)
-
-
-
-
SET
SET firstColumn = firstValue,
secondColumn = secondValue,
...
-
Definitions
SQL language elements :
Identifier: name on database object (like table, column and schema).
Predicate: condition that can be evaluated to three-valued logic (true/false/unknown) or Boolean truth values. Used to limit the effects of statements and queries, or to change program flow.
E.g.: name = 'USA' (checks if the name is "USA")
-
Keyword: special word that SQL understands (e.g. SELECT, UPDATE, WHERE, COUNT).
Clauses: constituent components of statements and queries; parts of an SQL statement that do a specific job. (In some cases, these are optional.)
E.g.: 'UPDATE country SET population = population + 1': The UPDATE and SET parts are clauses.
-
-
-
Keys
-
Foreign key: a references of the primary key of another table (means there's a relationship between the tables).
Misc
Scalar Data Type: represents single, non-composite values.
E.g.: Boolean (bool), Numeric (int, float, double), Character (char).
Aggregate function: function that performs a calculation on a set of values, and returns a single value.
-
Statements
-
-
INSERT
INSERT INTO
INSERT INTO tableName
VALUES (valueOfColumn1, valueOfColumn2, ...)
INSERT INTO email
VALUES (
NULL, 'Lorem ipsum dolor sit amet', 'sender@example.com', 'inbox', false, false, CURRENT_TIMESTAMP
);A value of NULL is provided for the id column, which means the id will be automatically generated with the next available autoincremented integer.
-
Other
-
-
The inequality operator (!=) is the same as in Kotlin. SQL also provides comparison operators <, <=, >, and >=.
CURRENT_TIMESTAMP - special variable that is replaced with the current time in UTC when the query runs
MISC
-
Alternative databases:
Not all databases are organized into tables, columns, and rows. Other kinds of databases, known as NoSQL, are structured similarly to a JSON object with nested pairs of keys and values. Examples of NoSQL databases include Redis or Cloud Firestore.
-
Relational database:
- Tables are often related.
- Contains a unique identifier for rows
(e.g. a column where the value in each row is an automatically incremented integer: the primary key).
-
-
-
-
-
-
-
-
-
-