SQL Database & JOINs Complete Master Notes

1. Database Create Karna & Select Karna

Definition

CREATE DATABASE ka use new database create karne ke liye hota hai. USE database_name; active session ke liye database ko select/activate karta hai taki saari aage wali tables us database ke andar banein.

Important Concept

USE office; execute karne ke baad, hum jo bhi CREATE TABLE ya INSERT queries chalayenge, woh automatically office database container ke andar execute honge.

</> SQL

CREATE DATABASE office;

USE office;

2. Relational Table Structure (ER Diagram)

Hum is master guide mein 4 Relational Tables banayenge: departments, employees, projects, aur employee_projects (Bridge Table).

departments: Department details store karega (Primary Key: department_id).
employees: Employee details & department link store karega (Primary Key: employee_id, Foreign Key: department_id).
projects: Project info & budgets store karega (Primary Key: project_id).
employee_projects: Many-to-Many relationship bridge table (Composite Primary Key: employee_id, project_id).

3. Table Creation & Constraints Explanation

A. Departments Table

</> SQL

CREATE TABLE departments (
    department_id INT PRIMARY KEY AUTO_INCREMENT,
    department_name VARCHAR(50) NOT NULL,
    location VARCHAR(50)
);

B. Employees Table (with Foreign Key)

</> SQL

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    employee_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    salary DECIMAL(10,2),
    department_id INT,
    manager_id INT,
    
    FOREIGN KEY (department_id)
    REFERENCES departments(department_id)
);

C. Projects Table

</> SQL

CREATE TABLE projects (
    project_id INT PRIMARY KEY AUTO_INCREMENT,
    project_name VARCHAR(100) NOT NULL,
    project_budget DECIMAL(12,2)
);

D. Employee Projects Table (Bridge Table with Composite Primary Key)

</> SQL

CREATE TABLE employee_projects (
    employee_id INT,
    project_id INT,
    
    PRIMARY KEY (employee_id, project_id),
    
    FOREIGN KEY (employee_id)
    REFERENCES employees(employee_id),
    
    FOREIGN KEY (project_id)
    REFERENCES projects(project_id)
);

4. Sample Data Insertion

Data insert karenge standard INSERT INTO command se. Notice that employee Neha has NULL department_id (helps us understand LEFT JOIN & NULL handling).

</> SQL

-- Departments Data
INSERT INTO departments (department_name, location)
VALUES
('HR', 'Chandigarh'),
('IT', 'Mohali'),
('Sales', 'Chandigarh'),
('Finance', 'Panchkula'),
('Marketing', 'Ludhiana');

-- Employees Data (Neha has department_id NULL)
INSERT INTO employees (employee_name, email, salary, department_id, manager_id)
VALUES
('Gagan', 'gagan@gmail.com', 45000, 2, NULL),
('Rahul', 'rahul@gmail.com', 35000, 2, 1),
('Aman', 'aman@gmail.com', 40000, 3, 4),
('Priya', 'priya@gmail.com', 50000, 1, NULL),
('Simran', 'simran@gmail.com', 38000, 4, 4),
('Neha', 'neha@gmail.com', 42000, NULL, NULL);

-- Projects Data
INSERT INTO projects (project_name, project_budget)
VALUES
('Website Development', 200000),
('Data Analytics', 300000),
('CRM System', 250000),
('Marketing Campaign', 150000);

-- Employee Projects Bridge Data
INSERT INTO employee_projects (employee_id, project_id)
VALUES
(1, 1), (1, 2), (2, 1), (3, 3), (4, 4), (5, 2);

5. INNER JOIN Deep Dive

Simple Definition

INNER JOIN sirf woh records return karta hai jinka dono tables (Table 1 aur Table 2) mein matching record available ho based on the ON condition.

Why Neha is Omitted?

Neha ka department_id NULL hai, isliye employees.department_id = departments.department_id match nahi hota, aur Neha result mein nahi aati.

</> SQL

