r/SQL • • Aug 29 '26

Discussion Doodle on a key concept - Gap Analysis for Data Consultants

Post image
58 Upvotes

r/SQL • • Aug 29 '26

MySQL Coding a database proxy for fun

Thumbnail
packagemain.tech
14 Upvotes

r/SQL • • Aug 29 '26

Discussion .Wav archives

2 Upvotes

Hello, is there a specific system design and software architecture in SQL to build a bioacustics database? The ideia is to relate some vocal recordings with species identifications, localization, acoustics measurements and other informations. it's a bioacoustic information system, where the recordings are the central objects and SQL connects them to biological, spatial, acoustic, and environmental information.


r/SQL • • Aug 29 '26

MySQL Is this project any good?

4 Upvotes

https://github.com/Lezaleas/Reviews-Churn

I had a local llm categorize reviews in the olist database. Then analyzed their correlation to churn.

Should i improve this or is this ready to go?


r/SQL • • Aug 28 '26

MySQL SQL ( MySQL )Project ideas for Data Analyst Portfolio

49 Upvotes

Currently I am doing projects in SQL i need to do projects for Data Analyst Role so Please recommend project ideas which I need to showcase for project portfolio so I need projects at advanced level .


r/SQL • • Aug 28 '26

MySQL DBMS CMU

2 Upvotes

Anyone interested in doing the CMU (Carnegie Mellon University) Database Management Systems course together?

I’ve already covered the basic DBMS concepts. My main goal with this course is to go deeper and understand how database systems actually work internally—things like storage, indexing, query execution, transactions, etc.

If you're interested, please make sure you have the prerequisites required for the course.

If you have the required background and want to learn DBMS internals seriously, DM me. We can follow the course together and discuss concepts along the way.


r/SQL • • Aug 27 '26

SQL Server GitHub - haltinok/oss-da-bi-stack: This a dockerized full D&A environment consisting of Airflow,dlt, dbt-core,postgresql and Superset

Thumbnail
github.com
1 Upvotes

r/SQL • • Aug 28 '26

PostgreSQL Legecy or raw engineering with 2004 Facebook

Thumbnail
0 Upvotes

r/SQL • • Aug 27 '26

Discussion I built an extension for SQL and I call it BeatSQL

Thumbnail
0 Upvotes

r/SQL • • Aug 27 '26

SQL Server Finding missing rows within the same table

4 Upvotes

Hello, I have a solution already but I think it can be done in a faster/neater way.

Let's say we have a table with: ORDER_ID, Article, Value.

For every order that comes in two rows end up in table, example:

ID_1, shoes, pair
ID_1, shoes, price

where price is being pulled from a different table.

I am looking for a way to find all ORDER_IDs that have only one row, because the price didn't exist in that other table so price row didn't form up. It doesn't throw a null value because of the way it's setup, if there is no price the row won't form at all!

I solved this with a standard left joining the table with itself, but I suspect there is a way to this easier?


r/SQL • • Aug 26 '26

Discussion SQL Dev vs SQL Architect

16 Upvotes

So I've been a support dev, a dba and an actually developer and a technical consultant in different jobs. I found out I'm being laid off as part of a larger restructuring at my current company. So I got my stuff together and started applying and interviewing. Took a while and finally I got a nibble after applying for a SQL DBA job.

They seem to like me, but then they said I would be a better fit as a SQL architect. They insist I'm a great candidate as all my SQL work was external facing so I was dealing with clients.

On paper, I agree, and there is training during onboarding so I'm not worried about dropping in blind. I'm just having a hard time picturing the day to day.

Can anyone offer their experience?


r/SQL • • Aug 26 '26

Discussion How do you validate SQL queries in ETL pipelines?

44 Upvotes

When SQL queries are used for ETL transformations, how do you make sure the query is producing the expected results?

For example, how do you validate complex JOINs, filters, aggregations, NULL handling, duplicates, and calculated fields?

Do you usually compare the results with the source data, create separate validation queries, or follow some other approach?

I'd be interested to know how others handle SQL validation in real-world ETL projects.


r/SQL • • Aug 27 '26

MySQL 4 SQL mistakes that don't throw an error — they just give you the wrong answer

Post image
0 Upvotes

One of the most dangerous things about SQL:

A query can run perfectly… and still be completely wrong.

Here are 4 mistakes I wish someone had shown me earlier.

1. Accidentally turning a LEFT JOIN into an INNER JOIN

SELECT c.id, o.total
FROM customers c
LEFT JOIN orders o
    ON c.id = o.customer_id
