标签：#SQL

Windows function VS Group by Both window functions and GROUP BY help you summarize data. But they do it in different ways, and mixing them up leads to confusing results. GROUP BY squishes many rows into one row per group. -A window function keeps every row , and just adds an extra column next to it. Once you see that difference, it's easy to know which one to reach for. We'll use one simple table the whole way through, so the examples stay easy to follow: students --------------------------- name | class | score --------------------------- Amina | A | 90 Brian | A | 70 Carla | A | 85 Dennis | B | 60 Efrem | B | 95 Difference between Windows Functions and Group by GROUP BY answers a question like: "What's the average score in each class?" It gives you back fewer rows than you started with — one row per class. A window function answers a question like: "How does this student's score compare to their class average?" It gives you back the same number of rows you started with — one per student — just with something extra calculated for each one. So: Want one summary row per group? Use GROUP BY . Want to keep every row, but add a calculation? Use a window function. Example 1: GROUP BY — one row per class -- One row per class. We lose the individual students. SELECT class , AVG ( score ) AS average_score FROM students GROUP BY class ; Result: class | average_score ------------------------ A | 81.6 B | 77.5 Notice we no longer see Amina, Brian, or any individual name. GROUP BY traded the detail for a summary. That's fine when the summary is all you need. Example 2: A window function — keep every row Now say you want to see each student's score next to their class average, without losing any rows: -- Every student stays, plus a new column showing their class average. SELECT name , class , score , AVG ( score ) OVER ( PARTITION BY class ) AS class_average FROM students ; Result: name | class | score | class_average ------------------------------------------ Amina | A | 90 | 8

I build text-to-SQL agents on Oracle and Postgres for a living. Every one of them had the same bug, and it wasn’t in my code. It was in the order of operations. The bug The schema goes into the prompt before the query runs. Row-level security runs when the query runs. So the model sees a table the user can’t read, writes perfectly valid SQL against it, the database returns zero rows, and the agent says “no records found”. A wrong answer, delivered with confidence. Vanna (23k stars, archived March 2026) applied identity exactly there: at execution, after the model had seen everything. The fix Apply identity at selection. Decide which tables the model is shown, per caller, before any SQL exists. A restricted table isn’t ranked low — it’s absent. from schemagate import Catalog, Principal cat = Catalog().bootstrap("postgresql://localhost/app") cat.restrict("hr_compensation", roles=["payroll"]) analyst = Principal("okta:jdoe", roles={"analyst"}) cat.select("salary by employee", principal=analyst).table_names # no hr_compensation pip install schemagate — one dependency, no API key, any SQLAlchemy database. The side effect that pays for it You’re now sending ~6 tables instead of the schema dump. Measured on the test schemas: 65–79% fewer prompt tokens on small ones, 97% on a 260-object one (16,095 → 444 per question). The selector never calls a model — BM25 plus a hashed embedder, offline, milliseconds. What broke while building it Six invented schemas found ten bugs before release. My favourite: a three-column orders_bkp outranked the real orders table, because short documents win cosine similarity. Backup and staging copies now rank below the object they shadow. The full list is in TESTING.md. Where it plugs in MCP server for Claude Desktop and Cursor, a LangChain retriever, a native Oracle 23ai VECTOR store, and a browser demo that needs no install: https://ashishsinha1602.github.io/schemagate/ Repo: https://github.com/ashishsinha1602/schemagate — tell me where it break

COUNT(*) looks like a trivial operation: SELECT COUNT ( * ) FROM orders ; The query asks for a single number, but that doesn't mean PostgreSQL can produce it with a constant-time read from some internal counter. When we need an exact count, PostgreSQL has to determine how many rows are actually part of the visible result set for that query. On large tables, that work can become a meaningful chunk of total execution time. And the problem doesn't just go away by throwing an index at it. The useful question isn't "do I have an index?" It's: How many rows does PostgreSQL actually need to examine to compute this count — and can that work be reduced? Why COUNT(*) Can Be Expensive in PostgreSQL PostgreSQL uses MVCC — Multi-Version Concurrency Control — to manage concurrent access to data. That's what lets multiple transactions work at the same time while each sees a consistent view of the database. But it also means row visibility depends on the snapshot the query is running under. That's why PostgreSQL can't answer: SELECT COUNT ( * ) FROM orders ; by simply reading an exact counter stored somewhere in the table's metadata. To return an exact result, it has to process the rows — or an index structure representing those rows — and determine which ones are part of the visible result. On a small table, that cost is invisible. On a table with millions of rows, the amount of work starts to matter. Which leads to an important distinction: returning a single row from COUNT(*) does not mean processing a single row. How to Analyze a COUNT with EXPLAIN ANALYZE Before reaching for an index, it's worth looking at what PostgreSQL is actually doing. Say we have this query: SELECT COUNT ( * ) FROM orders WHERE status = 'completed' ; We can analyze it with: EXPLAIN ( ANALYZE , BUFFERS ) SELECT COUNT ( * ) FROM orders WHERE status = 'completed' ; The goal isn't to hunt for an Index Scan by default. Worth checking instead: the scan type estimated rows vs. actual rows processed rows discard

