Mastering MySQL for Interviews

A Comprehensive Guide with Examples, Exercises, and Interview Questions

 Table of Contents
1. Introduction to MySQL
1.1 What is MySQL?
1.2 Features of MySQL
1.3 Installing and Setting Up MySQL
1.4 MySQL Command-Line Interface
2. MySQL Basics
2.1 Databases and Tables
2.2 Data Types in MySQL
2.3 Basic Commands: CREATE, DROP, ALTER
2.4 Inserting Data: INSERT
2.5 Selecting Data: SELECT
2.6 Modifying Data: UPDATE, DELETE
3. Advanced SQL Queries
3.1 Aggregate Functions: SUM, AVG, COUNT, GROUP BY, HAVING
3.2 Joins: Inner, Left, Right, Full
3.3 Sub-queries and Nested Queries
3.4 Views
4. Database Design
4.1 Normalization (1NF, 2NF, 3NF, BCNF)
4.2 Relationships: One-to-One, One-to-Many, Many-to-Many
4.3 Indexing and Keys: Primary, Foreign, Unique
5. MySQL Administration
5.1 User Management and Permissions
5.2 Backup and Restore
5.3 Performance Optimization
6. MySQL Functions and Stored Procedures
6.1 Using Functions: String, Date, Numeric
6.2 Stored Procedures and Triggers
6.3 Transactions and Error Handling
7. Common MySQL Interview Questions
7.1 Conceptual Questions
7.2 Query-Based Challenges
 7.3 Problem-Solving Scenarios
8. Real-World Applications
8.1 Building a Simple E-Commerce Database
8.2 Reporting System Example
9. Exercises and Challenges
9.1 Easy, Intermediate, and Advanced Level Problems
9.2 Real-World Scenarios
10. Appendix
10.1 Glossary of Terms
10.2 Cheat Sheet for Quick Revision
10.3 Resources for Further Learning
Chapter 1: Introduction to MySQL

1.1 What is MySQL?

MySQL is a widely used open-source relational database management system (RDBMS). It

enables users to store, retrieve, manage, and manipulate data in a structured way. Developed
by MySQL AB and now owned by Oracle Corporation, MySQL is known for its reliability,
scalability, and ease of use. It is widely used in web development, data analysis, and
enterprise applications.

Key Features of MySQL:

 Open-source and free to use.

 Cross-platform support (Windows, macOS, Linux, etc.).
 High performance and scalability.
 Strong data security measures.
 Support for multiple storage engines.
 Integration with many programming languages like PHP, Java, Python, etc.
 Active community support.

1.2 Features of MySQL

1. Reliability and Stability: MySQL has a proven track record of handling large-scale
applications efficiently.
2. Flexibility: Supports various data types, including structured, semi-structured, and
unstructured data.
3. High Availability: MySQL’s replication and clustering features ensure minimal
downtime.
4. ACID Compliance: Ensures data integrity through atomicity, consistency, isolation,
and durability.
5. Support for Large Databases: Can handle databases with millions of records
efficiently.

1.3 Installing and Setting Up MySQL

1.3.1 System Requirements Before installation, ensure your system meets the following
requirements:

 A supported operating system (Windows, macOS, or Linux).

 At least 1 GB of RAM (2 GB or more recommended).
 Minimum 500 MB of disk space.

1.3.2 Installation Steps For Windows:

1. Download the MySQL Installer from the official website.

2. Run the installer and choose the desired installation type (Developer Default is
recommended for beginners).
3. Configure the MySQL server by setting a root password.
 4. Complete the installation and verify by logging in using the MySQL Command-Line
Client.

For Linux:

1. Use the package manager to install MySQL:

2. sudo apt-get update
3. sudo apt-get install mysql-server
4. Secure the installation using the command:
5. sudo mysql_secure_installation
6. Start the MySQL service:
7. sudo service mysql start

1.3.3 Testing the Installation

 Open the MySQL Command-Line Client or terminal.

 Log in using the root credentials:
 mysql -u root -p
 Run the following command to check the version:
 SELECT VERSION();

1.4 MySQL Command-Line Interface

