BASIC QUERIES

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

The CITY table is described as follows:
Query 1
SELECT *
FROM CITY;

Explanation:

Here I am just getting all the data from the CITY table.
SELECT * means all columns and since there is no condition, it shows all rows.

Query 2
SELECT *
FROM CITY
WHERE ID = 1661;
Explanation:

In this query, I only want the row where ID is 1661.
So I used WHERE to filter.
SELECT * is used because I need all details of that city.

Query 3
SELECT *
FROM CITY
WHERE CountryCode = 'USA'
AND POPULATION > 100000;

Explanation:

Here I am filtering cities based on two conditions.
First, the city should be in USA.
Second, population should be more than 100000.
Both conditions must be true, so I used AND.

*Query 4
SELECT NAME
FROM CITY
WHERE CountryCode = 'USA'
AND POPULATION > 120000;
*
Explanation:

In this case, I only need the city names, not all columns.
So I used SELECT NAME.
The filtering is same as before but with population greater than 120000.

Query 5
SELECT NAME
FROM CITY
WHERE COUNTRYCODE = 'JPN';

Explanation:

This query gives names of cities in Japan.
I used WHERE COUNTRYCODE = 'JPN' to filter Japanese cities.

*Query 6
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%';
*
Explanation:

Here I don’t want cities that start with vowels.
So I used NOT LIKE for A, E, I, O, U.
% means anything after the first letter.
Also, I used DISTINCT to avoid duplicate city names.

**Query 7
SELECT COUNT(CITY) - COUNT(DISTINCT CITY)
FROM STATION;

**Explanation:

This is used to find duplicate city names.
COUNT(CITY) gives total entries.
COUNT(DISTINCT CITY) gives unique cities.
Subtracting them gives number of duplicates.

*Query 8
SELECT CITY, STATE
FROM STATION;
*
Explanation:

Here I am selecting only CITY and STATE columns.
So instead of *, I mentioned the column names