SQL & Database Design Mastery

Explore databases and schema design with Eskil and MySQL. Learn advanced techniques such as aggregates, unions, nested queries, and transactions with views to build scalable business applications.

Explore relational databases and rdbms concepts, including primary keys, foreign keys, indexing, and one-to-many and many-to-many relationships, with practical schema design.

Learn to set up MySQL using the Maisky relational database management system, including MySQL Community Server, MySQL Workbench, and the Israel command line client, and understand root connection basics.

Master MySQL data types, from tinyint to int, decimals for precision, floats for approximation, strings and binary varieties, blob and text, plus primary keys and constraints.

Learn the SQL language basics by exploring data definition and data manipulation commands, including create, alter, drop, rename, truncate, select, update, delete, grant and revoke permissions, and commit and rollback.

Create the accelerate care schema, define its tables with primary and foreign keys, add indexes, and manipulate data via insert, update, and describe queries.

Complete the accelerator schema for the assignment by creating tables and constraints, including order, product, and employee columns, with cascading foreign keys and index definitions for efficient queries.

This lecture builds a normalized database schema for accelerated care, detailing customers, orders, order lines, product groups and ingredients, including a many-to-many ingredients-in-products junction with keys and indexes.

Learn how to load data into MySQL tables from text files using load data, handling local versus server files, field and line terminators, duplicates, and optional foreign key checks.

Learn to use the select statement to answer business questions, filtering with where and between, sorting with order by, limiting rows, and using expressions and built-in functions to format results.

Learn to join tables with on conditions and aliases, enabling left and inner joins, while using distinct to remove duplicates in complex queries.

Discover how SQL aggregates sum quotas by region, count orders per customer, and use left joins, group by, and having to refine results.

Master nested queries by building derived tables from the select statement, then compute aggregates like the average order total for 2018, filtering by fulfilled or delivered orders.

Create and use views to simplify complex queries, reuse subqueries, and maintain derived results as virtual tables, while understanding merge vs temp table strategies and union for multi-year top customers.

Explore advanced insert and update techniques in SQL, including inserting with values or select results, on duplicate key updates, and multi-table joins for partners and customers.

Explore transactions that enforce acid principles—atomistic consistency, isolation, and durability—through start, commit, and rollback, plus variables and locking, including shared and exclusive locks at various isolation levels.

Dive into databases and MySQL; explore where conditions and expressions, use variables, procedures, functions, and triggers, and master backup, restore, explain commands, and query plans for large data sets.

Extend the database schema to track customer tiers (green, silver, gold), monthly credits from 30-day spend, and transition history, while supporting monthly updates and comprehensive reports.

Craft a tiered database design with a new tier column, tiers, transitions, and credit payouts, using six-month sales to assign tiers and generate reports.

Execute a monthly transaction to update customer tiers, log transitions, and issue credit payouts, then generate reports on payouts, program costs, top products, and twelve-month trends.

Design and implement a database schema for business scenarios, and work with MySQL. Write Esquibel and Escorial queries to create, update, delete data, and prepare VBA and Excel integrations.