QUERIES

1. Select All

Problem: Show all columns from the CITY table.

SELECT * FROM CITY;

This query displays all the data in the table.

1. Select By ID

Problem: Get details of the city with ID = 1661.

SELECT * FROM CITY WHERE ID = 1661;

The WHERE clause is used to filter a specific row.

3. Japanese Cities Name

Problem: Get names of all cities in Japan.

SELECT NAME FROM CITY WHERE COUNTRYCODE = 'JPN

';

Only the NAME column is selected here.

4. Japanese Cities Attributes

Problem: Get all details of cities in Japan.

SELECT * FROM CITY WHERE COUNTRYCODE = 'JPN';

This query returns all columns for cities in Japan.

5. Revising the Select Query

Problem: Get all American cities with population greater than 100000.

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

Multiple conditions are combined using AND.

6. Revising the Select Query 2

Problem: Get names of American cities with population greater than 120000.

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

Only the NAME column is required in this case.

*7. Weather Observation Station 1
*
Problem: Show CITY and STATE.

SELECT CITY, STATE FROM STATION;

This query selects two specific columns.

8. Weather Observation Station 4

Problem: Find the difference between total CITY entries and unique CITY entries.

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

This calculates how many duplicate city names exist.

9. Weather Observation Station 9

Problem: Get 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%';

DISTINCT removes duplicates, and NOT LIKE filters out cities starting with vowels.