The MySQL Command-Line Interface (CLI) is a powerful tool for managing and interacting
with your database. It allows users to execute SQL queries, manage users, and perform
administrative tasks.

Basic Commands:

 Log in to MySQL:
 mysql -u [username] -p
 Show all databases:
 SHOW DATABASES;
 Select a database:
 USE database_name;
 Show all tables in the database:
 SHOW TABLES;
 Exit the CLI:
 EXIT;

Exercise:

1. Install MySQL on your system.

2. Open the MySQL Command-Line Interface and create a new database named
test_db.
3. Verify the creation by listing all databases.

Expected Output:

CREATE DATABASE test_db;

SHOW DATABASES;
Summary: In this chapter, we introduced MySQL, explored its features, and walked through
the installation process. We also covered basic commands to get started with the MySQL
CLI. Practice the commands and ensure you have a running MySQL environment before
proceeding to the next chapter.

Chapter 2: MySQL Basics

2.1 Databases and Tables

A database is an organized collection of data, stored and accessed electronically. Tables are
the building blocks of a database, consisting of rows and columns to store data in a structured
format.

Key Concepts:

 Database: A container that holds tables and other database objects.

 Table: A structure with rows (records) and columns (fields).

Commands to Manage Databases:

 Create a new database:

 CREATE DATABASE database_name;
 Use an existing database:
 USE database_name;
 Delete a database:
 DROP DATABASE database_name;

Commands to Manage Tables:

 Create a table:
 CREATE TABLE table_name (
 column1 datatype,
 column2 datatype,
 column3 datatype
 );
 View table structure:
 DESCRIBE table_name;
 Delete a table:
 DROP TABLE table_name;

2.2 Data Types in MySQL

MySQL provides various data types to define the kind of data stored in each column.

1. Numeric Data Types:

 INT: Integer numbers.

 FLOAT, DOUBLE: Floating-point numbers.
 DECIMAL: Fixed-point numbers.
2. String Data Types:

 CHAR(n): Fixed-length strings.

 VARCHAR(n): Variable-length strings.
 TEXT: Large text.

3. Date and Time Data Types:

 DATE: Stores dates (YYYY-MM-DD).

 DATETIME: Stores date and time (YYYY-MM-DD HH:MM:SS).
 TIME: Stores time (HH:MM:SS).

2.3 Basic Commands: CREATE, DROP, ALTER

 Create a table:
 CREATE TABLE employees (
 id INT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(50),
 hire_date DATE
 );
 Modify a table:
 ALTER TABLE employees ADD COLUMN salary DECIMAL(10, 2);
 Delete a table:
 DROP TABLE employees;

2.4 Inserting Data: INSERT

 Add data to a table:

 INSERT INTO employees (name, hire_date, salary)
 VALUES ('John Doe', '2025-01-15', 55000.00);

2.5 Selecting Data: SELECT

 Retrieve all data from a table:

 SELECT * FROM employees;
 Retrieve specific columns:
 SELECT name, salary FROM employees;

2.6 Modifying Data: UPDATE, DELETE

 Update records:
 UPDATE employees SET salary = 60000.00 WHERE name = 'John Doe';
 Delete records:
 DELETE FROM employees WHERE name = 'John Doe';

Exercise:

1. Create a database named company_db.

2. Create a table named departments with columns: id, name, and location.
3. Insert three records into the departments table.
4. Retrieve all records from the table.
Expected Output:

CREATE DATABASE company_db;

USE company_db;
CREATE TABLE departments (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
location VARCHAR(50)
);
INSERT INTO departments (name, location)
VALUES ('HR', 'New York'), ('IT', 'San Francisco'), ('Finance', 'Chicago');
SELECT * FROM departments;

Summary: This chapter covered the fundamentals of databases and tables, data types, and
basic commands for managing and manipulating data. Practice these concepts to strengthen
your foundation in MySQL.

Chapter 3: Advanced Data Retrieval

3.1 Filtering Data with WHERE

The WHERE clause is used to filter records based on specific conditions.

Syntax:

SELECT column1, column2 FROM table_name WHERE condition;

