SQL Recursive CTE for Hierarchy Traversal

🚀 SQL Basics – Day 9: Stored Procedures (Super Simple) Today let’s learn how to save SQL logic and reuse it anytime 💡 👇 🔍 What is a Stored Procedure? 👉 A saved SQL code 👉 You can run it anytime 🧠 “Like a function in SQL” --- 📌 Create Procedure 💡 "CREATE PROCEDURE GetEmployees() BEGIN SELECT * FROM employees; END;" 👉 Save your query --- ▶️ Run Procedure 💡 "CALL GetEmployees();" 👉 Execute anytime --- ✏️ Procedure with Parameter 💡 "CREATE PROCEDURE GetByDept(IN dept_name VARCHAR(50)) BEGIN SELECT * FROM employees WHERE department = dept_name; END;" 👉 Pass value while running --- 😄 Easy way to remember: Procedure = Saved code CREATE = Save CALL = Run Parameter = Input value --- ✨ Conclusion: Stored procedures save time and reduce repeated work 💪 They make your SQL more powerful and reusable 🚀 📌 Work smart by writing once and using many times! #SQL #DataAnalytics #SQLBasics #StoredProcedure #LearningSQL #Day9 #DataAnalyst

🚀 SQL CTE (Common Table Expression) – Simplifying Complex Queries Continuing my SQL journey with CTEs, a powerful way to make queries more readable and modular 👇 🔹 What is a CTE? A CTE (Common Table Expression) is a temporary result set defined using WITH that you can reference within a query. 🔹 Basic Syntax WITH cte_name AS ( SELECT column1, column2 FROM table_name ) SELECT * FROM cte_name; 🔹 1. Simple CTE Example WITH avg_price AS ( SELECT AVG(unitPrice) AS avg_val FROM samples.bakehouse.sales_transactions ) SELECT * FROM samples.bakehouse.sales_transactions WHERE unitPrice > (SELECT avg_val FROM avg_price); 🔹 2. CTE with GROUP BY WITH product_sales AS ( SELECT product, SUM(unitPrice) AS total_sales FROM samples.bakehouse.sales_transactions GROUP BY product ) SELECT * FROM product_sales WHERE total_sales > 2000; 🔹 3. CTE with JOIN WITH customer_txn AS ( SELECT c.customerID, c.customerName, t.unitPrice FROM samples.bakehouse.sales_customers c JOIN samples.bakehouse.sales_transactions t ON c.customerID = t.customerID ) SELECT customerID, SUM(unitPrice) AS total_spent FROM customer_txn GROUP BY customerID; 🔹 4. Multiple CTEs WITH avg_price AS ( SELECT product, AVG(unitPrice) AS avg_val FROM samples.bakehouse.sales_transactions GROUP BY product ), filtered_data AS ( SELECT t.* FROM samples.bakehouse.sales_transactions t JOIN avg_price a ON t.product = a.product WHERE t.unitPrice > a.avg_val ) SELECT * FROM filtered_data; 💡 Key Learnings: Improves readability & maintainability Helps break complex queries into steps Reusable within the same query Often better than nested subqueries #SQL #DataAnalytics #DataEngineering #CTE #Learning #PySpark #Databricks #100DaysOfCode

🔰 PHASE–2 | CORE SQL QUERIES SELECT Statement – The Foundation of Data Retrieval The SELECT statement is the backbone of SQL. Every meaningful interaction with a database begins here. In this phase, I focused on: 📌 Basic SELECT syntax – understanding query structure 📌 Selecting specific columns – retrieving only relevant data 📌 Readable & efficient queries – clarity matters in real projects Mastering SELECT is not just about fetching data — it’s about asking the right questions from the database. This forms the base for advanced concepts like filtering, aggregation, and analytics used in: 💼 Backend Development 📊 Data Analysis 🗄 Database-driven Applications Step by step, strengthening my SQL fundamentals — one query at a time. 🚀 #SQL #DatabaseFundamentals #BackendDevelopment #DataSkills #LearningInPublic #TechCareers #SoftwareEngineering #SQLQueries #CareerGrowth

📊 Choosing the right data type in SQL? It matters more than you think. Your database schema directly impacts storage, performance, and query accuracy. Here's a quick reference cheat sheet for the most common SQL data types: - Numeric-Integer: `TINYINT` → `BIGINT` for whole numbers. Use `INT` as your default. - Numeric-Decimal: `FLOAT`, `DOUBLE` for approximate values, `DECIMAL/NUMERIC` when precision matters, like money. - Date & Time: `DATE`, `DATETIME`, `TIMESTAMP`, `TIME`, `YEAR` to handle all temporal data. - String (Character): `CHAR` for fixed length, `VARCHAR` for variable. `TEXT` types for long-form content. - String (Binary): `BLOB` types for storing files, images, and other binary data. - Enumerated: `ENUM` for one choice from a list, `SET` for multiple choices. Picking the smallest data type that safely fits your data = faster queries + lower storage cost #SQL #Database #DataEngineering #Backend #SoftwareEngineering #TechTips #DataTypes#frontlinesedutech #flm #frontlinesmedia #DataAnalytics