SELECT
    employees.employee_name,
    departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.department_id;

Query Result Output

employee_name	department_name
Gagan	IT
Rahul	IT
Aman	Sales
Priya	HR
Simran	Finance

6. LEFT JOIN Deep Dive

Simple Definition

LEFT JOIN (ya LEFT OUTER JOIN) Left Table ke saare records return karta hai, chahe Right Table mein matching record ho ya na ho. Agar right table mein match na ho, toh NULL values aati hain.

Why Neha Appears Here?

Left table employees mein Neha hai. Right table departments mein match na hone ki wajah se department_name column mein NULL display hoga.

</> SQL

SELECT
    employees.employee_name,
    departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.department_id;

Query Result Output

employee_name	department_name
Gagan	IT
Rahul	IT
Aman	Sales
Priya	HR
Simran	Finance
Neha	NULL

7. RIGHT JOIN Deep Dive

Simple Definition

RIGHT JOIN (ya RIGHT OUTER JOIN) Right Table ke saare records return karta hai, chahe Left Table mein matching record ho ya nahi.

Why Marketing Appears Here?

Right table departments mein Marketing department hai jisme koi employee nahi hai. Right Join execution mein Marketing department aayega aur employee_name NULL dikhayega.

</> SQL

SELECT
    employees.employee_name,
    departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.department_id;

Query Result Output

employee_name	department_name
Gagan	IT
Rahul	IT
Aman	Sales
Priya	HR
Simran	Finance
NULL	Marketing

8. FULL OUTER JOIN (using UNION in MySQL)

Simple Definition

FULL OUTER JOIN dono tables ke saare records ko combine karke dikhata hai. Jinka match hota hai woh match hote hain, baaki jagah NULL show hota hai.

MySQL Solution (LEFT JOIN + RIGHT JOIN + UNION)

MySQL mein direct FULL OUTER JOIN syntax supported nahi hota. Isliye hum LEFT JOIN aur RIGHT JOIN queries ko UNION se combine karte hain.

</> SQL

SELECT
    employees.employee_name,
    departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.department_id

UNION

SELECT
    employees.employee_name,
    departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.department_id;

Query Result Output

employee_name	department_name
Gagan	IT
Rahul	IT
Aman	Sales
Priya	HR
Simran	Finance
Neha	NULL
NULL	Marketing

9. CROSS JOIN (Cartesian Product)

CROSS JOIN pehli table ke har single record ko doosri table ke har single record ke sath combine karta hai (Cartesian Product).

Row Calculation Formula

Agar Table 1 mein 6 records hain aur Table 2 mein 5 records hain, toh CROSS JOIN total 6 × 5 = 30 rows return karega.

No ON Condition Needed

CROSS JOIN mein koi ON condition nahi di jaati kyunki yeh matching par nahi balki har possible combination par kaam karta hai.

</> SQL

SELECT
    employees.employee_name,
    departments.department_name
FROM employees
CROSS JOIN departments;

10. SELF JOIN (Manager-Employee Relationship)

Simple Definition

Jab hum same table ko khud ke saath JOIN karte hain, use SELF JOIN kehte hain. Yeh hierarchical data (jaise Employee and Manager) ke liye exact requirement hoti hai.

Table Aliases (e and m)

Table aliases e (for Employee view) aur m (for Manager view) ka use karke hum same table ko do alag roles mein treat karte hain: e.manager_id = m.employee_id.

</> SQL

SELECT
    e.employee_name AS Employee,
    m.employee_name AS Manager
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.employee_id;

Query Result Output

Employee	Manager
Gagan	NULL
Rahul	Gagan
Aman	Priya
Priya	NULL
Simran	Priya
Neha	NULL

11. Multiple Tables JOIN (Connecting 4 Tables)

Hum ek hi SQL query mein 3, 4 ya usse zyada tables ko multi-level JOINs se connect kar sakte hain.

</> SQL

SELECT
    e.employee_name,
    d.department_name,
    p.project_name
