First problem was Select By ID. The question was to select all columns from CITY table where ID = 1661.
SELECT *
FROM CITY
WHERE ID = 1661;
This was simple, we just filter using WHERE and ID. Since we need all columns, we used *.
Next was Japanese Cities Attributes. We need to get all columns for cities in Japan.
SELECT *
FROM CITY
WHERE COUNTRYCODE = 'JPN';
Here we filter rows where country code is JPN. Again * because all columns needed.
Next was Revising the Select Query 2. We need city names where population > 120000.
SELECT NAME
FROM CITY
WHERE POPULATION > 120000;
Here we only need NAME column so we did not use *.
Next was Weather Observation Station 9. We need city names that do not start with vowels.
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%';
I used NOT LIKE because we need cities not starting with vowels and DISTINCT to remove duplicates.
Next was Weather Observation Station 1. We need city and state from station table.
SELECT CITY, STATE
FROM STATION;
Here just selecting two columns.
Next was Revising the Select Query. We need all columns where population > 100000.
SELECT *
FROM CITY
WHERE POPULATION > 100000;
Used WHERE to filter population.
Next was Weather Observation Station 4. Count number of duplicate city entries.
SELECT COUNT(CITY) - COUNT(DISTINCT CITY)
FROM STATION;
This was interesting. Total cities minus unique cities gives duplicate count.
Next was Japanese Cities Name. We need names of cities in Japan.
SELECT NAME
FROM CITY
WHERE COUNTRYCODE = 'JPN';
Same logic as earlier but only NAME column.
Last was Select All SQL. Display all columns from CITY.
SELECT *
FROM CITY;
This is the most basic SELECT query.
After doing these problems I understood basic SELECT, WHERE, DISTINCT, COUNT, LIKE, and filtering. These are very basic SQL but very important because almost every query uses these concepts.
Top comments (0)