SelectQueryI.sql

-- Question link - https://www.hackerrank.com/challenges/revising-the-select-query/problem?isFullScreen=true
-- Language: T-SQL
-- Database: MySQL/PostgreSQL/SQL Server compatible

/*
Problem: 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.

Table Schema:
CITY
- ID (INTEGER)
- NAME (VARCHAR2)
- COUNTRYCODE (CHAR(3))  
- DISTRICT (VARCHAR2)
- POPULATION (INTEGER)

Input Format:
The CITY table is described as follows:
Field       | Type
------------|----------
ID          | NUMBER
NAME        | VARCHAR2(17)
COUNTRYCODE | CHAR(3)
DISTRICT    | VARCHAR2(20)
POPULATION  | NUMBER
*/

-- Solution:
SELECT * FROM city
WHERE (population > 100000) AND countryCode='USA';

/*
Explanation:
- SELECT * retrieves all columns from the table (ID, NAME, COUNTRYCODE, DISTRICT, POPULATION)
- FROM city: Specifies the CITY table as the data source
- WHERE clause filters rows based on two conditions:
  1. population > 100000 (cities with more than 100,000 people)
  2. countryCode='USA' (only American cities)
- AND operator ensures both conditions must be satisfied simultaneously

Expected Output:
Returns all columns for American cities with population exceeding 100,000.
Each row will contain: ID, NAME, COUNTRYCODE, DISTRICT, POPULATION

Performance Notes:
- This query performs a table scan with conditional filtering
- Consider indexing on COUNTRYCODE and POPULATION for better performance
- The query is compatible with standard SQL databases
*/