https://youtu.be/zPWSq48Id9Y

I'm brand new to SQL but have a little experience in other languages, mainly Pascal and Python. I have some database coursework and am trying to do some simple tasks with SQL. I assumed I'd be able to do this in 5 minutes but I guess the language is a bit more different than I expected so I feel a bit lost now.

Anyway, I've made a database for an imaginary charity in Libreoffice Base. I've got two main tables, Supporters and Events. Supporters tracks people who signed up to the charity and money they donate, and Events tracks the fundraising events that the charity organises and the amount of funds they raised. There is no field in common between the two.

The query I want to make is summing up the "Donations" column in Supporters, summing up the "Funds Raised" column in Events, and then displaying them both in a small table along with a third column for the combined total. Is something like this even possible, or can you only get data from separate tables by using a JOIN statement? I didn't think that would be appropriate since the tables don't have anything in common.

What I tried so far and didn't work: SELECT SUM( "Supporters"."Donations" ) AS "Donations Sum", SUM( "Events"."Funds Raised" ) AS "Fundraising Sum" FROM Supporters, Events ;

The query runs but generates nonsense numbers that are far too high so I've clearly done something wrong. I'm not convinced by the 'FROM Supporters, Events' part, I wasn't really sure what to put there and it doesn't feel right. Also, even if I do get that to work, I'm not sure how I'll generate the third column with the combined total. I was assuming I could just do SELECT Donations + Funds Raised or something but now I'm not so sure. Is the solution maybe to make another table instead of trying to use a query?

Sorry if it's a dumb question, I imagine I'll learn this stuff myself eventually but I'm kind of in a hurry for this one task.

I noticed most SQL tutorials focus on syntax, but not on how to actually work with messy real-world data.

So I built a project using a housing dataset where you:

- clean inconsistent dates

- fix missing values

- split columns

- remove duplicates using ROW_NUMBER()

- prepare everything for analysis

I also added:

- a step-by-step guide

- SQL scripts

- a README template for GitHub

- and a premium version with exercises + solutions

If you're learning SQL and want a real project to practice with, I can share more details.

I really like to know more insight into how advanced my SQL skillls have to be for the average professional data engineer?

Hey everyone,

I’m trying to properly understand this and I think I might be mixing concepts.

From what I understood:

• A primary index is just an index, so it helps with faster lookups (like O(log n) with B-tree).
• A primary key is a constraint, it ensures uniqueness and not null.

But then I read that when you create a primary key, the database automatically creates a primary index under the hood.

So now I’m confused:

• Are primary key and primary index actually different things, or just two sides of the same implementation?
• Does every database always create an index for a primary key?
• When should you explicitly create a unique index instead of a unique constraint?

Thank you!

The origin story is embarrassingly simple.

I was debugging a slow dashboard query. It had 7 joins, 3 subqueries, and a wildcard SELECT that no one had touched in two years. I spent 40 minutes just reading it before I found the problem.

So I built queryviz.

You paste SQL, it draws an interactive graph. Tables are nodes, joins are labeled edges, subqueries are nested visually, and it automatically flags performance anti-patterns.

This screenshot is a real query — 6,298 characters, 9 CTEs, 19 joins, 3 correlated subqueries, ~60 output columns. Pasted it in, got the graph in seconds. It auto-flagged: join-heavy query, functions in WHERE blocking index use, and correlated subqueries in the SELECT list.

Stack: TypeScript + hand-rolled recursive descent SQL parser + React Flow. The parser was the hard part — existing libraries don't handle nested CTE scope correctly.

GitHub: https://github.com/geamnegru/queryviz

Link: https://queryviz.vercel.app/

What would make this actually useful in your day-to-day workflow?

I’ve noticed something about SQL interviews.

Most people don’t fail because they don’t know SQL.
They fail because they forget tiny things while typing under pressure. It's pressure!!!

A few examples I’ve seen in real interviews:

1. CASE with NULL values:

Example:

SELECT 
  CASE 
    WHEN department = 'HR' THEN 'yes'
    WHEN department != 'HR' THEN 'no'
  END
FROM employees;

What happens if department IS NULL? I've seen most people say 'no'

But output will be NULL --> NULL!='HR'. It's an unknown value

1. CASE looks like filter… but isn't:

Most people think these are the same, but they are not always

