Chapter 4: Key Notes on Relational Databases and SQL Usage

  • Relational Databases Overview

    • Organized in rows and columns of tables

    • Uses Structured Query Language (SQL) for querying

    • Provides performance, security, concurrency, and recovery advantages

  • Key Concepts

    • Tables: Structure with fixed columns and variable rows

    • Rows: Sequence of values, each corresponding to a column's data type

    • Columns: Have names and enforce data types

    • Primary Key: Unique identifier for rows, should not contain NULL values

    • Foreign Key: References primary keys and can include NULL

    • NULL Values: Represents incomplete or inapplicable data

  • SQL and Statements

    • SQL consists of keywords, clauses, identifiers, and literals

    • Statements include SELECT, INSERT, DELETE, and UPDATE

    • Syntax rules dictate the formulation of commands

  • Operators

    • Arithmetic Operators: +, -, *, /, %

    • Comparison Operators: =, !=, <, <=, >, >=

    • Logical Operators: AND, OR, NOT

  • Queries

    • SELECT statement retrieves data from tables

    • FROM clause specifies the data source

    • WHERE clause filters records based on conditions

    • ORDER BY clause sorts results

    • LIMIT clause restricts the number of results

  • Aggregate Functions

    • Functions like COUNT(), AVG(), MIN(), MAX(), SUM() summarize data

    • Use GROUP BY clause to aggregate results based on specific fields

  • Joins

    • INNER JOIN: Returns matched rows from both tables

    • LEFT JOIN: Returns all rows from the left table, matched from the right

    • RIGHT JOIN: Returns all rows from the right table, matched from the left

    • FULL JOIN: Returns all rows from both tables, matched or not

    • Equijoins and Non-Equijoins compare columns with specific conditions

  • Subqueries

    • Queries nested within other queries to retrieve related data

    • Correlated subqueries depend on outer query values

    • EXISTS operator checks if subqueries return rows

  • Python Integration

    • Connect to MySQL databases using mysql.connector

    • Use cursors for executing SQL queries and fetching results

    • Handle SQL injection using parameterized queries

    • Fetch methods: fetchone(), fetchall() for retrieving data

    • Implement query parameters for safe database operations

  • Using SQL and Pandas

    • SQL is efficient for data retrieval; Python for data manipulation and analysis

    • Translate SQL queries into pandas methods for data analysis

    • Utilize pandas' query() function for SQL-like querying

    • Recognize comparison and logical operators in both languages