SQLite
SQLite Query: Select, Where, LIMIT, OFFSET, Count, Group By
To write SQL queries in an SQLite database, you have to know how the SELECT, FROM, WHERE, GROUP...
RELATIONAL ALGEBRA is a widely used procedural query language. It collects instances of relations as input and gives occurrences of relations as output. It uses various operations to perform this action. SQL Relational algebra query operations are performed recursively on a relation. The output of these operations is a new relation, which might be formed from one or more input relations.
In this tutorial, you will learn:
Relational Algebra devided in various groups
Let's study them in detail with solutions:
The SELECT operation is used for selecting a subset of the tuples according to a given selection condition. Sigma(σ)Symbol denotes it. It is used as an expression to choose tuples which meet the selection condition. Select operator selects tuples that satisfy a given predicate.
σ_{p}(r)
σ
is the predicate
r
stands for relation which is the name of the table
p
is prepositional logic
Example 1
σ _{topic = "Database"} (Tutorials)
Output - Selects tuples from Tutorials where topic = 'Database'.
Example 2
σ _{topic = "Database" and author = "guru99"}( Tutorials)
Output - Selects tuples from Tutorials where the topic is 'Database' and 'author' is guru99.
Example 3
σ _{sales > 50000 }(Customers)
Output - Selects tuples from Customers where sales is greater than 50000
The projection eliminates all attributes of the input relation but those mentioned in the projection list. The projection method defines a relation that contains a vertical subset of Relation.
This helps to extract the values of specified attributes to eliminates duplicate values. (pi) symbol is used to choose attributes from a relation. This operator helps you to keep specific columns from a relation and discards the other columns.
Example of Projection:
Consider the following table
CustomerID | CustomerName | Status |
---|---|---|
1 | Active | |
2 | Amazon | Active |
3 | Apple | Inactive |
4 | Alibaba | Active |
Here, the projection of CustomerName and status will give
Π _{CustomerName, Status }(Customers)
CustomerName | Status |
---|---|
Active | |
Amazon | Active |
Apple | Inactive |
Alibaba | Active |
Rename is a unary operation used for renaming attributes of a relation.
ρ (a/b)R will rename the attribute 'b' of relation by 'a'.
UNION is symbolized by ∪ symbol. It includes all tuples that are in tables A or in B. It also eliminates duplicate tuples. So, set A UNION set B would be expressed as:
The result <- A ∪ B
For a union operation to be valid, the following conditions must hold -
Example
Consider the following tables.
column 1 | column 2 | column 1 | column 2 | |
---|---|---|---|---|
1 | 1 | 1 | 1 | |
1 | 2 | 1 | 3 |
A ∪ B gives
column 1 | column 2 |
---|---|
1 | 1 |
1 | 2 |
1 | 3 |
- Symbol denotes it. The result of A - B, is a relation which includes all tuples that are in A but not in B.
Example
A-B
column 1 | column 2 |
---|---|
1 | 2 |
An intersection is defined by the symbol ∩
A ∩ B
Defines a relation consisting of a set of all tuple that are in both A and B. However, A and B must be union-compatible.
Example:
A ∩ B
column 1 | column 2 |
---|---|
1 | 1 |
Cartesian Product in DBMS is an operation used to merge columns from two relations. Generally, a cartesian product is never a meaningful operation when it performs alone. However, it becomes meaningful when it is followed by other operations. It is also called Cross Product or Cross Join.
Example – Cartesian product
σ _{column 2 }= _{'1' }(A X B)
Output – The above example shows all rows from relation A and B whose column 2 has value 1
column 1 | column 2 |
---|---|
1 | 1 |
1 | 1 |
Join operation is essentially a cartesian product followed by a selection criterion.
Join operation denoted by ⋈.
JOIN operation also allows joining variously related tuples from different relations.
Types of JOIN:
Various forms of join operation are:
Inner Joins:
Outer join:
In an inner join, only those tuples that satisfy the matching criteria are included, while the rest are excluded. Let's study various types of Inner Joins:
The general case of JOIN operation is called a Theta join. It is denoted by symbol θ
Example
A ⋈_{θ} B
Theta join can use any conditions in the selection criteria.
For example:
A ⋈ _{A.column 2 > B.column 2 }(B)
column 1 | column 2 |
---|---|
1 | 2 |
When a theta join uses only equivalence condition, it becomes a equi join.
For example:
A ⋈ _{A.column 2 = B.column 2 }(B)
column 1 | column 2 |
---|---|
1 | 1 |
EQUI join is the most difficult operations to implement efficiently using SQL in an RDBMS and one reason why RDBMS have essential performance problems.
Natural join can only be performed if there is a common attribute (column) between the relations. The name and type of the attribute must be same.
Example
Consider the following two tables
Num | Square |
---|---|
2 | 4 |
3 | 9 |
Num | Cube |
---|---|
2 | 8 |
3 | 27 |
C ⋈ D
Num | Square | Cube |
---|---|---|
2 | 4 | 4 |
3 | 9 | 27 |
In an outer join, along with tuples that satisfy the matching criteria, we also include some or all tuples that do not match the criteria.
In the left outer join, operation allows keeping all tuple in the left relation. However, if there is no matching tuple is found in right relation, then the attributes of right relation in the join result are filled with null values.
Consider the following 2 Tables
Num | Square |
---|---|
2 | 4 |
3 | 9 |
4 | 16 |
Num | Cube |
---|---|
2 | 8 |
3 | 18 |
5 | 75 |
A B
Num | Square | Cube |
---|---|---|
2 | 4 | 4 |
3 | 9 | 9 |
4 | 16 | - |
In the right outer join, operation allows keeping all tuple in the right relation. However, if there is no matching tuple is found in the left relation, then the attributes of the left relation in the join result are filled with null values.
A B
Num | Cube | Square |
---|---|---|
2 | 8 | 4 |
3 | 18 | 9 |
5 | 75 | - |
In a full outer join, all tuples from both relations are included in the result, irrespective of the matching condition.
A B
Num | Cube | Square |
---|---|---|
2 | 4 | 8 |
3 | 9 | 18 |
4 | 16 | - |
5 | - | 75 |
Operation(Symbols) | Purpose |
---|---|
Select(σ) | The SELECT operation is used for selecting a subset of the tuples according to a given selection condition |
Projection(π) | The projection eliminates all attributes of the input relation but those mentioned in the projection list. |
Union Operation(∪) | UNION is symbolized by symbol. It includes all tuples that are in tables A or in B. |
Set Difference(-) | - Symbol denotes it. The result of A - B, is a relation which includes all tuples that are in A but not in B. |
Intersection(∩) | Intersection defines a relation consisting of a set of all tuple that are in both A and B. |
Cartesian Product(X) | Cartesian operation is helpful to merge columns from two relations. |
Inner Join | Inner join, includes only those tuples that satisfy the matching criteria. |
Theta Join(θ) | The general case of JOIN operation is called a Theta join. It is denoted by symbol θ. |
EQUI Join | When a theta join uses only equivalence condition, it becomes a equi join. |
Natural Join(⋈) | Natural join can only be performed if there is a common attribute (column) between the relations. |
Outer Join | In an outer join, along with tuples that satisfy the matching criteria. |
In the left outer join, operation allows keeping all tuple in the left relation. | |
In the right outer join, operation allows keeping all tuple in the right relation. | |
In a full outer join, all tuples from both relations are included in the result irrespective of the matching condition. |
To write SQL queries in an SQLite database, you have to know how the SELECT, FROM, WHERE, GROUP...
What is Package in Oracle? PL/SQL package is a logical grouping of a related subprogram...
Data types in SQLite are different compared to other database management system. In SQLite, you...
In this tutorial, we will introduce SQL* Plus and learn how to connect it to the database. After...
What is WHERE Clause in MySQL? WHERE Clause in MySQL is a keyword used to specify the exact...
What is Nested Blocks Oracle? In PL/SQL, each block can be nested into another block. They are...