7⃣ SQL functions for data cleaning:

1. TRIM():

Usage: Removes leading and trailing whitespace from a string.

Example:

SELECT TRIM(column_name) FROM table_name;

2. UPPER() and LOWER():

Usage: Converts a string to uppercase or lowercase, respectively. This is useful for standardizing data.

Example:

SELECT UPPER(column_name) FROM table_name;

SELECT LOWER(column_name) FROM table_name;

3. COALESCE():

Usage: Returns the first non-null value in a list of arguments. It helps to handle null values effectively.

Example:

SELECT COALESCE(column1, column2, 'default_value') FROM table_name;

4. REPLACE():

Usage: Replaces occurrences of a specified string within another string, which can help in cleaning up data formats.

Example:

SELECT REPLACE(column_name, 'old_value', 'new_value') FROM table_name;

5. SUBSTRING():

Usage: Extracts a substring from a string based on specified starting position and length, useful for cleaning or formatting data.

Example:

SELECT SUBSTRING(column_name, start_position, length) FROM table_name;

6. CAST() and CONVERT():

Usage: Converts one data type to another. This is useful for ensuring data consistency across your database.

Example:

SELECT CAST(column_name AS VARCHAR(255)) FROM table_name;

SELECT CONVERT(VARCHAR(255), column_name) FROM table_name;

7. ISNULL():

Usage: Replaces NULL with a specified replacement value. This can help in making reports more readable.

Example:

SELECT ISNULL(column_name, 'default_value') FROM table_name;

Here you can find SQL Interview Resources👇
https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

Hope it helps :)

SQL Cheatsheet ✅

SQL books won’t teach you this.

Natural Keys vs. Autoincrement IDs vs. Public IDs. (or maybe all together)


𝗡𝗮𝘁𝘂𝗿𝗮𝗹 𝗞𝗲𝘆𝘀

Natural keys carry intrinsic meaning because they are part of the domain.

They are directly related to the data, making them intuitive and easy to understand. Examples include email addresses or employee IDs.

The problem is that they are usually not good for performance, but they can also be a security risk if you expose them.


𝗔𝘂𝘁𝗼𝗶𝗻𝗰𝗿𝗲𝗺𝗲𝗻𝘁 𝗜𝗗𝘀

Autoincrement IDs automatically generate unique integers to identify rows within a table.

They are often used as primary keys.

Simple integers are fast for the database to index and query. They provide optimal performance.

However, they are vulnerable to enumeration attacks since predicting the next or previous record is easy.


𝗣𝘂𝗯𝗹𝗶𝗰 𝗜𝗗𝘀 (𝗨𝗨𝗜𝗗𝘀)

UUIDs (Universally Unique Identifiers) are 128-bit identifiers used to uniquely identify information without relying on a centralized authority.

They are difficult to guess, making them suitable for public exposure in APIs.

The problem is they are larger and more complex than integers. This can impact performance, particularly in indexing and storage.


𝗙𝗶𝗻𝗱𝗶𝗻𝗴 𝘁𝗵𝗲 𝗦𝘄𝗲𝗲𝘁 𝗦𝗽𝗼𝘁: 𝗔 𝗠𝗶𝘅𝗲𝗱 𝗔𝗽𝗽𝗿𝗼𝗮𝗰𝗵

Combining different types of keys can offer a balanced solution:

• InternalID: Used for internal operations and relationships between tables.

• PublicID: Used in API responses and endpoints to securely reference user records.

• Email (Natural Key): Used to ensure unique identification of users within the business logic.


The mixed approach keeps your system fast, secure, and easy to understand.

Here you can find essential SQL Interview Resources👇
https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

Like this post if you need more 👍❤️

Hope it helps :)

14 Days Roadmap to learn SQL

𝗗𝗮𝘆 𝟭: 𝗜𝗻𝘁𝗿𝗼𝗱𝘂𝗰𝘁𝗶𝗼𝗻 𝘁𝗼 𝗗𝗮𝘁𝗮𝗯𝗮𝘀𝗲𝘀 𝗮𝗻𝗱 𝗦𝗤𝗟
Topics to Cover:
- What is SQL?
- Different types of databases (Relational vs. Non-Relational)
- SQL vs. NoSQL
- Overview of SQL syntax
Practice:
- Install a SQL database (e.g., MySQL, PostgreSQL, SQLite)
- Explore an online SQL editor like SQLFiddle or DB Fiddle