If you're prepping for a Business Intelligence (BI) Engineer, Data Analyst, Data Engineer, or Data Scientist role at Amazon, you probably already know SQL matters. It's a core part of the hiring bar. But Amazon isn't just checking your syntax. They want to see if you can think in sets, write clean queries under pressure, and reason about data the way the business actually uses it. Here's exactly how to prepare based on what the interview actually rewards. What the Interview Really Tests Amazon's SQL rounds usually show up in one of two ways. It's either a technical screen using a shared coding tool, or a whiteboarding case-study during the main loop. Either way, the interviewer is watching for a few specific signals. For starters, correctness always beats speed. A working query is far better than a clever one that fails. Communication is also huge. Do you talk through your logic before you even touch the keyboard? You'll often get a vague ask, like finding the "best" customers. You are completely expected to define what "best" means out loud before you start writing Common Table Expressions (CTEs). And watch out for messy data. Nulls, duplicates, and mismatched grain are almost always baked into the problem on purpose. The Core Topics to Master Focus your prep time on a few specific areas. Actually, it turns out this is where almost all the interview questions live. Joins Inside and Out You need to know your INNER, LEFT, RIGHT, and FULL OUTER joins cold. Be ready to explain exactly why row counts change after each one. A classic Amazon-style question is finding customers who placed orders but never left a review. That's just a LEFT JOIN with a NULL check. The interviewers want to see you reach for it right away. Window Functions Functions like ROW_NUMBER() , RANK() , DENSE_RANK() , and LAG() or LEAD() show up constantly. You might see a common pattern—like finding the second-highest order value per customer, or calculating month-over-month growth. If you're shaky he

