Quick SQL functions cheat sheet for beginners ✍

Aggregate Functions

COUNT(*): Counts rows.

SUM(column): Total sum.

AVG(column): Average value.

MAX(column): Maximum value.

MIN(column): Minimum value.

String Functions

CONCAT(a, b, …): Concatenates strings.

SUBSTRING(s, start, length): Extracts part of a string.

UPPER(s) / LOWER(s): Converts string case.

TRIM(s): Removes leading/trailing spaces.

Date Time Functions

CURRENT_DATE / CURRENT_TIME / CURRENT_TIMESTAMP: Current date/time.

EXTRACT(unit FROM date): Retrieves a date part (e.g., year, month).

DATE_ADD(date, INTERVAL n unit): Adds an interval to a date.

Numeric Functions

ROUND(num, decimals): Rounds to a specified decimal.

CEIL(num) / FLOOR(num): Rounds up/down.

ABS(num): Absolute value.

MOD(a, b): Returns the remainder.

Control Flow Functions

CASE: Conditional logic.

COALESCE(val1, val2, …): Returns the first non-null value.

Like for more free Cheatsheets ❤️

Share with credits: https://t.me/sqlspecialist

Hope it helps :)

Don't Confuse to learn Python.

Learn This Concept to be proficient in Python.

𝗕𝗮𝘀𝗶𝗰𝘀 𝗼𝗳 𝗣𝘆𝘁𝗵𝗼𝗻:
- Python Syntax
- Data Types
- Variables
- Operators
- Control Structures:
if-elif-else
Loops
Break and Continue
try-except block
- Functions
- Modules and Packages

𝗢𝗯𝗷𝗲𝗰𝘁-𝗢𝗿𝗶𝗲𝗻𝘁𝗲𝗱 𝗣𝗿𝗼𝗴𝗿𝗮𝗺𝗺𝗶𝗻𝗴 𝗶𝗻 𝗣𝘆𝘁𝗵𝗼𝗻:
- Classes and Objects
- Inheritance
- Polymorphism
- Encapsulation
- Abstraction

𝗣𝘆𝘁𝗵𝗼𝗻 𝗟𝗶𝗯𝗿𝗮𝗿𝗶𝗲𝘀:
- Pandas
- Numpy

𝗣𝗮𝗻𝗱𝗮𝘀:
- What is Pandas?
- Installing Pandas
- Importing Pandas
- Pandas Data Structures (Series, DataFrame, Index)

𝗪𝗼𝗿𝗸𝗶𝗻𝗴 𝘄𝗶𝘁𝗵 𝗗𝗮𝘁𝗮𝗙𝗿𝗮𝗺𝗲𝘀:
- Creating DataFrames
- Accessing Data in DataFrames
- Filtering and Selecting Data
- Adding and Removing Columns
- Merging and Joining DataFrames
- Grouping and Aggregating Data
- Pivot Tables

𝗗𝗮𝘁𝗮 𝗖𝗹𝗲𝗮𝗻𝗶𝗻𝗴 𝗮𝗻𝗱 𝗣𝗿𝗲𝗽𝗮𝗿𝗮𝘁𝗶𝗼𝗻:
- Handling Missing Values
- Handling Duplicates
- Data Formatting
- Data Transformation
- Data Normalization

𝗔𝗱𝘃𝗮𝗻𝗰𝗲𝗱 𝗧𝗼𝗽𝗶𝗰𝘀:
- Handling Large Datasets with Dask
- Handling Categorical Data with Pandas
- Handling Text Data with Pandas
- Using Pandas with Scikit-learn
- Performance Optimization with Pandas

𝗗𝗮𝘁𝗮 𝗦𝘁𝗿𝘂𝗰𝘁𝘂𝗿𝗲𝘀 𝗶𝗻 𝗣𝘆𝘁𝗵𝗼𝗻:
- Lists
- Tuples
- Dictionaries
- Sets

𝗙𝗶𝗹𝗲 𝗛𝗮𝗻𝗱𝗹𝗶𝗻𝗴 𝗶𝗻 𝗣𝘆𝘁𝗵𝗼𝗻:
- Reading and Writing Text Files
- Reading and Writing Binary Files
- Working with CSV Files
- Working with JSON Files

𝗡𝘂𝗺𝗽𝘆:
- What is NumPy?
- Installing NumPy
- Importing NumPy
- NumPy Arrays

