Mastering MYSQL: Database Creation, Management & SQL Queries

Master the MySQL select statement to retrieve data from tables such as the employees table, apply where filters, and perform joins and salary calculations with examples.

Explore how to use the where clause in MySQL to filter data during table creation and queries, with practical examples of select, insert, and conditional filtering on customer data.

Explore and practice MySQL logical operators: the and, or, and not operators, using the customer table to build where clauses, not null checks, and between and not between conditions.

Master the MySQL order by clause to sort results by one or more columns, in ascending, descending, or length function-based order, with practical customer table examples.

Learn how to use the MySQL insert into statement to add single records into a users table by specifying columns and values. Explore inserting multiple records and handling null values.

Explore how to handle null values in MySQL by creating tables with nullable and non-null columns, inserting records with nulls, and querying, updating, and deleting using is null.

Explore how to use the MySQL update statement to modify existing records, including salary updates, using where clauses, ignore clauses, and controlling results with order by and limit.

Master the MySQL delete statement to remove single or multiple records with where conditions, delete specific employees, delete by department, and delete all records from a table.

Master the MySQL limit clause to constrain row counts, apply offsets, and combine with where and order by to fetch specific subsets of the customer table.

Explore MySQL's min and max functions to identify the smallest and largest values in a data set, including grouping by location with temperature examples.

Explore MySQL aggregate functions count, avg and sum to analyze data, create an orders table, compute total orders, average price, and total quantity with conditional counts.

Master the MySQL IN operator to filter data by multiple values with lists and subqueries, including department filters and not in use cases.

Master the MySQL between operator to filter data by numeric and date ranges, using select queries on product prices and order dates with sample tables.

Learn how to use MySQL aliases to rename columns and tables for clearer results, including aliasing calculated columns like total price with tax and using subqueries for expensive products.

Learn how to join related tables in MySQL to fetch data across customers and orders, using inner and outer joins with a.id matching b.customer_id.

Master the MySQL union operator to merge results from multiple select queries into a single result set, with duplicates removed by default, and union all to include duplicates.

Master the MySQL exists operator to test row existence via a subquery, including correlated cases, with examples using customers and related tables and update scenarios.

Learn how MySQL any and all operators compare a value to a subquery result, with examples using laptop price comparisons and other product prices.

Explore how to work with null values in MySQL using is null, is not null, and ifnull to replace, count, and update nulls across employees and customers tables.

Explore how to document SQL queries with single-line and multi-line comments in MySQL, using double dash, slash-star, and examples on the employees, customers, and orders tables.

Create a new MySQL database using the create database statement, defining the database name and optional character set and the collections, then verify with show database.

Learn how to use the MySQL drop database statement to permanently delete a database and its data, explore drop variations like if exists, and review creation and deletion workflows.

Create tables in MySQL with the create table statement to define a table structure, including primary keys, not null, and unique keys.

Drop table statements in MySQL remove an existing table from a database, permanently deleting its structure and data; learn dropping multiple tables and using if exists to prevent errors.

Explore how to use the MySQL alter table statement to modify an existing table by adding and dropping columns and changing data types, with practical examples.

Explore MySQL constraints to enforce data integrity with column and table level constraints, using a customer table example to prevent nulls on id and name.

Learn how the MySQL not null constraint enforces values in a table's columns. See how to create a users table and alter columns to prevent nulls.

Learn how to enforce data uniqueness in MySQL using the unique constraint, apply it to columns like email, and modify tables to add unique keys to prevent duplicates.

Create a department and an employees table, with department_id as the primary key and dept_id as a foreign key referencing department_id. Demonstrate referential integrity with inserts.

Learn how MySQL check constraints work, including age checks (>=18), table creation with constraints, and the fact that enforcement may not be active in some storage engines.

Learn how to create single-column and composite indexes in MySQL (and DB2) to boost query performance on an employees table, including showing, dropping, and testing index effects.

Master the MySQL auto increment field to automatically generate unique primary key values, create tables with auto_increment, and insert and query rows to see sequential IDs.

Master working with dates in MySQL. Define date columns, insert date values into the events table, and perform date range queries, including adding seven days to dates.

Explore MySQL views, virtual tables defined by queries that encapsulate complex logic and are queried like regular tables, with steps to create, select, update, and drop.