DEV Community

King coder
King coder

Posted on

Frequently Asked Questions for Exam Preparation (RDBMS USING MySQL)

#sql #mysql #database #beginners

Q1: What is MySQL

MySQL is an open-source Relational Database Management System (RDBMS) that is used to store, manage, and organize data in the form of tables (rows and columns).

It uses SQL (Structured Query Language) to perform operations such as inserting, updating, deleting, and retrieving data.

⭐ Key Features of MySQL

  • Open-source & free to use
  • Fast and reliable
  • Stores data in structured tables
  • Supports large databases
  • Highly secure
  • Works on all major operating systems
  • Used in web applications like Facebook, YouTube, WordPress

⭐ Why MySQL is Popular?

  • Easy to learn and use
  • Cross-platform (Windows, Linux, macOS)
  • Supports multi-user environments
  • Works well with programming languages (PHP, Java, Python)
  • High performance for large data

Q2: Describe five DML commands with an example.

DML commands are used to insert, update, delete, and retrieve data stored in a database table.
These commands affect the data inside tables, not the table structure.

Below are the five most important DML commands with clear explanations and examples.

🟦 1. INSERT

Used to add new records (rows) into a table.

✔ Example: 

INSERT INTO students (id, name, age)
VALUES (1, 'Abhishek', 20);
Enter fullscreen mode Exit fullscreen mode

🟦 2. SELECT

Used to retrieve (fetch) data from a table.

✔ Example: 

SELECT name, age FROM students;
Enter fullscreen mode Exit fullscreen mode

🟦 3. UPDATE

Used to modify existing data in a table.

✔ Example: 

UPDATE students
SET age = 21
WHERE id = 1;
Enter fullscreen mode Exit fullscreen mode

🟦 4. DELETE

Used to remove rows from a table.

✔ Example: 

DELETE FROM students
WHERE id = 1;
Enter fullscreen mode Exit fullscreen mode

🟦 5. MERGE / REPLACE (MySQL alternative)

Used to insert a row if it does not exist
OR
update the row if it already exists (similar to UPSERT).

MySQL commonly uses REPLACE for this.

✔ Example: 

REPLACE INTO students (id, name, age)
VALUES (1, 'Abhishek', 22);
Enter fullscreen mode Exit fullscreen mode

⭐ Q3:- Explain MySQL Client–Server Architecture

MySQL works on a client–server architecture, which means the database system is divided into two parts:

🟦 1. MySQL Client

The client is any software or tool that sends SQL commands to the MySQL server.

Examples of clients:

  • MySQL Command-Line Client
  • MySQL Workbench
  • Web applications (PHP, Java, Python)
  • Any program connecting to MySQL

Clients cannot store data.
They only send queries and receive results.

🟦 2. MySQL Server

The server is the main program (mysqld) that:

  • Stores the database
  • Processes SQL queries
  • Checks user permissions
  • Returns results to the client

The server contains:

  • Query Parser → checks SQL syntax
  • Query Optimizer → finds fastest way to run the query
  • Execution Engine → executes the query
  • Storage Engine (InnoDB) → reads/writes data to disk

🟦 3. How the Architecture Works

(Explain in 4 simple steps)

✔ Step 1 — Client sends a SQL query

Example:

SELECT * FROM students;
Enter fullscreen mode Exit fullscreen mode

✔ Step 2 — Server checks login & permissions

✔ Step 3 — Server processes the query

  • Parser checks SQL
  • Optimizer finds best execution plan
  • Executor runs the query

✔ Step 4 — Server sends result back to the client

🟦 4. Simple Diagram (Perfect for Notes) 

Client (Sends SQL)
       |
       v
MySQL Server 
(Parser → Optimizer → Executor)
       |
       v
Storage Engine (InnoDB)
       |
       v
Client (Receives Result)
Enter fullscreen mode Exit fullscreen mode

🟦 5. Advantages of Client–Server Architecture

  • Many clients can connect at the same time
  • Centralized database management
  • High security & performance
  • Works over network/internet
  • Easy to maintain and scale