𝗗𝗮𝘆 𝟮: 𝗕𝗮𝘀𝗶𝗰 𝗦𝗤𝗟 𝗤𝘂𝗲𝗿𝗶𝗲𝘀
Topics to Cover:
- SELECT statement
- Filtering with WHERE clause
- DISTINCT keyword
Practice:
- Write simple SELECT queries to retrieve data from single table
- Filter records using WHERE clauses

𝗗𝗮𝘆 𝟯: 𝗦𝗼𝗿𝘁𝗶𝗻𝗴 𝗮𝗻𝗱 𝗙𝗶𝗹𝘁𝗲𝗿𝗶𝗻𝗴
Topics to Cover:
- ORDER BY clause
- Using LIMIT/OFFSET for pagination
- Comparison and logical operators
Practice:
- Sort data with ORDER BY
- Apply filtering with multiple conditions use AND/OR

𝗗𝗮𝘆 𝟰: 𝗦𝗤𝗟 𝗙𝘂𝗻𝗰𝘁𝗶𝗼𝗻𝘀 𝗮𝗻𝗱 𝗔𝗴𝗴𝗿𝗲𝗴𝗮𝘁𝗶𝗼𝗻𝘀
Topics to Cover:
- Aggregate functions (COUNT, SUM, AVG, MIN, MAX)
- GROUP BY and HAVING clauses
Practice:
- Perform aggregation on dataset
- Group data and filter groups using HAVING

𝗗𝗮𝘆 𝟱: 𝗪𝗼𝗿𝗸𝗶𝗻𝗴 𝘄𝗶𝘁𝗵 𝗠𝘂𝗹𝘁𝗶𝗽𝗹𝗲 𝗧𝗮𝗯𝗹𝗲𝘀 - 𝗝𝗼𝗶𝗻𝘀
Topics to Cover:
- Introduction to Joins (INNER, LEFT, RIGHT, FULL)
- CROSS JOIN and self-joins
Practice:
- Write queries using different types of JOINs to combine data from multiple table

𝗗𝗮𝘆 𝟲: 𝗦𝘂𝗯𝗾𝘂𝗲𝗿𝗶𝗲𝘀 𝗮𝗻𝗱 𝗡𝗲𝘀𝘁𝗲𝗱 𝗤𝘂𝗲𝗿𝗶𝗲𝘀
Topics to Cover:
- Subqueries in SELECT, WHERE, and FROM clauses
- Correlated subqueries
Practice:
- Write subqueries to filter, aggregate, an select data

𝗗𝗮𝘆 𝟳: 𝗗𝗮𝘁𝗮 𝗠𝗼𝗱𝗲𝗹𝗹𝗶𝗻𝗴 𝗮𝗻𝗱 𝗗𝗮𝘁𝗮𝗯𝗮𝘀𝗲 𝗗𝗲𝘀𝗶𝗴𝗻
Topics to Cover:
- Understanding ERD (Entity Relationship Diagram)
- Normalization (1NF, 2NF, 3NF)
- Primary and Foreign Key
Practice:
- Design a simple database schema and implement it in your database

𝗗𝗮𝘆 𝟴: 𝗠𝗼𝗱𝗶𝗳𝘆𝗶𝗻𝗴 𝗗𝗮𝘁𝗮 - 𝗜𝗡𝗦𝗘𝗥𝗧, 𝗨𝗣𝗗𝗔𝗧𝗘, 𝗗𝗘𝗟𝗘𝗧𝗘
Topics to Cover:
- INSERT INTO statement
- UPDATE and DELETE statement
- Transactions and rollback
Practice:
- Insert, update, and delete records in a table
- Practice transactions with COMMIT and ROLLBACK

