SQL Query Types and Components

Queries

• Types of SQL Queries:
  • DML (Data Manipulation Language): INSERT, UPDATE, DELETE
  • DDL (Data Definition Language): CREATE, DROP, ALTER
  • DQL (Data Query Language): SELECT
  • TCL (Transaction Control Language): COMMIT, ROLLBACK
• Query Components:
  • SELECT: specifies columns to retrieve
  • FROM: specifies tables to retrieve from
  • WHERE: specifies conditions for which rows to retrieve
  • GROUP BY: groups rows based on one or more columns
  • HAVING: specifies conditions for which groups to retrieve
  • ORDER BY: specifies order of retrieved rows
  • LIMIT: limits number of retrieved rows
• Query Optimization:
  • Use indexes to speed up queries
  • Avoid using SELECT * and instead specify only necessary columns
  • Use efficient join types (e.g. INNER JOIN instead of CROSS JOIN)

Normalization

• Goals of Normalization:
  • Eliminate data redundancy and inconsistencies
  • Improve data integrity and scalability
  • Reduce data storage requirements
• Normalization Rules:
  • First Normal Form (1NF): each table cell must contain a single value
  • Second Normal Form (2NF): each non-key attribute 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
• Denormalization:
  • intentional denormalization for performance or simplicity reasons
  • should be done carefully to avoid data inconsistencies

Database Design

• Database Design Principles:
  • Separation of Concerns: each table should have a single, well-defined purpose
  • Data Independence: data should be independent of application logic
  • Data Consistency: data should be consistent across the database
• Entity-Relationship Diagrams (ERDs):
  • visual representation of database design
  • entities (tables) and relationships (foreign keys) are modeled
• Database Schema:
  • defines the structure of the database
  • includes table definitions, relationships, and constraints

Joins

• Types of Joins:
  • INNER JOIN: returns only matching rows from both tables
  • LEFT JOIN: returns all rows from the left table and matching rows from the right table
  • RIGHT JOIN: returns all rows from the right table and matching rows from the left table
  • FULL OUTER JOIN: returns all rows from both tables
  • CROSS JOIN: returns the Cartesian product of both tables
• Join Conditions:
  • Equi-Join: join based on equal values
  • Natural Join: join based on common columns
  • Self-Join: join a table with itself

Indexing

• Types of Indexes:
  • Clustered Index: physical ordering of table rows based on index
  • Non-Clustered Index: separate data structure that contains index keys and pointers to table rows
  • Composite Index: index based on multiple columns
  • Unique Index: ensures uniqueness of indexed column(s)
• Indexing Benefits:
  • improved query performance
  • reduced I/O operations
  • faster data retrieval
• Indexing Considerations:
  • index maintenance overhead
  • additional storage requirements
  • potential negative impact on write performance

SQL Queries

• DML (Data Manipulation Language) queries: INSERT, UPDATE, DELETE
• DDL (Data Definition Language) queries: CREATE, DROP, ALTER
• DQL (Data Query Language) queries: SELECT
• TCL (Transaction Control Language) queries: COMMIT, ROLLBACK
• SELECT specifies columns to retrieve
• FROM specifies tables to retrieve from
• WHERE specifies conditions for which rows to retrieve
• GROUP BY groups rows based on one or more columns
• HAVING specifies conditions for which groups to retrieve
• ORDER BY specifies order of retrieved rows
• LIMIT limits number of retrieved rows

Query Optimization

• Use indexes to speed up queries
• Avoid using SELECT * and specify only necessary columns instead
• Use efficient join types (e.g. INNER JOIN instead of CROSS JOIN)

Normalization

• Goals: eliminate data redundancy and inconsistencies, improve data integrity and scalability, reduce data storage requirements
• First Normal Form (1NF): each table cell must contain a single value
• Second Normal Form (2NF): each non-key attribute 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
• Denormalization: intentional denormalization for performance or simplicity reasons, should be done carefully to avoid data inconsistencies

Database Design

• Database Design Principles: Separation of Concerns, Data Independence, Data Consistency
• Separation of Concerns: each table should have a single, well-defined purpose
• Data Independence: data should be independent of application logic
• Data Consistency: data should be consistent across the database
• Entity-Relationship Diagrams (ERDs): visual representation of database design, entities (tables) and relationships (foreign keys) are modeled
• Database Schema: defines the structure of the database, includes table definitions, relationships, and constraints

Joins

• Types of Joins: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN
• INNER JOIN: returns only matching rows from both tables
• LEFT JOIN: returns all rows from the left table and matching rows from the right table
• RIGHT JOIN: returns all rows from the right table and matching rows from the left table
• FULL OUTER JOIN: returns all rows from both tables
• CROSS JOIN: returns the Cartesian product of both tables
• Join Conditions: Equi-Join, Natural Join, Self-Join
• Equi-Join: join based on equal values
• Natural Join: join based on common columns
• Self-Join: join a table with itself

Indexing

• Types of Indexes: Clustered Index, Non-Clustered Index, Composite Index, Unique Index
• Clustered Index: physical ordering of table rows based on index
• Non-Clustered Index: separate data structure that contains index keys and pointers to table rows
• Composite Index: index based on multiple columns
• Unique Index: ensures uniqueness of indexed column(s)
• Indexing Benefits: improved query performance, reduced I/O operations, faster data retrieval
• Indexing Considerations: index maintenance overhead, additional storage requirements, potential negative impact on write performance