Some frequently Asked SQL Interview Questions with Answers in data analyst interviews:

1. Write a SQL query to find the average purchase amount for each customer. Assume you have two tables: Customers (CustomerID, Name) and Orders (OrderID, CustomerID, Amount).

SELECT c.CustomerID, c. Name, AVG(o.Amount) AS AveragePurchase
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c. Name;

2. Write a query to find the employee with the minimum salary in each department from a table Employees with columns EmployeeID, Name, DepartmentID, and Salary.

SELECT e1.DepartmentID, e1.EmployeeID, e1 .Name, e1.Salary
FROM Employees e1
WHERE Salary = (SELECT MIN(Salary) FROM Employees e2 WHERE e2.DepartmentID = e1.DepartmentID);

3. Write a SQL query to find all products that have never been sold. Assume you have a table Products (ProductID, ProductName) and a table Sales (SaleID, ProductID, Quantity).

SELECT p.ProductID, p.ProductName
FROM Products p
LEFT JOIN Sales s ON p.ProductID = s.ProductID
WHERE s.ProductID IS NULL;

4. Given a table Orders with columns OrderID, CustomerID, OrderDate, and a table OrderItems with columns OrderID, ItemID, Quantity, write a query to find the customer with the highest total order quantity.

SELECT o.CustomerID, SUM(oi.Quantity) AS TotalQuantity
FROM Orders o
JOIN OrderItems oi ON o.OrderID = oi.OrderID
GROUP BY o.CustomerID
ORDER BY TotalQuantity DESC
LIMIT 1;

5. Write a SQL query to find the earliest order date for each customer from a table Orders (OrderID, CustomerID, OrderDate).

SELECT CustomerID, MIN(OrderDate) AS EarliestOrderDate
FROM Orders
GROUP BY CustomerID;

6. Given a table Employees with columns EmployeeID, Name, ManagerID, write a query to find the number of direct reports for each manager.

SELECT ManagerID, COUNT(*) AS NumberOfReports
FROM Employees
WHERE ManagerID IS NOT NULL
GROUP BY ManagerID;


7. Given a table Customers with columns CustomerID, Name, JoinDate, and a table Orders with columns OrderID, CustomerID, OrderDate, write a query to find customers who placed their first order within the last 30 days.

SELECT c.CustomerID, c. Name
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE o.OrderDate = (SELECT MIN(o2.OrderDate) FROM Orders o2 WHERE o2.CustomerID = c.CustomerID)
AND o.OrderDate >= CURRENT_DATE - INTERVAL '30 day';

Hope it helps :)

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

Here are the links to the SQL series

Complete SQL Topics for Data Analyst: https://t.me/sqlspecialist/523

Part-1: https://t.me/sqlspecialist/524

Part-2: https://t.me/sqlspecialist/525

Part-3: https://t.me/sqlspecialist/526

Part-4: https://t.me/sqlspecialist/527

Part-5: https://t.me/sqlspecialist/529

Part-6: https://t.me/sqlspecialist/534

Part-7: https://t.me/sqlspecialist/534

Part-8: https://t.me/sqlspecialist/536

Part-9: https://t.me/sqlspecialist/537

Part-10: https://t.me/sqlspecialist/539

Part-11: https://t.me/sqlspecialist/540

Part-12:
https://t.me/sqlspecialist/541

Part-13: https://t.me/sqlspecialist/542

Part-14: https://t.me/sqlspecialist/544

Part-15: https://t.me/sqlspecialist/545

Part-16: https://t.me/sqlspecialist/546

Part-17: https://t.me/sqlspecialist/549

Part-18: https://t.me/sqlspecialist/552

Part-19: https://t.me/sqlspecialist/555

Part-20: https://t.me/sqlspecialist/556

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 Python Topics for Data Analysts: https://t.me/sqlspecialist/548

Complete Excel Topics for Data Analysts: https://t.me/sqlspecialist/547