𝗗𝗮𝘆 𝟵: 𝗔𝗱𝘃𝗮𝗻𝗰𝗲𝗱 𝗦𝗤𝗟 𝗙𝘂𝗻𝗰𝘁𝗶𝗼𝗻𝘀
Topics to Cover:
- String functions (CONCAT, SUBSTR, etc.)
- Date functions (NOW, DATEADD, DATEDIFF)
- CASE statement
Practice:
- Use string and date function in queries
- Write conditional logic using CASE

𝗗𝗮𝘆 𝟭𝟬: 𝗩𝗶𝗲𝘄𝘀 𝗮𝗻𝗱 𝗜𝗻𝗱𝗲𝘅𝗲𝘀
Topics to Cover:
- Creating and using Views
- Indexes: What they are and how they work
- Pros and cons of using indexes
Practice:
- Create and query views
- Explore how indexes affect query performance

Here you can find essential SQL Interview Resources👇
https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

Like this post if you need more 👍❤️

Hope it helps :)

𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿: You have 2 minutes to solve this SQL query.
Retrieve the department name and the highest salary in each department from the employees table, but only for departments where the highest salary is greater than $70,000.

𝗠𝗲: Challenge accepted!

SELECT department, MAX(salary) AS highest_salary
FROM employees
GROUP BY department
HAVING MAX(salary) > 70000;

I used GROUP BY to group employees by department, MAX() to get the highest salary, and HAVING to filter the result based on the condition that the highest salary exceeds $70,000. This solution effectively shows my understanding of aggregation functions and how to apply conditions on the result of those aggregations.

𝗧𝗶𝗽 𝗳𝗼𝗿 𝗦𝗤𝗟 𝗝𝗼𝗯 𝗦𝗲𝗲𝗸𝗲𝗿𝘀:
It's not about writing complex queries; it's about writing clean, efficient, and scalable code. Focus on mastering subqueries, joins, and aggregation functions to stand out!

I have curated essential SQL Interview Resources👇
https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

Like this post if you need more 👍❤️

Hope it helps :)

I've compiled a list of important SQL interview questions to help you prepare for your next data analytics interview. These questions cover everything from basic to advanced topics. Let’s dive in!👇

1. What is the purpose of the GROUP BY clause in SQL? Provide an example.
2. Explain the difference between an INNER JOIN and a LEFT JOIN with examples.
3. Discuss the role of the WHERE clause in SQL queries and provide examples of its usage.
4. Explain the concept of database transactions and the ACID properties.
5. Describe the benefits of using subqueries in SQL and provide a scenario where they would be useful.
6. Discuss the differences between the CHAR and VARCHAR data types in SQL.
7. Explain the purpose of the ORDER BY clause in SQL queries and provide examples.
8. Describe the importance of data integrity constraints such as NOT NULL, UNIQUE, and CHECK constraints in SQL databases.
9. Discuss the advantages and disadvantages of using stored procedures
Explain the difference between an aggregate function and a scalar function in SQL, with examples.
10. Discuss the role of the COMMIT and ROLLBACK statements in SQL transactions.
11. Explain the purpose of the LIKE operator in SQL and provide examples of its usage.
12. Describe the concept of normalization forms (1NF, 2NF, 3NF) and why they are important in database design.
13. Discuss the differences between a clustered and non-clustered index in SQL.
14. Explain the concept of data warehousing and how it differs from traditional relational databases.
15. Describe the benefits of using database triggers and provide examples of their usage.
16. Discuss the concept of database concurrency control and how it is achieved in SQL databases.
17. Explain the role of the SELECT INTO statement in SQL and provide examples of its usage.
18. Describe the differences between a database view and a materialized view in SQL.
19. Discuss the advantages of using parameterized queries in SQL applications.
20. Write a query to retrieve all employees who have a salary greater than $100,000.
21. Create a query to display the total number of orders placed in the last month.
22. Write a query to find the average order value for each customer.
23. Create a query to count the number of distinct products sold in the past week.
24. Write a query to find the top 10 customers with the highest total order amount.

Here you can find SQL Interview Resources👇
t.iss.one/mysqldata

Hope it helps :)

SQL Cheatsheet 📝

