Chapter 6 — JOINS

Goal: Learn every type of JOIN and understand when to use it.

Assume we have two tables:

students
+----+--------+----------+
| id | name   | class_id |
+----+--------+----------+
| 1  | Rahul  | 101      |
| 2  | Priya  | 102      |
| 3  | Aman   | 105      |
| 4  | Neha   | NULL     |
+----+--------+----------+

classes
+-----+----------+
| id  | class    |
+-----+----------+
|101  | Physics  |
|102  | Maths    |
|103  | Biology  |
+-----+----------+

Q1. Suppose your manager says:

"Show every student along with their class name."

Answer

Need INNER JOIN

SELECT
    students.name,
    classes.class
FROM students
INNER JOIN classes
ON students.class_id = classes.id;

Output

+--------+----------+
| Rahul  | Physics  |
| Priya  | Maths    |
+--------+----------+

Why?

Only matching records are returned.

Aman isn't shown because class 105 doesn't exist.

Neha isn't shown because class_id is NULL.

Q2. Suppose your manager says:

"Show ALL students. If their class exists, show it; otherwise show NULL."

Answer

Need LEFT JOIN

SELECT
    students.name,
    classes.class
FROM students
LEFT JOIN classes
ON students.class_id = classes.id;

Output

+--------+----------+
| Rahul  | Physics  |
| Priya  | Maths    |
| Aman   | NULL     |
| Neha   | NULL     |
+--------+----------+

Why?

LEFT JOIN always keeps every row from the left table.

Q3. Suppose your manager says:

"Show every class, even if no student belongs to it."

Answer

Need RIGHT JOIN

SELECT
    students.name,
    classes.class
FROM students
RIGHT JOIN classes
ON students.class_id = classes.id;

Output

+--------+----------+
| Rahul  | Physics  |
| Priya  | Maths    |
| NULL   | Biology  |
+--------+----------+

Why?

RIGHT JOIN always keeps every row from the right table.

Q4. Suppose your manager says:

"Explain all JOINs in one example."

Answer

JOIN	Returns
INNER JOIN	Only matching rows
LEFT JOIN	All left rows + matching right rows
RIGHT JOIN	All right rows + matching left rows
FULL JOIN*	Everything from both tables

*MySQL doesn't directly support FULL JOIN.

JOIN Syntax

SELECT columns
FROM table1
JOIN table2
ON table1.column = table2.column;

Concepts Covered ✅

INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL JOIN (theory)
Matching rows
Non-matching rows
ON clause
NULL after joins
Real interview usage

Chapter 7 — GROUP BY

Assume:

orders

+----+----------+--------+
|id  | customer | amount |
+----+----------+--------+
|1   | Rahul    | 200    |
|2   | Rahul    | 100    |
|3   | Priya    | 500    |
|4   | Aman     | 100    |
|5   | Priya    | 300    |
+----+----------+--------+

Q1. Suppose your manager says:

"How many orders did each customer place?"

Answer

SELECT customer,
COUNT(*) AS total_orders
FROM orders
GROUP BY customer;

Output

Rahul   2
Priya   2
Aman    1

Why?

GROUP BY creates groups.

COUNT() counts rows inside each group.

Q2. Suppose your manager says:

"Calculate total money spent by every customer."

Answer

SELECT customer,
SUM(amount) AS total
FROM orders
GROUP BY customer;

Output

Rahul   300
Priya   800
Aman    100

Q3. Suppose your manager says:

"Show customers whose total spending is greater than 500."

Answer

SELECT customer,
SUM(amount) total
FROM orders
GROUP BY customer
HAVING SUM(amount) > 500;

Output

Priya   800

Why HAVING?

WHERE filters before grouping.

HAVING filters after grouping.

Q4. Suppose your manager says:

"What's the difference between WHERE and HAVING?"

Answer

-- Before grouping

WHERE amount > 100

-- After grouping

HAVING SUM(amount) > 500

Concepts Covered ✅

GROUP BY
Group creation
COUNT()
SUM()
HAVING
WHERE vs HAVING
Aliases
Group-wise calculations

Chapter 8 — Aggregate Functions