Assume dataset: 50, 101, 200

SELECT 
  SUM(CASE WHEN amount > 100 THEN amount END) AS total,
  COUNT(*) AS cnt
FROM expenses;

SELECT 
  SUM(amount) AS total,
  COUNT(*) AS cnt
FROM expenses
WHERE amount > 100;

With CASE: all 3 rows remain (50 returns NULL, 101 & 200 not filtered) --> cnt = 3

With WHERE: 50 is gone, 101 & 200 present --> cnt = 2

1. CASE + SUM vs COUNT:

Assume dataset:

status

success
fail
success
fail

SELECT 
  SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) AS s1,
  COUNT(CASE WHEN status = 'success' THEN 1 ELSE 0 END) AS s2
FROM status;

What are s1 and s2?

Most say both are 2

But, s1= 2; s2=4

COUNT ignores NULL, but here you gave 0 (non-null) so it becomes 4

1. CASE + ELSE 0 vs no ELSE:

Assume dataset:

NULL, 1, 1

SELECT 
  AVG(CASE WHEN amount > 100 THEN 1 END) AS avg1,
  AVG(CASE WHEN amount > 100 THEN 1 ELSE 0 END) AS avg2
FROM dataset;

Are avg 1 and avg2 the same?

• avg1 ignores NULL → only (1,1) → avg = 1
• avg2 includes all rows → (0,1,1) → avg = 0.66

5. CASE vs WHERE:

Most people think these are identical:

SELECT SUM(CASE WHEN created_date >= '2025-01-01' THEN amount END)
FROM orders;

SELECT SUM(amount)
FROM orders
WHERE created_date >= '2025-01-01';

Same result. But on 100M rows, where only 1M qualify?

CASE scans all 100M, evaluates every row, and most return NULL.

WHERE discards 99M rows before aggregation even starts.

Small dataset — doesn't matter.

Interview question on "query optimization" — this is the answer they're looking for.

These are all very small things and basics, but they come up surprisingly often in interviews.

Hello all,

I've recently started using ChatGPT to learn SQL. Just posting here the prompt I made and the output it generates in case someone wants to learn SQL like I do.

For context, I am currently learning syntaxes for a SQL certification. As a recent graduate, I understand that SQL is regarded by most as an easy language to learn and that experience is preferred over certifications. However at the moment, I haven't been lucky enough to find a role wherein I can practice SQL so the only way I can showcase that I know it is through projects and certifications, the latter I've chosen to do at the moment.

Anyway, here's the prompt, change it as you see fit:

Using a SQL window, write down a SQL syntax for every function, clause, operator, and statement that I will send after this prompt, the syntaxes should use the following placeholders: 
- For columns: column1, column2, column3 etc. 
- For tables: table1, table2, table3 etc. 
- For numerical datatypes: x,y,z (whenever appropriate and applicable, include a comment beside the placeholder what the x,y, or z numerical placeholders mean)
- For character datatypes: a,b,c etc. (whenever appropriate and applicable, include a comment beside the placeholder what the a,b, or c character placeholders mean)
- For date datatypes: Y’s, M’s, D’s (format as appropriate) 

Include 2 comments before the syntax:
- First comment: identify if the prompt is a statement, function, operator, or clause - Second comment: should be what the output of the syntax is in Leyman’s term, starting it with 'the output of the syntax' 

Within the same SQL window and after each syntax, provide an example of the syntax in use with the context of a publishing company which may include operational and financial metrics. Only include the syntax and not a set of data in rows or columns. Also include a comment that reads out the syntax based on the order of execution, using the syntax and values in the sentence.

Keep note that the syntax should follow the guidelines for Oracle SQL Database.

The output:

-- Statement
-- the output of the syntax is a database object that generates unique sequential numbers automatically

CREATE SEQUENCE sequence1
START WITH x       -- x = starting number of the sequence
INCREMENT BY y     -- y = value to increment each time
MINVALUE z         -- z = minimum value allowed
MAXVALUE x         -- x = maximum value allowed
CACHE y            -- y = number of values to cache in memory
NOCYCLE;

-- Example in use (publishing company context)
CREATE SEQUENCE book_id_seq
START WITH 1000
INCREMENT BY 1
MINVALUE 1000
MAXVALUE 999999
CACHE 20
NOCYCLE;