Examples:

 Retrieve employees with a salary greater than 50,000:

 SELECT * FROM employees WHERE salary > 50000;
 Retrieve records matching multiple conditions:
 SELECT * FROM employees WHERE hire_date > '2024-01-01' AND salary >
50000;

3.2 Sorting Data with ORDER BY

The ORDER BY clause is used to sort the result set in ascending (default) or descending order.

Syntax:

SELECT column1, column2 FROM table_name ORDER BY column1 [ASC|DESC];

Examples:

 Sort employees by salary in ascending order:

 SELECT * FROM employees ORDER BY salary ASC;
 Sort by hire date in descending order:
 SELECT * FROM employees ORDER BY hire_date DESC;

3.3 Limiting Results with LIMIT

The LIMIT clause is used to restrict the number of rows returned by a query.

Syntax:

SELECT column1, column2 FROM table_name LIMIT number_of_rows;

Examples:

 Retrieve the first 5 employees:

 SELECT * FROM employees LIMIT 5;
 Retrieve 3 employees starting from the 2nd record:
 SELECT * FROM employees LIMIT 3 OFFSET 1;

3.4 Combining Conditions with AND, OR, and NOT

 AND: All conditions must be true.

 OR: At least one condition must be true.
 NOT: Reverses the condition.

Examples:

 Retrieve employees hired after 2024 and earning more than 50,000:
 SELECT * FROM employees WHERE hire_date > '2024-01-01' AND salary >
50000;
 Retrieve employees hired in 2024 or 2025:
 SELECT * FROM employees WHERE hire_date LIKE '2024%' OR hire_date
LIKE '2025%';

Exercise:

1. Create a database named sales_db.

2. Create a table named products with columns: id, name, price, and stock.
3. Insert five records into the products table.
4. Retrieve all products priced above 100.
5. Sort the products by price in descending order.

Expected Output:

CREATE DATABASE sales_db;

USE sales_db;
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
price DECIMAL(10, 2),
stock INT
);
INSERT INTO products (name, price, stock)
VALUES ('Laptop', 1200.00, 10), ('Mouse', 25.50, 50), ('Keyboard', 45.99,
30), ('Monitor', 199.99, 20), ('Headset', 79.99, 15);
SELECT * FROM products WHERE price > 100;
SELECT * FROM products ORDER BY price DESC;
Summary: This chapter covered advanced data retrieval techniques, including filtering,
sorting, limiting results, and combining conditions. Mastering these techniques is crucial for
effective data analysis.

Chapter 4: Aggregate Functions and Grouping

4.1 Understanding Aggregate Functions

Aggregate functions in MySQL are used to perform calculations on a set of values and return
a single value. Common aggregate functions include:

1. COUNT(): Returns the number of rows.

2. SUM(): Calculates the total sum of a numeric column.
3. AVG(): Computes the average of a numeric column.
4. MIN(): Finds the smallest value.
5. MAX(): Finds the largest value.

Examples:

 Count the number of employees:

sql
CopyEdit
SELECT COUNT(*) FROM employees;

 Calculate the total salary of all employees:

sql
CopyEdit
SELECT SUM(salary) FROM employees;

 Find the highest salary:

sql
CopyEdit
SELECT MAX(salary) FROM employees;

4.2 Grouping Data with GROUP BY

The GROUP BY clause groups rows with the same values in specified columns and allows
aggregate functions to be applied to each group.

Syntax:

sql
CopyEdit
SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;

Examples:
  Find the total salary for each department:

sql
CopyEdit
SELECT department_id, SUM(salary)
FROM employees
GROUP BY department_id;

 Count employees in each department:

sql
CopyEdit
SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;

4.3 Filtering Groups with HAVING

The HAVING clause filters groups after the GROUP BY clause is applied. It is used instead of
WHERE for aggregate functions.

Syntax:

sql
CopyEdit
SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING condition;

Examples:

 Show departments with a total salary greater than 100,000:

sql
CopyEdit
SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id
HAVING total_salary > 100000;

4.4 Combining Clauses

