SQL for Beginners: Essential Concepts Made Simple

Source: Dev.to

Overview

SQL stands for Structured Query Language. It is the standard language used to communicate with databases. Think of SQL as the “English” you speak to a database when you want to get information, add new data, update records, or delete something. SQL allows you to perform the four main CRUD operations:

• Create → INSERT
• Read → SELECT
• Update → UPDATE
• Delete → DELETE

SQL also provides powerful clauses for filtering (WHERE), sorting (ORDER BY), grouping (GROUP BY, AVG, COUNT), combining tables (JOIN), and advanced analysis (NTILE).

Learning SQL becomes easier when you break it down into key concepts. This guide covers fundamental topics that every beginner should know, including JOINS, a comparison of Window Functions and GROUP BY, and essential SQL functions.

SQL JOINs

A JOIN combines rows from two or more tables based on a related column. The tables must share a column in common. The main types of joins are:

• INNER JOIN – Returns only rows that have matching values in both tables; rows without a match are excluded.
• LEFT JOIN (or LEFT OUTER JOIN) – Returns all rows from the left table and matching rows from the right table. If no match exists, columns from the right table are NULL.
• RIGHT JOIN (or RIGHT OUTER JOIN) – Returns all rows from the right table and matching rows from the left table. It is the opposite of a left join.
• FULL OUTER JOIN (or FULL JOIN) – Returns all rows from both tables, matching where possible and inserting NULL where there is no match.

Window Functions vs GROUP BY – What Is the Difference?

Both window functions and GROUP BY perform calculations across related rows, but they solve different problems:

• GROUP BY collapses rows into a single result per group, producing a smaller result set. It is typically used for aggregation and reporting. Non‑grouped columns cannot be selected unless they are aggregated as well.
• Window functions calculate values across a set of rows while preserving the original row details. The output retains the same number of rows as the input because the calculation is attached to each row using OVER (…). This is useful when you need both detailed rows and aggregated context (e.g., ranks, running totals, moving averages).

SQL Functions Every Beginner Should Know

ROUND

-- ROUND: rounds a number to a specified number of decimal places
ROUND(number, decimals)
-- Example:
SELECT ROUND(87.345, 2);  -- Returns 87.35

CONCAT (or || in PostgreSQL)

-- CONCAT (or || in PostgreSQL): concatenates strings
CONCAT(str1, str2, ...)
-- PostgreSQL example using ||
SELECT first_name || ' ' || last_name AS full_name
FROM employees;  -- Returns "John Smith"

TO_CHAR

-- TO_CHAR: formats dates (PostgreSQL syntax)
TO_CHAR(date, format)
-- Example:
SELECT TO_CHAR(exam_date, 'Day, DDth Month YYYY') AS formatted_date
FROM exams;  -- Returns "Friday, 15th March 2024"