7-Day SQL Mastery Roadmap for Job-Ready Backend/Data Developers

Pre-Setup Requirements

Tools to Install:

• MySQL Workbench (Official MySQL GUI)
• MySQL Server (Community Edition - Free)

Day 1: SQL Foundations & Database Basics

1. THEORY / INTRODUCTION

• Definition: SQL (Structured Query Language) is a standardized language for managing and querying data in relational databases 1
• How it works: SQL organizes data into tables with rows and columns, where each table represents an entity and relationships exist between tables 1
• Basic Structure: SQL uses commands like SELECT, INSERT, UPDATE, DELETE to interact with data 1
• Database vs DBMS: Understanding the difference between data storage and management systems

2. WHY IT'S IMPORTANT

• Industry Standard: 90% of companies use SQL databases for data management
• Job Market: Essential skill for Data Analyst, Backend Developer, Database Administrator roles
• Real-world Applications: E-commerce platforms, banking systems, social media platforms
• Interview Focus: Most technical interviews include SQL rounds for data-related positions

3. KEY CONCEPTS

• Database: Collection of organized data
• Table: Structure with rows (records) and columns (fields)
• Primary Key: Unique identifier for each row
• Data Types: INT, VARCHAR, DATE, DECIMAL, BOOLEAN
• Basic Commands: CREATE, SELECT, INSERT, UPDATE, DELETE
• Best Practices: Use uppercase for SQL keywords, proper indentation

4. PRACTICAL EXERCISES

Sample Database Schema - "Company Management System":

Practice Queries:

1. Basic SELECT: SELECT * FROM employees;
2. Specific Columns: SELECT first_name, last_name, salary FROM employees;
3. WHERE Clause: SELECT * FROM employees WHERE salary > 70000;
4. INSERT New Record: INSERT INTO employees (first_name, last_name, email, salary, department_id) VALUES ('Anita', 'Verma', 'anita@company.com', 72000, 1);
5. UPDATE Record: UPDATE employees SET salary = 78000 WHERE emp_id = 103;

5. TIPS OR NOTES

• Common Mistakes: Forgetting semicolons, case sensitivity in table names
• Efficient Techniques: Always use specific column names instead of SELECT *
• Interview Tip: Practice explaining your query logic step by step

6. REVISION / MINI QUIZ

1. What does SQL stand for?
2. Which command is used to retrieve data? (SELECT/INSERT/UPDATE)
3. Fix this query: select name from employee where salary > 50000

7. RESOURCES / REFERENCES

• Hindi YouTube: "MySQL Tutorial in Hindi" by CodeWithHarry
• Practice Platform: W3Schools SQL Tryit Editor (Hindi interface available)

Day 2: Data Filtering & Sorting Mastery

1. THEORY / INTRODUCTION

• WHERE Clause: Filters records based on specified conditions
• Comparison Operators: =, !=, <, >, <=, >=, BETWEEN, IN, LIKE
• Logical Operators: AND, OR, NOT
• ORDER BY: Sorts result sets in ascending or descending order
• LIMIT: Restricts number of records returned

2. WHY IT'S IMPORTANT

• Data Analysis: 80% of data analysis involves filtering and sorting
• Performance: Proper filtering reduces query execution time
• Business Intelligence: Essential for generating reports and insights
• Interview Focus: Most SQL interviews test filtering and sorting skills

3. KEY CONCEPTS

• WHERE: Condition-based filtering
• LIKE: Pattern matching with wildcards (%, _)
• IN: Multiple value matching
• BETWEEN: Range-based filtering
• IS NULL/IS NOT NULL: Handling missing data
• ORDER BY ASC/DESC: Sorting direction
• LIMIT: Result set limitation

4. PRACTICAL EXERCISES

Extended Sample Data:

Practice Queries:

1. Multiple Conditions: SELECT * FROM employees WHERE salary > 70000 AND department_id = 2;
2. Pattern Matching: SELECT * FROM employees WHERE first_name LIKE 'R%';
3. Range Filtering: SELECT * FROM employees WHERE salary BETWEEN 65000 AND 80000;
4. Multiple Values: SELECT * FROM employees WHERE department_id IN (1, 2, 3);
5. Sorting: SELECT * FROM employees ORDER BY salary DESC LIMIT 5;

5. TIPS OR NOTES

• Performance Tip: Use indexed columns in WHERE clauses
• Common Mistake: Using = instead of LIKE for pattern matching
• Interview Tip: Explain the difference between WHERE and HAVING

6. REVISION / MINI QUIZ

1. Which operator is used for pattern matching?
2. How do you sort in descending order?
3. Fix: SELECT * FROM employees WHERE name LIKE 'A*';

