Mastering SQL (Using MySQL, Java, and Go)

Course Curriculum

Chapter 1: Introduction & Installation

Lecture 1: Welcome to The Mastering SQL (Using MySQL, Java, and Go)

Lecture 2: Sample Course Lectures (Only For Prospective Students)

Lecture 3: A Brief History of SQL and MySQL

Lecture 4: Database Concepts – Part 1 (Data and Tables)

Lecture 5: Database Concepts – Part 2 (Columns, Rows, Tables, and Databases)

Lecture 6: Database Concepts – Part 3 (DBMS, RDBMS, CRUD, and an Example) – Exercise

Lecture 7: MySQL Installation on Windows

Chapter 2: Your First Database

Lecture 1: Section Introduction

Lecture 2: First Data Types (INT and VARCHAR)

Lecture 3: Creating & Managing Your First Database

Lecture 4: Creating & Managing Your First Tables

Lecture 5: Inserting Data into Tables

Lecture 6: Querying Data using the SELECT Statement

Lecture 7: Exercise – Working with Databases and Tables

Chapter 3: Retrieving Data – Part 1 (Basics)

Lecture 1: Section Introduction

Lecture 2: NULL Values, Testing for NULL Values, Columns Alias, and Equality Operators

Lecture 3: Primary Key, WHERE Clause, and AUTO_INCREMENT

Lecture 4: The DEFAULT Keyword, Logical Operators, and Exercises

Lecture 5: Creating Primary Keys (2nd approach), Simple & Composite Primary Keys – Exercise

Lecture 6: Data Types (DECIMAL, DATE, and ENUM) – Mathematical Operators

Lecture 7: Exercise [DECIMAL, DATE, and ENUM, Mathematical Operators, CONCAT() and LEFT() ]

Lecture 8: Managing Duplicate Rows – DISTINCT and ALL Keywords – Comparison Operators

Lecture 9: Writing Subqueries Using the IN Keyword – The BETWEEN Keyword

Lecture 10: The ORDER BY Clause, and the LIMIT Keyword – Exercise

Lecture 11: Pattern Matching using the LIKE Operator

Lecture 12: Complex Pattern Matching using the REGEXP Operator

Chapter 4: Retrieving Data – Part 2 (Joins)

Lecture 1: Section Introduction

Lecture 2: Table Joins, Concepts (Inner and Outer Joins)

Lecture 3: Table Joins, Examples

Lecture 4: Inner Joins, Exercise 1,2

Lecture 5: Implicit Inner Joins

Lecture 6: Left Outer Joins, Exercise 4,5

Lecture 7: Right Outer Joins, Exercise 6,7

Lecture 8: Implicit and Explicit Self Joins – Equijoins – The USING Keyword – Exercise

Lecture 9: Cross Joins, UNION, and Full Outer Joins – Exercise 1,2

Lecture 10: Joining Multiple Tables – Exercise 1-3

Chapter 5: Tables & Databases – Part 1 (CRUD Operations)

Lecture 1: Section Introduction

Lecture 2: Creating and Dropping Databases and Tables – CRUD Operations – Exercise 1

Lecture 3: Creating Tables using the 'SELECT AS' and 'LIKE' keywords – Exercise 1,2

Lecture 4: Modifying Structure of Tables using the 'Alter Table' Command – Exercise

Lecture 5: Updating Data using the UPDATE statement – Exercise 1,2

Lecture 6: Removing Data using the DELETE statement – Exercise

Chapter 6: Retrieving Data – Part 3 (More Advanced Queries)

Lecture 1: Section Introduction

Lecture 2: Summary Queries / Aggregate Functions, Part 1 – AVG, MIN, MAX, SUM, COUNT

Lecture 3: Summary Queries / Aggregate Functions, Part 2 – Exercise 1,2

Lecture 4: Summary Queries / Aggregate Functions, Part 3 – Exercise 3,4

Lecture 5: The Group By Clause, Part 1 – Exercise 1-3

Lecture 6: Running MySQL Scripts

Lecture 7: The Group By Clause, Part 2 – Exercise 1,2

Lecture 8: The Group By Clause, Part 3-1 – Exercise 1,2

Lecture 9: The Group By Clause, Part 3-2 – The HAVING Clause – Exercise 3-5

Lecture 10: The Group By Clause, Part 3-3 – Exercise 6

Lecture 11: The Group By Clause, Part 3-4 – Exercise 7,8

Lecture 12: Replacing Inner Joins with Implicit Joins – Exercise 1-5

Lecture 13: Using Subqueries in Different Parts of Queries – Exercise 1-4

Lecture 14: Subqueries – ALL, ANY, and SOME Operators

Chapter 7: Data Types

Lecture 1: Section Introduction

Lecture 2: Data Types – CHAR, and VARCHAR

Lecture 3: Data Types – Texts, Binary Large Objects, ENUM, and SET

Lecture 4: Data Types-INT, BIGINT, …, BOOLEAN, DECIMAL, FLOAT, DOUBLE, DATE, TIME, DATETIME

Lecture 5: Implicit and Explicit Data Conversion – CAST(), CONVERT(), FORMAT(), CHAR()

Chapter 8: String & Numeric Functions

Lecture 1: Section Introduction

Lecture 2: String Functions – Part 1 (Lower, Upper, Lcase, Ucase)

Lecture 3: String Functions – Part 2 (Concat, Concat_WS, LTrim, RTrim, Trim)

Lecture 4: String Functions – Part 3 (Length, Char_Length, Lpad, Rpad)

Lecture 5: String Functions – Part 4 (Left, Right, Reverse, Repeat, Space)

Lecture 6: String Functions – Part 5 (Replace, Locate, Insert)

Lecture 7: String Functions – Part 6 (Strcmp, Substring)

Lecture 8: String Functions – Exercise 1,2

Lecture 9: Numeric Functions – Part 1 (Mod, Sign, Abs, Ceiling, Floor)

Lecture 10: Numeric Functions – Part 2 (Power, Sqrt, Truncate, Rand) – Exercise

Chapter 9: Date-Time & Miscellaneous Functions

Lecture 1: Section Introduction

Lecture 2: Datetime Functions – Part 1 (Now, Sysdate, Curdate, Curtime, UTC_Date, …)

Lecture 3: Datetime Functions – Part 2 (Year, Quarter, Month, MonthName, Week)

Lecture 4: Datetime Functions – Part 3 (Day, Hour, Minute, Second, DayOfWeek, …)

Lecture 5: Datetime Functions – Part 4 (Extract)

Lecture 6: Datetime Functions – Part 5 (Date_Format, Time_Format, Get_Format)

Lecture 7: Datetime Functions – Part 6 (Format Specifiers)

Lecture 8: Datetime Functions – Part 7 (Date_add, Date_Sub, DateDiff, TimeDiff, …)

Lecture 9: Datetime Functions – Part 8 – Searching for date/time values – Exercise 1,2

Lecture 10: The Case() Function – Exercise

Lecture 11: The IF() Function

Lecture 12: The IfNull() and Coalesce() Functions

Chapter 10: Tables & Databases – Part 2 (Relationships & Constraints)

Lecture 1: Section Introduction

Lecture 2: One-to-One Relationships / Primary and Foreign Keys

Lecture 3: One-to-Many Relationships

Lecture 4: Many-to-Many Relationships

Lecture 5: One-to-One Relationships – Practice

Lecture 6: One-to-Many Relationships – Practice

Lecture 7: Many-to-Many Relationships – Practice (Including an ERD)