Master MySQL Basics: Interview Q&A on Relational Databases

Relational Databases

Relational databases store data in tables composed of rows and columns. The relational model enables queries that filter rows based on conditions and combine multiple tables using joins.

Key Advantages

Intuitive two‑dimensional representation mirrors real‑world entities.

SQL provides powerful, expressive queries across one or many tables.

Transactional support guarantees atomicity, consistency, isolation, and durability (ACID).

MySQL Basics

SQL Overview

SQL (Structured Query Language) is the standard language for defining, manipulating, and controlling data in relational databases.

MySQL Overview

MySQL is a widely used open‑source relational database management system (RDBMS). It originated from MySQL AB, later acquired by Oracle. MariaDB is a binary‑compatible fork maintained by the original developers.

Checking the Server Version

From the operating‑system shell: mysql -V From within the MySQL client: status Or execute a SQL query:

SELECT VERSION();

Basic Data Types

Integer types: BIT, BOOL, TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT Floating‑point types: FLOAT, DOUBLE, DECIMAL String types:

CHAR, VARCHAR, TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT, TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB

Date and time types: DATE, DATETIME, TIMESTAMP, TIME, YEAR Other types:

BINARY, VARBINARY, ENUM, SET

CHAR vs VARCHAR

CHAR is fixed‑length; MySQL pads shorter values with spaces (which are trimmed on retrieval). Maximum length is 255 characters.

VARCHAR is variable‑length; it stores the actual string plus 1 byte of length if ≤255 bytes, otherwise 2 bytes. Maximum length is 65,535 bytes (subject to row size limits).

INT(10) vs BIGINT(10)

INT

occupies 4 bytes (signed range –2,147,483,648 to 2,147,483,647). BIGINT occupies 8 bytes (signed range –9,223,372,036,854,775,808 to 9,223,372,036,854,775,807). The number in parentheses is only display width, not storage size. Display width can be zero‑padded with the ZEROFILL attribute (e.g., INT(5) ZEROFILL displays 00042). Default display widths are 11 for INT and 20 for BIGINT.

Storage Engines

Common Engines

InnoDB – default engine, supports transactions, row‑level locking, MVCC, foreign keys.

MyISAM – older engine, no transaction support, table‑level locking, provides fast SELECT COUNT(*) and full‑text indexing.

InnoDB Features

Multi‑Version Concurrency Control (MVCC) with four standard isolation levels.

Clustered primary‑key index; secondary indexes implicitly contain the primary key.

Adaptive hash index, insert buffer, double‑write buffer, and configurable buffer pool ( innodb_buffer_pool_size).

Supports online (hot) backup without stopping writes.

MyISAM Features

Table‑level locking (shared for reads, exclusive for writes) with concurrent inserts.

Row count is stored, making SELECT COUNT(*) extremely fast when no WHERE clause is used.

Full‑text indexing and optional DELAY_KEY_WRITE for faster bulk inserts (risking index corruption on crash).

Engine Comparison

Transactions: InnoDB yes, MyISAM no.

Locking granularity: InnoDB row‑level, MyISAM table‑level.

MVCC: InnoDB yes, MyISAM no.

Foreign keys: InnoDB yes, MyISAM no.

Full‑text index: MyISAM yes (prior to MySQL 5.6), InnoDB no (added in later versions).

Database Design – Three Normal Forms

1NF – All attributes are atomic (indivisible).

2NF – Every non‑key attribute is fully functionally dependent on the whole primary key (eliminates partial dependencies).

3NF – No transitive dependencies; non‑key attributes depend only on the primary key.

Denormalization may be applied deliberately for performance, but it introduces redundancy.

SQL Statement Categories

DDL – Data Definition Language (e.g., CREATE, ALTER, DROP).

DML – Data Manipulation Language (e.g., INSERT, UPDATE, DELETE).

DTL – Data Transaction Language (e.g., COMMIT, ROLLBACK, SAVEPOINT).

DCL – Data Control Language (e.g., GRANT, REVOKE).

DROP vs TRUNCATE vs DELETE

DROP TABLE tbl_name;

removes the table definition and all data. TRUNCATE TABLE tbl_name; deletes all rows but retains the table structure and indexes. DELETE FROM tbl_name WHERE condition; removes rows that satisfy the WHERE clause; without a clause it removes all rows but logs each deletion.

Views

A view is a virtual table defined by a SELECT statement; it does not store data itself.

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED|MERGE|TEMPTABLE}]
    [DEFINER = user] [SQL SECURITY {DEFINER|INVOKER}]
    VIEW view_name [(column_list)] AS select_statement
    [WITH CHECK OPTION];

Advantages: simplifies complex queries, enforces security by exposing only required columns/rows, and isolates applications from underlying table changes.

Default Network Port

MySQL listens on TCP port 3306. Verify with:

SHOW VARIABLES LIKE 'port';

DISTINCT on Multiple Columns

DISTINCT

treats the entire list of selected columns as a single composite key. To deduplicate on a single column while still displaying other columns, use GROUP BY:

SELECT number, name FROM tbl_name GROUP BY number;

Stored Procedures

A stored procedure is a pre‑compiled set of SQL statements that may contain flow‑control logic. It can improve performance by reducing round‑trips between client and server.

DELIMITER $$
CREATE PROCEDURE proc_name()
BEGIN
    -- SQL statements go here
END$$
DELIMITER ;

Key differences from functions:

Invoked with CALL (functions with SELECT).

Parameters can be IN, OUT, or INOUT (functions only IN).

Functions must return a single value; procedures return no value but can output via OUT parameters.