Day 4: Updating and Deleting Data

Updating Multiple Columns

You can update more than one column at a time.

Example:

UPDATE Employees
SET Department = 'Finance', Salary = 60000
WHERE EmployeeID = 2;

Deleting All Rows

To delete all rows without removing the table structure, skip the WHERE clause.
Example:

DELETE FROM Employees;


Truncating Data

If you need to quickly remove all rows while resetting the auto-increment counters, use TRUNCATE.
Example:

TRUNCATE TABLE Employees;


Action Steps

1. Update a column value (e.g., increase all salaries by 10%).

UPDATE Employees
SET Salary = Salary * 1.1;

2. Delete a specific row based on a condition.


3. Optionally, practice truncating your table (use carefully!).


🔝 SQL 30 Days Challenge

Here you can find SQL Interview Resources👇
https://365datascience.pxf.io/APy44a

Like this post if you want me to continue this SQL series 👍♥️

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)

How to Build an Impressive Data Analysis Portfolio

As a data analyst, your portfolio is your personal brand. It showcases not only your technical skills but also your ability to solve real-world problems.

Having a strong, well-rounded portfolio can set you apart from other candidates and help you land your next job or freelance project.

Here's how to build a portfolio that will impress potential employers or clients.

1. Start with a Strong Introduction:
Before jumping into your projects, introduce yourself with a brief summary. Include your background, areas of expertise (e.g., Python, R, SQL), and any special achievements or certifications. This is your chance to give context to your portfolio and show your personality.

Tip: Make your introduction engaging and concise. Add a professional photo and link to your LinkedIn or personal website.


2. Showcase Real-World Projects:
The most powerful way to showcase your skills is through real-world projects. If you don’t have work experience yet, create your own projects using publicly available datasets (e.g., Kaggle, UCI Machine Learning Repository). These projects should highlight the full data analysis process—from data collection and cleaning to analysis and visualization.

Examples of project ideas:
- Analyzing customer data to identify purchasing trends.
- Predicting stock market trends based on historical data.
- Analyzing social media sentiment around a brand or event.


3. Focus on Impactful Data Visualizations:
Data visualization is a key part of data analysis, and it’s crucial that your portfolio highlights your ability to tell stories with data. Use tools like Tableau, Power BI, or Python (matplotlib, Seaborn) to create compelling visualizations that make complex data easy to understand.

Tips for great visuals:
- Use color wisely to highlight key insights.
- Avoid clutter; focus on clarity.
- Create interactive dashboards that allow users to explore the data.


4. Explain Your Methodology:
Employers and clients will want to know how you approached each project. For each project in your portfolio, explain the methodology you used, including:
- The problem or question you aimed to solve.
- The data sources you used.
- The tools and techniques you applied (e.g., statistical tests, machine learning models).
- The insights or results you discovered.

Make sure to document this in a clear, step-by-step manner, ideally with code snippets or screenshots.


5. Include Code and Jupyter Notebooks:
If possible, include links to your code or Jupyter Notebooks so potential employers or clients can see your technical expertise firsthand. Platforms like GitHub or GitLab are perfect for hosting your code. Make sure your code is well-commented and easy to follow.

Tip: Organize your projects in a structured way on GitHub, using denoscriptive README files for each project.


6. Feature a Blog or Case Studies:
If you enjoy writing, consider adding a blog or case study section to your portfolio. Writing about the data analysis process and the insights you’ve uncovered helps demonstrate your ability to communicate complex ideas in a digestible way. It also allows you to reflect on your projects and show your thought leadership in the field.

Blog post ideas:
- A breakdown of a data analysis project you’ve completed.
- Tips for aspiring data analysts.
- Reviews of tools and technologies you use regularly.

7. Continuously Update Your Portfolio:
Your portfolio is a living document. As you gain more experience and complete new projects, regularly update it to keep it fresh and relevant. Always add new skills, projects, and certifications to reflect your growth as a data analyst.

I have curated best 80+ top-notch Data Analytics Resources 👇👇
https://whatsapp.com/channel/0029VaGgzAk72WTmQFERKh02

Like this post for more content like this 👍♥️

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)

Day 5: Filtering Data with WHERE, LIKE, IN, and BETWEEN

1. Using WHERE for Filtering

The WHERE clause filters rows based on specific conditions.

Example:

SELECT * FROM Employees
WHERE Department = 'IT';

2. Using LIKE for Pattern Matching

Use LIKE with wildcards to match patterns:

%: Matches zero or more characters.

_: Matches a single character.



Examples:

-- Names starting with 'J'
SELECT * FROM Employees
WHERE Name LIKE 'J%';

-- Names ending with 'n'
SELECT * FROM Employees
WHERE Name LIKE '%n';

-- Names with 'a' as the second character
SELECT * FROM Employees
WHERE Name LIKE '_a%';

3. Using IN for Specific Values

Use IN to filter rows matching a list of values.


Example:

SELECT * FROM Employees
WHERE Department IN ('IT', 'Finance');

4. Using BETWEEN for Ranges

Use BETWEEN to filter data within a range (inclusive).


Examples:

-- Salaries between 40000 and 60000
SELECT * FROM Employees
WHERE Salary BETWEEN 40000 AND 60000;

-- Hire dates in 2023
SELECT * FROM Employees
WHERE HireDate BETWEEN '2023-01-01' AND '2023-12-31';

Combining Conditions with AND & OR

Example:

WHERE Department = 'IT' AND Salary > 50000;