⭐Q4 Explain Locking in MySQL. Discuss the Types of Locking.

Locking in MySQL is a mechanism used to control access to data when multiple users try to read or modify the same data at the same time.

When a lock is applied, it prevents other users from making changes until the first user finishes the operation.
This ensures data safety, accuracy, and consistency.

✔ Why Locking is Important?

  • Prevents two users from modifying the same row together
  • Avoids data corruption
  • Ensures correct results in transactions
  • Maintains database consistency in multi-user environments

🟦 Types of Locking in MySQL

MySQL provides five major types of locking, mainly depending on storage engines like InnoDB and MyISAM.

⭐ 1. Table-Level Locking

  • The entire table is locked during a read or write operation.
  • Used by: MyISAM storage engine
  • Simple but slower when many users try to access the table.

✔ Example:

If one user updates a table → others must wait.

✔ Pros:

  • Easy to implement
  • Good for read-heavy operations

✔ Cons:

  • Low concurrency (many users cannot update together)

⭐ 2. Row-Level Locking

  • Only the specific rows being read or updated are locked.
  • Used by: InnoDB (default engine)

✔ Why it’s better?

Multiple users can work on different rows at the same time.

✔ Pros:

  • High concurrency
  • Better performance

✔ Cons:

  • Slightly more complex for the server to manage

⭐ 3. Page-Level Locking

(A combination of table-level and row-level)

  • Locks a group of rows (called a page) at once.
  • Used by older storage engines (not common now)

✔ Advantage:

  • Faster than row-level in some cases

✔ Disadvantage:

  • More locking conflict than row-level

⭐ 4. Internal Locking

  • These locks are handled automatically inside MySQL.

  • Protects internal server data structures like:

    • Cache
    • Indexes
    • Buffers

Users do not control internal locking; MySQL manages it itself.

⭐ 5. External Locking

  • These locks use the operating system’s file-level locks.
  • Mainly used to ensure MySQL and other programs do not access the same file simultaneously.

This is used less in modern MySQL installations.

⭐ Deadlock (Important Part of Locking)

A deadlock happens when:

  • Transaction A waits for B
  • Transaction B waits for A

→ Both are stuck forever.

✔ InnoDB automatically:

  • Detects deadlocks
  • Cancels one transaction
  • Releases locks

⭐ Q5: What are the various methods for making backup in MySQL? What is BACKUP TABLE? Is it useful?

📘 Backup in MySQL (Easy Explanation)

A backup in MySQL means creating a safe copy of your database so that you can recover it in case of data loss, server crash, accidental delete, or corruption.

MySQL provides many ways to take backups, depending on the requirement.

🟦 Various Methods for Making Backup in MySQL

⭐ 1. Logical Backup (mysqldump)

This is the most common backup method.

  • Creates a .sql file containing SQL commands (CREATE TABLE, INSERT).
  • Easy to read, edit, move, and restore.

Example: 

mysqldump -u root -p school > school_backup.sql
Enter fullscreen mode Exit fullscreen mode

⭐ 2. Physical Backup

This backup copies actual database files from disk.

  • Copies .ibd, .frm, .myd, .myi, and log files.
  • Useful for large databases because it is faster.

Types:

  • Cold backup → take backup when MySQL server is stopped
  • Hot backup → take backup while MySQL is running (needs extra tools)

⭐ 3. Binary Log Backup

MySQL stores all changes (INSERT, UPDATE, DELETE) in the binary log.

Binary logs are used for:

  • Point-in-time recovery
  • Replication

Useful for restoring data up to the last executed command.

⭐ 4. Hot Backups Using Tools

These allow backup without stopping the server.

Tools:

  • Percona XtraBackup
  • MySQL Enterprise Backup

Best option for live production databases.

⭐ 5. Export Data Using SELECT INTO OUTFILE

Exports table data to a text or CSV file.

SELECT * FROM students
INTO OUTFILE 'students.csv';
Enter fullscreen mode Exit fullscreen mode

