SQL Tutorial - Full Database Course for Beginners

Introduction

• SQL: Language to interact with relational database management systems
• RDMS: Software to create/manage databases

Course Outline

1. Basics of Databases: Types, definitions, usage
2. SQL Introduction: Meaning, usage in creating databases
3. Installation: MySQL (popular beginner-friendly RDMS)
4. Writing SQL: Queries for creating databases/tables, data manipulation
5. Advanced SQL Techniques: Retrieving specific information
6. Database Design/Schemas: Creating/describing database schema
7. Course Resources: Code available on GiraffeAcademy.com

What is a Database?

• Definition: Any collection of related information (phonebook, to-do list, user base)
• Storage Methods: Paper, mind, computer
• Use Case Comparison: Amazon (complex) vs. Shopping List (simple)
• Importance of Computers: Security, backup, large storage
• Database Management Systems: Specialized software to create/maintain databases
• CRUD Operations: Create, Read, Update, Delete data
• Relational vs. Non-relational Databases
  • Relational: Organizes data in tables (columns, rows, unique keys)
  • Non-relational: Stores data in flexible structures (documents, graphs)

MySQL & SQL Basics

• Relational Database Management Systems (RDMS): Examples like MySQL, PostgreSQL
• SQL: Structured Query Language for interacting with RDMS
  • CRUD Operations: Core operations (Create, Retrieve, Update, Delete)
  • Queries: Requests to RDMS for specific information
  • Data Types: (INT, DECIMAL, VARCHAR, BLOB, DATE, TIMESTAMP)
• MySQL Installation: Setting up MySQL server on Windows/Mac
• Creating Tables
  • Key Concepts: Primary key, rows/columns, data types (e.g., VARCHAR, INT)
  • Modifying Tables: Altering schema (adding, removing columns)

Inserting Data & Advanced Operations

• Inserting Data: Syntax, handling null values
• Updating/Deleting Rows: Syntax and use cases
• Basic Queries: SELECT statements, ordering, filtering
• Functions: COUNT, SUM, AVG for aggregating data

Patterns & Advanced Queries

• Wildcards & LIKE Keyword: Searching with patterns
• UNION Operator: Combining results from multiple SELECT statements
• JOINS: Combining rows from multiple tables based on related columns
  • Inner, Left, Right Joins
• Nested Queries: Writing complex queries using multiple SELECT statements

Managing Data with Keys

• ON DELETE Options: Handling deletions in tables with foreign keys (SET NULL, CASCADE)
• Triggers: Automating actions on specific database operations (INSERT, UPDATE, DELETE)

ER Diagrams for Database Design

• Introduction to ER Diagrams: Representing data models with entities, relationships
• Designing ER Diagrams: Creating diagrams based on data requirements
• Converting ER Diagrams to Schemas: Mapping diagrams into SQL database schemas

Practical Exercises: Example scenarios and coding exercises provided throughout.