The Complete Roadmap to Data Analyst

Introduction to Databases and SQL

• Why learn SQL and MySQL?
• Overview of databases

SQL Fundamentals

• SQL as a declarative language
• Key components:
  • Data Definition Language (DDL)
  • Data Manipulation Language (DML)
  • Data Control Language (DCL)
  • Transaction Control Language (TCL)
• Common SQL keywords

Database Basics

• Relational database concepts
• Databases vs spreadsheets
• Key terminologies:
  • Primary, Foreign, and Unique keys
  • Null values and relationships

Getting Started with SQL

• Setting up a database
• Understanding SQL files and data types:
  • Strings, Integers, Floating-point, and other types
• Creating and using tables

MySQL Constraints

• Primary Key
• Foreign Key
• Unique
• Default
• Not Null

SQL Queries

Select Statement

• Basic queries: SELECT, FROM, WHERE
• Logical operators: AND, OR, IN, NOT IN
• Wildcards and pattern matching: LIKE, NOT LIKE
• Filters: BETWEEN, IS NULL, IS NOT NULL
• Comparison operators
• Distinct rows and aliases
• Aggregate functions introduction
• Sorting (ORDER BY), grouping (GROUP BY), and filtering (HAVING)
• Limits and differences: WHERE vs HAVING

Insert Statement

• Adding data to tables

Update Statement

• Updating data with COMMIT and ROLLBACK

Delete Statement

• Deleting data: DELETE, DROP, TRUNCATE, and differences

Aggregate Functions

• COUNT, SUM, MIN, MAX, AVG
• Handling nulls: ISNULL and COALESCE

SQL Joins

• Types of joins:
  • Inner, Left, Right, and Cross Joins
  • Self Joins
  • Joining multiple tables
• Aggregate functions with joins
• UNION vs UNION ALL

SQL Subqueries

• Subqueries in WHERE, SELECT, and FROM

Advanced SQL

• • Views and Indexes
  • Conditional logic: CASE statement
  • Triggers in MySQL
  • Ranking functions: ROW_NUMBER, RANK