SQL Joins Explanation ♥️

JOINS

Definition

Joins in MySQL allow you to retrieve data from two or more tables based on a related column. They are used to combine rows from multiple tables.

Types of Joins

1. INNER JOIN:
- Returns rows where there is a match in both tables.
- Syntax:

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

- Example:

            SELECT employees.name, departments.name
            FROM employees
            INNER JOIN departments
            ON employees.department_id = departments.id;
            

2. LEFT JOIN (OUTER JOIN):
- Returns all rows from the left table and matching rows from the right table. Non-matching rows have NULL.
- Example:

            SELECT employees.name, departments.name
            FROM employees
            LEFT JOIN departments
            ON employees.department_id = departments.id;
            

3. RIGHT JOIN (OUTER JOIN):
- Returns all rows from the right table and matching rows from the left table. Non-matching rows have NULL.
- Example:

            SELECT employees.name, departments.name
            FROM employees
            RIGHT JOIN departments
            ON employees.department_id = departments.id;         

4. FULL OUTER JOIN:
- Returns all rows from both tables, matching where possible. Not natively supported in MySQL, but can be simulated using UNION.
- Example:

            SELECT employees.name, departments.name
            FROM employees
            LEFT JOIN departments
            ON employees.department_id = departments.id
            
            UNION
            
            SELECT employees.name, departments.name
            FROM employees
            RIGHT JOIN departments
            ON employees.department_id = departments.id;

5. CROSS JOIN:
- Returns the Cartesian product of both tables.
- Example:

            SELECT employees.name, departments.name
            FROM employees
            CROSS JOIN departments;

Interview Questions

1. What is the difference between INNER JOIN and OUTER JOIN?
- INNER JOIN only includes rows with matches in both tables, while OUTER JOIN includes unmatched rows.
2. How can you simulate a FULL OUTER JOIN in MySQL?
- Use UNION of LEFT JOIN and RIGHT JOIN.
3. What is a Cartesian product, and when does it occur?
- A Cartesian product occurs in a CROSS JOIN or when no ON condition is specified, resulting in all possible row combinations.

1. What is a Self-Join?

A self-join is a type of join that can be used to connect two tables. As a result, it is a unary relationship. Each row of the table is attached to itself and all other rows of the same table in a self-join. As a result, a self-join is mostly used to combine and compare rows from the same database table.


2. What is OLTP?

OLTP, or online transactional processing, allows huge groups of people to execute massive amounts of database transactions in real time, usually via the internet. A database transaction occurs when data in a database is changed, inserted, deleted, or queried.


3. What is the difference between joining and blending in Tableau?

Joining term is used when you are combining data from the same source, for example, worksheet in an Excel file or tables in Oracle databaseWhile blending requires two completely defined data sources in your report.


4. How to prevent someone from copying the cell from your worksheet in excel?

If you want to protect your worksheet from being copied, go into Menu bar > Review > Protect sheet > Password.

By entering password you can prevent your worksheet from getting copied.

Join Most Useful Telegram channel for data analysts
👇👇
https://t.iss.one/sqlspecialist

Quick Recap of Essential SQL Concepts

1️⃣ FROM clause: Specifies the tables from which data will be retrieved.
2️⃣ WHERE clause: Filters rows based on specified conditions.
3️⃣ GROUP BY clause: Groups rows that have the same values into summary rows.
4️⃣ HAVING clause: Filters groups based on specified conditions.
5️⃣ SELECT clause: Specifies the columns to be retrieved.
6️⃣ WINDOW functions: Functions that perform calculations across a set of table rows.
7️⃣ AGGREGATE functions: Functions like COUNT, SUM, AVG that perform calculations on a set of values.
8️⃣ UNION / UNION ALL: Combines the result sets of multiple SELECT statements.
9️⃣ ORDER BY clause: Sorts the result set based on specified columns.
🔟 LIMIT / OFFSET (or FETCH / OFFSET in some databases): Controls the number of rows returned and starting point for retrieval.

Basic SQL Commands

Practise these 5 intermediate SQL interview questions today!

1. Write a SQL query for cumulative sum of salary of each employee from Jan to July. (Column name – Emp_id, Month, Salary).

