HackerRank SQL | Basic Select

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.

Solve:

SELECT * 
FROM city
WHERE population > 100000
    AND countrycode = 'USA';

Explanation:

Use (*) to retrieve all columns then filter data based on two condition.

Revising the Select Query II : Query the NAME field for all American cities in the CITY table with populations larger than 120000. The CountryCode for America is USA.

Solve:

SELECT name
FROM city
WHERE population > 120000
    AND countrycode = 'USA';

Explanation:

• SELECT name: Selecting only the name column for the result.
• WHERE population > 120000: Filtering cities to include only those with a population exceeding 120,000.
• AND countrycode = 'USA': Further filtering to include only cities where the countrycode is 'USA' (representing the United States).

It returns a list of city names meeting both conditions.

Select name column then use condition for filter.

Select All : Query all columns (attributes) for every row in the CITY table.

Solve:

SELECT *
FROM city;

Explanation:

Use (*) for select all columns.

Select By ID : Query all columns for a city in CITY with the ID 1661.

Solve:

SELECT *
FROM city
WHERE id = 1661;

Explanation:

• SELECT * selects all columns (attributes) for the city.
• WHERE id = 1661 filters the rows to return only the city that has an ID value of 1661.

The result will show all the information available for the city with ID 1661.

Japanese Cities’ Attributes : Query all attributes of every Japanese city in the CITY table. The COUNTRYCODE for Japan is JPN.

Solve:

SELECT *
FROM city
WHERE countrycode = 'JPN';

Explanation:

• Using SELECT * to select all columns (attributes) for each city.
• Filtering the rows where the countrycode is 'JPN' (the code for Japan).

It returns all information (like city name, population, district, etc.) for every city in Japan.

Japanese Cities’ Names : Query the names of all the Japanese cities in the CITY table. The COUNTRYCODE for Japan is JPN.

Solve:

SELECT name
FROM city
WHERE countrycode = 'JPN';

Explanation:

• Selecting the name column, which contains the city names.
• Filtering rows where the countrycode is 'JPN', the code for Japan.

So, it returns a list of city names in Japan.

Weather Observation Station 1 : Query a list of CITY and STATE from the STATION table.

Solve:

SELECT city,state
FROM station;

Explanation:

This query retrieves two columns, CITY and STATE, from the STATION table. It returns all rows, showing the city name and its corresponding state for each entry in the table.

Weather Observation Station 3 : Query a list of CITY names from STATION for cities that have an even ID number. Print the results in any order, but exclude duplicates from the answer.

Solve:

SELECT DISTINCT city
FROM station
WHERE id%2 = 0;

Explanation:

• DISTINCT city: Ensures that only unique city names are selected (no duplicates).
• WHERE id % 2 = 0: Filters the rows where the ID is even (the remainder when divided by 2 is 0).

The query returns a list of cities with even IDs, excluding duplicates.

Weather Observation Station 4 : Find the difference between the total number of CITY entries in the table and the number of distinct CITY entries in the table.

Solve:

SELECT COUNT(city)-COUNT(DISTINCT city)
FROM station;

Explanation:

This query calculates the difference between:

• COUNT(city): The total number of city entries in the STATION table, including duplicates.
• COUNT(DISTINCT city): The number of unique city entries, ignoring duplicates.

The result of COUNT(city) - COUNT(DISTINCT city) gives the number of duplicate city names in the table.

Weather Observation Station 5 : Query the two cities in STATION with the shortest and longest CITY names, as well as their respective lengths (i.e.: number of characters in the name). If there is more than one smallest or largest city, choose the one that comes first when ordered alphabetically.

Solve:

SELECT CITY, LENGTH(CITY) 
FROM STATION 
ORDER BY LENGTH(CITY),CITY LIMIT 1;

SELECT CITY, LENGTH(CITY) 
FROM STATION 
ORDER BY LENGTH(CITY) DESC, CITY LIMIT 1;

Explanation:

The first query finds the city with the shortest name from the STATION table. It orders cities by their name length (smallest first) and alphabetically, then picks the first one.

The second query finds the city with the longest name. It orders cities by their name length (largest first) and alphabetically, then picks the first one.

Weather Observation Station 6 : Query the list of CITY names starting with vowels (i.e., a, e, i, o, or u) from STATION. Your result cannot contain duplicates.

Solve:

SELECT DISTINCT city
FROM station
WHERE city LIKE 'a%' 
    OR city LIKE 'e%' 
    OR city LIKE 'i%' 
    OR city LIKE 'o%' 
    OR city LIKE 'u%';

