The INNER JOIN creates a new result table based on the join predicate combining the column values of the two tables (table1 and table2). The query compares each row in table1 with each row in table2 to find matching pairs for all rows that satisfy the join predicate. When the join predicate is satisfied, the column values for each matching pair of rows A and B are merged into one result row. INNER JOIN is the most common type of connection and is the default connection type. The INNER keyword is optional. Table COMPANY: 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 Table DEPARTMENT: ID DEPT EMP_ID ---------- ---------- ---------- 1 IT Billing 1 2 Engineerin 2 3 Finance 7 Demo: Sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID; The results are as follows: EMP_ID NAME DEPT ---------- ---------- ---------- 1 Paul IT Billing 2 Allen Engineerin 7 James FinanceOuter joins-OUTER join
SQLite constraintsOUTER JOIN is an extension of the INNER JOIN. Although the SQL standard defines three types of outer joins: LEFT, RIGHT, FULL, However, SQLite only supports LEFT OUTER JOIN. The outer join (OUTER JOIN) declares the condition in the same way as the inner join (INNER JOIN), expressed using the ON, USING, or NATURAL keywords. The initial results table is calculated in the same way. Once the primary join calculation is complete, the outer join (OUTER JOIN) will merge any unconnected rows from one or both tables. Outer joined columns use NULL values to append them to the result table. Demo: Sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID; result: EMP_ID NAME DEPT ---------- ---------- ---------- 1 Paul IT Billing 2 Allen Engineerin Teddy Mark David Kim 7 James Finance
A constraint is a rule that is enforced on a table's data columns. These are used to limit the types of data that can be inserted into a table. This ensures the accuracy and reliability of the data in the database.
Constraints can be column-level or table-level. Column-level constraints apply only to columns, and table-level constraints are applied to the entire table.
Not NULL constraintThe following are the commonly used constraints in SQLite. NOT NULL constraint: Ensure that a column cannot have a NULL value. DEFAULT constraint: Provides a default value for a column when no value is specified. UNIQUE constraint: Ensure that all values in a column are different. PRIMARY Key constraint: Uniquely identifies each row/record in the database table. CHECK constraint: The CHECK constraint ensures that all values in a column satisfy certain conditions.
By default, columns can hold NULL values. If you do not want a column to have a null value, you need to define this constraint on that column to specify that null values are not allowed on that column.
NULL is not the same as no data, it represents unknown data.
Instance
For example, the following SQLite statement creates a new table company and adds five columns, where the ID, NAME, and age three columns specify that NULL values are not accepted:
DEFAULT constraintCREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
The default constraint provides a value for the column when the INSERT into statement does not provide a specific value.
Instance
For example, the following SQLite statement creates a new table company and adds five columns. Here, the SALARY column is set to 5000.00 by default. So when the INSERT into statement does not provide a value for the column, the column is set to 5000.00.
UNIQUE constraintCREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL DEFAULT 50000.00 );
UNIQUE constraints prevent the existence of two records in a particular column with the same value. In the company table, for example, you may want to prevent two or more than two people from having the same age.
Instance
For example, the following SQLite statement creates a new table company and adds columns. Here, the Age column is set to UNIQUE, so you cannot have two records of the same ages:
PRIMARY KEY ConstraintCREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL UNIQUE, ADDRESS CHAR(50), SALARY REAL DEFAULT 50000.00 );
The PRIMARY KEY constraint uniquely identifies each record in a database table. There can be multiple UNIQUE columns in a table, but only one primary key. When you design a database table, the primary key is important. The primary key is the unique ID.
We use the primary key to refer to the rows in the table. You can create relationships between tables by setting the primary key to a foreign key for the other table. Because of "long-standing encoding supervision", in SQLite, the primary key can be NULL, which is different from other databases.
A primary key is a field in a table that uniquely identifies rows/records in a database table. The primary key must contain unique values. Primary key columns cannot have NULL values.
A table can have only one primary key, which may consist of one or more fields. When multiple fields are used as primary keys, they are called composite keys.
If a table has a primary key defined on any field, you cannot have two records with the same value on those fields.
Instance
We've seen the various instances of the COMAPNY table that we created with the ID as the primary key:
CHECK constraintCREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
Check constraints enable entering a record to check the value of the condition. If the condition value is false, the record violates the constraint and cannot be entered into the table.
Instance
For example, the following SQLite creates a new table company, and adds five columns. Here, we add a CHECK for the SALARY column, so the salary cannot be zero:
SQLite NULL ValueCREATE TABLE COMPANY3( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL CHECK(SALARY > 0) );
SQLite's NULL is an item used to represent a missing value. A NULL value in the table is a value that appears blank in the field.
A field with a NULL value is a field that does not have a value. It is important to understand that a NULL value is different from a 0 value or a field that contains spaces.
Grammar
The basic syntax for using NULL when creating a table is as follows:
SQLite> CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
here, not NULL indicates that the column always accepts an explicit value for the given data type. There are two columns here we do not use NOT NULL, which means that these two columns cannot be null.
fields with null values can be left blank when the record is created.
Instance
A NULL value can cause problems when selecting data, because when an unknown value is compared to another value, the result is always unknown and is not included in the final result. Suppose you have the following table,
Company's records are as follows:
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
Let's use the UPDATE statement to set some values that allow null values to be NULL, as follows:
sqlite> UPDATE COMPANY SET ADDRESS = NULL, SALARY = NULL where ID IN(6,7);
Now, the records for the company table are as follows:
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 7 James 24
Next, let's look at the use of the is NOT NULL operator, which is used to list all records that SALARY are NOT null:
sqlite> SELECT ID, NAME, AGE, ADDRESS, SALARY FROM COMPANY WHERE SALARY IS NOT NULL;
The above SQLite statement will produce the following result:
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
The following is the use of the IS null operator, which lists all records that are SALARY null:
sqlite> SELECT ID, NAME, AGE, ADDRESS, SALARY FROM COMPANY WHERE SALARY IS NULL;
The above SQLite statement will produce the following result:
SQLite aliasesID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 6 Kim 22 7 James 24
You can temporarily rename a table or column to another name, which is called an alias. Using a table alias refers to renaming a table in a particular SQLite statement. Renaming is a temporary change, and the name of the actual table in the database does not change.
Column aliases are used to rename a column in a table for a particular SQLite statement.
Grammar
The basic syntax for table aliases is as follows:
SELECT column1, column2.... FROM table_name AS alias_name WHERE [condition];
The basic syntax for column aliases is as follows:
SELECT column_name AS alias_name FROM table_name WHERE [condition];
Instance
Suppose there are two tables below, (1) The company table looks like this:
sqlite> select * from COMPANY; 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
(2) Another table is DEPARTMENT, as follows:
ID DEPT EMP_ID ---------- -------------------- ---------- 1 IT Billing 1 2 Engineering 2 3 Finance 7 4 Engineering 3 5 Finance 4 6 Engineering 5 7 Finance 6
Now, here is the use of table aliases, where we use C and D as aliases for company and DEPARTMENT tables, respectively:
sqlite> SELECT C.ID, C.NAME, C.AGE, D.DEPT FROM COMPANY AS C, DEPARTMENT AS D WHERE C.ID = D.EMP_ID;
The above SQLite statement will produce the following result:
ID NAME AGE DEPT ---------- ---------- ---------- ---------- 1 Paul 32 IT Billing 2 Allen 25 Engineerin 3 Teddy 23 Engineerin 4 Mark 25 Finance 5 David 27 Engineerin 6 Kim 22 Finance 7 James 24 Finance
Let's look at an instance of a column alias, where COMPANY_ID is the alias of the ID column, and Company_Name is the alias of the NAME column:
sqlite> SELECT C.ID AS COMPANY_ID, C.NAME AS COMPANY_NAME, C.AGE, D.DEPT FROM COMPANY AS C, DEPARTMENT AS D WHERE C.ID = D.EMP_ID;
The above SQLite statement will produce the following result:
SQLite Alter CommandCOMPANY_ID COMPANY_NAME AGE DEPT ---------- ------------ ---------- ---------- 1 Paul 32 IT Billing 2 Allen 25 Engineerin 3 Teddy 23 Engineerin 4 Mark 25 Finance 5 David 27 Engineerin 6 Kim 22 Finance 7 James 24 Finance
Using the ALTER TABLE statement to rename a table, you can also add additional columns to an existing table by using the ALTER TABLE statement.
In SQLite, in addition to renaming tables and adding columns to existing tables, the ALTER Table command does not support other operations.
Instance
Suppose our 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
Now, let's try renaming the table using the ALTER table statement, as follows:
sqlite> ALTER TABLE COMPANY RENAME TO OLD_COMPANY;
The SQLite statement above renames the company table to Old_company. Now, let's try adding a new column to the Old_company table as follows:
sqlite> ALTER TABLE OLD_COMPANY ADD COLUMN SEX char(1);
Now that the company table has changed, use the SELECT statement to output the following:
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 subquery in the SELECT statement uses theNote that the newly added column is populated with a NULL value.
Subqueries can be used with SELECT, INSERT, UPDATE, and DELETE statements, along with operators such as =, <, >, >=, <=, in, between, and so on.
Here are a few rules that a subquery must follow:
Instance
Suppose 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
Now, let's examine the subquery in the SELECT statement using:
sqlite> SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY WHERE SALARY > 45000) ;
This will produce the following results:
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
The subquery in the INSERT statement uses the
Subqueries can also be used with INSERT statements. Insert statements are inserted into another table using the data returned by the subquery. The data selected in the subquery can be used with any character,
Date or number function modification.
Instance Assume that COMPANY_BKP has a structure similar to the COMPANY table and can be created with the same CREATE TABLE, except that the table name is changed to COMPANY_BKP. Now copy the entire COMPANY table to COMPANY_BKP with the following syntax: Sqlite> INSERT INTO COMPANY_BKP SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY) ;
The subquery in the UPDATE statement uses the
Subqueries can be used in conjunction with UPDATE statements. When a subquery is used through an UPDATE statement, single or multiple columns in the table are updated. Instance Suppose we have the COMPANY_BKP table, which is a backup of the COMPANY table. The following example updates the SALARY of all clients with AGE greater than or equal to 27 in the COMPANY table to the original 0.50 times: Sqlite> UPDATE COMPANY SET SALARY = SALARY * 0.50 WHERE AGE IN (SELECT AGE FROM COMPANY_BKP WHERE AGE >= 27 );
This affects two lines, and the last record in the company table is as follows:
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 10000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 42500.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
Subqueries in a DELETE statement can be used in conjunction with a DELETE statement, just like the other statements mentioned above.
Instance
Suppose we have a COMPANY_BKP table, which is a backup of the company table.
The following instance deletes all customer records in the company table that have age greater than or equal to 27:
sqlite> DELETE FROM COMPANY WHERE AGE IN (SELECT AGE FROM COMPANY_BKP WHERE AGE > 27 );
This affects two lines, and the last record in the company table is as follows:
SQLite Common functionsID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 42500.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
SQLite has many built-in functions for working with string or numeric data. Some useful SQLite built-in functions are listed below, and all functions are case insensitive, which means you can use lowercase or uppercase or mixed forms of these functions. For more information, please check out the official SQLite documentation:
Ordinal 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 choose the maximum value of a column. 3 SQLite MIN function The SQLite MIN aggregate function allows us to choose the minimum value 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 you to calculate the sum for 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 parameter. 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 a lowercase letter. 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:
SQLite COUNT functionID 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 is an example:
sqlite> SELECT count(*) FROM COMPANY;
The above SQLite SQL statement will produce the following results:
SQLite MAX functioncount(*)----------7
The SQLite max aggregation function allows us to select the maximum value for a column. Here is an example:
sqlite> SELECT max(salary) FROM COMPANY;
The above SQLite SQL statement will produce the following results:
SQLite MIN functionmax(salary)-----------85000.0
The SQLite min aggregation function allows us to select the minimum value for a column. Here is an example:
sqlite> SELECT min(salary) FROM COMPANY;
The above SQLite SQL statement will produce the following results:
SQLite AVG functionmin(salary)-----------10000.0
The SQLite AVG aggregation function calculates the average of a column. Here is an example:
Sqlite> SELECT avg (Salary) from company;
The above SQLite SQL statement will produce the following results:
SQLite SUM functionavg(salary)----------------37142.8571428572
The SQLite sum aggregation function allows you to calculate a sum for a numeric column. Here is an example:
sqlite> SELECT sum(salary) FROM COMPANY;
The above SQLite SQL statement will produce the following results:
SQLite RANDOM functionsum(salary)-----------260000.0
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 results:
SQLite ABS functionRandom-------------------5876796417670984050
The SQLite ABS function returns the absolute value of the numeric parameter. 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 results:
SQLite UPPER functionabs(5) abs(-15) abs(NULL) abs(0) abs("ABC") ---------- ---------- ---------- ---------- ---------- 5 15 0 0.0
The SQLite UPPER function converts a string to uppercase. Here is an example:
sqlite> SELECT upper(name) FROM COMPANY;
The above SQLite SQL statement will produce the following results:
SQLite LOWER functionupper(name) ----------- PAUL ALLEN TEDDY MARK DAVID KIM JAMES
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 results:
SQLite LENGTH functionlower(name) ----------- paul allen teddy mark david kim james
The SQLite length function returns the lengths of the strings. Here is an example:
sqlite> SELECT name, length(name) FROM COMPANY;
The above SQLite SQL statement will produce the following results:
SQLite sqlite_version functionNAME length(name) ---------- ------------ Paul 4 Allen 5 Teddy 5 Mark 4 David 5 Kim 3 James 5
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 results:
SQLite Version--------------3.6.20
More detailed information: http://www.runoob.com/sqlite/sqlite-operators.html
SQLite Learning Note 2
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
