1.
Introduction to Databases & MySQL
Definition:
A database is an organized collection of structured information, or data, typically stored
electronically. MySQL is an open-source relational database management system (RDBMS) that
uses SQL (Structured Query Language) to manage and manipulate data.
Real-Life Example:
A social media platform like Facebook uses databases to store user profiles, posts, messages, and
media files in an organized and searchable way.
Interview Questions:
- What is a database? How is MySQL different from other databases?
- Why do we use MySQL?
2. Types of Databases
Definition:
Databases can be classified as Relational (RDBMS), NoSQL (non-relational), Hierarchical, Network,
and Object-oriented.
Real-Life Example:
E-commerce websites use RDBMS like MySQL for transactional data and NoSQL like MongoDB for
storing product catalog data.
Interview Questions:
- Explain the difference between SQL and NoSQL databases.
- Give examples of use cases for different types of databases.
3. SQL Commands & Queries
Definition:
SQL commands are instructions used to communicate with the database. Common types are DDL
(Data Definition Language), DML (Data Manipulation Language), DCL (Data Control Language), and
TCL (Transaction Control Language).
Real-Life Example:
When you register on an app, an INSERT command is executed to store your info in the database.
Interview Questions:
- What is the difference between DDL and DML?
- Write an example of an SQL SELECT query.
4. MySQL Data Types
Definition:
MySQL provides various data types such as INT, VARCHAR, DATE, FLOAT, TEXT, etc., to store
different kinds of values.
Real-Life Example:
A bank database may use INT for account numbers, DECIMAL for balances, DATE for transactions.
Interview Questions:
- Name some common MySQL data types and their usage.
- What data type would you use for storing an email address?
5. Table Creation
Definition:
Tables are database objects that store data in rows and columns. CREATE TABLE command is
used to define a new table structure.
Real-Life Example:
A hospital database may have a Patients table with columns like ID, Name, DOB, Diagnosis.
Interview Questions:
- How do you create a table in MySQL?
- What constraints can you define while creating a table?
6. SQL Queries (CRUD Operations)
Definition:
CRUD stands for Create, Read, Update, Delete - the four basic operations to manage data.
Real-Life Example:
An online order system uses INSERT for new orders, SELECT to view them, UPDATE to modify
details, and DELETE to cancel orders.
Interview Questions:
- Write a query to update a record.
- How do you delete all records in a table?
7. Aggregating & Grouping Data
Definition:
Aggregate functions like COUNT, SUM, AVG, MIN, MAX help summarize data. GROUP BY groups
rows sharing a property.
Real-Life Example:
Generating a report of total sales per product category in a store.
Interview Questions:
- What is GROUP BY used for?
- Give an example using SUM and GROUP BY.
8. Relationships & Joins in SQL
Definition:
Relationships define how tables relate. Joins combine rows from two or more tables.
Real-Life Example:
An employee database might join Employees and Departments tables to show each employee's
department.
Interview Questions:
- What is a foreign key?
- Explain INNER JOIN and OUTER JOIN with examples.
9. Subqueries, CTEs & Views
Definition:
Subqueries are nested queries. CTEs (Common Table Expressions) simplify complex joins. Views
are virtual tables.
Real-Life Example:
A view can present a summarized monthly sales report without giving access to raw data.
Interview Questions:
- What is a subquery?
- How is a CTE different from a view?