-- OR  
  
SELECT DISTINCT city
FROM station 
WHERE city REGEXP '^[aeiouAEIOU]';

Explanation:

Both of your SQL queries aim to retrieve the distinct city names from the station table where the city names start with a vowel (either lowercase or uppercase).

First one:

• SELECT DISTINCT city: This selects distinct city names from the station table, ensuring there are no duplicate city names in the result.
• WHERE city LIKE 'a%' ... 'u%': The LIKE clause is used to match cities where the city name starts with the specified vowels (a, e, i, o, or u).
• 'a%' matches any city name starting with 'a'.
• 'e%', 'i%', 'o%', and 'u%' do the same for the other vowels.
• The OR operators are used to combine the multiple LIKE conditions, meaning the city name can start with any of the specified vowels.

Second:

• SELECT DISTINCT city: Again, this selects distinct city names from the station table.
• WHERE city REGEXP '^[aeiouAEIOU]':
• REGEXP is a regular expression operator that is more flexible than LIKE for pattern matching.
• The '^' character indicates the start of the string, meaning it will only match cities where the first character is one of the specified vowels.
• [aeiouAEIOU] defines a character class that includes both lowercase (aeiou) and uppercase vowels (AEIOU), so this query matches city names that start with either lowercase or uppercase vowels.

Key Differences:

• The first query uses multiple LIKE conditions and requires you to manually specify both lowercase and uppercase letters.
• The second query uses a regular expression (REGEXP), which is more concise and automatically matches both lowercase and uppercase vowels.

Weather Observation Station 7 : Query the list of CITY names ending with vowels (a, e, i, o, u) from STATION. Your result cannot contain duplicates.

Solve:

SELECT DISTINCT city FROM STATION
WHERE 
CITY LIKE '%A' OR
CITY LIKE '%E' OR
CITY LIKE '%I' OR
CITY LIKE '%O' OR
CITY LIKE '%U';

Weather Observation Station 8 : Query the list of CITY names from STATION which have vowels (i.e., a, e, i, o, and u) as both their first and last characters. Your result cannot contain duplicates.

Solve:

SELECT DISTINCT CITY 
FROM STATION 
WHERE CITY REGEXP '^[aeiou]' 
    AND city REGEXP '[aeiou]$';

Weather Observation Station 9 : Query the list of CITY names from STATION that do not start with vowels. Your result cannot contain duplicates.

Solve:

SELECT DISTINCT city
FROM station
WHERE city NOT REGEXP '^[aeiou]';

Weather Observation Station 10 : Query the list of CITY names from STATION that do not end with vowels. Your result cannot contain duplicates.

Solve:

SELECT DISTINCT city
FROM station 
WHERE city NOT REGEXP '[aeiou]$';

Weather Observation Station 11 : Query the list of CITY names from STATION that either do not start with vowels or do not end with vowels. Your result cannot contain duplicates.

Solve:

SELECT DISTINCT city
FROM station
WHERE city NOT REGEXP '^[aeiou]' 
    OR city NOT REGEXP '[aeiou]$';

Weather Observation Station 12 : Query the list of CITY names from STATION that do not start with vowels and do not end with vowels. Your result cannot contain duplicates.

Solve:

SELECT DISTINCT city
FROM station
WHERE city NOT REGEXP '^[aeiou]' 
    AND city NOT REGEXP '[aeiou]$';

Higher Than 75 Marks : Query the Name of any student in STUDENTS who scored higher than Marks. Order your output by the last three characters of each name. If two or more students both have names ending in the same last three characters (i.e.: Bobby, Robby, etc.), secondary sort them by ascending ID.

Solve:

SELECT name
FROM students
WHERE marks > 75
ORDER BY SUBSTRING(NAME, -3), ID A

Employee Names : Write a query that prints a list of employee names (i.e.: the name attribute) from the Employee table in alphabetical order.

Solve:

SELECT name 
FROM employee
ORDER BY name ASC;

Employee Salaries : Write a query that prints a list of employee names (i.e.: the name attribute) for employees in Employee having a salary greater than per month who have been employees for less than months. Sort your result by ascending employee_id.

Solve:

SELECT name
FROM employee
WHERE salary > 2000
    AND months < 10
ORDER BY employee_id ASC;

Thank for Reading !

Your Network is your Networth” — Tim Sanders

Connect on LinkedIn : https://www.linkedin.com/in/md-sawrab/

GitHub: https://github.com/md-sawrab