Throughout the course we will be using Hackerrank to further practice the SQL that we are learning. Please go ahead and create an ID for Hackerrank if you don’t already have one.
Once you’ve made an account, please look around and see how things are! There are practices for a variety of languages and experience levels, and you could submit your work to earn points!
Here are five exercises below that you should try.
Let’s walk through the first one (“Revising the Select Query I”) together.
We have the problem above.
The table name is “CITY”, so you could already be thinking that we’ll go with a “FROM CITY.” It asked for all columns, so it should start with “SELECT *”, and seems like we’re making some progress.
Let’s try it out!
Make sure you use the drop down menu to specify that you will be using MySQL. Type in the code, and click “Run Code.”
You will be getting an frowny face saying you’ve gotten the wrong answer, but that’s ok. We knew that the code wasn’t complete yet. But know we know we’re in business as you’ve gotten some output.
Now we will need to implement the two conditions. It has to be American Cities and the population has to be over 100,000. For the city to be an American City, we could think of code such as “COUNTRYCODE = ‘USA'” and for the population being over 100,000 it could be “POPULATION > 100000.” As we want both conditions to be met at the same time, we will be using “AND.” So putting all of this together, the code below looks like a great idea.
SELECT * FROM CITY WHERE COUNTRYCODE = ‘USA’ AND POPULATION > 100000;
Try running this code and if you get the output below, you’ve passed the challenge (and earned some points too)!
Please try out the other four exercises, they shouldn’t be too hard with all the material covered in this lesson and will give you some good experience in running MySQL code.
You can watch a video of a brief walk through of this topic here.