The GROUP BY clause in SQL is used to arrange identical data into groups. This is particularly useful when combined with aggregate functions like COUNT(), SUM(), AVG(), MIN(), and MAX(). The GROUP BY clause groups rows that have the same values in specified columns into summary rows.

▎Basic Syntax

SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition
GROUP BY column1;

▎Example 1: Counting Rows

Suppose you have a table called employees with the following structure:

| id | department | salary |
|----|------------|--------|
| 1 | HR | 50000 |
| 2 | IT | 60000 |
| 3 | HR | 55000 |
| 4 | IT | 70000 |
| 5 | Sales | 65000 |

To find out how many employees are in each department, you can use:

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

Result:

| department | employee_count |
|------------|----------------|
| HR | 2 |
| IT | 2 |
| Sales | 1 |

▎Example 2: Summing Salaries

To calculate the total salary paid to employees in each department, you can use:

SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;

Result:

| department | total_salary |
|------------|--------------|
| HR | 105000 |
| IT | 130000 |
| Sales | 65000 |

▎Example 3: Average Salary

To find the average salary of employees in each department:

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

Result:

| department | average_salary |
|------------|----------------|
| HR | 52500 |
| IT | 65000 |
| Sales | 65000 |

▎Example 4: Grouping by Multiple Columns

You can also group by multiple columns. For instance, if you had another column for job_title:

| id | department | job_title | salary |
|----|------------|-----------|--------|
| 1 | HR | Manager | 50000 |
| 2 | IT | Developer | 60000 |
| 3 | HR | Assistant | 55000 |
| 4 | IT | Manager | 70000 |
| 5 | Sales | Executive | 65000 |

To count employees by both department and job_title:

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

Result:

| department | job_title | employee_count |
|------------|-----------|----------------|
| HR | Manager | 1 |
| HR | Assistant | 1 |
| IT | Developer | 1 |
| IT | Manager | 1 |
| Sales | Executive | 1 |

▎Important Notes

1. Aggregate Functions: Any column in the SELECT statement that is not an aggregate function must be included in the GROUP BY clause.

2. HAVING Clause: You can filter groups using the HAVING clause, which is similar to the WHERE clause but is used for aggregated data. For example:

   SELECT department, COUNT(*) AS employee_count
   FROM employees
   GROUP BY department
   HAVING COUNT(*) > 1;
   

This would return only departments with more than one employee.

▎Conclusion

The GROUP BY clause is a powerful tool in SQL for summarizing data. It allows you to analyze and report on your datasets effectively by grouping similar data points and applying aggregate functions.

Types Of Database YOU MUST KNOW

1. Relational Databases (e.g., MySQL, Oracle, SQL Server):
- Uses structured tables to store data.
- Offers data integrity and complex querying capabilities.
- Known for ACID compliance, ensuring reliable transactions.
- Includes features like foreign keys and security control, making them ideal for applications needing consistent data relationships.

2. Document Databases (e.g., CouchDB, MongoDB):
- Stores data as JSON documents, providing flexible schemas that can adapt to varying structures.
- Popular for semi-structured or unstructured data.
- Commonly used in content management and automated sharding for scalability.

3. In-Memory Databases (e.g., Apache Geode, Hazelcast):
- Focuses on real-time data processing with low-latency and high-speed transactions.
- Frequently used in scenarios like gaming applications and high-frequency trading where speed is critical.

4. Graph Databases (e.g., Neo4j, OrientDB):
- Best for handling complex relationships and networks, such as social networks or knowledge graphs.
- Features like pattern recognition and traversal make them suitable for analyzing connected data structures.

5. Time-Series Databases (e.g., Timescale, InfluxDB):
- Optimized for temporal data, IoT data, and fast retrieval.
- Ideal for applications requiring data compression and trend analysis over time, such as monitoring logs.

6. Spatial Databases (e.g., PostGIS, Oracle, Amazon Aurora):
- Specializes in geographic data and location-based queries.
- Commonly used for applications involving maps, GIS, and geospatial data analysis, including earth sciences.

Different types of databases are optimized for specific tasks. Relational databases excel in structured data management, while document, graph, in-memory, time-series, and spatial databases each have distinct strengths suited for modern data-driven applications.

If you are interested to learn SQL for data analytics purpose and clear the interviews, just cover the following topics

