DEV Community: Caroline Caillaud

HackerRank 'The Pads' MySQL

Caroline Caillaud — Mon, 01 Dec 2025 17:57:24 +0000

PROBLEM

Generate the following two result sets:

Query an alphabetically ordered list of all names in OCCUPATIONS, immediately followed by the first letter of each profession in parentheses. For example: AnActorName(A), ADoctorName(D), AProfessorName(P), and ASingerName(S).
Query the number of occurrences of each occupation in OCCUPATIONS. Sort the occurrences in ascending order, and output them in the following format:

There are a total of [occupation_count] [occupation]s.
where [occupation_count] is the number of occurrences of an occupation in OCCUPATIONS and [occupation] is the lowercase occupation name. If more than one Occupation has the same [occupation_count], they should be ordered alphabetically.

Note: There will be at least two entries in the table for each occupation type.

Input Format

Column	Type
Name	String
Occupation	String

The OCCUPATIONS table is described as follows:

Name	Occupation
Samantha	Doctor
Juia	Actor
Maria	Actor
Meera	Singer
Ashely	Professor
Ketty	Professor
Christeen	Professor
Jane	Actor
Jenny	Doctor
Prya	Singer

Sample Output

Ashely(P)
Christeen(P)
Jane(A)
Jenny(D)
Julia(A)
Ketty(P)
Maria(A)
Meera(S)
Priya(S)
Samantha(D)
There are a total of 2 doctors.
There are a total of 2 singers.
There are a total of 3 actors.
There are a total of 3 professors.

SOLUTION

SELECT 
    CONCAT(
        name,
        '(', 
        SUBSTR( 
            occupation,
            1,
            1),
        ')'
    )
FROM occupations
ORDER BY 
    name ASC;
SELECT
    CONCAT(
        'There are a total of',
        ' ',
        COUNT(occupation),
        ' ',
        LOWER(occupation),
        's.'
    )
FROM occupations
GROUP BY 
    occupation
ORDER BY 
    COUNT(
        occupation
        ) ASC;

EXPLANATION
The problem requires generating two result sets based on the given table, which contains two columns: Name and Occupation.
The Occupation column can only have four possible values: Doctor, Professor, Singer, or Actor.
In the first query, list all the names from the OCCUPATION table in alphabetical order, and for each name, append the first letter of the occupation in parentheses.
In the second query, we need to count the number of occurrences of each occupation type and list them in ascending order by count. If multiple occupations have the same count, they should be ordered alphabetically by occupation name.

The output should follow this format: There are a total of [n] [occupation]s.

The exercise expects two separate queries to be written.

First Query

SELECT 
    CONCAT(
        name,
        '(', 
        SUBSTR( 
            occupation,
            1,
            1),
        ')'
    )
FROM occupations
ORDER BY 
    name ASC;

This returns all the names with the initial of their occupation in parentheses, sorted alphabetically by name.
The SUBSTR function extracts specific characters from each name, and the CONCAT function combines all the strings.

Second Query

SELECT
    CONCAT(
        'There are a total of',
        ' ',
        COUNT(occupation),
        ' ',
        LOWER(occupation),
        's.'
    )
FROM occupations
GROUP BY 
    occupation
ORDER BY 
    COUNT(
        occupation
    ) ASC;

The COUNT function counts the number of people in each profession.
The LOWER function converts each occupation to lowercase.
Here, we need not only to count how many people belong to each occupation but also to display these results in a specific order, sorted by the number of people in each occupation and in ascending ASC order.
If there’s a tie, such as having 3 doctors and 3 actors, the results should be sorted alphabetically by the occupation name.
The ORDER BY COUNT( ) ASC clause sorts these groups in ascending order based on the number of people. The occupation column serves as the tiebreaker; if two professions have the same count, the results are sorted alphabetically by occupation name.

SQL JOIN

Caroline Caillaud — Tue, 30 Sep 2025 23:29:56 +0000

When we need to combine multiple tables and analyse their data simultaneously, we have to write an SQL query using a JOIN.
A friend of mine, even after researching how to use JOIN, said he was still having trouble writing queries with JOIN clauses.
Because of that, I decided to create a post that’s simple and straightforward, so he can clearly understand how to perform queries using JOIN.

Let’s suppose I have two tables:

Table A

CREATE TABLE A (
  id serial primary key,
  name varchar(255)
);

Table B

CREATE TABLE B (
  id serial primary key,
  name varchar(255),
  a_id int
);

Next, let's insert data into them:

Table A

INSERT INTO A (name) VALUES ('A1'), ('A2'), ('A3');

Table B

INSERT INTO B (name, a_id) VALUES ('B1', 1), ('B2', 2), ('B3', null);

This way, we’ll have our tables with their respective data inserted:

Table A

id	name
1	A1
2	A2
3	A3

Table B

id	name	a_id
1	B1	1
2	B2	2
3	B3

Note that both tables share an ID column.

Syntax

SELECT 
   table1.column_name
   table2.column_name
FROM table1 t1 -- choose the main table
JOIN table2 t2 -- indicates the table that will be combined
  ON t1.key = t2.key; -- "match" condition between the tables

There are a few things to keep in mind when writing a JOIN in SQL. It’s necessary to specify which columns should be displayed. Since our example is very short and I want to make it clear what each JOIN returns, I’ll use * to make it easier to visualise. However, in more complex cases, we specify only the data we want to show in the SELECT statement.
A JOIN can be an INNER JOIN, LEFT JOIN, RIGHT JOIN, or FULL OUTER JOIN.
We use the ON clause to specify which tables we are joining and how the tables are related. We need to explicitly state in the SQL query that the id column of table A corresponds to the a_id column of table B.

Now, let’s run SQL statements that will select records with matching values in both tables.

INNER JOIN
QUERY:

SELECT * FROM A INNER JOIN B ON A.id = B.a_id;

OUTPUT:

a.id  a.name  b.id  b.name  b.a_id
 1      A1     1      B1      1
 2      A2     2      B2      2

The INNER JOIN returns rows where the condition A.id = B.a_id is true in both tables. B3 → a_id has no match. Since there’s no corresponding row, B3 isn't included in the INNER JOIN.

LEFT JOIN
QUERY:

SELECT * FROM A LEFT JOIN B ON A.id = B.a_id;

OUTPUT:

a.id  a.name  b.id  b.name  b.a_id
 1      A1     1      B1      1
 2      A2     2      B2      2
 3      A3    null   null    null

In a LEFT JOIN, all rows from table A are retained. When there’s no matching row in B, the fields from B are NULL.

RIGHT JOIN
QUERY:

SELECT * FROM A RIGHT JOIN B ON A.id = B.a_id;

OUTPUT:

a.id  a.name  b.id  b.name  b.a_id
 1      A1     1      B1      1
 2      A2     2      B2      2
null   null    3      B3     null

In a RIGHT JOIN, all rows from table B are retained. When there’s no matching row in A, the fields from A are NULL.

FULL OUTER JOIN
QUERY:

SELECT * FROM A FULL OUTER JOIN B ON A.id = B.a_id;

OUTPUT:

a.id  a.name  b.id  b.name  b.a_id
 1      A1      1     B1       1
 2      A2      2     B2       2
null   null     3     B3      null
 3      A3    null    null    null

The FULL OUTER JOIN (or OUTER JOIN) returns all rows from both A and B, with NULL where there is no match, and also includes rows that don’t have a corresponding match, such as the row A3, which had no match, and the row B3, which also had no match.

Understanding JOINs is essential for efficiently working with relational databases. When we clearly understand the different types and how tables relate to one another, we can write queries better.

HackerRank 'Higher than 75 marks' MySQL and PostgreSQL