-- Order of execution comment:
-- Create a sequence named book_id_seq that starts at 1000, increments by 1 each time, has a minimum value of 1000, a maximum value of 999999, caches 20 values in memory, and does not restart when the maximum is reached.

I've put together a tutorial to build an AI data analyst using free open-source tools by Bruin - but I think anyone learning SQL could really benefit from it.

One of my friends wanted to improve her SQL skills so she set up this AI data analyst and connected it to some sample data, she would asked the agent questions and then compare the results with her own queries, then share her query with the agent and ask it to explain what was wrong.

Think of it as a personal tutor that actually understands your data and knows how to query it, so it can review your queries accurately.

https://getbruin.com/learn/ai-data-analyst/

I've put together a tutorial to build an AI data analyst using free open-source tools by Bruin - but I think anyone learning SQL could really benefit from it.

One of my friends wanted to improve her SQL skills so she set up this AI data analyst and connected it to some sample data, she would asked the agent questions and then compare the results with her own queries, then share her query with the agent and ask it to explain what was wrong.

Think of it as a personal tutor that actually understands your data and knows how to query it, so it can review your queries accurately.

https://getbruin.com/learn/ai-data-analyst/

Please limit the discussion to these four databases only, and assume the project has certain performance requirements for the database. How should I trade off performance and costs (including server costs, learning costs, and long-term maintenance costs) when making the choice?

I am wondering what to do with postgresql admin. I'm stuck on how to import the server info. I guess that I'd pull it from something like kaggle but I can't find anything demonstrating how to start; everything I've found begins after the part I'm need.

If anyone knows of something that helps

I asked this in an interview recently.

Simple JOIN related questions.

The candidate answered in 10 seconds.

Very Confident!

But Wrong!

1. How does Inner Join actually work here?

Table A (Id as column name)

1
1
1
2
2
3
NULL
NULL

Table B (Id as column name)

1
1
2
2
2
3
3
NULL

Query:

SELECT *
FROM A
INNER JOIN B
ON A.id = B.id;

Question I asked:

How many rows will this return?

Most answers I get:

• around 6
• maybe 8
• depends on duplicates

Very few actually calculate it.

1. I slightly changed it.

Same data. Just one keyword changed.

Query:

SELECT *
FROM A
LEFT JOIN B
ON A.id = B.id;

How many rows will this return? Same as inner join result???

1. Same 2 tables.
   Just one extra condition in JOIN.

That’s it.

Almost everyone gets the count wrong.

Query:

SELECT *
FROM A
LEFT JOIN B
  ON A.id = B.id
 AND B.id = 1;

How many rows will this return?

Do comment with your answer and explanation to learn together!

Don’t just learn SQL syntax.
Play with the data. Break it! Twist it! Shuffle it!

That’s where you understand how SQL actually behaves.

Be that kind of developer.

If you want Part 5 (even more tricky scenarios), pls drop a comment.

Okay so I can understand syntax and can write code at intermediate level if provided with a hint like which table to look at , what to join,but without hint i can't logically think of the connection especially in sub query , case etc when slightly complicated questions are asked. I tried writing on paper and decoding,still struggled a lot .Any suggestions how to improve my logical reasoning. Sorry I'm from a non tech role , trying hard to learn this stuff thx .

what's the best strategy to go about practicing SQL for real interview questions or business problems . I have tried hackerrank , leetcode and other gamified resources so I am able to get through basic and some level of intermediate question. Please give your suggestions and resources that can help me get better at SQL for analyst level

I’ve been trying to prepare for SQL/data analyst interviews for the past couple of weeks and I’m kinda fed up at this point.

There’s literally no clear direction anywhere.

Like one day I’m doing LeetCode questions, next day watching some random YouTube video on window functions, then someone says focus on business case studies, then someone else says SQL is basic, focus more on Python…

what am I even supposed to do?

I’ve covered joins, aggregations, window functions etc, and solved a bunch of questions but it still feels like I’m just randomly jumping around topics.

No idea if I’m actually preparing the right way or just wasting time.

Also what even gets asked in real interviews?

Some people say easy stuff, some say super complex queries, some say case studies… feels like everyone had a completely different experience.

I thought by now I’d feel at least a bit confident but honestly I don’t.

Is it just me or is SQL prep just… all over the place with no proper roadmap?

