MySQL Database Development Mastery

Install MySQL server and MySQL Workbench by selecting the community edition, choosing the web or offline installer, and configuring a standalone server with legacy authentication.

connect to a database on the My School server and create your first database using the command line or MySQL Workbench, then verify with show databases and use.

Master how to drop a database in MySQL using the drop database command, with deliberate steps, no undo, and removal of the target schema.

Create a sql database table by scripting multiple commands in a single file, define an id primary key with auto increment and not null, plus var char and date fields.

Insert data into the student table using a script with insert into, listing columns and values. Use single quotes for strings, yyyy-mm-dd dates, and rely on auto-incremented IDs for records.

Insert records into MySQL Workbench using the visual grid and form views, compare direct inserts with scripts, and ensure data integrity with auto‑incrementing keys.

Learn to select data from the students table with SQL script, choose specific columns, and export the results to CSV for reports.

Learn to generate and refine select queries using dot notation, where clauses, limits, and like with wildcards, to filter data and concatenate first and last names into a full name.

Update data in the students table using update and set, apply a where clause to target specific records by primary key, and safely change enrollment dates or other fields.

Master how to delete records in MySQL: use delete from with where to target rows, apply and or conditions, in clauses, and wildcards, preventing accidental mass deletion.

Reverse engineer a database into an ER diagram model using the workbench, import tables, view entities and relationships, and generate the database from the model.

Forward engineer a data model into a database using a visual diagram to define a schema, tables with primary keys and auto incrementing, then generate and export deployable code.

Explore relational database design through normalization and foreign keys, and learn how to establish connections between users, photos, and likes using primary keys and linking tables for referential integrity.

Learn to create relationships with data modelling in MySQL by adding products to customers and orders, using one-to-one, one-to-many, and many-to-many patterns with foreign keys and link tables.

Develop and enforce relationships in a school database using MySQL Workbench, creating a class and enrollment tables with foreign keys to connect students, lecturers, and courses, and ensuring referential integrity.

Insert data into the class and enrollment tables using foreign keys to reference students and classes, ensuring referential integrity in a relational MySQL database.

Master inner joins to pull details from related tables like enrollments, classes, lectures, and courses, using primary and foreign keys and table aliases.

Master the differences between left join, right join, and cross join in MySQL. Learn how inner joins and self joins contrast and when to use each.

Explore how SQL group by contrasts with select distinct for eliminating duplicates and performing aggregations like count and average. Learn to mix inner and left joins to report rostered courses.

Learn to compute per-course average grades by adding a grades column to enrollments, inner joining with classes for course titles, and using avg with group by.

Explore how to use the sql count aggregate function to tally students by class and by course, using group by and inner joins across enrollments, classes, and courses.

Use min and max aggregate functions to fetch the highest and lowest grades per class from enrollments, grouped by class id.

Learn how to compute each student's total semester credits by joining enrollments, classes, and courses and using the sum aggregate function with group by.

Learn how to use the having clause to filter aggregated results after group by and sum, enabling conditions on aggregate values like minimum or total credits.