2. Write a SQL query to display year on year growth for each product. (Column name – transaction_id, Product_id, transaction_date, spend). Output will have year, product_id & yoy_growth.

3. Write a SQL query to find the numbers which consecutively occurs 3 times. (Column name – id, numbers)

4. Write a SQL query to find the days when temperature was higher than its previous dates. (Column name – Days, Temp)

5. Write a SQL query to find the nth highest salary from the table emp. (Column name – id, salary)

SQL Joins

SQL Resources
👇👇
https://t.iss.one/mysqldata

Free access for next few days

Best way to prepare for a SQL interviews 👇👇

1. Review Basic Concepts: Ensure you understand fundamental SQL concepts like SELECT statements, JOINs, GROUP BY, and WHERE clauses.

2. Practice SQL Queries: Work on writing and executing SQL queries. Practice retrieving, updating, and deleting data.

3. Understand Database Design: Learn about normalization, indexes, and relationships to comprehend how databases are structured.

4. Know Your Database: If possible, find out which database system the company uses (e.g., MySQL, PostgreSQL, SQL Server) and familiarize yourself with its specific syntax.

5. Data Types and Constraints: Understand various data types and constraints such as PRIMARY KEY, FOREIGN KEY, and UNIQUE constraints.

6. Stored Procedures and Functions: Learn about stored procedures and functions, as interviewers may inquire about these.

7. Data Manipulation Language (DML): Be familiar with INSERT, UPDATE, and DELETE statements.

8. Data Definition Language (DDL): Understand statements like CREATE, ALTER, and DROP for database and table management.

9. Normalization and Optimization: Brush up on database normalization and optimization techniques to demonstrate your understanding of efficient database design.

10. Troubleshooting Skills: Be prepared to troubleshoot queries, identify errors, and optimize poorly performing queries.

11. Scenario-Based Questions: Practice answering scenario-based questions. Understand how to approach problems and design solutions.

12. Latest Trends: Stay updated on the latest trends in database technologies and SQL best practices.

13. Review Resume Projects: If you have projects involving SQL on your resume, be ready to discuss them in detail.

14. Mock Interviews: Conduct mock interviews with a friend or use online platforms to simulate real interview scenarios.

15. Ask Questions: Prepare questions to ask the interviewer about the company's use of databases and SQL.

Best Resources to learn SQL 👇

SQL Topics for Data Analysts

SQL Udacity Course

Download SQL Cheatsheet

SQL Interview Questions

Learn & Practice SQL

Also try to apply what you learn through hands-on projects or challenges.

Please give us credits while sharing: -> https://t.iss.one/free4unow_backup

ENJOY LEARNING 👍👍

✍ Mastering Conditional Aggregation in SQL: A Quick Guide

Conditional aggregation is a powerful SQL technique that lets you perform aggregate functions based on specific conditions. This approach allows you to calculate values more selectively, adding flexibility to your data analysis. Let's break it down with examples to see how you can leverage this method in SQL.

📍 What Is Conditional Aggregation?
Standard aggregation functions like SUM(), COUNT(), and AVG() summarize data across rows without any distinction. However, there are times when you only want to aggregate data that meets certain conditions. Conditional aggregation helps with that by applying aggregate functions based on specific criteria.

📍 Example: Sales Data Analysis
Let’s say you have a table called `sales` with information on store sales: store ID (`stor_id`), quantity sold (`qty`), and order date (`ord_date`). You want to calculate total sales for each store in the year 1993.

📍Example 1: Total Sales in 1993

SELECT stor_id,
       SUM(CASE WHEN YEAR(ord_date) = 1993 THEN qty ELSE 0 END) AS total_sales
FROM sales
GROUP BY stor_id
ORDER BY total_sales DESC;

Here, the SUM() function aggregates only the sales data from 1993 by using a CASE statement. Rows from other years contribute 0 to the total.

#### Example 2: Average Monthly Sales in 1993

SELECT stor_id, MONTH(ord_date) AS month,
       AVG(CASE WHEN YEAR(ord_date) = 1993 THEN qty ELSE 0 END) AS avg_sales
FROM sales
WHERE YEAR(ord_date) = 1993
GROUP BY stor_id, month
ORDER BY stor_id;