FROM employees e

INNER JOIN departments d
ON e.department_id = d.department_id

INNER JOIN employee_projects ep
ON e.employee_id = ep.employee_id

INNER JOIN projects p
ON ep.project_id = p.project_id;

Query Result Output

employee_name	department_name	project_name
Gagan	IT	Website Development
Gagan	IT	Data Analytics
Rahul	IT	Website Development
Aman	Sales	CRM System
Priya	HR	Marketing Campaign
Simran	Finance	Data Analytics

12. JOIN + WHERE, ORDER BY & GROUP BY Clauses

A. JOIN + WHERE (Filtering IT Department Only)

</> SQL

SELECT
    e.employee_name,
    d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_name = 'IT';

B. JOIN + ORDER BY (Sorting Salaries Descending)

</> SQL

SELECT
    e.employee_name,
    d.department_name,
    e.salary
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
ORDER BY e.salary DESC;

C. JOIN + GROUP BY (Department Average Salary)

</> SQL

SELECT
    d.department_name,
    AVG(e.salary) AS average_salary
FROM departments d
LEFT JOIN employees e
ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;

13. JOIN + HAVING (Group-Level Filters)

WHERE vs HAVING Difference

WHERE: Individual rows ko filter karta hai pehle (before grouping).

HAVING: Aggregated groups ko filter karta hai (after GROUP BY execution).

Query Goal

Sirf woh departments list karo jinme 1 se zyada employees (total_employees > 1) kaam karte hain.

</> SQL

SELECT
    d.department_name,
    COUNT(e.employee_id) AS total_employees
FROM departments d
LEFT JOIN employees e
ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
HAVING COUNT(e.employee_id) > 1;

Query Result Output

department_name	total_employees
IT	2

14. JOINs & Important SQL Keywords Cheat Sheet

JOIN Type	Returned Records Summary
INNER JOIN	Sirf matching records return karta hai dono tables se.
LEFT JOIN	Left table ke saare records + matching right table records.
RIGHT JOIN	Right table ke saare records + matching left table records.
FULL OUTER JOIN	Dono tables ke saare records (MySQL mein LEFT + RIGHT + UNION).
CROSS JOIN	Cartesian Product (m × n combinations).
SELF JOIN	Same table ko khud se connect karna (Aliases e, m).

21 Important SQL Keywords

Keyword	Simple Definition
`CREATE DATABASE`	Creates a new database container.
`USE`	Selects and activates database session.
`CREATE TABLE`	Creates a new table with column specifications.
`INSERT INTO`	Inserts rows into a table.
`SELECT`	Retrieves records from tables.
`FROM`	Specifies the primary source table.
`JOIN`	Combines rows from multiple tables.
`ON`	Specifies joining condition between key columns.
`WHERE`	Filters individual rows before grouping.
`GROUP BY`	Groups identical rows into summary categories.
`HAVING`	Filters aggregated groups after GROUP BY.
`ORDER BY`	Sorts result rows ascending or descending.
`AS`	Assigns a temporary alias name to columns or tables.
`UNION`	Combines distinct result sets of two SELECT queries.

15. Primary Key, Foreign Key & Relational Constraints

PRIMARY KEY: Uniquely identifies each record in a table. Cannot be NULL or duplicate (e.g. department_id INT PRIMARY KEY).
FOREIGN KEY: Establishes a link/relationship between two tables by pointing to Primary Key of parent table (e.g. FOREIGN KEY (department_id) REFERENCES departments(department_id)).
COMPOSITE PRIMARY KEY: A primary key made of multiple columns combined together (e.g. PRIMARY KEY (employee_id, project_id) in bridge table).
UNIQUE: Prevents duplicate values in a column (e.g. email VARCHAR(100) UNIQUE).
NOT NULL: Prevents column from holding empty/NULL values (e.g. department_name VARCHAR(50) NOT NULL).
AUTO_INCREMENT: Automatically generates sequential integer numbers (1, 2, 3...) when new records are inserted.

