# TiDB SQL for Developers Courses Outlines (201 series) (TiDB v6.x)

## Labs and Demo Scripts Public Location:
[https://github.com/pingcap/tidb-course-201-lab](https://github.com/pingcap/tidb-course-201-lab)

## About
+ Courses Count: 5
+ Total Duration: 3 days
+ TiDB Version: 6.x
+ Description: Designed for learners with developing experiences on MySQL database or beginners developers who will use TiDB as a back-end database. This course focuses on practical skills and provides guidelines and best practices about developing on TiDB.

## Course 1: Introduction to TiDB Distributed Architecture (Code#: 201.1, Duration: 3 hours)
### Description
+ This course introduces the core components and their concepts build up the TiDB distributed database cluster. 
### Audience
+ Any roles
### Prerequisites
+ None
### Level
+ Introductory
### Summary
+ Introduction to TiDB Architecture
+ TiDB Servers
+ TiKV Servers and PD Servers
+ The Advantages and Characteristics of TiDB Compared to Traditional RDBMS
+ Getting Started with TiDB

## Course 2: Learn SQL with TiDB (Code#: 201.2, Duration: 1 day)
### Description
+ This course 
### Audience
+ Any roles
### Prerequisites
+ Intermediate
### Level
+ Introductory
### Summary
+ Overall TiDB Architecture
+ TiDB Servers
+ TiKV Servers and PD Servers
+ The Advantages and Characteristics of TiDB Compared to Traditional RDBMS


## Pre-Requisites
+ **20A**: You already have the experience of developing applications with any programming platform (Java, Python, Node.js, etc...) on top of MySQL compatible databases.
+ **20B-Java**: Java programmer who can use SQL queries to fetch data from MySQL compatible databases.
+ **201**: None.

## Modules
+ **Module: 201.1 Introduction to TiDB Distributed Architecture (0.5 Day)**
  + Chapter 01: Introduction to TiDB Architecture
    + Overall TiDB architecture
    + TiDB Servers compute cluster
    + TiKV Servers storage cluster and Placement Driver servers scheduling cluster
    + The advantages and characteristics of TiDB compared to traditional database architectures
+ **Module: 201.2 Learn SQL with TiDB (1 Day)**
  + Chapter 02: Querying Table Data
    + Use DESC
    + Retrieve records using a SELECT statement
    + SELECT capabilities: DISTINCT, WHERE, ORDER BY, LIMIT
    + Query data from a MySQL client
    + Get query execution plan
    + Optimize pagination by KeySet seeker
  + Chapter 03: Data Types
    + Describe data types
    + Describe numeric, date, string, and NULL
    + Describe boolean expression
    + Choose the right data type
  + Chapter 04: Functions
    + Use String functions
    + Use Date and time functions
    + Use Numeric functions
    + Use aggregate functions
    + Run a simple analysis using RANK window function
    + Use control flow functions
    + Understand TiKV expression push down
  + Chapter 05: Joins
    + JOIN concepts
    + JOIN multiple tables
    + UNION and other set operations on multiple queries
  + Chapter 06: Subqueries
    + Subquery concept
    + Simple subqueries
    + Correlated subqueries
+ **Module: 201.3 TiDB Data Definition Language (DDL) and Transaction Control (0.5 Day)**
  + Chapter 07: TiDB Data Definition Language (DDL)
    + Create and drop database
    + Describe database charactersets
    + Online DDL
    + Create, modify, delete, flashback tables
    + Use table and index related DDL
    + AUTO_RANDOM and AUTO_INCREMENT
    + Describe and set column properties, table options
    + Create indexes, primary keys (clustered and non-clustered), unique keys, and constraints
    + Use cached tables
    + Use partitioned table
    + Use placement policy
    + Optimize Analytical Queries with TiFlash
  + Chapter 08: TiDB Data Manupilation Language (DML) and Transaction Control
    + Add new data to a table using INSERT and REPLACE
    + Use UPDATE to modify data
    + Avoid duplicate records
    + Delete rows using DELETE
    + Control transactions
    + Use pessimistic and optimistic locking
    + Describe SQL injections and prepared statements
+ **Module: 201.4-Java Using Connector/J (0.5 Day)**
  + Chapter 09-Java: Using Database Connector API
    + Describe Database Connector
    + Describe TiDB Connector
    + Access TiDB via Java
    + Describe the benefits of connection pooling
    + Handle NULL values in the result set
  + Chapter 10-Java: Using Prepared Statements
    + Describe the reasons for using prepared statements and their limitations
    + Use mysql command line client to prepare, execute, and deallocate prepared statements
    + Use prepared statements in your code via the Connector API
    + Use batch processing API
  + Chapter 11-Java: Handling Exceptions
    + Set SQL Mode to modify the behavior of error output
    + Use SHOW WARNINGS and SHOW ERRORS
    + Handle Exceptions in Java
+ **Module: 201.5 Best Practices for Developing on TiDB (0.5 Day)**
  + Chapter 12: Best Practices Summary
    + Describe best practices for using TiDB SQL
    + Describe best practices for developing applications using TiDB (using Java as an example)

## Recommended Learning Paths for 3 Roles:
+ **201**: **TiDB SQL for Beginners**
  + Modules: 201.1, 201.2, 201.3, 201.4-Java, 201.5
+ **20B-Java**: **TiDB SQL for Java Developers**
  + Modules: 201.1, 201.3, 201.4-Java, 201.5
+ **20A**: **TiDB SQL for Experienced MySQL Application Developers**
  + Modules: 201.1, 201.3, 201.5

## References
+ TiDB documentation: https://docs.pingcap.com/tidb
+ MySQL documentation: https://dev.mysql.com/doc