```SELECT * FROM Employees
WHERE Department = 'HR' OR Salary < 40000;`

Action Steps

1. Retrieve rows using LIKE to match patterns in a column.


2. Filter rows using IN and BETWEEN.


3. Combine conditions with AND and OR.

🔝 SQL 30 Days Challenge

Here you can find SQL Interview Resources👇
https://365datascience.pxf.io/APy44a

Like this post if you want me to continue this SQL series 👍♥️

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)

Exploring the World of Data Analyst Freelancing: Tips and Opportunities

Freelancing as a data analyst offers incredible flexibility, independence, and the opportunity to work on a variety of exciting projects. In this post, we’ll explore tips and opportunities for entering the world of data analyst freelancing.

1. Understanding the Freelance Landscape:
The freelancing market for data analysts has expanded significantly as businesses increasingly rely on data-driven decisions. Companies—from startups to large enterprises—often prefer to hire freelancers for short-term projects rather than full-time employees to save on costs and gain specialized expertise.

Freelancing platforms to explore:
- Upwork: A leading platform for data analysts with a range of opportunities, from data cleaning to machine learning projects.
- Freelancer: Offers a wide range of data analytics projects.
- Fiverr: Great for offering specific data-related services such as data visualization or SQL queries.
- Toptal: Known for its high-quality freelancers, often requiring an application process to join.
- PeoplePerHour: Allows you to offer hourly rates for your services and find clients in need of specialized data analysis.

2. Build a Niche and Specialization:
While being a generalist can help you land a variety of projects, establishing a niche can help you stand out in a crowded market. Specializing in a particular aspect of data analysis—such as data visualization, statistical analysis, predictive modeling, or machine learning—can allow you to command higher rates and attract clients who need your specific expertise.

Some lucrative niches include:
- Machine learning and AI-based analytics: This is a rapidly growing field with high demand.
- Data visualization: Many companies seek data analysts who can turn complex datasets into interactive, insightful visuals using tools like Tableau, Power BI, or Python.
- Business Intelligence (BI): Providing actionable insights to companies using data from various sources.
- Predictive analytics: Helping businesses forecast trends using historical data.

3. Building an Impressive Portfolio:
A solid portfolio is one of the most important assets when starting your freelancing career. It showcases your skills, expertise, and the real-world results you can deliver. For data analysts, a portfolio should include a variety of projects that demonstrate your full range of skills—from data cleaning and analysis to data visualization.

Key elements for a freelance portfolio:
- Diverse projects: Include projects that cover different industries or types of analysis.
- Real-world case studies: Show how your analysis led to actionable insights or business improvements.
- Publicly available datasets: Utilize datasets from platforms like Kaggle to work on projects that can be shared freely.
- Clear project explanations: Explain your methodology and the tools you used.

4. Pricing Your Services:
Determining how much to charge as a freelancer can be tricky, especially when you're starting. Research what other freelancers are charging in your niche and adjust your rates accordingly. As you build your reputation and gain experience, you can increase your rates.

Freelancer pricing models to consider:
- Hourly rate: Common for smaller tasks or when working on short-term projects.
- Project-based pricing: Best for larger projects, where you can give clients a fixed price.
- Retainer model: A monthly fee for ongoing work. This can provide stable income.

Tip: Don’t undersell yourself! As you build your experience, don’t hesitate to raise your rates to reflect your growing skill set.

5. Finding Clients and Networking:
Finding clients is crucial to sustaining your freelance career. In addition to using freelancing platforms, actively network with potential clients through LinkedIn, online communities, and industry-specific forums.

Here you can find more freelancing tips: https://news.1rj.ru/str/freelancing_upwork

I have curated best 80+ top-notch Data Analytics Resources 👇👇
https://news.1rj.ru/str/DataSimplifier

Hope it helps :)

Day 6: Aggregating Data with Functions (SUM, AVG, MIN, MAX, COUNT)

1. SUM: Calculate the Total

The SUM() function adds up all numeric values in a column.

Example:

SELECT SUM(Salary) AS TotalSalary
FROM Employees;

2. AVG: Calculate the Average

The AVG() function calculates the average value of a numeric column.

Example:

SELECT AVG(Salary) AS AverageSalary
FROM Employees;

3. MIN and MAX: Find the Lowest and Highest Values

MIN() finds the smallest value.

MAX() finds the largest value.


Examples:

-- Lowest salary
SELECT MIN(Salary) AS LowestSalary
FROM Employees;

-- Highest salary
SELECT MAX(Salary) AS HighestSalary
FROM Employees;

4. COUNT: Count Rows

The COUNT() function counts the number of rows.

Examples:

-- Count all rows
SELECT COUNT(*) AS TotalEmployees
FROM Employees;

-- Count employees in a specific department
SELECT COUNT(*) AS TotalITEmployees
FROM Employees
WHERE Department = 'IT';

5. Combining Aggregates

You can use multiple aggregate functions in one query.

Example:

SELECT 
    COUNT(*) AS TotalEmployees,
    AVG(Salary) AS AverageSalary,
    MAX(Salary) AS HighestSalary
FROM Employees;

Action Steps

1. Find the total, average, minimum, and maximum salaries in your table.

2. Count rows based on specific conditions (e.g., employees in a department).

3. Combine multiple aggregates in a single query.


🔝 SQL 30 Days Challenge

Here you can find SQL Interview Resources👇
https://news.1rj.ru/str/mysqldata

