Key MySQL Concepts: DROP/DELETE/TRUNCATE, Data Types, Storage Engines, Transactions, Views, Procedures, Indexes, UNION, and SQL Language Parts

1. DROP, DELETE and TRUNCATE Differences

Similarities: TRUNCATE, DELETE without a WHERE clause, and DROP all remove data stored in a table.

TRUNCATE clears table data and resets the auto‑increment ID to 1; DELETE only removes rows; DROP can delete the entire table or database and releases all allocated space.

Both TRUNCATE and DELETE keep the table structure, while DROP removes the table definition and any dependent constraints, though triggers, stored procedures, and functions that reference the table become invalid rather than being deleted.

Performance order (generally): DROP > TRUNCATE > DELETE.

Use DELETE to remove specific rows, DROP to remove the whole table, and TRUNCATE to delete all rows while keeping the table when the operation is not part of a transaction; if transaction control or trigger execution is required, use DELETE.

DELETE is a DML statement and does not auto‑commit; DROP and TRUNCATE are DDL statements and commit automatically.

2. Best Field Type for Storing Currency in MySQL

NUMERIC and DECIMAL are implemented identically in MySQL and conform to the SQL‑92 standard. They store values as strings rather than binary floating‑point numbers, preserving exact decimal precision, which is essential for monetary data.

3. MyISAM vs. InnoDB Differences

InnoDB supports transactions; MyISAM does not.

InnoDB uses row‑level locking; MyISAM uses table‑level locking.

InnoDB provides MVCC; MyISAM does not.

InnoDB supports foreign keys; MyISAM does not.

MyISAM supports full‑text indexes; InnoDB does not (in older versions).

MyISAM tables can be copied by directly copying the table files; InnoDB tables cannot.

InnoDB offers multiple row formats; MyISAM does not.

InnoDB is an index‑organized table; MyISAM is a heap table.

4. Four Transaction Properties (ACID) and Their Meaning

Atomicity: All operations in a transaction are completed entirely or not at all; if an error occurs, the transaction is rolled back to its initial state.

Consistency: The database’s integrity constraints remain intact before and after the transaction.

Isolation: Transactions execute as if they are the only operations running at that moment, preventing interference and ensuring serializable behavior.

Durability: Once a transaction commits, its changes are permanently stored and survive system failures.

5. Characteristics of MySQL Compared to Other Databases

Can handle tables with tens of millions of records.

Supports standard SQL syntax.

Portable, lightweight installation.

Good performance with extensive community support.

Simple debugging, management, and optimization relative to larger RDBMSs.

6. Purpose of Views and Whether They Can Be Modified

A view is a virtual table that does not store data itself; it dynamically retrieves data based on a query. Views simplify complex SQL, hide implementation details, and protect underlying data. After creation, a view can be queried like a table, but it cannot be indexed, have triggers, or default values, and ordering clauses inside the view may be overridden by outer queries.

7. Difference Between Stored Procedures and Triggers

Both consist of SQL statements, but a trigger cannot be invoked with EXECUTE; it fires automatically in response to data‑modifying events (INSERT, UPDATE, DELETE). Stored procedures are called explicitly by name. Triggers enforce business rules and referential integrity automatically, whereas stored procedures are invoked manually.

8. Role of Indexes and Their Advantages/Disadvantages

Indexes act like a book’s table of contents, allowing the database engine to locate rows quickly without scanning the entire table. They can be unique and may cover one or multiple columns. However, indexes slow down data insertion and increase storage size.

9. Difference Between UNION and UNION ALL

UNION removes duplicate rows by sorting and filtering the combined result set, which adds overhead. UNION ALL simply concatenates the result sets, preserving duplicates and offering better performance when duplicate elimination is unnecessary.

10. Parts of the SQL Language and Their Key Keywords

SQL is divided into four categories:

Data Definition Language (DDL): CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE/DROP INDEX, etc.

Data Manipulation Language (DML): SELECT, INSERT, UPDATE, DELETE.

Data Control Language (DCL): GRANT, REVOKE.

Data Query Language (DQL): SELECT.