Learning to use SQLite (version 3.7.4) (medium E)

For two relations a and B, the Union keyword Concatenates the two into a single relationship that only contains non-duplicate fields A and B. In SQL, Union joins two select results, and deduplication is eliminated by default (all data is retained using Union all ):

Select F. *, top_foods.count from foods F
Inner join
(Select food_id, count (food_id) as count from foods_episodes
Group by food_id
Order by count (food_id) DESC limit 1) top_foods
On F. ID = top_foods.food_id
Union
Select F. *, bottom_foods.count from foods F
Inner join
(Select food_id, count (food_id) as count from foods_episodes
Group by food_id
Order by count (food_id) DESC limit 1) bottom_foods

On F. ID = bottom_foods.food_id
Order by top_foods.count DESC;

This is to find out the highest frequency and lowest frequency of food in the foods table.

Intersect operations have two relationships: A and B. Select the rows in which a is also in B. Intersect is used to find the top 10 foods between 3 and 5:

Select F. * from foods F
Inner join
(Select food_id, count (food_id) as count from foods_episodes
Group by food_id
Order by count (food_id) DESC limit 10) top_foods
On F. ID = top_foods.food_id
Intersect
Select F. * from foods F
Inner join foods_episodes Fe on F. ID = Fe. food_id
Inner join episodes e on Fe. episode_id = E. ID
Where E. season between 3 and 5
Order by F. Name;

Except operates on the two relations A and B to find all rows in a but not in B:

Select F. * from foods F
Inner join
(Select food_id, count (food_id) as count from foods_episodes
Group by food_id
Order by count (food_id) DESC limit 10) top_foods
On F. ID = top_foods.food_id

Except

Select F. * from foods F
Inner join foods_episodes Fe on F. ID = Fe. food_id
Inner join episodes e on Fe. episode_id = E. ID
Where E. season between 3 and 5
Order by F. Name;

Note: Compound query only requires an order by statement at the end.

Processing null in SQLite:

Null is a placeholder for missing information and is not a value. Relationship between null and true/false values:

Table: Null-related logic or

X	Y	X and Y	X or Y
True	True	True	True
True	False	False	True
True	Null	Null	True
False	False	False	False
False	Null	False	Null
Null	Null	Null	Null

Note: if we want to check whether null exists, we can use is null or is not null. However, using equal or greater than may produce strange results. Null is not equal to any value. null and null are also different, because you do not know what null stores.

Any value other than 0 is true ".

Coalesce () function usage:

Syntax: coalesce (expression [,,...... N,]);
Coalesce (expression [,... n])

If all variables are null, null is returned. If at least one variable is not null, the first one is not null. Note the usage of null in aggregation.

Note:

Coalesce (expression1,... n) is equivalent to this case function:

Case

When (expression1 is not null) Then expression1

...

When (expressionn is not null) Then expressionn

Else null

For more information, see:

SQL Server coalesce:

Http://content.edu-edu.com.cn/info/2010/07/21/000048p2.shtml