Technical Skills Required to become a data analyst 😄👇

Tool 1: MS-Excel (Google sheets knowledge is a plus)

👉 Lookups (vlookup, xlookup, hlookup and its use cases)
👉 Pivot tables, Pivot charts
👉 Power Query, Power Pivot
👉 Conditional formatting
👉 Various charts and its formatting
👉 Basic VBA/Macro
👉 Major Excel functions/formulas (text, numeric, logical functions)

Tool 2: SQL (with any one RDBMS tool)

👉 Database fundamentals (primary key, foreign key, relationships, cardinality, etc.)
👉 DDL, DML statements (commonly used ones)
👉 Basic Select queries (single table queries)
👉 Joins and Unions (multiple table queries)
👉 Subqueries and CTEs
👉 Window functions (Rank, DenseRank, RowNumber, Lead, Lag)
👉 Views and Stored Procedures
👉 SQL Server/MySQL/PostGreSQL (any one RDBMS)
👉 Complete Roadmap for SQL

Tool 3: Power BI (equivalent topics in Tableau)

👉 Power Query, Power Pivot (data cleaning and modelling)
👉 Basic M-language and Intermediate DAX functions
👉 Filter and row context
👉 Measures and calculated columns
👉 Data modelling basics (with best practices)
👉 Types of charts/visuals (and its use cases)
👉 Bookmarks, Filters/Slicers (for creating buttons/page navigation)
👉 Advanced Tooltips, Drill through feature
👉 Power BI service basics (schedule refresh, license types, workspace roles, etc.)
👉 Power BI Interview Questions

Tool 4: Python (equivalent topics in R)

👉 Python basic syntax
👉 Python libraries/IDEs (Jupyter notebook)
👉 Pandas
👉 Numpy
👉 Matplotlib
👉 Scikitlearn

You may learn a combination of any 3 of these tools to secure an entry-level role and then upskill on the 4th one after getting a job.

➡ Excel + SQL + Power BI/ Tableau + Python/ R

So, in my learning series, I will focus on these tools mostly.

If we get time, I'll also try to cover other essential Topics like Statistics, Data Portfolio, etc.

Obviously everything will be free of cost.

Stay tuned for free learning

Share with credits: https://t.iss.one/sqlspecialist

Hope it helps :)

1100+ wanted to continue learning SQL, so here you go 😄

SQL LEARNING SERIES PART-6

Complete SQL Topics for Data Analysis
-> https://t.iss.one/sqlspecialist/523

Today we will learn about Data Modification:

Let's explore how to modify data within a database using SQL. There are three main operations: INSERT, UPDATE, and DELETE.

#### INSERT Statement:
Adds new rows of data into a table.

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

Example:

INSERT INTO employees (first_name, last_name, department) VALUES ('John', 'Doe', 'HR');

#### UPDATE Statement:
Modifies existing data in a table.

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

Example:

UPDATE employees SET department = 'Finance' WHERE last_name = 'Doe';

#### DELETE Statement:
Removes rows from a table based on a condition.

DELETE FROM table_name WHERE condition;

Example:

DELETE FROM employees WHERE last_name = 'Doe';

Ensure caution when performing UPDATE and DELETE operations to avoid unintended consequences.

This is a bit tricky but important concept.

Share with credits: https://t.iss.one/sqlspecialist

Hope it helps :)

SQL LEARNING SERIES PART-7

Complete SQL Topics for Data Analysis
-> https://t.iss.one/sqlspecialist/523

Today we will learn about Data Types and Constraints:

Understanding data types and constraints is crucial for designing a well-structured database.

#### Data Types:
SQL supports various data types, such as INT, VARCHAR, DATE, and more. Each column in a table must be assigned a specific data type.

CREATE TABLE table_name (
    column1 INT,
    column2 VARCHAR(50),
    column3 DATE
);

#### Constraints:
Constraints enforce rules on the data in a table. Common constraints include:

- PRIMARY KEY: Uniquely identifies each record in a table.
- FOREIGN KEY: Establishes a link between two tables.
- NOT NULL: Ensures a column cannot have NULL values.
- UNIQUE: Ensures all values in a column are different.

Example:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

