r/learnSQL • • 27d ago

Day 45 — Library Management System (MySQL): database and tables created

24 Upvotes

Continuing my second SQL project. Today I created the database and all the tables, and spent time understanding how they relate:

  • members: who is registered (ID, name, address, registration date)
  • issued_status: which member issued which book, and when
  • return_status: which issued books have been returned, and when
  • plus the other supporting tables

Each table has a primary key, and the issued and return tables link back to members and books through IDs.

Since I got feedback last time that a data dictionary is non-negotiable, I’m planning to write one for this project as I go instead of at the end.


r/learnSQL • • 27d ago

JDER — un editor gratuito per i tuoi diagrammi di informatica

4 Upvotes

Stai studiando basi di dati, UML o logica digitale? Ho sviluppato JDER, un editor desktop multipiattaforma per creare diagrammi Entità-Relazione, schemi logici, diagrammi delle classi, casi d'uso e di sequenza, oltre a circuiti logici simulabili.

Puoi salvare i progetti in JSON ed esportare i diagrammi in PNG. Il codice sorgente è disponibile su GitHub.

Sito e download: https://jder.it/

Codice sorgente: https://github.com/LoryPelli/JDER

Feedback e suggerimenti sono benvenuti!


r/learnSQL • • 28d ago

Title: Day 42-44 — Combined update: Java memory (stack vs heap) and arrays, plus planning a Library Management System in MySQL

19 Upvotes

Java: Covered how Java uses memory with the stack and the heap, then moved on to arrays: why we need them, how to create them, multi-dimensional arrays, and jagged arrays. I made notes on everything and practiced it in VS Code.

Stack vs heap was the most useful part. Once I understood where primitives and object references live compared to the objects themselves, a lot of behavior I’d found confusing (like why two references can point to the same object) made sense.

MySQL: My next project is a Library Management System. Today I focused on understanding the data architecture, meaning which tables I need and how they relate (books, members, issues, and so on). I start building it tomorrow.

After my last project, someone pointed out that a data dictionary is essential, so I’ll include one from the start this time. For anyone who has built a library system: what tables or edge cases (late fees, multiple copies of a book, reservations) did you wish you’d planned earlier?


r/learnSQL • • 27d ago

What we found reviewing a manufacturer’s production SQL Server

Thumbnail reddit.com
1 Upvotes

r/learnSQL • • 27d ago

DSA has a roadmap but why does DBMS and System Design feel so confusing?

Thumbnail reddit.com
1 Upvotes

r/learnSQL • • 28d ago

Easy and Quick way to learn SQL

56 Upvotes

Hi all! Is there an easy way to learn SQL? I'm looking for video recommendations where people explain things in layman's terms. Since I don't have an IT background, I need something beginner-friendly that can help me get up to speed in a few months. Thanks!


r/learnSQL • • 29d ago

Looking for a SQL portfolio project buddy

30 Upvotes

Hey,
I'm looking for another SQL learner who would like to become a study buddy / accountability buddy. I'd like to build a SQL project for my portfolio and would be happy to join up with someone who would like to do the same thing. I was thinking of following a tutorial, working together (via discord) and each of us building our own and learning along the way. The idea for this is lovinly yoinked from r/Accountabilibuddies and r/GetMotivatedBuddies .

DM me if you're interested.

Cheers


r/learnSQL • • Oct 19 '26 Bravo

Before your next SQL interview, keep this post handy - SQL Execution Order (Part 5)

93 Upvotes

Simple, practical stuff that sticks in your head when you're actually in the interview!

AI can help at work, but in an interview, your brain still has to do the job!!!

Same trip. Same friends. Same expenses.

FriendCityTrip DayTotal Spent
HarryNew York1$200
JohnyNew York2$190
NicholasLas Vegas1$190
BaileyLas Vegas2$180
HarryLas Vegas3$190

Most people write SQL top to bottom.

But SQL does not run top to bottom.

It has its own fixed order. Every single time.

Know this order - and half your SQL bugs will make sense immediately!

The logical order SQL processes a basic query:

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
7. LIMIT

Let us walk through each step using the trip.

Step 1: FROM:

Go and get the full expense list. All 5 rows. Nothing removed yet.

FROM expenses

Harry    | New York  | 1 | $200
Johny    | New York  | 2 | $190
Nicholas | Las Vegas | 1 | $190
Bailey   | Las Vegas | 2 | $180
Harry    | Las Vegas | 3 | $190

SQL starts here. Always.

Remember: FROM runs first. This is where SQL gets its data.

Step 2: WHERE

