SQL Fundamentals and Database Design

SQL Syntax

• Basic Syntax:
  • SELECT statements: retrieve data from a database
  • FROM clause: specifies the table(s) to retrieve data from
  • WHERE clause: filters data based on conditions
  • GROUP BY clause: groups data based on one or more columns
  • HAVING clause: filters grouped data based on conditions
• Data Manipulation:
  • INSERT statements: adds new data to a table
  • UPDATE statements: modifies existing data in a table
  • DELETE statements: deletes data from a table
• Data Control:
  • CREATE statements: creates a new database object (e.g., table, index)
  • DROP statements: deletes a database object
  • ALTER statements: modifies a database object

Query Optimization

• Indexing:
  • Creates a data structure to quickly locate data
  • Improves query performance by reducing scan time
• Query Optimization Techniques:
  • Reordering joins: optimizes join operations
  • Subquery optimization: optimizes subqueries
  • Index selection: chooses the best index for a query
• Query Tuning:
  • Analyzes query execution plans
  • Identifies performance bottlenecks
  • Applies optimization techniques

Normalization

• First Normal Form (1NF):
  • Eliminates repeating groups in a table
  • Each row has a unique combination of values
• Second Normal Form (2NF):
  • Each non-key attribute depends on the entire primary key
  • No partial dependencies
• Third Normal Form (3NF):
  • No transitive dependencies
  • Each non-key attribute depends only on the primary key
• Higher Normal Forms:
  • Boyce-Codd Normal Form (BCNF)
  • Fourth Normal Form (4NF)
  • Fifth Normal Form (5NF)
• Denormalization:
  • Intentionally violating normalization rules
  • Improves performance by reducing joins