Like this post if you want me to continue this SQL series 👍♥️

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)

Day 7: Grouping Data with GROUP BY and Filtering with HAVING

1. Using GROUP BY

The GROUP BY statement groups rows sharing a common value, often used with aggregate functions.


Syntax:

SELECT Column1, AggregateFunction(Column2)
FROM TableName
GROUP BY Column1;

Example:

-- Total salary per department
SELECT Department, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department;

2. Filtering Groups with HAVING

Use HAVING to filter groups created by GROUP BY.

Similar to WHERE, but for aggregated data.


Example:

-- Departments with total salary > 100000
SELECT Department, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department
HAVING SUM(Salary) > 100000;

3. Combining WHERE, GROUP BY, and HAVING

Use WHERE to filter rows before grouping.

Use HAVING to filter groups after aggregation.


Example:

-- Total salary of IT department with salary > 40000
SELECT Department, SUM(Salary) AS TotalSalary
FROM Employees
WHERE Salary > 40000
GROUP BY Department
HAVING SUM(Salary) > 100000;

4. Sorting Groups with ORDER BY

Sort grouped data using ORDER BY.


Example:

-- Sort by total salary (descending)
SELECT Department, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department
ORDER BY TotalSalary DESC;

Action Steps

1. Group data by a column (e.g., department) and use aggregate functions.

2. Filter groups using HAVING.

3. Sort grouped data with ORDER BY.

These are very useful SQL concepts, so I would recommend you solve problems related to GROUP BY & HAVING from leetcode or Stratascrach today itself. Start with easy ones and increase difficulty level as you proceed.

🔝 SQL 30 Days Challenge

Here you can find SQL Interview Resources👇
https://365datascience.pxf.io/APy44a

Like this post if you want me to continue this SQL series 👍♥️

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)

Why Learning Data Analysis is the Best Career Move in 2025 👇

Guys, let’s talk about why data analysis is the ultimate skill to master right now. Whether you’re switching careers or leveling up, here’s why this field is your golden ticket:


1. Insane Demand for Data Analysts

💼 Companies across industries are hiring data analysts like crazy!
💡 From startups to Fortune 500s, businesses need people who can turn data into actionable insights.

What this means for you:
📈 More job openings = more opportunities to land your dream role.


2. High Salaries, Even for Freshers

💰 Data analysts earn competitive salaries, even at entry-level.
🎯 With just 1-2 years of experience, you can double your earning potential.


3. Easy to Get Started

📊 Unlike some tech roles, you don’t need coding mastery or advanced math.
✨ Learn tools like Excel, SQL, and Power BI, and you’re good to go!


4. Cross-Industry Applications

🚀 Love e-commerce? Go for it.
🏦 Interested in banking? They need analysts too.
🎥 Passionate about entertainment? Data is shaping Hollywood!

Pro Tip: Pick an industry you love, and combine it with data skills.


5. Flexibility to Work Remotely

🌍 Data analysis roles often offer remote or hybrid setups, giving you work-life balance.
📈 With remote work on the rise, you can work for global companies from anywhere.


6. The Tools are User-Friendly

🛠 Tools like Power BI, Tableau, and Excel make it simple to visualize data.
💡 SQL and Python are beginner-friendly and widely used in the field.


7. Gateway to Advanced Roles

🚪 Start as a data analyst and transition into:

Data Scientist

Product Analyst

BI Specialist


The possibilities are endless once you have the basics down.


How to Start Your Data Analytics Journey ✅

1️⃣ Master the Basics:

Excel: Learn Pivot Tables, VLOOKUP, and data cleaning.

SQL: Practice writing queries and joining datasets.

Visualization: Get familiar with Tableau or Power BI.


2️⃣ Practice Real-World Problems:

Analyze sales trends.

Identify customer segments.

Evaluate campaign performance.


3️⃣ Build a Portfolio:

Use Kaggle datasets to showcase your skills.

Create dashboards and share them on LinkedIn.


4️⃣ Certifications:

Earn certifications from platforms like 365datascience, Coursera, or DataCamp to boost your resume.

I have curated top-notch Data Analytics Resources 👇👇
https://whatsapp.com/channel/0029VaGgzAk72WTmQFERKh02

Like this post if you want me to post more useful content ❤️

Hope it helps :)

The Untold Truth About Junior Data Analyst Interviews (From Someone Who’s Seen It All)

Guys, let’s cut through the noise. Most companies aren’t testing how many fancy tools you know—they’re testing how you think! Here’s what you really need to focus on:


SQL Interview Round

WHAT YOU THINK THEY WANT:
“Write the most complex SQL queries!”

WHAT THEY ACTUALLY TEST:

Can you clean messy data?

Do you handle NULL values logically?

How do you deal with duplicates?

Can you explain what you did, step-by-step?

Do you verify your results?


REALISTIC QUESTIONS YOU’LL FACE:
1️⃣ Find duplicate orders in a sales table.
2️⃣ Calculate monthly revenue for the past year.
3️⃣ Identify the top 10 customers by revenue.


Excel Interview Round

WHAT YOU THINK THEY WANT:
“Show off crazy Excel skills with macros and VBA.”

WHAT THEY REALLY WANT TO SEE:

Your ability to use VLOOKUP/XLOOKUP.

Comfort with Pivot Tables for summarization.

Your knack for creating basic formulas for data cleaning.

A logical approach to tackling Excel problems.


