SQL for Data Analysis


Гео и язык канала: Весь мир, Английский
Категория: Технологии


Find top SQL resources from global universities, cool projects, and learning materials for data analytics.
For promotions: @coderfun
Buy ads: https://telega.io/c/sqlanalyst
Useful links: heylink.me/DataAnalytics

Связанные каналы  |  Похожие каналы

Гео и язык канала
Весь мир, Английский
Категория
Технологии
Статистика
Фильтр публикаций


𝑪𝒐𝒎𝒑𝒓𝒆𝒉𝒆𝒏𝒔𝒊𝒗𝒆 𝒓𝒐𝒂𝒅𝒎𝒂𝒑 𝒕𝒐 𝒃𝒆𝒄𝒐𝒎𝒊𝒏𝒈 𝒂 𝒎𝒂𝒔𝒕𝒆𝒓 𝒊𝒏 𝑺𝑸𝑳:

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.


𝗦𝗤𝗟 𝗣𝗿𝗼𝗷𝗲𝗰𝘁𝘀 𝗧𝗵𝗮𝘁 𝗖𝗮𝗻 𝗔𝗰𝘁𝘂𝗮𝗹𝗹𝘆 𝗚𝗲𝘁 𝗬𝗼𝘂 𝗛𝗶𝗿𝗲𝗱!😍

Want to land a Data Analyst or SQL-based job?

𝐋𝐢𝐧𝐤👇:-

https://pdlink.in/4hCYob9

🚀 Start working on these projects today & boost your SQL skills! 💻


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


𝗙𝗿𝗲𝗲 𝗽𝗿𝗲𝘃𝗶𝗲𝘄 𝗼𝗳 𝗔𝗜-𝗽𝗼𝘄𝗲𝗿𝗲𝗱 𝗣𝗿𝗲𝗺𝗶𝘂𝗺 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 𝗮𝗻𝗱 𝗮𝗰𝗰𝗲𝘀𝘀 𝘁𝗼 𝟭𝟬𝟬𝟬+ 𝗳𝗿𝗲𝗲 𝗼𝗻𝗹𝗶𝗻𝗲 𝗰𝗼𝘂𝗿𝘀𝗲𝘀😍

Benefits:-

- AI Mentor for unlimited, 24/7 doubt resolution
- Coding Exercises with real-time coding assistance
- Mock Interviews with AI-driven personalized prep and more

𝗟𝗶𝗻𝗸👇:-

 https://pdlink.in/4aZZWtf

💥 Limited time offer: Free preview on all Premium Courses + Access to 1000+ free courses on GenAI, Data Science, etc.


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


Репост из: Old Glory Vortex
Trump Takes Action: Tariffs on China, Energy Dominance, Vaccine Ban & IRS Shakeup 🇺🇸🔥

🚨 Major moves from President Trump:

💰Tariffs on China: Trump announced that he has imposed import duties totaling 600 billion rubles—more than any other U.S. president before him.

⚡️Energy Dominance: Trump signed an executive order creating the National Council for Energy Dominance, chaired by Secretary of State Bergum, aiming to unleash America’s full energy potential.

🚫COVID-19 Vaccine Ban in Schools: Schools receiving federal funding can no longer require the COVID-1COVID-19 vaccine—a decisive move that shuts down speculation about Trump's stance on vaccines.

📉Reports suggest the IRS is prepaIRS is preparing mass layoffs next week followingmajor audit of the agency.

🔥Bold moves, big changes—what’s next?

#Trump #Tariffs #EnergyDominance #COVID19 #VaccineBan #IRS #China #AmericaFirst #BreakingNews

Don't miss it, subscribe to
📱 Old Glory Vortex 🇺🇸


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.


𝗟𝗲𝗮𝗿𝗻 𝗣𝘆𝘁𝗵𝗼𝗻 𝗳𝗼𝗿 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘😍

Want to master Python and level up your data analytics skills?✨️

These high-quality tutorials to help you go from beginner to pro!✅️

𝐋𝐢𝐧𝐤👇:-

https://pdlink.in/4hXQOHQ

📢 No cost, no catch – just pure learning! 🚀


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)Business Analysis – Foundation
2)Business Analysis Fundamentals
3)The Essentials of Business & Risk Analysis 
4)Master Microsoft Power BI 

𝗟𝗶𝗻𝗸 👇:-

https://pdlink.in/4hHxBdW

Enroll For FREE & Get Certified🎓


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:
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.me/sqlspecialist

Like this post if you want me to continue SQL Interview Preparation Series 👍❤️

Hope it helps :)


𝗙𝗥𝗘𝗘 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻𝘀 𝗧𝗼 𝗕𝗼𝗼𝘀𝘁 𝗬𝗼𝘂𝗿 𝗖𝗮𝗿𝗲𝗲𝗿😍

1) Introduction to Cyber Security
2) AWS Cloud Masterclass
3)Salesforce Developer Catalyst
4) Python Basics
5) Project Management Basics

𝗟𝗶𝗻𝗸 👇:-

https://pdlink.in/4jQJfo5

Enroll For FREE & Get Certified🎓


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


Репост из: Trump's Ear
Видео недоступно для предпросмотра
Смотреть в Telegram
George Soros said at the WEF that President Trump is a fraud and a complete narcissist who wants the world to revolve around him.

#Soros #WEF #Trump

👂 More on Trump's Ear ⚠️


SQL Essentials 👆


𝗠𝗮𝘀𝘁𝗲𝗿 𝗦𝗤𝗟 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘 & 𝗨𝗻𝗹𝗼𝗰𝗸 𝗛𝗶𝗴𝗵-𝗣𝗮𝘆𝗶𝗻𝗴 𝗢𝗽𝗽𝗼𝗿𝘁𝘂𝗻𝗶𝘁𝗶𝗲𝘀!😍

Top 3 Free YouTube Playlists to Learn SQL

1)SQL Tutorial Videos
2)SQL Mastery: From Basics to Advanced
3)Learn Complete SQL (Beginner to Advanced)

𝗟𝗶𝗻𝗸 👇:-

https://pdlink.in/4hFyseX

Enroll For FREE & Get Certified🎓


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.me/DataAnalystInterview/158

ENJOY LEARNING 👍👍


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.


𝗟𝗲𝗮𝗿𝗻 𝗣𝘆𝘁𝗵𝗼𝗻 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘 𝘄𝗶𝘁𝗵 𝗚𝗼𝗼𝗴𝗹𝗲’𝘀 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲𝘀!😍

You want to break into IT automation, data analysis, or software development✨️

These FREE Google-backed courses will help you master Python from scratch!💡

𝐋𝐢𝐧𝐤👇:-

https://pdlink.in/42QHRM5

📢 Don’t miss out! Invest in your future and start learning today! 🚀


SQL Interviews LOVE to test you on Window Functions. Here’s the list of 7 most popular window functions

👇 𝟕 𝐌𝐨𝐬𝐭 𝐓𝐞𝐬𝐭𝐞𝐝 𝐖𝐢𝐧𝐝𝐨𝐰 𝐅𝐮𝐧𝐜𝐭𝐢𝐨𝐧𝐬

* RANK() - gives a rank to each row in a partition based on a specified column or value

* DENSE_RANK() - gives a rank to each row, but DOESN'T skip rank values

* ROW_NUMBER() - gives a unique integer to each row in a partition based on the order of the rows

* LEAD() - retrieves a value from a subsequent row in a partition based on a specified column or expression

* LAG() - retrieves a value from a previous row in a partition based on a specified column or expression

* NTH_VALUE() - retrieves the nth value in a partition

Hope it helps :)

Показано 20 последних публикаций.