SQL BootCamp - Learn MySQL for Data Analysis

Download the latest MySQL community server and MySQL Workbench for macOS, then install, configure a password, connect to the server, and open Cicely's database in the Workbench.

Learn how to download and install MySQL on Windows using a single installer, configure MySQL Community Server and MySQL Workbench, and choose between developer default, custom, standalone, or cluster setups.

Learn to download and set up three sample databases—employees, box office, and organization—for MySQL practice by downloading zip files, loading schemas, and running create table statements.

Create the sample database structure by building tables in the employees database, then load data from dump files into each table and verify content before proceeding to the next tutorial.

Explore SQL statements, logical operators, arithmetic and comparison operators, and practice querying the MySQL database from prior tutorials, using wildcards and learning to limit results.

Learn to use the select statement to retrieve data from tables, filter results with where, and obtain unique values using distinct in MySQL for data analysis.

Learn how to use select with where and distinct in MySQL, including single-column and multi-column distinct, from clauses, and practical examples involving titles, departments, and dates.

Learn how to use the and operator to combine multiple conditions in SQL, building queries with select and from, and applying multiple criteria using and, or, and not.

Explore how to use the and operator to filter data in MySQL, combining department, titles, current employees, salary thresholds, and hire dates to find precise employee subsets.

Explore how the or operator selects records when any one condition is true, combine with and, group with brackets, and apply to employee data using salary and dates.

Explore how to use the not operator alongside and and or in sql queries, with brackets for grouping, to filter current employees, departments, titles, and hire dates.

Apply and, or, and not to filter employees by title and current status, using brackets to combine conditions and include staff or senior staff while excluding not current employees.

Learn to use arithmetic operators in MySQL—addition, subtraction, multiplication, division, and modulo—on numeric columns like salaries, using select statements to compute raises, taxes, and net pay.

Explore how to use comparison operators in SQL to filter results, including equal to, not equal to, greater than, less than, and ranges, with examples on dates, salaries, and titles.

Explore using the in and not in operators to specify multiple values in SQL queries, with practical examples from the titles table and quoted values.

Explore the between operator to select rows within a value range, using lower and upper bounds, with inclusive endpoints, and compare it to using separate comparison operators.

Explore the like operator and wildcards in SQL for pattern matching, using percent, underscore, and bracket expressions, including not in patterns, to filter first and last names in employees table.

Examine the like operator and its negations, not like, not between, not in, using % and _ wildcards to filter employees table by first name, last name, hire year, and gender.

Learn how to use aliases to give columns temporary names in sql, and see how to select from salaries and employees with aliases like salary as net salary.

Explore how to use limit, max, and min in MySQL to constrain results, compute extreme values, and apply group by and order by for per-employee salary insights.

Learn to join employees and salaries, group by employee number, and display each employee's first name, last name, max salary, min salary, tax (10%), with a limit of 50 results.

Learn to use sum, average, and count functions in MySQL to compute total and average salaries, count employees, and analyze salary by department with joins and group by.

Explore how to use order by and group by clauses in MySQL, applying aggregate functions like count to group data by department and sort results with ascending or descending orders.

Learn how to use the having clause to filter grouped results. See how rollup yields totals of aggregates, with examples counting employees by department and computing overall totals.

Clarify rollup rules, demonstrate grouping by product code, and sum quantity and price across joined tables using a single value rule.

Explore different types of functions in MySQL, including normal, date, and string functions, and learn how to convert from one function to another using the cluster and convert keywords.

Master casting and converting data types in MySQL to transform integers, dates, decimals, and strings using cast and convert, including signed vs unsigned and precision control.

Explore null handling in MySQL with is null, is not null, and coalesce, using real table examples to distinguish NULL, empty values, and how to retrieve first non-null values.

Learn how to use coalesce to return the first non-null address components across two tables, joining customers and offices and handling missing city or country values.

Master date functions in MySQL, including now and current_date for timestamps, the date function for deriving dates, and date_format to format dates in various styles.

Learn how to compute age and date differences in MySQL using datediff and timestampdiff, handle date order, and add intervals to birth dates to project future ages.

Explore date functions in MySQL to compute ages, add or subtract intervals, and analyze future dates such as age in five years and retirement timelines.

Learn to calculate each employee's retirement year by adding 70 years to birth dates using MySQL date functions, enabling retirement planning and staffing insights.

Explore how the CONCAT function merges strings in MySQL, joining first and last names with spaces to form full names and concatenating address fields into a full address.

demonstrates the instring function to locate a substring within a main string and returns its starting position, with examples using select statements and like patterns.

Explore MySQL string functions, including lower and upper, length, left, right, and substring, with practical queries on customers data to create full names.

Learn MySQL string functions trim and replace, removing leading and trailing spaces or zeros and substituting substrings in data.

Recap the section's focus on SQL functions, including convert statements, is null, is not null, and coalesce, then date and time functions, and finally string functions.

Master SQL joins, including inner and outer joins, left and right joins, self joins, and cross joins, through theory and hands-on practice in this essential section of the SQL bootcamp.

Explore inner joins in MySQL: learn how to combine multiple tables on a common column, using from, inner join, and on clauses to build a unified data view.

