Basic Select SQL Queries

Source: Dev.to

HackerRank SQL Practice

Question 1

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

Solution:

SELECT * FROM CITY WHERE ID = 1661;

Question 2

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

Solution:

SELECT * FROM CITY WHERE COUNTRYCODE = 'JPN';

Question 3

Task: Query the NAME field for all American cities in the CITY table with populations larger than 120,000. The COUNTRYCODE for the United States is USA.

Solution:

SELECT NAME
FROM CITY
WHERE POPULATION > 120000
  AND COUNTRYCODE = 'USA';

Question 4

Task: Query the list of city names from STATION that do not start with a vowel. The result must contain no duplicates.

Solution:

SELECT DISTINCT CITY
FROM STATION
WHERE CITY NOT LIKE 'A%'
  AND CITY NOT LIKE 'E%'
  AND CITY NOT LIKE 'I%'
  AND CITY NOT LIKE 'O%'
  AND CITY NOT LIKE 'U%';

Question 5

Task: Query a list of CITY and STATE from the STATION table.

Solution:

SELECT CITY, STATE
FROM STATION;

Question 6

Task: Query all columns for all American cities in the CITY table with populations larger than 100,000. The COUNTRYCODE for the United States is USA.

Solution:

SELECT *
FROM CITY
WHERE POPULATION > 100000
  AND COUNTRYCODE = 'USA';

Question 7

Task: Find the difference between the total number of CITY entries in the table and the number of distinct CITY entries in the table.

Solution:

SELECT COUNT(CITY) - COUNT(DISTINCT CITY) AS difference
FROM STATION;

Question 8

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

Solution:

SELECT NAME
FROM CITY
WHERE COUNTRYCODE = 'JPN';