Problem Solving Using SQL (via HackerRank) — Part 1

Databases are part of our everyday life, and being able to communicate with them is essential. Structured Query Language (SQL) is a widely used powerful query language that lets you work with many types of data.

Now there are tons of videos, tutorials, courses, career tracks online which can help you with SQL. So, rather than dwelling into the theoretical aspects of SQL, we will look ahead how to solve SQL problems and you can try them yourself as it’s freely available on Hacker Rank. I would recommend you to refresh or familiarise yourself with SQL by checking out my Teach Yourself SQL article here on Medium.

This Problem Solving Using SQL will be a ’n’ part series of articles ( :) haven’t decided on the number of problems yet). I’ll be solving 5 problems in each article (will try to post them every day), starting from the basics with breaking down the problem, and you can try the same before checking the solution. The series will involve all types of SQL problems with problem difficulty ranging from easy, medium and hard in upcoming articles.

The problems for today are of easy beginners level, from hacker rank as follows —

1. Revising the Select Query I
2. Revising the Select Query II
3. Select All
4. Select By ID
5. Japanese Cities Attributes

SQL FUNCTIONALITIES USED —

Before going ahead you should note that we will only use the following SQL functionalities for solving these 5 problems —

• SELECT : to retrieve the output fields/columns. It can individual columns (SELECT column_name1), multiple columns ( SELECT column_name1, column_name2,…) or all the columns (SELECT *)
• FROM : from which table (SELECT * FROM TABLE_1)
• WHERE : used if there are any conditions for retrieving our results. If multiple conditions we can use AND, OR along with WHERE.

E.g.
SELECT * FROM TABLE_1
WHERE
column_A > 100
AND
column_B < 10

So let’s get started.

Problem 1 : Revising the Select Query I

Query all columns for all American cities in the CITY table with populations larger than 100000. The CountryCode for America is USA.

The CITY table is described as follows:

City Table

Now before going for the solution, lets divide the problem —

• the problem says query all the columns so you know there is ‘SELECT *’ involved as * will retrieve all the rows and column.
• We have the CITY table, we want only values where the country is America (CountryCode for America is USA) and population is larger than 100000. The question itself tells us we have to use WHERE clause with AND.

So before checking the below solution, I would recommend you to solve the problem yourself — Revising the Select Query 1.

Solution for Problem 1

Problem 2 : Revising the Select Query II

Query the NAME field for all American cities in the CITY table with populations larger than 120000. The CountryCode for America is USA.

The CITY table is described as follows:

Breaking down the problem —

• The problems says to query only the NAME field i.e. just one column so we have ‘SELECT NAME’
• As in previous problem, we have the CITY table, we want only values where the country is America (CountryCode for America is USA) and population is larger than 120000. The question itself tells us we have to use WHERE clause with AND.

Before checking the below solution, try to solve the problem yourself — Revising the Select Query II.

Solution for Problem 2

Problem 3 : Select All

Query all columns (attributes) for every row in the CITY table.

The CITY table is described as seen in the previous 2 problems.

This is straight forward as mentioned in the problem statement, we need to select all the columns from CITY table.

Before checking the below solution, try to solve the problem yourself — Select All.

Solution for Problem 3

Problem 4 : Select By ID

Query all columns for a city in CITY with the ID 1661. The CITY table is described as seen in the previous problems.

Breaking down the problem —

• Query all columns from city: SELECT * FROM CITY
• where ID is 1661, so use the WHERE clause

Before checking the below solution, try to solve the problem yourself — Select By ID.

Solution for Problem 4

Problem 5: Japanese Cities’ Attributes

Query all attributes of every Japanese city in the CITY table. The COUNTRYCODE for Japan is JPN. The CITY table is described as seen in the previous problems.

Breaking down the problem —

• FROM CITY table, query all attributes : SELECT *
• for every Japanese city (COUNTRYCODE = JPN) : Use WHERE clause

Before checking the below solution, try to solve the problem yourself — Japanese Cities Attributes.

Solution for Problem 5

Thats 5 SQL problems you have practised today which makes you X% better at SQL then before solving these problems. We will solve the next five problems in Part 2 of this series. As mentioned, this series involves all types of SQL problems with problem difficulty ranging from easy, medium and hard in upcoming articles. So stay tuned and practice SQL along with me.