I'll continue with learning series on Python, Power BI, Excel & Tableau.

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

Hope it helps :)

Complete topics & subtopics of SQL for Data Analyst role:-

1. SQL Fundamentals

A. SQL Basics
• SQL Keywords and Syntax
• Data Types (Numeric, String, Date/Time, etc.)
• Operators (Arithmetic, Comparison, Logical)

B. Core SQL Statements
• SELECT
• INSERT
• UPDATE
• DELETE

2. Database Design and Schema

A. Data Definition Language (DDL)
• CREATE TABLE
• ALTER TABLE
• DROP TABLE
• TRUNCATE TABLE

B. Data Constraints
• Primary Key
• Foreign Key
• Unique
• NOT NULL
• CHECK

3. Querying and Data Manipulation

A. Data Manipulation Language (DML)
• SELECT Clauses (SELECT, FROM, WHERE)
• Sorting and Filtering (ORDER BY, GROUP BY, HAVING)
• JOIN Operations (INNER, LEFT, RIGHT, FULL OUTER, SELF, CROSS)
• INSERT, UPDATE, DELETE Operations

B. Aggregate Functions and Grouping
• Functions (SUM, AVG, COUNT, MIN, MAX)
• GROUP BY and HAVING Clauses

4. Advanced Querying Techniques

A. Joins and Subqueries
• Types of Joins and Their Use Cases
• Subqueries (Scalar, Column, Row, Table)
• Nested and Correlated Subqueries

B. Advanced SQL Functions
• String Functions (CONCAT, LENGTH, SUBSTRING, REPLACE, UPPER, LOWER)
• Date/Time Functions (DATE, TIME, TIMESTAMP, DATEPART, DATEADD)
• Numeric Functions (ROUND, CEILING, FLOOR, ABS, MOD)
• Conditional Functions (CASE, COALESCE, NULLIF)

5. Views and Indexes

A. Views
• Creating and Managing Views
• Modifying and Dropping Views

B. Indexes
• Types of Indexes (Single Column, Composite)
• Creating and Using Indexes for Optimization

6. Security and Data Integrity

A. Data Integrity
• Referential and Entity Integrity
• Enforcing Data Constraints

B. Security Management
• GRANT and REVOKE Permissions
• Best Practices for Database Security

7. Stored procedure and functions

A. Stored Procedures
• Creating, Modifying, and Executing Stored Procedures
• Benefits and Use Cases

B. Functions
• User-Defined Functions
• Using Functions in Queries

8. Performance Optimization

A. Query Optimization Techniques
• Index Usage
• Optimizing Joins and Subqueries
• Execution Plans and Query Analysis

B. Performance Tuning Best Practices
• Avoiding Common Pitfalls
• Regular Maintenance and Updates

9. Advanced SQL Features

A. Complex Query Techniques
• Recursive Queries
• Pivot and Unpivot Operations
• Window Functions (ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG)

B. Common Table Expressions (CTEs) and Dynamic SQL
• Using CTEs for Improved Readability
• Implementing Dynamic SQL for Flexible Queries

10. Practical Applications and Case Studies

• Real-World SQL Scenarios
• Data Analysis Case Studies
• Problem-Solving with SQL


Hope it helps :)

For a data analytics interview, focusing on key SQL topics can be crucial. Here's a list of last-minute SQL topics to revise:

1. SQL Basics:
• SELECT statements: Syntax, SELECT DISTINCT
• WHERE clause: Conditions and operators (>, <, =, LIKE, IN, BETWEEN)
• ORDER BY clause: Sorting results
• LIMIT clause: Limiting the number of rows returned

2. Joins:
• INNER JOIN
• LEFT (OUTER) JOIN
• RIGHT (OUTER) JOIN
• FULL (OUTER) JOIN
• CROSS JOIN
• Understanding join conditions and scenarios for each type of join

3. Aggregation and Grouping:
• GROUP BY clause
• HAVING clause: Filtering grouped results
• Aggregate functions: COUNT, SUM, AVG, MIN, MAX

