Lesson 22
Revising the Select Query I | SQL Basic Select | HackerRank Solution
Summary
SQL Lesson Summary: Select Query for American Cities
In this lesson, the focus is on revising the first select query from the HackerRank SQL section, specifically concerning the city
table. The key elements of the task include:
-
Table Structure: The
city
table contains the following columns:- ID
- Name
- Country Code
- District
- Population
-
Query Requirements:
- Select all columns for entries where:
- The country code is 'USA'.
- The population is greater than 100,000.
- Select all columns for entries where:
SQL Query Breakdown
-
Selecting All Columns: Use
SELECT *
to get all the columns.SELECT * FROM city
-
Filtering Conditions:
- To filter for American cities:
WHERE country_code = 'USA'
- To filter for populations greater than 100,000:
AND population > 100000
- To filter for American cities:
-
Complete SQL Query: Combining these, the final SQL query looks like this:
SELECT * FROM city WHERE country_code = 'USA' AND population > 100000;
Output
Running the code yields a list of cities in the USA with populations exceeding 100,000. The code is then submitted for evaluation.
This lesson outlines the steps and considerations needed to effectively filter data in a SQL query based on specific criteria.
Video Transcript
Hey everybody, welcome to another lesson I will do, revising the select query number
one from basic select subsection of the SQL section of Hacker Rank.
So we're given this stable city that has the columns ID, name, country, codes, district
and population.
We're asked to query all columns that is the star through all American cities.
So we have to filter by only USA.
And the population is larger than 100,000.
We also have to filter by population greater than 100,000.
So let's get started.
So I'm just going to select since it wants all the columns, right?
We say star from what's the name of the table, city.
Now that's great, but we need to get only the American city.
So we have to say where and we have to say country, code is equal to and usna.
But because we also have the population constraint, we also have to say and population,
greater than 100,000.
Okay, let's see what we get when I click run code.
And this is what we get.
We got the list of rows with city entries that are only for new USA instead of all the countries.
I'm going to click submit code.
And that was revising the select query number one.
No comments yet (loading...)
No comments yet (loading...)
Did you like the lesson? 😆👍
Consider a donation to support our work: