From SQL Expertise to Ecto Mastery

In this tutorial, you will learn about SQL queries and their equivalent Ecto queries. Both are used to interact with the database.

In Ecto, aliases are used to set names for modules. You can reference the module by that name and access everything inside it.

`select` and `from` are used to view a table and any columns from a table.

The `limit` command is used to limit the number of records from being fetched.

The `where` command is used to select records based on a condition. It returns only those records that satisfy the conditions specified in the `where` command, effectively filtering the records."

In `SQL` and `Ecto`, comparison operators are used to compare values, allowing you to filter results based on specified conditions.

Logical operators allow you to use multiple logical operators in a query.

The `like` operator is used to match string values in `where`. You can fetch records that contain specific string characters.

`AND` is used to check for multiple conditions in a query. `AND` will return records which satisfy all the conditions.

The `between` command is used in the `where` to check for a range. It verifies whether the records have values within the specified range and is typically combined with the `and` command.

The `in` command is employed to retrieve records by checking for multiple values in the `where` clause.

The `is null` command is used to fetch records with no value or null value. It is used in the `where` command.

The `or` command is used to check either one of the conditions in `where`. In the `or` condition either one can be true or both conditions can be true. It is commonly used in the `where` clause.

The `not` command is used to exclude some values from the records.

The `order by` command is used to alphabetically order records. `desc` helps in descending order and `asc` helps in ascending order. The default ordering is `asc`.

The `COUNT` command is used to count the total number of non-null values for a particular column. You can also count the entire table.

The `SUM` command is used to obtain the sum of a particular column in a table. It provides the total numeric value of the specified column and operates exclusively on numerical values.

The `MAX` and `MIN` are used to get the maximum and minimum values of a column which value can be numerical, chasracter string, or dates.

The `AVG` is used to find out the average of a column. It is only used on numerical values.

The `GROUP BY` command is used to group rows with the same value in a particular column and perform aggregate functions on those grouped rows.

The `HAVING` command is used to perform aggregate functions such as `AVG`,`COUNT`, and `MAX` in a `group_by`.

The `distinct` command is used to get unique values from any columns in a table.

The `join/inner join` command is used to join multiple tables based on a particular column. Tables can be joined using the same column that the tables share. There are four types of `join`.

`OUTER JOIN` includes four types, `RIGHT OUTER JOIN`, `LEFT OUTER JOIN`, `FULL OUTER JOIN`

The `left join` command returns all rows from the left table and only the matching rows from the right.

The `right join` command returns all rows from the right table and only the matching rows from the left table.

You can use `where`, `and` in a `join` to filter your table.

A `full outer join` will return unmatched and matched values from both tables.

The `union` command is used to do multiple queries at once. By using `union`, Both queries will return a single table.

The `SELF JOIN` command, used to join the same table with it's own table based on the queries.