Assume:

employees

+------+--------+
|name  |salary  |
+------+--------+
|Rahul |50000   |
|Aman  |70000   |
|Priya |60000   |
|Neha  |90000   |
+------+--------+

Q1. Suppose your manager says:

"What is the highest salary?"

Answer

SELECT MAX(salary)
FROM employees;

Output

Q2. Suppose your manager says:

"What is the minimum salary and average salary?"

Answer

SELECT
MIN(salary),
AVG(salary)
FROM employees;

Output

50000
67500

Q3. Suppose your manager says:

"How much salary does the company pay in total?"

Answer

SELECT SUM(salary)
FROM employees;

Output

Q4. Suppose your manager says:

"How many employees are there?"

Answer

SELECT COUNT(*)
FROM employees;

Output

Aggregate Functions Summary

Function	Purpose
COUNT()	Count rows
SUM()	Total
AVG()	Average
MAX()	Largest value
MIN()	Smallest value

Concepts Covered ✅

COUNT
SUM
AVG
MAX
MIN
Numeric aggregation
Single-row result

Chapter 9 — Primary & Foreign Keys

Assume:

students

+----+-------+
|id  |name   |
+----+-------+
|1   |Rahul  |
|2   |Priya  |
+----+-------+

orders

+----+------------+
|id  |student_id  |
+----+------------+
|1   |1           |
|2   |1           |
|3   |2           |
+----+------------+

Q1. Suppose your manager says:

"Every student must have a unique ID. How will you enforce that?"

Answer

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

Why?

Primary Key

Unique
Cannot be NULL
One per table

Q2. Suppose your manager says:

"Orders should only belong to existing students."

Answer

CREATE TABLE orders(
    id INT PRIMARY KEY,
    student_id INT,
    FOREIGN KEY(student_id)
    REFERENCES students(id)
);

Why?

Foreign Key prevents invalid references.

You can't insert:

student_id = 999

if student 999 doesn't exist.

Q3. Suppose someone deletes Rahul.

What happens?

Answer

Depends on the foreign key rule.

ON DELETE CASCADE

Deletes Rahul and all related orders.

ON DELETE SET NULL

Orders remain, but student_id becomes NULL.

ON DELETE RESTRICT

Deletion is blocked if related orders exist.

Q4. Suppose the interviewer asks:

"Difference between Primary Key and Foreign Key?"

Answer

Primary Key	Foreign Key
Unique	Can repeat
Not NULL	Can be NULL
Identifies a row	References another table
One per table	Many allowed

Concepts Covered ✅

Primary Key
Foreign Key
Uniqueness
Referential Integrity
References
Parent table
Child table
ON DELETE CASCADE
ON DELETE SET NULL
ON DELETE RESTRICT

Chapter 10 — Database Design

Assume you're building an e-commerce website.

Q1. Suppose your manager says:

"Can we store customer name, address, products, quantity, and payment details all in one table?"

Answer

Technically yes…

orders

id
customer_name
customer_address
product_name
price
quantity
payment_type
payment_status
...

But this is bad design.

Problems:

Duplicate customer data
Hard to update
Wasted storage
Inconsistent information

Instead:

Customers

Orders

Products

Payments

Separate tables.

Q2. Suppose a customer changes their address.

What should happen?

Bad Design

Update 1000 rows

Good Design

Update only Customers table.

Orders automatically reference the customer.

Q3. Suppose one order contains five products.

How should you design it?

Answer

Orders

OrderItems

Products

Orders
--------
1

OrderItems
-----------
order_id
product_id
quantity

Never store:

Laptop,Mouse,Keyboard

inside one column.

Q4. Suppose the interviewer asks:

"What are the characteristics of a good database design?"

Answer

A good design should:

Avoid duplicate data
Use primary keys
Use foreign keys
Keep related data together
Separate unrelated data
Be easy to maintain
Be scalable
Prevent inconsistencies

Concepts Covered ✅

Normalization (basic idea)
Data duplication
One table per entity
Relationships
One-to-many relationship
Order & OrderItems pattern
Scalability
Maintainability
Good schema design principles