Blog di SQL Arena
Tutorial SQL
Basi: SELECT e filtraggio
4 articoliWhat is SELECT … FROM in SQL? Reading from a table for beginners
SELECT is the heart of SQL. Every query starts here. We cover the syntax, picking specific columns, aliases, computed fields, common mistakes, and three practical exercises.
What is WHERE in SQL? Row filtering for beginners
WHERE is the row filter in SQL. Plain English with lots of examples: =, IN, BETWEEN, LIKE, IS NULL and AND/OR. Tables before/after, beginner pitfalls, and a quick practice at the end.
What is LIMIT in SQL? Capping the number of rows for beginners
LIMIT caps the query result at N rows. Essential for pagination, top-N queries, and previews. We cover the syntax, OFFSET, PostgreSQL vs MySQL differences, common mistakes, and three practice exercises.
What is ORDER BY in SQL? Sorting query results for beginners
ORDER BY sorts the result of a SQL query. We cover ASC and DESC, multi-column sorting, NULLs in sort order, and the classic ORDER BY + LIMIT for top-N. Plenty of examples and three exercises.
Unione di tabelle (JOIN)
3 articoliWhat is INNER JOIN in SQL? Joining tables for beginners
INNER JOIN combines rows from two tables by a shared key. The simplest and most common JOIN. We cover the syntax, ON clause, multi-table joins, common mistakes, and three practice exercises.
What is LEFT JOIN in SQL? A beginner's guide
LEFT JOIN keeps every row from the left table; missing matches on the right become NULL. We cover the syntax, the difference from INNER JOIN, finding orphans, and three exercises.
Aliases (AS) in SQL: table and column aliases for beginners
Aliases are short names for tables and columns in SQL. They make queries readable and become mandatory when JOINing tables with shared column names. Syntax, must-have cases, common mistakes, and three exercises.
Aggregazione e raggruppamento
2 articoliWhat is GROUP BY in SQL? Grouping rows for beginners
GROUP BY is the SQL command for "collapse rows into groups and aggregate". Plain words: how to get "how many orders each customer has" in a single query. What you can and can't put in SELECT, the difference from DISTINCT, GROUP BY on multiple columns, and common pitfalls.
What is HAVING in SQL? Filtering groups for beginners
HAVING is the filter that runs after GROUP BY and applies to aggregates. Plain words: WHERE filters input rows, HAVING filters output groups. We'll cover the difference, typical patterns (top-N, anomalies), and why WHERE and HAVING get confused.
Sottoquery e DISTINCT
4 articoliWhat is DISTINCT in SQL? Unique values for beginners
DISTINCT means "remove duplicates". Plain words: unique values of a column or combination of columns, the difference from GROUP BY, NULL handling, and the PostgreSQL-specific DISTINCT ON for "one row per group". With before/after tables and common pitfalls.
What is IN with a subquery in SQL? Membership check for beginners
IN with a subquery filters "rows where a column's value appears in another query's result". Plain words: filtering by a dynamic list, the difference from a literal list, the NOT IN-with-NULL trap, and comparison with EXISTS. With tables and common pitfalls.
What is EXISTS in SQL? Existence check for beginners
EXISTS asks "is there at least one row matching the condition?". Plain words: a filter on the presence of a related record (e.g. "customers with at least one order"), the difference from IN with a subquery, NOT EXISTS, and NULL behavior.
What is a scalar subquery in SQL? Single value in SELECT for beginners
A scalar subquery is a SELECT that returns exactly one value and slots into a column position or WHERE expression. Plain words: pull one field from a related table, add a summary number to each row of a report, use as a constant in a condition. With tables and common pitfalls.
Funzioni finestra
5 articoliWhat is ROW_NUMBER in SQL? Row numbering for beginners
ROW_NUMBER assigns "a sequential number to each row". Plain words: the first window function worth learning. Descending numbering, numbering within groups via PARTITION BY, the classic top-N per group pattern, and dedup. With tables and common pitfalls.
What are RANK and DENSE_RANK in SQL? Ranking with ties for beginners
RANK and DENSE_RANK are ranking functions where equal values get equal ranks. Plain words: the difference between ROW_NUMBER (always unique), RANK (ties get equal rank with gaps after), and DENSE_RANK (equal rank without gaps). With tables, an Olympic-style example, and common pitfalls.
What is PARTITION BY in SQL? Groups inside a window for beginners
PARTITION BY is the part of OVER that splits rows into groups for window functions. Plain words: like GROUP BY, but rows don't collapse — each row stays, with its group's aggregate appended. The difference from GROUP BY, typical patterns, and aggregate behavior inside windows.
What are LAG and LEAD in SQL? Neighbouring rows in a window for beginners
LAG and LEAD return the value from the **previous** or **next** row of a window. Plain words: day-over-day delta, time-to-next-event, price change — tasks that previously required self-joining a table. With tables and common pitfalls.
Window functions in SQL: ROW_NUMBER, RANK, LAG/LEAD in practice
Window functions are the analyst's most-used tool in SQL. We'll break down ROW_NUMBER, RANK, LAG/LEAD and PARTITION BY through real cases: top-N per group, day-over-day metrics, cumulative sums.
CTE (WITH)
1 articoloModifiche ai dati (DML)
3 articoliWhat is INSERT in SQL? Adding rows for beginners
INSERT is the SQL command for "add a new row". Plain words: basic syntax, batch inserts of multiple rows in one shot, INSERT FROM SELECT, RETURNING to get back generated IDs, and UPSERT via ON CONFLICT for idempotent operations. With before/after tables and common pitfalls.
What is UPDATE in SQL? Modifying rows for beginners
UPDATE is the SQL command for "change data in existing rows". Plain words: what to change, why WHERE is non-negotiable, how to update several columns at once. Before/after tables, beginner pitfalls, a quick recap, and three practice tasks at the end.
What is DELETE in SQL? Removing rows for beginners
DELETE is the SQL command for "remove rows from the table". Plain words: what to delete, why WHERE is non-negotiable, soft-delete vs hard-delete, the difference from TRUNCATE, and ON DELETE CASCADE. Before/after tables, common mistakes, a quick recap, and three practice tasks.
Schema (DDL)
2 articoliWhat is CREATE TABLE in SQL? Creating a table for beginners
CREATE TABLE is the SQL command for "create a new table". Plain words: which columns and what types, what NOT NULL, DEFAULT, PRIMARY KEY, and FOREIGN KEY do, and why thinking about the schema upfront pays off. With before/after tables, common beginner mistakes, and three exercises.
What is ALTER TABLE in SQL? Changing table structure for beginners
ALTER TABLE is the SQL command for "change the structure of an existing table". Plain words: add a column, drop, rename, change a type, add and drop constraints. Plus the main pain on production — long table locks and the add → backfill → drop pattern for safe migrations.
Stringhe e date
3 articoliWhat are LOWER, UPPER, LENGTH in SQL? String functions for beginners
String functions are a daily SQL tool: case normalization (LOWER, UPPER), length (LENGTH), whitespace trimming (TRIM), substrings (SUBSTRING), replace (REPLACE). Plain words: case-insensitive search, data cleanup, and Unicode gotchas.
What is CONCAT in SQL? String concatenation for beginners
CONCAT means "join strings together". Plain words: three forms (||, CONCAT, CONCAT_WS), how each treats NULL differently, and why CONCAT_WS is the best choice for addresses and names. With tables and common pitfalls.
What is EXTRACT in SQL? Year, month, day from a date for beginners
EXTRACT pulls a piece out of a date: year, month, day, hour, day of week. Plain words: how to group by year/month, filter by weekday, count seconds via EPOCH. Compared with DATE_TRUNC and PostgreSQL/MySQL differences.
CASE e NULL
3 articoliWhat is CASE WHEN in SQL? Conditional logic for beginners
CASE WHEN is "if/else inside SQL". Plain words: how to put conditions right into SELECT, the difference between searched CASE and simple CASE, bucketing numbers into categories, pivoting with one formula, and conditional aggregates. With tables and common pitfalls.
What is COALESCE in SQL? Handling NULL for beginners
COALESCE returns the first non-NULL value from a list. Plain words: defaults for missing data, fallback chains (nickname → name → 'Guest'), safe arithmetic, and pairing with NULLIF. With tables and common pitfalls.
What is NULLIF in SQL? Safe division and cleanup for beginners
NULLIF returns NULL if two values are equal. Plain words: safe division via NULLIF(x, 0), cleaning placeholder values like '' or 'unknown', and pairing with COALESCE for clean data handling. With tables and common pitfalls.
Altro
2 articoliWhat is WITH … AS (CTE) in SQL? Common Table Expressions for beginners
WITH … AS is a "named intermediate result", aka CTE (Common Table Expression). Plain words: a way to break complex queries into readable steps, reuse computed values, and write SQL you'll actually want to re-read later. With tables and common pitfalls.
What are COUNT, SUM, AVG, MIN, MAX in SQL? Aggregate functions for beginners
Aggregate functions are tools for "computing something across a group of rows". COUNT — how many rows, SUM — total, AVG — average, MIN/MAX — smallest and largest. Plain words: the difference between COUNT(*) and COUNT(column), how NULL affects aggregates, common scenarios, and pitfalls.