Qn 1. Query all columns for a city in CITY with the ID 1661.
Since the question asks for all columns for a specific city with ID 1661, I simply use SELECT * and filter the row using WHERE id = 1661.
SELECT * FROM city WHERE id=1661;
Qn 2. Query all columns for all American cities in the CITY table with populations larger than 100000. The CountryCode for America is USA.
Here I need all columns but only for cities in the USA with population greater than 100000, so I use SELECT * and apply both conditions together using AND.
SELECT *
FROM city
WHERE countrycode = 'USA' AND population > 100000;
The question asks for all attributes of Japanese cities, so I select all columns and filter rows where the country code is JPN.
Qn 3. Query all attributes of every Japanese city in the CITY table. The COUNTRYCODE for Japan is JPN.
The question asks for all attributes of Japanese cities, so I select all columns and filter rows where the country code is JPN.
SELECT *
FROM city
WHERE countrycode = 'JPN';
Qn 4. Find the difference between the total number of CITY entries and the number of distinct CITY entries.
I need the difference between total and unique city entries, so I count all city values, count distinct ones, and subtract the two.
SELECT COUNT(city) - COUNT(DISTINCT city)
FROM station;
Qn 5. Query the NAME field for all American cities in the CITY table with populations larger than 120000. The CountryCode for America is USA.
Only the name column is required for American cities with population above 120000, so I select name and apply both conditions in the WHERE clause.
SELECT name
FROM city
WHERE countrycode = 'USA' AND population > 120000;
Qn 6. Query the names of all the Japanese cities in the CITY table. The COUNTRYCODE for Japan is JPN.
Since only the names of Japanese cities are needed, I select the name column and filter rows where the country code is JPN.
SELECT name
FROM city
WHERE countrycode = 'JPN';
Qn 7. Query the list of CITY names from STATION that do not start with vowels. Your result cannot contain duplicates.
I need unique city names that do not start with vowels, so I use DISTINCT and exclude each vowel condition using multiple NOT LIKE clauses combined with AND.
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%';
Qn 8. Query all columns (attributes) for every row in the CITY table.
The question asks for all columns for every row, so I simply select everything from the city table without any conditions.
SELECT * FROM city;
Qn 9. Query a list of CITY and STATE from the STATION table.
Only the city and state columns are required with no filtering, so I directly select those two columns from the station table.
SELECT city, state
FROM station;
Top comments (0)