MySQL and Database Management: From beginner to Expert

Learn how database management enables real-time data updates, secure storage, and shareable, transportable analyses from data mining and business intelligence to forecast the future and guide decisions.

Explore core database theory by contrasting internal and external memory, illustrating data organization, table design, and data management to improve decision quality with data, information, and knowledge.

Model reality in databases by identifying entities and attributes, using unique identifiers, and preparing for many-to-many relationships, illustrated with stocks and Marine Traffic data as realistic tables.

Design a MySQL table to record Olympic cities, with city name, country, continent, season, year, opening day, and ending day, using a composite primary key on city name and year.

Learn how to insert rows in a MySQL table using insert into with and without a column list, covering data types, quotes for strings, and proper datetime formatting.

Explore how to query a table by projecting specific columns or selecting all with a star, and restrict rows using a where clause, illustrated with share-table examples.

Learn to perform calculations in SQL by deriving totals from existing columns without adding new ones, including computing payments (dividends times quantity) and ordering results by the highest payments.

Explore MySQL built-in functions such as avg, max, min, sum, and count, and learn to apply them to results, including correlated queries and nested function calls.

Learn how to use subqueries in MySQL to compare values against aggregates, such as filtering firms by price-to-earnings ratio, and to find the firm with the maximum holding value.

Explore regular expressions in MySQL, using anchors like ^ and $ and the alternation | to search string columns for patterns such as names, shows, or phrases, highlighting case sensitivity.

Explore how the distinct function returns only unique values from a column, and why it shows only that column. Use built-in functions like max, average, and count with distinct.

Delete from share where code equals fc, and update share set price=31.50, to modify rows, including applying calculations, while recognizing these changes permanently modify the original table.

Practice-focused review of sql basics through quiz questions on data models, queries, and star models. Emphasizes data types, primary keys, and where clauses for value calculations.

Explore why one-to-many relationships matter in relational databases, using stocks and nations, and learn how primary keys and foreign keys link tables to avoid duplication.

Create two related tables in MySQL using a one-to-many relationship. Define primary keys and a non-identifying foreign key, enforce referential integrity, and populate with sample data.

Group by groups of similar characteristics to compute totals, such as total stock value or dividend payments by nation, and use having to filter groups by count.

Master advanced regexp usage in MySQL by filtering strings with negated character sets, repetition, alternation, and anchored positions, illustrated through nation names and starting with united queries.

Master correlated subqueries in MySQL by linking inner and outer queries, comparing with joins, and computing country-specific averages and maximum stock quantities.

Explore views as virtual tables that store the query, not results, so underlying data changes reflect instantly, and learn to create and query views with joins and aliases.

Practice building data models and performing complex MySQL queries using primary keys, joins, and correlated subqueries, with currency conversions via exchange rates and group by operations.

Explore how to model many-to-many relationships using an associative table to link items and receipts, detailing lines, sales, and identifying relationships.

Explore three table joins for many-to-many relationships by enforcing two matching conditions on the join table in both joins, with sales items and Olympics examples to select specific fields.

Explore exists and not exists queries to identify clothing items type c sold in line_item and those not sold, using item and line_item relationships.

Explore how to implement divide in SQL, using for all and not exists, with three-table joins and count distinct to identify items that appear in all sales.

Learn how to use union to combine two queries into one with unique results, enforce matching columns, and filter by date and value in MySQL.

Design and query many-to-many relationships using data models and associative tables, with examples from patients and physicians, marathons and runners, and injuries across players; master three-table joins.

Design and implement many-to-many and supporting one-to-many relationships across domains—patients and physicians with visits, marathon runners and races with participation, teams with players and injuries—plus practice complex SQL queries.

Explore mapping one-to-one and recursive relationships in a data model, decide foreign key placements, and design Olympic-themed tables for countries, athletes, teams, and events.

Learn to query one-to-one and recursive relationships in MySQL using self-joins and table aliases. Compare salaries between workers and bosses and identify employees in the same department as bosses.

Explore modeling one-to-one and one-to-many recursive relationships with self-referencing foreign keys, illustrated by monarchs, employees, and the Olympics city data model.

Explore recursive queries with self-joins to retrieve predecessor and successor data in a monarchs table, handling composite keys and on-clause joins for accurate results.

Model recursive many-to-many relationships for products using assembly, two one-to-many links, a composite key, and foreign keys; apply to a round-robin tournament with groups and a contest table recording scores.

Learn to query many-to-many relationships with joins across the animal photography kit and its components. Work with subqueries and three-way joins, modeling friendships and course prerequisites in a matrix organization.

Explore data modeling basics, identify entities, attributes, relationships, and identifiers, and craft well-formed, high fidelity models that reflect real-world context and support business decisions.

Explore how to model geographic data with accuracy and minimal impurities, focusing on nations, cities, administrative units, primary keys, and one-to-many and many-to-many capital relationships.

Explore how to structure people data models for family relationships, modeling marriages as many-to-many between persons, with start and end dates and a marriage status, plus child relationships.

Explore data modeling for libraries by treating a book as the type and a copy as the borrowable instance, using identifiers like ISBN, library numbers, and barcodes to avoid redundancy.

Explore entity types in data modeling, including independent, dependent (weak), associative, aggregate, and subordinate entities, and learn how identifiers and relationships like one-to-many and many-to-many shape a database schema.

Explore spatial data management and temporal data management within MySQL for location-based services. Learn how themes, geographic objects, maps, geometry, and topology model and query geospatial data.

Explore spatial querying in MySQL by calculating 'as the crow flies' distances between city origins and destinations in Ireland, and identify the closest and farthest cities using coordinates and joins.

Explore temporal databases in MySQL and database management: learn how transaction time and valid time attach time stamps to data, store historical states, and query time-varying records.

Explore spatial and temporal databases through review questions on coordinates and distances like Lisbon to Madrid, areas, and transaction time, including shopping basket details for a supermarket chain.

explain how location-based services drive spatial data use, perform spatial queries and distance calculations (Lisbon to Madrid), and design temporal aspects in a MySQL database with customers, baskets, and items.

Prepare for a MySQL final exam covering data models for buildings, tenants, leases, and computer parts, mapping patient visits, and executing complex queries plus a credit limit update procedure.

Explore practical MySQL and database management concepts through the final exam, covering building-tenant lease relations, many-to-many mappings, joins, aggregations, subqueries, and a stored procedure.

Explore the data model in part I of the course, establishing foundations for database design in MySQL. Identify core concepts of data modeling and database management for effective data organization.

Design a relational data model for a sports organization, creating tables for coaches, teams, stadiums, general managers, and owners, with one-to-many and one-to-one relationships, including composite keys.

Map data models to a database by defining user and account entities, primary keys, one-to-one and one-to-many relationships, including a favorite team; validate against an Excel sheet.

Demonstrates inserting data into MySQL from Excel by generating insert statements, handling one-to-one, one-to-many, and many-to-many relationships with proper keys, and troubleshooting data issues.

Learn practical SQL queries and data modeling for sports analytics, mastering inner joins, subqueries, aggregations, filtering, and ranking to turn data into actionable insights.

Explore building practical MySQL queries using where clauses, counts, group by, having, and joins to analyze NBA data, including ages, team sizes, and stadium capacities.