REALISTIC TASKS:
✅ Merge two datasets using VLOOKUP.
✅ Summarize sales trends in a Pivot Table.
✅ Clean up inconsistent text fields (hello, TRIM function).


Business Case Analysis

WHAT YOU THINK THEY WANT:
“Build a mind-blowing dashboard or deliver complex models.”

WHAT THEY ACTUALLY EVALUATE:

Can you break down the problem into manageable parts?

Do you ask smart, relevant questions?

Is your analysis focused on business outcomes?

How clearly can you present your findings?


What You'll Definitely Face

1. The “Data Mess” Scenario
They’ll hand you a messy dataset with:

Missing data, duplicates, and weird formats.

No clear instructions.


They watch:
👉 How you approach the problem.
👉 If you spot inconsistencies.
👉 The steps you take to clean and structure data.

2. The “Explain Your Analysis” Challenge
They’ll say:
“Walk us through what you did and why.”

They’re looking for:

Clarity in communication.

Your thought process.

The connection between your work and the business context.

How to Stand Out in Interviews

1. Nail the Basics

SQL: Focus on joins, filtering, grouping, and aggregating.

Excel: Get comfortable with lookups, pivots, and cleaning techniques.

Data Cleaning: Practice handling real-world messy datasets.


2. Understand the Business

Research their industry and common metrics (e.g., sales, churn rate).

Know basic KPIs they might ask about.

Prepare thoughtful, strategic questions.


3. Practice Real Scenarios
🔹 Analyze trends: Monthly revenue, churn analysis.
🔹 Segment customers: Who are your top spenders?
🔹 Evaluate campaigns: Which marketing effort drove the best ROI?


Reality Check: What Really Matters

🌟 How you think through a problem.
🌟 How you communicate your insights.
🌟 How you connect your work to business goals.

🚫 What doesn’t matter?

Writing overly complex SQL.

Knowing every Excel formula.

Advanced machine learning knowledge (for most junior roles).


Pro Tip: Stay calm, ask questions, and show you’re eager to solve problems. Your mindset is just as important as your technical skills!

I know it's a very long post but it'll be worth the efforts I took even if it helps a single person. Give it a like if you want me to continue posting such detailed posts.

I have curated best 80+ top-notch Data Analytics Resources 👇👇
https://whatsapp.com/channel/0029VaGgzAk72WTmQFERKh02

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)

Day 8: Working with Joins

1. INNER JOIN

Returns rows with matching values in both tables.


Syntax:

SELECT Table1.Column1, Table2.Column2
FROM Table1
INNER JOIN Table2
ON Table1.CommonColumn = Table2.CommonColumn;

Example:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
INNER JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

2. LEFT JOIN (or LEFT OUTER JOIN)

Returns all rows from the left table and matching rows from the right table. Non-matching rows in the right table return NULL.


Example:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

3. RIGHT JOIN (or RIGHT OUTER JOIN)

Returns all rows from the right table and matching rows from the left table. Non-matching rows in the left table return NULL.


Example:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
RIGHT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

4. FULL JOIN (or FULL OUTER JOIN)

Returns all rows when there is a match in either table. Rows without matches return NULL.


Example:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
FULL JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

5. SELF JOIN

A SELF JOIN is a table joined with itself, useful for hierarchical or relationship data.


Syntax:

SELECT A.Column1, B.Column2
FROM TableName A
INNER JOIN TableName B
ON A.CommonColumn = B.CommonColumn;

Example:

-- Find employees with the same manager
SELECT A.Name AS Employee, B.Name AS Manager
FROM Employees A
INNER JOIN Employees B
ON A.ManagerID = B.EmployeeID;

6. CROSS JOIN

Combines each row from the first table with all rows from the second table, creating a Cartesian product.


Syntax:

SELECT *
FROM Table1
CROSS JOIN Table2;

Example:

SELECT Employees.Name, Projects.ProjectName
FROM Employees
CROSS JOIN Projects;

Combining Joins with Filters

Use WHERE or ON to refine join results.

Example:

-- Employees without departments
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID
WHERE Departments.DepartmentName IS NULL;

Action Steps

1. Create two related tables (e.g., Employees and Departments).


2. Practice each join type (INNER, LEFT, RIGHT, FULL).


3. Write queries combining joins with filtering conditions.

🔝 SQL 30 Days Challenge

Here you can find SQL Interview Resources👇
https://365datascience.pxf.io/APy44a

Like this post if you want me to continue this SQL series 👍♥️

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)

Day 9: Subqueries and Common Table Expressions (CTEs)

1. Subqueries

A subquery is a query nested inside another query. It can be used in SELECT, FROM, or WHERE clauses.

Types of Subqueries:

1. Single-row Subquery: Returns one row.


2. Multi-row Subquery: Returns multiple rows.


3. Correlated Subquery: Depends on the outer query.


Example 1: Single-row Subquery

-- Find employees earning more than the average salary
SELECT Name, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

Example 2: Multi-row Subquery

-- Find employees in specific departments
SELECT Name
FROM Employees
WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE Location = 'NY');

Example 3: Correlated Subquery

-- Find employees with the highest salary in each department
SELECT Name, Salary
FROM Employees E1
WHERE Salary = (
    SELECT MAX(Salary)
    FROM Employees E2
    WHERE E1.DepartmentID = E2.DepartmentID
);

2. Common Table Expressions (CTEs)

CTEs provide a way to define temporary result sets that can be reused in the main query.

Syntax:

WITH CTEName AS (
SELECT Column1, Column2
FROM TableName
WHERE Condition
)
SELECT *
FROM CTEName;

Example 1: Simple CTE

-- Get total salary per department
WITH DepartmentSalary AS (
    SELECT DepartmentID, SUM(Salary) AS TotalSalary
    FROM Employees
    GROUP BY DepartmentID
)
SELECT *
FROM DepartmentSalary;

Example 2: Recursive CTE Used for hierarchical data (e.g., organizational structures).

WITH RecursiveCTE AS (
    -- Anchor member
    SELECT EmployeeID, ManagerID, Name
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    -- Recursive member
    SELECT E.EmployeeID, E.ManagerID, E.Name
    FROM Employees E
    INNER JOIN RecursiveCTE R
    ON E.ManagerID = R.EmployeeID
)
SELECT *
FROM RecursiveCTE;

Action Steps

1. Practice writing subqueries in WHERE, SELECT, and FROM.

2. Use a CTE to simplify complex queries.

3. Create a recursive CTE for hierarchical data if applicable.

🔝 SQL 30 Days Challenge

Here you can find SQL Interview Resources👇
https://365datascience.pxf.io/APy44a

Like this post if you want me to continue this SQL series 👍♥️

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)

If you have time to learn...!

You have time to grow...!

Start from Scratch !!!!

You have time to become a Data Analyst...!!


➜ learn Excel
➜ learn SQL
➜ learn either Power BI or Tableau
➜ learn what the heck ATS is and how to get around it
➜ learn to be ready for any interview question
➜ to build projects for a portfolio
➜ to put invest the time for your future
➜ to fail and pick yourself back up


And you don't need to do it all at once!

I have curated best 80+ top-notch Data Analytics Resources 👇👇
https://news.1rj.ru/str/DataSimplifier

Hope it helps :)

Day 10: Advanced SQL Functions and Window Functions

1. Advanced SQL Functions

These functions enhance data manipulation and analysis.

1. String Functions:

UPPER(), LOWER(): Change case.

CONCAT(): Combine strings.

SUBSTRING(): Extract part of a string.

TRIM(): Remove leading/trailing spaces.


Example:

SELECT CONCAT(FirstName, ' ', LastName) AS FullName
FROM Employees;


2. Date Functions:

NOW(): Current date and time.

DATEADD(): Add intervals to a date.

DATEDIFF(): Difference between dates.


Example:

SELECT DATEDIFF(DAY, HireDate, GETDATE()) AS DaysWorked
FROM Employees;


3. Mathematical Functions:

ROUND(), CEIL(), FLOOR(), ABS(): Perform numerical operations.


Example:

SELECT ROUND(Salary, 2) AS RoundedSalary
FROM Employees;


2. Window Functions

Window functions perform calculations across a set of rows related to the current row, without collapsing rows like aggregate functions.

1. ROW_NUMBER(): Assigns a unique number to each row in a result set.

SELECT Name, Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
FROM Employees;


2. RANK(): Assigns a rank to rows, with gaps for ties.

SELECT Name, Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM Employees;


3. DENSE_RANK(): Similar to RANK() but without gaps.

SELECT Name, Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank
FROM Employees;


4. NTILE(): Divides rows into a specified number of groups.

SELECT Name, Salary, NTILE(4) OVER (ORDER BY Salary DESC) AS Quartile
FROM Employees;


5. LEAD() and LAG(): Access data from the next or previous row.

SELECT Name, Salary, LEAD(Salary) OVER (ORDER BY Salary) AS NextSalary
FROM Employees;


6. Aggregate with PARTITION BY:

Use PARTITION BY to calculate aggregates within subsets of data.


Example:

SELECT DepartmentID, Name, Salary, SUM(Salary) OVER (PARTITION BY DepartmentID) AS DepartmentTotal
FROM Employees;


Action Steps

1. Practice string, date, and math functions on your dataset.

2. Implement ROW_NUMBER(), RANK(), and PARTITION BY to analyze data.

3. Use LEAD() and LAG() to compare current rows with previous/next rows.

🔝 SQL 30 Days Challenge

Here you can find SQL Interview Resources👇
https://365datascience.pxf.io/APy44a

Like this post if you want me to continue this SQL series 👍♥️

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)

Day 11: Indexes and Performance Optimization

1. What are Indexes?

Indexes improve query performance by allowing the database to find rows more quickly. They act as a data structure that provides a faster way to look up data.

2. Types of Indexes

1. Clustered Index:

Stores data physically in order based on indexed column(s).

Only one per table.

Example: Primary key.


Syntax:

CREATE CLUSTERED INDEX idx_name ON TableName(ColumnName);


2. Non-Clustered Index:

Creates a separate structure for the index while data remains unsorted.

Multiple non-clustered indexes can exist on a table.


Syntax:

CREATE NONCLUSTERED INDEX idx_name ON TableName(ColumnName);


3. Unique Index:

Ensures all values in the indexed column(s) are unique.

Automatically created for PRIMARY KEY and UNIQUE constraints.


Syntax:

CREATE UNIQUE INDEX idx_name ON TableName(ColumnName);


4. Composite Index:

Indexes multiple columns together.


Syntax:

CREATE INDEX idx_name ON TableName(Column1, Column2);


3. Best Practices for Indexing

1. Index columns frequently used in WHERE, JOIN, or ORDER BY.


2. Avoid over-indexing (too many indexes can slow down write operations).