𝗡𝘂𝗺𝗣𝘆 𝗔𝗿𝗿𝗮𝘆 𝗢𝗽𝗲𝗿𝗮𝘁𝗶𝗼𝗻𝘀:
- Creating Arrays
- Accessing Array Elements
- Slicing and Indexing
- Reshaping Arrays
- Combining Arrays
- Splitting Arrays
- Arithmetic Operations
- Broadcasting

𝗪𝗼𝗿𝗸𝗶𝗻𝗴 𝘄𝗶𝘁𝗵 𝗗𝗮𝘁𝗮 𝗶𝗻 𝗡𝘂𝗺𝗣𝘆:
- Reading and Writing Data with NumPy
- Filtering and Sorting Data
- Data Manipulation with NumPy
- Interpolation
- Fourier Transforms
- Window Functions

𝗣𝗲𝗿𝗳𝗼𝗿𝗺𝗮𝗻𝗰𝗲 𝗢𝗽𝘁𝗶𝗺𝗶𝘇𝗮𝘁𝗶𝗼𝗻 𝘄𝗶𝘁𝗵 𝗡𝘂𝗺𝗣𝘆:
- Vectorization
- Memory Management
- Multithreading and Multiprocessing
- Parallel Computing

Like this post if you need more content like this 👍❤️

Quick Excel Functions Cheat Sheet for Beginners 📊✍️

Excel offers powerful functions for data analysis, calculations, and automation—perfect for beginners handling spreadsheets.

▎Aggregation Functions

• SUM(range): Totals all values in a range, e.g., SUM(A1:A10).
• AVERAGE(range): Computes the mean of numbers, ignoring blanks.
• COUNT(range): Counts cells with numbers.
• COUNTA(range): Counts non-empty cells.
• MAX(range): Finds the highest value.
• MIN(range): Finds the lowest value.

▎Lookup Functions

• VLOOKUP(value, table, col_index, [range_lookup]): Searches vertically for a value and returns from specified column.
• HLOOKUP(value, table, row_index, [range_lookup]): Searches horizontally.
• INDEX(range, row_num, [column_num]): Returns value at specific position.
• MATCH(lookup_value, range, [match_type]): Finds position of a value.

▎Logical Functions

• IF(condition, true_value, false_value): Executes based on condition, e.g., IF(A1>10, "High", "Low").
• AND(condition1, condition2): True if all conditions met.
• OR(condition1, condition2): True if any condition met.
• NOT(logical): Reverses TRUE/FALSE.

▎Text Functions

• CONCATENATE(text1, text2): Joins text strings (or use operator).
• LEFT(text, num_chars): Extracts from start.
• RIGHT(text, num_chars): Extracts from end.
• LEN(text): Counts characters.
• TRIM(text): Removes extra spaces.

▎Date Time Functions

• TODAY(): Current date.
• NOW(): Current date and time.
• YEAR(date): Extracts year.
• MONTH(date): Extracts month.
• DATEDIF(start_date, end_date, unit): Calculates interval (Y/M/D).

▎Math Stats Functions

• ROUND(number, num_digits): Rounds to digits.
• SUMIF(range, criteria, sum_range): Sums based on condition.
• COUNTIF(range, criteria): Counts based on condition.
• ABS(number): Absolute value.

Excel Resources: https://whatsapp.com/channel/0029VaifY548qIzv0u1AHz3i

Double Tap ♥️ For More

⚙️ Data Analytics Roadmap

📂 Excel/Google Sheets (VLOOKUP, Pivot Tables, Charts)
∟📂 SQL (SELECT, JOINs, GROUP BY, Window Functions)
∟📂 Python/R Basics (Pandas, Data Cleaning)
∟📂 Statistics (Descriptive, Inferential, Correlation)
∟📂 Data Visualization (Tableau, Power BI, Matplotlib)
∟📂 ETL Processes (Extract, Transform, Load)
∟📂 Dashboard Design (KPIs, Storytelling)
∟📂 Business Intelligence Tools (Looker, Metabase)
∟📂 Data Quality & Governance
∟📂 A/B Testing & Experimentation
∟📂 Advanced Analytics (Cohort Analysis, Funnel Analysis)
∟📂 Big Data Basics (Spark, Airflow)
∟📂 Communication (Reports, Presentations)
∟📂 Projects (Sales Dashboard, Customer Segmentation)
∟✅ Apply for Data Analyst / BI Analyst Roles