This creates a table of employees with a primary key, non-null first and last names, and a foreign key linking to the departments table.

Understanding and implementing data types and constraints contribute to a well-designed and efficient database.

Share with credits: https://t.iss.one/sqlspecialist

Hope it helps :)

SQL LEARNING SERIES PART-8

Complete SQL Topics for Data Analysis
-> https://t.iss.one/sqlspecialist/523

Today we will learn about Indexes:

Indexes are crucial for optimizing the performance of database queries by allowing faster retrieval of data. They work similarly to the index of a book, making it quicker to find specific information.

#### Creating Indexes:

CREATE INDEX index_name ON table_name (column1, column2, ...);

Indexes can be created on one or multiple columns.

#### Removing Indexes:

DROP INDEX index_name ON table_name;

Indexes should be used judiciously, as they consume additional storage space and can impact the performance of write operations.

Optimizing queries often involves balancing the use of indexes to speed up read operations without significantly affecting write performance.

Share with credits: https://t.iss.one/sqlspecialist

Hope it helps :)

SQL LEARNING SERIES PART-9

Complete SQL Topics for Data Analysis
-> https://t.iss.one/sqlspecialist/523

Today we will learn about Views:

Views in SQL are virtual tables based on the result of a SELECT query. They provide a way to simplify complex queries and encapsulate logic.

#### Creating a View:

CREATE VIEW view_name AS
SELECT column1, column2 FROM table1 WHERE condition;

#### Querying a View:
Once created, you can treat a view like a regular table in your queries.

SELECT * FROM view_name;

#### Updating a View:
Views can be updated if they are based on simple SELECT statements.

CREATE OR REPLACE VIEW view_name AS
SELECT new_column1, new_column2 FROM new_table WHERE new_condition;

Views are useful for abstracting complex queries and enhancing the security of sensitive data.

Share with credits: https://t.iss.one/sqlspecialist

Hope it helps :)

SQL LEARNING SERIES PART-10

Complete SQL Topics for Data Analysis
-> https://t.iss.one/sqlspecialist/523

Today we will learn about Stored Procedures and Functions:

Stored procedures and functions are precompiled and stored in the database, providing a way to encapsulate and reuse logic on the server side.

#### Stored Procedures:
A stored procedure is a set of SQL statements that can be executed as a single unit.

CREATE PROCEDURE procedure_name
AS
BEGIN
    -- SQL statements
END;

#### Executing a Stored Procedure:

EXEC procedure_name;

#### Functions:
A function returns a value based on input parameters. There are two types: scalar functions and table-valued functions.

CREATE FUNCTION function_name (@param1 INT, @param2 VARCHAR(50))
RETURNS INT
AS
BEGIN
    -- SQL statements
    RETURN some_value;
END;

#### Calling a Function:

SELECT dbo.function_name(param1, param2);

Stored procedures and functions enhance code modularity and maintainability. They are valuable for implementing business logic on the database side.

Share with credits: https://t.iss.one/sqlspecialist

Hope it helps :)

SQL LEARNING SERIES PART-11

Complete SQL Topics for Data Analysis
-> https://t.iss.one/sqlspecialist/523

Let's also learn about Normalization today:

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. There are different normal forms, each with specific rules:

#### First Normal Form (1NF):
- Each column contains atomic (indivisible) values.
- There are no repeating groups or arrays.

#### Second Normal Form (2NF):
- Meets the requirements of 1NF.
- All non-key columns are fully functionally dependent on the primary key.

#### Third Normal Form (3NF):
- Meets the requirements of 2NF.
- Eliminates transitive dependencies, where non-key columns depend on other non-key columns.

#### Example:
Consider a denormalized table:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(50),
    product_name VARCHAR(50),
    price DECIMAL(10, 2)
);

Normalized to 3NF:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    product_id INT,
    order_date DATE,
    quantity INT,
    total_price DECIMAL(10, 2)
);

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50)
);

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50),
    price DECIMAL(10, 2)
);

Normalization helps avoid data anomalies and ensures efficient database design.

Share with credits: https://t.iss.one/sqlspecialist

Hope it helps :)

SQL LEARNING SERIES PART-12

Complete SQL Topics for Data Analysis
-> https://t.iss.one/sqlspecialist/523

Today, we will learn about Data Import and Export:

SQL provides mechanisms to import data into a database or export it to external files.

#### Importing Data:

-- Using INSERT INTO SELECT to import data from one table to another
INSERT INTO destination_table (column1, column2)
SELECT column3, column4 FROM source_table;

#### Exporting Data:

-- Using SELECT INTO OUTFILE to export data to a file
SELECT column1, column2 INTO OUTFILE 'file_path.csv'
FIELDS TERMINATED BY ',' FROM table_name;

These operations are useful for transferring data between databases, archiving, or exchanging information with other systems.

Share with credits: https://t.iss.one/sqlspecialist

Hope it helps :)

SQL LEARNING SERIES PART-13

Complete SQL Topics for Data Analysis
-> https://t.iss.one/sqlspecialist/523

Let's also learn about Window Functions today:

Window functions perform calculations across a set of table rows related to the current row. They are particularly useful for analytics and reporting.

#### ROW_NUMBER():
Assigns a unique number to each row within a partition of a result set.

SELECT column1, column2, ROW_NUMBER() OVER (PARTITION BY column3 ORDER BY column4) AS row_num
FROM table_name;

#### RANK(), DENSE_RANK():
Assign ranks to rows based on a specified column, with optional handling of ties.

SELECT column1, column2, RANK() OVER (ORDER BY column3) AS rank_num
FROM table_name;

#### LEAD(), LAG():
Access data from subsequent or previous rows within the result set.

SELECT column1, column2, LEAD(column2) OVER (ORDER BY column1) AS next_value
FROM table_name;

Window functions provide powerful capabilities for comparative and sequential analysis in a dataset.

Share with credits: https://t.iss.one/sqlspecialist

Hope it helps :)

SQL LEARNING SERIES PART-14

Complete SQL Topics for Data Analysis
-> https://t.iss.one/sqlspecialist/523

Today, we will learn about Advanced Filtering:

Advanced filtering in SQL involves using CASE statements for conditional logic within queries.

#### CASE Statement:
Allows conditional logic in a query, similar to a switch statement in other programming languages.

SELECT column1, column2,
    CASE
        WHEN condition1 THEN 'Result1'
        WHEN condition2 THEN 'Result2'
        ELSE 'DefaultResult'
    END AS custom_column
FROM table_name;

#### Example:

SELECT product_name, price,
    CASE
        WHEN price > 1000 THEN 'Expensive'
        WHEN price BETWEEN 500 AND 1000 THEN 'Moderate'
        ELSE 'Affordable'
    END AS price_category
FROM products;

This query categorizes products based on their price into 'Expensive', 'Moderate', or 'Affordable'.

Advanced filtering is useful for creating custom columns based on specific conditions in your data.

Share with credits: https://t.iss.one/sqlspecialist

Hope it helps :)

SQL LEARNING SERIES PART-15

Complete SQL Topics for Data Analysis
-> https://t.iss.one/sqlspecialist/523

Today, we will learn about Advanced Join Techniques:

Beyond basic joins, there are scenarios where advanced join techniques become useful.

#### Self-Join:
A self-join occurs when a table is joined with itself. It's useful when you want to compare rows within the same table.

SELECT e1.employee_id, e1.first_name, e1.manager_id, e2.first_name AS manager_name
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.employee_id;

This query retrieves employee details and the corresponding manager's name from the same "employees" table.

#### Cross Join:
A cross join returns the Cartesian product of two tables, meaning all possible combinations of rows.

SELECT * FROM table1
CROSS JOIN table2;

#### Example:

SELECT product_name, category_name
FROM products
CROSS JOIN categories;

This query returns all possible combinations of product names and category names.

Understanding these advanced join techniques expands your ability to work with diverse data relationships.

Share with credits: https://t.iss.one/sqlspecialist

Hope it helps :)

SQL LEARNING SERIES PART-16

Complete SQL Topics for Data Analysis
-> https://t.iss.one/sqlspecialist/523

Today, we will learn about Analytical Functions:

Analytical functions operate on a set of rows related to the current row and are often used for advanced analytics and reporting.

#### LAG() and LEAD():
Retrieve data from rows before or after the current row within a partition.

SELECT product_name, price, LAG(price) OVER (ORDER BY price) AS prev_price
FROM products;

#### FIRST_VALUE() and LAST_VALUE():
Get the first or last value within a partition.

SELECT department, employee_name, FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY hire_date) AS first_salary
FROM employees;

#### PERCENTILE_CONT():
Calculates a specified percentile within a group.

SELECT product_category, product_price, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY product_price) OVER (PARTITION BY product_category) AS median_price
FROM products;

Analytical functions enable advanced statistical analysis and reporting.

Share with credits: https://t.iss.one/sqlspecialist

Hope it helps :)

Complete Excel Topics for Data Analysts 😄👇

MS Excel Free Resources
-> https://t.iss.one/excel_data

1. Introduction to Excel:
- Basic spreadsheet navigation
- Understanding cells, rows, and columns

2. Data Entry and Formatting:
- Entering and formatting data
- Cell styles and formatting options

3. Formulas and Functions:
- Basic arithmetic functions
- SUM, AVERAGE, COUNT functions

4. Data Cleaning and Validation:
- Removing duplicates
- Data validation techniques

5. Sorting and Filtering:
- Sorting data
- Using filters for data analysis

6. Charts and Graphs:
- Creating basic charts (bar, line, pie)
- Customizing and formatting charts

7. PivotTables and PivotCharts:
- Creating PivotTables
- Analyzing data with PivotCharts

8. Advanced Formulas:
- VLOOKUP, HLOOKUP, INDEX-MATCH
- IF statements for conditional logic

9. Data Analysis with What-If Analysis:
- Goal Seek
- Scenario Manager and Data Tables

10. Advanced Charting Techniques:
- Combination charts
- Dynamic charts with named ranges

11. Power Query:
- Importing and transforming data with Power Query

12. Data Visualization with Power BI:
- Connecting Excel to Power BI
- Creating interactive dashboards

13. Macros and Automation:
- Recording and running macros
- Automation with VBA (Visual Basic for Applications)

14. Advanced Data Analysis:
- Regression analysis
- Data forecasting with Excel

15. Collaboration and Sharing:
- Excel sharing options
- Collaborative editing and comments

16. Excel Shortcuts and Productivity Tips:
- Time-saving keyboard shortcuts
- Productivity tips for efficient work

17. Data Import and Export:
- Importing and exporting data to/from Excel

18. Data Security and Protection:
- Password protection
- Worksheet and workbook security

19. Excel Add-Ins:
- Using and installing Excel add-ins for extended functionality

20. Mastering Excel for Data Analysis:
- Comprehensive project or case study integrating various Excel skills

Since Excel is another essential skill for data analysts, I have decided to teach each topic daily in this channel for free. Like this post if you want me to continue this Excel series 👍♥️

Share with credits: https://t.iss.one/sqlspecialist

Hope it helps :)

SQL LEARNING SERIES PART-17

Complete SQL Topics for Data Analysis
-> https://t.iss.one/sqlspecialist/523

Lets learn about how to work with Dates and Times in SQL today:

Manipulating date and time data is a common task in SQL, and various functions are available for these operations.

- CURRENT_DATE:

  SELECT CURRENT_DATE;
  

- DATEADD: DATEADD() function adds specific time/date interval to a date and then returns the date. 

  SELECT DATEADD(day, 7, order_date) AS future_date FROM orders;
  

- CURRENT_TIME:

  SELECT CURRENT_TIME;
  

- DATEDIFF: DATEDIFF() function calculates the difference between two dates

  SELECT DATEDIFF(hour, start_time, end_time) AS duration FROM events;
  

- FORMAT: Change the format of date field

  SELECT FORMAT(order_date, 'MM/dd/yyyy') AS formatted_date FROM orders;
  

Understanding these functions is crucial for performing time-based analysis in SQL.

Share with credits: https://t.iss.one/sqlspecialist

Hope it helps :)

SQL LEARNING SERIES PART-18

Complete SQL Topics for Data Analysis
-> https://t.iss.one/sqlspecialist/523

Let's learn about Performance Tuning today:

Optimizing the performance of your SQL queries is essential for efficient data retrieval. Several strategies can be employed:

#### Indexing:
- Create indexes on columns frequently used in WHERE clauses or JOIN conditions.

CREATE INDEX idx_column ON table_name (column);

#### Query Optimization:
- Use appropriate JOIN types based on the relationship between tables.
- Avoid SELECT *; instead, only select the columns you need.

#### LIMITing Results:
- When retrieving a large dataset, use LIMIT to retrieve a specified number of rows.

SELECT column1, column2 FROM table_name LIMIT 100;

#### EXPLAIN Statement:
- Use the EXPLAIN statement to analyze the execution plan of a query.

EXPLAIN SELECT column1, column2 FROM table_name WHERE condition;

#### Normalization and Denormalization:
- Choose an appropriate level of normalization for your database structure.

#### Consideration of Data Types:
- Choose the most suitable data types for your columns to minimize storage and enhance query performance.

CREATE TABLE example_table (
    column1 INT,
    column2 VARCHAR(50),
    column3 DATE
);

#### Regular Database Maintenance:
- Regularly analyze and defragment tables to improve performance.

ANALYZE TABLE table_name;
OPTIMIZE TABLE table_name;

#### Use of Stored Procedures:
- Stored procedures can be precompiled, leading to faster execution times.

CREATE PROCEDURE example_procedure AS
BEGIN
    -- SQL statements
END;

#### Database Caching:
- Utilize caching mechanisms to store frequently accessed data.

Optimizing queries and database design contributes significantly to overall system performance.

Share with credits: https://t.iss.one/sqlspecialist

Hope it helps :)

SQL LEARNING SERIES PART-19

Complete SQL Topics for Data Analysis
-> https://t.iss.one/sqlspecialist/523

Let's discuss about Security related topics in SQL today:
(Pretty-much advance concept but will be good if you know it)

Ensuring the security of your SQL database is paramount to protect sensitive information and prevent unauthorized access. Consider the following best practices:

#### SQL Injection Prevention:
- Use parameterized queries or prepared statements to protect against SQL injection attacks.

-- Example of a parameterized query
SELECT column1, column2 FROM table_name WHERE username = @username AND password = @password;

#### Role-Based Access Control:
- Assign specific roles to users with appropriate permissions.

GRANT SELECT, INSERT ON table_name TO role_name;

#### Encryption:
- Encrypt sensitive data, especially when storing passwords.

-- Example of storing hashed passwords
INSERT INTO users (username, password) VALUES ('user1', HASH('sha256', 'password'));

#### Auditing and Monitoring:
- Implement auditing to track database activity and identify potential security breaches.

-- Example of setting up database auditing
CREATE DATABASE AUDIT SPECIFICATION ExampleAuditSpec
FOR SERVER AUDIT ExampleAudit
ADD (SELECT, INSERT, UPDATE, DELETE ON DATABASE::example_db BY PUBLIC);

#### Regular Updates and Patching:
- Keep the database management system and software up to date to address security vulnerabilities.

Security is an ongoing process, and implementing these measures helps safeguard your database.

Share with credits: https://t.iss.one/sqlspecialist

Hope it helps :)

SQL LEARNING SERIES PART-20

Complete SQL Topics for Data Analysis
-> https://t.iss.one/sqlspecialist/523

Let's discuss on how to Handle NULL Values in SQL today:
(Pretty much important topic)

Dealing with NULL values is a common aspect of SQL, and understanding how to handle them is crucial for accurate data analysis.

#### IS NULL and IS NOT NULL:
- Use the IS NULL condition to filter rows with NULL values.

SELECT column1, column2 FROM table_name WHERE column3 IS NULL;

- Use the IS NOT NULL condition to filter rows without NULL values.

SELECT column1, column2 FROM table_name WHERE column3 IS NOT NULL;

#### COALESCE Function:
- Replace NULL values with a specified default value.

SELECT column1, COALESCE(column2, 'DefaultValue') AS modified_column FROM table_name;

#### NULLIF Function:
- Set a column to NULL if it matches a specified value.

SELECT column1, NULLIF(column2, 'UnwantedValue') AS modified_column FROM table_name;

Handling NULL values appropriately ensures accurate and reliable results in your queries.

Share with credits: https://t.iss.one/sqlspecialist

Hope it helps :)