Background : In migration projects involving different databases, incompatibility of SQL syntax is often encountered. Question : If there is a large amount of code that needs to be rewritten, manual processing would be time-consuming and prone to errors. Is it possible to achieve automatic conversion of code syntax in large quantities through tools? Solution : The open-source tool ZGLanguage can be utilized to perform automated conversion of SQL code in large batches. For example： Suppose SQL PIVOT function is as follows : SELECT * FROM ( select country , state , yr , qtr , sales , cogs from table111 ) PIVOT ( SUM ( sales ) AS ss1 , SUM ( cogs ) AS sc FOR qtr IN ( 'Q1' AS Quarter1 , 'Q2' AS Quarter2 , 'Q3' AS Quarter3 , 'Q4' AS Quarter4 ) ) tmp ; Using the ZGLanguage conversion rule, execute the conversion to obtain the result : SELECT * FROM ( select ### , ### , ### SUM ( case when qtr = 'Q1' then sales else null end ) AS Quarter1_ss1 , SUM ( case when qtr = 'Q2' then sales else null end ) AS Quarter2_ss1 , SUM ( case when qtr = 'Q3' then sales else null end ) AS Quarter3_ss1 , SUM ( case when qtr = 'Q4' then sales else null end ) AS Quarter4_ss1 , SUM ( case when qtr = 'Q1' then cogs else null end ) AS Quarter1_sc , SUM ( case when qtr = 'Q2' then cogs else null end ) AS Quarter2_sc , SUM ( case when qtr = 'Q3' then cogs else null end ) AS Quarter3_sc , SUM ( case when qtr = 'Q4' then cogs else null end ) AS Quarter4_sc from ( select country , state , yr , qtr , sales , cogs from table111 ) where qtr IN ( 'Q1' , 'Q2' , 'Q3' , 'Q4' ) group by ### , ### , ### ) tmp ; The conversion rule is as follows : __DEF_FUZZY__ Y __DEF_DEBUG__ N __DEF_CASE_SENSITIVE__ N __DEF_LINE_COMMENT__ -- __DEF_LINES_COMMENT__ /* */ __DEF_STR__ __IF_KW__ <1,100> [1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz [0,100]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_ [NO] XXX __DEF_PATH__ __FROM_PIVOT_1_1__ 1 : frm @ %__IF_KW__ | from : tab @ | __TABLE_NAME__ : ssl @

Same query. Same table. Same million rows. One day it takes 4 seconds . The next day, 4 milliseconds . Nothing changed in the data. The only thing that changed was one line — you added an index . Four seconds to four milliseconds is a thousand times faster, from one line of SQL. But here's the part nobody tells you: half the indexes people add do nothing. The query stays slow, the writes get slower, and they can't figure out why. By the end of this you'll know what an index actually is — and the one rule that decides whether yours even gets used. Prefer to watch? Full walkthrough with the B-tree lookup animation: With no index: a full table scan You ask the database for one user by email. With no index, what does it do? It reads the first row. Not a match. The second row. Not a match. It keeps going — every single row — until it finds yours or runs out. A million rows, a million checks. SELECT * FROM users WHERE email = 'vlad@stack.dev' ; With no index, that WHERE line has only one way to run: look at all of them. The work grows with the table — ten times the rows, ten times the wait. That's a full table scan , and that's your four seconds. What an index actually is Most people picture an index as a copy of the table, or some kind of cache. It's neither. An index is a sorted map — just the column you search on, kept in order, with a pointer back to the full row. And the shape it's sorted into has a name: a B-tree (the default index in both Postgres and MySQL — technically a B+ tree). At the top, one node — the root . It splits into a few branches . Each branch splits again, down to the leaves , where the pointers to the rows actually live. Every node is sorted. The root doesn't hold your data — it holds signposts . Emails before "M"? Go left. "N" and after? Go right. Each step throws away half the tree, or more. You're never reading rows. You're following signs. The walk: three hops, not a million rows Watch what the lookup actually does: The root — one hop. A branc

Background : In migration projects involving different databases, incompatibility of SQL syntax is often encountered. Question : If there is a large amount of code that needs to be rewritten, manual processing would be time-consuming and prone to errors. Is it possible to achieve automatic conversion of code syntax in large quantities through tools? Solution : The open-source tool ZGLanguage can be utilized to perform automated conversion of SQL code in large batches. For example： Suppose 'ORACLE START WITH CONNECT' syntax code（ start_with_connect.sql ）： SELECT * FROM tree START WITH id = 1 CONNECT BY NOCYCLE PRIOR id = parentid ; By configuring the conversion rules, the above code can be directly converted into the following code（convert to "with recursive" syntax）： with recursive wr_tree as ( SELECT id , parentid , 1 as level from tree where id = 1 union SELECT tree . id , tree . parentid , level + 1 from tree , wr_tree where tree . parentid = wr_tree . id ) SELECT * from wr_tree order by id ; Conversion rule (STATR_WITH_CONNECT_SQL_REPLACE.syn) is as follows： __DEF_FUZZY__ Y __DEF_DEBUG__ N __DEF_CASE_SENSITIVE__ N __DEF_LINE_COMMENT__ -- __DEF_LINES_COMMENT__ /* */ __DEF_STR__ __IF_KW__ <1,100> [1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz [0,100]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_ __DEF_PATH__ __START_WITH_CONNECT__ 1 : sel @ %__IF_KW__ | select : cc @ | * : frm @ | from : srctab @ | __NAME__ : sta @ %__IF_KW__ | start : wth @ %__IF_KW__ | with : swp @ | __NAME__ : dy1 @ | = : int @ | __INT__ : str @ + __STRING__ : cnn @ %__IF_KW__ | connect : by @ %__IF_KW__ | by : ncy @ %__IF_KW__ CAN_SKIP | nocycle : prr1 @ %__IF_KW__ CAN_SKIP | prior : col1 @ | __NAME__ : dy @ | = : col2 @ | __NAME__ : end @ | ; ----------------------------------------------------------------------- 1 : sel @ | with : sel @ | recursive : sel @ | wr_ : srctab @ \ __NAME__ : sel @ STRING | as : sel @ | __\n__ : sel @ | ( : sel @ | __\n__ : sel @ | selec

The Transactional Outbox Pattern: Dual-Write Consistency in Distributed Systems One of the most dangerous anti-patterns in microservices architecture is the Dual-Write Vulnerability : updating a database record and immediately publishing an event to a message broker (e.g., RabbitMQ, Kafka) in the same API call. If the network fails or the broker is unavailable after the database transaction commits, the event is lost forever. Conversely, if the event publishes but the database rollback triggers, downstream consumers process a phantom event that does not exist in the source of truth. In this deep dive, we architect the Transactional Outbox Pattern with Change Data Capture (CDC) to guarantee At-Least-Once delivery with zero distributed locking overhead. Technical & Interview Cheat Sheet Approach Consistency Guarantee Failure Mode Overhead Dual Write (Naive) None (Eventual inconsistency) Message lost if broker drops Low 2-Phase Commit (2PC / XA) Strict Atomicity Blocking locks, single point of failure Very High Transactional Outbox (Polling) At-Least-Once Polling query table contention Moderate Outbox + CDC (Debezium) At-Least-Once (Zero Table Locking) Requires WAL decoder plugin Optimal 1: Database Schema Design The business entity change and the outbox event MUST commit within the exact same database transaction: -- Business Entity CREATE TABLE orders ( id UUID PRIMARY KEY DEFAULT gen_random_uuid (), customer_id UUID NOT NULL , total_amount NUMERIC ( 12 , 2 ) NOT NULL , status VARCHAR ( 32 ) NOT NULL , created_at TIMESTAMPTZ NOT NULL DEFAULT NOW () ); -- Transactional Outbox Table CREATE TABLE outbox_events ( id UUID PRIMARY KEY DEFAULT gen_random_uuid (), aggregate_type VARCHAR ( 64 ) NOT NULL , aggregate_id VARCHAR ( 64 ) NOT NULL , event_type VARCHAR ( 64 ) NOT NULL , payload JSONB NOT NULL , created_at TIMESTAMPTZ NOT NULL DEFAULT NOW () ); -- Index for high-throughput CDC streaming CREATE INDEX idx_outbox_created ON outbox_events ( created_at ); 2: Atomic C# Tra

Our dataset said the average loan was 2.3 million kroner. The number that actually mattered was 255,000. Both were correct. Only one of them was true. This is a writeup of three ways a dashboard can be arithmetically perfect and still lie, using real figures from an analysis of 1,000 Norwegian debt consolidation applications. If you build reporting for anyone, you have probably shipped at least one of these. 1. Summing a field that contains two different things A debt consolidation loan pays off your expensive credit card debt. It also, if you own property, rolls your existing mortgage into the same new loan. Same column in the database. Same loan_amount . Utterly different meaning. SELECT AVG ( loan_amount ) FROM applications ; -- 2,300,000 That query is right and the answer is useless. Of that 2.3 million, roughly 1.9 million is an existing mortgage being moved from one lender to another. The expensive debt, the part the customer actually has a problem with, averages 255,000 . So the headline figure overstates the thing you care about by a factor of nine. Nothing in the schema warns you. loan_amount is a number, AVG is a function, the result renders fine. The bug is that one column is holding two concepts and only a human who understands the domain will notice. -- what you actually wanted SELECT AVG ( unsecured_debt ) FROM applications ; -- 255,000 If a column can mean two things depending on another column, split it. Every time. 2. Reporting the mean when the distribution has a tail Income in this dataset runs from ordinary salaries up to about five million kroner. A handful of very high earners drag the mean upward: Mean income: ~635,000 Median income: 647,000 for homeowners, 550,000 for renters Look at what happens there. The mean sits between the two medians and describes neither group. Someone reading only the mean concludes the typical applicant earns 635,000. Nobody earns 635,000. It is an artefact. df . groupby ( ' housing ' )[ ' income ' ]. agg ([ ' mean

Hi Folks , In this article, I'm going to share my learnings in SQL . Install MySQL Workbench editor from **MySQL Downloads **found in internet. After Installation, Open MySQL Workbench Click on '+' icon to create new sql editor Click on import icon present on top in sql editor workbench After importing, See the data, schema tables present in left side panel We see the sql editor workspace, result Grid,output view. How to display data present in result table ? Use SELECT '*' as Keyword FROM table_name Ex: SELECT * FROM moviesdb.movies; Instead of moviesdb.movies we can use movies as name. Follow the steps below: Go to the left panel where movie data present, select movie data table -> Right click on default schema it highlights in bold -> Then, Write query as, SELECT * FROM movies Method - 2 USE moviesdb SELECT * FROM movies; Here we're using ' USE ' Keyword How can we check how many rows are present after writing query ? Just go to output view present in MySQL Workbench editor (present below to the result Grid) you can see there (or) Check with Excel sheet using filter option to check how many rows are present. Suppose we're writing query but we don't know recieved output is right or wrong. We can check with our excel sheet data using filter option if it matches with our output rows then our query is correct this is how we can debug and check ✅ output. There are some inbuilt clauses present in SQL 1.WHERE 2.COUNT 3.DISTINCT (for unique values) 4.LIKE 5.% - Wildcard Search Ex: %movies% Gives the movies data present anywhere in the sentence %movies - Returns movies data present starting at the sentence %movies - Returns of movies present at last in the sentence Ctrl + Scroll UP - Zoom in SQL Editor Database - Collection of rows and columns in tabular format with schema it has duplicate ids, unique ids in different formats like excel, .sql etc. How to save all written queries ? Write all queries at one place and highlight each query, do the next steps mentioned below,

If you work with Java/JDBC long enough, you eventually run into this situation: You have code like this: String sql = "SELECT * FROM users WHERE id = ? AND status = ?" ; PreparedStatement pst = con . prepareStatement ( sql ); pst . setLong ( 1 , userId ); pst . setString ( 2 , status ); And then, from a log or debugger, you know something like: userId = 42 status = ACTIVE But what you actually need is the SQL you can paste into your database client: SELECT * FROM users WHERE id = 42 AND status = 'ACTIVE' ; Doing this once is trivial. Doing it repeatedly while debugging production issues is annoying. It gets worse when: the SQL is split across several Java strings; values come from map.get("KEY"); there are dates or timestamps; strings contain apostrophes; some parameters are unresolved; the method contains several PreparedStatements. I kept doing this manually, so I built a small tool called Bind2SQL. What it does Bind2SQL takes Java/JDBC code and reconstructs the executable SQL. For example: String sql = "SELECT * FROM person " + "WHERE person_id = ? " + "AND type_id = ? " + "AND created_at >= ?" ; PreparedStatement pst = con . prepareStatement ( sql ); pst . setLong ( 1 , values . get ( "PERSON_ID" )); pst . setInt ( 2 , values . get ( "TYPE_ID" )); pst . setDate ( 3 , Date . valueOf ( "2026-09-02" )); With runtime values: {PERSON_ID=12648350, TYPE_ID=29} It produces something like: SELECT * FROM person WHERE person_id = 12648350 AND type_id = 29 AND created_at >= DATE '2026-09-02' ; The important part is that unresolved parameters are not silently guessed. If Bind2SQL cannot resolve something, it leaves it clearly marked so you can review it manually. Why I made it browser-only I often use this kind of tool with real application code and runtime values. That may include: internal SQL; identifiers; production log values; table names; application-specific data. So I didn't want a server in the middle. Bind2SQL runs entirely in the browser. There is: no backend; no

The bug that doesn't show up in tests — and what to do about it There is a class of bug in event-driven systems that is almost invisible in development and devastating in production: publishing a message to Kafka for data that never actually reached the database. It doesn't crash. It doesn't throw. The Kafka message goes out, the consumer picks it up, and it tries to process a batch that doesn't exist. Depending on your retry and error handling strategy, this can cascade silently for a long time before anyone notices. The fix is simple. The reason most people don't apply it is that the problem isn't obvious until you've seen it. The Problem: Publishing Inside the Transaction The intuitive approach is to publish to Kafka as part of the same transactional method: @Transactional public void process ( SettlementWindow window , LocalDate today , Participant participant ) { // ... FileBatch savedBatch = batchPort . save ( batch ); orderPort . updateStatusBatch ( orders ); // Publishes BEFORE the transaction commits publisherPort . publish ( savedBatch ); } This looks safe. The transaction is still open, the data is there, everything is consistent — until the transaction rolls back. If anything fails after publish() — another database update, a constraint violation, an unexpected exception — Spring rolls back the transaction. The database returns to its previous state. But Kafka already received the message. There is no rollback for Kafka. The consumer now holds a reference to a FileBatch that does not exist in the database. This is a phantom message . The Fix: afterCommit() Spring's TransactionSynchronizationManager provides a hook that fires after the transaction has successfully committed: @Transactional ( propagation = Propagation . REQUIRES_NEW ) public void process ( SettlementWindow window , LocalDate today , Participant participant ) { // ... FileBatch savedBatch = batchPort . save ( batch ); orderPort . updateStatusBatch ( orders ); // Kafka fires only after the d

Adding an index stops working eventually. Here's what we did when a nationwide logistics platform's core table crossed a billion rows — and the parts nobody warns you about. There's a specific moment in a backend engineer's life when the usual advice stops working. A query gets slow. You check the execution plan, you add an index, it gets fast again. This works for years. It works so reliably that it starts to feel like a law of nature. Then one day you add the index and nothing happens. Or worse — the index takes six hours to build, locks the table while it does, and the query is still slow at the end of it. That's roughly where we were on a nationwide logistics platform processing tens of thousands of orders a day. The tracking events table — one row per scan, per parcel, per status change — had crossed a billion rows. Every parcel generated a dozen or more events on its journey. The table only ever grew. This is what we did about it, and more usefully, what nobody told us beforehand. First: are you sure you need this? Partitioning is not a performance trick you reach for when a query feels sluggish. It carries real operational cost, and most tables that people want to partition should just be indexed properly. Some honest signals that you're actually at the boundary: Your indexes no longer fit comfortably in memory, so index reads hit disk Index maintenance — REINDEX, VACUUM, ANALYZE — takes so long you can't schedule it Deleting old data is impossible in practice, because a DELETE of a hundred million rows will destroy your write throughput for hours Your queries almost always filter on a single obvious dimension, usually time That last one matters more than the rest. Partitioning only helps if your access pattern lines up with how you split the data. If your queries hit every partition anyway, you have added complexity and gained nothing. For us the alignment was clean: nearly every query on the events table was scoped to a date range. Operations dashboards loo

By Michael Nocito , data analyst · Published August 8, 2026 By the end of this page you will be running SQL directly against a CSV file on your machine, with no import step, no CREATE TABLE , and no schema written by hand. DuckDB reads the file where it lies, works out the column types itself, and gives you a normal SQL result. It takes one command to install and about a minute to prove. Here is what to actually do today. Run python -m pip install duckdb , then write a query with your CSV's filename in quotes where the table name would normally go. That is the entire idea, and everything else on this page is a consequence of it. The short version: a file is a table. It suits large files and folders of files, it does not replace SQLite for a shared database you keep, and section 6 says which to use when. The missing import step is the one idea worth the page, so it gets the picture. The original carries a diagram here. In words: Two horizontal sequences. The upper sequence runs through four stages joined by arrows: a file icon, then a box representing a schema being written, then a database cylinder, then a result grid. The lower sequence has only two stages joined by a single long arrow: the same file icon on the left and the same result grid on the right, with the middle two stages absent and the empty space where they used to be left visibly blank. Every output on this page is real. Run on 8 August 2026 with DuckDB 1.5.5 on Windows, against a 412-row CSV exported from the Chinook sample database. The numbers match the ones in the sample-database guide and the Python guide on purpose, because it is the same data through three different tools. 1. Install it Before the explanation: every database you have met so far needed you to create a table before you could put anything in it. What would have to be true for that step to be unnecessary? python -m pip install duckdb That is the whole installation. No server, no service running in the background, no configuration fi

Many engineers believe Common Table Expressions (CTEs) are always faster than subqueries. In modern SQL Server (and PostgreSQL), that is a myth . Here is what actually happens under the hood: 1. Inlining & The Query Optimizer By default, the SQL optimizer treats standard CTEs and derived tables (subqueries) almost identically: The engine expands both into the same relational tree. They generate the exact same execution plan and I/O cost . -- Pattern A: Derived Table (Subquery) SELECT DeptID , EmpName , Salary FROM ( SELECT DeptID , EmpName , Salary , DENSE_RANK () OVER ( PARTITION BY DeptID ORDER BY Salary DESC ) AS rnk FROM Employees ) RankedData WHERE rnk <= 2 ; -- Pattern B: Common Table Expression (CTE) WITH RankedData AS ( SELECT DeptID , EmpName , Salary , DENSE_RANK () OVER ( PARTITION BY DeptID ORDER BY Salary DESC ) AS rnk FROM Employees ) SELECT DeptID , EmpName , Salary FROM RankedData WHERE rnk <= 2 ; 2. When CTEs Truly Win: Readability & Pipeline Stacking: You can chain 5 CTEs sequentially without deeply nested pyramid brackets. In-Place Deduplication: In SQL Server, you can run DELETE directly on a CTE, and it deletes duplicate rows straight from the real underlying table! WITH DuplicateCleaner AS ( SELECT CustomerID , Email , ROW_NUMBER () OVER ( PARTITION BY Email ORDER BY RegistrationDate ASC ) AS rn FROM Customers WHERE Email IS NOT NULL ) DELETE FROM DuplicateCleaner WHERE rn > 1 ; -- ✅ Clean in-place deletion! 3. The Big Trap (Spooling Overhead): If you reference the same CTE multiple times in a query (e.g. CTE_A JOIN CTE_A ), SQL Server may execute the underlying CTE query multiple times or create a Lazy Spool in tempdb . -> Fix: For heavy multi-million row reuse, use a Temporary Table ( #TempTable ) with an explicit Clustered Index instead! 💡 How do you choose between CTEs, Temp Tables, and Subqueries in your pipelines? 💼 Connect on LinkedIn: linkedin.com/in/arpitmbangre

I recently ran into a weird issue while working on a Laravel project on Windows using Laragon . Everything was working fine until I tried to import a database through phpMyAdmin. Instead of an SQL error, phpMyAdmin simply returned: Internal Server Error The server encountered an internal error or misconfiguration... No useful message. Just HTTP 500. My SQL file was around 97 MB , so at first I thought it was probably a PHP upload limit issue. It wasn't that simple. Here is how I debugged it. 1. Check which PHP configuration is actually running From Laragon Terminal: php --ini Then I checked the important error settings: php.exe -r "echo 'error_log=' . ini_get('error_log') . PHP_EOL;" php.exe -r "echo 'log_errors=' . ini_get('log_errors') . PHP_EOL;" php.exe -r "echo 'display_errors=' . ini_get('display_errors') . PHP_EOL;" My output was: error_log=D:/C-data/laragon/tmp/php_errors.log log_errors=1 display_errors=1 One small Laragon/Git Bash issue I also found was: type php returned: php is aliased to `winpty php.exe' Because of that, commands like: php -i | grep ... sometimes returned: stdout is not a tty Using php.exe directly avoids that problem. 2. Check the PHP error log My PHP error log was: D:/C-data/laragon/tmp/php_errors.log I reproduced the import error and checked it: tail -n 50 /d/C-data/laragon/tmp/php_errors.log Nothing useful appeared. That was an important clue. 3. Make sure browser PHP and CLI PHP use the same php.ini I created a temporary file: <?php phpinfo (); Then opened it through the browser. Important values were: Server API: CGI/FastCGI PHP Version: 8.4.4 Loaded Configuration File: D:\C-data\laragon\bin\php\php-8.4.4-nts-Win32-vs17-x64\php.ini My PHP limits were already high enough: upload_max_filesize = 512M post_max_size = 512M memory_limit = 512M max_execution_time = 36000 So the 97 MB SQL file should have been allowed by PHP. 4. Check Apache logs I located the Apache error log with: grep -Ri "ErrorLog" /d/C-data/laragon/etc/apache2 /d/C-da

A chatbot can sound convincing for five minutes without remembering anything. Then you mention the job interview you were stressed about last week, the name of your dog, or a small detail from a late-night conversation. It replies like none of it happened. That is where most "AI companion" demos fall apart. I am building Local Waifu , a desktop AI companion that runs on the user's own Mac or PC. One of the rules I set early was simple: conversations and memories should stay on the machine. No central chat database. No server that needs to be online for the character to remember someone. The rule sounds clean. Building it was not. Saving chats is not memory The first version of memory was the obvious one: save messages. That gives you history, which is useful, but it does not solve recall. A long chat history grows fast. Sending all of it back to a local language model on every message is slow, expensive in context space, and usually makes the reply worse. The model does not need to see every conversation from the last six months. It needs the few pieces that matter right now. If someone says, "I have to take Luna to the vet tomorrow," the character should be able to find that Luna is their dog. It should not need to reread hundreds of unrelated messages about work, movies, and dinner plans to get there. So I treated chat history and long-term memory as different things. Chat history is the recent conversation. It gives the model immediate context. Long-term memory is a small collection of facts, moments, preferences, and relationship details that may matter later. Those memories need to be searchable by meaning, not only by exact words. The memory data stays in SQLite I wanted the app to work without a hosted database, so the storage layer is local SQLite. Each character gets their own data. Chats, memories, extracted entities, and relationships are stored locally on the device. If a user creates two characters, one character does not quietly inherit the other one's

By Michael Nocito , data analyst · Published August 8, 2026 By the end of this page you will be running real SQL against a real database with nothing installed, and you will know which of the free browser tools suits which job. You will also know the thing none of them puts on the front page: some of them run entirely inside your browser, and some upload whatever you paste to a stranger's server. That difference decides what you are allowed to practise on. Here is what to actually do today. If you want a database already loaded and questions already written, open sql-practice.com . If you want to create your own tables and share the result with someone, open DB Fiddle . Both start working immediately with no account. The short version: browser-only tools keep your data on your machine, server-backed tools do not, and neither kind is the right place for anything from work. Where the data goes is the one idea that should drive your choice, so it gets the picture. The original carries a diagram here. In words: Two panels side by side, each drawn as a laptop outline containing a browser window. In the left panel a small data box sits inside the browser window, with a short circular arrow looping back into itself, showing the data never leaves the laptop. In the right panel the same data box has a long arrow leading out of the laptop, across a gap, and into a separate server rack drawn beyond the laptop's edge, with a copy of the data box now sitting in the rack as well. The original box remains, showing the data has been copied out rather than moved. Every tool below was opened and checked on 8 August 2026. These sites change often, so the descriptions describe what was actually on screen, and anything I could not confirm by looking is not claimed here. 1. Run your first query, right now Before the explanation: what do you think has to exist on your computer for a SELECT statement to return rows? The honest answer is nothing at all, and that surprises people who have sp

By Michael Nocito , data analyst · Published August 8, 2026 By the end of this page you will have a real database sitting on your own computer, with 11 tables, 3,503 tracks and 412 customer invoices in it, and you will have run a query that proves every table arrived intact. Then you will run a join across two of those tables, which is the thing a single spreadsheet can never teach you. It takes about five minutes and costs nothing. Here is what to actually do today. Download the Chinook database file, open it in DB Browser for SQLite, and run one query that counts the rows in every table. If the counts match the ones printed below, you have a working practice environment and you can stop shopping for one. The short version: get Chinook_Sqlite.sqlite , open it, count the rows, then join two tables. Northwind and Sakila are the other two names you will see, and there is a table further down saying when each is the right pick. The reason a sample database beats the CSV you already have is one idea, so it gets the picture. The original carries a diagram here. In words: Two panels side by side. The left panel holds a single grid of rows and columns, standing alone with nothing attached to it. The right panel holds four smaller grids arranged around each other. A highlighted column at the edge of each small grid is joined by a solid line to a matching highlighted column on a neighbouring grid, so all four grids are wired together into a connected shape. The left panel has no lines at all, because there is nothing for a line to reach. Every number on this page is real. I downloaded Chinook v1.4.5 and Northwind on 8 August 2026 and ran each query with SQLite 3.51.1. The counts, the outputs and the row multiplication are what came back, not what should have come back. If you have no database software at all yet, how to set up a SQL database is the fifteen-minute version of that step, and this page picks up right after it. 1. Why one CSV is not enough Before the explanation:

Disclosure: I build Schemity , a desktop ERD tool - this post is from our blog and uses it for the examples. TL;DR: The database has a built-in place to document a column - COMMENT ON COLUMN in PostgreSQL, the COMMENT attribute in MySQL - and almost nobody fills it in, because a sentence of prose has to travel the same path as a schema change: a migration file, a review, a deploy. Schemity keeps field descriptions in the diagram instead, where editing one generates no SQL, reads existing database comments in on import, and exports the result as a data dictionary. You can document a database column without touching the database: write the description in the model rather than in the schema. That sounds like a dodge until you price the alternative. The database's own mechanism for column documentation, COMMENT ON COLUMN in PostgreSQL and the COMMENT attribute in MySQL, sends a sentence of prose down exactly the same path as a change to how data is stored - a migration file, a code review, an approval, a deploy window - and on MySQL it does something worse than that. Schemity keeps field descriptions in the diagram, where editing one produces no SQL at all. This is why so many production schemas have thousands of columns and almost no comments. Not because nobody wanted to write them. Because writing one costs a deploy. How do I document a database column without running a migration? Keep the description in the model rather than in the storage engine. A field description is a fact about what the column means to your team; it changes no type, no constraint, no index, and nothing about what the database will accept. When it lives in the diagram, editing it is like editing a comment in a code file: you change it, review it in the same pull request as everything else, and nothing has to run against production for it to take effect. The moment that description is a column comment, it stops being prose and becomes DDL. Now it needs a migration file, and the migration needs a