UNDERSTANDING BASIC SELECT SQL QUERIES

What is SQL?

SQL (Structured Query Language) is a language used to interact with databases. A database is just a collection of organized data. For example:

• Student records
• City information
• Weather data

SQL helps us:

• Store data
• Retrieve data
• Update data
• Delete data

Why do we need SQL?

Imagine you have a huge table with thousands of rows.

Without SQL:

• You would manually search data

With SQL:

• You can get exactly what you want in seconds

For example:

• Find all cities
• Find cities in Japan
• Count how many unique cities exist

SQL makes data powerful and usable.

Understanding Tables

STATION Table

• ID
• CITY
• STATE
• LAT_N
• LONG_W

CITY Table

• ID
• NAME
• COUNTRYCODE
• DISTRICT
• POPULATION

Each row = one record
Each column = one attribute

What is SELECT?

SELECT is one of the most important SQL command.

It is used to retrieve data from a table.

Basic syntax:

SELECT column_name FROM table_name;

1. Select all columns

SELECT * FROM CITY;

* means "all columns".
Used when you want full data

2. Select specific columns

SELECT CITY, STATE FROM STATION;

3. Select with condition (WHERE)

SELECT * FROM CITY WHERE ID = 1661;

4. Filtering using text condition

SELECT * FROM CITY WHERE COUNTRYCODE = 'JPN';

• Keyword is filtering so use WHERE

5. Removing duplicates (DISTINCT)

SELECT DISTINCT CITY FROM STATION;

6. Using functions (COUNT)

SELECT COUNT(CITY) FROM STATION;

7. Difference between total and unique cities

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

8. Filtering cities not starting with vowels

SELECT DISTINCT CITY
FROM STATION
WHERE LOWER(SUBSTR(CITY, 1, 1)) 
NOT IN ('a','e','i','o','u');

SUBSTR(CITY,1,1) -> first letter
LOWER() -> handles uppercase
NOT IN -> excludes vowels

MAIN KEYWORDS

• SELECT = fetch data
• * = all columns
• WHERE = filter rows
• DISTINCT = remove duplicates
• COUNT() = aggregate data
• SUBSTR() = extract part of text
• LOWER() = handle case sensitivity