3. Use composite indexes for multi-column searches.


4. Regularly update statistics for accurate query plans.


4. Query Performance Optimization

1. EXPLAIN/Execution Plan:

Use it to analyze query performance and identify bottlenecks.


Syntax:

EXPLAIN SELECT * FROM TableName WHERE Column = 'Value';


2. Avoid SELECT :

Only retrieve required columns to minimize data retrieval.


Example:

SELECT Name, Salary FROM Employees WHERE DepartmentID = 1;


3. Use Joins Efficiently:

Prefer INNER JOIN for better performance if applicable.



4. Optimize WHERE Clauses:

Use indexed columns in WHERE.


Example:

SELECT * FROM Employees WHERE EmployeeID = 101;


5. Avoid Functions in WHERE Clauses:

Functions prevent the use of indexes.


Inefficient:

SELECT * FROM Employees WHERE YEAR(HireDate) = 2023;

Efficient:

SELECT * FROM Employees WHERE HireDate >= '2023-01-01' AND HireDate < '2024-01-01';


6. Use LIMIT/OFFSET:

Reduce the result set size for better performance.

Example:

SELECT * FROM Employees LIMIT 10 OFFSET 0;


5. Dropping Unused Indexes

Too many indexes can slow down write operations. Drop unused ones.

Syntax:

DROP INDEX idx_name ON TableName;

Action Steps

1. Create clustered, non-clustered, and composite indexes on a test table.

2. Use EXPLAIN or execution plans to analyze slow queries.

3. Optimize queries based on the best practices above.

🔝 SQL 30 Days Challenge

Here you can find SQL Interview Resources👇
https://365datascience.pxf.io/APy44a

Like this post if you want me to continue this SQL series 👍♥️

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)

Day 12: Transactions and Error Handling

1. What are Transactions?

A transaction is a sequence of SQL operations performed as a single logical unit of work. Transactions ensure data consistency and integrity.

2. ACID Properties of Transactions

1. Atomicity: All operations within the transaction succeed or none do.


2. Consistency: The database remains consistent before and after the transaction.


3. Isolation: Transactions do not interfere with each other.


4. Durability: Once committed, the transaction’s changes are permanent.


3. Transaction Control Statements

1. BEGIN TRANSACTION: Starts a transaction.

BEGIN TRANSACTION;


2. COMMIT: Saves all changes made during the transaction.

COMMIT;


3. ROLLBACK: Undoes all changes made during the transaction.

ROLLBACK;


4. SAVEPOINT: Sets a point within a transaction to roll back to.

SAVEPOINT SavePointName;


5. RELEASE SAVEPOINT: Deletes a savepoint.

RELEASE SAVEPOINT SavePointName;

4. Example of a Transaction

BEGIN TRANSACTION;

-- Deduct from sender's account
UPDATE Accounts
SET Balance = Balance - 1000
WHERE AccountID = 1;

-- Add to receiver's account
UPDATE Accounts
SET Balance = Balance + 1000
WHERE AccountID = 2;

-- Check for errors
IF @@ERROR <> 0
BEGIN
    ROLLBACK;
    PRINT 'Transaction Failed';
END
ELSE
BEGIN
    COMMIT;
    PRINT 'Transaction Successful';
END;

5. Error Handling

1. TRY...CATCH: Handle errors and ensure proper cleanup in case of failure.

Syntax:

BEGIN TRY
    -- SQL statements
END TRY
BEGIN CATCH
    -- Error handling code
END CATCH

2. Example with TRY...CATCH:

BEGIN TRY
    BEGIN TRANSACTION;

    -- Insert operation
    INSERT INTO Employees (Name, Salary) VALUES ('John', 5000);

    -- Error-prone operation
    INSERT INTO Employees (Name, Salary) VALUES (NULL, NULL);

    COMMIT;
END TRY
BEGIN CATCH
    ROLLBACK;
    PRINT 'Error occurred: ' + ERROR_MESSAGE();
END CATCH;

3. @@ERROR:

A system function that returns the error code of the last T-SQL statement.


6. Isolation Levels

Control how transactions interact with each other.

1. Read Uncommitted: Allows dirty reads.

2. Read Committed: Prevents dirty reads.

3. Repeatable Read: Prevents non-repeatable reads.

4. Serializable: Prevents dirty, non-repeatable, and phantom reads.

Syntax:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
-- SQL operations
COMMIT;

Action Steps

1. Write a transaction with BEGIN TRANSACTION, COMMIT, and ROLLBACK.


2. Implement error handling using TRY...CATCH.


3. Experiment with different isolation levels in test scenarios.

🔝 SQL 30 Days Challenge

Here you can find SQL Interview Resources👇
https://365datascience.pxf.io/APy44a

Like this post if you want me to continue this SQL series 👍♥️

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)

Top Tableau Features Every Data Analyst Should Know

Data Connection:

Connect to Multiple Data Sources: Blend data from files, databases, and cloud platforms.

Live vs. Extract: Choose between real-time data updates or working with a snapshot.


Visualizations:

Drag-and-Drop Interface: Quickly create bar charts, line graphs, and heat maps.

Dual-Axis Charts: Compare two measures with separate axes.

Trend Lines: Add statistical trend lines to visuals.


Filters and Parameters:

Interactive Filters: Allow users to filter data dynamically.

Parameters: Let users input values to customize analysis (e.g., thresholds).


Calculated Fields:

Custom Calculations: Create metrics like profit ratios or rolling averages.