💬 Tap ❤️ for more!

Quick Python Cheat Sheet for Beginners 🐍✍️

Python is widely used for data analysis, automation, and AI—perfect for beginners starting their coding journey.

Aggregation Functions 📊

• sum(list) → Adds all values
👉 sum([1,2,3]) = 6
• len(list) → Counts total elements
👉 len([1,2,3]) = 3
• max(list) → Highest value
👉 max([4,7,2]) = 7
• min(list) → Lowest value
👉 min([4,7,2]) = 2
• sum(list)/len(list) → Average
👉 sum([10,20])/2 = 15

Lookup / Searching 🔍

• in → Check existence
👉 5 in [1,2,5] = True
• list.index(value) → Position of value
👉 [10,20,30].index(20) = 1
• Dictionary lookup
👉 data = {"name": "John", "age": 25} data["name"] # John

Logical Operations 🧠

• if condition: → Decision making
👉 if x > 10: print("High") else: print("Low")
• and → All conditions true
• or → Any condition true
• not → Reverse condition

Text (String) Functions 🔤

• len(text) → Length
👉 len("hello") = 5
• text.lower() → Lowercase
• text.upper() → Uppercase
• text.strip() → Remove spaces
👉 " hi ".strip() = "hi"
• text.replace(old, new)
👉 "hi".replace("h","H") = "Hi"
• String concatenation
👉 "Hello " + "World"

Date Time Functions 📅

• from datetime import datetime
• datetime.now() → Current date time
• Extract values:
now = datetime.now() now.year now.month now.day

Math Functions ➗

• import math
• math.sqrt(x) → Square root
• math.ceil(x) → Round up
• math.floor(x) → Round down
• abs(x) → Absolute value

Conditional Aggregation (Like Excel SUMIF) ⚡

• Using list comprehension

nums = [10, 20, 30, 40] sum(x for x in nums if x > 20) # 70

• Count condition

len([x for x in nums if x > 20]) # 2

Pro Tip for Data Analysts 💡

👉 For real-world work, use libraries: pandas & numpy

Example:
import pandas as pd df["salary"].mean()

Python Resources: https://whatsapp.com/channel/0029VaiM08SDuMRaGKd9Wv0L

Double Tap ♥️ For More

✅ SQL Real-world Interview Questions with Answers 🖥️

📊 TABLE: employees

id | name | department | salary
1 | Rahul | IT | 50000
2 | Priya | IT | 70000
3 | Amit | HR | 60000
4 | Neha | HR | 70000
5 | Karan | IT | 80000
6 | Simran | HR | 60000

🎯 1️⃣ Find the 2nd highest salary
🧠 Logic: Get highest salary Then find max salary below that

✅ Query:
SELECT MAX(salary) FROM employees WHERE salary < ( SELECT MAX(salary) FROM employees );

🎯 2️⃣ Find employees earning more than average salary
🧠 Logic: Calculate overall average salary Compare each employee

✅ Query:
SELECT name, salary FROM employees WHERE salary > ( SELECT AVG(salary) FROM employees );

🎯 3️⃣ Find highest salary in each department
🧠 Logic: Group by department Use MAX

✅ Query:
SELECT department, MAX(salary) AS highest_salary FROM employees GROUP BY department;

🎯 4️⃣ Find top 2 highest salaries in each department
🧠 Logic: Use ROW_NUMBER Partition by department Filter top 2

✅ Query:
SELECT * FROM (
SELECT name, department, salary,
ROW_NUMBER() OVER( PARTITION BY department ORDER BY salary DESC ) r
FROM employees
) t WHERE r <= 2;

🎯 5️⃣ Find employees earning more than their department average
🧠 Logic: Use correlated subquery Compare with department avg

✅ Query:
SELECT e.name, e.department, e.salary
FROM employees e
WHERE e.salary > (
SELECT AVG(salary) FROM employees WHERE department = e.department
);

⭐ What Interviewer Checks Here
These 5 questions test:
✔ Subqueries
✔ GROUP BY
✔ Window functions
✔ Correlated queries
✔ Real business logic

SQL Resources: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

Double Tap ♥️ For More

🎯 📊 DATA ANALYST MOCK INTERVIEW (WITH ANSWERS)