7. RESOURCES / REFERENCES

• Hindi YouTube: "SQL WHERE Clause Tutorial" by Geeky Shows
• Practice: Use the company_db for all filtering exercises

Day 3: Aggregate Functions & GROUP BY

1. THEORY / INTRODUCTION

• Aggregate Functions: Perform calculations on multiple rows and return single result
• Common Functions: COUNT(), SUM(), AVG(), MAX(), MIN()
• GROUP BY: Groups rows with same values into summary rows
• HAVING: Filters groups (used with GROUP BY)
• Statistical Analysis: Essential for data summarization

2. WHY IT'S IMPORTANT

• Business Reports: Generate sales summaries, employee statistics
• Data Analysis: Calculate KPIs, metrics, and performance indicators
• Interview Essential: 70% of SQL interviews include aggregate function questions
• Real-world Usage: Dashboard creation, financial reporting

3. KEY CONCEPTS

• COUNT(): Counts number of rows
• SUM(): Adds up numeric values
• AVG(): Calculates average
• MAX()/MIN(): Finds maximum/minimum values
• GROUP BY: Groups data by column values
• HAVING: Filters grouped results
• DISTINCT: Removes duplicates

4. PRACTICAL EXERCISES

Additional Sample Data:

Practice Queries:

1. Basic Aggregation: SELECT COUNT(*) as total_employees FROM employees;
2. Department-wise Summary: SELECT department_id, COUNT(*) as emp_count, AVG(salary) as avg_salary FROM employees GROUP BY department_id;
3. Sales Analysis: SELECT emp_id, SUM(sale_amount) as total_sales FROM sales GROUP BY emp_id;
4. Filtered Groups: SELECT region, COUNT(*) as sale_count FROM sales GROUP BY region HAVING COUNT(*) > 2;
5. Complex Aggregation: SELECT department_id, MAX(salary) as highest_salary, MIN(salary) as lowest_salary FROM employees GROUP BY department_id;

5. TIPS OR NOTES

• Performance: GROUP BY can be expensive on large datasets
• Common Mistake: Using WHERE instead of HAVING with GROUP BY
• Interview Tip: Explain the execution order: WHERE → GROUP BY → HAVING → ORDER BY

6. REVISION / MINI QUIZ

1. Which clause filters grouped results?
2. What's the difference between COUNT(*) and COUNT(column_name)?
3. Fix: SELECT department_id, COUNT(*) FROM employees WHERE COUNT(*) > 2;

7. RESOURCES / REFERENCES

• Hindi YouTube: "SQL Aggregate Functions" by Thapa Technical
• Practice: Create your own sales scenarios with the sample data

Day 4: Table Relationships & JOINS

1. THEORY / INTRODUCTION

• Relationships: How tables connect to each other in relational databases 1
• Foreign Keys: References to primary keys in other tables
• JOIN Types: INNER, LEFT, RIGHT, FULL OUTER JOIN
• Normalization: Reducing data redundancy by separating related data 1
• Referential Integrity: Maintaining consistency between related tables

2. WHY IT'S IMPORTANT

• Real-world Databases: 95% of production databases use multiple related tables
• Data Integrity: Prevents data duplication and maintains consistency 1
• Complex Queries: Essential for generating comprehensive reports
• Interview Critical: JOINs are the most tested SQL concept in interviews

3. KEY CONCEPTS

• INNER JOIN: Returns matching records from both tables
• LEFT JOIN: Returns all records from left table, matching from right
• RIGHT JOIN: Returns all records from right table, matching from left
• FULL OUTER JOIN: Returns all records when there's a match in either table
• Foreign Key: Column that references primary key of another table
• Table Aliases: Shorthand notation for table names

4. PRACTICAL EXERCISES

Enhanced Database Schema:

Practice Queries:

1. INNER JOIN: SELECT e.first_name, e.last_name, d.dept_name FROM employees e INNER JOIN departments d ON e.department_id = d.dept_id;
2. LEFT JOIN: SELECT e.first_name, p.project_name FROM employees e LEFT JOIN projects p ON e.emp_id = p.emp_id;
3. Multiple JOINs: SELECT e.first_name, d.dept_name, s.sale_amount FROM employees e INNER JOIN departments d ON e.department_id = d.dept_id LEFT JOIN sales s ON e.emp_id = s.emp_id;
4. Aggregation with JOINs: SELECT d.dept_name, COUNT(e.emp_id) as employee_count FROM departments d LEFT JOIN employees e ON d.dept_id = e.department_id GROUP BY d.dept_name;
5. Complex JOIN: SELECT e.first_name, d.dept_name, SUM(s.sale_amount) as total_sales FROM employees e INNER JOIN departments d ON e.department_id = d.dept_id INNER JOIN sales s ON e.emp_id = s.emp_id GROUP BY e.emp_id, d.dept_name;

