PostgreSQL Fundamentals
Week 13 covered PostgreSQL from setup to aggregate functions. Lots of SQL practice.
Getting Started
Installed PostgreSQL and learned the basics. Creating databases, connecting with psql, and understanding basic commands. The interface uses backslash commands for meta operations and SQL for data operations.
Small thing that surprised me: semicolons matter. They end commands. Forget one, and your query hangs, waiting for you to finish. Similar to forgetting the return in JavaScript, where the function keeps running.
SQL Queries
SELECT and Filtering
Retrieving data with SELECT. All columns or specific ones. Filtering with WHERE using various conditions.
Pattern matching with LIKE. Finding values with IN. Range checking with BETWEEN. Handling nulls with IS NULL.
Sorting results with ORDER BY. Ascending or descending. Multiple columns. Limiting results with LIMIT for pagination.
Data Manipulation
INSERT adds new records. Single rows or multiple at once. The RETURNING clause shows what was inserted without a separate query.
UPDATE modifies existing records. Single column or multiple. Conditional updates with WHERE. RETURNING shows what changed.
DELETE removes records. Always test with SELECT first. Use transactions for safety. TRUNCATE for clearing entire tables.
Aggregate Functions and GROUP BY
This was the challenging part. Aggregates calculate values across rows. COUNT for counting, SUM for totals, AVG for averages, MIN and MAX for extremes.
GROUP BY organizes data into groups before aggregating. Instead of one result, you get one result per group.
WHERE vs HAVING
The key difference was that it took practice to understand.
WHERE filters individual rows before grouping. It runs first, deciding which rows to include in the aggregation.
HAVING filter groups after aggregation. It runs after GROUP BY, deciding which grouped results to show.
Example: finding cities with more than 2 users where those users are over 25.
WHERE age > 25 filters the rows first. Then GROUP BY city groups them. Then HAVING COUNT(*) > 2 filters the groups. WHERE operates on rows, HAVING operates on groups.
Practice Exercises
Worked through many queries. Sales data analysis, grouping by categories and regions, calculating totals and averages, and finding top performers.
Counting distinct values, calculating percentages, combining multiple aggregates, and ordering grouped results. The repetition made the patterns clear.
GROUP BY with multiple columns, HAVING with multiple conditions, and combining WHERE and HAVING in one query. Each exercise reinforced when to use which clause.
Key Lessons
Semicolons end commands. Forget them, and queries hang.
WHERE filters before grouping, HAVING filters after. Different purposes, different timing.
Aggregate functions need GROUP BY when you want per-group results instead of one overall result.
Always test DELETE with SELECT first. Transactions provide safety.
Practice is how SQL concepts stick. Reading about GROUP BY is different from writing 20 queries using it.
Moving Forward
Continuing with SQL. Database design and relationships next. Connecting PostgreSQL to Node.js using the pg library. Starting MongoDB to understand NoSQL alongside relational databases.
Question: What SQL concept took you longest to understand?
Top comments (0)