4. Subqueries:
• Nested subqueries: Using subqueries in SELECT, FROM, WHERE, and HAVING clauses
• Correlated subqueries

5. Common Table Expressions (CTEs):
• Syntax and use cases for CTEs (WITH clause)

6. Window Functions:
• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• LEAD() and LAG()
• PARTITION BY clause

7. Data Manipulation:
• INSERT, UPDATE, DELETE statements
• Understanding transaction control with COMMIT and ROLLBACK

8. Data Definition:
• CREATE TABLE
• ALTER TABLE
• DROP TABLE
• Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL

9. Indexing:
• Purpose and types of indexes
• How indexing affects query performance

10. Performance Optimization:
• Understanding query execution plans
• Identifying and resolving common performance issues

11. SQL Functions:
• String functions: CONCAT, SUBSTRING, LENGTH
• Date functions: DATEADD, DATEDIFF, GETDATE
• Mathematical functions: ROUND, CEILING, FLOOR

12. Stored Procedures and Triggers:
• Basics of writing and using stored procedures
• Basics of writing and using triggers

13. ETL (Extract, Transform, Load):
• Understanding the process and SQL's role in ETL operations

14. Advanced Topics (if time permits):
• Understanding complex data types (JSON, XML)
• Working with large datasets and big data considerations

Hope it helps :)

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.

Like this post if you need more 👍❤️

Hope it helps :)

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;

Hope it helps :)

Why SQL Still Rules the Data World?



SQL + Relational Databases = Structured Data Management

SQL + Joins = Seamless Data Integration

SQL + Aggregations = Powerful Data Summarization

SQL + Subqueries = Complex Data Retrieval

SQL + Indexing = Faster Query Performance

SQL + Transactions = Reliable Data Integrity

SQL + Views = Simplified Data Access

SQL + Stored Procedures = Efficient Data Operations

SQL + Triggers = Automated Actions Based on Data Changes

SQL + Constraints = Data Validation and Integrity

SQL + Normalization = Eliminate Redundancy

SQL + Data Warehousing = Scalable Data Storage Solutions

SQL + Data Lakes = Manage Vast Amounts of Raw Data

SQL + ETL Processes = Efficient Data Transformation

SQL + Backup and Recovery = Secure Data Management

SQL + Big Data Integration = Bridging SQL and NoSQL

SQL + Reporting Tools = Generating Insightful Reports

SQL + BI Tools = Business Intelligence Integration

SQL + Analytics = Deep Data Insights

SQL remains unbeatable with its ability to manage, query, and analyze data efficiently.

Hope it helps :)

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.

Hope it helps :)

One of the favorite topics in SQL interviews is inventory management.

Here is a good example:

𝗧𝗵𝗲 𝗾𝘂𝗲𝘀𝘁𝗶𝗼𝗻:

Imagine you are helping the Inventory department to identify high or low stock levels at specific locations.


𝗧𝗼 𝘀𝗼𝗹𝘃𝗲 𝘁𝗵𝗲 𝗾𝘂𝗲𝘀𝘁𝗶𝗼𝗻, 𝘆𝗼𝘂 𝗻𝗲𝗲𝗱 𝘁𝗼:

• Establish a threshold to define high/low (we will use 10 units for this example).
• Calculate the stock level of each product
• Calculate the average stock level across all stores.

It sounds simple, but think again: You can't use a GROUP BY because you need the details for each product per store.


𝗧𝗵𝗲 𝘀𝗶𝗺𝗽𝗹𝗲𝘀𝘁 𝘄𝗮𝘆 𝘁𝗼 𝘀𝗼𝗹𝘃𝗲 𝘁𝗵𝗶𝘀 𝗽𝗿𝗼𝗯𝗹𝗲𝗺 𝗶𝘀 𝘂𝘀𝗶𝗻𝗴 𝗣𝗔𝗥𝗧𝗜𝗧𝗜𝗢𝗡 𝗕𝗬.