🧠 1️⃣ Tell me about yourself
✅ Sample Answer:
“I have around 3 years of experience working with data. My core skills include SQL, Excel, and Power BI. I regularly work with data cleaning, transformation, and building dashboards to generate business insights. Recently, I’ve also been strengthening my Python skills for data analysis. I enjoy solving business problems using data and presenting insights in a simple and actionable way.”

📊 2️⃣ What is the difference between WHERE and HAVING?
✅ Answer:
WHERE filters rows before aggregation
HAVING filters after aggregation
Example:
SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

🔗 3️⃣ Explain different types of JOINs
✅ Answer:
INNER JOIN → only matching records
LEFT JOIN → all left + matching right
RIGHT JOIN → all right + matching left
FULL JOIN → all records from both
👉 In analytics, LEFT JOIN is most used.

🧠 4️⃣ How do you find duplicate records in SQL?
✅ Answer:
SELECT column, COUNT(*)
FROM table
GROUP BY column
HAVING COUNT(*) > 1;

👉 Used for data cleaning.

📈 5️⃣ What are window functions?
✅ Answer:
“Window functions perform calculations across rows without reducing the number of rows. They are used for ranking, running totals, and comparisons.”
Example:
SELECT salary, RANK() OVER(ORDER BY salary DESC)
FROM employees;

📊 6️⃣ How do you handle missing data?
✅ Answer:
Remove rows (if small impact)
Replace with mean/median
Use default values
Use interpolation (advanced)
👉 Depends on business context.

📉 7️⃣ What is the difference between COUNT(_) and COUNT(column)?
✅ Answer:
COUNT(*) → counts all rows
COUNT(column) → ignores NULL values

📊 8️⃣ What is a KPI? Give example
✅ Answer:
“KPI (Key Performance Indicator) is a measurable value used to track performance.”
Examples: Revenue growth, Conversion rate, Customer retention

🧠 9️⃣ How would you find the 2nd highest salary?
✅ Answer:
SELECT MAX(salary)
FROM employees
WHERE salary < ( SELECT MAX(salary) FROM employees );

📊 🔟 Explain your dashboard project
✅ Strong Answer:
“I created a sales dashboard in Power BI where I analyzed revenue trends, top-performing products, and regional performance. I used DAX for calculations and added filters for better interactivity. This helped stakeholders identify key areas for growth.”

🔥 1️⃣1️⃣ What is normalization?
✅ Answer:
“Normalization is the process of organizing data to reduce redundancy and improve data integrity.”

📊 1️⃣2️⃣ Difference between INNER JOIN and LEFT JOIN?
✅ Answer:
INNER JOIN → only matching data
LEFT JOIN → keeps all left table data
👉 LEFT JOIN is preferred in analytics.

🧠 1️⃣3️⃣ What is a CTE?
✅ Answer:
“A CTE (Common Table Expression) is a temporary result set defined using WITH clause to improve readability.”

📈 1️⃣4️⃣ How do you explain insights to non-technical people?
✅ Answer:
“I focus on storytelling. Instead of technical terms, I explain insights in simple business language with visuals and examples.”

📊 1️⃣5️⃣ What tools have you used?
✅ Answer:
SQL, Excel, Power BI, Python (basic/advanced depending on you)

💼 1️⃣6️⃣ Behavioral Question: Tell me about a challenge
✅ Answer:
“While working on a dataset, I found inconsistencies in data. I cleaned and standardized it using SQL and Excel, ensuring accurate analysis. This improved the dashboard reliability.”

Double Tap ♥️ For More

🔰 Data Analyst Roadmap 2026

├── 🗃 Introduction to Data Analysis
│ ├── Role overview & career paths
│ ├── Key skills: SQL, Excel, storytelling
│ └── Tools ecosystem (Colab, Tableau Public)

├── 📊 Excel Mastery (Formulas, Pivots)
│ ├── VLOOKUP, INDEX-MATCH, XLOOKUP
│ ├── PivotTables, slicers, Power Query
│ ├── Charts & conditional formatting
│ └── ETL basics in spreadsheets

├── 🔍 SQL for Analytics (Joins, Aggregates)
│ ├── Advanced SELECT with WHERE, GROUP BY
│ ├── JOINS (INNER, LEFT, window functions)
│ └── Performance: indexes, EXPLAIN plans