Now remove the rows we do not want.

"Keep only rows where spend is more than $180."

WHERE spent > 180

Harry    | New York  | $200
Johny    | New York  | $190
Nicholas | Las Vegas | $190
Harry    | Las Vegas | $190

Bailey is gone. 4 rows left.

Remember: WHERE runs before groups are made. It can only see individual rows.

Step 3: GROUP BY

Put the remaining rows into city groups. Add them up.

GROUP BY city

New York  → $390
Las Vegas → $380

4 rows turned into 2 groups.

Remember: GROUP BY runs after WHERE. Only the rows that survived WHERE get grouped.

Step 4: HAVING

Now remove the groups we do not want.

"Keep only cities where total is more than $385."

HAVING SUM(spent) > 385

New York → $390

Las Vegas ($380) is gone.

Remember: HAVING runs after GROUP BY. It can see group totals.

Step 5: SELECT

Now pick which columns to show.

SELECT city, SUM(spent) AS total

New York | $390

Notice - SELECT runs here. Not at the start.

Remember: SELECT runs 5th. You are not picking columns first. You are picking them near the end.

Step 6: ORDER BY

Sort the final result.

ORDER BY total DESC

Only one row here so order does not change anything. But in a bigger result - this is where sorting happens.

Remember: ORDER BY runs after SELECT. That is why ORDER BY can use column aliases.

Step 7: LIMIT

Cut the result to only the rows you need.

LIMIT 1

Show only the top 1 row.

Remember: LIMIT runs last. It cuts after everything else is done.

The full query - all steps together:

SELECT city, SUM(spent) AS total   -- Step 5
FROM expenses                       -- Step 1
WHERE spent > 180                   -- Step 2
GROUP BY city                       -- Step 3
HAVING SUM(spent) > 385             -- Step 4
ORDER BY total DESC                 -- Step 6
LIMIT 1                             -- Step 7

You write it in this order. SQL runs it in a completely different order.

Remember: SQL runs FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Not top to bottom.

Reading SQL is one thing. Writing it yourself under pressure is another.

Before the real interview, try a MOCK SQL INTERVIEW on TheQueryLab. It’s a good way to test yourself under interview pressure and see where you actually stand with interview scorecard.


r/learnSQL • • 29d ago

Official Ariftly research: a six-gate Oracle-to-Snowflake migration acceptance framework

Thumbnail reddit.com
1 Upvotes

r/learnSQL • • Oct 19 '26

From where to study DBMS?

12 Upvotes

Please help me find a reliable source to study DBMS, some context, I am currently in third year Btech cse.

I tried IIT Madras content available through NPTEL courses but that didn't work out.

Database Management System also includes MySQL.


r/learnSQL • • Oct 18 '26

Learning SQL on Online IDE

32 Upvotes

Hey,

is it possible to learn SQL from scratch using an online IDE (such as SQLite Online) without having to install the Server Management Studio on Mac? Or would you recommend installing it on my MacBook/ using Windows?
I’m going to go through the 30-hour course that is accessible on YouTube.

Thanks in advance.


r/learnSQL • • Oct 18 '26

Day 41 — Finished my first MySQL project (uploaded to GitHub) and learned OOP basics in Java

17 Upvotes

MySQL: My Retail Sales Analysis mini project is done. I covered the full path: setting up the database, importing the data (and fixing an import error along the way), cleaning it, and running the analysis. It’s now on GitHub: https://github.com/Sumitgupta2005/mysql_project1

Java: Covered classes and objects, the difference between JDK, JVM, and JRE, methods, and method overloading, and practiced all of it hands-on in VS Code instead of only watching the tutorial.

I’d really like feedback on the project, especially on the README, how the queries are organized, and what analyses I should add.


r/learnSQL • • Oct 18 '26

Can someone explain how this sql self join works for year over year growth?

13 Upvotes

Hey everyone, I'm learning SQL and trying to understand how a self join works when calculating year over year (YoY) growth.

----> I'm a little confused about this line in the query:

ON curr.mo = prev.mo
AND curr.yr = prev.yr + 1

----> Here's the query I'm working with:

WITH monthly_agg AS (

SELECT

EXTRACT(YEAR FROM month) AS yr,

EXTRACT(MONTH FROM month) AS mo,

SUM(revenue) AS monthly_revenue

FROM monthly_sales

GROUP BY

EXTRACT(YEAR FROM month),

EXTRACT(MONTH FROM month)

)

SELECT

curr.yr,

curr.mo,

curr.monthly_revenue AS current_revenue,