The PARTITION BY clause in SQL divides the result set into partitions or "windows" based on one or more columns.

Each partition is treated separately for calculations performed by a window function.

Unlike GROUP BY, it does not aggregate the data into a single row per group, meaning you can still see all the original rows in the output.


𝗪𝗵𝗮𝘁 𝗵𝗮𝗽𝗽𝗲𝗻𝗲𝗱 𝗶𝗻 𝘁𝗵𝗶𝘀 𝗾𝘂𝗲𝗿𝘆:

• The query calculates the average stock level for each product across all stores using the PARTITION BY clause. This clause groups the data by ProductId to ensure the average is calculated per product.

• These average stock levels are stored temporarily using a Common Table Expression (CTE).

• The query checks each store's stock level against the average within its product group.

• The final result shows each store's stock level, the average stock level for that product (calculated using PARTITION BY), and whether the stock is 'High', 'Low', or 'Normal'.


PARTITION BY is like a GROUP BY, but you keep the details.😉

Hope it helps :)

Key SQL Commands:

➡️ SELECT: Retrieves data from one or more tables.
➡️ FROM: Specifies the table(s) to query.
➡️ WHERE: Filters results based on conditions.
➡️ GROUP BY: Groups rows that share a value in specified columns.
➡️ ORDER BY: Sorts results in ascending or descending order.
➡️ JOIN: Combines rows from multiple tables based on related columns.
➡️ UNION: Merges the results of two or more SELECT statements.
➡️ LIMIT: Restricts the number of rows returned.
➡️ INSERT INTO: Adds new records to a table.
➡️ UPDATE: Modifies existing records.
➡️ DELETE: Removes records from a table.

Understanding SQL Command Types:

Data Definition Language (DDL):
➡️ CREATE: Generates new database objects like tables, indexes, and views.
➡️ ALTER: Changes the structure of existing database objects.
➡️ DROP: Deletes database objects permanently.
➡️ TRUNCATE: Erases all records from a table but keeps its structure intact.
➡️ RENAME: Changes the name of a database object.

Data Manipulation Language (DML):
➡️ INSERT: Adds new data into a table.
➡️ UPDATE: Updates existing data within a table.
➡️ DELETE: Deletes existing data from a table.
➡️ MERGE: Conditionally inserts or updates data.

Data Control Language (DCL):
➡️ GRANT: Assigns access privileges to users.
➡️ REVOKE: Removes access privileges from users.

Transaction Control Language (TCL):
➡️ COMMIT: Saves the changes made by a transaction.
➡️ ROLLBACK: Reverses the changes made by a transaction.
➡️ SAVEPOINT: Sets a point within a transaction to which you can rollback.

Data Query Language (DQL):
➡️ SELECT: Fetches data from the database.

Hope it helps :)

𝗗𝗶𝗱 𝘆𝗼𝘂 𝗸𝗻𝗼𝘄 𝘁𝗵𝗮𝘁 𝘂𝗻𝗱𝗲𝗿𝘀𝘁𝗮𝗻𝗱𝗶𝗻𝗴 𝗦𝗤𝗟’𝘀 𝗲𝘅𝗲𝗰𝘂𝘁𝗶𝗼𝗻 𝗼𝗿𝗱𝗲𝗿 𝗰𝗮𝗻 𝘀𝗶𝗴𝗻𝗶𝗳𝗶𝗰𝗮𝗻𝘁𝗹𝘆 𝗶𝗺𝗽𝗿𝗼𝘃𝗲 𝘁𝗵𝗲 𝗲𝗳𝗳𝗶𝗰𝗶𝗲𝗻𝗰𝘆 𝗼𝗳 𝘆𝗼𝘂𝗿 𝗾𝘂𝗲𝗿𝗶𝗲𝘀?

Most people focus on writing SQL statements but often overlook the importance of how SQL processes these statements.

