All Levels SQL Bootcamp - Learn MySQL - MySQL Database

Practice writing simple select statements to fetch first name, last name, and city; alias columns using as; and select all columns, with tips on semicolons and executing queries.

Learn how to use select distinct to find unique localities and unique locality–species combinations in the Antarctic populations table, revealing duplicates and ensuring each row is unique.

Learn to use the limit clause with an order by to return a specific number of rows, such as the top paid employees or the most recent orders.

Explore how the where clause filters supplier data using three-valued logic and null values, explain is null versus equals, and apply or and not conditions to state data.

Learn the where clause and its comparison operators, including equals, not equals, in, between, and less than, less than or equal to, greater than, and greater than or equal to.

Understand correlations define how to compare and sort characters, including case sensitive and case insensitive rules. Use like and % wildcards for pattern matching and inspect correlations via information schema.

Learn pattern matching with the like operator in SQL, using underscore for a single character and percent for characters, and use an escape character with escape keyword to search literals.

Demonstrate pattern matching with the like operator and percent wildcards to filter countries starting with in. Identify Gmail addresses and product names containing mouse, with case-insensitive checks from information schema.

Explore the main my school data types: string, numeric, and date time, and learn to choose varchar and char, signed and unsigned integers, decimal precision and scale, and timestamp.

Explore scalar and aggregate functions in MySQL, including min, max, sum, avg, and count, which return a single value per group of data and show aliasing.

Learn to compute totals and counts using group by and aggregate functions, summing area by property zone and counting rows by occupancy, including multi-column groupings.

Tackle five group by challenges: count employees by department, average salaries by department with order, on-hand products by warehouse, and most recent population counts in the populations table.

Master group by techniques to count employees per department, compute average salaries, sum quantities by warehouse, and find the most recent population counts by locality and species in Antarctic data.

Apply the and operator in the where clause to require both conditions to be true; here room style is single and window view is ocean, returning rooms 1 and 3.

Use the OR operator to filter hotel rooms by either room style being single or room view being ocean, returning all but room five.

Explore integrity constraints in MySQL databases, focusing on primary key and foreign key constraints to enforce data integrity and referential integrity, including composite keys and best practices for unique identifiers.

Learn join challenges with inner, left outer, and right outer joins to link employees to departments, then group by department name to count employees, including those without a department.

Explore how predicate placement affects query results in joins, comparing on clause versus where clause across inner and left outer joins, with practical examples of suppliers and products.

Explore self-referencing joins that link a table to itself to reveal manager details using aliases and on clauses. See how a left outer join includes employees without a manager.

Demonstrates cross joins by pairing every row from stores with every row from products, producing a Cartesian product and showing the comma form of the from clause, rarely needed.

Master advanced join techniques, including self joins, outer joins, and multi-table queries, across the hcm and oas schemas to extract employee, product, and warehouse data with filtering and aggregation.

Combine results from multiple select statements using union or union all to produce a single result set, ensuring matching column counts and similar data types, with duplicates removed or kept.

Practice set operators in MySQL across multiple tables, using union and union all to fetch all rows, unique species, and ordered state and species pairs.

Explore set operator solutions using union all and union across California, Arizona, and Florida sightings tables, aligning columns and handling different column names and data types.

Explore self-contained subqueries in MySQL by retrieving product details with the highest price and filtering using scalar and multiple-row subqueries, including where clauses and in operators.

Explore the exists operator and correlated subqueries to find products with ids present in the order details table, and compare its performance with the in operator for different result sizes.

Learn how to avoid the not in trap in MySQL by using not exists and outer join approaches to find departments with no employees, and handle nulls properly in queries.

Explore seven sql subquery challenges, including min in a self-contained subquery, correlated subqueries, rank window functions in a common table expression, and not in patterns and not exists patterns.

Explore the case expression in sql, including how to use when, then, else, and end in both simple and search forms to classify customers by country and membership.

Master case expressions in MySQL—from discontinued status logic and price grading to shipping status and shipping days—using simple and search forms, timestampdiff, and CTEs.

Explore SQL function challenges: concatenate names with optional middle names, extract genus and species from scientific names, compute ages with timestamp, and compute average shipping days by company.

Master the insert statement to add rows to a table, including single and multiple inserts and inserts from a select, with explicit column lists for safety.

Learn how to alter table statements to add, modify, rename, or drop columns, specify data types and constraints, including not null, and control column order with first or after.

Explore alter table challenges: add termination date to HCM.employees, modify East customers' first and last name types, and rename Phone to Main with not null and 26 data type.

Explore the four properties of transactions—atomic, consistency, isolation, and durability—along with explicit transactions, commits, rollbacks, and practical MySQL examples with orders and items.

Learn how stored procedures save SQL code in the database, execute as a unit, and optionally return results or update data using input, output, and in out parameters.

Apply check constraints to enforce conditions on a column, ensuring data integrity in SQL by using create table or alter table syntax, with examples for quantity and color.

Learn data normalization, the cornerstone of relational database design, to minimize redundancy and prevent update, insertion, and deletion anomalies using primary keys, foreign keys, and association tables.

Identify candidate keys and super keys, explain irreducibility and functional dependency, and distinguish prime from non-prime attributes with practical examples.

Master first normal form by enforcing atomic values and unique rows, then normalize by dividing multi-valued emails into a parent and child table with primary and foreign keys.

Normalize multi valued and multi type fields by creating atomic values, a stations table, and a power outputs table linked by station id as a foreign key, favoring deeper design.

Learn how second normal form eliminates redundancy by splitting a supply parts table into a supplier main cities parent table and a supplier parts child table with a composite key.

Learn how third normal form extends second normal form by removing transitive dependencies, such as main city determining distance rank, and apply surrogate keys and foreign keys in supplies data.