SQL-with-Problem-Solving/SelectQueryI.sql at main · Ak-Rajak/SQL-with-Problem-Solving · GitHub

History

50 lines (43 loc) · 1.52 KB

Raw

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

SQL-with-Problem-Solving/SelectQueryI.sql at main · Ak-Rajak/SQL-with-Problem-Solving · GitHub

Expand file tree

Breadcrumbs

SelectQueryI.sql

Latest commit

History

Breadcrumbs

SelectQueryI.sql

File metadata and controls