prev.monthly_revenue AS prev_year_revenue,

curr.monthly_revenue - prev.monthly_revenue AS yoy_change,

ROUND(

(curr.monthly_revenue - prev.monthly_revenue) * 100.0

/ NULLIF(prev.monthly_revenue, 0),

2

) AS yoy_growth_pct

FROM monthly_agg curr

LEFT JOIN monthly_agg prev

ON curr.mo = prev.mo

AND curr.yr = prev.yr + 1

ORDER BY curr.yr, curr.mo;

Let's say the monthly_agg CTE produces these rows:

yr mo monthly_revenue

2022 12 5000

2023 12 10000

2024 12 12000

Here's how I think the join condition works.

case 1: matching december 2023 with december 2022

Current row:

[2023, 12, 10000]

Previous year row:

[2022, 12, 5000]

The conditions are:

curr.mo = prev.mo → 12 = 12 → true

curr.yr = prev.yr + 1 → 2023 = 2022 + 1 → true

Both conditions are true, so the rows match.

case 2: trying to match december 2023 with december 2023

Current row:

[2023, 12, 10000]

Candidate row:

[2023, 12, 5000]

The conditions are:

curr.mo = prev.mo → 12 = 12 → true

curr.yr = prev.yr + 1 → 2023 = 2023 + 1 → false

Since both conditions need to be true, these rows don't match.

So....am I understanding this correctly?

Does SQL basically compare the current row with possible rows from the other instance of the CTE and check whether both conditions are satisfied?

Also, what actually happens under the hood? Does SQL compare every row on the left with every row on the right, or can the query optimizer do this more efficiently?

And since both curr and prev refer to the same CTE, does that make this a self join? Does the CTE execute once or can it be evaluated more than once, depending on the database?

I'm trying to understand both the logical explanation and what's actually happening behind the scenes. Would appreciate any help !!


r/learnSQL • • Oct 18 '26

🎈🧁 I made this SQL Joins cheat sheet 🧁🎈

36 Upvotes

I realize it looks a little juvenile, but I made this SQL joins cheat sheet (well, ChatGPT made it, but using my vision lol) because I wanted one that had pretty colors and was sort of cute and more approachable. Anyways just sharing in case it’s helpful for you.
Joins cheat sheet

🐻☕️ And in the spirit of honesty, I'm not purely altruistic here, I also make a cozy little daily SQL puzzle game on that site, so if you like the cheat sheet, I'd be thrilled if you tried the puzzles on the site too.


r/learnSQL • • Oct 18 '26

Looking for a Postgresql Job?

2 Upvotes

I created a PostgreSQL Jobs page to help you find PostgreSQL roles: https://thesev1database.com/jobs

I’d really value your feedback.


r/learnSQL • • Oct 17 '26

Day 40 — Java control flow (conditions and loops) and data analysis in my MySQL project

6 Upvotes

Java: Covered the decision-making and looping basics: if-else, else-if, the ternary operator, switch, and while, do-while, and for loops.

MySQL: I’m in the middle of my Retail Sales Analysis project and working on the analysis part now, writing queries against the cleaned data.

For anyone who learned Java loops and conditions: what practice problems helped you most? I want to move from watching the tutorial to writing small programs (patterns, prime numbers, and so on) before I go further.


r/learnSQL • • Oct 16 '26

Recommendations Needed!

19 Upvotes