5. TIPS OR NOTES

• Performance: Always use proper indexes on JOIN columns
• Common Mistake: Forgetting table aliases in complex queries
• Interview Tip: Draw table relationships before writing JOIN queries

6. REVISION / MINI QUIZ

1. Which JOIN returns all records from the left table?
2. What happens if you don't specify JOIN condition?
3. Fix: SELECT * FROM employees, departments WHERE employees.dept_id = departments.dept_id;

7. RESOURCES / REFERENCES

• Hindi YouTube: "SQL JOINs Explained" by CodeWithHarry
• Visual Learning: Use MySQL Workbench's visual query builder

Day 5: Subqueries & Advanced Filtering

1. THEORY / INTRODUCTION

• Subqueries: Queries nested inside other queries
• Types: Single-row, Multi-row, Correlated subqueries
• Placement: Can be used in SELECT, WHERE, FROM, HAVING clauses
• Execution: Inner query executes first, result used by outer query
• Alternative to JOINs: Sometimes more readable than complex JOINs

2. WHY IT'S IMPORTANT

• Complex Business Logic: Handle multi-step data retrieval
• Data Analysis: Find top performers, outliers, comparative analysis
• Interview Favorite: Tests logical thinking and SQL depth
• Real-world Scenarios: Reporting, data validation, conditional updates

3. KEY CONCEPTS

• Single-row Subquery: Returns one row (use =, <, >)
• Multi-row Subquery: Returns multiple rows (use IN, ANY, ALL)
• Correlated Subquery: References outer query columns
• EXISTS: Tests for existence of rows in subquery
• Scalar Subquery: Returns single value
• Table Subquery: Returns multiple rows and columns

4. PRACTICAL EXERCISES

Additional Sample Data:

Practice Queries:

1. Single-row Subquery: SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
2. Multi-row Subquery: SELECT * FROM employees WHERE emp_id IN (SELECT emp_id FROM sales WHERE sale_amount > 30000);
3. Correlated Subquery: SELECT e1.first_name, e1.salary FROM employees e1 WHERE e1.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id = e1.department_id);
4. EXISTS: SELECT * FROM employees e WHERE EXISTS (SELECT 1 FROM projects p WHERE p.emp_id = e.emp_id);
5. Complex Subquery: SELECT dept_name FROM departments WHERE dept_id = (SELECT department_id FROM employees WHERE emp_id = (SELECT emp_id FROM performance_ratings WHERE performance_score = (SELECT MAX(performance_score) FROM performance_ratings)));

5. TIPS OR NOTES

• Performance: Correlated subqueries can be slow on large datasets
• Common Mistake: Using = with multi-row subqueries instead of IN
• Interview Tip: Explain subquery execution order and optimization

6. REVISION / MINI QUIZ

1. What's the difference between correlated and non-correlated subqueries?
2. When would you use EXISTS instead of IN?
3. Fix: SELECT * FROM employees WHERE department_id = (SELECT dept_id FROM departments WHERE dept_name IN ('Engineering', 'Sales'));

7. RESOURCES / REFERENCES

• Hindi YouTube: "SQL Subqueries Tutorial" by Geeky Shows
• Practice: Create complex business scenarios with nested conditions

Day 6: Data Modification & Database Design

1. THEORY / INTRODUCTION

• DML Operations: INSERT, UPDATE, DELETE for data manipulation
• DDL Operations: CREATE, ALTER, DROP for structure changes
• Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK
• Indexes: Improve query performance
• Transactions: ACID properties for data consistency 1

2. WHY IT'S IMPORTANT

• Database Administration: Essential for maintaining production databases
• Data Integrity: Constraints ensure data quality and consistency 1
• Performance Optimization: Indexes crucial for large-scale applications
• Job Requirements: Backend developers must handle data modifications safely

3. KEY CONCEPTS

• INSERT: Add new records (single/multiple rows)
• UPDATE: Modify existing records with WHERE clause
• DELETE: Remove records (always use WHERE!)
• ALTER TABLE: Modify table structure
• Constraints: Data validation rules
• Indexes: Speed up data retrieval
• Transactions: BEGIN, COMMIT, ROLLBACK

4. PRACTICAL EXERCISES

Database Design Practice:

Practice Queries:

