1 - SQL

PoC Software Pool 2024 - Day 02 - SQL

Day purposes

✔️ Execute simple SQL requests.

✔️ Discover database management tools.

✔️ Understand the fundamentals of relational database.

Introduction

During the day 01, you learned a programming language to develop software. But a software isn't only composed of a hundred thousand lines of code, it's common to use external tools to take in charge a specific task. 😉
For example, you can use Prometheus/Grafana to monitor your app, Kafka as a queue or a database to store huge amount of data...

What's a database?

It's an organized space where you can store pieces of information. Each time you need a permanent storage, for example, to store users, you will need a database.
It has many usage and ways, the most popular is SQL database or also called relational database.

Type of database

Today we will learn relational database but other exists:

• Document
• Graph
• Column
• Key/Value
• Search Engine
• Multi model

You can find more information about databases in this post 😄

Requirements

There are many tools to manage a database. We give you the choice between DataGrip and SQL IDE Online.

We recommend DataGrip for its powerful UX and easy adoption 😉

In the folder resources, you will find a file named database.sql to generate a new database with artists and musics 🎵

Here's a schema of our data:

In relational database, data is stored into a table where each information is a column.

You can then create a relation between tables 😄

💡 You can find more information about PostgreSQL concepts here.

These concepts are important, if you are lost don't hesitate to ask the staff for help they'll be happy to help you understand 😜

DataGrip

If you don't want to use DataGrip, move to the SQL IDE Online setup.

First, download DataGrip using the Jetbrains Toolbox.

You can use docker to run a PostgreSQL database with the following command in the directory containing the file database.sql

docker run  --name my_database -e POSTGRES_PASSWORD=password -e POSTGRES_USER=root -e POSTGRES_DB=my_database -p 5432:5432 -v "$(pwd)"/database.sql:/docker-entrypoint-initdb.d/init.sql -d postgres:alpine

If you can't manage to connect to your database, try changing the POSTGRES_USER parameter to something else and reloading your container using docker rm.

Don't worry about this command for now, you will learn docker during day04 👀

Start DataGrip and create a new Data Source of type PostgreSQL.

Here's the information to fill in the form:

• Database name: my_database
• Username: root
• Password: password
• Host: localhost
• Port: 5432

💡 You will certainly have to download the PostgreSQL driver on your first connection.

Below you have an example of configuration:

After applying the configuration, you should see a new data source in the left panel of DataGrip.

Verify that you have something similar to the example below:

You can look a these steps if you encounter an issue during the configuration.

💡 If you are already familiar with You can use pgAdmin or DBeaver feel free to use them.

SQL IDE Online

We do not recommend this solution because it's important to use professional tools, but you can use it if you want to go fast 🏃

• Go to SQL IDE Online.
• Click on PostgreSQL database.
• Click on category File.
• Open database.sql
• Execute it with the button Run.

You should get the following result

Step 0 - Setup

📑 Description:

If you correctly followed the requirements, you should have a database ready to use 😍

📌 Tasks:

You will just need to create a new directory in your pool repository to submit your work:

mkdir -p day02

This day is composed of two parts, so for now you will push your work in the directory SQL 😉

mkdir -p day02/SQL

Create a file queries.md in which you will write every query you make to keep a trace:

touch queries.md

Step 1 - Basics

📑 Description:

Your database is ready to run your first requests 🥳

The goal of this step is to understand how to read data in a database using SQL.

Let's try to get some information from the table artists.

📌 Tasks:

Write 3 queries to :

• Retrieve all the information contained in the artists table.
• Retrieve only name and genre from the table artists.
• Retrieve the list of all artists of genre hip-hop/rap.

📚 Documentation:

See how to read data in SQL or in PostgreSQL.

Step 2 - Relations

📑 Description:

As we said before, a relational database is perfect to handle data with multiple relations between them.

📌 Tasks:

Let's write 3 new queries to link information from tables:

• Retrieve name from artists and musics.
  You must specify the name of your result column with artists_names and musics_names.
• Retrieve all artist who singed in the music We Are The World.
  Those artists must be sorted in descending order according to their number of fans.
• Retrieve all the musics from Booba.
  They must be sorted in alphabetical order.

📚 Documentation:

See how to sort data and join in SQL.

Step 3 - CRUD

📑 Description:

Yesterday, you programmed the CRUD of a resource, let's learn how to do it using SQL 💪

📌 Tasks:

Write 3 queries to:

• Add a new artist with his id set to 100.
• Delete all musics that have the Gold certification.
• Add the music Take What You Want to the artists you previously created.

⚠️ artists and musics are linked using a relationship table, you will maybe need to do 2 queries to delete records.

📚 Documentation:

See how create or delete data in SQL.

Step 4 - Good counts make good friends

📑 Description:

You've learned the basics, let's see more advanced features with pre-processing SQL functions 🧐

📚 Documentation

You will use functions to count elements directly from SQL.

💡 Databases are faster than any programming language (except C) so if you can pre-process your data in your query, do it.

📌 Tasks:

Write 4 new queries to:

• Count the number of artists
• Count the number of artists in each genre.
• Count the number of musics sorted by their certification and displayed in ascending order.
• Count the number of musics of each artists, sorted by their certification and displayed in ascending order.

⚠️ Be sure you never count the same music two time.

You'll certainly need to group element in SQL 😉

Step 5 - Rap Game

📑 Description:

You have certainly noticed, there are several kind of musics related to rap: the rap and hip-hop/rap.

We would like to organize a concert with all the rappers in our database, but for that, we need a list of them.

📌 Tasks:

Write a query that retrieve all the rappers in the database, sorted in descending order by their fans' number.

📚 Documentation:

💡 You'll maybe need to manipulate string and cast data.

To go further

Congratulation, you now have solid knowledge in SQL 🎉

Here are some links for the most courageous among you:

• Organize your database with schemas
• Create your own PostgreSQL function
• Automate task with triggers
• Improve query performance

Organization

🚀 Don't hesitate to follow us on our different networks, and put a star 🌟 on PoC's repositories.