Caroline Caillaud — Wed, 30 Apr 2025 21:15:45 +0000

I'm trying to create the habit of revisiting exercises I've previously solved after some time, to check whether I'm truly retaining the concepts and topics I’ve studied.

When redoing a HackerRank exercise, I decided to solve it using a different approach — this time with ORDER BY RIGHT, which is valid in MySQL and useful when we want to sort a column by the end of the string values. I wasn’t aware of this usage before. So, the first time I solved the challenge, I used the SUBSTRING function instead.

This was the exercise:

It’s a simple task. It asks us to select the names of students who scored more than 75, and order the results by the last three characters of their names in ascending order. If two or more names end with the same three letters, then sort them by their ID in ascending order.
The difference lies in how the SUBSTRING function works in PostgreSQL compared to MySQL.

Here are some possible solutions:

Compatible with both MySQL and PostgreSQL:

SELECT
  name
FROM 
  STUDENTS
WHERE 
  marks > 75 
ORDER BY 
  RIGHT(name, 3),
  id ASC;

Using RIGHT(column, n) returns the last n characters of a string. So, this query sorts by the last three letters of the name. Simple and straightforward.

SUBSTRING() in MySQL:

SELECT 
  name
FROM 
  STUDENTS
WHERE 
  marks > 75
ORDER BY 
  SUBSTRING(name,-3, 3), 
  id ASC;

Testing in PostgreSQL via WSL
HackerRank doesn’t offer PostgreSQL as an option, but I was advised to test queries in the actual database environment. It takes more effort, but I find that it really helps me get used to the tool. Plus, new questions always come up — whether it's due to an error or a difference in PostgreSQL's syntax — and I end up learning beyond the scope of the original problem. And also, cause I believe this has helped me to absorb the concepts in a smooth way.

When I tried to use SUBSTRING the same way I did in the MySQL version, it didn’t work as expected in PostgreSQL.
It turns out that PostgreSQL doesn’t support negative indices in SUBSTRING. While it allows syntax like SUBSTRING(column, -X, Y), the behavior may not be what you'd expect.

In my local database, I created a station table with a city column and added some city names. To better visualize what was happening, I ran this query:

SELECT 
  city, 
  SUBSTRING(city, -3, 3) AS error,
  RIGHT(city, 3) AS certo
FROM station;

And this was the result:

So, PostgreSQL accepts the syntax, but returns an empty string because the negative index is ignored in SUBSTRING.

A simple way to view:

PostgreSQL does not interpret -3 as “from the end.” It didn’t throw an error — just returned an empty string.

Using SUBSTRING correctly:

This returns the last 3 characters as expected.

So, to use SUBSTRING correctly in PostgreSQL for the same exercise, it would look like this:

SELECT 
  name
FROM 
  students
WHERE 
  marks > 75
ORDER BY 
  SUBSTRING(name FROM LENGTH(name) - 2 FOR 3),
  id ASC;

Here, the starting index must be positive, and it counts from the beginning of the string.

Practical Backup and Restore in PostgreSQL using pg_dump and pg_restore

Caroline Caillaud — Tue, 11 Mar 2025 21:33:16 +0000

In this article, you will learn how to practically perform backups and restore databases in PostgreSQL using the pg_dump and pg_restore tools.
In PostgreSQL, to extract a database or a table into a file, we can generate backups using the pg_dump utility. pg_dump is an external tool used to export data and/or schema into a format that can be restored later.
It is possible to generate a backup either in SQL text format or in a binary format.

CREATING A BACKUP

SQL BACKUP

We can use pg_dump to save the entire database:

pg_dump -U postgres_username -d db_name > backup.sql

Or we can use pg_dump to save only a specific table:

pg_dump -U postgres_username -d db_name -t table_name > backup.sql

The pg_dump command also accepts common options that psql supports. So, in addition to specifying -U (user) and -d (database), you can explicitly indicate the server's host and port if needed. -h to specify the host (the PostgreSQL server address) -p to specify the port (the port where the server is listening).