1)Install MYSQL workbench
2) Select
3) From
4) where
5) group by
6) having
7) limit
8) Joins (Left, right , inner, self, cross)
9) Aggregate function ( Sum, Max, Min , Avg)
9) windows function ( row num, rank, dense rank, lead, lag, Sum () over)
10)Case
11) Like
12) Sub queries
13) CTE
14) Replace CTE with temp tables
15) Methods to optimize Sql queries
16) Solve problems and case studies at Ankit Bansal youtube channel

Trick: Just copy each term and paste on youtube and watch any 10 to 15 minute on each topic and practise it while learning , By doing this , you get the basics understanding

17) Now time to go on youtube and search data analysis end to end project using sql

18) Watch them and practise them end to end.

17) learn integration with power bi

In this way , you will not only memorize the concepts but also learn how to implement them in your current working and projects and will be able to defend it in your interviews as well.

Like for more

Here you can find essential SQL Interview Resources👇
https://t.iss.one/DataSimplifier

Hope it helps :)

⚠ This is very important guys 🚨

Guys as there are rumours around Telegram usage, to avoid any inconvenience, you can join the whatsapp channel here
👇👇
https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

It took lot of efforts to build this community, please join fast so that we can continue our initiative to help you all 🚀

SQL best practices ✅

✔ Use EXISTS in place of IN wherever possible
✔ Use table aliases with columns when you are joining multiple tables
✔ Use GROUP BY instead of DISTINCT.
✔ Add useful comments wherever you write complex logic and avoid too many comments.
✔ Use joins instead of subqueries when possible for better performance.
✔ Use WHERE instead of HAVING to define filters on non-aggregate fields
✔ Avoid wildcards at beginning of predicates (something like '%abc' will cause full table scan to get the results)
✔ Considering cardinality within GROUP BY can make it faster (try to consider unique column first in group by list)
✔ Write SQL keywords in capital letters.
✔ Never use select *, always mention list of columns in select clause.
✔ Create CTEs instead of multiple sub queries , it will make your query easy to read.
✔ Join tables using JOIN keywords instead of writing join condition in where clause for better readability.
✔ Never use order by in sub queries , It will unnecessary increase runtime.
✔ If you know there are no duplicates in 2 tables, use UNION ALL instead of UNION for better performance
✔ Always start WHERE clause with 1 = 1.This has the advantage of easily commenting out conditions during debugging a query.
✔ Taking care of NULL values before using equality or comparisons operators. Applying window functions. Filtering the query before joining and having clause.
✔ Make sure the JOIN conditions among two table Join are either keys or Indexed attribute.

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

Like this post if you need more 👍❤️

Hope it helps :)

SQL Joins: Unlock the Secrets Data Aficionado's

♐️ SQL joins are the secret ingredients that bring your data feast together, they are the backbone of relational database querying, allowing us to combine data from multiple tables.

➠ Let's explore the various types of joins and their applications:

1. INNER JOIN
- Returns only the matching rows from both tables
- Use case: Finding common data points, e.g., customers who have made purchases

2. LEFT JOIN
- Returns all rows from the left table and matching rows from the right table
- Use case: Retrieving all customers and their orders, including those who haven't made any purchases

3. RIGHT JOIN
- Returns all rows from the right table and matching rows from the left table
- Use case: Finding all orders and their corresponding customers, including orders without customer data

4. FULL OUTER JOIN
- Returns all rows from both tables, with NULL values where there's no match
- Use case: Comprehensive view of all data, identifying gaps in relationships

5. CROSS JOIN
- Returns the Cartesian product of both tables
- Use case: Generating all possible combinations, e.g., product variations

6. SELF JOIN
- Joins a table with itself
- Use case: Hierarchical data, finding relationships within the same table

🚀 Advanced Join Techniques

1. UNION and UNION ALL
- Combines result sets of multiple queries
- UNION removes duplicates, UNION ALL keeps them
- Use case: Merging data from similar structures

2. Joins with NULL Checks
- Useful for handling missing data or exclusions

💡 SQL Best Practices for Optimal Performance

1. Use Appropriate Indexes : Create indexes on join columns and frequently filtered fields.

2. Leverage Subqueries: Simplify complex queries and improve readability.

3. Utilize Common Table Expressions (CTEs): Enhance query structure and reusability.

4. Employ Window Functions: For advanced analytics without complex joins.

5. Optimize Query Plans: Analyze and tune execution plans for better performance.

6. Master Regular Expressions: For powerful pattern matching and data manipulation.

20 medium-level SQL interview questions:

1. Write a SQL query to find the second-highest salary.
2. How would you optimize a slow SQL query?
3. What is the difference between INNER JOIN and OUTER JOIN?
4. Write a SQL query to find the top 3 departments with the highest average salary.
5. How do you handle duplicate rows in a SQL query?
6. Write a SQL query to find the employees who have the same name and work in the same department.
7. What is the difference between UNION and UNION ALL?
8. Write a SQL query to find the departments with no employees.
9. How do you use indexing to improve SQL query performance?
10. Write a SQL query to find the employees who have worked for more than 5 years.
11. What is the difference between SUBQUERY and JOIN?
12. Write a SQL query to find the top 2 products with the highest sales.
13. How do you use stored procedures to improve SQL query performance?
14. Write a SQL query to find the customers who have placed an order but have not made a payment.
15. What is the difference between GROUP BY and HAVING?
16. Write a SQL query to find the employees who work in the same department as their manager.
17. How do you use window functions to solve complex queries?
18. Write a SQL query to find the top 3 products with the highest average price.
19. What is the difference between TRUNCATE and DELETE?
20. Write a SQL query to find the employees who have not taken any leave in the last 6 months.

SQL Interviews LOVE to test you on Window Functions. Here’s the list of 7 most popular window functions

👇 𝟕 𝐌𝐨𝐬𝐭 𝐓𝐞𝐬𝐭𝐞𝐝 𝐖𝐢𝐧𝐝𝐨𝐰 𝐅𝐮𝐧𝐜𝐭𝐢𝐨𝐧𝐬

* RANK() - gives a rank to each row in a partition based on a specified column or value

* DENSE_RANK() - gives a rank to each row, but DOESN'T skip rank values

* ROW_NUMBER() - gives a unique integer to each row in a partition based on the order of the rows

* LEAD() - retrieves a value from a subsequent row in a partition based on a specified column or expression

* LAG() - retrieves a value from a previous row in a partition based on a specified column or expression

* NTH_VALUE() - retrieves the nth value in a partition

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.

Complete SQL Road Map 👇👇

1.Intro to SQL
• Definition
• Purpose
• Relational DBs
• DBMS

2.Basic SQL Syntax
• SELECT
• FROM
• WHERE
• ORDER BY
• GROUP BY

3. Data Types
• Integer
• Floating-Point
• Character
• Date
• VARCHAR
• TEXT
• BLOB
• BOOLEAN

4.Sub languages
• DML
• DDL
• DQL
• DCL
• TCL

5. Data Manipulation
• INSERT
• UPDATE
• DELETE

6. Data Definition
• CREATE
• ALTER
• DROP
• Indexes

7.Query Filtering and Sorting
• WHERE
• AND
• OR Conditions
• Ascending
• Descending

8. Data Aggregation
• SUM
• AVG
• COUNT
• MIN
• MAX

9.Joins and Relationships
• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• Self-Joins
• Cross Joins
• FULL OUTER JOIN

10.Subqueries
• Subqueries used in
• Filtering data
• Aggregating data
• Joining tables
• Correlated Subqueries

11.Views
• Creating
• Modifying
• Dropping Views

12.Transactions
• ACID Properties
• COMMIT
• ROLLBACK
• SAVEPOINT
• ROLLBACK TO SAVEPOINT

13.Stored Procedures
• CREATE PROCEDURE
• ALTER PROCEDURE
• DROP PROCEDURE
• EXECUTE PROCEDURE
• User-Defined Functions (UDFs)

14.Triggers
• Trigger Events
• Trigger Execution and Syntax

15. Security and Permissions
• CREATE USER
• GRANT
• REVOKE
• ALTER USER
• DROP USER

16.Optimizations
• Indexing Strategies
• Query Optimization

17.Normalization
• 1NF(Normal Form)
• 2NF
• 3NF
• BCNF

18.Backup and Recovery
• Database Backups
• Point-in-Time Recovery

19.NoSQL Databases
• MongoDB
• Cassandra etc...
• Key differences

20. Data Integrity
• Primary Key
• Foreign Key

21.Advanced SQL Queries
• Window Functions
• Common Table Expressions (CTEs)

22.Full-Text Search
• Full-Text Indexes
• Search Optimization

23. Data Import and Export
• Importing Data
• Exporting Data (CSV, JSON)
• Using SQL Dump Files

24.Database Design
• Entity-Relationship Diagrams
• Normalization Techniques

