Every article tagged SQL, in one place.
Showing 15 articles
PostgreSQL has become the go-to database for startups and enterprises alike. It's open source, reliable, and handles workloads that once needed several different databases. What developers love Rich SQL : CTEs, window functions, UPSERT , RETURNING , lateral jo...
If your MySQL habits date from version 5.x, you're missing features that make queries simpler and schema changes safer. Common table expressions WITH monthly AS ( SELECT DATE_FORMAT(created_at, '%Y-%m') AS month, SUM(total) AS revenue FROM orders GROUP BY mont...
Short answer: WHERE filters individual rows before grouping. HAVING filters groups after GROUP BY , so it can use aggregate functions like COUNT and SUM . -- Customers who placed more than 5 paid orders in 2026 SELECT customer_id, COUNT(*) AS paid_orders, SUM(...
Short answer: joins combine rows from two tables based on a condition. They differ in what happens to rows that have no match. -- customers: (1, Asha), (2, Ravi), (3, Meera) -- orders: (101, customer 1), (102, customer 1), (103, customer 9) INNER JOIN: only ma...
Duplicate rows creep in through double-clicked buttons, retried imports and missing constraints. Here's how to find them, clean them up and stop them coming back. 1. Find duplicates SELECT email, COUNT(*) AS copies FROM subscribers GROUP BY email HAVING COUNT(...
SQL injection happens when user input is pasted into a SQL string, letting an attacker change the query itself. It's old, well understood and still found in production code. The vulnerability // email comes from a login form const sql = `SELECT * FROM users WH...
A favourite interview question: given employees(id, name, department, salary) , find the Nth highest salary. Mention how you handle ties and missing values. 1. DISTINCT with LIMIT/OFFSET -- 3rd highest distinct salary SELECT DISTINCT salary FROM employees ORDE...
Short answer: an index is a separate, sorted data structure (usually a B-tree) that maps column values to row locations, so the database can find matching rows without scanning the whole table, much like a book's index. CREATE INDEX idx_orders_customer ON orde...
Short answer: normalisation organises tables to reduce duplicated data and prevent update anomalies. Each normal form removes a specific kind of redundancy. Starting point orders_raw order_id | customer | customer_city | products | product_prices 101 | Asha |...
Short answer: ACID describes the guarantees a database transaction gives: A tomicity, C onsistency, I solation and D urability. BEGIN; UPDATE accounts SET balance = balance - 1000 WHERE id = 1; UPDATE accounts SET balance = balance + 1000 WHERE id = 2; COMMIT;...
Short answer: both stack the results of two queries. UNION removes duplicate rows; UNION ALL keeps every row. Because removing duplicates needs a sort or hash step, UNION ALL is faster. -- Emails from two sources SELECT email FROM newsletter_subscribers UNION...
Short answer: DELETE removes selected rows, TRUNCATE quickly removes all rows but keeps the table, and DROP removes the table itself, including its structure. DELETE FROM sessions WHERE expires_at < NOW(); -- some rows TRUNCATE TABLE import_staging; -- all row...
Window functions compute values across related rows without collapsing them like GROUP BY does. Combined with CTEs, they replace a lot of application-side data crunching. Window function basics SELECT customer_id, id AS order_id, total_paise, ROW_NUMBER() OVER...
Short answer: a primary key uniquely identifies each row and can't be NULL (one per table). A unique key also enforces uniqueness but a table can have many, and they may allow NULLs. A foreign key links a column to a key in another table and enforces that the...
Short answer: all three number rows in a given order. They differ only in how they handle ties. SELECT name, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num, RANK() OVER (ORDER BY score DESC) AS rnk, DENSE_RANK() OVER (ORDER BY score DESC) AS dense F...