In this query, we calculate the average monthly sales for each store in 1993. The AVG() function works conditionally by including only rows from that year. We use WHERE to filter out irrelevant data, focusing on the year 1993.

📍 Example 3: Categorizing Sales

SELECT stor_id,
       SUM(CASE WHEN YEAR(ord_date) = 1993 THEN qty ELSE 0 END) AS total_sales_1993,
       CASE
           WHEN SUM(CASE WHEN YEAR(ord_date) = 1993 THEN qty ELSE 0 END) < 1000 THEN 'Low Sales'
           WHEN SUM(CASE WHEN YEAR(ord_date) = 1993 THEN qty ELSE 0 END) BETWEEN 1000 AND 5000 THEN 'Medium Sales'
           ELSE 'High Sales'
       END AS sales_category
FROM sales
GROUP BY stor_id;

This query goes one step further by creating a new column that categorizes stores based on their total sales in 1993. We use a CASE statement to label the sales as 'Low', 'Medium', or 'High' based on specific thresholds.

📍 Key Takeaways
- Conditional aggregation allows you to apply functions like SUM(), AVG(), and others based on specific criteria.
- Using CASE statements inside aggregate functions gives you control over which rows contribute to the result.
- Conditional aggregation is useful for more tailored insights, such as filtering by specific timeframes, creating categories, and more.

Incorporating these techniques into your #SQL queries enhances your ability to extract meaningful, granular insights from your data. Start experimenting with conditional aggregation to take your SQL analysis to the next level!

JOINS

Definition

Joins in MySQL allow you to retrieve data from two or more tables based on a related column. They are used to combine rows from multiple tables.

Types of Joins

1. INNER JOIN:
- Returns rows where there is a match in both tables.
- Syntax:

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

- Example:

            SELECT employees.name, departments.name
            FROM employees
            INNER JOIN departments
            ON employees.department_id = departments.id;

2. LEFT JOIN (OUTER JOIN):
- Returns all rows from the left table and matching rows from the right table. Non-matching rows have NULL.
- Example:

            SELECT employees.name, departments.name
            FROM employees
            LEFT JOIN departments
            ON employees.department_id = departments.id;

3. RIGHT JOIN (OUTER JOIN):
- Returns all rows from the right table and matching rows from the left table. Non-matching rows have NULL.
- Example:

            SELECT employees.name, departments.name
            FROM employees
            RIGHT JOIN departments
            ON employees.department_id = departments.id;

4. FULL OUTER JOIN:
- Returns all rows from both tables, matching where possible. Not natively supported in MySQL, but can be simulated using UNION.
- Example:

            SELECT employees.name, departments.name
            FROM employees
            LEFT JOIN departments
            ON employees.department_id = departments.id
            
            UNION
            
            SELECT employees.name, departments.name
            FROM employees
            RIGHT JOIN departments
            ON employees.department_id = departments.id;

5. CROSS JOIN:
- Returns the Cartesian product of both tables.
- Example:

            SELECT employees.name, departments.name
            FROM employees
            CROSS JOIN departments;

Interview Questions

1. What is the difference between INNER JOIN and OUTER JOIN?
- INNER JOIN only includes rows with matches in both tables, while OUTER JOIN includes unmatched rows.
2. How can you simulate a FULL OUTER JOIN in MySQL?
- Use UNION of LEFT JOIN and RIGHT JOIN.
3. What is a Cartesian product, and when does it occur?
- A Cartesian product occurs in a CROSS JOIN or when no ON condition is specified, resulting in all possible row combinations.

Data Types in SQL

Learn SQL from basic to advanced level in 30 days

Week 1: SQL Basics

Day 1: Introduction to SQL and Relational Databases

Overview of SQL Syntax

Setting up a Database (MySQL, PostgreSQL, or SQL Server)


Day 2: Data Types (Numeric, String, Date, etc.)

Writing Basic SQL Queries:

SELECT, FROM

Day 3: WHERE Clause for Filtering Data

Using Logical Operators:

AND, OR, NOT

Day 4: Sorting Data: ORDER BY

Limiting Results: LIMIT and OFFSET

Understanding DISTINCT

Day 5: Aggregate Functions:

COUNT, SUM, AVG, MIN, MAX