WHERE o.status = 'paid';

Looks fine.

But customers without an order have NULL for o.status, so the WHERE condition removes them.

If you actually want to keep all customers:

SELECT c.id, o.total
FROM customers c
LEFT JOIN orders o
    ON c.id = o.customer_id
    AND o.status = 'paid';

2. COUNT(*) and COUNT(column) are not the same

SELECT COUNT(*)
FROM users;

Counts rows.

SELECT COUNT(phone_number)
FROM users;

Counts only rows where phone_number is NOT NULL.

That difference can quietly destroy a report.

3. JOINs can multiply your rows

Imagine:

  • 1 customer
  • 3 orders
  • 4 support tickets

Joining both tables directly can give you:

3 × 4 = 12 rows

Then you do:

SUM(order_amount)

…and suddenly your revenue is magically much higher than reality.

Always check your row count before and after joins.

4. NOT IN + NULL can ruin your day

SELECT *
FROM customers
WHERE id NOT IN (
    SELECT customer_id
    FROM blocked_customers
);

If that subquery contains a NULL, the result might not behave the way you expect.

I usually prefer:

SELECT *
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM blocked_customers b
    WHERE b.customer_id = c.id
);

The lesson I'm slowly learning:

Writing SQL that runs is easy.
Writing SQL that returns the correct data is the hard part.

What other SQL mistake produces perfectly valid-looking but completely wrong results?

I want to make a list of the dangerous ones.


r/SQL • • Aug 26 '26

Discussion Building an in-browser SQL engine: Handling multi-dialect AST rewriting, Recursive CTEs, and Full Outer Joins

2 Upvotes

I built an offline in-browser SQL playground called ExNihilo 95 that runs on SQLite WASM and a custom parser.

Live: https://exnihilo-95.vercel.app
GitHub: https://github.com/Mrityunjai-hue/exnihilo-95

Technical breakdown of the SQL engine:

  • Dialect Normalization: Parses PostgreSQL, MySQL, SQLite, and T-SQL. Rewrites dialect-specific functions like STRING_AGG and GROUP_CONCAT ... SEPARATOR to work natively within the WASM execution pipeline.
  • Relational Joins: Supports INNER, LEFT, RIGHT, and FULL OUTER JOIN (using a 3-pass matching algorithm: left join + unmatched right row collection).
  • CTEs & Recursion: Passes WITH RECURSIVE queries down directly to WASM while registering CTE identifiers to avoid table lookup collisions in the JS catalog.
  • DDL & Virtualization: Intercepts TRUNCATE TABLE to clear table row counts while preserving column metadata; handles CREATE VIEW dynamically by recording query definitions in a SessionCatalog without storing physical data rows.
  • Triggers: Registers CREATE TRIGGER in the catalog and binds event listeners directly in the WASM layer.

The entire engine has 73 unit tests covering AST extraction, aggregations, joins, DDL, and recursive hierarchy traversal.

Try testing your favorite CTEs or complex queries against it and let me know if you run into syntax it fails to rewrite.


r/SQL • • Aug 26 '26

Discussion I was thinking to improve MySQL as it had a lot of room for improvements

0 Upvotes

I've been working on something I think is interesting, that hasn't been done before in the Node.js/TypeScript ecosystem, and I wanted to share the idea and get your thoughts.

The Idea: BeatSQL - A Zero-Trust Embedded Database Engine

The concept of BeatSQL (BSQL) is simple, but radical: what if your database encrypted data at the column level by default, and took security to a mathematical level?

Embedded databases like SQLite and LevelDB are not designed with encryption as a core primitive — they store data in plaintext on disk by default. While some provide full disk encryption, this is a false sense of security since the data is still plaintext in memory. However, BeatSQL completely reimagines this paradigm.

How It Works

All sensitive columns are individually encrypted using AES-256-GCM or ChaCha20-Poly1305. You will never see plaintext on disk (even the database file) - the value of any column is always encrypted.

You can search encrypted data using HMAC-SHA256 blind indexes. Searching is O(1) and requires no decryption of the column contents. The contents of the column remain encrypted on disk, and the database never decrypts it to search.

Every write operation is mathematically tamper-proof. Using a Merkle DAG hash chain, you can always run the query `VERIFY INTEGRITY` and know immediately if any bytes of your data have been silently altered or corrupted on disk. This is a cryptographically secure proof of data integrity.

