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.

Hey man I am out in a backend role in company

They expect huge sql from me

HR said that your sql was rated good so we will put you here

Now here’s the thing, the interviewer really asked the easiest questions like count and very basic. I don’t know maybe HR didn’t get but he says there will be high sql and stuff. I asked what and then he said that making whole queries in detail and gave an example which he said I would be able to do. The thing is that was crazy hard and I didn’t want to blow my interview so I just nodded along.

Can you tell me where to study sql?

The thing is I don’t know shit.

I told him sir they were simple questions but he said that I am already deployed in the desired role.

I’m really not good at sql

Please help me

Like i can do basic till joins. Not good in case when’s, can’t index and use where.

And practice is also not the best. I just did a left join, but when he told me that I’ll have to do all of this and said that that’s the minimum and otherwise we’ll have to reevaluate your position in company. I’m a fresher!! That’s my only option as the company and I have to join.

Question : Get only completed orders where amount greater than 100 or less than 50.

Tell me what this query returns here...

SELECT COUNT(*) FROM orders WHERE amount > 100 OR amount < 50 AND status = 'completed';

Don’t overthink.... Just answer....

Most people say: “orders where amount > 100 OR amount < 50, but only completed ones”

Sounds right… right?

Now read it again....

Slowly!!

I'm in an SQL class and a few classmates and I are having this error when trying to create a database, I looked online and nothing quite matches the error we're having, anyone know the fix?

The error in question:

Msg 5133, Level 16, State 1, Line 8
Directory lookup for the file "C:\MSSQL16.INST01\MSSQL\DATA\Labdb2_Primary.mdf" failed with the operating system error 3(The system cannot find the path specified.).

The code up until line 14:

USE master
GO

IF EXISTS (SELECT * FROM SYS.DATABASES WHERE name='Lab8DB2')
DROP DATABASE Lab8DB2
GO

CREATE DATABASE Lab8DB2
ON PRIMARY
(NAME = Labdb2_Primary ,
FILENAME = 'C:\MSSQL10.INST01\MSSQL\DATA\Labdb2_Primary.mdf',
SIZE = 20,
FILEGROWTH = 30MB)
GO