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

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