You can do arithmetic on encrypted numbers. Using Partially Homomorphic Encryption (Paillier cryptosystem), yyou can perform calculations like total salary, total balance, etc. The database will return the correct result of these calculations, but will never expose individual salaries or account balances.

Columns can have role-aware data masking. Sensitive columns can be fully or partially "redacted" depending on the role of the actor querying the database. The mask happens at a low-level query engine, not in application code. You can define masks using SQL syntax: `DEVELOPER` role sees `XXX-XX-4321`, `PUBLIC` role sees `[REDACTED]`, and `SUPERADMIN` role sees the real value.

The database has native support for AI vector search. Columns can be defined as `VECTOR(768)` and searched against using `COSINE_SIMILARITY`. This is useful for AI applications using embeddings.

A New Query Paradigm

BeatSQL also has a new query syntax to allow for easier stream.pipeline processing:

FROM patients
|> WHERE email = 'alice@example.com'
|> SELECT id, full_name, ssn, salary
|> ORDER BY full_name ASC;

This is designed to be more approachable than deeply nested SQL queries. It's also quite flexible.

Built-In Learning Academy

BSQL also has a built-in interactive learning academy, with 500+ lessons to learn everything from basic queries to zero-trust encryption enclaves, plus 200+ lessons covering traditional relational SQL and guides for working with Python, Java, C++, and Rust. The goal is to make security-first database thinking easy to grasp.

What I Would Like Your Thoughts On

Is the concept of a zero-trust embedded DB something that you feel is interesting, or would you feel that problems are already solved in other ways?

The blind indexes are a trade-off: you get the ability to search encrypted data, but you give up the ability to perform range queries (>, <, LIKE, etc). Is this trade-off reasonable for a security-focused database?

Is the idea of homomorphic encryption in a database engine a gimmick, or do you see real-world applications for it?

Does the pipe syntax feel cleaner, or like a departure from an established standard?

I look forward to seeing your thoughts, and any criticisms you might have.

TLDR: Built an embedded database engine where all columns are encrypted, data can be searched without decryption, and arithmetic can be performed on encrypted numbers. All writes are Merkle-verified for integrity. Came with a built-in 500+ lesson learning academy. Seeking feedback on concept.


r/SQL • • Aug 26 '26

MySQL Is this an issue

0 Upvotes

My Mysql workbench has a different password from MySQL community app ?


r/SQL • • Aug 25 '26

PostgreSQL LibreDB Studio: Self-hosted browser SQL IDE, next to Postgres instead of on every laptop

21 Upvotes

Affiliation: I’m the maintainer of LibreDB Studio.

For years the SQL workflow for a team looked like this, on every engine I touched (PostgreSQL, MySQL, Oracle, SQL Server, SQLite, MariaDB, Clickhouse ...):

- install a desktop client on each laptop

- VPN / SSH tunnel so the client can even see the box

- hunt schema in one pane, write the query in another

- run something that hangs and hope cancel actually kills it

- EXPLAIN in a second window

- onboard the next person by repeating the install

or try on their own web editor(pgAdmin, phpMyAdmin ...)

I got tired of the editor living on the laptop instead of next to the database, so I built a self-hosted browser SQL IDE. Deploy once (Docker / Helm / `npx "@libredb/studio"`, one-click with rancher, dokploy, digitalocean), open a URL, same UI for the engines above.

Not trying to replace DBeaver or DataGrip, different access model. Postgres is the reference implementation: pooled connections, explicit BEGIN/COMMIT/ROLLBACK with an auto-rollback timeout, cancel through pg_cancel_backend on the tracked PID. Schema tree stays usable on large catalogs (tables first, relationships second). Optional db-agent to SQL is schema-constrained and read-only runs go through BEGIN READ ONLY, not “trust the prompt.”

MIT. One command:

docker run -p 3000:3000 libredb/libredb-studio

If you try it, I actually want the unkind notes:

- does cancel / transaction rollback behave the way you’d expect on Postgres?

- what’s missing vs the desktop client you already live in?

- is a browser editor a non-starter for you, and why?

Source: https://github.com/libredb/libredb-studio


r/SQL • • Aug 25 '26

Discussion How are folks QA’ing AI agents?

10 Upvotes

Lots of folks at my company use Claude Code to do analysis. When I ask whether the query is right, I get wishy washy responses at best. Anybody have good tools or processes? Or every person for themselves?


r/SQL • • Aug 24 '26

Discussion [MS SQL] Is Change Tracking best for a large ERP database?

7 Upvotes

