DATABASE
10 articles in this category.
Advertisement-
Learn MySQL for free: a 50-lesson course built on a real shop database
A free, beginner-friendly MySQL course with 50 short lessons, one sample online-shop database, verified query results, a final project and a 40-question exam bank. Here is what it covers and how to study it.
-
Stored procedures vs functions in MySQL: parameters, errors and when to use each
Stored procedures and stored functions compared with working MySQL code: IN, OUT and INOUT parameters, return values, calling with CALL or inside SELECT, error handlers, restrictions, and the pros and cons of keeping logic in the database.
-
Database views: create, update, secure and when to use materialised views
What a view is and why you would use one: simplifying joins, hiding sensitive columns, updatable views and WITH CHECK OPTION, what makes a view read-only, why you cannot index a MySQL view, and materialised views as the alternative, with tested MySQL output.
-
Database transactions: ACID, isolation levels, locking and deadlocks
What a database transaction is and why money transfers need one: ACID explained with a real MySQL bank example, COMMIT, ROLLBACK and savepoints, isolation levels shown with two live sessions, row locks, deadlocks, and transactions in PHP with PDO.
-
SQL performance optimisation: find slow queries and fix them with EXPLAIN
A practical method for speeding up SQL: find the slow query, read EXPLAIN and EXPLAIN ANALYZE, then fix the usual causes (missing indexes, functions on columns, SELECT *, deep OFFSET paging, N+1 queries) with MySQL examples measured on 100,000 rows.
-
SQL subqueries: types, EXISTS vs IN, and how they differ from joins
What a subquery is, the scalar, row, table and correlated types, where a subquery can sit, IN versus EXISTS, the NOT IN and NULL trap, and when to prefer a join, a derived table or a CTE, with real MySQL output.
-
SQL joins in detail: every join type explained with MySQL output
A detailed guide to SQL joins: how a join builds its result, INNER, LEFT, RIGHT, full outer, CROSS and self joins, the ON versus WHERE trap, NULL keys, row multiplication and how to choose the right join, all with real MySQL output.
-
Database indexes explained: types, composite indexes and when they help
How a database index works and why it speeds up queries: B-tree indexes, primary and unique keys, composite indexes and the leftmost-prefix rule, covering indexes, the cases where an index is ignored, and the cost of too many, tested on 100,000 MySQL rows.
-
Database normalisation: 1NF, 2NF, 3NF and BCNF with a worked example
What normalisation is and why it matters: the insert, update and delete anomalies, then 1NF, 2NF, 3NF and BCNF explained step by step on one student-course table, built and tested in MySQL, plus when to denormalise.
-
Top 20 SQL and database topics for a senior developer interview
The twenty SQL and database topics senior developer interviews keep returning to, each with a short explanation, a tested MySQL example and the follow-up question to expect: joins, indexes, transactions, window functions, CTEs, locking and more.