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.
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.
๐6โค1
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 ๐๐
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 ๐๐
๐7๐4โค3
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:
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?
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:
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:
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?
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;
๐16โค3
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:
Examples:
1. Using ROW_NUMBER():
Assign a unique number to each row within a partition of the result set.
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.
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.
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.
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 :)
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 :)
๐8โค2๐1
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.
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.
โค6๐4
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.
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.
๐3โค1
30-day Roadmap plan for SQL covers beginner, intermediate, and advanced topics ๐
Week 1: Beginner Level
Day 1-3: Introduction and Setup
1. Day 1: Introduction to SQL, its importance, and various database systems.
2. Day 2: Installing a SQL database (e.g., MySQL, PostgreSQL).
3. Day 3: Setting up a sample database and practicing basic commands.
Day 4-7: Basic SQL Queries
4. Day 4: SELECT statement, retrieving data from a single table.
5. Day 5: WHERE clause and filtering data.
6. Day 6: Sorting data with ORDER BY.
7. Day 7: Aggregating data with GROUP BY and using aggregate functions (COUNT, SUM, AVG).
Week 2-3: Intermediate Level
Day 8-14: Working with Multiple Tables
8. Day 8: Introduction to JOIN operations.
9. Day 9: INNER JOIN and LEFT JOIN.
10. Day 10: RIGHT JOIN and FULL JOIN.
11. Day 11: Subqueries and correlated subqueries.
12. Day 12: Creating and modifying tables with CREATE, ALTER, and DROP.
13. Day 13: INSERT, UPDATE, and DELETE statements.
14. Day 14: Understanding indexes and optimizing queries.
Day 15-21: Data Manipulation
15. Day 15: CASE statements for conditional logic.
16. Day 16: Using UNION and UNION ALL.
17. Day 17: Data type conversions (CAST and CONVERT).
18. Day 18: Working with date and time functions.
19. Day 19: String manipulation functions.
20. Day 20: Error handling with TRY...CATCH.
21. Day 21: Practice complex queries and data manipulation tasks.
Week 4: Advanced Level
Day 22-28: Advanced Topics
22. Day 22: Working with Views.
23. Day 23: Stored Procedures and Functions.
24. Day 24: Triggers and transactions.
25. Day 25: Windows Function
Day 26-30: Real-World Projects
26. Day 26: SQL Project-1
27. Day 27: SQL Project-2
28. Day 28: SQL Project-3
29. Day 29: Practice questions set
30. Day 30: Final review and practice, explore advanced topics in depth, or work on a personal project.
Like for more
Week 1: Beginner Level
Day 1-3: Introduction and Setup
1. Day 1: Introduction to SQL, its importance, and various database systems.
2. Day 2: Installing a SQL database (e.g., MySQL, PostgreSQL).
3. Day 3: Setting up a sample database and practicing basic commands.
Day 4-7: Basic SQL Queries
4. Day 4: SELECT statement, retrieving data from a single table.
5. Day 5: WHERE clause and filtering data.
6. Day 6: Sorting data with ORDER BY.
7. Day 7: Aggregating data with GROUP BY and using aggregate functions (COUNT, SUM, AVG).
Week 2-3: Intermediate Level
Day 8-14: Working with Multiple Tables
8. Day 8: Introduction to JOIN operations.
9. Day 9: INNER JOIN and LEFT JOIN.
10. Day 10: RIGHT JOIN and FULL JOIN.
11. Day 11: Subqueries and correlated subqueries.
12. Day 12: Creating and modifying tables with CREATE, ALTER, and DROP.
13. Day 13: INSERT, UPDATE, and DELETE statements.
14. Day 14: Understanding indexes and optimizing queries.
Day 15-21: Data Manipulation
15. Day 15: CASE statements for conditional logic.
16. Day 16: Using UNION and UNION ALL.
17. Day 17: Data type conversions (CAST and CONVERT).
18. Day 18: Working with date and time functions.
19. Day 19: String manipulation functions.
20. Day 20: Error handling with TRY...CATCH.
21. Day 21: Practice complex queries and data manipulation tasks.
Week 4: Advanced Level
Day 22-28: Advanced Topics
22. Day 22: Working with Views.
23. Day 23: Stored Procedures and Functions.
24. Day 24: Triggers and transactions.
25. Day 25: Windows Function
Day 26-30: Real-World Projects
26. Day 26: SQL Project-1
27. Day 27: SQL Project-2
28. Day 28: SQL Project-3
29. Day 29: Practice questions set
30. Day 30: Final review and practice, explore advanced topics in depth, or work on a personal project.
Like for more
๐15
Top 10 SQL statements & functions used for data analysis
SELECT: To retrieve data from a database.
FROM: To specify the table or tables from which to retrieve data.
WHERE: To filter data based on specified conditions.
GROUP BY: To group rows with similar values into summary rows.
HAVING: To filter grouped data based on conditions.
ORDER BY: To sort the result set by one or more columns.
COUNT(): To count the number of rows or non-null values in a column.
SUM(): To calculate the sum of values in a numeric column.
AVG(): To calculate the average of values in a numeric column.
JOIN: To combine data from multiple tables based on a related column.
These SQL statements and functions are fundamental for data analysis and querying relational databases effectively.
Hope it helps :)
SELECT: To retrieve data from a database.
FROM: To specify the table or tables from which to retrieve data.
WHERE: To filter data based on specified conditions.
GROUP BY: To group rows with similar values into summary rows.
HAVING: To filter grouped data based on conditions.
ORDER BY: To sort the result set by one or more columns.
COUNT(): To count the number of rows or non-null values in a column.
SUM(): To calculate the sum of values in a numeric column.
AVG(): To calculate the average of values in a numeric column.
JOIN: To combine data from multiple tables based on a related column.
These SQL statements and functions are fundamental for data analysis and querying relational databases effectively.
Hope it helps :)
๐11
๐ช๐๐๐๐๐๐๐๐๐๐๐๐ ๐๐๐๐
๐๐๐ ๐๐ ๐๐๐๐๐๐๐๐ ๐ ๐๐๐๐๐๐ ๐๐ ๐บ๐ธ๐ณ:
1. ๐ผ๐๐ ๐๐๐๐๐๐๐ ๐๐๐ ๐ฉ๐๐๐๐๐ ๐๐ ๐บ๐ธ๐ณ
๐. ๐๐ง๐ญ๐ซ๐จ๐๐ฎ๐๐ญ๐ข๐จ๐ง ๐ญ๐จ ๐๐๐ญ๐๐๐๐ฌ๐๐ฌ
๐๐ก๐๐ญ ๐ข๐ฌ ๐ ๐๐๐ญ๐๐๐๐ฌ๐?: Understanding the concept of databases and relational databases.
๐๐๐ญ๐๐๐๐ฌ๐ ๐๐๐ง๐๐ ๐๐ฆ๐๐ง๐ญ ๐๐ฒ๐ฌ๐ญ๐๐ฆ๐ฌ (๐๐๐๐): Learn about different DBMS like MySQL, PostgreSQL, SQL Server, Oracle.
๐. ๐๐๐ฌ๐ข๐ ๐๐๐ ๐๐จ๐ฆ๐ฆ๐๐ง๐๐ฌ
๐๐๐ญ๐ ๐๐๐ญ๐ซ๐ข๐๐ฏ๐๐ฅ:
๐๐๐๐๐๐: Basic retrieval of data.
๐๐๐๐๐: Filtering data based on conditions.
๐๐๐๐๐ ๐๐: Sorting results.
๐๐๐๐๐: Limiting the number of rows returned.
๐๐๐ญ๐ ๐๐๐ง๐ข๐ฉ๐ฎ๐ฅ๐๐ญ๐ข๐จ๐ง:
๐๐๐๐๐๐: Adding new data.
๐๐๐๐๐๐: Modifying existing data.
๐๐๐๐๐๐: Removing data.
2. ๐๐ง๐ญ๐๐ซ๐ฆ๐๐๐ข๐๐ญ๐ ๐๐๐ ๐๐ค๐ข๐ฅ๐ฅ๐ฌ
๐. ๐๐๐ฏ๐๐ง๐๐๐ ๐๐๐ญ๐ ๐๐๐ญ๐ซ๐ข๐๐ฏ๐๐ฅ
๐๐๐๐๐ฌ: Understanding different types of joins (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN).
๐๐ ๐ ๐ซ๐๐ ๐๐ญ๐ ๐ ๐ฎ๐ง๐๐ญ๐ข๐จ๐ง๐ฌ: Using functions like COUNT, SUM, AVG, MIN, MAX.
๐๐๐๐๐ ๐๐: Grouping data to perform aggregate calculations.
๐๐๐๐๐๐: Filtering groups based on aggregate values.
๐. ๐๐ฎ๐๐ช๐ฎ๐๐ซ๐ข๐๐ฌ ๐๐ง๐ ๐๐๐ฌ๐ญ๐๐ ๐๐ฎ๐๐ซ๐ข๐๐ฌ
๐๐ฎ๐๐ช๐ฎ๐๐ซ๐ข๐๐ฌ: Using queries within queries.
๐๐จ๐ซ๐ซ๐๐ฅ๐๐ญ๐๐ ๐๐ฎ๐๐ช๐ฎ๐๐ซ๐ข๐๐ฌ: Subqueries that reference columns from the outer query.
๐ช. ๐ซ๐๐๐ ๐ซ๐๐๐๐๐๐๐๐๐ ๐ณ๐๐๐๐๐๐๐ (๐ซ๐ซ๐ณ)
๐๐ซ๐๐๐ญ๐ข๐ง๐ ๐๐๐๐ฅ๐๐ฌ: CREATE TABLE.
๐๐จ๐๐ข๐๐ฒ๐ข๐ง๐ ๐๐๐๐ฅ๐๐ฌ: ALTER TABLE.
๐น๐๐๐๐๐๐๐ ๐ป๐๐๐๐๐: DROP TABLE.
3. ๐๐๐ฏ๐๐ง๐๐๐ ๐๐๐ ๐๐๐๐ก๐ง๐ข๐ช๐ฎ๐๐ฌ
๐. ๐๐๐ซ๐๐จ๐ซ๐ฆ๐๐ง๐๐ ๐๐ฉ๐ญ๐ข๐ฆ๐ข๐ณ๐๐ญ๐ข๐จ๐ง
๐๐ง๐๐๐ฑ๐๐ฌ: Understanding and creating indexes to speed up queries.
๐๐ฎ๐๐ซ๐ฒ ๐๐ฉ๐ญ๐ข๐ฆ๐ข๐ณ๐๐ญ๐ข๐จ๐ง: Techniques to write efficient SQL queries.
๐. ๐๐๐ฏ๐๐ง๐๐๐ ๐๐๐ ๐ ๐ฎ๐ง๐๐ญ๐ข๐จ๐ง๐ฌ
๐๐ข๐ง๐๐จ๐ฐ ๐ ๐ฎ๐ง๐๐ญ๐ข๐จ๐ง๐ฌ: Using functions like ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG.
๐๐๐ (๐๐จ๐ฆ๐ฆ๐จ๐ง ๐๐๐๐ฅ๐ ๐๐ฑ๐ฉ๐ซ๐๐ฌ๐ฌ๐ข๐จ๐ง๐ฌ): Using WITH to create temporary result sets.
๐. ๐๐ซ๐๐ง๐ฌ๐๐๐ญ๐ข๐จ๐ง๐ฌ ๐๐ง๐ ๐๐จ๐ง๐๐ฎ๐ซ๐ซ๐๐ง๐๐ฒ
๐๐ซ๐๐ง๐ฌ๐๐๐ญ๐ข๐จ๐ง๐ฌ: Using BEGIN, COMMIT, ROLLBACK.
๐๐จ๐ง๐๐ฎ๐ซ๐ซ๐๐ง๐๐ฒ ๐๐จ๐ง๐ญ๐ซ๐จ๐ฅ: Understanding isolation levels and locking mechanisms.
4. ๐๐ซ๐๐๐ญ๐ข๐๐๐ฅ ๐๐ฉ๐ฉ๐ฅ๐ข๐๐๐ญ๐ข๐จ๐ง๐ฌ ๐๐ง๐ ๐๐๐๐ฅ-๐๐จ๐ซ๐ฅ๐ ๐๐๐๐ง๐๐ซ๐ข๐จ๐ฌ
๐. ๐๐๐ญ๐๐๐๐ฌ๐ ๐๐๐ฌ๐ข๐ ๐ง
๐๐จ๐ซ๐ฆ๐๐ฅ๐ข๐ณ๐๐ญ๐ข๐จ๐ง: Understanding normal forms and how to normalize databases.
๐๐ ๐๐ข๐๐ ๐ซ๐๐ฆ๐ฌ: Creating Entity-Relationship diagrams to model databases.
๐. ๐๐๐ญ๐ ๐๐ง๐ญ๐๐ ๐ซ๐๐ญ๐ข๐จ๐ง
๐๐๐ ๐๐ซ๐จ๐๐๐ฌ๐ฌ๐๐ฌ: Extract, Transform, Load processes for data integration.
๐๐ญ๐จ๐ซ๐๐ ๐๐ซ๐จ๐๐๐๐ฎ๐ซ๐๐ฌ ๐๐ง๐ ๐๐ซ๐ข๐ ๐ ๐๐ซ๐ฌ: Writing and using stored procedures and triggers for complex logic and automation.
๐. ๐๐๐ฌ๐ ๐๐ญ๐ฎ๐๐ข๐๐ฌ ๐๐ง๐ ๐๐ซ๐จ๐ฃ๐๐๐ญ๐ฌ
๐๐๐๐ฅ-๐๐จ๐ซ๐ฅ๐ ๐๐๐๐ง๐๐ซ๐ข๐จ๐ฌ: Work on case studies involving complex database operations.
๐๐๐ฉ๐ฌ๐ญ๐จ๐ง๐ ๐๐ซ๐จ๐ฃ๐๐๐ญ๐ฌ: Develop comprehensive projects that showcase your SQL expertise.
๐๐๐ฌ๐จ๐ฎ๐ซ๐๐๐ฌ ๐๐ง๐ ๐๐จ๐จ๐ฅ๐ฌ
๐๐จ๐จ๐ค๐ฌ: "SQL in 10 Minutes, Sams Teach Yourself" by Ben Forta, "SQL for Data Scientists" by Renee M. P. Teate.
๐๐ง๐ฅ๐ข๐ง๐ ๐๐ฅ๐๐ญ๐๐จ๐ซ๐ฆ๐ฌ: Coursera, Udacity, edX, Khan Academy.
๐๐ซ๐๐๐ญ๐ข๐๐ ๐๐ฅ๐๐ญ๐๐จ๐ซ๐ฆ๐ฌ: LeetCode, HackerRank, Mode Analytics, SQLZoo.
1. ๐ผ๐๐ ๐๐๐๐๐๐๐ ๐๐๐ ๐ฉ๐๐๐๐๐ ๐๐ ๐บ๐ธ๐ณ
๐. ๐๐ง๐ญ๐ซ๐จ๐๐ฎ๐๐ญ๐ข๐จ๐ง ๐ญ๐จ ๐๐๐ญ๐๐๐๐ฌ๐๐ฌ
๐๐ก๐๐ญ ๐ข๐ฌ ๐ ๐๐๐ญ๐๐๐๐ฌ๐?: Understanding the concept of databases and relational databases.
๐๐๐ญ๐๐๐๐ฌ๐ ๐๐๐ง๐๐ ๐๐ฆ๐๐ง๐ญ ๐๐ฒ๐ฌ๐ญ๐๐ฆ๐ฌ (๐๐๐๐): Learn about different DBMS like MySQL, PostgreSQL, SQL Server, Oracle.
๐. ๐๐๐ฌ๐ข๐ ๐๐๐ ๐๐จ๐ฆ๐ฆ๐๐ง๐๐ฌ
๐๐๐ญ๐ ๐๐๐ญ๐ซ๐ข๐๐ฏ๐๐ฅ:
๐๐๐๐๐๐: Basic retrieval of data.
๐๐๐๐๐: Filtering data based on conditions.
๐๐๐๐๐ ๐๐: Sorting results.
๐๐๐๐๐: Limiting the number of rows returned.
๐๐๐ญ๐ ๐๐๐ง๐ข๐ฉ๐ฎ๐ฅ๐๐ญ๐ข๐จ๐ง:
๐๐๐๐๐๐: Adding new data.
๐๐๐๐๐๐: Modifying existing data.
๐๐๐๐๐๐: Removing data.
2. ๐๐ง๐ญ๐๐ซ๐ฆ๐๐๐ข๐๐ญ๐ ๐๐๐ ๐๐ค๐ข๐ฅ๐ฅ๐ฌ
๐. ๐๐๐ฏ๐๐ง๐๐๐ ๐๐๐ญ๐ ๐๐๐ญ๐ซ๐ข๐๐ฏ๐๐ฅ
๐๐๐๐๐ฌ: Understanding different types of joins (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN).
๐๐ ๐ ๐ซ๐๐ ๐๐ญ๐ ๐ ๐ฎ๐ง๐๐ญ๐ข๐จ๐ง๐ฌ: Using functions like COUNT, SUM, AVG, MIN, MAX.
๐๐๐๐๐ ๐๐: Grouping data to perform aggregate calculations.
๐๐๐๐๐๐: Filtering groups based on aggregate values.
๐. ๐๐ฎ๐๐ช๐ฎ๐๐ซ๐ข๐๐ฌ ๐๐ง๐ ๐๐๐ฌ๐ญ๐๐ ๐๐ฎ๐๐ซ๐ข๐๐ฌ
๐๐ฎ๐๐ช๐ฎ๐๐ซ๐ข๐๐ฌ: Using queries within queries.
๐๐จ๐ซ๐ซ๐๐ฅ๐๐ญ๐๐ ๐๐ฎ๐๐ช๐ฎ๐๐ซ๐ข๐๐ฌ: Subqueries that reference columns from the outer query.
๐ช. ๐ซ๐๐๐ ๐ซ๐๐๐๐๐๐๐๐๐ ๐ณ๐๐๐๐๐๐๐ (๐ซ๐ซ๐ณ)
๐๐ซ๐๐๐ญ๐ข๐ง๐ ๐๐๐๐ฅ๐๐ฌ: CREATE TABLE.
๐๐จ๐๐ข๐๐ฒ๐ข๐ง๐ ๐๐๐๐ฅ๐๐ฌ: ALTER TABLE.
๐น๐๐๐๐๐๐๐ ๐ป๐๐๐๐๐: DROP TABLE.
3. ๐๐๐ฏ๐๐ง๐๐๐ ๐๐๐ ๐๐๐๐ก๐ง๐ข๐ช๐ฎ๐๐ฌ
๐. ๐๐๐ซ๐๐จ๐ซ๐ฆ๐๐ง๐๐ ๐๐ฉ๐ญ๐ข๐ฆ๐ข๐ณ๐๐ญ๐ข๐จ๐ง
๐๐ง๐๐๐ฑ๐๐ฌ: Understanding and creating indexes to speed up queries.
๐๐ฎ๐๐ซ๐ฒ ๐๐ฉ๐ญ๐ข๐ฆ๐ข๐ณ๐๐ญ๐ข๐จ๐ง: Techniques to write efficient SQL queries.
๐. ๐๐๐ฏ๐๐ง๐๐๐ ๐๐๐ ๐ ๐ฎ๐ง๐๐ญ๐ข๐จ๐ง๐ฌ
๐๐ข๐ง๐๐จ๐ฐ ๐ ๐ฎ๐ง๐๐ญ๐ข๐จ๐ง๐ฌ: Using functions like ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG.
๐๐๐ (๐๐จ๐ฆ๐ฆ๐จ๐ง ๐๐๐๐ฅ๐ ๐๐ฑ๐ฉ๐ซ๐๐ฌ๐ฌ๐ข๐จ๐ง๐ฌ): Using WITH to create temporary result sets.
๐. ๐๐ซ๐๐ง๐ฌ๐๐๐ญ๐ข๐จ๐ง๐ฌ ๐๐ง๐ ๐๐จ๐ง๐๐ฎ๐ซ๐ซ๐๐ง๐๐ฒ
๐๐ซ๐๐ง๐ฌ๐๐๐ญ๐ข๐จ๐ง๐ฌ: Using BEGIN, COMMIT, ROLLBACK.
๐๐จ๐ง๐๐ฎ๐ซ๐ซ๐๐ง๐๐ฒ ๐๐จ๐ง๐ญ๐ซ๐จ๐ฅ: Understanding isolation levels and locking mechanisms.
4. ๐๐ซ๐๐๐ญ๐ข๐๐๐ฅ ๐๐ฉ๐ฉ๐ฅ๐ข๐๐๐ญ๐ข๐จ๐ง๐ฌ ๐๐ง๐ ๐๐๐๐ฅ-๐๐จ๐ซ๐ฅ๐ ๐๐๐๐ง๐๐ซ๐ข๐จ๐ฌ
๐. ๐๐๐ญ๐๐๐๐ฌ๐ ๐๐๐ฌ๐ข๐ ๐ง
๐๐จ๐ซ๐ฆ๐๐ฅ๐ข๐ณ๐๐ญ๐ข๐จ๐ง: Understanding normal forms and how to normalize databases.
๐๐ ๐๐ข๐๐ ๐ซ๐๐ฆ๐ฌ: Creating Entity-Relationship diagrams to model databases.
๐. ๐๐๐ญ๐ ๐๐ง๐ญ๐๐ ๐ซ๐๐ญ๐ข๐จ๐ง
๐๐๐ ๐๐ซ๐จ๐๐๐ฌ๐ฌ๐๐ฌ: Extract, Transform, Load processes for data integration.
๐๐ญ๐จ๐ซ๐๐ ๐๐ซ๐จ๐๐๐๐ฎ๐ซ๐๐ฌ ๐๐ง๐ ๐๐ซ๐ข๐ ๐ ๐๐ซ๐ฌ: Writing and using stored procedures and triggers for complex logic and automation.
๐. ๐๐๐ฌ๐ ๐๐ญ๐ฎ๐๐ข๐๐ฌ ๐๐ง๐ ๐๐ซ๐จ๐ฃ๐๐๐ญ๐ฌ
๐๐๐๐ฅ-๐๐จ๐ซ๐ฅ๐ ๐๐๐๐ง๐๐ซ๐ข๐จ๐ฌ: Work on case studies involving complex database operations.
๐๐๐ฉ๐ฌ๐ญ๐จ๐ง๐ ๐๐ซ๐จ๐ฃ๐๐๐ญ๐ฌ: Develop comprehensive projects that showcase your SQL expertise.
๐๐๐ฌ๐จ๐ฎ๐ซ๐๐๐ฌ ๐๐ง๐ ๐๐จ๐จ๐ฅ๐ฌ
๐๐จ๐จ๐ค๐ฌ: "SQL in 10 Minutes, Sams Teach Yourself" by Ben Forta, "SQL for Data Scientists" by Renee M. P. Teate.
๐๐ง๐ฅ๐ข๐ง๐ ๐๐ฅ๐๐ญ๐๐จ๐ซ๐ฆ๐ฌ: Coursera, Udacity, edX, Khan Academy.
๐๐ซ๐๐๐ญ๐ข๐๐ ๐๐ฅ๐๐ญ๐๐จ๐ซ๐ฆ๐ฌ: LeetCode, HackerRank, Mode Analytics, SQLZoo.
๐8โค5
Top 10 SQL concepts for data analysts
1. Database: A database is a structured collection of data that is organized and managed for easy access and retrieval. It stores data in tables, which are made up of rows and columns.
2. SQL (Structured Query Language): SQL is a programming language used to communicate with and manipulate databases. It allows data analysts to retrieve, insert, update, and delete data from a database.
3. Tables: Tables are the basic building blocks of a database. They consist of rows (records) and columns (fields) that store data in a structured format.
4. Queries: Queries are SQL statements used to retrieve specific data from a database. They can be simple or complex, involving multiple tables and conditions.
5. Joins: Joins are used to combine data from multiple tables based on a common field. They allow data analysts to retrieve related information from different tables in a single query.
6. Aggregation Functions: Aggregation functions, such as SUM, COUNT, AVG, MIN, and MAX, are used to perform calculations on groups of data. They are commonly used to summarize and analyze large datasets.
7. Filtering: Filtering allows data analysts to retrieve specific data based on certain conditions. It involves using the WHERE clause in SQL queries to specify the criteria for selecting records.
8. Sorting: Sorting allows data analysts to arrange data in a specific order. It involves using the ORDER BY clause in SQL queries to sort records based on one or more columns.
9. Subqueries: Subqueries are queries nested within another query. They allow data analysts to perform complex operations by using the results of one query as input for another query.
10. Data Manipulation Language (DML): DML statements, such as INSERT, UPDATE, and DELETE, are used to modify data in a database. Data analysts use these statements to insert new records, update existing records, or delete unwanted records.
1. Database: A database is a structured collection of data that is organized and managed for easy access and retrieval. It stores data in tables, which are made up of rows and columns.
2. SQL (Structured Query Language): SQL is a programming language used to communicate with and manipulate databases. It allows data analysts to retrieve, insert, update, and delete data from a database.
3. Tables: Tables are the basic building blocks of a database. They consist of rows (records) and columns (fields) that store data in a structured format.
4. Queries: Queries are SQL statements used to retrieve specific data from a database. They can be simple or complex, involving multiple tables and conditions.
5. Joins: Joins are used to combine data from multiple tables based on a common field. They allow data analysts to retrieve related information from different tables in a single query.
6. Aggregation Functions: Aggregation functions, such as SUM, COUNT, AVG, MIN, and MAX, are used to perform calculations on groups of data. They are commonly used to summarize and analyze large datasets.
7. Filtering: Filtering allows data analysts to retrieve specific data based on certain conditions. It involves using the WHERE clause in SQL queries to specify the criteria for selecting records.
8. Sorting: Sorting allows data analysts to arrange data in a specific order. It involves using the ORDER BY clause in SQL queries to sort records based on one or more columns.
9. Subqueries: Subqueries are queries nested within another query. They allow data analysts to perform complex operations by using the results of one query as input for another query.
10. Data Manipulation Language (DML): DML statements, such as INSERT, UPDATE, and DELETE, are used to modify data in a database. Data analysts use these statements to insert new records, update existing records, or delete unwanted records.
๐8
SQL Interview Questions !!
๐ Write a query to find all employees whose salaries exceed the company's average salary.
๐ Write a query to retrieve the names of employees who work in the same department as 'John Doe'.
๐ Write a query to display the second highest salary from the Employee table without using the MAX function twice.
๐ Write a query to find all customers who have placed more than five orders.
๐ Write a query to count the total number of orders placed by each customer.
๐ Write a query to list employees who joined the company within the last 6 months.
๐ Write a query to calculate the total sales amount for each product.
๐ Write a query to list all products that have never been sold.
๐ Write a query to remove duplicate rows from a table.
๐ Write a query to identify the top 10 customers who have not placed any orders in the past year.
Here you can find more SQL Resources๐
https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v
Hope it helps :)
๐ Write a query to find all employees whose salaries exceed the company's average salary.
๐ Write a query to retrieve the names of employees who work in the same department as 'John Doe'.
๐ Write a query to display the second highest salary from the Employee table without using the MAX function twice.
๐ Write a query to find all customers who have placed more than five orders.
๐ Write a query to count the total number of orders placed by each customer.
๐ Write a query to list employees who joined the company within the last 6 months.
๐ Write a query to calculate the total sales amount for each product.
๐ Write a query to list all products that have never been sold.
๐ Write a query to remove duplicate rows from a table.
๐ Write a query to identify the top 10 customers who have not placed any orders in the past year.
Here you can find more SQL Resources๐
https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v
Hope it helps :)
โค4๐1
SQL Interview Questions which can be asked in a Data Analyst Interview.
1๏ธโฃ What is difference between Primary key and Unique key?
โผPrimary key- A column or set of columns which uniquely identifies each record in a table. It can't contain null values and only one primary key
can exist in a table.
โผUnique key-Similar to primary key it also uniquely identifies each record in a table and can contain null values.Multiple Unique key can exist in a table.
2๏ธโฃ What is a Candidate key?
โผA key or set of keys that uniquely identifies each record in a table.It is a combination of Primary and Alternate key.
3๏ธโฃ What is a Constraint?
โผSpecific rule or limit that we define in our table. E.g - NOT NULL,AUTO INCREMENT
4๏ธโฃ Can you differentiate between TRUNCATE and DELETE?
โผTRUNCATE is a DDL command. It deletes the entire data from a table but preserves the structure of table.It doesn't deletes the data row by row hence faster than DELETE command, while DELETE is a DML command and it deletes the entire data based on specified condition else deletes the entire data,also it deletes the data row by row hence slower than TRUNCATE command.
5๏ธโฃ What is difference between 'View' and 'Stored Procedure'?
โผA View is a virtual table that gets data from the base table .It is basically a Select statement,while Stored Procedure is a sql statement or set of sql statement stored on database server.
6๏ธโฃ What is difference between a Common Table Expression and temporary table?
โผCTE is a temporary result set that is defined within execution scope of a single SELECT ,DELETE,UPDATE statement while temporary table is stored in TempDB and gets deleted once the session expires.
7๏ธโฃ Differentiate between a clustered index and a non-clustered index?
โผ A clustered index determines physical ordering of data in a table and a table can have only one clustered index while a non-clustered index is analogous to index of a book where index is stored at one place and data at other place and index will have pointers to storage location of the data,a table can have more than one non-clustered index.
8๏ธโฃ Explain triggers ?
โผThey are sql codes which are automatically executed in response to certain events on a table.They are used to maintain integrity of data.
Join our WhatsApp channel for more resources ๐
https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v
Like this post if you need more ๐โค๏ธ
Hope it helps :)
1๏ธโฃ What is difference between Primary key and Unique key?
โผPrimary key- A column or set of columns which uniquely identifies each record in a table. It can't contain null values and only one primary key
can exist in a table.
โผUnique key-Similar to primary key it also uniquely identifies each record in a table and can contain null values.Multiple Unique key can exist in a table.
2๏ธโฃ What is a Candidate key?
โผA key or set of keys that uniquely identifies each record in a table.It is a combination of Primary and Alternate key.
3๏ธโฃ What is a Constraint?
โผSpecific rule or limit that we define in our table. E.g - NOT NULL,AUTO INCREMENT
4๏ธโฃ Can you differentiate between TRUNCATE and DELETE?
โผTRUNCATE is a DDL command. It deletes the entire data from a table but preserves the structure of table.It doesn't deletes the data row by row hence faster than DELETE command, while DELETE is a DML command and it deletes the entire data based on specified condition else deletes the entire data,also it deletes the data row by row hence slower than TRUNCATE command.
5๏ธโฃ What is difference between 'View' and 'Stored Procedure'?
โผA View is a virtual table that gets data from the base table .It is basically a Select statement,while Stored Procedure is a sql statement or set of sql statement stored on database server.
6๏ธโฃ What is difference between a Common Table Expression and temporary table?
โผCTE is a temporary result set that is defined within execution scope of a single SELECT ,DELETE,UPDATE statement while temporary table is stored in TempDB and gets deleted once the session expires.
7๏ธโฃ Differentiate between a clustered index and a non-clustered index?
โผ A clustered index determines physical ordering of data in a table and a table can have only one clustered index while a non-clustered index is analogous to index of a book where index is stored at one place and data at other place and index will have pointers to storage location of the data,a table can have more than one non-clustered index.
8๏ธโฃ Explain triggers ?
โผThey are sql codes which are automatically executed in response to certain events on a table.They are used to maintain integrity of data.
Join our WhatsApp channel for more resources ๐
https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v
Like this post if you need more ๐โค๏ธ
Hope it helps :)
โค3๐1
Forwarded from Data Analytics
Which of the following is SQL Command is used to sort results?
Anonymous Quiz
34%
SORT BY
57%
ORDER BY
7%
SORTED
3%
ORDER ON
๐5
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.iss.one/sqlspecialist/523
Part-1: https://t.iss.one/sqlspecialist/524
Part-2: https://t.iss.one/sqlspecialist/525
Part-3: https://t.iss.one/sqlspecialist/526
Part-4: https://t.iss.one/sqlspecialist/527
Part-5: https://t.iss.one/sqlspecialist/529
Part-6: https://t.iss.one/sqlspecialist/534
Part-7: https://t.iss.one/sqlspecialist/534
Part-8: https://t.iss.one/sqlspecialist/536
Part-9: https://t.iss.one/sqlspecialist/537
Part-10: https://t.iss.one/sqlspecialist/539
Part-11: https://t.iss.one/sqlspecialist/540
Part-12:
https://t.iss.one/sqlspecialist/541
Part-13: https://t.iss.one/sqlspecialist/542
Part-14: https://t.iss.one/sqlspecialist/544
Part-15: https://t.iss.one/sqlspecialist/545
Part-16: https://t.iss.one/sqlspecialist/546
Part-17: https://t.iss.one/sqlspecialist/549
Part-18: https://t.iss.one/sqlspecialist/552
Part-19: https://t.iss.one/sqlspecialist/555
Part-20: https://t.iss.one/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.iss.one/sqlspecialist/548
Complete Excel Topics for Data Analysts: https://t.iss.one/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 :)
Here are the links to the SQL series
Complete SQL Topics for Data Analyst: https://t.iss.one/sqlspecialist/523
Part-1: https://t.iss.one/sqlspecialist/524
Part-2: https://t.iss.one/sqlspecialist/525
Part-3: https://t.iss.one/sqlspecialist/526
Part-4: https://t.iss.one/sqlspecialist/527
Part-5: https://t.iss.one/sqlspecialist/529
Part-6: https://t.iss.one/sqlspecialist/534
Part-7: https://t.iss.one/sqlspecialist/534
Part-8: https://t.iss.one/sqlspecialist/536
Part-9: https://t.iss.one/sqlspecialist/537
Part-10: https://t.iss.one/sqlspecialist/539
Part-11: https://t.iss.one/sqlspecialist/540
Part-12:
https://t.iss.one/sqlspecialist/541
Part-13: https://t.iss.one/sqlspecialist/542
Part-14: https://t.iss.one/sqlspecialist/544
Part-15: https://t.iss.one/sqlspecialist/545
Part-16: https://t.iss.one/sqlspecialist/546
Part-17: https://t.iss.one/sqlspecialist/549
Part-18: https://t.iss.one/sqlspecialist/552
Part-19: https://t.iss.one/sqlspecialist/555
Part-20: https://t.iss.one/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.iss.one/sqlspecialist/548
Complete Excel Topics for Data Analysts: https://t.iss.one/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 :)
โค9๐4๐3
โ๏ธBest practices for writing SQL ๐queries:
1- Write SQL keywords in capital letters.
2- Use table aliases with columns when you are joining multiple tables.
3- Never use select *, always mention list of columns in select clause.
4- Add useful comments wherever you write complex logic. Avoid too many comments.
5- Use joins instead of 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.
9- If you know there are no duplicates in 2 tables, use UNION ALL instead of UNION for better performance.
Here you can find essential SQL Resources๐
https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v
Like this post if you need more ๐โค๏ธ
Hope it helps :)
1- Write SQL keywords in capital letters.
2- Use table aliases with columns when you are joining multiple tables.
3- Never use select *, always mention list of columns in select clause.
4- Add useful comments wherever you write complex logic. Avoid too many comments.
5- Use joins instead of 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.
9- If you know there are no duplicates in 2 tables, use UNION ALL instead of UNION for better performance.
Here you can find essential SQL Resources๐
https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v
Like this post if you need more ๐โค๏ธ
Hope it helps :)
๐7โค2
SQL Basics for Beginners: Must-Know Concepts
1. What is SQL?
SQL (Structured Query Language) is a standard language used to communicate with databases. It allows you to query, update, and manage relational databases by writing simple or complex queries.
2. SQL Syntax
SQL is written using statements, which consist of keywords like
- SQL keywords are not case-sensitive, but it's common to write them in uppercase (e.g.,
3. SQL Data Types
Databases store data in different formats. The most common data types are:
-
-
-
-
4. Basic SQL Queries
Here are some fundamental SQL operations:
- SELECT Statement: Used to retrieve data from a database.
- WHERE Clause: Filters data based on conditions.
- ORDER BY: Sorts data in ascending (
- LIMIT: Limits the number of rows returned.
5. Filtering Data with WHERE Clause
The
You can use comparison operators like:
-
-
-
-
6. Aggregating Data
SQL provides functions to summarize or aggregate data:
- COUNT(): Counts the number of rows.
- SUM(): Adds up values in a column.
- AVG(): Calculates the average value.
- GROUP BY: Groups rows that have the same values into summary rows.
7. Joins in SQL
Joins combine data from two or more tables:
- INNER JOIN: Retrieves records with matching values in both tables.
- LEFT JOIN: Retrieves all records from the left table and matched records from the right table.
8. Inserting Data
To add new data to a table, you use the
9. Updating Data
You can update existing data in a table using the
10. Deleting Data
To remove data from a table, use the
Hope it helps :)
1. What is SQL?
SQL (Structured Query Language) is a standard language used to communicate with databases. It allows you to query, update, and manage relational databases by writing simple or complex queries.
2. SQL Syntax
SQL is written using statements, which consist of keywords like
SELECT, FROM, WHERE, etc., to perform operations on the data.- SQL keywords are not case-sensitive, but it's common to write them in uppercase (e.g.,
SELECT, FROM).3. SQL Data Types
Databases store data in different formats. The most common data types are:
-
INT (Integer): For whole numbers.-
VARCHAR(n) or TEXT: For storing text data.-
DATE: For dates.-
DECIMAL: For precise decimal values, often used in financial calculations.4. Basic SQL Queries
Here are some fundamental SQL operations:
- SELECT Statement: Used to retrieve data from a database.
SELECT column1, column2 FROM table_name;
- WHERE Clause: Filters data based on conditions.
SELECT * FROM table_name WHERE condition;
- ORDER BY: Sorts data in ascending (
ASC) or descending (DESC) order.SELECT column1, column2 FROM table_name ORDER BY column1 ASC;
- LIMIT: Limits the number of rows returned.
SELECT * FROM table_name LIMIT 5;
5. Filtering Data with WHERE Clause
The
WHERE clause helps you filter data based on a condition:SELECT * FROM employees WHERE salary > 50000;
You can use comparison operators like:
-
=: Equal to-
>: Greater than-
<: Less than-
LIKE: For pattern matching6. Aggregating Data
SQL provides functions to summarize or aggregate data:
- COUNT(): Counts the number of rows.
SELECT COUNT(*) FROM table_name;
- SUM(): Adds up values in a column.
SELECT SUM(salary) FROM employees;
- AVG(): Calculates the average value.
SELECT AVG(salary) FROM employees;
- GROUP BY: Groups rows that have the same values into summary rows.
SELECT department, AVG(salary) FROM employees GROUP BY department;
7. Joins in SQL
Joins combine data from two or more tables:
- INNER JOIN: Retrieves records with matching values in both tables.
SELECT employees.name, departments.department
FROM employees
INNER JOIN departments
ON employees.department_id = departments.id;
- LEFT JOIN: Retrieves all records from the left table and matched records from the right table.
SELECT employees.name, departments.department
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.id;
8. Inserting Data
To add new data to a table, you use the
INSERT INTO statement: INSERT INTO employees (name, position, salary) VALUES ('John Doe', 'Analyst', 60000);
9. Updating Data
You can update existing data in a table using the
UPDATE statement:UPDATE employees SET salary = 65000 WHERE name = 'John Doe';
10. Deleting Data
To remove data from a table, use the
DELETE statement:DELETE FROM employees WHERE name = 'John Doe';
Hope it helps :)
๐12โค10
SQL Programming Resources
๐๐
Jokes apart, if you really want to improve your communication skills, you should definitely join @englishlearnerspro
โค2๐2
๐ฑ ๐ฆ๐ค๐ ๐ ๐๐๐ต๐ ๐๐ฒ๐ฏ๐๐ป๐ธ๐ฒ๐ฑ โ ๐ช๐ต๐ฎ๐ ๐๐ฒ๐ด๐ถ๐ป๐ป๐ฒ๐ฟ๐ ๐ข๐ณ๐๐ฒ๐ป ๐๐ฒ๐ ๐ช๐ฟ๐ผ๐ป๐ด
SQL is super powerful, but some myths around it can trip up beginners.
Letโs clear up five common misunderstandings and set the record straight:
๐ ๐๐๐ต ๐ญ: ๐ฆ๐ค๐ ๐ถ๐ ๐ท๐๐๐ ๐ณ๐ผ๐ฟ ๐ฝ๐๐น๐น๐ถ๐ป๐ด ๐ฑ๐ฎ๐๐ฎ.
โฆ ๐ฅ๐ฒ๐ฎ๐น๐ถ๐๐: Nope, itโs not just for that! SQL can also create, modify, and manage databases, control access, and maintain data consistency.
โฆ ๐๐ถ๐ ๐ถ๐: Explore all the features of SQL, like DDL (for database design), DCL (for access control), and TCL (for transactions). It's more than just SELECT!
๐ ๐๐๐ต ๐ฎ: ๐จ๐๐ถ๐ป๐ด
โฆ ๐ฅ๐ฒ๐ฎ๐น๐ถ๐๐: It might be easy, but itโs not efficient. Pulling all columns wastes memory and slows down performance.
โฆ ๐๐ถ๐ ๐ถ๐: Only select the columns you actually need. Itโs faster and cleaner.
Not great - SELECT * FROM employees;
Better - SELECT employee_id, name, department FROM employees;
๐ ๐๐๐ต ๐ฏ: ๐ฆ๐ค๐ ๐ฐ๐ฎ๐ป'๐ ๐ต๐ฎ๐ป๐ฑ๐น๐ฒ ๐ฐ๐ผ๐บ๐ฝ๐น๐ฒ๐ ๐ฎ๐ป๐ฎ๐น๐๐๐ถ๐.
โฆ ๐ฅ๐ฒ๐ฎ๐น๐ถ๐๐: SQL can do way more than basic queries! With concepts like window functions and CTEs, you can handle really complex data analysis.
โฆ ๐๐ถ๐ ๐ถ๐: Learn advanced SQL features like window functions (ROW_NUMBER(), RANK()) and CTEs to up your game.
Example - Ranking employees by salary within their department
๐ ๐๐๐ต ๐ฐ: ๐ฆ๐น๐ผ๐ ๐พ๐๐ฒ๐ฟ๐ถ๐ฒ๐ ๐ฎ๐ฟ๐ฒ ๐ฎ๐น๐๐ฎ๐๐ ๐๐ต๐ฒ ๐ฑ๐ฎ๐๐ฎ๐ฏ๐ฎ๐๐ฒโ๐ ๐ณ๐ฎ๐๐น๐.
โฆ ๐ฅ๐ฒ๐ฎ๐น๐ถ๐๐: Itโs usually inefficient queries causing the slowdown. Things like missing indexes or unoptimized code can be the culprit.
โฆ ๐๐ถ๐ ๐ถ๐: Use indexes properly, avoid complex calculations in
๐ ๐๐๐ต ๐ฑ: ๐ฆ๐ค๐ ๐ถ๐ ๐ผ๐๐๐ฑ๐ฎ๐๐ฒ๐ฑ ๐ฎ๐ป๐ฑ ๐๐ถ๐น๐น ๐ฏ๐ฒ ๐ฟ๐ฒ๐ฝ๐น๐ฎ๐ฐ๐ฒ๐ฑ ๐๐ผ๐ผ๐ป.
โฆ ๐ฅ๐ฒ๐ฎ๐น๐ถ๐๐: SQL is here to stay! Despite the rise of NoSQL, SQL remains the backbone for structured data.
โฆ ๐๐ถ๐ ๐ถ๐: Stay current and explore how SQL integrates with big data platforms and cloud databases. Itโs more relevant than ever.
Donโt let these myths hold you back. SQL is powerful, and when you understand it fully, you can do amazing things with your data.
Here you can find more SQL Resources
๐๐
https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v
Like this post if you need more ๐โค๏ธ
Hope it helps :)
SQL is super powerful, but some myths around it can trip up beginners.
Letโs clear up five common misunderstandings and set the record straight:
๐ ๐๐๐ต ๐ญ: ๐ฆ๐ค๐ ๐ถ๐ ๐ท๐๐๐ ๐ณ๐ผ๐ฟ ๐ฝ๐๐น๐น๐ถ๐ป๐ด ๐ฑ๐ฎ๐๐ฎ.
โฆ ๐ฅ๐ฒ๐ฎ๐น๐ถ๐๐: Nope, itโs not just for that! SQL can also create, modify, and manage databases, control access, and maintain data consistency.
โฆ ๐๐ถ๐ ๐ถ๐: Explore all the features of SQL, like DDL (for database design), DCL (for access control), and TCL (for transactions). It's more than just SELECT!
๐ ๐๐๐ต ๐ฎ: ๐จ๐๐ถ๐ป๐ด
๐ฆ๐๐๐๐๐ง * ๐ถ๐ ๐ณ๐ถ๐ป๐ฒ.โฆ ๐ฅ๐ฒ๐ฎ๐น๐ถ๐๐: It might be easy, but itโs not efficient. Pulling all columns wastes memory and slows down performance.
โฆ ๐๐ถ๐ ๐ถ๐: Only select the columns you actually need. Itโs faster and cleaner.
Not great - SELECT * FROM employees;
Better - SELECT employee_id, name, department FROM employees;
๐ ๐๐๐ต ๐ฏ: ๐ฆ๐ค๐ ๐ฐ๐ฎ๐ป'๐ ๐ต๐ฎ๐ป๐ฑ๐น๐ฒ ๐ฐ๐ผ๐บ๐ฝ๐น๐ฒ๐ ๐ฎ๐ป๐ฎ๐น๐๐๐ถ๐.
โฆ ๐ฅ๐ฒ๐ฎ๐น๐ถ๐๐: SQL can do way more than basic queries! With concepts like window functions and CTEs, you can handle really complex data analysis.
โฆ ๐๐ถ๐ ๐ถ๐: Learn advanced SQL features like window functions (ROW_NUMBER(), RANK()) and CTEs to up your game.
Example - Ranking employees by salary within their department
WITH ranked_salaries AS (SELECT employee_id, salary, department,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank FROM employees)
SELECT * FROM ranked_salaries WHERE rank = 1;
๐ ๐๐๐ต ๐ฐ: ๐ฆ๐น๐ผ๐ ๐พ๐๐ฒ๐ฟ๐ถ๐ฒ๐ ๐ฎ๐ฟ๐ฒ ๐ฎ๐น๐๐ฎ๐๐ ๐๐ต๐ฒ ๐ฑ๐ฎ๐๐ฎ๐ฏ๐ฎ๐๐ฒโ๐ ๐ณ๐ฎ๐๐น๐.
โฆ ๐ฅ๐ฒ๐ฎ๐น๐ถ๐๐: Itโs usually inefficient queries causing the slowdown. Things like missing indexes or unoptimized code can be the culprit.
โฆ ๐๐ถ๐ ๐ถ๐: Use indexes properly, avoid complex calculations in
WHERE clauses, and check your query execution plan to spot bottlenecks.๐ ๐๐๐ต ๐ฑ: ๐ฆ๐ค๐ ๐ถ๐ ๐ผ๐๐๐ฑ๐ฎ๐๐ฒ๐ฑ ๐ฎ๐ป๐ฑ ๐๐ถ๐น๐น ๐ฏ๐ฒ ๐ฟ๐ฒ๐ฝ๐น๐ฎ๐ฐ๐ฒ๐ฑ ๐๐ผ๐ผ๐ป.
โฆ ๐ฅ๐ฒ๐ฎ๐น๐ถ๐๐: SQL is here to stay! Despite the rise of NoSQL, SQL remains the backbone for structured data.
โฆ ๐๐ถ๐ ๐ถ๐: Stay current and explore how SQL integrates with big data platforms and cloud databases. Itโs more relevant than ever.
Donโt let these myths hold you back. SQL is powerful, and when you understand it fully, you can do amazing things with your data.
Here you can find more SQL Resources
๐๐
https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v
Like this post if you need more ๐โค๏ธ
Hope it helps :)
๐8โค1