Previously, we introduced the preliminary learning of SQLite, learned how to build tables, and how to add, delete and modify the contents of tables. This article will study SQLite in depth.

SQLite constraints

Since I began to learn the basic knowledge of Oracle database, I have some preliminary understanding of constraints. Constraints are rules that are enforced on the data columns of tables. These are used to limit the types of data that can be inserted into tables and ensure the accuracy and reliability of 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.

Common constraints:

constraint	Explain
NOT NULL	Ensure that a column does not have NULL values
DEFAULT	Provide default values for a column when it does not have a specified value
UNIQUE	Ensure that all values in a column are different
PRIMARY KEY	Uniquely identifies row records in database tables
CHECK	Ensure that all values in a column satisfy certain conditions

CREATE TABLE SCHOOL(
ID    INT PRIMARY KEY     NOT NULL,
NAME  TEXT                NOT NULL,
AGE   INT                 NOT NULL,
ADDRESS   CHAR(50),      
SALARY    REAL            CHECK(SALARY>0),
GREAD     INT             DEFAULT 100.00,
WEIGHT    INT             NOT NULL UNIQUE
);

In SQLite, the ALTER TABLE command allows users to rename a table or add a new column to the table, but rename and delete a column, adding and deleting constraints cannot be completed.

SQLite Joins

The Joins clause of SQLite is used to combine records of tables in two or more databases. JOIN is a means of combining fields in two tables by common values.

Three main types of connection for SQL:
1. Cross Connection - CROSS JOIN
2. Internal Connection - INNER JOIN
3. External Connection - OUTER JOIN

To illustrate these three ways of joining, two tables are created:

Table 1

CREATE TABLE COMPANY(
ID   INT PRIMARY KEY      NOT NULL,
NAME TEXT                 NOT NULL,
AGE  INT                  NOT NULL,
ADDRESS     CHAR(50),
SALARY      REAL
);

Information:

sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
   ...> VALUES(1,'Paul',32,'Califronia',20000.00);
sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
   ...> VALUES(2,'Allen',25,'Texas',15000.00);          
sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
   ...> VALUES(3,'Teddy',23,'Norway',20000.00);         
sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
   ...> VALUES(4,'Mark',25,'Rich-Mond',65000.00);       
sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
   ...> VALUES(5,'David',27,'Texas',85000.00);          
sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
   ...> VALUES(6,'Kim',22,'South-Hall',45000.00);
sqlite> INSERT INTO COMPANY values(7,'James',24,'Houston',10000.00);

Then set the output format to display our table information:

sqlite> .header on
sqlite> .mode column
sqlite> SELECT * FROM COMPANY;  
ID          NAME        AGE         ADDRESS     SALARY    
----------  ----------  ----------  ----------  ----------
1           Paul        32          Califronia  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 two

CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

Information:

sqlite> INSERT INTO DEPARTMENT VALUES(1,'IT Billing', 1);
sqlite> INSERT INTO DEPARTMENT VALUES(2,'Engineering', 2);           
sqlite> INSERT INTO DEPARTMENT VALUES(3,'Finance', 7); 

Display table information:

sqlite> SELECT * FROM DEPARTMENT;
ID          DEPT        EMP_ID    
----------  ----------  ----------
1           IT Billing  1         
2           Engineerin  2         
3           Finance     7         

Cross Connection - CROSS JOIN

Cross-join is to match each row of the first table with each row of the second table. Cross-join may produce very large tables, which are rarely used.

Grammar:

SELECT ... FROM table1 CROSS JOIN table2 ...

Based on the above table, write a cross connection:

sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;

The results are as follows:

sqlite> SELECT EMP_ID,NAME,DEPT FROM COMPANY CROSS JOIN DEPARTMENT;
EMP_ID      NAME        DEPT      
----------  ----------  ----------
1           Paul        IT Billing
2           Paul        Engineerin
7           Paul        Finance   
1           Allen       IT Billing
2           Allen       Engineerin
7           Allen       Finance   
1           Teddy       IT Billing
2           Teddy       Engineerin
7           Teddy       Finance   
1           Mark        IT Billing
2           Mark        Engineerin
7           Mark        Finance   
1           David       IT Billing
2           David       Engineerin
7           David       Finance   
1           Kim         IT Billing
2           Kim         Engineerin
7           Kim         Finance   
1           James       IT Billing
2           James       Engineerin
7           James       Finance   

It is not difficult to find that NAME is in Table 1 and EMP_ID and DEPT are in Table 2. Therefore, NAME in each row of Table 1 is matched with EMP_ID and DEPT in each row of Table 2, and the result table of cross connection is obtained.

Internal Connection - INNER JOIN

The inner join creates a new result table by combining the column values of the two tables according to the conditions, compares none of the rows in Table 1 with each row in Table 2, and merges the rows that satisfy the conditions.

Grammar:

SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...

The INNER keyword is optional.

You can also use USING expressions to declare inner join conditions:

SELECT ... FROM table1 JOIN table2 USING ( column1 ,... ) ...

NATURAL JOIN is similar to JOIN. USING automatically tests the equivalence between the values of each column in two tables:

SELECT ... FROM table1 NATURAL JOIN table2...

Write an example of an internal connection based on the table above.

sqlite> SELECT EMP_ID,NAME,DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID; 
EMP_ID      NAME        DEPT      
----------  ----------  ----------
1           Paul        IT Billing
2           Allen       Engineerin
7           James       Finance   

This is the result table based on the same rows of ID in Table 1 and EMP_ID in Table 2.

Outer Connection - OUTER JOIN

The SQL standard defines three types of external connections, LEFT, RIGHT and FULL, but SQLite only supports left external connections, namely LEFT OUTER JOIN.

Grammar:

SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...

External connection conditions can be declared by USING:

SELECT ... FROM table1 LEFT OUTER JOIN table2 USING ( column1 ,... ) ...

Examples of external connections:

sqlite> SELECT EMP_ID,NAME,DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
EMP_ID      NAME        DEPT      
----------  ----------  ----------
1           Paul        IT Billing
2           Allen       Engineerin
            Teddy                 
            Mark                  
            David                 
            Kim                   
7           James       Finance   

Here, NAME with different ID numbers has also been found, which is the external connection.