Database Indexes

Indexes

• Definition: An index is a data structure that improves the speed of data retrieval by providing a quick way to locate specific data.
• Types:
  • Clustered Index: Reorders the physical records of the table to match the index, which can improve range queries.
  • Non-Clustered Index: Creates a separate data structure that contains the index keys and pointers to the data.
• Advantages:
  • Faster query performance
  • Reduced I/O operations
  • Improved data retrieval
• Disadvantages:
  • Increased storage space
  • Slower data insertion and update operations

Joins

• Definition: A join is a SQL operation that combines rows from two or more tables based on a common column.
• Types:
  • INNER JOIN: Returns only the rows that have a match in both tables.
  • LEFT JOIN: Returns all the rows from the left table and the matched rows from the right table.
  • RIGHT JOIN: Returns all the rows from the right table and the matched rows from the left table.
  • FULL OUTER JOIN: Returns all the rows from both tables.
• Join Conditions:
  • Equi-Join: Join based on equality of columns.
  • Natural Join: Join based on all columns with the same names.
  • Cross Join: Join that returns the Cartesian product of both tables.

Subqueries

• Definition: A subquery is a query nested inside another query.
• Types:
  • Nested Subquery: A subquery that is nested inside another query.
  • Correlated Subquery: A subquery that references columns from the outer query.
  • Non-Correlated Subquery: A subquery that does not reference columns from the outer query.
• Advantages:
  • Allows for more complex queries
  • Enables the use of aggregate functions
  • Improves query performance

Normalization

• Definition: Normalization is the process of organizing data in a database to minimize data redundancy and dependency.
• Normal Forms:
  • First Normal Form (1NF): Each table cell must contain a single value.
  • Second Normal Form (2NF): Each non-key attribute in a table must depend on the entire primary key.
  • Third Normal Form (3NF): If a table is in 2NF, and a non-key attribute depends on another non-key attribute, then it should be moved to a separate table.
• Advantages:
  • Reduces data redundancy
  • Improves data integrity
  • Enhances scalability

Queries

• Definition: A query is a request for specific data or action from a database.
• Types:
  • SELECT: Retrieves data from a database table.
  • INSERT: Adds new data to a database table.
  • UPDATE: Modifies existing data in a database table.
  • DELETE: Deletes data from a database table.
• Clauses:
  • WHERE: Specifies conditions for which data to retrieve or modify.
  • FROM: Specifies the tables to retrieve data from.
  • GROUP BY: Groups data by one or more columns.
  • HAVING: Specifies conditions for which groups to include.
  • ORDER BY: Sorts data in ascending or descending order.

Indexes

• A data structure that speeds up data retrieval by providing a quick way to locate specific data.
• Types of indexes include:
  • Clustered Index, which reorders physical records to match the index, improving range queries.
  • Non-Clustered Index, which creates a separate data structure containing index keys and pointers to the data.
• Indexes offer advantages such as:
  • Faster query performance.
  • Reduced I/O operations.
  • Improved data retrieval.
• However, indexes also have disadvantages, including:
  • Increased storage space.
  • Slower data insertion and update operations.

Joins

• A SQL operation that combines rows from two or more tables based on a common column.
• Types of joins include:
  • INNER JOIN, which returns only rows with matches in both tables.
  • LEFT JOIN, which returns all rows from the left table and matched rows from the right table.
  • RIGHT JOIN, which returns all rows from the right table and matched rows from the left table.
  • FULL OUTER JOIN, which returns all rows from both tables.
• Join conditions include:
  • Equi-Join, which joins based on equality of columns.
  • Natural Join, which joins based on all columns with the same names.
  • Cross Join, which returns the Cartesian product of both tables.

Subqueries

• A query nested inside another query.
• Types of subqueries include:
  • Nested Subquery, which is a subquery nested inside another query.
  • Correlated Subquery, which references columns from the outer query.
  • Non-Correlated Subquery, which does not reference columns from the outer query.
• Subqueries offer advantages such as:
  • Allowing for more complex queries.
  • Enabling the use of aggregate functions.
  • Improving query performance.

Normalization

• The process of organizing data in a database to minimize data redundancy and dependency.
• Normal Forms include:
  • First Normal Form (1NF), which requires each table cell to contain a single value.
  • Second Normal Form (2NF), which requires each non-key attribute to depend on the entire primary key.
  • Third Normal Form (3NF), which requires non-key attributes to depend on the primary key only.
• Normalization offers advantages such as:
  • Reducing data redundancy.
  • Improving data integrity.
  • Enhancing scalability.

Queries

• A request for specific data or action from a database.
• Types of queries include:
  • SELECT, which retrieves data from a database table.
  • INSERT, which adds new data to a database table.
  • UPDATE, which modifies existing data in a database table.
  • DELETE, which deletes data from a database table.
• Query clauses include:
  • WHERE, which specifies conditions for which data to retrieve or modify.
  • FROM, which specifies the tables to retrieve data from.
  • GROUP BY, which groups data by one or more columns.
  • HAVING, which specifies conditions for which groups to include.
  • ORDER BY, which sorts data in ascending or descending order.

Learn about indexes in databases, including their definition, types, and advantages. Understand how they improve query performance and data retrieval.