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.

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
```
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.