Joins (36–50)

36. What is a JOIN?
    A JOIN combines rows from multiple tables.
37. What is INNER JOIN?
    Returns matching rows from both tables.
38. What is LEFT JOIN?
    Returns all rows from left table and matching rows from right.
39. What is RIGHT JOIN?
    Returns all rows from right table and matching rows from left.
40. What is FULL OUTER JOIN?
    Returns all matching and non-matching rows from both tables.
41. What is CROSS JOIN?
    Returns Cartesian product of both tables.
42. What is SELF JOIN?
    A table joined with itself.
43. Difference between INNER and LEFT JOIN?
    INNER returns matches only; LEFT returns all left rows.
44. What happens if no match in LEFT JOIN?
    NULL values are returned for right table columns.
45. What is Cartesian product?
    Every row from first table joins with every row from second.
46. Can we join more than 2 tables?
    Yes, multiple joins are allowed.
47. Which join is used to find unmatched records?
    LEFT JOIN with WHERE right_table.id IS NULL.
48. Scenario: Find customers with no orders.
    Use LEFT JOIN between customers and orders, filter NULL orders.
49. Scenario: Get employee with manager name.
    Use SELF JOIN on employee table.
50. Can we join tables without primary key?
    Yes, using common columns. 

Bhagavad Gita Wisdom #shorts

https://www.youtube.com/playlist?list=PLQM-BpTd9ZSumxwKgJjuJjlx2OcP_W516

Filtering & Sorting (21–35)

 

21. What does WHERE clause do?
    It filters rows based on conditions.
22. Difference between WHERE and HAVING?
    WHERE filters rows before grouping; HAVING filters groups after aggregation.
23. What is ORDER BY?
    It sorts query results in ascending or descending order.
24. Default sort order in SQL?
    Ascending (ASC) by default.
25. What is DISTINCT?
    It removes duplicate values from result.
26. How do you fetch top 5 records?
    SELECT TOP 5 * FROM table; (SQL Server)
27. How do you fetch first 5 rows in MySQL?
    SELECT * FROM table LIMIT 5;
28. What is BETWEEN?
    It filters values within a range.
29. What is IN operator?
    It matches values from a given list.
30. What is LIKE used for?
    It is used for pattern matching.
31. What does % mean in LIKE?
    It matches zero or more characters.
32. What does _ mean in LIKE?
    It matches exactly one character.
33. How do you handle NULL values?
    Use IS NULL or IS NOT NULL.
34. Can we use = NULL?
    No, use IS NULL.
35. What is alias in SQL?
    A temporary name given to a column or table.

Bhagavad Gita Wisdom #shorts

https://www.youtube.com/playlist?list=PLQM-BpTd9ZSumxwKgJjuJjlx2OcP_W516

OLTP vs. OLAP Databases

 OLTP (Online Transaction Processing) and OLAP (Online Analytical Processing) are two distinct types of database systems designed to serve different purposes in an organization. Here are the key differences between OLTP and OLAP databases:

1. Purpose:

·        OLTP: OLTP databases are designed for transactional processing. They handle day-to-day operational data, such as order processing, inventory management, and customer interactions. The primary goal is to support real-time transactional activities and ensure data accuracy.

·        OLAP: OLAP databases are designed for analytical processing. They store historical data, often in large volumes, and support complex queries for reporting, data analysis, and decision-making. OLAP databases focus on providing insights from data rather than handling individual transactions.

2. Data Structure:

·        OLTP: OLTP databases have a normalized data structure, which minimizes data redundancy. They use many small, related tables and enforce referential integrity through primary keys and foreign keys.

·        OLAP: OLAP databases typically use a denormalized or star/snowflake schema structure. Data is aggregated, and dimensional modeling is often employed to facilitate complex queries and reporting.

3. Query Complexity:

·        OLTP: OLTP queries are relatively simple and usually involve basic CRUD (Create, Read, Update, Delete) operations. The focus is on fast and efficient retrieval and modification of individual records.

·        OLAP: OLAP queries are complex and involve aggregations, joins, and multidimensional analysis. These queries are optimized for reporting and data analysis, and response times can be longer compared to OLTP systems.

4. Data Volume:

·        OLTP: OLTP databases handle a moderate volume of data, typically focused on current and recent transactions. They prioritize fast write operations and are optimized for low-latency access.

·        OLAP: OLAP databases store a large volume of historical data, often including years of data for analytical purposes. They prioritize read-heavy operations for complex reporting and analysis.

5. User Types:

·        OLTP: OLTP systems primarily serve operational users who interact with the system in real-time, such as customer service representatives, sales teams, and order processing personnel.

·        OLAP: OLAP systems cater to analytical users, including business analysts, data scientists, and decision-makers, who require in-depth data analysis, ad-hoc querying, and reporting capabilities.

6. Data Freshness:

·        OLTP: Data in OLTP databases is typically up-to-date in real-time, reflecting the current state of the business operations.

·        OLAP: OLAP databases may have a slight delay in data updates due to the process of aggregating and loading large volumes of historical data.

7. Indexing:

·        OLTP: OLTP databases use minimal indexing to optimize write operations and maintain data integrity.

·        OLAP: OLAP databases use extensive indexing and pre-aggregated data to optimize read-heavy analytical queries.

8. Examples:

·        OLTP: Examples of OLTP systems include e-commerce platforms, banking systems, and airline reservation systems.

·        OLAP: Examples of OLAP systems include data warehouses, business intelligence platforms, and reporting systems.

In summary, OLTP databases focus on real-time transactional processing with normalized data structures, while OLAP databases are designed for analytical processing, historical data storage, and complex reporting with denormalized structures. These differences reflect the distinct roles