MySQL, SQL and Stored Procedures from Beginner to Advanced

Install the MySQL community server and Workbench, start the server, and test the connection to a local database. Remember your root password and use Google for troubleshooting.

Learn to use the MySQL command line to start the server, connect with the MySQL client, and run basic commands like show databases, with notes on PATH and MySQL Workbench.

Create a MySQL database and a users table in MySQL Workbench, then insert usernames and query them to verify results.

Explore creating tables with multiple columns in MySQL, using the int data type for IDs and names, dropping and recreating tables, inserting values, and comparing integer types for storage efficiency.

Discover how null values and not null constraints work in MySQL, including default values, inserting partial rows, and the difference between blank strings and no value.

Explore mysql storage engines, view available engines with show engines, and configure a default storage engine via session settings or the my.cnf config file.

Discover how sql_mode governs insert behavior in MySQL, including global and session settings, strict mode for not null columns, and no engine substitution, and how default values and errors arise.

Delete from a table clears all rows, not the table itself; learn to disable sql_safe_updates to allow deleting all rows, then verify by selecting from the table to confirm emptiness.

Create a users table in MySQL and define an integer id as the primary key. See how primary keys enforce uniqueness and prevent duplicate data in inserts.

Learn how auto increment primary keys work in MySQL by creating a users table with an auto increment primary key, inserting without an id, and observing 0 handling.

Narrow down MySQL results by using where clauses and column lists in select statements. Switch from select star to specific columns and safely delete rows with conditions through safe updates.

Export data from a database to a self-contained sql file using the sequel workbench, then import into a target schema to recreate databases.

Export a MySQL database using command line tools and redirect the output to a file, covering server, user, and database parameters across Unix and Windows environments.

Learn to import a database from a file using the command line, connect with mysql -u root, show databases, select a database like 10, and import via input redirection.

Create and populate a test users table in MySQL, dropping and recreating the table, inserting sample data, and running basic queries to build SQL skills.

Explore the key MySQL comparison operators, including equals, not equal, less than, greater than, and like. Apply them in where clauses and simple patterns to shape queries.

Explore how to use logical operators in MySQL to combine multiple conditions with and, or, and not in where clauses, using examples like age and name.

Explore exclusive or (xor) in MySQL and learn how it differs from or by showing when rows match one condition, both, or neither.

Practice six sql exercises in mysql workbench with a downloadable text version, and review solutions next. Learn to format comments and run selected queries with semicolons.

Explore building and testing SQL queries with select and where clauses, using between, like, not like, and logical operators to filter users by age ranges, names, and exclusions.

Learn to write update statements with update table set column = value and optional where clauses to target rows, including safe update mode considerations.

Explore how to sort query results using order by in MySQL, including ascending and descending orders, sorting by one or multiple columns, and handling ties with secondary columns.

Learn how to limit SQL query results using the limit keyword, with optional offset, order by, and where clauses to retrieve a small, precise subset from large tables.

Explore MySQL data types and when to use them for efficient storage and performance. Use rules of thumb, like int for primary keys, and consult the manual for optimization tips.

Learn how to choose the right text types in MySQL, including text, blob, and medium text, and use fixed vs variable length char to optimize storage and performance.

Explore MySQL numeric types, from choosing the smallest integer type (tinyint to bigint) for space to using float, double, and decimal (numeric) for precise storage with decimal(4,2).

Explore the bit data type in MySQL, learn how bit fields store binary values, how to insert and view them, and how binary representations convert to decimal.

Learn how to use boolean (bool) fields in MySQL, including true/false values, defaults, and the boolean alias for tinyint(1), with practical insert, select, and update examples.

Explore MySQL blob types and storing binary data, such as images, in a database, and learn why linking to disk files with names is often preferred over storing binaries.

Master handling time, date, and year types in MySQL using now and year functions to format, extract, and display the current moment.

Explore the difference between timestamp and datetime in MySQL, including real-time recording versus date-time storage, and learn practical usage with now as a default value in a products table.

Learn how MySQL enumerations constrain a column to a fixed set of values using enum types, defaults, and examples like temperature and flavor, with inserts and optional foreign key considerations.

Learn how brackets disambiguate boolean conditions in SQL queries, mastering how to combine criteria with and/or, and using practice to visualize true/false evaluations.