Let’s break down the SQL execution order:

• FROM - Choose and join tables to get the base data.
• WHERE - Filters the base data to meet specific conditions.
• GROUP BY - Aggregates the base data into groups.
• HAVING - Filters the aggregated data to refine your results.
• SELECT - Returns the final data, with only the needed columns.
• ORDER BY - Sorts the final data based on your specified criteria.
• LIMIT - Limits the returned data to a specific row count.

Understanding these steps will help you optimize your queries and get the results you need more efficiently!

If you are ready to improve your SQL skills, explore each step and see how mastering the execution order can make your queries more powerful.

Like this post if you need more 👍❤️

Hope it helps :)

Did You Know? SQL has been around for over 40 years and is still the go-to language for database management

50 interview SQL questions, including both technical and non-technical questions, along with their answers PART-1

1. What is SQL?
- Answer: SQL (Structured Query Language) is a standard programming language specifically designed for managing and manipulating relational databases.

2. What are the different types of SQL statements?
- Answer: SQL statements can be classified into DDL (Data Definition Language), DML (Data Manipulation Language), DCL (Data Control Language), and TCL (Transaction Control Language).

3. What is a primary key?
- Answer: A primary key is a field (or combination of fields) in a table that uniquely identifies each row/record in that table.

4. What is a foreign key?
- Answer: A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table or the same table. It establishes a link between the data in two tables.

5. What are joins? Explain different types of joins.
- Answer: A join is an SQL operation for combining records from two or more tables. Types of joins include INNER JOIN, LEFT JOIN (or LEFT OUTER JOIN), RIGHT JOIN (or RIGHT OUTER JOIN), and FULL JOIN (or FULL OUTER JOIN).

6. What is normalization?
- Answer: Normalization is the process of organizing data to reduce redundancy and improve data integrity. This typically involves dividing a database into two or more tables and defining relationships between them.

7. What is denormalization?
- Answer: Denormalization is the process of combining normalized tables into fewer tables to improve database read performance, sometimes at the expense of write performance and data integrity.

8. What is stored procedure?
- Answer: A stored procedure is a prepared SQL code that you can save and reuse. So, if you have an SQL query that you write frequently, you can save it as a stored procedure and then call it to execute it.

9. What is an index?
- Answer: An index is a database object that improves the speed of data retrieval operations on a table at the cost of additional storage and maintenance overhead.

10. What is a view in SQL?
- Answer: A view is a virtual table based on the result set of an SQL query. It contains rows and columns, just like a real table, but does not physically store the data.

11. What is a subquery?
- Answer: A subquery is an SQL query nested inside a larger query. It is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

12. What are aggregate functions in SQL?
- Answer: Aggregate functions perform a calculation on a set of values and return a single value. Examples include COUNT, SUM, AVG (average), MIN (minimum), and MAX (maximum).

13. Difference between DELETE and TRUNCATE?
- Answer: DELETE removes rows one at a time and logs each delete, while TRUNCATE removes all rows in a table without logging individual row deletions. TRUNCATE is faster but cannot be rolled back.

14. What is a UNION in SQL?
- Answer: UNION is an operator used to combine the result sets of two or more SELECT statements. It removes duplicate rows between the various SELECT statements.

15. What is a cursor in SQL?
- Answer: A cursor is a database object used to retrieve, manipulate, and navigate through a result set one row at a time.

16. What is trigger in SQL?
- Answer: A trigger is a set of SQL statements that automatically execute or "trigger" when certain events occur in a database, such as INSERT, UPDATE, or DELETE.

17. Difference between clustered and non-clustered indexes?
- Answer: A clustered index determines the physical order of data in a table and can only be one per table. A non-clustered index, on the other hand, creates a logical order and can be many per table.

18. Explain the term ACID.
- Answer: ACID stands for Atomicity, Consistency, Isolation, and Durability.


Hope it helps :)

SQL 💪

