SQL queries, relational design, and the notes I come back to.

I keep my database practice here as part of my Java/Spring Boot backend learning. PostgreSQL is my working database: a place to write queries, check what they return, and understand the data behind an application.

Recorded work: 1 environment exercise · 4 LeetCode SQL solutions

Learning map  ·  Queries & practice  ·  Use the lab  ·  Connected labs

SQL learning map

From reading rows to designing tables and understanding query plans. Linked entries open recorded work; the remaining topics are still to add. The saved SQL roadmap is my broader reference.

01 · Environment and query fundamentals

• Connection context and expressions — database, role, schema, and arithmetic with SELECT.
• Filtering practice — WHERE, AND, OR, IS NULL, and LENGTH.
• Data types, casts, aliases, sorting, DISTINCT, and LIMIT.
• Boolean conditions, operator precedence, and how NULL affects comparisons.

02 · Relationships and schema design

• Entities, attributes, candidate keys, primary keys, and foreign keys.
• One-to-one, one-to-many, and many-to-many relationships.
• Chen ER, relational ERD, EER, and translating a model into tables.
• Normalization, constraints, and the tradeoffs behind a schema.
• Inner, outer, and self joins; unmatched rows and accidental duplicates.

03 · Aggregation and multi-step queries

• Aggregate functions, GROUP BY, and HAVING.
• Subqueries, correlated queries, EXISTS, and set operations.
• CTEs for readable intermediate steps.
• Window functions for ranking, running totals, and comparisons across rows.

04 · Data changes and integrity

• CREATE, ALTER, INSERT, UPDATE, and DELETE.
• Defaults, identity columns, uniqueness, and referential integrity.
• Transactions, COMMIT, ROLLBACK, and savepoints.
• Isolation, concurrent updates, and the mistakes a constraint can prevent.

05 · Performance and application work

• Indexes, EXPLAIN, execution plans, and measured query comparisons.
• Views and PostgreSQL functions.
• Parameterized queries through JDBC, then persistence with Spring Boot.
• Schema migrations and transaction boundaries in an application.

Java + Spring Boot + PostgreSQL is the direction I'm working towards. Application integration is still ahead of the SQL exercises here.

Queries & practice

The current files are small, focused exercises. Each keeps its purpose or problem link beside the SQL so I can revisit the reasoning, change a condition, and check an edge case.

Entry	What I practise
01 · PostgreSQL environment	Five read-only statements: connection context and arithmetic.
1757 · Recyclable and Low Fat Products	Keep rows where both conditions hold — AND.
595 · Big Countries	Match either threshold — OR.
584 · Find Customer Referee	Exclude one referee and retain missing values — IS NULL.
1683 · Invalid Tweets	Filter text by character count — LENGTH.

_{A worked illustration of the saved 1757 query, using sample rows. Result order is unspecified without ORDER BY.}

Revision checks

• Environment: explain what database, role, and schema mean before running the file again.
• AND / OR: identify which rows change when I switch the operator or add parentheses.
• Missing values: explain why referee_id <> 2 alone drops rows with a NULL referee.
• Boundaries: test the exact area, population, or text-length threshold as well as values on either side.

LeetCode supplies the problem tables. Use the problem link in each file with its PostgreSQL editor; the environment exercise does not create those tables. HackerRank practice is still to add.

Schema sketchbook · one model, two views

These are conceptual README sketches, separate from the runnable exercises. A topic contains exercises; each exercise belongs to one topic.

The first view describes the relationship; the second shows the tables and foreign key. Neither is an implemented schema in this lab yet.

Working with the lab

PostgreSQL · DataGrip · VS Code · Git

I use DataGrip for queries, VS Code for repository files and notes, and Git to keep attempts and corrections. The PostgreSQL setup guide covers both DataGrip and psql, with expected results for the first exercise.

Setup and first run

Have a running PostgreSQL server and an existing practice database. Then clone the lab:

git clone https://github.com/codewithmahad/SQL_LAB.git
cd SQL_LAB

In DataGrip, open 01_environment.sql, select your database connection, and run one statement at a time with Ctrl+Enter. It reads session information and evaluates expressions; it needs no sample tables.

For psql, replace each YOUR_... value with your connection details and run from the repository root:

psql -X --set=ON_ERROR_STOP=on -h "YOUR_HOST" -p "YOUR_PORT" -U "YOUR_ROLE" -d "YOUR_DATABASE" -f "learning/postgresql/01_fundamentals/01_environment.sql"

The first three results depend on the connection. The arithmetic results are 30 and 5300. Enter a password if prompted; keep credentials out of repository files.

Connection refused? Check that the server is running and the host and port match. Authentication failed? Check the role, password, and server authentication settings.

Repository layout and references

• learning/postgresql/ — fundamentals and the setup guide.
• practice/leetcode-sql-50/ — saved problem solutions, grouped by topic.
• resources/ — the saved SQL roadmap.
• assets/ — README artwork, source generators, and credits.

PostgreSQL: Tutorial · SQL reference · Session functions · psql.

Practice: LeetCode SQL 50 · HackerRank SQL. DataGrip: Run queries.

Found a mistake? Open an issue with the file, query, and result.

How the labs fit together

This is the database side of my backend learning: queries, relational models, and PostgreSQL. Alongside it, I work through Java language concepts, practise DSA in Java, and keep my Linux and Bash coursework notes.

These profiles bring together my wider coding practice.

---

_{Maintained by Shaikh Mahad  ·  Portfolio  ·  Artwork credits  ·  Back to top ↑}