You can use GROUP BY, HAVING, and other clauses like ORDER BY together for more complex
queries.

Example: Retrieve departments with more than 5 employees, sorted by total salary:

sql
CopyEdit
SELECT department_id, COUNT(*) AS employee_count, SUM(salary) AS
total_salary
FROM employees
GROUP BY department_id
HAVING employee_count > 5
ORDER BY total_salary DESC;

Exercise:

1. Create a table named sales with columns: id, product_name, quantity, and price.
2. Insert records for at least 5 products.
3. Write queries to:
o Find the total revenue (quantity * price) for all products.
o Group products by their names and calculate total revenue for each product.
o Display only products with revenue greater than 500.

Expected Output:

sql
CopyEdit
CREATE TABLE sales (
id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(50),
quantity INT,
price DECIMAL(10, 2)
);

INSERT INTO sales (product_name, quantity, price)

VALUES ('Laptop', 10, 750), ('Mouse', 50, 20), ('Keyboard', 30, 25),
('Monitor', 15, 200), ('Headphones', 20, 100);

SELECT product_name, SUM(quantity * price) AS total_revenue

FROM sales
GROUP BY product_name
HAVING total_revenue > 500;

Summary: In this chapter, we explored aggregate functions, grouping data, and filtering
groups using GROUP BY and HAVING. These tools are essential for analyzing data and
generating reports in MySQL. Practice these concepts with real-world examples to strengthen
your understanding.

Chapter 5: Advanced Joins and Subqueries

5.1 Understanding Joins

Joins in MySQL are used to combine rows from two or more tables based on a related
column between them.

Types of Joins:

1. Inner Join: Returns records with matching values in both tables.

2. Left Join (or Left Outer Join): Returns all records from the left table and the
matching records from the right table. Records without a match in the right table
appear as NULL.
3. Right Join (or Right Outer Join): Returns all records from the right table and the
matching records from the left table.
 4. Full Join (or Full Outer Join): Returns all records when there is a match in either
table (MySQL does not support this directly, but you can simulate it using a union of
LEFT JOIN and RIGHT JOIN).
5. Cross Join: Returns the Cartesian product of two tables (all combinations of rows).

Examples:

 Inner Join: Get employee names and their department names:

sql
CopyEdit
SELECT employees.name, departments.name AS department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.id;

 Left Join: Get all employees and their department names (including employees
without a department):

sql
CopyEdit
SELECT employees.name, departments.name AS department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.id;

5.2 Subqueries

Subqueries, also known as nested queries, are queries inside another query. They are useful
for performing intermediate calculations or filtering data.

Types of Subqueries:

1. Scalar Subquery: Returns a single value.

2. Row Subquery: Returns a single row with multiple columns.
3. Table Subquery: Returns a set of rows and columns.

Examples:

 Find employees earning more than the average salary:

sql
CopyEdit
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

 Get the highest-paid employee's name:

sql
CopyEdit
SELECT name
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);
5.3 Combining Joins and Subqueries

You can combine joins and subqueries to handle complex queries.

Example: List all departments along with the total number of employees in each department,
but include only departments with more than 5 employees:

sql
CopyEdit
SELECT d.name AS department_name, COUNT(e.id) AS employee_count
FROM departments d
LEFT JOIN employees e
ON d.id = e.department_id
GROUP BY d.name
HAVING employee_count > 5;

Exercise:

1. Create two tables: products (id, name, category_id, price) and categories (id,
category_name).
2. Insert records into both tables.
3. Write queries to:
o Retrieve all products along with their category names.
o List all categories, including those with no products.
o Find the category with the most expensive product.

Expected Output:

sql
CopyEdit
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
category_id INT,
price DECIMAL(10, 2)
);

CREATE TABLE categories (

id INT AUTO_INCREMENT PRIMARY KEY,
category_name VARCHAR(50)
);

INSERT INTO categories (category_name)

VALUES ('Electronics'), ('Clothing'), ('Groceries');

INSERT INTO products (name, category_id, price)

VALUES ('Laptop', 1, 750), ('Shirt', 2, 20), ('Milk', 3, 1.5);