├── 📈 Visualization Principles (Charts, Dashboards)
│ ├── Chart types (bar, line, heatmaps)
│ ├── Design rules (avoid chart junk)
│ └── Color theory & accessibility

├── 🐍 Python Basics (Pandas, NumPy)
│ ├── DataFrames: load, clean, merge
│ ├── Grouping, pivoting, NumPy arrays
│ └── Jupyter notebooks & stats intro

├── 🔢 Statistics Fundamentals (Averages, Tests)
│ ├── Descriptive (mean, median, distributions)
│ ├── Hypothesis testing (t-tests, chi-square)
│ └── A/B testing & confidence intervals

├── 🛠 Tableau/Power BI Essentials
│ ├── Tableau: calculated fields, LOD
│ ├── Power BI: DAX, data modeling
│ └── Interactive dashboards & storytelling

├── 🤖 AI Tools for Insights (Prompts, AutoML)
│ ├── Prompt engineering for SQL/viz
│ ├── Tableau Einstein, Power BI Copilot
│ └── AutoML basics (no-code modeling)

├── ☁️ Cloud Platforms (BigQuery Basics)
│ ├── BigQuery SQL & massive datasets
│ ├── AWS QuickSight, Snowflake intro
│ └── Free tier cost optimization

├── 📊 Data Storytelling Frameworks
│ ├── Pyramid Principle for reports
│ ├── KPI dashboards & executive summaries
│ └── Narrative structure (context-insight-action)

├── 🔗 ETL Pipelines Intro (dbt, Airflow)
│ ├── Data transformation with dbt
│ ├── Orchestration (Airflow basics)
│ └── No-code: Zapier automation

├── 💼 Portfolio & Interview Prep
│ ├── 3-5 projects (sales, churn analysis)
│ ├── Kaggle datasets & GitHub portfolio
│ └── STAR method, mock interviews

└── 🧪 Real-world Challenges (Kaggle, Cases)
├── E-commerce churn prediction
├── Marketing ROI analysis
├── Supply chain optimization
└── LeetCode SQL, case studies

Like for detailed explanation ❤️

🗃 Introduction to Data Analysis

This is the foundation of your entire data analyst journey. If you get this right, everything else becomes easier.

🎯 1. What Does a Data Analyst Actually Do?

A Data Analyst turns raw data into useful insights that help businesses make decisions.

👉 Simple Flow:
Raw Data → Clean → Analyze → Visualize → Tell Story → Decision

🔍 Real Example:

Imagine an e-commerce company:

Data Analyst checks: Why sales dropped last month?

Finds: Mobile users faced checkout issues

Suggests: Fix mobile UX
Result: Sales improve

👉 This is the real job — not just coding.

🧭 2. Career Paths in Data Analytics

You don’t have just one path. You can specialize based on your interest:

🔹 Business Analyst
Focus: Business decisions
Tools: Excel, Power BI
Work: Reports, KPIs, dashboards

🔹 Product Analyst
Focus: User behavior (apps/websites)
Tools: SQL, Python
Work: A/B testing, funnels

🔹 Data Analyst (Core)
Focus: Data querying reporting
Tools: SQL, Excel, Tableau
Work: Data cleaning, dashboards

🔹 Analytics Engineer (Advanced)
Focus: Data pipelines + modeling
Tools: SQL, dbt
Work: Clean data for analysts

🧠 3. Key Skills You MUST Build

🟢 1. SQL (Most Important Skill)

Used to extract data from databases

You’ll write queries like: SELECT, WHERE, GROUP BY, JOIN

🟡 2. Excel (Underrated but Powerful)

• Quick analysis tool
• Used everywhere in companies

Key things: Pivot Tables
Lookups (XLOOKUP)
Dashboards

🔵 3. Data Storytelling

This is what separates average vs high-paid analysts

👉 Anyone can analyze data
👉 Few can explain it simply

Example: Instead of saying:
> “Sales dropped by 20%”

Say:
“Sales dropped by 20% mainly due to mobile checkout issues, fixing this can recover revenue quickly.”

🧰 4. Tools Ecosystem (What You’ll Use)

🧪 Notebooks Practice

Google Colab
👉 Run Python in browser (no setup needed)

📊 Visualization Tools

Tableau Public
👉 Create dashboards portfolio

Microsoft Power BI
👉 Industry-level reporting tool