25.Advanced Indexing
• Composite Indexes
• Covering Indexes

26.Database Transactions
• Savepoints
• Nested Transactions
• Two-Phase Commit Protocol

27.Performance Tuning
• Query Profiling and Analysis
• Query Cache Optimization

------------------ @sqlanalyst -------------------

Some good resources to learn SQL

1.Tutorial & Courses
• Learn SQL: https://bit.ly/3FxxKPz
• Udacity: imp.i115008.net/AoAg7K

2. YouTube Channel
• FreeCodeCamp:rb.gy/pprz73

3. Books
• SQL in a Nutshell: https://t.iss.one/DataAnalystInterview/158

ENJOY LEARNING 👍👍

SQL Essentials 👆

Top 10 Advanced SQL Interview Questions and Answers 

1. What is a Common Table Expression (CTE), and when would you use it? 
   A Common Table Expression (CTE) is a temporary result set that can be referred to within a SELECT, INSERT, UPDATE, or DELETE statement. 
   Example: 
 

   WITH SalesCTE AS (
       SELECT SalespersonID, SUM(SalesAmount) AS TotalSales
       FROM Sales
       GROUP BY SalespersonID
   )
   SELECT * FROM SalesCTE WHERE TotalSales > 5000;
   

2. How do you optimize a query with a large dataset? 
   - Use proper indexes. 
   - Avoid SELECT *; only retrieve required columns. 
   - Break down complex queries using temporary tables or CTEs. 
   - Analyze query execution plans. 

3. What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()? 
   - RANK(): Skips ranking if there’s a tie (e.g., 1, 2, 2, 4). 
   - DENSE_RANK(): Does not skip ranks after a tie (e.g., 1, 2, 2, 3). 
   - ROW_NUMBER(): Assigns unique numbers sequentially, regardless of ties. 

4. How do you find duplicate records in a table? 
 

   SELECT ColumnName, COUNT(*)
   FROM TableName
   GROUP BY ColumnName
   HAVING COUNT(*) > 1;
   

5. What is the difference between INNER JOIN and LEFT JOIN? 
   - INNER JOIN: Returns records that match in both tables. 
   - LEFT JOIN: Returns all records from the left table, and matching records from the right table (NULL if no match). 

6. Explain window functions and provide an example. 
   Window functions operate on a set of rows related to the current row, without collapsing them into a single output. 
   Example: 
 

   SELECT EmployeeID, Salary, 
          RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS Rank
   FROM Employees;
   

7. What are the different types of indexes in SQL? 
   - Clustered Index: Reorders the data physically in the table. 
   - Non-Clustered Index: Creates a separate structure for data retrieval. 
   - Unique Index: Ensures no duplicate values in the column. 

8. How do you handle NULL values in SQL? 
   - Use COALESCE() or ISNULL() to replace NULL values. 
   - Filter with IS NULL or IS NOT NULL in WHERE clauses. 
   Example: 
 

   SELECT COALESCE(PhoneNumber, 'N/A') AS ContactNumber FROM Customers;
   

9. What is the difference between DELETE and TRUNCATE? 
   - DELETE: Removes specific rows, can use WHERE clause, and logs individual row deletions. 
   - TRUNCATE: Removes all rows, faster, and resets table identity. 

10. How do you use a CASE statement in SQL? 
  

    SELECT ProductName,
           CASE 
               WHEN Quantity > 100 THEN 'High Stock'
               WHEN Quantity BETWEEN 50 AND 100 THEN 'Medium Stock'
               ELSE 'Low Stock'
           END AS StockStatus
    FROM Products;
   

SQL INTERVIEW Questions

Explain the concept of window functions in SQL. Provide examples to illustrate their usage.

Answer:

Window Functions:
Window functions perform calculations across a set of table rows related to the current row. Unlike aggregate functions, window functions do not group rows into a single output row; instead, they return a value for each row in the query result.

Types of Window Functions:
1. Aggregate Window Functions: Compute aggregate values like SUM, AVG, COUNT, etc.
2. Ranking Window Functions: Assign a rank to each row, such as RANK(), DENSE_RANK(), and ROW_NUMBER().
3. Analytic Window Functions: Perform calculations like LEAD(), LAG(), FIRST_VALUE(), and LAST_VALUE().

Syntax:

SELECT column_name, 
       window_function() OVER (PARTITION BY column_name ORDER BY column_name)
FROM table_name;

Examples:

1. Using ROW_NUMBER():
Assign a unique number to each row within a partition of the result set.

   SELECT employee_name, department_id, salary,
          ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
   FROM employees;
   

This query ranks employees within each department based on their salary in descending order.

2. Using AVG() with OVER():
Calculate the average salary within each department without collapsing the result set.

   SELECT employee_name, department_id, salary,
          AVG(salary) OVER (PARTITION BY department_id) AS avg_salary
   FROM employees;
   

This query returns the average salary for each department along with each employee's salary.

3. Using LEAD():
Access the value of a subsequent row in the result set.

   SELECT employee_name, department_id, salary,
          LEAD(salary, 1) OVER (PARTITION BY department_id ORDER BY salary) AS next_salary
   FROM employees;
   

This query retrieves the salary of the next employee within the same department based on the current sorting order.

4. Using RANK():
Assign a rank to each row within the partition, with gaps in the ranking values if there are ties.

   SELECT employee_name, department_id, salary,
          RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
   FROM employees;
   

This query ranks employees within each department by their salary in descending order, leaving gaps for ties.

Tip: Window functions are powerful for performing calculations across a set of rows while retaining the individual rows. They are useful for running totals, moving averages, ranking, and accessing data from other rows within the same result set.

Go though SQL Learning Series to refresh your basics

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

Like this post if you want me to continue SQL Interview Preparation Series 👍❤️

Hope it helps :)

If you want to be a data analyst, you should work to become as good at SQL as possible.

1. SELECT

What a surprise! I need to choose what data I want to return.

2. FROM

Again, no shock here. I gotta choose what table I am pulling my data from.

3. WHERE

This is also pretty basic, but I almost always filter the data to whatever range I need and filter the data to whatever condition I’m looking for.

4. JOIN

This may surprise you that the next one isn’t one of the other core SQL clauses, but at least for my work, I utilize some kind of join in almost every query I write.

5. Calculations

This isn’t necessarily a function of SQL, but I write a lot of calculations in my queries. Common examples include finding the time between two dates and multiplying and dividing values to get what I need.

Add operators and a couple data cleaning functions and that’s 80%+ of the SQL I write on the job.

As a Data_Analyst, SQL has been important l for conducting in-depth data analysis. Here are some advanced SQL techniques that can significantly enhance your analytical capabilities:

1. Window Functions:
• Advanced Analytics: Master the use of OVER() for complex analytical tasks. Window functions are essential for calculating running totals, rankings, and performing lead-lag analysis within datasets. Explore functions like ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE() to gain nuanced insights into your data.
• Partitioning and Ordering: Learn how to partition your data and order within partitions to perform segmented calculations efficiently.

2. CTEs and Temporary Tables:
• Simplifying Complex Queries: Common Table Expressions (CTEs) and temporary tables are invaluable for breaking down and simplifying complex queries, especially when dealing with large datasets.
• Recursive CTEs: Utilize recursive CTEs for hierarchical data processing and recursive algorithms, which can be critical for tasks like organizational chart creation and graph traversal.
• Performance Considerations: Understand when to use CTEs versus temporary tables for optimal performance and resource management.

3. Dynamic SQL:
• Flexibility and Responsiveness: Learn to construct SQL queries dynamically to enhance the flexibility of your database interactions. Dynamic SQL allows you to create more adaptable and responsive applications by building queries based on variable inputs and user interactions.
• Security Best Practices: Implement best practices for securing dynamic SQL, such as using parameterized queries to prevent SQL injection attacks.

4. Query Optimization:
• Performance Tuning: Delve into advanced techniques for optimizing query performance. This includes the strategic use of indexing, query restructuring, and understanding execution plans to significantly boost efficiency.
• Indexing Strategies: Explore different types of indexes (e.g., clustered, non-clustered, covering indexes) and their appropriate use cases.
• Execution Plans: Gain expertise in reading and interpreting execution plans to identify bottlenecks and optimize query performance.

5. PIVOT and UNPIVOT:
• Data Transformation: These operations are crucial for transforming rows into columns and vice versa, making your data more accessible and analysis-friendly.
• Advanced Pivoting: Combine PIVOT and UNPIVOT with aggregate functions to summarize data dynamically. This is particularly useful for creating cross-tab reports and reshaping data for better visualization and analysis.
• Complex Transformations: Implement complex data transformations using nested PIVOT/UNPIVOT operations to handle multi-dimensional data structures effectively.