SELECT p.name, c.category_name

FROM products p
INNER JOIN categories c
ON p.category_id = c.id;

SELECT c.category_name, COUNT(p.id) AS product_count

FROM categories c
LEFT JOIN products p
ON c.id = p.category_id
GROUP BY c.category_name;

SELECT c.category_name
FROM categories c
INNER JOIN products p
ON c.id = p.category_id
WHERE p.price = (SELECT MAX(price) FROM products);

Summary: In this chapter, we covered advanced techniques for joining tables and using
subqueries in MySQL. These tools are invaluable for solving complex data retrieval
challenges. Practice with your own datasets to master these concepts.

Chapter 4: Joins in MySQL

Joins are used in MySQL to combine rows from two or more tables based on a related
column between them. They are essential for working with relational databases and retrieving
data efficiently.

4.1 Types of Joins

1. INNER JOIN: Retrieves records that have matching values in both tables.
2. LEFT JOIN (OUTER JOIN): Retrieves all records from the left table and matching
records from the right table.
3. RIGHT JOIN (OUTER JOIN): Retrieves all records from the right table and
matching records from the left table.
4. FULL JOIN: Combines the result of both LEFT JOIN and RIGHT JOIN, returning
all records with matches in either table. (Not directly supported in MySQL; achieved
using UNION.)

4.2 Syntax and Examples

INNER JOIN

sql
CopyEdit
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

Example: Consider two tables:

 employees:

id name department_id
1 John 101
2 Alice 102
 id name department_id
3 Bob 101

 departments:

id name
101 HR
102 IT
103 Finance

Query:

sql
CopyEdit
SELECT employees.name, departments.name AS department
FROM employees
INNER JOIN departments
ON employees.department_id = departments.id;

Output:

name department
John HR
Alice IT
Bob HR

LEFT JOIN

sql
CopyEdit
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

Example: Retrieve all employees and their departments, even if no matching department
exists:

sql
CopyEdit
SELECT employees.name, departments.name AS department
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.id;

Output:

name department
John HR
Alice IT
Bob HR
RIGHT JOIN

sql
CopyEdit
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

Example: Retrieve all departments and their employees:

sql
CopyEdit
SELECT employees.name, departments.name AS department
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.id;

Output:

name department
John HR
Alice IT
NULL Finance

FULL JOIN (Using UNION)

sql
CopyEdit
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column
UNION
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

Example: Retrieve all employees and departments, showing NULL where there are no
matches.

4.3 Self-Join

A self-join is a join where a table is joined with itself. This is useful for hierarchical data,
such as employees and their managers.

Example:
sql
CopyEdit
SELECT A.name AS employee, B.name AS manager
FROM employees A
LEFT JOIN employees B
ON A.manager_id = B.id;

4.4 Cross Join

A cross join returns the Cartesian product of two tables, meaning all possible combinations of
rows.

Example:

sql
CopyEdit
SELECT employees.name AS employee, departments.name AS department
FROM employees
CROSS JOIN departments;

4.5 Exercises

1. Create two tables: students and courses. Populate them with data.
2. Write queries to:
o Retrieve all students enrolled in a specific course using INNER JOIN.
o List all courses, even those without students, using LEFT JOIN.
o Find students not enrolled in any course using RIGHT JOIN.

Expected Output:

sql
CopyEdit
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50)
);

CREATE TABLE courses (

id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50)
);

CREATE TABLE enrollments (

student_id INT,
course_id INT,
FOREIGN KEY (student_id) REFERENCES students(id),
FOREIGN KEY (course_id) REFERENCES courses(id)
);

INSERT INTO students (name) VALUES ('John'), ('Alice'), ('Bob');

INSERT INTO courses (name) VALUES ('Math'), ('Science');

SELECT students.name, courses.name AS course

FROM students
INNER JOIN enrollments ON students.id = enrollments.student_id
INNER JOIN courses ON courses.id = enrollments.course_id;

Summary: This chapter covered various types of joins in MySQL, their syntax, and practical
use cases. Joins are essential for working with relational data, enabling efficient querying
across multiple tables.