SQL Commands (DQL, DDL, DML)

Deep dive into Data Query Language (retrieving), Data Definition Language (structuring), and Data Manipulation Language (modifying).

Data Query Language (DQL)

DQL is used to fetch data from the database. The most common command is SELECT. It allows you to retrieve specific columns, filter rows, aggregate data, and join multiple tables.

Anatomy of a SQL Query

SELECT

T.country, SUM(T.score)

FROM

table_name AS T

JOIN

other_table AS O ON T.id = O.ref_id

WHERE

T.active = true

GROUP BY

T.country

HAVING

SUM(T.score) > 100

ORDER BY

SUM(T.score) DESC

LIMIT

Logical Order of Execution

SQL doesn't execute from top to bottom like other code. The database engine processes clauses in a specific order:

FROM / JOINIdentify tables & datasets

WHEREFilter rows

GROUP BYAggregate data

HAVINGFilter aggregated data

SELECTSelect columns

DISTINCTRemove duplicates

ORDER BYSort results

LIMIT / TOPRestrict row count

Common Patterns & Clauses

Retrieving & Filtering

Get specific columns for customers from 'Geometry':

SELECT first_name, countryFROM CustomersWHERE country = 'Germany';

Sorting Results

Sort by score (High to Low). If scores match, then alphabetically.

Note: Default sort is ASC (Ascending).

SELECT *FROM CustomersORDER BY score DESC, name ASC;

Grouping & Aggregation

Advanced

Concept:

GROUP BY: Groups rows that have the same values into summary rows.
HAVING: Filters groups (like WHERE, but for groups).

Note: WHERE filters rows before grouping. HAVING filters groups after aggregation.

Find countries with total customer score higher than 800:

SELECT country, SUM(score)FROM CustomersGROUP BY countryHAVING SUM(score) > 800;

Limiting Results

MySQL / PostgreSQL / SQLite

SELECT * FROM Customers LIMIT 5;

SQL Server (Microsoft)

SELECT TOP 5 * FROM Customers;