🧮 Data Sources (Where data lives)
• Databases (MySQL, PostgreSQL)
• Excel files
• APIs

⚡ 5. Types of Data You’ll Work With

📄 Structured Data

Tables (rows columns)

Example: Excel, SQL tables

🧾 Unstructured Data

Text, images, videos

Example: Reviews, tweets

📊 Semi-structured

JSON, XML
Used in APIs

🔁 6. Typical Data Analyst Workflow
Step-by-step:
1. Understand the problem
2. Collect data
3. Clean data (most time spent here!)
4. Analyze
5. Visualize
6. Communicate insights

👉 70% of work = cleaning + understanding data

👉 Only 30% = actual analysis

🚨 7. Beginner Mistakes to Avoid
❌ Learning too many tools at once
❌ Ignoring SQL
❌ Only watching tutorials (no practice)
❌ Not building projects

💡 Reality Check
👉 Data Analysis is NOT about coding
👉 It’s about thinking, problem-solving, and communication

Double Tap ❤️ For More

Scenario based  Interview Questions & Answers for Data Analyst

1. Scenario: You are working on a SQL database that stores customer information. The database has a table called "Orders" that contains order details. Your task is to write a SQL query to retrieve the total number of orders placed by each customer.
  Question:
  - Write a SQL query to find the total number of orders placed by each customer.
Expected Answer:
    SELECT CustomerID, COUNT(*) AS TotalOrders
    FROM Orders
    GROUP BY CustomerID;

2. Scenario: You are working on a SQL database that stores employee information. The database has a table called "Employees" that contains employee details. Your task is to write a SQL query to retrieve the names of all employees who have been with the company for more than 5 years.
  Question:
  - Write a SQL query to find the names of employees who have been with the company for more than 5 years.
Expected Answer:
    SELECT Name
    FROM Employees
    WHERE DATEDIFF(year, HireDate, GETDATE()) > 5;

Power BI Scenario-Based Questions

1. Scenario: You have been given a dataset in Power BI that contains sales data for a company. Your task is to create a report that shows the total sales by product category and region.
    Expected Answer:
    - Load the dataset into Power BI.
    - Create relationships if necessary.
    - Use the "Fields" pane to select the necessary fields (Product Category, Region, Sales).
    - Drag these fields into the "Values" area of a new visualization (e.g., a table or bar chart).
    - Use the "Filters" pane to filter data as needed.
    - Format the visualization to enhance clarity and readability.

2. Scenario: You have been asked to create a Power BI dashboard that displays real-time stock prices for a set of companies. The stock prices are available through an API.
  Expected Answer:
    - Use Power BI Desktop to connect to the API.
    - Go to "Get Data" > "Web" and enter the API URL.
    - Configure the data refresh settings to ensure real-time updates (e.g., setting up a scheduled refresh or using DirectQuery if supported).
    - Create visualizations using the imported data.
    - Publish the report to the Power BI service and set up a data gateway if needed for continuous refresh.

3. Scenario: You have been given a Power BI report that contains multiple visualizations. The report is taking a long time to load and is impacting the performance of the application.
    Expected Answer:
    - Analyze the current performance using Performance Analyzer.
    - Optimize data model by reducing the number of columns and rows, and removing unnecessary calculations.
    - Use aggregated tables to pre-compute results.
    - Simplify DAX calculations.
    - Optimize visualizations by reducing the number of visuals per page and avoiding complex custom visuals.
    - Ensure proper indexing on the data source.

Free SQL Resources: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

Like if you need more similar content

Hope it helps :)

Real-world SQL Questions with Answers 🔥

Let's dive into some real-world SQL questions with a mini dataset.

📊 Dataset: employees

id  name    department  salary  manager_id
1   Aditi   HR          30000   5
2   Rahul   IT          50000   6
3   Neha    IT          60000   6
4   Aman    Sales       40000   7
5   Kiran   HR          70000   NULL
6   Mohit   IT          80000   NULL
7   Suresh  Sales       65000   NULL
8   Pooja   HR          30000   5

1. Find average salary per department

SELECT department, AVG(salary) AS avg_salary 
FROM employees 
GROUP BY department;

2. Find employees earning above department average

SELECT name, department, salary 
FROM employees e 
WHERE salary > ( 
    SELECT AVG(salary) 
    FROM employees 
    WHERE department = e.department 
);

