The PostgreSQL Challenge - Question 1

Introduction

Are you looking to hone your SQL chops? We have started a new series called The PostgreSQL Challenge that will put your PostgreSQL skills to the test! We’ll give you a task to accomplish with a SQL query and you’ll see if you can come up with the correct SQL query. There’s always multiple ways to accomplish the same task but we’ll provide you with the answer that we think uses best practices.

You ready? Let’s get started with the first challenge …

Question 1

Instructions

We have a people table with the following columns:

• id
• username
• zipcode

The task is to write a query that returns a table with the following schema:

• username
• zipcode

where:

• the username is the original username string repeated three times … ‘alex’ becomes ‘alexalexalex’
• the zipcode is the original zipcode reversed … ‘12345’ becomes ‘54321’

Can you do it?

Answer

Explanation

The first important commands here is the ‘REPEAT’ command which will repeat a value a specified number of times. We use this command to repeat the username three times. You can also have done this using CONCAT(username, username, username) but this kind of query becomes painful if you the repeat was 12 times instead of 3. We use the AS username to give the column the correct name that was specified in the instructions.

The second important command here is the ‘REVERSE’ command which takes a string and reverses it just as we want. Again we use the AS zipcode to give the column the correct name that was specified in the instructions.

Try it Yourself

Let’s create the table to test with:

Let’s insert some data into our table:

Let’s verify the data as properly inserted:

The moment of truth! Let’s run our answer query to see what we get!

Conclusion

To solve this challenge we used the ‘REPEAT’ and ‘REVERSE’ commands along with some basic SQL. If you didn’t get this one, don’t worry, keep with these challenges because you’ll start to see patterns on how problems are being solved in SQL.

Pleas follow along with this series of challenges. It’ll hone your PostgreSQL skills and SQL skills in general. It’ll expose you to commands you probably never use but that might come in handy. Even if you solved the challenge in a different way it might show you an alternative or different perspective on a problem.

Want to challenge us? Go ahead! We’re eager to hear your own SQL challenges … especially if they’re real world problems. We love solving real world database problems, so contact us if you have any!