This SQL cheatsheet is designed to be your quick reference guide for SQL programming. Whether you’re a beginner learning how to query databases or an experienced developer looking for a handy resource, this cheatsheet covers essential SQL topics.

1. Database Basics
   - CREATE DATABASE db_name;
   - USE db_name;

2. Tables
   - Create Table: CREATE TABLE table_name (col1 datatype, col2 datatype);
   - Drop Table: DROP TABLE table_name;
   - Alter Table: ALTER TABLE table_name ADD column_name datatype;

3. Insert Data
   - INSERT INTO table_name (col1, col2) VALUES (val1, val2);

4. Select Queries
   - Basic Select: SELECT * FROM table_name;
   - Select Specific Columns: SELECT col1, col2 FROM table_name;
   - Select with Condition: SELECT * FROM table_name WHERE condition;

5. Update Data
   - UPDATE table_name SET col1 = value1 WHERE condition;

6. Delete Data
   - DELETE FROM table_name WHERE condition;

7. Joins
   - Inner Join: SELECT * FROM table1 INNER JOIN table2 ON table1.col = table2.col;
   - Left Join: SELECT * FROM table1 LEFT JOIN table2 ON table1.col = table2.col;
   - Right Join: SELECT * FROM table1 RIGHT JOIN table2 ON table1.col = table2.col;

8. Aggregations
   - Count: SELECT COUNT(*) FROM table_name;
   - Sum: SELECT SUM(col) FROM table_name;
   - Group By: SELECT col, COUNT(*) FROM table_name GROUP BY col;

9. Sorting & Limiting
   - Order By: SELECT * FROM table_name ORDER BY col ASC|DESC;
   - Limit Results: SELECT * FROM table_name LIMIT n;

10. Indexes
   - Create Index: CREATE INDEX idx_name ON table_name (col);
   - Drop Index: DROP INDEX idx_name;

11. Subqueries
   - SELECT * FROM table_name WHERE col IN (SELECT col FROM other_table);

12. Views
   - Create View: CREATE VIEW view_name AS SELECT * FROM table_name;
   - Drop View: DROP VIEW view_name;

Here you can find SQL Interview Resources👇
https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

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

Hope it helps :)

Best practices for writing SQL queries:

1- Filter Early, Aggregate Late: Apply filtering conditions in the WHERE clause early in the query, and perform aggregations in the HAVING or SELECT clauses as needed.

2- Use table aliases with columns when you are joining multiple tables.

3- Never use select *, always mention list of columns in select clause before deploying the code.

4- Add useful comments wherever you write complex logic. Avoid too many comments.

5- Use joins instead of correlated subqueries when possible for better performance.

6- Create CTEs instead of multiple sub queries, it will make your query easy to read.

7- Join tables using JOIN keywords instead of writing join condition in where clause for better readability.

8- Never use order by in sub queries, It will unnecessary increase runtime. In fact some databases don't even allow you to do that.

9- If you know there are no duplicates in 2 tables, use UNION ALL instead of UNION for better performance.

Most Asked SQL Interview Questions at MAANG Companies🔥🔥

Preparing for an SQL Interview at MAANG Companies? Here are some crucial SQL Questions you should be ready to tackle:

1. How do you retrieve all columns from a table?

SELECT * FROM table_name;

2. What SQL statement is used to filter records?

SELECT * FROM table_name
WHERE condition;

The WHERE clause is used to filter records based on a specified condition.

3. How can you join multiple tables? Describe different types of JOINs.

SELECT columns
FROM table1
JOIN table2 ON table1.column = table2.column
JOIN table3 ON table2.column = table3.column;

Types of JOINs:

1. INNER JOIN: Returns records with matching values in both tables

SELECT * FROM table1
INNER JOIN table2 ON table1.column = table2.column;

2. LEFT JOIN: Returns all records from the left table & matched records from the right table. Unmatched records will have NULL values.

SELECT * FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

3. RIGHT JOIN: Returns all records from the right table & matched records from the left table. Unmatched records will have NULL values.

SELECT * FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

4. FULL JOIN: Returns records when there is a match in either left or right table. Unmatched records will have NULL values.

SELECT * FROM table1
FULL JOIN table2 ON table1.column = table2.column;

