Introduction

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

SQL

SQL stands for Structured Query Language, a language designed to interact with databases. We will explore the database used in this tutorial.

Database

The database for this tutorial, named sql_ecto_repo, is derived from the sql-sample-database and manages the HR data of a small business. It has 7 tables:

1. The employees table stores the data of employees.
2. The jobs table stores the job data including job title and salary range.
3. The departments table stores department data.
4. The dependents table stores the employee’s dependents.
5. The locations table stores the location of the departments of the company.
6. The countries table stores the data of countries where the company is doing business.
7. The regions table stores the data of regions such as Asia, Europe, America, and the Middle East and Africa. The countries are grouped into regions.

The diagram below illustrates the tables in the sql_ecto_repo database.

The number of records in each table:

• employees - 31
• dependents - 30
• departments - 11
• jobs - 11
• locations - 7
• countries - 25
• regions - 4

Ecto

Ecto is an Elixir library that acts as a wrapper around the database, allowing us to interact with it. Ecto has four main components:

1. Ecto.Schema - Defines the structure of our tables, including columns, data types, and relationships.
2. Ecto.Repo - Handles database operations such as creating, deleting, updating, and querying records.
3. Ecto.Query - Used to perform queries through Ecto.Repo.
4. Ecto.Changeset - Sets constraints for columns, defines how values should be inserted, and allows casting of columns

Our repo is named as Hr.Repo. You can explore the schema structure used in this tutorial on GitHub.

Every SQL query has its equivalent Ecto query and vice versa. Let's explore some SQL queries and their Ecto counterparts in this tutorial.