Mastering SQL (Using MySQL, Java, and Go)

Explore the first data types, integer, and create your first database and tables. Learn to insert data, query with the select statement, and complete an exercise to sharpen your skills.

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.

Master inserting data into tables with the insert into syntax (columns) values (values). Explore drop table and drop database commands with if exists, and review describing and selecting from tables.

Learn to query data with the select statement, display all data using star, or choose specific fields like country, continent, and population, and adjust column order.

Create a database for samples, build two tables named animal one and animal two with id and name, insert data, display and describe, then drop tables and the database.

Mastering sql section covers where clause, keys, default and increment, data types such as decimal and enumeration, and functions like concat and left, plus distinct, like, and regex pattern matching.

Learn how null values behave in SQL, test for null with is null or is not, and use aliases and equality operators for clear comparisons.

Explore how primary keys uniquely identify rows, including composite primary keys, and how auto increment generates unique IDs while using the where clause to filter data in select statements.

Master the default keyword to set default column values and apply SQL logical operators and, or, xor, not through practical exercises and truth table explanations.

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.

Learn how to use decimal, date, and enum data types, apply concat and left functions, and perform basic mathematical operators for practical SQL data handling.

Create and query an employees table using decimal, date, and enum types in MySQL, and apply concat and left functions, including arithmetic operations, for names and initials.

Explore distinct and all to remove duplicates across fields, and master comparison operators including equal, greater than, less than, greater than or equal, less than or equal, and not equal.

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.

Sort query results with the order by clause, using ascending by default and descending with desc, sorting by last name and first name, and applying where before order by limit.

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 sql table joins, including inner, left, right, cross, and full outer joins, plus self joins and using keyword. compare implicit vs explicit joins and learn unions.

Explore inner and outer joins by visualizing two tables with id and name; use a Venn diagram to map inner join intersections and left, right, and full outer join regions.

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.

Practice inner joins by building three tables and joining book to category to display book name, category id, and category name; then filter to category id > 2.

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.

Master right outer joins by displaying categories with their books from category and book tables, highlighting the one-to-many relationship. Use a right join with a where clause to refine results.

Explains how to perform explicit and implicit self joins on a single table to pair teachers with their students, using equal joins and the using keyword.

Explore cross joins and cartesian products, understand how union combines multiple selects and deduplicates rows, and practice full outer joins through two exercises using sample book, category, and animal tables.

Mastering sql: join multiple tables across book, category, and author using inner and left joins; practice links on category_id and author_id with and/or conditions.

Explore creating and dropping databases and tables, perform CRUD operations, and modify data using the update and delete commands, with examples and exercises.

Learn to create and drop databases and tables in sql, perform create, read, update, and delete operations, and use fully qualified names and unions across databases.

Learn to create tables using select as to copy data, excluding primary and foreign keys, and using like to copy the structure including column attributes and indexes.

Modify the structure of tables using the alter table command to add, drop, rename, modify, and change columns, while following production database safeguards by consulting a database administrator.

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.

Practice deleting rows with the delete statement and where clause, avoiding full-table removals; understand default save_updates=1 protects non-key columns, and explore case-insensitive deletes and multi-id deletes using the in-clause.

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.

Learn how to use aggregate scalar functions such as average, minimum, maximum, sum, and count to compute values across data like employee salaries, including count distinct scenarios.

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.

Explore aggregate functions by calculating employee counts and average salaries, filtering for employees born before 1980 with employee numbers between 11 and 14, and rounding the results.

Explore the group by clause and having clause to group data by multiple columns, apply aggregate functions like count, min, and max, and interpret grouped results through practical exercises.

Learn to run MySQL scripts by creating a world_one database and a country table, then execute the script via MySQL Workbench, run the selected portion, or use the command prompt.

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.

Explore the group by clause through two invoices tables, counting invoices per vendor, and joining on invoice number and vendor id to compare results.

Learn to use group by and having clauses in sql to group vendor IDs, count vendors, compute average invoice and payment amounts, and filter groups with counts greater than one.

Explore the differences between the having and where clauses in group by queries, with exercise 6 examples illustrating that having applies after grouping and can include aggregates.

Master the group by clause with having filters, applying averages to invoice amounts, and filtering groups by vendor count and average invoice values.

Explore implicit joins by converting inner joins to comma separated syntax and where clauses, then apply to complex queries with aggregates, group by, and having.

Master subqueries in SQL, applying them in select, from (as derived table with alias), and in where or having conditions to write efficient, readable queries.

Explore subqueries using all, any, and some operators to compare a value with a subquery result, and learn to filter invoices by amounts greater than all or any, using in.