How to do JOINs in SQL 👇


𝟭/ 𝗦𝘁𝗮𝗿𝘁 𝘄𝗶𝘁𝗵 𝗱𝗲𝗳𝗶𝗻𝗶𝗻𝗴 𝘆𝗼𝘂𝗿 𝗱𝗲𝘀𝗶𝗿𝗲𝗱 𝗼𝘂𝘁𝗽𝘂𝘁 𝘁𝗮𝗯𝗹𝗲.

I like to work backwards from the output. It's always helpful to know 𝘸𝘩𝘦𝘳𝘦 you're going, before starting.

↳ Identify the columns that you need
↳ Determine the level of granularity of the table

𝟮/ 𝗜𝗱𝗲𝗻𝘁𝗶𝗳𝘆 𝘁𝗵𝗲 𝘀𝗼𝘂𝗿𝗰𝗲 𝘁𝗮𝗯𝗹𝗲𝘀 𝘄𝗶𝘁𝗵 𝘁𝗵𝗲 𝗶𝗻𝗳𝗼𝗿𝗺𝗮𝘁𝗶𝗼𝗻.

You likely won't need all the tables at your disposal. Find the tables with the relevant columns you need.

𝟯/ 𝗨𝗻𝗱𝗲𝗿𝘀𝘁𝗮𝗻𝗱 𝘁𝗵𝗲 𝗿𝗲𝗹𝗮𝘁𝗶𝗼𝗻𝘀𝗵𝗶𝗽𝘀 𝗯𝗲𝘁𝘄𝗲𝗲𝗻 𝘁𝗵𝗲𝘀𝗲 𝘁𝗮𝗯𝗹𝗲𝘀.

↳ Identify columns that link the tables together.
↳ If needed, convert these columns to compatible data types.

𝟰/ 𝗖𝗵𝗼𝗼𝘀𝗲 𝘁𝗵𝗲 𝗮𝗽𝗽𝗿𝗼𝗽𝗿𝗶𝗮𝘁𝗲 𝘁𝘆𝗽𝗲 𝗼𝗳 𝗝𝗢𝗜𝗡.

Decide if you need:
↳ All rows from one table: LEFT/RIGHT JOIN
↳ Only matching rows: INNER JOIN
↳ Unmatched rows from both tables: FULL OUTER JOIN

𝟱/ 𝗪𝗿𝗶𝘁𝗲 𝘁𝗵𝗲 𝗝𝗢𝗜𝗡 𝗰𝗹𝗮𝘂𝘀𝗲.

The first part of any SQL query I write: the FROM and JOIN clauses.

↳ Start FROM the base table that contains most of your required data.
↳ Add JOIN clauses to bring in additional data from other tables.
↳ Use ON to define the column(s) to join on.

𝟲/ 𝗦𝗘𝗟𝗘𝗖𝗧 𝘁𝗵𝗲 𝗰𝗼𝗹𝘂𝗺𝗻𝘀 𝘁𝗵𝗮𝘁 𝘆𝗼𝘂 𝗻𝗲𝗲𝗱.

Remember, the fewer columns you select, the more efficient your query is!

↳ List out all the columns you need in your SELECT statement.
↳ Use table aliases to state which table each column comes from.

𝟳/ 𝗔𝗱𝗱 𝗻𝗲𝗰𝗲𝘀𝘀𝗮𝗿𝘆 𝗳𝗶𝗹𝘁𝗲𝗿𝘀.

Include WHERE clauses to filter your data as needed.

𝟴/ 𝗥𝗲𝘃𝗶𝗲𝘄 𝘆𝗼𝘂𝗿 𝗾𝘂𝗲𝗿𝘆.

𝘛𝘩𝘦 𝘮𝘰𝘴𝘵 𝘪𝘮𝘱𝘰𝘳𝘵𝘢𝘯𝘵 𝘴𝘵𝘦𝘱! Check if your query returns the expected results.

