ElasticSearch: how and when to use it
Textual search engines are a feature that plays an important role in the construction of applications. The user experience is greatly improved if the results requested are truly inherent to the words entered in the search bar. Elasticsearch allows us to integrate a full-text search system and obtain excellent results efficiently. Let us find out how and when it is possible to exploit these features.
SQL: triggers in Oracle
Databases can play an active role in validating and implementing business rules. Through triggers it is possible, in fact. to define rules to ensure data integrity and automation of critical operations. We will analyze their definition and the different types of triggers, focusing our attention on what is provided in Oracle. Through some practical examples, we will understand how to validate data and implement business rules without having to leave these aspects to applications.
SQL: Recursive CTE
In some scenarios, there is a need to store hierarchies within the database, such as the corporate organizational chart or product categories of an e-commerce store. Although relational databases allow hierarchies to be modeled appropriately, querying them can be difficult and burdensome. Recursive CTEs were introduced to address these needs. We will study their syntax and application through an example, not before also evaluating other technological alternatives such as NoSQL databases.
SQL: correlation
In the SQL language, it is possible to condition the execution of a query on the values of the tuples being parsed. This behavior is defined by correlation, that is, conditions in the WHERE clause of a nested query that relate to attributes of the tables in the outer query. Through some examples we will discover which operators use this construct, how to appropriately define correlations, and its alternatives.
SQL: Common Table Expression
Expressing complex queries in SQL language can be really difficult. Dividing the problem into mini-queries is the best strategy to get the desired results quickly without running into errors. Common Table Expressions, also called CTEs, allow the queries of our interest to be defined once to be reused multiple times when needed. Their clean syntax increases the readability of SQL scripts, allows us to debug written code and to create very complex logic to filter and extract data. Through some examples we will discover their potential.
SQL: derived tables
In some contexts, it is necessary to compute intermediate results on which we then perform further operations, such as filtering, grouping, and calculating aggregate measures. The SQL language allows us to do this through the definition of derived tables, that is, SELECT statements defined within the FROM clause. Through some examples we will discover how to use them appropriately.
SELECT: set operators
Sometimes we need to operate on the results of several queries as if they were sets. We do not have to use a programming language to do these operations on the data, but we can use constructs from the SQL language. The operators available allow us to both join and exclude the results two sets, as well as define the intersection. With some examples we will understand how to use these constructs appropriately.
SELECT: NOT IN operator and tuple constructor
In some cases there is a need to exclude one set of values from another set. The SQL language allows us to exclude values to be excluded returned by a nested query using the NOT IN operator. Where the elements to be excluded are identified by a set of attributes we can use the tuple constructor. With some examples we will understand how to use these constructs appropriately.
SELECT: IN operator
In some cases writing SQL queries can be really complex. The divide and conquer strategy, always turns out to be successful because it allows us to merge the results of two or more queries. We have, however, the need to use special constructs. By means of nested queries and in particular of the IN operator this operation is simple and intuitive.
SELECT: query with JOIN and GROUP BY
Relational databases are based on modeling that divides the concepts we want to represent into different tables. However, when we use them in real application contexts we have to reconstruct these operations and sometimes define subsets of data to calculate statistics. How can we do this in the SQL language? By means of the JOIN and GROUP BY clauses! Let’s find out how to write them through practical examples.
