ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
2
Must Have Technical Requirements
**This is Part 1 and only contains the roadmap for Technical Requirements that one must know be it Tech/Non Tech Background.
3
SQL
**Part 2, Part 3 sheets contain Projects that you can display to show your proficiency and the certifications + Non technical skills.
4
PYTHON
**Follow the Instagram Channel @analytics.with.miraj [https://www.instagram.com/analytics.with.miraj]
Instagram Link
5
POWERBI**To get Referral Follow the Telegram community and fill the referral form Telegram Link
6
EXCEL**This sheet will automatically get updated with time.
7
CONCEPTS OF DATABASES
8
9
10
SQL END TO END CONCEPTSPYTHON END TO END CONCEPTSEXCEL END TO END CONCEPTSPowerBI END TO END CONCEPTS
11
1. Introduction to SQLLinks
Completed
1. Introduction to PythonLinksCompleted1. Introduction to ExcelLinksCompleted1. Introduction to PowerBILinksCompleted
12
What is SQL?LinkDone
Basics of Python: Understanding Python syntax, variables, data types, and basic operations.
LinkDoneExcel Tutorial end to end[Link 1]LinkDone
PowerBI Tutorial end to end[Link 1] Follow the Official Documentation
https://learn.microsoft.com/en-us/power-bi/
Done
13
Understanding what SQL (Structured Query Language) is and its importance in managing databases.
LinkDoneResources: Official Python Documentation, Python for Everybody (online course)LinkDoneExcel Tutorial end to end[Link 2]
Advanced Excel Full Course 2023 | Excel Tutorial For Beginners | Excel Training | Simplilearn (youtube.com)
DonePowerBI Tutorial end to end[Link 2]
https://www.youtube.com/watch?v=esrtYvVQEZk&list=PLKpO3lQqxJ6YH6HmlslmTPdqVQ1wmVco7
Done
14
History and evolution of SQL.LinkDone
Control Structures: Conditional statements (if, else, elif), loops (for, while), and comprehensions.
LinkDoneExcel Tutorial end to end[Link 3]
Grammarly Basics: What You Can Get for Free (youtube.com)
DonePowerBi Tutorial end to end[Link 3]
https://www.youtube.com/playlist?list=PLoyECfvEFOjaMKFbBSKSmnOpEcXqqRegW
Done
15
Popular SQL database systems (e.g., MySQL, PostgreSQL, SQL Server, SQLite, Oracle).
DoneResources: Automate the Boring Stuff with Python LinkDoneBasic Excel Functions
16
Done2. Python for Data AnalysisLinkDoneData Entry and Formatting
17
SQL SyntaxLinkDone
NumPy: Numerical computing library. Essential for numerical operations on arrays.
LinkDoneBasic Formulas and Functions
18
Basic SQL commands and their syntax.Key Topics: Arrays, array operations, broadcasting, random sampling.LinkCell Referencing
19
Case sensitivity in SQL.
Resources: NumPy Documentation, Python Data Science Handbook by Jake VanderPlas
LinkSorting and Filtering DataDataBases END TO END CONCEPTS
20
SQL statement structure (keywords, clauses, expressions).Pandas: Data manipulation and analysis library.LinkConditional Formatting1. Introduction to PowerBILinksCompleted
21
Key Topics: Series, DataFrames, indexing, filtering, grouping, merging, aggregation.
LinkData ValidationDatabases Tutorial end to end[Link ]
https://www.freecodecamp.org/news/dbms-and-sql-basics/
Done
22
SQL Data TypesLinkDoneResources: Pandas Documentation, Python for Data Analysis by Wes McKinneyLinkDoneCharts and Graphs
23
Different data types available in SQL (e.g., INT, VARCHAR, DATE, BOOLEAN).
3. Data VisualizationPivot Tables and Pivot Charts
24
Choosing appropriate data types for columns.
Matplotlib: Plotting library for creating static, animated, and interactive visualizations.
LinkAdvanced Formulas and Functions
25
Null values and their significance.Key Topics: Basic plots (line, bar, scatter), customization, subplots, styles.Text Functions
26
Resources: Matplotlib Documentation, Practical Data Visualization with Matplotlib and Seaborn
Lookup Functions (VLOOKUP, HLOOKUP, XLOOKUP)
27
2. Basic SQL OperationsLinkDoneSeaborn: Statistical data visualization library based on Matplotlib.DoneDate and Time Functions
28
SELECT StatementKey Topics: Statistical plots (boxplot, violin plot, pairplot, heatmap), themeing.Logical Functions (IF, AND, OR, NOT)
29
Syntax and usage of the SELECT statement.Resources: Seaborn Documentation, Seaborn official tutorialsArray Formulas
30
Selecting specific columns.Plotly: Interactive plotting library.Data Analysis Tools (Goal Seek, Solver, Data Tables)
31
Using aliases for columns and tables.Key Topics: Interactive plots, dashboards, 3D plots.Power Query
32
Resources: Plotly Documentation, Plotly ExpressPower Pivot
33
WHERE Clause4. Data Cleaning and PreprocessingLinkMacros and VBA
34
Filtering records using the WHERE clause.LinkDoneHandling Missing Values: Identifying and imputing missing values.DoneExcel Add-ins and Integration
35
Using comparison operators (=, <, >, <=, >=, !=).Data Transformation: Scaling, normalization, encoding categorical variables.Collaboration and Sharing
36
Using logical operators (AND, OR, NOT).Feature Engineering: Creating new features, dimensionality reduction.Excel for Business Analysis
37
Using wildcard characters with LIKE.
Resources: Data Cleaning with Python (book), Hands-On Machine Learning with Scikit-Learn, Keras, and TensorFlow
Excel for Financial Modeling
38
5. Exploratory Data Analysis (EDA)LinkExcel for Data Visualization
39
ORDER BY ClauseLinkDoneDescriptive Statistics: Measures of central tendency and variability.Done
40
Sorting result sets using the ORDER BY clause.Visual EDA: Using plots to understand data distributions and relationships.
41
Sorting by multiple columns.Correlation Analysis: Understanding relationships between variables.
42
Sorting in ascending (ASC) and descending (DESC) order.Resources: Analyzing Data with Python, Practical Statistics for Data Scientists
43
6. Working with DatabasesLink
44
LIMIT ClauseLinkDoneUsing SQL with Python: Libraries like SQLite3, SQLAlchemy, and Pandas' read_sql.
45
Limiting the number of rows returned by a query.Resources: SQL for Data Science (online course), SQLAlchemy Documentation
46
Using LIMIT with OFFSET.Tools and Platforms to Practice:Done
47
3. Intermediate SQL OperationsKaggle: Competitions, datasets, and community.Link
48
Google Colab: Free Jupyter notebook environment.Link
49
Aggregate FunctionsLinkDone
50
Using aggregate functions (SUM, AVG, COUNT, MIN, MAX).
51
Combining aggregate functions with the GROUP BY clause.
52
53
GROUP BY ClauseLinkDone
54
Grouping rows based on one or more columns.
55
Grouping with aggregate functions.
56
Understanding the HAVING clause for filtering groups.
57
58
JOIN OperationsLinkDone
59
Types of joins (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN).
60
Joining multiple tables.
61
Self-joins and cross joins.
62
Understanding join conditions and performance considerations.
63
64
4. Advanced SQL OperationsLinkDone
65
Subqueries
66
Using subqueries in SELECT, FROM, WHERE, and HAVING clauses.
67
Correlated subqueries vs. non-correlated subqueries.
68
69
CTEs (Common Table Expressions)LinkDone
70
Using CTEs for simplifying complex queries.
71
Recursive CTEs.
72
73
Window FunctionsLinkDone
74
Introduction to window functions and their use cases.
75
Using ROW_NUMBER, RANK, DENSE_RANK, NTILE.
76
Using aggregate functions as window functions (e.g., SUM, AVG, COUNT, MIN, MAX).
77
Partitioning data with PARTITION BY and ordering data with ORDER BY in window functions.
78
79
IndexesLinkDone
80
Understanding indexes and their importance in query performance.
81
Creating and managing indexes.
82
Types of indexes (e.g., unique, composite, full-text).
83
Performance considerations and trade-offs.
84
85
5. SQL Data ManipulationLinkDone
86
87
INSERT StatementLinkDone
88
Syntax and usage of the INSERT statement.
89
Inserting single rows vs. multiple rows.
90
Using INSERT INTO SELECT for copying data.
91
92
UPDATE StatementLinkDone
93
Syntax and usage of the UPDATE statement.
94
Updating specific records using the WHERE clause.
95
Bulk updates.
96
97
DELETE StatementLinkDone
98
Syntax and usage of the DELETE statement.
99
Deleting specific records using the WHERE clause.
100
Using TRUNCATE to remove all records from a table.