1. Bulk INSERT: INSERT INTO customers (first_name, last_name, email, phone) VALUES ('Raj', 'Kumar', 'raj@email.com', '9876543210'), ('Sita', 'Sharma', 'sita@email.com', '9876543211');
2. Conditional UPDATE: UPDATE products SET price = price * 1.1 WHERE category = 'Electronics';
3. Safe DELETE: DELETE FROM orders WHERE order_status = 'Cancelled' AND order_date < '2023-01-01';
4. ALTER TABLE: ALTER TABLE customers ADD COLUMN loyalty_points INT DEFAULT 0;
5. Transaction Example:

5. TIPS OR NOTES

• Safety First: Always use WHERE clause with UPDATE/DELETE
• Backup: Take backups before major data modifications
• Interview Tip: Explain ACID properties and transaction importance

6. REVISION / MINI QUIZ

1. What happens if you UPDATE without WHERE clause?
2. Which constraint ensures unique values?
3. Fix: DELETE FROM customers; (make it safe)

7. RESOURCES / REFERENCES

• Hindi YouTube: "MySQL Database Design" by Thapa Technical
• Best Practices: Study database normalization rules

Day 7: Performance Optimization & Interview Preparation

1. THEORY / INTRODUCTION

• Query Optimization: Techniques to improve query performance
• Execution Plans: Understanding how MySQL executes queries
• Indexing Strategies: When and how to create effective indexes
• Common Performance Issues: N+1 queries, missing indexes, inefficient JOINs
• Interview Preparation: Common SQL interview patterns and questions

2. WHY IT'S IMPORTANT

• Production Readiness: Optimized queries essential for scalable applications
• Cost Efficiency: Better performance reduces server costs
• User Experience: Fast queries improve application responsiveness
• Interview Success: Performance questions separate junior from senior developers

3. KEY CONCEPTS

• EXPLAIN: Analyze query execution plans
• Index Types: PRIMARY, UNIQUE, COMPOSITE, PARTIAL
• Query Optimization: Use appropriate JOINs, avoid SELECT *
• Normalization: Reduce data redundancy 1
• Denormalization: Strategic data duplication for performance
• Common Patterns: Pagination, search optimization, reporting queries

4. PRACTICAL EXERCISES

Performance Testing Setup:

Interview-Ready Queries:

1. Performance Analysis: EXPLAIN SELECT * FROM large_orders WHERE order_date BETWEEN '2023-06-01' AND '2023-06-30';
2. Pagination: SELECT * FROM large_orders ORDER BY order_date DESC LIMIT 20 OFFSET 100;
3. Top N Query: SELECT customer_id, SUM(order_amount) as total_spent FROM large_orders GROUP BY customer_id ORDER BY total_spent DESC LIMIT 10;
4. Window Functions: SELECT order_id, customer_id, order_amount, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_amount DESC) as rank_in_customer FROM large_orders;
5. Complex Reporting: SELECT DATE_FORMAT(order_date, '%Y-%m') as month, status, COUNT(*) as order_count, AVG(order_amount) as avg_amount FROM large_orders GROUP BY month, status ORDER BY month, status;

5. TIPS OR NOTES

• Index Strategy: Create indexes on frequently queried columns
• Query Patterns: Learn common interview query patterns
• Explain Plans: Practice reading and interpreting execution plans
• Interview Prep: Practice explaining your thought process

6. REVISION / MINI QUIZ

Common Interview Questions:

1. Explain the difference between INNER JOIN and LEFT JOIN
2. How do you find the second highest salary?
3. What's the difference between WHERE and HAVING?
4. How do you optimize a slow query?
5. Explain database normalization

7. RESOURCES / REFERENCES

• Hindi YouTube: "SQL Interview Questions" by Knowledge Gate
• Practice Platform: HackerRank SQL challenges
• Mock Interviews: Practice with friends or online platforms

Final Job-Ready Checklist

Technical Skills Mastered:

✅ Database design and normalization
✅ CRUD operations (CREATE, READ, UPDATE, DELETE)
✅ Complex JOINs and relationships
✅ Aggregate functions and GROUP BY
✅ Subqueries and advanced filtering
✅ Performance optimization basics
✅ Transaction management

Interview Preparation:

✅ Common SQL interview questions
✅ Query optimization techniques
✅ Database design principles
✅ Real-world problem-solving scenarios

Practical Experience:

✅ Built complete database schemas
✅ Worked with realistic datasets
✅ Optimized query performance
✅ Handled complex business requirements

Next Steps:

1. Apply for Jobs: You're ready for fresher-level SQL positions
2. Continue Learning: Explore advanced topics like stored procedures, triggers
3. Build Projects: Create portfolio projects using your SQL skills
4. Practice Daily: Solve SQL challenges on coding platforms

Congratulations! You're now job-ready with SQL skills for Backend Developer, Data Analyst, and Database Administrator roles! 🚀