Create two MySQL tables, products and personnel, defining primary keys and fields for names, category, sell-by date, sold time, quantity, weight, status, plus extensive personnel and address data.

The lecture demonstrates building MySQL tables for a shop app, detailing primary keys, auto increment, data types (int, varchar, timestamp, boolean, numeric), inserts, and preparing for foreign keys and joins.

Apply the distinct keyword in MySQL to extract unique values, identify duplicates, and understand how to filter by combinations of columns for precise grouped results.

Learn how to count distinct values in sql using count and distinct inside aggregate functions, with examples counting by name, by name and age, and by other columns.

Explore common MySQL aggregate functions, including count, count distinct, average, min, max, and sum, and learn how group by and having unlock powerful data analysis.

Explore arithmetic in MySQL using select statements to perform multiplication, division, addition, and subtraction. Learn to apply aggregate functions, including calculating an average without the average function.

Explore a health survey database used for MySQL practice, including importing a self-contained file, creating the health schema, and enforcing data integrity with foreign keys in the survey table.

Explore how to use group by in SQL to aggregate data, compute averages and counts by gender and country, and combine group by with order by and other clauses.

Explore using aggregate functions with group by to compute per-country max weights, then apply having to restrict groups and order by to sort results.

Practice SQL by solving single-query exercises on calculating average weight and height by country, counting respondents, and analyzing gender, exercise categories, and health scores.

Explore practical MySQL queries to analyze survey data, including computing average weight and height by country, counting respondents, and using group by, having, and order by to filter results.

Master naming and aliasing columns in multi-table queries to keep results stable when column order changes, and prefix columns with the table name (for example survey.id) for future joins.

Discover how foreign keys link tables by creating an addresses table and a people table, using address_id as a foreign key to enforce referential integrity.

Explore entity relationship diagrams in MySQL Workbench, reverse engineer a small database to visualize tables and foreign keys, and understand crow's feet notation for one-to-many and many-to-one relationships.

Explore how joins combine data from the person and address tables, illustrate the cartesian product, and refine results by matching the person's address id to the address table's primary key.

Learn how inner joins in MySQL improve queries by replacing comma-based syntax, using on conditions and aliases to prevent cartesian products and span multiple tables.

Master inner, left, and right joins and how they return matched or unmatched rows across tables; note that full outer joins aren’t supported by this syntax.

Join multiple tables to replace survey IDs with actual answers from related tables using left or right outer joins, aliasing tables to avoid ambiguity, and rename columns for clarity.

Explore querying chains of tables by joining person, address, and region to retrieve individuals with their street and region, using foreign keys and one-to-many relationships.

Master one-to-many and one-to-one relationships in MySQL, learn why the foreign key lives in the child table, and how to design many-to-many with an extra table in the middle.

Explore many-to-many relationships using a customer and product example, and learn how a linking table with foreign keys connects the two tables to track which customer buys which product.

Explore how to perform a self-join on the seats table to identify the first free seat in each consecutive pair, using id and id+1 in SQL.

Explore how foreign keys enforce referential integrity, practice restrict on delete and update, and observe how the database prevents deletions that would break constraints.

explain cascade and restrict options for foreign keys across three tables, showing how on delete and on update cascade propagate deletions and updates through kingdom, organism, and individual.

Design a simple online shop database using multiple related tables with foreign keys, including products, categories, customers, and sales with the time of sale, plus join queries for listing sales.

Design a simple online shop database in MySQL with products, categories, customers, and a sales table. Employ foreign keys and inner joins to analyze sales and identify top products.

Learn how to combine queries with union and union all, compare duplicate handling, and explore their relation to left, right, and full outer joins.

Learn how to use the in keyword in MySQL to filter rows by a list of IDs or by a subquery, with practical patterns for web forms and dynamic queries.

Use inline views to treat query results as a table, creating a temporary table from a subquery to enable calculations like the average number of respondents by country.

Alter tables to add columns in MySQL, including email as varchar(50) and placing it first or after another column. Later sections cover adding indexes and foreign keys.

Add foreign keys to existing tables to enforce data integrity, by using alter table to create a foreign key referencing library(id), and validate against valid ids.

Discover how adding an index to a non-primary key column speeds up queries in MySQL, illustrated with a music table and an alter table add index example.

Learn how to create multi-column indexes in MySQL to speed up queries on the leftmost columns, with practical band and song examples and guidance on index design.