Learn MySQL and Solve 42 Practical Problems

Set up a simple sample database in MySQL Workbench by importing a script that creates and populates tables like customers, employees, offices, and orders, and run it to view results.

Learn how to use the distinct keyword to eliminate duplicate rows in a result set, demonstrate single-column and multi-column applications, and compare row counts before and after.

Learn to filter records in MySQL using where clause to select last name, first name, and job title from employees, such as sales reps, and understand quotes, syntax, keyword order.

Learn how to use and and or in SQL where clauses to filter records by multiple conditions, combining country and state such as USA and CA or France.

Learn to filter records using greater than, less than, and between operators in SQL, including not and inclusive between, with examples on buy price ranges.

Use the in keyword to filter data by multiple values, such as country in ('USA','France'); apply not in for negation and recognize its concise readability with possible subquery use.

Master the like keyword in MySQL to filter records by patterns, using % as a wildcard and escaping with a backslash when needed.

Master the MySQL limit keyword to control the number of displayed rows, retrieve the first few records, and offset the starting point for flexible data viewing.

Sort retrieved data with the order by clause, choosing ascending or descending order across one or more columns such as last name and first name.

Use column aliases to rename retrieved columns in a select query, making concatenated results like first name plus last name readable as name, and reuse the alias in subsequent clauses.

Learn how to use MySQL inner join to retrieve data from multiple tables, using join conditions and aliases, with practical examples from products, product lines, and orders.

Explore MySQL left join concepts by comparing left join with inner join, and learn to retrieve all customers and their orders, including those with no orders.

Learn how to perform a self-join by aliasing the same employees table to list each employee alongside their manager, using the reports_to relationship and a concatenated name.

Learn how the group by clause groups rows by status or expressions and applies aggregate functions to produce per-group totals in queries.

Discover how the having clause filters groups and aggregates after a group by, compare it with where, and apply it to order details and totals in practice.

Explore subqueries in MySQL and how inner queries nest inside outer queries. See examples like finding the maximum payment, computing the average, and using in and not in with customers.

Combine multiple select results into single result set using MySQL union operator, ensure column counts are equal and types compatible; it eliminates duplicates, while a form that retains duplicates exists.

Master creating tables with the create table syntax, including if not exist, column definitions with data types and sizes, and setting a primary key and engine.

Learn to insert records into tables using the insert into syntax for single and multiple rows, with column lists and auto primary keys; handle duplicates by updating the primary key.

Explore MySQL storage engines, from InnoDB's ACID transactions and foreign keys to MyISAM, memory, archive, and federated options, and learn how to choose the right engine.

Learn how to use the update statement in MySQL to modify rows with set and where clauses, including low priority and ignoring errors, with examples updating emails and names.

Learn to delete records in MySQL using delete from single or multiple tables, with where, order by, and limit clauses, and how to disable foreign key checks for related deletions.

Learn to use the union keyword to combine multiple select results into a single result set. Follow constraints on column count and type compatibility, and understand how duplicates are handled.

Learn how to use the drop statement to drop tables, with optional if exist and comma-separated names, in the test database.

Set up the sample Northwind database in MySQL from the attached script, then explore basic to advanced problems and join tables for meaningful results.

Tackle six basic MySQL problems to practice select statements, from clauses, and where conditions, listing shippers, category names, sales representatives, and orders by employee, plus suppliers not marketing manager.

Master practical MySQL queries in problems 7–12, including like with wildcards, filtering orders by ship country with or in, ordering by birth date, extracting date parts, and concatenating names.

Explore MySQL basics through problems 13–19, calculating total_price as unit_price times quantity, and using group by and aggregate function to report orders, customers, products, and shippers.

Learn MySQL and solve 42 practical problems by writing intermediate queries that count products per category, compute customers by country and city, and identify inventory that needs reordering.

Sort customers by region and id, placing null regions at the end with a case expression. Identify top three shipping countries by average freight within 2015 and last 12 months.

Solve an intermediate problem in MySQL using a left join between customers and orders, applying two conditions on customer_id and employee_id to identify relevant orders.

Identify high value customers in 2016 by joining customers, orders, and order details; compute total amount as quantity times unit price and apply group by and having 10000 and 15000.

Explore how to query monthly orders by employee, join orders with order details, and count line items to identify high volume orders, using group by and order by.

Identify potential duplicate orders by grouping by order ID and quantity to spot different product IDs with the same quantity, 60 or more; then analyze late orders per salesperson.

practice solving mysql queries across problems 39–42 by identifying late vs total orders, tracking missing employees, and using subqueries, left joins, group by, and case statements.