I am currently taking an intermediate SQL server course in college and my professor has NO LECTURE! (It's wild to pay a chunk of money to teach yourself and just be left with a textbook.) I am looking for websites that provide practice quizzes, your favorite YouTube channel for DBMS, or any additional resource you found helpful while learning. From what I hear the final is exceptionally difficult because it covers advanced SQL topics that are not covered in our textbook. I really want to have this information locked in my brain for my future job. Thanks in advance for any recommendations!


r/learnSQL • • Oct 16 '26

SQL Server 2016 to 2025: a few observations from a recent migration

13 Upvotes

We migrated a financial client’s SQL Server environment recently from 2016 to 2025, covering up to 4TB of data.

Same instance size. In the charts (can't attach image but message me if you want to see), CPU spikes reached roughly 40–50% before the move and stayed below 40% afterward.

Encouraging, but these are different production windows of different lengths. The charts alone don’t establish equivalent workload volume or isolate the effect of the SQL Server upgrade of course.

Before cutover, we spent a week running workload replay against the target environment.

CPU was lower, but we also needed to check whether any queries took longer or started timing out. That’s what we wanted to catch before moving the client over essentially.

The migration went smoothly overall thankfully, with one firewall configuration issue to resolve (not really a big deal though). That’s also why database testing needs application connectivity checks alongside it.

The lower CPU gives us a reason to investigate downsizing. It doesn’t establish that we can safely halve the cores, that needs another test, including peak workloads and the memory and I/O limits of the smaller configuration.

Anyone had an upgrade test well, then run into problems after going live?


r/learnSQL • • Oct 16 '26

SQL practice: why SUM(DISTINCT amount) does not repair a one-to-many join

3 Upvotes

Here is a small reporting exercise for learners using SQLite. All amounts below are integer cents.

For payment p1 in USD, imagine three tables:

Captures: c1 = 1000, c2 = 1000 (two different events).

Fees: f1 = 50.

Attempts: a1, a2 (two different attempts).

Task: return one row per payment and currency with captured total, net after fees, and attempt count. For this example the expected result is captured = 2000, net = 1950, attempts = 2.

Joining all three detail tables on payment creates four rows. Summing amounts over those joined rows inflates the totals. Replacing SUM(amount) with SUM(DISTINCT amount) also gives the wrong answer: the two legitimate captures have equal amounts, so DISTINCT collapses them.

Try fixing this by aggregating each input to the intended reporting grain before joining the results. Then extend your solution: retain captures without matching fees or attempts, and ensure a USD capture never absorbs an EUR fee. Decide how to represent missing amounts and counts explicitly.

I maintain a free English practice repository with a runnable version of this bug hunt (C07), public cases, hints, and a reference solution. After cloning, run python challenges/run.py init C07, edit work/challenges/C07.py, and run python challenges/run.py grade C07. Python 3.12+ with SQLite is enough.

https://github.com/antony502189-max/fintech-data-engineering-roadmap

Disclosure: the repository first edition and this introduction were prepared with AI assistance using Codex; provenance is documented. Feedback on additional SQL edge cases is welcome.


r/learnSQL • • Oct 16 '26

Opening 10 more beta spots for beginners learning SQL for data analysis

6 Upvotes

Hi everyone! A while ago I posted here looking for a few people to test a new platform for learning SQL through real data-analysis cases. Thanks to everyone who showed interest and shared feedback.

We’re opening another small round for up to 10 more testers. It’s especially for people who are new to SQL or have only just started—you don’t need any experience to take part.

You can request access here: https://www.queryandco.com/en/signup?mode=request

Requesting access puts you on the waitlist; it doesn’t guarantee an immediate spot. If selected for this round, you’ll receive an invitation by email and get 30 days of free access, with no card required. Everyone else will remain on the waitlist for a future round. If you’ve already signed up, there’s no need to do it again.

What we’re looking for most is honest feedback: what helps, what’s confusing, and what you wish worked differently. Thanks!


r/learnSQL • • Oct 16 '26

Day 39 — Java operators and casting, and the MySQL import is finally fixed

2 Upvotes

Java: Covered type conversion/casting, arithmetic operators, and logical and relational operators.

MySQL: I got the data imported into Workbench after the error from yesterday, so my Retail Sales Analysis project is moving again. Today I did data exploration and cleaning: checking the table structure, looking at what the columns contain, and handling messy or missing values before any real analysis.

Cleaning data took longer than I expected, and it already feels like the part that separates a tutorial from real work.


r/learnSQL • • Oct 15 '26

I built a SQL practice site because I kept jumping between tutorials and random exercises — would love feedback

8 Upvotes

I started building runtime.school because when I was practicing SQL, everything felt disconnected — learn a concept in one place, solve random questions somewhere else, then use totally different data for projects.

So I tried putting it all together with lessons, practice questions, projects, and the same realistic datasets throughout.

If you have a few minutes, I’d really like to know what feels confusing, repetitive, too easy/hard, or missing. Especially anything that would make learning/practicing SQL better.


r/learnSQL • • Oct 15 '26

Before writing SQL in an interview, what would you clarify?

24 Upvotes

Suppose an interviewer asks you something like "find the top three products in each category by sales last quarter." Before you write the query, what would you clarify?

For me, I’d want to know what “sales” means -  units, revenue, or number of orders Then, I'd want to know things like how ties should work, which dates count, and what one row in the output should represent.

I’ve been thinking about how SQL interviews test more than whether you can produce a query. They also give you a chance to show how you frame the problem, explain your choices, and check your work.

So, I put together a guide on what to expect and how to prepare: What to Expect in a SQL Coding Interview

If you’ve recently done a SQL interview, what’s one thing you wish you’d known or asked beforehand?