4. What is the difference between WHERE & HAVING clauses?

WHERE: Filters records before any groupings are made.

SELECT * FROM table_name
WHERE condition;

HAVING: Filters records after groupings are made.

SELECT column, COUNT(*)
FROM table_name
GROUP BY column
HAVING COUNT(*) > value;

5. How do you calculate average, sum, minimum & maximum values in a column?

Average: SELECT AVG(column_name) FROM table_name;

Sum: SELECT SUM(column_name) FROM table_name;

Minimum: SELECT MIN(column_name) FROM table_name;

Maximum: SELECT MAX(column_name) FROM table_name;


Like this post if you need more 👍❤️

Hope it helps :)

SQL Roadmap ✅

Commit and master 𝗦𝗤𝗟 in just 𝟯𝟬 𝗗𝗮𝘆𝘀!

I've outlined a simple, actionable plan for you to follow…

𝗪𝗲𝗲𝗸 𝟭: 𝗕𝗮𝘀𝗶𝗰𝘀 𝗼𝗳 𝗦𝗤𝗟

➛ Day 1-2: Introduction to SQL, setting up your environment (MySQL/PostgreSQL/SQL Server).

➛ Day 3-4: Understanding databases, tables, and basic SQL syntax.

➛ Day 5-7: Working with SELECT, WHERE, and filtering data.

𝗪𝗲𝗲𝗸 𝟮: 𝗖𝗼𝗿𝗲 𝗤𝘂𝗲𝗿𝗶𝗲𝘀

➛ Day 8-10: Using JOINs – INNER, LEFT, RIGHT, FULL.

➛ Day 11-13: GROUP BY, HAVING, and aggregate functions (SUM, COUNT, AVG).

➛ Day 14: Practice session – write complex queries.

𝗪𝗲𝗲𝗸 𝟯: 𝗠𝗼𝗱𝗶𝗳𝘆𝗶𝗻𝗴 𝗗𝗮𝘁𝗮

➛ Day 15-17: INSERT, UPDATE, DELETE – altering your data.

➛ Day 18-20: Subqueries, nested queries, and derived tables.

➛ Day 21: Practice session – work on a mini-project.

𝗪𝗲𝗲𝗸 𝟰: 𝗔𝗱𝘃𝗮𝗻𝗰𝗲𝗱 𝗦𝗤𝗟 𝗧𝗼𝗽𝗶𝗰𝘀 𝗮𝗻𝗱 𝗣𝗿𝗼𝗷𝗲𝗰𝘁

➛ Day 22-24: Window functions, RANK, DENSE_RANK, ROW_NUMBER.

➛ Day 25-27: Creating and managing indexes, views, and stored procedures.

➛ Day 28-30: Capstone project – work with real-world data to design and query a database.

Here you can find SQL Interview Resources👇
https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

Like this post if you need more 👍❤️

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

Hope it helps :)

SQL Number Functions 👆

SQL books won’t teach you this.

Natural Keys vs. Autoincrement IDs vs. Public IDs. (or maybe all together)


𝗡𝗮𝘁𝘂𝗿𝗮𝗹 𝗞𝗲𝘆𝘀

Natural keys carry intrinsic meaning because they are part of the domain.

They are directly related to the data, making them intuitive and easy to understand. Examples include email addresses or employee IDs.

The problem is that they are usually not good for performance, but they can also be a security risk if you expose them.


𝗔𝘂𝘁𝗼𝗶𝗻𝗰𝗿𝗲𝗺𝗲𝗻𝘁 𝗜𝗗𝘀

Autoincrement IDs automatically generate unique integers to identify rows within a table.

They are often used as primary keys.

Simple integers are fast for the database to index and query. They provide optimal performance.

However, they are vulnerable to enumeration attacks since predicting the next or previous record is easy.


𝗣𝘂𝗯𝗹𝗶𝗰 𝗜𝗗𝘀 (𝗨𝗨𝗜𝗗𝘀)

UUIDs (Universally Unique Identifiers) are 128-bit identifiers used to uniquely identify information without relying on a centralized authority.

They are difficult to guess, making them suitable for public exposure in APIs.

