Your cart is currently empty!
Author: alien
-
Khóa học miễn phí SQLite – JOINS nhận dự án làm có lương
SQLite – JOINS
SQLite Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.
SQL defines three major types of joins −
- The CROSS JOIN
- The INNER JOIN
- The OUTER JOIN
Before we proceed, let”s consider two tables COMPANY and DEPARTMENT. We already have seen INSERT statements to populate COMPANY table. So just let”s assume the list of records available in COMPANY table −
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
Another table is DEPARTMENT with the following definition −
CREATE TABLE DEPARTMENT( ID INT PRIMARY KEY NOT NULL, DEPT CHAR(50) NOT NULL, EMP_ID INT NOT NULL );
Here is the list of INSERT statements to populate DEPARTMENT table −
INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (1, ''IT Billing'', 1 ); INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (2, ''Engineering'', 2 ); INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (3, ''Finance'', 7 );
Finally, we have the following list of records available in DEPARTMENT table −
ID DEPT EMP_ID ---------- ---------- ---------- 1 IT Billing 1 2 Engineering 2 3 Finance 7
The CROSS JOIN
CROSS JOIN matches every row of the first table with every row of the second table. If the input tables have x and y row, respectively, the resulting table will have x*y row. Because CROSS JOINs have the potential to generate extremely large tables, care must be taken to only use them when appropriate.
Following is the syntax of CROSS JOIN −
SELECT ... FROM table1 CROSS JOIN table2 ...
Based on the above tables, you can write a CROSS JOIN as follows −
sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;
The above query will produce the following result −
EMP_ID NAME DEPT ---------- ---------- ---------- 1 Paul IT Billing 2 Paul Engineering 7 Paul Finance 1 Allen IT Billing 2 Allen Engineering 7 Allen Finance 1 Teddy IT Billing 2 Teddy Engineering 7 Teddy Finance 1 Mark IT Billing 2 Mark Engineering 7 Mark Finance 1 David IT Billing 2 David Engineering 7 David Finance 1 Kim IT Billing 2 Kim Engineering 7 Kim Finance 1 James IT Billing 2 James Engineering 7 James Finance
The INNER JOIN
INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, the column values for each matched pair of rows of A and B are combined into a result row.
An INNER JOIN is the most common and default type of join. You can use INNER keyword optionally.
Following is the syntax of INNER JOIN −
SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...
To avoid redundancy and keep the phrasing shorter, INNER JOIN conditions can be declared with a USING expression. This expression specifies a list of one or more columns.
SELECT ... FROM table1 JOIN table2 USING ( column1 ,... ) ...
A NATURAL JOIN is similar to a JOIN…USING, only it automatically tests for equality between the values of every column that exists in both tables −
SELECT ... FROM table1 NATURAL JOIN table2...
Based on the above tables, you can write an INNER JOIN as follows −
sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
The above query will produce the following result −
EMP_ID NAME DEPT ---------- ---------- ---------- 1 Paul IT Billing 2 Allen Engineering 7 James Finance
The OUTER JOIN
OUTER JOIN is an extension of INNER JOIN. Though SQL standard defines three types of OUTER JOINs: LEFT, RIGHT, and FULL, SQLite only supports the LEFT OUTER JOIN.
OUTER JOINs have a condition that is identical to INNER JOINs, expressed using an ON, USING, or NATURAL keyword. The initial results table is calculated the same way. Once the primary JOIN is calculated, an OUTER JOIN will take any unjoined rows from one or both tables, pad them out with NULLs, and append them to the resulting table.
Following is the syntax of LEFT OUTER JOIN −
SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...
To avoid redundancy and keep the phrasing shorter, OUTER JOIN conditions can be declared with a USING expression. This expression specifies a list of one or more columns.
SELECT ... FROM table1 LEFT OUTER JOIN table2 USING ( column1 ,... ) ...
Based on the above tables, you can write an outer join as follows −
sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
The above query will produce the following result −
EMP_ID NAME DEPT ---------- ---------- ---------- 1 Paul IT Billing 2 Allen Engineering Teddy Mark David Kim 7 James Finance
Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc
Khóa học miễn phí SQLite – Constraints nhận dự án làm có lương
SQLite – Constraints
Constraints are the rules enforced on a data columns on table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database.
Constraints could be column level or table level. Column level constraints are applied only to one column, whereas table level constraints are applied to the whole table.
Following are commonly used constraints available in SQLite.
-
NOT NULL Constraint − Ensures that a column cannot have NULL value.
-
DEFAULT Constraint − Provides a default value for a column when none is specified.
-
UNIQUE Constraint − Ensures that all values in a column are different.
-
PRIMARY Key − Uniquely identifies each row/record in a database table.
-
CHECK Constraint − Ensures that all values in a column satisfies certain conditions.
NOT NULL Constraint
By default, a column can hold NULL values. If you do not want a column to have a NULL value, then you need to define such constraint on this column specifying that NULL is now not allowed for that column.
A NULL is not the same as no data, rather, it represents unknown data.
Example
For example, the following SQLite statement creates a new table called COMPANY and adds five columns, three of which, ID and NAME and AGE, specifies not to accept NULLs.
CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
DEFAULT Constraint
The DEFAULT constraint provides a default value to a column when the INSERT INTO statement does not provide a specific value.
Example
For example, the following SQLite statement creates a new table called COMPANY and adds five columns. Here, SALARY column is set to 5000.00 by default, thus in case INSERT INTO statement does not provide a value for this column, then by default, this column would be set to 5000.00.
CREATE 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 Constraint
The UNIQUE Constraint prevents two records from having identical values in a particular column. In the COMPANY table, for example, you might want to prevent two or more people from having an identical age.
Example
For example, the following SQLite statement creates a new table called COMPANY and adds five columns. Here, AGE column is set to UNIQUE, so that you cannot have two records with the same age −
CREATE 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 );
PRIMARY KEY Constraint
The PRIMARY KEY constraint uniquely identifies each record in a database table. There can be more UNIQUE columns, but only one primary key in a table. Primary keys are important when designing the database tables. Primary keys are unique IDs.
We use them to refer to table rows. Primary keys become foreign keys in other tables, when creating relations among tables. Due to a ”longstanding coding oversight”, primary keys can be NULL in SQLite. This is not the case with other databases.
A primary key is a field in a table which uniquely identifies each rows/records in a database table. Primary keys must contain unique values. A primary key column cannot have NULL values.
A table can have only one primary key, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a composite key.
If a table has a primary key defined on any field(s), then you cannot have two records having the same value of that field(s).
Example
You already have seen various examples above where we have created COMPANY table with ID as a primary key.
CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
CHECK Constraint
CHECK Constraint enables a condition to check the value being entered into a record. If the condition evaluates to false, the record violates the constraint and isn”t entered into the table.
Example
For example, the following SQLite creates a new table called COMPANY and adds five columns. Here, we add a CHECK with SALARY column, so that you cannot have any SALARY Zero.
CREATE TABLE COMPANY3( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL CHECK(SALARY > 0) );
Dropping Constraint
SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or add a new column to an existing table. It is not possible to rename a column, remove a column, or add or remove constraints from a table.
Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc