Window Functions (79–88)

 

Window Functions (79–88)

79. What is a window function?
    It performs calculations across rows without collapsing them.
80. What is ROW_NUMBER()?
    Assigns unique row number.
81. What is RANK()?
    Assigns rank with gaps for ties.
82. What is DENSE_RANK()?
    Assigns rank without gaps.
83. Difference between RANK and DENSE_RANK?
    RANK skips numbers after tie; DENSE_RANK does not.
84. What is PARTITION BY?
    Divides result set into groups for window functions.
85. Find highest salary in each department.
    Use RANK() with PARTITION BY dept.
86. What is LEAD()?
    Fetches next row value.
87. What is LAG()?
    Fetches previous row value.
88. Scenario: Compare current month sales with previous month.
    Use LAG(sales).

Bhagavad Gita Wisdom #shorts

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

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

Key Concepts - Primary Keys and Foreign Keys

 Primary Keys and Foreign Keys 

• Explain the role of primary keys and foreign keys in maintaining data integrity. 

Primary keys and foreign keys are essential database constraints that play a crucial role in maintaining data integrity within a relational database system. They ensure that data is accurate, consistent, and reliable. Here's an explanation of their roles in data integrity: 

Primary Keys: 

• Definition: A primary key is a unique identifier for each record (row) in a database table. It ensures that each record in the table can be uniquely identified and distinguished from others. 

• Uniqueness: A primary key constraint enforces the uniqueness of values in the designated column(s). No two records in the table can have the same primary key value. 

• Data Integrity: Primary keys enforce data integrity by preventing duplicate or null values in the identifier column(s). This ensures that each record is uniquely identifiable. 

• Indexing: Primary keys are often indexed, which allows for efficient data retrieval. Queries that involve searching for specific records or joining tables benefit from this indexing. 

• Relationships: Primary keys serve as the basis for establishing relationships with other tables. In related tables, the primary key of one table can be used as a foreign key in another, creating referential integrity. 

Foreign Keys: 

• Definition: A foreign key is a column or set of columns in a table that is used to establish a link between the data in two tables. It creates a relationship between the tables based on a common attribute. 

• Referential Integrity: Foreign keys enforce referential integrity, ensuring that data in the related tables remains consistent. They define a relationship between a child table (containing the foreign key) and a parent table (containing the primary key). 

• Data Consistency: When a foreign key exists in a child table, it must refer to a valid primary key value in the parent table. This ensures that the data in the child table is consistent and accurate. 

• Cascading Actions: Foreign keys can be configured with cascading actions, such as CASCADE DELETE or CASCADE UPDATE, which automatically propagate changes from the parent table to the child table, maintaining data consistency. 

• Enforcement of Relationships: Foreign keys enforce relationships between tables, preventing or controlling actions that would violate these relationships, such as attempting to insert a record with a foreign key value that does not exist in the parent table. 

• Navigation: Foreign keys provide a way to navigate and retrieve related data across tables. They enable the creation of joins and help retrieve data from multiple related tables. 

Roles in Maintaining Data Integrity: 

• Primary keys ensure that each record in a table is uniquely identified, preventing duplicate or null values. They are the foundation for establishing relationships with other tables. 

• Foreign keys enforce referential integrity, ensuring that data in related tables remains consistent. They control the relationships between tables and prevent actions that could compromise data integrity. 

Together, primary keys and foreign keys create a structure that promotes data accuracy and consistency within a relational database. They are essential for maintaining the integrity of the data and ensuring that it remains reliable and meaningful. 

 

Key Concepts in Data Modelling - Normalization

  Normalization 

Normalization is a crucial concept in database design that involves structuring a relational database in a way that reduces data redundancy and ensures data integrity. It is particularly important in Online Transaction Processing (OLTP) databases, which are designed for day-to-day transactional operations. Here, we'll introduce the concept of normalization and discuss its significance in OLTP databases: 

1. Definition of Normalization: 

• Normalization is a systematic process of organizing data in a relational database to eliminate data anomalies and redundancies. 

• It divides a database into multiple related tables and establishes relationships between them, ensuring that each piece of data is stored in only one place. 

2. Importance in OLTP Databases: 

A. Data Integrity: 

• Normalization enhances data integrity by minimizing data redundancy. Redundant data can lead to inconsistencies and anomalies, such as update anomalies, insertion anomalies, and deletion anomalies. 

• In OLTP databases, maintaining data accuracy and consistency is critical because they handle real-time transactional activities (e.g., order processing, inventory management, and customer interactions). 

B. Space Efficiency: 

• Normalization reduces storage space requirements by eliminating redundant data. This can lead to more efficient storage utilization, which is beneficial for OLTP databases where data storage costs can be significant. 

C. Query Performance: 

• While normalization may create more tables and relationships, it can improve query performance by reducing the amount of data that needs to be accessed and processed. 

• In OLTP databases, where rapid retrieval and updates of specific transactional data are essential, query efficiency is crucial. 

D. Simplified Updates: 

• Normalization simplifies data updates and maintenance. With non-redundant data, changes need to be made in only one place, reducing the risk of inconsistent or incomplete updates. 

• In OLTP databases, where data is frequently updated, this simplification helps maintain data accuracy. 

E. Adherence to Business Rules: 

• Normalization encourages the proper representation of business rules and constraints in the database schema. This ensures that the database enforces data integrity rules and follows the logic of the business processes. 

F. Scalability: 

• Normalization supports the scalability of OLTP databases. As the volume of transactional data increases, a well-normalized schema is easier to scale and adapt to changing requirements. 

3. Normal Forms: 

• Normalization is typically achieved through a series of steps called normal forms (e.g., First Normal Form, Second Normal Form, Third Normal Form, and Boyce-Codd Normal Form). Each normal form addresses specific data integrity and redundancy issues. 

• OLTP databases are often normalized up to at least Third Normal Form (3NF) to strike a balance between data integrity and performance. 

In summary, normalization is essential in OLTP databases to ensure data integrity, optimize storage space, improve query performance, simplify updates, and maintain adherence to business rules. By organizing data efficiently, normalization helps OLTP databases handle high volumes of transactions and ensures that the data remains accurate and consistent, which is critical for day-to-day operations.