The > symbol redirects the output of the command to a file, saving the result into a newly created .sql file.
This file will contain the necessary SQL commands to recreate the database or table in the same state and data as it was at the time of the backup.
The generated file can be edited and opened in any text editor. It can also be versioned using tools like Git to track changes over time, or directly applied to another PostgreSQL server to recreate the database elsewhere.

BINARY BACKUP(-Fc FORMAT)

pg_dump -U postgres_username -d db_name -Fc -f /path/backup.dump

The -Fc option instructs pg_dump to save the backup in a custom binary format.
The -f option specifies the output file.

RESTORING THE BACKUP

Important: To restore a backup, the target database must already exist.

RESTORING FROM AN SQL BACKUP
For a SQL restore use psql:

psql -U postgres -d newdb_name < backup.sql

RESTORING FROM A BINARY BACKUP
For large databases, it is recommended to use pg_restore

Binary backup custom:

pg_restore -U postgres -d newdb_name backup.dump

The pg_restore command also accepts common options that psql supports. Therefore, you can also specify the server's host and port if needed.

With pg_dump and pg_restore, PostgreSQL provides flexible options for both simple exports and optimized backups for handling large volumes of data.

Getting Started with PostgreSQL

Caroline Caillaud — Mon, 10 Mar 2025 22:57:20 +0000

In this article, I will cover the first steps needed to use a database.
First, we need to install PostgreSQL. In my case, I am using PostgreSQL on Windows via WSL, so I used the terminal to run the necessary commands.

INSTALLING POSTGRESQL

Before installing any packages, let's update the system:

sudo apt update && sudo apt upgrade -y

Now, let's install PostgreSQL along with additional packages:

sudo apt install postgresql postgresql-contrib -y

Start and enable postgres:

sudo systemctl start postgresql
sudo systemctl enable postgresql

Check if the service is running:

sudo systemctl status postgresql

If everything is working correctly, you should see something like this.

● postgresql.service - PostgreSQL database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; preset: disabled)
     Active: active (running) since Sun 2025-03-09 08:11:05 -03; 2h 13min ago
 Invocation: 438028c9f2dd4a159e35421d9c15d7fe
       Docs: man:postgres(1)
   Main PID: 747 (postgres)

CREATING A ROLE FOR YOUR USER

Check the name of your user:

whoami

Access the database and create a role for your user.

su postgres
psql -U postgres

The prompt will change to postgres=#, indicating that we are inside the database.
Inside postgres shell run:

CREATE ROLE <user-name> WITH LOGIN SUPERUSER CREATEDB CREATEROLE;

Quit out of postgres:

\q

exit

Now run:

psql

CREATING A DATABASE

Run the following command to create a new PostgreSQL database:

CREATE DATABASE database_name;

Now you should be able to connect to postgres just using:

psql

CONNECTING TO THE DATABASE

There are two ways to connect.

By running the following command:

\c database_name

If you are unsure of the database name you want to access, you can list all databases in PostgreSQL by running:

\l

If no database is specified, PostgreSQL will try to connect to the default postgres database created during installation.

CREATING TABLES

To illustrate, let's create a users table:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    age INT CHECK (age >= 0),
    create_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

The difference between using NOT NULL and UNIQUE NOT NULL is as follows:
NOT NULL ensures that a column cannot have null values but allows duplicates.
UNIQUE NOT NULL prevents null values and also enforces uniqueness.

CRUD OPERATIONS

CRUD stands for Create, Read, Update, and Delete. The four fundamental operations in database management.

INSERTING VALUES (Create)
When a table is first created, it is empty. To insert values into a PostgreSQL table, use the INSERT INTO command:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

This command adds rows to the table.

READING DATA (Read)
To do it use the SELECT command:

SELECT name, email FROM users;