3. Find highest salary in each department

SELECT department, MAX(salary) AS max_salary 
FROM employees 
GROUP BY department;

4. Find employees who earn more than their manager

SELECT e.name 
FROM employees e 
JOIN employees m ON e.manager_id = m.id 
WHERE e.salary > m.salary;

5. Count employees in each department

SELECT department, COUNT(*) AS total_employees 
FROM employees 
GROUP BY department;

6. Find departments with more than 2 employees

SELECT department, COUNT(*) AS total 
FROM employees 
GROUP BY department 
HAVING COUNT(*) > 2;

7. Find second highest salary

SELECT MAX(salary) 
FROM employees 
WHERE salary < (SELECT MAX(salary) FROM employees);

8. Find employees without managers

SELECT name 
FROM employees 
WHERE manager_id IS NULL;

9. Rank employees by salary

SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rank 
FROM employees;

10. Find duplicate salaries

SELECT salary, COUNT(*) 
FROM employees 
GROUP BY salary 
HAVING COUNT(*) > 1;

11. Top 2 highest salaries

SELECT DISTINCT salary 
FROM employees 
ORDER BY salary DESC 
LIMIT 2;

Double Tap ❤️ For More

Here's a concise cheat sheet to help you get started with Python for Data Analytics. This guide covers essential libraries and functions that you'll frequently use.


1. Python Basics
- Variables:
x = 10
y = "Hello"

- Data Types:
  - Integers: x = 10
  - Floats: y = 3.14
  - Strings: name = "Alice"
  - Lists: my_list = [1, 2, 3]
  - Dictionaries: my_dict = {"key": "value"}
  - Tuples: my_tuple = (1, 2, 3)

- Control Structures:
  - if, elif, else statements
  - Loops: 
  

    for i in range(5):
        print(i)
    

  - While loop:
  

    while x < 5:
        print(x)
        x += 1
    

2. Importing Libraries

- NumPy:

  import numpy as np
  

- Pandas:

  import pandas as pd
  

- Matplotlib:

  import matplotlib.pyplot as plt
  

- Seaborn:

  import seaborn as sns
  

3. NumPy for Numerical Data

- Creating Arrays:

  arr = np.array([1, 2, 3, 4])
  

- Array Operations:

  arr.sum()
  arr.mean()
  

- Reshaping Arrays:

  arr.reshape((2, 2))
  

- Indexing and Slicing:

  arr[0:2]  # First two elements
  

4. Pandas for Data Manipulation

- Creating DataFrames:

  df = pd.DataFrame({
      'col1': [1, 2, 3],
      'col2': ['A', 'B', 'C']
  })
  

- Reading Data:

  df = pd.read_csv('file.csv')
  

- Basic Operations:

  df.head()          # First 5 rows
  df.describe()      # Summary statistics
  df.info()          # DataFrame info
  

- Selecting Columns:

  df['col1']
  df[['col1', 'col2']]
  

- Filtering Data:

  df[df['col1'] > 2]
  

- Handling Missing Data:

  df.dropna()        # Drop missing values
  df.fillna(0)       # Replace missing values
  

- GroupBy:

  df.groupby('col2').mean()
  

5. Data Visualization

- Matplotlib:

  plt.plot(df['col1'], df['col2'])
  plt.xlabel('X-axis')
  plt.ylabel('Y-axis')
  plt.title('Title')
  plt.show()
  

- Seaborn:

  sns.histplot(df['col1'])
  sns.boxplot(x='col1', y='col2', data=df)
  

6. Common Data Operations

- Merging DataFrames:

  pd.merge(df1, df2, on='key')
  

- Pivot Table:

  df.pivot_table(index='col1', columns='col2', values='col3')
  

- Applying Functions:

  df['col1'].apply(lambda x: x*2)
  

7. Basic Statistics

- Descriptive Stats:

  df['col1'].mean()
  df['col1'].median()
  df['col1'].std()
  

- Correlation:

  df.corr()
  

This cheat sheet should give you a solid foundation in Python for data analytics. As you get more comfortable, you can delve deeper into each library's documentation for more advanced features.

I have curated the best resources to learn Python 👇👇
https://whatsapp.com/channel/0029VaiM08SDuMRaGKd9Wv0L

Hope you'll like it

Like this post if you need more resources like this 👍❤️