The Complete Guide to SQL¶

SQL is one of the most important languages in IT. Every developer should master it.

Basics¶

SELECT name, email FROM users WHERE active = true ORDER BY name LIMIT 10;

INSERT, UPDATE, DELETE¶

INSERT INTO users (name, email) VALUES (‘Jan’, ‘[email protected]’); UPDATE users SET active = false WHERE last_login < ‘2024-01-01’; DELETE FROM users WHERE active = false;

JOIN¶

SELECT u.name, o.total FROM users u INNER JOIN orders o ON u.id = o.user_id WHERE o.total > 100;

INNER JOIN = both must exist. LEFT JOIN = includes rows without a match on the right. RIGHT JOIN = the opposite.

Aggregation¶

SELECT status, COUNT(*), AVG(total), SUM(total) FROM orders GROUP BY status HAVING COUNT(*) > 10;

Subqueries¶

SELECT * FROM users WHERE id IN ( SELECT user_id FROM orders WHERE total > 1000 );

CTE (Common Table Expression)¶

WITH top_customers AS ( SELECT user_id, SUM(total) as total_spent FROM orders GROUP BY user_id ) SELECT u.name, tc.total_spent FROM users u JOIN top_customers tc ON u.id = tc.user_id ORDER BY tc.total_spent DESC;

Window Functions¶

SELECT name, salary, RANK() OVER (ORDER BY salary DESC) as rank, AVG(salary) OVER () as avg_salary FROM employees;

Indexes¶

CREATE INDEX idx_users_email ON users(email); CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

Transactions¶

BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT;

Tip¶

SQL is a language worth mastering at an advanced level. Window functions and CTEs will set you apart from the average.