Hope it helps :)

Essential SQL Topics for Data Analyst

Introduction to Databases

Fundamentals of databases and Database Management Systems (DBMS)
Basic SQL syntax and structure

Retrieving Data

Using the SELECT statement
Filtering data with the WHERE clause
Sorting results using ORDER BY
Limiting output with LIMIT (MySQL) or TOP (SQL Server)

Basic SQL Functions

Utilizing COUNT, SUM, AVG, MIN, and MAX

Data Types

Numeric, character, date, and time data types

Joining Tables

INNER JOIN
LEFT JOIN (or LEFT OUTER JOIN)
RIGHT JOIN (or RIGHT OUTER JOIN)
FULL JOIN (or FULL OUTER JOIN)
CROSS JOIN
Self JOIN

Advanced Data Filtering

Using IN and NOT IN
Applying BETWEEN for range filtering
Using LIKE with wildcards
Handling NULL values with IS NULL and IS NOT NULL

Grouping and Aggregation

GROUP BY clause
Filtering groups with HAVING

Subqueries

Subqueries in the SELECT clause
Subqueries in the WHERE clause
Derived tables using subqueries in the FROM clause
Correlated subqueries
Set Operations

Combining results with UNION

UNION ALL for combining results including duplicates
INTERSECT for common elements
EXCEPT (or MINUS) for differences

Window Functions

Using ROW_NUMBER
RANK and DENSE_RANK
NTILE for distributing rows
LEAD and LAG for accessing prior or subsequent rows
Aggregate functions as window functions (SUM, AVG, COUNT)

Common Table Expressions (CTEs)

Using the WITH clause
Creating recursive CTEs

Stored Procedures and Functions

Creating and utilizing stored procedures
Creating and utilizing user-defined functions

Views

Creating and managing views
Using indexed views (materialized views)

Indexing

Creating indexes
Understanding clustered versus non-clustered indexes
Maintaining indexes

Transactions

Controlling transactions with BEGIN, COMMIT, and ROLLBACK
Performance Optimization


Hope it helps :)

𝗔𝗿𝗲 𝗬𝗼𝘂 𝗦𝗸𝗶𝗽𝗽𝗶𝗻𝗴 𝗧𝗵𝗶𝘀 𝗜𝗺𝗽𝗼𝗿𝘁𝗮𝗻𝘁 𝗦𝘁𝗲𝗽 𝗪𝗵𝗲𝗻 𝗪𝗿𝗶𝘁𝗶𝗻𝗴 𝗦𝗤𝗟 𝗤𝘂𝗲𝗿𝗶𝗲𝘀?

𝗧𝗵𝗶𝗻𝗸 𝘆𝗼𝘂𝗿 𝗦𝗤𝗟 𝗾𝘂𝗲𝗿𝗶𝗲𝘀 𝗮𝗿𝗲 𝗲𝗳𝗳𝗶𝗰𝗶𝗲𝗻𝘁? 𝗬𝗼𝘂 𝗺𝗶𝗴𝗵𝘁 𝗯𝗲 𝘀𝗸𝗶𝗽𝗽𝗶𝗻𝗴 𝘁𝗵𝗶𝘀!

Hi everyone! Writing SQL queries can be tricky, especially if you forget to include one key part: indexing.

When I first started writing SQL queries, I didn’t pay much attention to indexing. My queries worked, but they took way longer to run.

Here’s why indexing is so important:

- 𝗪𝗵𝗮𝘁 𝗜𝘀 𝗜𝗻𝗱𝗲𝘅𝗶𝗻𝗴?: Indexing is like creating a shortcut for your database to find the data you need faster. Without it, your database might have to scan through all the data, making your queries slow.

- 𝗪𝗵𝘆 𝗜𝘁 𝗠𝗮𝘁𝘁𝗲𝗿𝘀: If your query takes too long, it can slow down your entire system. Adding the right indexes helps your queries run faster and more efficiently.