Logical Functions: Use IF, CASE, and other functions for custom logic.


Dashboards:

Combine Views: Merge multiple sheets into a single dashboard.

Actions: Add interactivity like filters or URL actions.


Geospatial Analysis:

Map Visualizations: Plot data points on a map using lat-long or names.

Filled Maps: Visualize regions (e.g., countries, states) with color gradients.


Sharing and Publishing:

Tableau Public: Publish visuals for public access.

Tableau Server/Online: Share dashboards securely within an organization.

Best Resources to learn Tableau: https://whatsapp.com/channel/0029VasYW1V5kg6z4EHOHG1t

Like this post if you want me to continue this Tableau series 👍♥️

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)

Day 13: Views, Stored Procedures, and Triggers

1. Views

A view is a virtual table based on a SQL query. It simplifies complex queries and improves data abstraction.

1. Creating a View:

CREATE VIEW ViewName AS
SELECT Column1, Column2
FROM TableName
WHERE Condition;

2. Using a View:

SELECT * FROM ViewName;


3. Updating a View:

Views can often be updated if based on a single table and meet certain criteria.


Example:

UPDATE ViewName
SET Column1 = 'NewValue'
WHERE Condition;


4. Dropping a View:

DROP VIEW ViewName;


2. Stored Procedures

A stored procedure is a set of SQL statements stored in the database and executed as a single unit.

1. Creating a Stored Procedure:

CREATE PROCEDURE ProcedureName
AS
BEGIN
    SELECT * FROM TableName WHERE Condition;
END;

2. Executing a Stored Procedure:

EXEC ProcedureName;


3. Stored Procedure with Parameters:

CREATE PROCEDURE GetEmployeeDetails @EmployeeID INT
AS
BEGIN
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;

Execution:

EXEC GetEmployeeDetails @EmployeeID = 1;

4. Dropping a Stored Procedure:

DROP PROCEDURE ProcedureName;


3. Triggers

Triggers are SQL code automatically executed in response to specific events on a table.

1. Types of Triggers:

AFTER Trigger: Executes after an INSERT, UPDATE, or DELETE operation.

INSTEAD OF Trigger: Replaces the triggering action.



2. Creating an AFTER Trigger:

CREATE TRIGGER TriggerName
ON TableName
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    PRINT 'Trigger executed';
END;

3. Example: Logging Changes:

CREATE TRIGGER LogChanges
ON Employees
AFTER UPDATE
AS
BEGIN
    INSERT INTO AuditLog (EmployeeID, ChangeTime)
    SELECT EmployeeID, GETDATE()
    FROM Inserted;
END;

4. Dropping a Trigger:

DROP TRIGGER TriggerName;

4. Use Cases

1. Views: Simplify reporting or provide restricted access to data.


2. Stored Procedures: Automate repetitive tasks or enforce business logic.


3. Triggers: Automatically maintain audit trails or enforce rules.

Action Steps

1. Create a view to simplify a complex query.

2. Write a stored procedure to retrieve specific data based on a parameter.

3. Create a trigger to log changes in a table.

🔝 SQL 30 Days Challenge

Here you can find SQL Interview Resources👇
https://365datascience.pxf.io/APy44a

Like this post if you want me to continue this SQL series 👍♥️

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)

Cloud-Based Data Analysis Tools

Google BigQuery:

Purpose: Query and analyze large datasets.

Strengths: Scalable, serverless, integrates with Google Cloud.


Amazon Redshift:

Purpose: Data warehousing and analytics.

Strengths: Handles massive datasets with fast query speeds.


Microsoft Azure Synapse Analytics:

Purpose: Integrates big data and data warehousing.

Strengths: Seamless with Power BI and other Azure services.


Snowflake:

Purpose: Cloud data platform for storage and computation.

Strengths: Elastic scalability, easy-to-use SQL interface.


Databricks:

Purpose: Unified analytics for big data and machine learning.

Strengths: Ideal for collaboration and advanced ML workloads.


Tableau Online:

Purpose: Cloud-hosted analytics for sharing visualizations.

Strengths: Real-time dashboards and collaboration.

I have curated best 80+ top-notch Data Analytics Resources 👇👇
t.me/datasimplifier

Like this post for more content like this 👍♥️

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)

Day 14: Common Table Expressions (CTEs) and Recursive Queries

1. Common Table Expressions (CTEs)

A Common Table Expression (CTE) is a temporary result set that simplifies complex queries. It exists only during the execution of the query.


2. Syntax of a CTE

WITH CTE_Name (Column1, Column2, ...)
AS
(
    SELECT Column1, Column2
    FROM TableName
    WHERE Condition
)
SELECT * FROM CTE_Name;

3. Example of a CTE

Simple CTE:

WITH EmployeeCTE AS
(
    SELECT EmployeeID, Name, Salary
    FROM Employees
    WHERE Salary > 5000
)
SELECT * FROM EmployeeCTE;

4. Recursive CTE

A recursive CTE refers to itself and is commonly used to query hierarchical data like organizational charts or folder structures.

Syntax:

WITH RecursiveCTE (Column1, Column2, ...)
AS
(
    -- Anchor member
    SELECT Column1, Column2
    FROM TableName
    WHERE Condition

    UNION ALL

    -- Recursive member
    SELECT Column1, Column2
    FROM TableName
    INNER JOIN RecursiveCTE
    ON TableName.ParentID = RecursiveCTE.ID
)
SELECT * FROM RecursiveCTE;