Explore data types such as char, smallint, int, bigint, and real numbers like decimal, float, double, plus date time, and learn implicit and explicit data conversion with cast and convert.

Explains char and varchar data types, fixed vs variable length, padding behavior, and how UTF-8 and multibyte characters affect storage.

Explore binary large objects and text types in mysql, detailing blob variants like tiny and long, text variants, and contrast enum with set for single versus multiple values.

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.

Explore implicit versus explicit data conversion and learn how to use cast, convert, format, and char to transform dates, numbers, and strings.

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.

Master the four string functions in sql: lower, lcase, upper, and ucase, using select syntax to convert strings to lowercase or uppercase and understand their arguments.

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.

Compare length (bytes) and char_length (characters) and see how multi-byte characters affect results, then demonstrate lpad and rpad for left and right padding.

Mastering SQL presents left, right, reverse, repeat, and space string functions with practical examples in SQL queries, demonstrating how to extract characters, repeat patterns, reverse strings, and generate spaces.

Explore sql string functions part 5, which demonstrates replace, locate, and insert operations on strings, including replacing substrings and converting asterisks to hyphens, with practical examples.

Master string functions in SQL, focusing on strcmp and substring; learn how string comparison returns zero for equality and how substrings are extracted by start position and length.

Mastering SQL demonstrates two string function exercises: keep the first two and last two characters with dashes, remove special characters, and capitalize the first letter while lowercasing the rest.

Explore numeric functions in SQL, including modulo and modulo operator for remainders, sign, absolute value, ceiling, and floor functions with practical examples.

Explore numeric functions in MySQL: power, sqrt, truncate, and rand, then practice generating integers between 5 and 15 using floor with a 0–1 random output.

Master SQL date and time functions, including now, current date, current time, UTC date, extract, date format, and time format, with examples and exercises on parsing and formatting timestamps.

Explore date time functions in SQL, including now, current date, current time, and UTC date/time variants. Compare their results and understand Unix timestamp and the Unix epoch.

Review the second set of time date functions in SQL, using current date or now to derive year, quarter, month, month name, and week.

Master sql datetime functions, including day, day of week, day of year, last day of month, and time components like hour, minute, and second, using current date and time values.

Master the extract function to parse day time units—hour, minute, second, month, day, year—using now and date literals, with practical examples of extracting each unit.

Learn how to format date, time, and timestamp values in MySQL using date_format, time_format, and get_format, with ISO and US formats demonstrated.

Explore MySQL datetime formatting via two approaches—the format function and direct format specifiers—covering day, month, year representations, separators, weekday and month names, and 12-hour time with am/pm.

Learn how date_add and date_sub modify dates by day, week, month, or year, and use date_diff, time_diff, and timestampdiff to measure differences in days, hours, or seconds.

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.

Mastering sql teaches using the case function to perform conditional logic with case when then else, illustrated through a payment_id example and a case-based exercise extracting time with date_format.

Apply the if function in SQL to conditionally transform data, using a true expression when the condition is met and a false expression otherwise, reversing Spain.

Explore the ifnull and coalesce functions in MySQL, learning syntax with default values and multi-column checks, illustrated through practical examples using category ID and author ID.

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.

Explore the one-to-many relationship between a person and their phones. See how a person's primary key becomes a foreign key in the phone table, with examples like Josh and Nick.

Learn to model many-to-many relationships with a junction table linking a person to a location via two foreign keys and a composite primary key.

Practice building a one-to-one relationship between a person table and a passport table. Create the database and tables, define primary keys, and establish a foreign key to link records.

Create and relate the person and form tables by adding a form table, defining a foreign key on person_id, and establishing a one-to-many relationship.

Create a many-to-many relationship between the person and location tables using a junction table called person_location, with foreign keys and a composite primary key.

Explore attributes and constraints in mysql, enforcing data integrity with not null, unique, default, and auto_increment, and distinguish column-level from table-level constraints, including compound primary keys.

Explore foreign key constraints and referential integrity in a one-to-many author-book relationship, learning delete behaviors like cascade, set null, and no action to manage related records.

alter table exercises demonstrate adding, dropping, and changing columns and constraints, including primary and foreign keys, to enforce referential integrity in a one-to-many mother and child relationship.

Explore altering table constraints, adding and dropping primary and foreign keys with references, and enforcing referential integrity between child and mother tables with on delete cascade.

Learn to rename, truncate, and drop tables in MySQL, using a sample book table with an auto-incrementing primary key and a varchar(30) name, plus practical in-action examples.