Day 6: Grouping Data: GROUP BY and HAVING

Combining Filters with Aggregations


Day 7: Review Week 1 Topics with Hands-On Practice

Solve SQL Exercises on platforms like HackerRank, LeetCode, or W3Schools


Week 2: Intermediate SQL

Day 8: SQL JOINS:

INNER JOIN, LEFT JOIN

Day 9: SQL JOINS Continued: RIGHT JOIN, FULL OUTER JOIN, SELF JOIN

Day 10: Working with NULL Values

Using Conditional Logic with CASE Statements

Day 11: Subqueries: Simple Subqueries (Single-row and Multi-row)

Correlated Subqueries

Day 12: String Functions:

CONCAT, SUBSTRING, LENGTH, REPLACE

Day 13: Date and Time Functions: NOW, CURDATE, DATEDIFF, DATEADD

Day 14: Combining Results: UNION, UNION ALL, INTERSECT, EXCEPT

Review Week 2 Topics and Practice

Week 3: Advanced SQL

Day 15: Common Table Expressions (CTEs)

WITH Clauses and Recursive Queries

Day 16: Window Functions:

ROW_NUMBER, RANK, DENSE_RANK, NTILE

Day 17: More Window Functions:

LEAD, LAG, FIRST_VALUE, LAST_VALUE


Day 18: Creating and Managing Views

Temporary Tables and Table Variables

Day 19: Transactions and ACID Properties

Working with Indexes for Query Optimization

Day 20: Error Handling in SQL

Writing Dynamic SQL Queries


Day 21: Review Week 3 Topics with Complex Query Practice

Solve Intermediate to Advanced SQL Challenges



Week 4: Database Management and Advanced Applications

Day 22: Database Design and Normalization:

1NF, 2NF, 3NF


Day 23: Constraints in SQL:
PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, DEFAULT


Day 24: Creating and Managing Indexes

Understanding Query Execution Plans

Day 25: Backup and Restore Strategies in SQL

Role-Based Permissions

Day 26: Pivoting and Unpivoting Data

Working with JSON and XML in SQL

Day 27: Writing Stored Procedures and Functions

Automating Processes with Triggers

Day 28: Integrating SQL with Other Tools (e.g., Python, Power BI, Tableau)

SQL in Big Data: Introduction to NoSQL

Day 29: Query Performance Tuning:

Tips and Tricks to Optimize SQL Queries


Day 30: Final Review of All Topics

Attempt SQL Projects or Case Studies (e.g., analyzing sales data, building a reporting dashboard)

Since SQL is one of the most essential skill for data analysts, I have decided to teach each topic daily in this channel for free. Like this post if you want me to continue this SQL series 👍♥️

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

Hope it helps :)

1. Does SQL support programming language features?
It is true that SQL is a language, but it does not support programming as it is not a programming language, it is a command language. We do not have some programming concepts in SQL like for loops or while loop, we only have commands which we can use to query, update, delete, etc. data in the database. SQL allows us to manipulate data in a database.

2. What is a trigger?
Trigger is a statement that a system executes automatically when there is any modification to the database. In a trigger, we first specify when the trigger is to be executed and then the action to be performed when the trigger executes. Triggers are used to specify certain integrity constraints and referential constraints that cannot be specified using the constraint mechanism of SQL.

3. What are aggregate and scalar functions?
For doing operations on data SQL has many built-in functions, they are categorized into two categories and further sub-categorized into seven different functions under each category. The categories are:
Aggregate functions:
These functions are used to do operations from the values of the column and a single value is returned.
Scalar functions:
These functions are based on user input, these too return a single value.

4. Define SQL Order by the statement?
The ORDER BY statement in SQL is used to sort the fetched data in either ascending or descending according to one or more columns.
By default ORDER BY sorts the data in ascending order.
We can use the keyword DESC to sort the data in descending order and the keyword ASC to sort in ascending order.

5. What is the difference between primary key and unique constraints? 
The primary key cannot have NULL values, the unique constraints can have NULL values. There is only one primary key in a table, but there can be multiple unique constraints. The primary key creates the clustered index automatically but the unique key does not.

Join Biggest Telegram channel for data analysts
👇👇
https://t.iss.one/sqlspecialist