5. Example of a Recursive CTE

Hierarchy of Employees:

WITH EmployeeHierarchy AS
(
    -- Anchor member
    SELECT EmployeeID, ManagerID, Name
    FROM Employees
    WHERE ManagerID IS NULL

    UNION ALL

    -- Recursive member
    SELECT e.EmployeeID, e.ManagerID, e.Name
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh
    ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy;

6. Key Points to Remember

1. Use CTEs to break down complex queries for better readability.


2. Recursive CTEs must include:

An anchor member (base case).

A recursive member with a termination condition (e.g., ManagerID IS NULL).



3. Recursive queries must include a UNION ALL operator.


7. Benefits of CTEs

1. Improved query readability.


2. Simplifies hierarchical or recursive queries.


3. Can be referenced multiple times within the same query.


Action Steps

1. Write a simple CTE to filter data from a table.


2. Create a recursive CTE to display a hierarchical structure like an organization chart.


3. Test your recursive CTE with a termination condition to avoid infinite loops.

🔝 SQL 30 Days Challenge

Here you can find SQL Interview Resources👇
https://365datascience.pxf.io/APy44a

Like this post if you want me to continue this SQL series 👍♥️

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)

Many people pay too much to learn Python, but my mission is to break down barriers. I have shared complete learning series to learn Python from scratch.

Here are the links to the Python series

Complete Python Topics for Data Analyst: https://news.1rj.ru/str/sqlspecialist/548

Part-1: https://news.1rj.ru/str/sqlspecialist/562

Part-2: https://news.1rj.ru/str/sqlspecialist/564

Part-3: https://news.1rj.ru/str/sqlspecialist/565

Part-4: https://news.1rj.ru/str/sqlspecialist/566

Part-5: https://news.1rj.ru/str/sqlspecialist/568

Part-6: https://news.1rj.ru/str/sqlspecialist/570

Part-7: https://news.1rj.ru/str/sqlspecialist/571

Part-8: https://news.1rj.ru/str/sqlspecialist/572

Part-9: https://news.1rj.ru/str/sqlspecialist/578

Part-10: https://news.1rj.ru/str/sqlspecialist/577

Part-11: https://news.1rj.ru/str/sqlspecialist/578

Part-12:
https://news.1rj.ru/str/sqlspecialist/581

Part-13: https://news.1rj.ru/str/sqlspecialist/583

Part-14: https://news.1rj.ru/str/sqlspecialist/584

Part-15: https://news.1rj.ru/str/sqlspecialist/585

I saw a lot of big influencers copy pasting my content after removing the credits. It's absolutely fine for me as more people are getting free education because of my content.

But I will really appreciate if you share credits for the time and efforts I put in to create such valuable content. I hope you can understand.

Complete SQL Topics for Data Analysts: https://news.1rj.ru/str/sqlspecialist/523

Complete Power BI Topics for Data Analysts: https://news.1rj.ru/str/sqlspecialist/588

Thanks to all who support our channel and share the content with proper credits. You guys are really amazing.

Hope it helps :)

Day 15: Window Functions

1. What are Window Functions?

Window functions perform calculations across a set of rows related to the current row, helping analyze data without grouping.

2. Types of Window Functions

Aggregate Functions: SUM(), AVG(), COUNT(), MIN(), MAX().

Ranking Functions: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE().

Value Functions: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE().

3. Syntax

FunctionName() OVER ( PARTITION BY ColumnName ORDER BY ColumnName )

4. Examples

a) Aggregate with PARTITION

Calculate total salary for each department:

SELECT EmployeeID, DepartmentID, Salary, SUM(Salary) OVER (PARTITION BY DepartmentID) AS TotalSalary FROM Employees;

In Department 101, if employees earn 5000, 6000, and 4000, the total salary for all rows is 15000.

In Department 102, with only one employee earning 7000, the total salary is 7000.

b) ROW_NUMBER

Assign a unique number to each row based on salary:

SELECT EmployeeID, Name, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNumber FROM Employees;

If salaries are 7000, 6000, 5000, employees are ranked as 1, 2, and 3 respectively.

c) RANK and DENSE_RANK
Rank employees based on salary:

SELECT EmployeeID, Name, Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank, DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank FROM Employees;

With salaries 7000, 6000, 6000:

RANK: 1, 2, 2 (skips 3 for ties).

DENSE_RANK: 1, 2, 2 (does not skip numbers for ties).

d) LAG and LEAD

Fetch the previous and next salaries in a sequence:

SELECT EmployeeID, Name, Salary, LAG(Salary) OVER (ORDER BY Salary) AS PreviousSalary, LEAD(Salary) OVER (ORDER BY Salary) AS NextSalary FROM Employees;

For salaries 4000, 5000, 6000:

PreviousSalary: NULL, 4000, 5000.

NextSalary: 5000, 6000, NULL.

5. Key Takeaways

PARTITION BY groups data into subsets for calculations.
ORDER BY defines the sequence for calculations.
Use window functions to analyze data efficiently without grouping rows.

Action Steps

- Write a query using SUM() and PARTITION BY to calculate group totals.

- Use ROW_NUMBER to rank rows based on any column.

- Experiment with LAG and LEAD to fetch previous and next row values.

🔝 SQL 30 Days Challenge

Here you can find SQL Interview Resources👇
https://news.1rj.ru/str/sqlanalyst

Like this post if you want me to continue this SQL series 👍♥️

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)