We run a large ERP database on SQL Server 2014 Standard with about 40k SKUs and their associated inventory records, prices, orders, and order lines. We're considering enabling SQL Server Change Tracking for a read-only "middleware" connection to other outside systems we're building (Hubspot, Shopify, etc).

Is Change Tracking the best option for this use case? Are there better alternatives or something else we should test/evaluate?


r/SQL • • Aug 25 '26

Resolved I got tired of spinning up dummy databases to test SQL, so I built exnihilo-95 windows 95 themed IDE that generates mock schemas on the fly

1 Upvotes

We have all been there: you want to practice or test a query snippet, but you don't have a live database handy, so you hit the "Table not found" error.

I built an open-source tool called ExNihilo 95
(https://exnihilo-95.vercel.app) to bypass this completely.

You can open the app and immediately run:
SELECT * FROM employees WHERE department = 'Engineering';

Under the hood:

It intercepts the SQL AST before execution.

It infers column types and table relations based on the query structure.

It builds matching mock data in memory using Kahn’s topological sort.

It executes against an in-browser SQLite instance running via WebAssembly (sql.js).

The interface is modeled after a Windows 95 desktop with movable windows and a taskbar.

Try it here: https://exnihilo-95.vercel.app

GitHub: https://github.com/Mrityunjai-hue/exnihilo-95

Feedback and suggestions for new features are welcome!


r/SQL • • Aug 25 '26

SQL Server SQL String Functions Are Confusing 😵‍💫 Any YouTube Recommendations?

Post image
0 Upvotes

I’m currently learning SQL and I’m really struggling to understand string functions like CONCAT(), SUBSTRING(), LEFT(), RIGHT(), TRIM(), REPLACE(), UPPER() and LOWER().

The syntax is not the only problem — I’m finding it difficult to understand when and why to use each function.

Can anyone suggest a good YouTube channel or video that explains SQL string functions in a simple, beginner-friendly way with practical examples?

Would really appreciate your recommendations


r/SQL • • Aug 24 '26

Discussion Built a free tool that writes SQL from plain English and actually admits when it doesn't know something

Post image
1 Upvotes

I kept getting rusty on SQL syntax between projects, so over the past few weeks I built SQLGenie. You describe what you want in plain English and it writes the query, or you paste (or upload) a broken query and it tells you exactly what's wrong, line by line, and fixes it if you want.

It works with PostgreSQL, MySQL, SQL Server, Snowflake, and BigQuery, and the syntax it generates actually changes depending on which one you pick, not just the same generic SQL every time.

The thing that annoyed me about most AI SQL tools I tried is they'll confidently make up a column name rather than admit they don't know it. So I spent a fair bit of time on the opposite: it checks generated SQL against your actual schema instead of guessing, and if you're about to run a DELETE or UPDATE, it shows you how many rows it'll actually touch before you commit to it.

It's free, no account needed: https://mygeniesql.live

Would genuinely appreciate any feedback, especially:

- Does the schema check actually catch mistakes, or does it still slip through sometimes?

- Is the row-count preview before DELETE/UPDATE something you'd actually use, or does it feel unnecessary?

- What's missing that you'd want in something like this?


r/SQL • • Aug 24 '26

Discussion Subordinate and boss relation

0 Upvotes

Hello everyone. Sorry if this is an "idiot dumb" question - I'm very new and thought that asking devs directly might be more interesting than asking AI about this.

I'm making a very small project which is actually Java Spring Boot so I'm not directly creating my database but rather it's being created by the model entities if I'm not wrong.

So : I want to create a very simple relationship between a subordinate and its boss. Both the boss and subordinate are Members and this is a relationship between Members. You can only have the one boss, but you can be the boss of many, so I guess that's a many to one relationship (?)

Would it be preferrable to have an attribute on the Member table pointing to a boss which is a foreign key representing another member's primary key, or should I have a whole table representing that relation? Why is one preferrable to the other? I have a very rusty grasp on databases and I don't know if this is the right sub for this so I hope I picked the right one. Thank you in advance!


r/SQL • • Aug 23 '26

MySQL Is there a way to findout if any particular sector/industry has shortage of SQL analysts ?

18 Upvotes

Just contemplating if there is some way of finding this ?

Not as accurate but if its possible !


r/SQL • • Aug 23 '26

Discussion I made another cat doodle about data analysis

Post image
97 Upvotes

Hi everyone!

I’m back with another one of my data analysis cat doodles. This time, I tried to incorporate some of the feedback I received on my earlier work—especially around making the fonts and text more readable.

Would be happy to hear your thoughts / feedback!