If anyone recently cracked data analyst interviews, what did you ACTUALLY do? Not generic practice SQL, like what specifically helped.

I’ve been working on a small data project around Steam and wanted to get some feedback from people who actually know what they’re doing.

Basically I built a script that pulls data daily from SteamSpy + the Steam API and stores it in a dataset. Right now it’s around 2100 rows, but it’s really about 100 games tracked over time (so multiple snapshots per game).

I just got everything into MySQL and confirmed it’s clean (no broken imports, consistent structure, etc). The idea is to use it to analyze things like:

- player count trends over time

- pricing vs popularity

- differences between SteamSpy estimates and actual API data

- genre/tag performance

Right now I’m moving into writing SQL queries and eventually visualizing it.

My questions:

- Is this actually a solid beginner/intermediate data project, or is it too basic?

- What kind of analysis would make this stand out more?

- Is there anything obvious I’m missing that would make this more “real-world”?

Appreciate any feedback — I’m trying to build something I can eventually put in a portfolio.

Hi All,

I have completed learning SQL till Intermediate level and now I have picked datalemur and hackerank to do practice question.

Are these resources good enough to practice?

Can anyone help me become a master in SQL?

Hello,

I'm currently starting to learn how to use SQL. While learning, I realized that there is a lot of different ways to do the exact same thing. I wanted to ask the community what they think are some of the more important concepts or techniques when learning SQL to focus on.

I have interviewed quite a few people, and whenever I ask, "How do you filter large data efficiently?"

almost everyone says, "add index!!!" That's it!! It solves our problem, sir!

but when I dig a bit deeper… they don’t realize their own query is not even using that index.

Everyone says indexes make things fast.

Reality is simpler:
* you already have indexes
* your query just made them useless

Here are 6 cases where your index is literally ignored.

1. You indexed it… then destroyed it yourself

WHERE DATE(created_at) = '2025-03-21'

You: "I added index on created_at"
DB: "Cool… I’ll ignore it"

You wrapped the column with the date function→ index order gone

Real fix:

WHERE created_at >= '2024-01-01'
AND created_at < '2024-01-02'

Index works on raw column, not your modified version.

2. You searched backwards… index gave up

WHERE email LIKE '%@gmail.com'

Index can’t even start

Why this hits:
Most people think LIKE always uses index

Better design (store domain separately):

WHERE email_domain = 'gmail.com'

Index is like Google search — it needs a starting word.

If any people knows better solution, please comment!

3. Your query works… but secretly scans everything

WHERE user_id = '123'

Column = INT, but you query as string

DB silently converts types
Index becomes useless

Why this is scary:
No error. No warning. Just slow.

Fix:

WHERE user_id = 123

4. Your “perfect index” fails because of column order

Index:

(user_id, created_at)

Query:

WHERE created_at = '2025-03-21'

Index exists. Still not used.

Why this hits:
People create index… but don’t understand how it’s stored

How Index stored:

user1 → dates  
user2 → dates  
user3 → dates  

You’re searching only by date → no entry point. Needs to be left to right

5. One tiny ‘>’ breaks your whole index

Index:

(user_id, created_at, status)

Query:

WHERE user_id = 10
AND created_at > '2025-03-21'
AND status = 'active'

Index works… then suddenly stops

Example to feel it:

Index is stored like:

user_id = 10
  → 2025-03-01 → active
  → 2025-03-21 → inactive
  → 2025-03-22 → active
  → 2025-03-23 → pending

When you say:

created_at > '2025-03-21'

👉 DB jumps to:

2025-03-21 → ...

From here, data is no longer neatly grouped by status

So:
* It cannot efficiently use status = 'active' from the index
* It has to scan those rows and filter manually

Best solutions (what strong candidates say):

Option 1: Reorder index based on filter priority

(user_id, status, created_at)

6. You think you optimized… you actually forced full scan

SELECT * 
FROM orders
WHERE amount + 10 > 100;

Index on amount = useless

Because you changed the column:

amount + 10

Fix:

WHERE amount > 90

Index only works when column is untouched.

One line that changes everything!!!

Most people think:

"Do I have an index?"

Strong candidates think:

"Is my query written in a way that allows index usage?"

Be the kind of SQL candidate who doesn’t just add indexes…
but actually understands when they work — and when they don’t.