16. Complete Full Runnable MySQL Script

Aap is poore code script ko copy karke directly MySQL Workbench ya Command Line mein execute kar sakte hain:

</> SQL

CREATE DATABASE office;

USE office;

CREATE TABLE departments (
    department_id INT PRIMARY KEY AUTO_INCREMENT,
    department_name VARCHAR(50) NOT NULL,
    location VARCHAR(50)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    employee_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    salary DECIMAL(10,2),
    department_id INT,
    manager_id INT,

    FOREIGN KEY (department_id)
    REFERENCES departments(department_id)
);

CREATE TABLE projects (
    project_id INT PRIMARY KEY AUTO_INCREMENT,
    project_name VARCHAR(100) NOT NULL,
    project_budget DECIMAL(12,2)
);

CREATE TABLE employee_projects (
    employee_id INT,
    project_id INT,

    PRIMARY KEY (employee_id, project_id),

    FOREIGN KEY (employee_id)
    REFERENCES employees(employee_id),

    FOREIGN KEY (project_id)
    REFERENCES projects(project_id)
);

INSERT INTO departments (department_name, location)
VALUES
('HR', 'Chandigarh'),
('IT', 'Mohali'),
('Sales', 'Chandigarh'),
('Finance', 'Panchkula'),
('Marketing', 'Ludhiana');

INSERT INTO employees (employee_name, email, salary, department_id, manager_id)
VALUES
('Gagan', 'gagan@gmail.com', 45000, 2, NULL),
('Rahul', 'rahul@gmail.com', 35000, 2, 1),
('Aman', 'aman@gmail.com', 40000, 3, 4),
('Priya', 'priya@gmail.com', 50000, 1, NULL),
('Simran', 'simran@gmail.com', 38000, 4, 4),
('Neha', 'neha@gmail.com', 42000, NULL, NULL);

INSERT INTO projects (project_name, project_budget)
VALUES
('Website Development', 200000),
('Data Analytics', 300000),
('CRM System', 250000),
('Marketing Campaign', 150000);

INSERT INTO employee_projects (employee_id, project_id)
VALUES
(1, 1), (1, 2), (2, 1), (3, 3), (4, 4), (5, 2);

SQL JOINs Master

SQL JOINs & Relational Database: Complete Master Notes

1. Database Create Karna & Select Karna

Definition

Important Concept

2. Relational Table Structure (ER Diagram)

3. Table Creation & Constraints Explanation

A. Departments Table

B. Employees Table (with Foreign Key)

C. Projects Table

D. Employee Projects Table (Bridge Table with Composite Primary Key)

4. Sample Data Insertion

5. INNER JOIN Deep Dive

Simple Definition

Why Neha is Omitted?

6. LEFT JOIN Deep Dive

Simple Definition

Why Neha Appears Here?

7. RIGHT JOIN Deep Dive

Simple Definition

Why Marketing Appears Here?

8. FULL OUTER JOIN (using UNION in MySQL)

Simple Definition

MySQL Solution (LEFT JOIN + RIGHT JOIN + UNION)

9. CROSS JOIN (Cartesian Product)

Row Calculation Formula

No ON Condition Needed

10. SELF JOIN (Manager-Employee Relationship)

Simple Definition

Table Aliases (e and m)

11. Multiple Tables JOIN (Connecting 4 Tables)

12. JOIN + WHERE, ORDER BY & GROUP BY Clauses

A. JOIN + WHERE (Filtering IT Department Only)

B. JOIN + ORDER BY (Sorting Salaries Descending)

C. JOIN + GROUP BY (Department Average Salary)

13. JOIN + HAVING (Group-Level Filters)

WHERE vs HAVING Difference

Query Goal

14. JOINs & Important SQL Keywords Cheat Sheet

21 Important SQL Keywords

15. Primary Key, Foreign Key & Relational Constraints

16. Complete Full Runnable MySQL Script