Used for quick export of large tables.

⭐ 6. Import Data Using LOAD DATA INFILE

Fastest method to import huge files:

LOAD DATA INFILE 'students.csv'
INTO TABLE students;
Enter fullscreen mode Exit fullscreen mode

⭐ 7. Backup Using GUI Tools

  • phpMyAdmin
  • MySQL Workbench
  • DBeaver

Easy interface → ideal for beginners.

⭐ 3. What is Recovery?

Recovery is the process of restoring the database using backup files after a failure.

✔ Why Recovery is needed?

  • Server crash
  • Table corruption
  • Data deletion
  • Hardware failure

🟦 Q6 What is BACKUP TABLE?

BACKUP TABLE is an old MySQL command used to back up only MyISAM tables.

✔ Syntax: 

BACKUP TABLE table_name TO '/backup/';
Enter fullscreen mode Exit fullscreen mode

✔ How it works:

  • Copies the table files (.frm, .MYD, .MYI) to a backup location.

🟦 Is BACKUP TABLE useful today?

❌ Not very useful now.

Reasons:

  • Works only on MyISAM (old engine).
  • Modern MySQL uses InnoDB, which does NOT support this command.
  • Tools like mysqldump, XtraBackup, and binary logs are much better.

✔ Useful only for:

  • Very old MySQL versions
  • Small MyISAM tables

⭐ Q7 Explain the architecture of Replication in MySQL. Explain the types of Replication.

(Very Easy · Notes-Friendly · Full Marks Answer)

📘 What is Replication in MySQL? (Easy Definition)

Replication in MySQL means copying data automatically from one MySQL server (Master) to another server (Slave).

It is used for:

  • Backup
  • High availability
  • Load balancing
  • Reducing server load

🟦 Architecture of MySQL Replication (Easy Explanation)

MySQL replication mainly follows a Master → Slave structure.
The master handles write operations, and slaves copy those changes.

Replication uses 3 important parts:

⭐ 1. Master Server

  • The main server where all INSERT, UPDATE, DELETE operations happen.
  • Every change made on the master is written to a file called the Binary Log (binlog).

⭐ 2. Slave Server

  • The slave server receives a copy of the master’s changes.
  • It has a relay log which stores events from the master.
  • The slave updates its own database using these logs.

⭐ 3. Replication Threads

There are two threads on the slave:

✔ a. I/O Thread

  • Connects to the master
  • Reads binary log events
  • Writes them to the Relay Log

✔ b. SQL Thread

  • Reads relay log
  • Executes events (applies changes to slave database)

🟦 How Replication Works (Step-by-Step)

✔ Step 1 → Master writes changes to Binary Log

✔ Step 2 → Slave I/O thread copies the binary log

✔ Step 3 → Slave SQL thread executes the log

✔ Step 4 → Slave becomes an exact copy of the master

📘 Simple Diagram (Perfect for Notes) 

      MASTER SERVER
   (Binary Log Events)
            |
            |  I/O Thread
            v
      SLAVE SERVER
   Relay Log → SQL Thread
Enter fullscreen mode Exit fullscreen mode

🟦 Types of Replication in MySQL

⭐ 1. Asynchronous Replication (Default)

  • Master does NOT wait for the slave.
  • Slave may lag behind.
  • Fastest and most common.

⭐ 2. Semi-Synchronous Replication

  • Master waits until at least one slave receives the event.
  • More safe than asynchronous.
  • Slower but reduces data loss.

⭐ 3. Synchronous Replication

  • Master waits until all slaves update the data.
  • Highest data safety
  • Slowest type
  • Used in MySQL Cluster (NDB)

⭐ 4. Master–Slave Replication

  • One master → many slaves
  • Master handles writes
  • Slaves handle reads
  • Useful for load balancing

⭐ 5. Master–Master Replication

  • Both servers act as masters and slaves to each other
  • Both can accept write operations
  • High availability but risk of conflicts

Top comments (0)