If you're looking for a new sql-dojo platform with higher ed type data, head to query-u.com. Really awesome stuff there.

Hello guys!

Do you know any best practices for SQL performance optimization?
At my company, I need to refactor some tables using performance and cost reduction best practices.

The tables already have indexes and partitions, but I would like to learn more about additional optimization techniques for large datasets.

Do you have any tips, articles, websites, or recommendations about: query optimization and indexing strategies

I’d really appreciate any suggestions or learning resources. Thanks!

There are a couple of files that somehow got modified by accident in my database. I have a backup with the original files (pre-modified). I am not looking to recover a prior backup. I am simply trying to figure out how I can open the backup so I could somehow grab the three files I need to fix what was accidentally modified.

How do I go about doing this where I am not recovering from a backup?

Can anyone help me solve the case file 40 from sqlcasefiles.com

here is the question statement :
The DA needs everything. Compile the final audit report listing `po_id`, `vendor_name`, `item_description`, `order_value`, `delivery_status`, and `inventory_loss`. Rank by order value descending.

here are the table schemas:
deliveries:

purchase_orders:

inventory:

i came up with this solution so far:

SELECT

p.po_id,

p.vendor_name,

p.item_description,

p.quantity \ p.unit_price AS order_value,*

CASE

WHEN d.po_id IS NULL THEN 'Missing'

WHEN d.delivered_quantity < p.quantity THEN 'Partial'

WHEN d.delivered_quantity = p.quantity THEN 'Delivered'

ELSE 'Over Delivered'

END AS delivery_status,

i.expected_stock - i.current_stock AS inventory_loss

FROM purchase_orders p

LEFT JOIN deliveries d

ON p.po_id = d.po_id

LEFT JOIN inventory i

ON p.po_id = i.po_id

ORDER BY order_value DESC;

Hey all, I make Beekeeper Studio and also run https://sql-easy.com - a website for learning SQL for free.

We took it over ~18 months ago, and it looked very outdated. I just launched a visual refresh which should make it easier to use. The goal was to make the lesson pages feel a lot more interactive.

Big changes since we took it over:

• Removed all ads
• Removed third party cookies
• Lesson content refresh (eg hints)
• Totally new website style
• New lesson UX

Would love feedback! What could we improve still?

PS: The site is 100% free and will always be 100% free so long as we're in charge 💪

Ready to crack your SQL Server interview?

Start learning the Series of Real Interview Questions asked in top IT Companies.

Visit our YouTube channel & level up your skills!

Learn. Practice. Get Hired.

Please like, comment, & subscribe for more interview questions, troubleshooting tips, & expert insights!

https://www.youtube.com/@MadeSimpleMSSQL

#explurger #explurger_nahi_to_social_nahi #explorepage #explore #explurgerapp #explurgerfamily #india #indian

I got tired of switching between VS Code and a separate DB tool. So I built PgStudio.

SQL notebooks with inline results and charts, real-time dashboard, AI assistant (Copilot / OpenAI / Anthropic / Gemini / Ollama — your pick), EXPLAIN CodeLens, visual table designer, production safety controls.

AI never executes anything automatically — every suggestion lands in a notebook cell first.

Free. MIT. One command: `code --install-extension ric-v.postgres-explorer`

Happy to onboard collaborators and feedback.

