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 :)

Preparing for a SQL interview?

Focus on mastering these essential topics:

1. Joins: Get comfortable with inner, left, right, and outer joins.
Knowing when to use what kind of join is important!

2. Window Functions: Understand when to use
ROW_NUMBER, RANK(), DENSE_RANK(), LAG, and LEAD for complex analytical queries.

3. Query Execution Order: Know the sequence from FROM to
ORDER BY. This is crucial for writing efficient, error-free queries.

4. Common Table Expressions (CTEs): Use CTEs to simplify and structure complex queries for better readability.

5. Aggregations & Window Functions: Combine aggregate functions with window functions for in-depth data analysis.

6. Subqueries: Learn how to use subqueries effectively within main SQL statements for complex data manipulations.

7. Handling NULLs: Be adept at managing NULL values to ensure accurate data processing and avoid potential pitfalls.

8. Indexing: Understand how proper indexing can significantly boost query performance.

9. GROUP BY & HAVING: Master grouping data and filtering groups with HAVING to refine your query results.

10. String Manipulation Functions: Get familiar with string functions like CONCAT, SUBSTRING, and REPLACE to handle text data efficiently.

11. Set Operations: Know how to use UNION, INTERSECT, and EXCEPT to combine or compare result sets.

12. Optimizing Queries: Learn techniques to optimize your queries for performance, especially with large datasets.

If we master/ Practice in these topics we can track any SQL interviews..

Like this post if you need more 👍❤️

Hope it helps :)

Getting started with SQL comparison operators.

If you're new to SQL, understanding comparison operators is one of the first things you'll need to learn.

They’re really important for filtering and analyzing your data. Let’s break them down with some simple examples.

Comparison operators let you compare values in SQL queries. Here are the basics:
1. = (Equal To): Checks if two values are the same.
Example: SELECT * FROM Employees WHERE Age = 30; (This will find all employees who are exactly 30 years old).

2. <> or != (Not Equal To): Checks if two values are different.
Example: SELECT * FROM Employees WHERE Age <> 30; (This will find all employees who are not 30 years old).

3. > (Greater Than): Checks if a value is larger.
Example: SELECT * FROM Employees WHERE Salary > 50000; (This will list all employees earning more than 50,000).

4. < (Less Than): Checks if a value is smaller.
Example: SELECT * FROM Employees WHERE Salary < 50000; (This will show all employees earning less than 50,000).

5. >= (Greater Than or Equal To): Checks if a value is larger or equal.
Example: SELECT * FROM Employees WHERE Age >= 25; (This will find all employees who are 25 years old or older).

6. <= (Less Than or Equal To): Checks if a value is smaller or equal.
Example: SELECT * FROM Employees WHERE Age <= 30; (This will find all employees who are 30 years old or younger).

These simple operators can help you get more accurate results in your SQL queries.

Keep practicing and you’ll be great at SQL in no time.

Like this post if you need more 👍❤️

Hope it helps :)

SQL data cleaning methods you should know for Data Science:

1. Identifying Missing Data
2. Removing Duplicate Records
3. Handling Missing Data
4. Standardizing Data
5. Correcting Data Entry Errors

SQL Roadmap 👇👇
https://www.linkedin.com/posts/sql-analysts_sql-activity-7235150234706690048-ydnI

Like for more ❤️

To be honest, I also wasn't aware of this fullform until today 😂