Mastering SQL (Using MySQL, Java, and Go)

Define and distinguish the int and varchar data types for MySQL, including int syntax, numeric limits, and varchar length, with quotes and backticks usage.

Create and manage your first database using sql commands: create database, create schema, drop database, show databases, and use to activate, plus add comments and view the active database.

Create and manage tables with create table syntax, define id, country name, continent, and population, and use show tables and describe table across databases with fully qualified names.

Explore techniques for creating primary keys, including simple and composite keys, by building tables, inserting rows, and examining key naming conventions such as snake_case.

Explore using the in operator to search a set of values and the between operator to filter ranges, including not in and subqueries with the employees table.

Explore pattern matching in sql using the like operator with percent and underscore, escape characters, and introductory regular expressions for more complex searches with examples on employees' names.

Explore complex pattern matching with the regexp operator in SQL, mastering regular expressions to find beginnings, ends, and any occurrences using common symbols, ranges, and alternation.

Master the sql joins through two animal tables, practicing inner and outer joins, using aliases and on conditions, and compare left and right outer joins with full outer join concepts.

Explore implicit inner joins and explicit inner joins, learn they produce the same results, and follow the recommendation to use inner join over the implicit syntax for efficiency.

Apply left outer joins to show all books with their category IDs and names by joining the book table with the category table, and compare results with inner joins.

Learn how to use the update statement to modify existing data, set new values, and filter rows with where clauses and primary keys; exercises focus on updating employee records.

Learn aggregate functions such as average, minimum, maximum, sum, and count; explore group by and having; learn what a SQL script is; practice all, any, and some operators in queries.

Explore summary queries and aggregate functions in sql using group by to compute average salaries by birthday and identify same-salary employees via self-joins.

Learn to use the group by clause to count countries per continent, filter with having, and sort by country counts in this two-part exercise.

Master integer and boolean types with signed and unsigned ranges, then compare fixpoint decimal and numeric to float and double, and review date, time, datetime, and timestamp.

Master MySQL string and math functions, including lower and upper case, trim, length, left and right pad, reverse, replace, locate, substring, sine, absolute, ceiling, floor, square root, and random.

Apply string functions in SQL by exploring concat, concat_ws, ltrim, rtrim, and trim; see how leading and trailing spaces are removed and how nested calls evaluate inside-out.

This session covers creating a date table, inserting date and time values, and fixing two exercises by applying formats like %y-%m-%d and %T to search timestamp data.

Master primary and foreign keys, entity relationship diagrams, and constraints like not null, unique, default, primary key, and auto increment, plus altering constraints and renaming, truncating, and dropping tables.

Explore how primary keys and foreign keys connect two tables to form one-to-one relationships, illustrated with person and passport data. Clarify primary and foreign key roles on the tables.