The problem is they are larger and more complex than integers. This can impact performance, particularly in indexing and storage.


𝗙𝗶𝗻𝗱𝗶𝗻𝗴 𝘁𝗵𝗲 𝗦𝘄𝗲𝗲𝘁 𝗦𝗽𝗼𝘁: 𝗔 𝗠𝗶𝘅𝗲𝗱 𝗔𝗽𝗽𝗿𝗼𝗮𝗰𝗵

Combining different types of keys can offer a balanced solution:

• InternalID: Used for internal operations and relationships between tables.

• PublicID: Used in API responses and endpoints to securely reference user records.

• Email (Natural Key): Used to ensure unique identification of users within the business logic.


The mixed approach keeps your system fast, secure, and easy to understand.

Here you can find essential SQL Interview Resources👇
https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

Like this post if you need more 👍❤️

Hope it helps :)

SQL Query Logical Order

How a SQL query gets executed internally - Lets see step by step!

We all know SQL, but most of us do not understand the internals of it.

Let me take an example to explain this better.

Select p.plan_name, count(plan_id) as total_count
From plans p
Join subscriptions s on s.plan_id=p.plan_id
Where p.plan_name !=’premium’
Group by p.plan_name
Having count(plan_id) > 100
Order by p.plan_name
Limit 10;

Step 01: Get the table data required to run the sql query
Operations: FROM, JOIN (From plans p, Join subscriptions s)

Step 02: Filter the data rows
Operations: WHERE (where p.plan_name=’premium’)

Step 03: Group the data
Operations: GROUP (group by p.plan_name)

Step 04: Filter the grouped data
Operations: HAVING (having count(plan_id) > 100)

Step 05: Select the data columns
Operations: SELECT (select p.plan_name, count(p.plan_id)

Step 06: Order the data
Operations: ORDER BY (order by p.plan_name)

Step 07: Limit the data rows
Operations: LIMIT (limit 100)

Knowing the Internals really help.

Here you can find essential SQL Interview Resources👇
https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

Like this post if you need answer for the above question👍❤️

Hope it helps :)

SQL for Data Analysis

Most people learn SQL just enough to pull some data. But if you really understand it, you can analyze massive datasets without touching Excel or Python.

Here are 8 game-changing SQL concepts that will make you a data pro:

👇

1. Stop pulling raw data. Start pulling insights.

The biggest mistake? Running a query that gives you everything and then filtering it later.

Good analysts don’t pull raw data. They shape the data before it even reaches them.

2. “SELECT ” is a rookie move.

Pulling all columns is lazy and slow.

A pro only selects what they need.
✔️ Fewer columns = Faster queries
✔️ Less noise = Clearer insights

The more precise your query, the less time you waste cleaning data.

3. GROUP BY is your best friend.

You don’t need 100,000 rows of transactions. What you need is:
✔️ Sales per region
✔️ Average order size per customer
✔️ Number of signups per month

Grouping turns chaotic data into useful summaries.

4. Joins = Connecting the dots.

Your most important data is split across multiple tables.

Want to know how much each customer spent? You need to join:
✔️ Customer info
✔️ Order history
✔️ Payments

Joins = unlocking hidden insights.

5. Window functions will blow your mind.

They let you:
✔️ Rank customers by total purchases
✔️ Calculate rolling averages
✔️ Compare each row to the overall trend

It’s like pivot tables, but way more powerful.

6. CTEs will save you from spaghetti SQL.

Instead of writing a 50-line nested query, break it into steps.

CTEs (Common Table Expressions) make your SQL:
✔️ Easier to read
✔️ Easier to debug
✔️ Reusable

Good SQL is clean SQL.

7. Indexes = Speed.

If your queries take forever, your database is probably doing unnecessary work.

Indexes help databases find data faster.

If you work with large datasets, this is a game changer.

SQL isn’t just about pulling data. It’s about analyzing, transforming, and optimizing it.

Master these 8 concepts, and you’ll never look at SQL the same way again.

Join us on WhatsApp: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

Top Tools to learn Data Engineering

Join for more data engineering resources: https://t.iss.one/sql_engineer