This will display rows from the selected columns.

UPDATING DATA (Update)
The UPDATE operation is used when we need to modify existing data in a table, whether to correct a record or update some information. Use the the following command:

UPDATE users
SET email = 'carlos.cpg@exemplo.com'
WHERE name = 'Carlos Silva';

This will update the specified columns for all rows that match the condition.

DELETING DATA (Delete)
The DELETE operation removes data from a table. Use the DELETE command as follows:

DELETE FROM users
WHERE name = 'Carlos Silva';

DELETING A DATABASE

If you want to delete everything and restart for practice, run the command:

DROP DATABASE users;

This will delete the database you created.

Now that we have configured PostgreSQL and explored the basic operations for inserting, querying, updating, and deleting data, you can start experimenting. Practice and explore to get comfortable with data manipulation and database operations. Have fun!

HackerRank 'Occupations' MySQL

Caroline Caillaud — Sun, 09 Feb 2025 15:24:04 +0000

PROBLEM
Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. The output should consist of four columns (Doctor, Professor, Singer, and Actor) in that specific order, with their respective names listed alphabetically under each column.

Note: Print NULL when there are no more names corresponding to an occupation.

Input Format

The OCCUPATIONS table is described as follows:

Occupation will only contain one of the following values: Doctor, Professor, Singer or Actor.

EXPLANATION
The first column is an alphabetically ordered list of Doctor names.
The second column is an alphabetically ordered list of Professor names.
The third column is an alphabetically ordered list of Singer names.
The fourth column is an alphabetically ordered list of Actor names.
The empty cell data for columns with less than the maximum number of names per occupation (in this case, the Professor and Actor columns) are filled with NULL values.

SOLUTION

EXPLANATION
The data is initially organized vertically, and the exercise requires the data to be arranged in a horizontal format. That is, we need to pivot the Occupation column from the OCCUPATIONS table so that each Name is sorted alphabetically and displayed under its respective occupation, with a separate column for each occupation.

The output should contain four columns (Doctor, Professor, Singer, and Actor) with the names listed in alphabetical order under each column and should return NULL when there are no more names corresponding to an occupation.
MySQL does not have a built-in PIVOT function. To solve this problem, it will be necessary to create a pivot table using CASE(), ROW_NUMBER() and MAX() to reorganize the data from the OCCUPATIONS table.

SELECT 
    MAX(
      CASE 
        WHEN occupation = 'Doctor' 
        THEN name END
    ) AS Doctor,
    MAX(
      CASE 
        WHEN occupation = 'Professor' 
        THEN name END
    ) AS Professor,
    MAX(
      CASE 
        WHEN occupation = 'Singer' 
        THEN name END
    ) AS Singer,
    MAX(
      CASE 
        WHEN occupation = 'Actor' 
        THEN name END
    ) AS Actor;

The CASE expression organizes each name into the correct column in the pivot table. CASE checks each row and correctly distributes the names among the corresponding columns. The MAX() function selects the non-null name in each group.
What happens is that if the row analyzed by the CASE logic is 'Doctor', the name is placed in the 'Doctor' column; otherwise, it returns NULL. The same applies to 'Professor', 'Singer', and 'Actor'.

FROM (
   SELECT
       name,
       occupation,
       ROW_NUMBER() OVER(
         PARTITION BY occupation ORDER BY name
      ) AS occ_rownumber
   FROM OCCUPATIONS
) AS subquery;

The subquery uses ROW_NUMBER() to create a unique index for each name within its occupation, allowing the data to be aligned within a partition by numbering the rows by occupation.

By partitioning by Occupation and ordering by Name, we ensure that each occupation has its entries numbered in alphabetical order. This returns a list where each name is assigned a row number within its occupation.

The ROW_NUMBER logic allows transforming the vertical list into a table, where each column represents an occupation, and each row contains a name sorted alphabetically.

This way, we can correctly pivot the data in MySQL.