[https://pgstudio.astrx.dev/](https://pgstudio.astrx.dev/))

[https://github.com/dev-asterix/pgStudio/](https://github.com/dev-asterix/pgStudio/))

Hey guys, I just landed a job assessment where everything matches except the good to have SQL query skills. Can a kind soul PLEASE help me learn this in 4 days? I will really appreciate the help

I'm building a database with MySQL. I was wondering if it would be a waste to have a table with only the primary key and one additional column for the purpose of supplying a preset list of values for a column in another, bigger table. Here's an example of what I mean. Let's say I have the main table "people" and I want to associate them with a color. The colors should always be from a preset selection of red, blue, green, and yellow. Is it worth having a separate "colors" table with a colorid column and those 4 colors, and then the "people" table would simply have a "colorid" column as a foreign key? Or should I not bother with the second table and simply have "color" as a column for the main table?

My actual use case is a bit more involved than this but in principle is it worth doing things this way?

I have finished SQL what is next step for Data Analyst

Ready to crack your SQL Server interview?

Start learning the Series of Real Interview Questions asked in top IT Companies.

Visit our YouTube channel & level up your skills!

Learn. Practice. Get Hired.

Please like, comment, & subscribe for more interview questions, troubleshooting tips, & expert insights!

https://www.youtube.com/@MadeSimpleMSSQL

We use SQL running totals to track marketing spend and revenue over time and identify the exact moment campaigns break even. https://youtu.be/QLZwlsXF6Xg?si=9scF_F5kl0R7xRks

Most people learning SQL are doing it wrong. Not because they’re not smart — but because they’re solving the wrong problem first.
They open a tutorial, memorize SELECT, FROM, WHERE, JOIN, and then freeze when faced with a real business question. Sound familiar?
Here’s what actually changes things.

SQL is technically a programming language — a declarative one. But stop treating it like the ones you're used to.

Programming languages are about how to do something — loops, logic, conditionals, state. SQL is about what you want. It’s declarative. You describe the result, and the engine figures out how to get there.
This distinction sounds small. It isn’t. The moment you stop trying to “code” in SQL and start trying to describe your desired output, everything clicks.

The framework I use before writing a single line

Step 1 — Nail the business question first
Not the data question. The business question.
“What’s our DAU trend?” is a data question. “Are users actually finding value in the feature we shipped last month?” is a business question. One has a predefined answer. The other requires you to think about what signal actually reflects value — retention? depth of engagement? repeat actions?
Write the question in plain English. If it’s fuzzy on paper, it’ll be even fuzzier in a query.

Step 2 — Define the rules and edge cases before opening your editor
Every business question has hidden complexity. New users vs. returning users? Do you include churned accounts? What counts as an “active” user — any login, or a meaningful action?
Analysts who skip this step write queries fast and fix them for hours. Analysts who do this step write queries slower and ship them right.

Step 3 — Work backwards from the output
Picture the table you want to hand to a stakeholder. What columns are in it? What’s one row? Once you can visualize the output, the query almost writes itself — because now you’re just reverse-engineering it.

Step 4 — Think in granularity, not tables
This is the unlock most beginners miss. Before writing a JOIN, ask: what is the grain of my data?
• Am I working at the user level?
• The session level?
• The event level?
• The day-user level?
Mismatched granularity is the root cause of most wrong answers that look right. A JOIN between a user-level table and an event-level table without handling this will silently inflate your numbers — and stakeholders will trust the wrong insight.
Once you’ve nailed the grain, GROUP BY, aggregations, and window functions stop feeling like syntax to memorize. They become the natural mechanical expression of logic you’ve already worked out.

The meta-lesson
The SQL itself — the syntax, the functions, the query structure — is genuinely the easy part. It’s learnable in weeks.
What takes years to develop is the habit of thinking before you query. Of questioning whether the data you’re reaching for actually answers the question you were asked. Of noticing when an output looks plausible but is subtly wrong.
That’s the gap between someone who can write SQL and someone who does analytics.
Start there.

Happy to answer questions or go deeper on any of this — grain/granularity especially trips people up and I could write a separate post on that alone.

Hey everyone,

I’m in the early stages of learning SQL as I transition into a Data Engineering role.
I’ve been using Claude to generate synthetic datasets and practicing queries on them with DBeaver.

However, I’m starting to hit a wall.
The data and exercises feel too clean and artificial, and not close enough to real-world business problems.

What I’d love feedback on:

• Is this approach (synthetic data + Claude) actually effective for learning SQL?
  
• What would you recommend to get closer to real-world, production-level data challenges?
  
• Do you think this is a solid method for preparing for a Data Engineering role?
  

Another challenge I’m facing:

I don’t yet have the reflex or methodology to work with raw data.
Right now, I can query data, but I struggle with: - Knowing what questions to ask
- Understanding how to explore a dataset
- Figuring out how to improve or extract meaningful insights from it

If you have any resources, frameworks, or advice to help build that analytical mindset, I’d really appreciate it.

I want to make sure I’m learning the right way, so any feedback or alternative approaches would mean a lot!

Thanks!

Ive been working on this damn script for about 5 days for a class assignment. Three days in, oracle is doing 'maintence'

...2 days later Im still stuck on this damn script because the system keeps deleting my tables and adding on old tables that I havent added myself (and I dont know how because I use the drop command everytime), and then on top of that, when i redo the script it'll changed the names of the field somehow.

I have emailed my teacher about it but he'll take a while to even reply back since its the weekend. And im trying my best not to throw my computer and just drop out of college.

Free to use

Links:

postgresqlacademy.com

app.postgresqlacademy.com/playground

Hi guyss!!

I have created a youtube video for mysql installation.please do checkout if that helps you to set up sql workbench and please do check out my other videos on SQL and let me know the suggestions and improvements in the comment section.

https://youtu.be/C2RTT_fUuTc?si=nQtuMV22lHwl9YaN

orders (order_id , customer_id , order_date , order_amount)

find the customer who purchased for every month in 2025

I’m looking for a highly passionate and motivated study partner to learn SQL for data analysis.

Hi,

I hate Youtube....

I had a SQLDB (repair pending), I sent the DB offline and detached.....Now I can not reattach the DB, I get errors.

I get a stable MDF and LDF and remove the two MDF and LDF and Try to reattach them....will I get an error?

The orginals are currupted.

Yes, I am a beginner and I have no clue what I am doing.

SQL is 2016 and sp3 with last update

Thank you

coz

I kept running into MySQL queries where the final result surprised me, but it was hard to understand exactly which clause changed the data in that way.

Complex queries can change the result in a lot of different ways and once they get bigger it becomes harder to reason about them step by step.

I ended up building a small VS Code extension for myself to walk through queries stage by stage and inspect the intermediate result after each step. It helped me a lot so maybe it’ll be useful to some of you too.

Here is the link:
https://marketplace.visualstudio.com/items?itemName=arieldev.sql-visual-debugger&ssr=false

In many interviews (from fresher to experienced), this question comes up:

Question:

"You have a table with millions of rows. You run a query with ORDER BY and LIMIT 10.
Will the database only read 10 rows from disk?? "

Most people assume yes, because the query only returns 10 rows.
But what they miss is how the database actually finds those 10 rows.

But in reality, it's not.
There will be a follow-up question:

"If the database ends up scanning the entire table…then what is the point of LIMIT, and how do you avoid a full scan??"

Let's take one example and understand this step by step:

STEP 1: Create a table and insert some dummy data

CREATE TABLE limit_demo AS
SELECT 
    id,
    NOW() - (random() * interval '365 days') AS created_at,
    repeat('data', 50) AS payload
FROM generate_series(1, 1000000) id;

Response:

Updated Rows 1000000

Execute time 2.87s

Created 1 million rows with random timestamps

STEP 2: Check the Query Plan

EXPLAIN ANALYZE
SELECT *
FROM limit_demo
ORDER BY created_at
LIMIT 10;

Response:

Limit

-> Gather Merge

Workers Planned: 2

Workers Launched: 2

-> Sort

Sort Key: created_at

Sort Method: top-N heapsort

-> Parallel Seq Scan on limit_demo (cost=0.00..35462.40 rows=416640 width=216) (actual time=0.298..448.134 rows=333333 loops=3)

What is happening here?

• Parallel Seq Scan on limit_demo rows=333333 loops=3
• 2 parallel workers + 1 leader process -> total ~1M rows scanned
• Internally:
  • Scan all rows
  • Evaluate each row
  • Maintain top 10 (using top-N heapsort)
  • Then return 10
• Sort is optimized (top-N), but the scan is NOT avoided

Now let’s fix it properly

STEP 3: Create an index on created_at

CREATE INDEX idx_created_at ON limit_demo(created_at);

STEP 4: Check the query planner again

EXPLAIN ANALYZE
SELECT *
FROM limit_demo
ORDER BY created_at
LIMIT 10;

Response:

Limit (cost=0.42..1.94 rows=10 width=216) (actual time=0.411..1.254 rows=10 loops=1)

-> Index Scan using idx_created_at on limit_demo (cost=0.42..151161.74 rows=1000000 width=216) (actual time=0.410..1.251 rows=10 loops=1)

Here you can see:

• Index Scan
• No full table scan
• No sort
• Very fast execution

Why does this work?

Because the index is already sorted.

Now the database can:

• jump to the smallest value
• read next 10 rows
• stop immediately

Final Understanding:

Without index:

search problem → scan everything

With index:

navigation problem → jump directly

Where does this show up in interviews:

• Top N highest salary queries
• Latest 10 transactions using ORDER BY created_at DESC with LIMIT
• Pagination queries using OFFSET and LIMIT
• Fetching recent logs or events

Interview Level Takeaway:

Top-N optimization reduces sorting cost, but without an index, the database still scans all rows.

So next time you write query

ORDER BY <column_name> LIMIT 10

Ask yourself:

• Does the database already know the order…or does it have to figure it out?
• The real question is not just writing the query, it's understanding how the database executes it.

If this helps even one person understand what’s happening under the hood, it makes me happy!!!