Learn how a left join pulls all rows from the left table and matching rows from the right table, returning nulls when no match.

Master left joins by combining customers and orders to pull customer names and order numbers, and see how the left join returns all customers even without matching orders.

Learn how right join, the opposite of left join, uses the right table as the master table, returns matched rows, and uses nulls for left table columns when no match.

Explore cross join behavior by multiplying all rows from two tables, note how unmatched left and right rows affect results, and consider size implications of full outer join.

Learn how to perform a self join by aliasing the same table and comparing rows within it, using inner join, left join, or right join.

Explore self joins by linking an employee table to itself to reveal employee and manager details, titles, and reporting relationships, then analyze salary ranges with aggregate min and max.

Explore sql joins, including left and right outer joins, cross joins, and self joins, with practical exercises on box office, employees, and organization databases.

Explore SQL set operators, including union, intersect, and minus, as this section introduces core concepts and practical examples for data analysis.

Explore how the union operator merges two queries into one result set, with union distinct or union all, requiring compatible data types and parallel column structure.

Learn to simulate a full outer join in MySQL by combining two left joins with the union operator, using the customizable and staff tables with store_id as the key.

Learn how to perform a full outer join using the union operator, combining departments and employees data with left joins on department numbers.

Explore the intersect operator, which returns the common rows between two queries, requiring identical column order and data types. When not supported, simulate it with distinct and an inner join.

Discover the minus operator in SQL, which returns rows present in the first dataset but not the second, and learn to simulate it with a left join and null checks.

This section recap covers the union operator, the distinction between union listings and union all, and practice with the miners operator and the intersect operator as three main set operators.

Explore advanced case statements, subqueries, derived tables, and common table expressions in MySQL through practical, hands-on practice.

Master the sql case statement as a flexible if-then-else construct, using case when conditions then results, else other results, end, to categorize orders by sum(quantity_ordered) into small, medium, and large.

Explore the case statement to classify employee salaries into low, medium, and high using current and latest salary data from the employees and salaries tables, with between ranges.

Master subqueries in MySQL to filter employees by department or country, compare nested queries with inner joins, and practice with employee and office tables for data analysis.

Learn how to use derived tables by aliasing subqueries to compute current salary statistics, max, min, and average, from a salary table.

Explore derived tables and subqueries with inner joins to combine department data and current managers, retrieving manager names and department names across multiple tables.

Master derived tables in MySQL by building inner joins across orders, products, and details to calculate 2003 sales, including product names, quantities, and selling prices.

Learn how to use the exists keyword in SQL with subqueries to filter customers who have placed orders, and compare exists versus not exists in data analysis scenarios.

Common table expressions (cte) name a derived query and create a temporary result set, letting you query that result with the outer query.

Delve into subqueries and derived tables, explore exists and not exists checks, learn common table expressions, and practice kiss keywood, case when and else.

Master modifying data in MySQL by using insert, select, update, delete, and replace statements, including inserting multiple values and performing bulk updates.

learn to use the insert statement to add rows with values into a departments table, specify columns when needed, and insert multiple rows in one command.

Learn how to insert into a secondary table from a select statement to create a department 001 employees subset. Build and inspect a temporary table using column lists.

Learn to use insert into select to create a larger joined table of employees and departments, extracting employee names, department names, and current salary along with date ranges.

Learn to use the update statement to modify existing data in MySQL, with set and where clauses, demonstrated on changing customer emails to a box office dot com.

Understand delete statements in MySQL, including delete from, delete cascade, and on delete cascade, and how foreign key constraints control cascading deletions.

This lecture explains the replace statement in MySQL, showing two styles—insert-like and update-like using replace into and sets—handling existing rows or inserting new ones in a table.

This recap covers inserting single and multiple rows, creating tables, inserting via select with joins, updating, cascade delete, and replace value operations; preview foreign keys, primary keys, and data types.

Create databases and tables, select data types and constraints, and build tables with primary keys and auto-generated columns using create table like.

Explore SQL data types, including exact numeric (integer), decimal, and approximate numbers, plus date, time, varchar, and timestamp. Learn how to define column types in tables with real-world examples.

Learn how sql constraints ensure data accuracy, covering not null, unique, primary key, foreign key, check, default, and index constraints, with a school database practical.

Create a school database and five related tables, define relationships and constraints, and use sql to set up a new schema and tables with primary keys and foreign keys.

Design and create a MySQL database by building the students, instructor, and department tables. Set up dependencies with on delete cascade and use workbench or scripts.

Learn to create instructor and department tables in MySQL using if not exists, define primary and foreign keys with on delete cascade, and model one-to-many relationships from instructors to departments.

Learn to create a new table with the same structure as an existing table using create table like, enabling backups while preserving primary keys and constraints.

Explore drop table, truncate table, and alter table in section nine, review their differences, and reinforce creating databases and tables you practiced earlier with the provided documentation.

Learn the difference between drop and truncate in MySQL. Drop removes the table and its structure, while truncate deletes only the data, preserving the table for future use.

Learn how to use alter table to add, drop, modify, and rename columns or tables in MySQL, including changing data types and column order.