- 𝗛𝗼𝘄 𝘁𝗼 𝗨𝘀𝗲 𝗜𝗻𝗱𝗲𝘅𝗲𝘀: When you create a table, consider which columns are used often in WHERE clauses or JOIN conditions. Index those columns to speed up your queries.

Indexing is a simple step that can make a big difference in performance. Don’t skip it!

Hope it helps :)

Window Functions 🪟🔍

🔍 Section 1: Introduction to Window Functions
- Understand the concept of window functions as a way to perform calculations across a set of rows related to the current row.
- Learn how window functions differ from aggregate functions and standard SQL functions.

SELECT column1, column2, SUM(column3) OVER (PARTITION BY column1 ORDER BY column2) AS running_total
FROM table_name;

🔍 Section 2: Common Window Functions
- Explore commonly used window functions, including ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE().
- Understand the syntax and usage of each window function for different analytical purposes.

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

🔍 Section 3: Partitioning Data
- Learn how to partition data using window functions to perform calculations within specific groups.
- Understand the significance of the PARTITION BY clause in window function syntax.

SELECT column1, column2, AVG(column3) OVER (PARTITION BY column1) AS avg_column3
FROM table_name;

🔍 Section 4: Ordering Results
- Explore techniques for ordering results within window functions to control the calculation scope.
- Understand the impact of the ORDER BY clause on window function behavior.

SELECT column1, column2, MAX(column3) OVER (ORDER BY column1 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS max_window
FROM table_name;

🔍 Section 5: Advanced Analytical Capabilities
- Discover advanced analytical capabilities enabled by window functions, such as cumulative sums, moving averages, and percentile rankings.
- Explore real-world scenarios where window functions can provide valuable insights into data trends and patterns.

SELECT column1, column2, AVG(column3) OVER (ORDER BY column1 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM table_name;

Like this post if you need more 👍❤️

Hope it helps :)

𝐒𝐨𝐦𝐞 𝐁𝐞𝐬𝐭 𝐩𝐫𝐚𝐜𝐭𝐢𝐜𝐞𝐬 𝐭𝐨 𝐡𝐞𝐥𝐩 𝐲𝐨𝐮 𝐨𝐩𝐭𝐢𝐦𝐢𝐳𝐞 𝐲𝐨𝐮𝐫 𝐒𝐐𝐋 𝐪𝐮𝐞𝐫𝐢𝐞𝐬:

1. Simplify Joins

• Decompose complex joins into simpler, more manageable queries when possible.
• Index columns that are used as foreign keys in joins to enhance join performance.

2. Query Structure Optimization

• Apply WHERE clauses as early as possible to filter out rows before they are processed further.
• Utilize LIMIT or TOP clauses to restrict the number of rows returned, which can significantly reduce processing time.

3. Partition Large Tables

• Divide large tables into smaller, more manageable partitions.
• Ensure that each partition is properly indexed to maintain query performance.

4. Optimize SELECT Statements

• Limit the columns in your SELECT clause to only those you need. Avoid using SELECT * to prevent unnecessary data retrieval.
• Prefer using EXISTS over IN for subqueries to improve query performance.

5. Use Temporary Tables Wisely

• Temporary Tables: Use temporary tables to save intermediate results when you have a complex query. This helps break down a complicated query into simpler steps, making it easier to manage and faster to run.

6. Optimize Table Design

• Normalize your database schema to eliminate redundant data and improve consistency.
• Consider denormalization for read-heavy systems to reduce the number of joins needed.

7. Avoid Correlated Subqueries

• Replace correlated subqueries with joins or use derived tables to improve performance.
• Correlated subqueries can be very inefficient as they are executed multiple times.

8. Use Stored Procedures:

• Put complicated database tasks into stored procedures. These are pre-written sets of instructions saved in the database. They make your queries run faster because the database doesn’t have to figure out how to execute them each time

Like this post if you need more 👍❤️

Hope it helps :)