🚀 Day 31 of My SQL Journey – 𝗜𝗻𝘁𝗿𝗼𝗱𝘂𝗰𝘁𝗶𝗼𝗻 𝘁𝗼 𝗖𝗧𝗘𝘀 (𝗖𝗼𝗺𝗺𝗼𝗻 𝗧𝗮𝗯𝗹𝗲 𝗘𝘅𝗽𝗿𝗲𝘀𝘀𝗶𝗼𝗻𝘀) Today I explored 𝗖𝗧𝗘𝘀, a powerful SQL feature that helps write cleaner, more structured, and readable queries. 🔹 Learned how to use the `WITH` clause to create temporary result sets 🔹 Understood how CTEs simplify complex queries and replace nested subqueries 🔹 Practiced grouping and filtering data using CTEs 🔹 Explored real use cases like data transformation and hierarchical queries 💡 𝗢𝗻𝗲 𝗸𝗲𝘆 𝘁𝗮𝗸𝗲𝗮𝘄𝗮𝘆: CTEs not only improve readability but also make SQL queries more maintainable and professional. Here’s a simple example I worked on: calculating total amount per customer and filtering those above a threshold using a CTE. 📈 Step by step, getting better at writing optimized and structured SQL queries! #SQL #CTE #DataAnalytics #LearningJourney #Database #100DaysOfCode #TechSkills

🚀 #30DaysOfSQL – Day 28 Continuing my 30 Days of SQL Challenge to strengthen my SQL and database management skills. 📌 Topic for Day 28: STORED PROCEDURES – Reusable SQL Logic Today I learned about Stored Procedures, which allow us to save SQL queries as reusable blocks inside the database. This helps in reducing repetition and improving performance. 💡 Practice Example: Create a stored procedure to retrieve employees with salary greater than a given value. 🧠 SQL Example: CREATE PROCEDURE GetHighSalaryEmployees(IN min_salary INT) BEGIN SELECT name, salary FROM employees WHERE salary > min_salary; END; 📊 Key Learnings: • Stored procedures store reusable SQL logic • Reduce code duplication • Improve performance and security • Can accept input parameters Stored procedures are useful for automating repetitive database operations. Excited to continue learning with Day 29! #SQL #30DaysOfSQL #DataScience #LearningInPublic #SQLPractice #DataAnalytics

Day 56 🗄️ | Exploring SQL Commands & Constraints Today I learned some fundamental SQL commands used to create, modify, and manage database structures and data. 🔹 SQL Commands I Learned 📌 CREATE Used to create databases, tables, and schemas 📌 ALTER Modify existing table structure (add/remove columns) 📌 DROP Deletes entire table or database permanently 📌 DELETE Removes specific rows from a table 📌 TRUNCATE Removes all rows from a table quickly (without deleting structure) 🔹 Constraints in SQL Constraints are rules applied to ensure data accuracy and integrity: PRIMARY KEY → Unique identifier for each row FOREIGN KEY → Links tables together NOT NULL → Prevents empty values UNIQUE → Ensures no duplicate values DEFAULT → Sets default value 💡 Key Takeaway Understanding SQL commands and constraints is essential to build structured, reliable, and clean databases. Step by step moving from basic concepts → practical SQL skills. Krishna Mantravadi Rakesh Viswanath Frontlines EduTech (FLM) #Day56 #SQL #Database #DataAnalytics #LearningJourney #DataAnalyst

⚡ SQL Days 27 & 28: Subquery Mastery These two days focused on moving from basic syntax to advanced logic building, learning to break complex problems into nested steps. 🧠 Logic Flow Inner Query: Runs first to find a specific value (e.g., the average price). Outer Query: Uses that value to filter the final results. 🛠️ The 3 Essential Types Single-Row: Returns one value. Use with =, >, <. Multi-Row: Returns a list. Use with IN, ANY, or ALL. Correlated: Runs once for every row in the outer query. Powerful for row-by-row comparisons but can be slower. 💡 Key Takeaways Performance: If a subquery is too slow, consider using a JOIN. Inside-Out: Always test the inner query first to ensure the data types match. Day 29 Loading... #SQLJourney #Subqueries #DataLogic #SQLTips

Day 16 & 17/30 — SQL Server Revision Journey Over the last two days, I focused on intermediate-level SQL problem-solving to strengthen my practical understanding of advanced concepts. Practice Focus Worked on problems involving: Complex JOIN scenarios Aggregations with GROUP BY & HAVING Subqueries for dynamic filtering Data comparison across multiple tables Identifying missing and unmatched records Types of Problems Solved Find records present in one table but not in another Retrieve top-performing entities based on conditions Compare values across rows using subqueries Combine multiple conditions for real-world scenarios Analyze grouped data to extract meaningful insights Key Learning This phase helped me understand: Writing queries is one part Structuring logic to solve problems is the real skill I started focusing more on: Breaking down problems before writing queries Choosing the right approach (JOIN vs Subquery) Improving query clarity and readability Progress Reflection Moving from: Basic queries → Concept understanding → Problem solving Next Focus Window Functions (RANK, LEAD, LAG) Query optimization techniques Consistency continues. #SQL #SQLServer #DataAnalytics #DataAnalyst #AdvancedSQL #ProblemSolving #LearningJourney #Upskilling

🚀 From Beginner to Advanced in SQL! I’ve started a structured journey to master SQL step-by-step — covering everything from basics to performance tuning. 📌 What I’m learning: • SQL fundamentals & database concepts • Data types, functions & queries • Joins (basic to advanced) • Subqueries, aggregation & grouping • Indexing & query optimization Consistency over intensity. Let’s grow daily! 💡 #SQL #DataAnalytics #LearningJourney #TechSkills #CareerGrowth #SAMAITechnologies