![]() It can beįollowed by one or more modifiers. AdvertisementsĪnd datetime functions is the time string. The time function gives the current time. The datetime function returns the current date and time. The date function with the now string returns Take various time strings, modifiers, and formats. SQLite has functions for working with date and time. Here we count how many orders our customers placed. sqlite> SELECT sum(OrderPrice) AS Sum FROM Orders In our example, we show the average priceįinally, we mention the sum function. The avg function returns the average value sqlite> SELECT avg(Price) AS 'Average price' FROM Cars The count(Id) counts only non NULL values. sqlite> SELECT count(Id) AS '# of non NULL values' FROM Testing The count(*) returns the number of rows in the table. sqlite> SELECT count(*) AS '# of rows' FROM Testing ![]() The last_insert_rowid function returns the Id of the last Advertisements sqlite> SELECT last_insert_rowid() Here we create table Testing with 3 numerical and 2 Sqlite> INSERT INTO Testing VALUES (1), (2), (3), (NULL), (NULL) sqlite> CREATE TABLE TESTING(Id INTEGER) By default, the NULL value is shown as an empty string. Next we are going to demonstrate the difference between the count(*)Īnd count(ColumnName) functions. sqlite> SELECT count(DISTINCT Customer) AS '# of customers' FROM Orders Number of unique customers, we have to utilise the DISTINCT clause. This SQL statement returns the number of orders. The number of orders and how do we count the number of customers? sqlite> SELECT count(Customer) AS '# of orders' FROM Orders Logically, each customer can make multiple orders. In the Orders table, we do have duplicate records of customers. The count function returns the number of rows in the Advertisements sqlite> SELECT count(*) AS '# of cars' FROM Cars ![]() Notice that there are no duplicate records. We recapitulate what we have in the Cars table. In such cases, duplicate elementsĪre filtered before being passed into the aggregate function. Aggregate functions that take a singleĪrgument can be preceded by the DISTINCT keyword. With aggregate functions, we get some statistical data. The typeof function returns the data type of the argument. (PRAGMA foreign_keys = 1 ) sqlite> SELECT typeof(12), typeof('ZetCode'), typeof(33.2), typeof(NULL), We use the PRAGMA statement to change it. The function returns 0, which means that the constraint is not enforced byĭefault. We check if the FOREIGN KEY constraint is enforced by default. The sqlite_compileoption_used function returns a boolean value,ĭepending on whether or not that option was used during the build. Sqlite> SELECT sqlite_compileoption_used('SQLITE_DEFAULT_FOREIGN_KEYS') AS 'FK' Three INSERT statements, so total changes is equal to three. In the current database connection, we have done Or DELETE statements since the current database connection The total_changes function returns the number of sqlite> SELECT total_changes() AS 'Total changes' The length function returns the length of a string. With the lower function we change the names of first three rows Advertisements sqlite> SELECT lower(Name) AS 'Names in lowercase' FROM Friends The upper function converts characters into upper-case letters. Sqlite> SELECT upper(Name) AS 'Names in capitals' FROM Friends Most and the least expensive cars from the Cars table. In our example, the max and min functions return the sqlite> SELECT max(Price), min(Price) FROM Cars The abs function returns the absolute value of a numeric argument. Advertisements sqlite> SELECT random() AS Random The sqlite_version function returns the version sqlite> SELECT sqlite_version() AS 'SQLite Version' We will cover some functions from each group of SQLite functions. There are three types of functions in SQLite database: core, aggregate, In this part of the SQLite tutorial, we will cover SQLite built-in functions. ![]() Contents Previous Next SQLite functions Advertisements ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |