10 Day MySQL Bootcamp | My SQL Database Design for Beginners

Begin day one of the 10-day mysql bootcamp by setting up your workspace and exploring the mock Planetary Goods e-commerce database, including the product and order data.

Discover how databases organize, store, and persist data behind apps, from hardcoding data to relational tables and NoSQL JSON formats, and understand schema and dynamic updates.

Understand how a database management system (DBMS) serves as the interface between users and databases, enabling data abstraction, security, and backup and recovery across relational and NoSQL systems.

Discover relational database concepts by examining tables, columns, and rows, and learn how primary keys provide unique identifiers to correctly fetch or update records.

Explore how SQL enables querying and retrieving data from relational databases, including the select statement and the major language families: DQL, DML, DDL, DCL, and TCL.

Install and configure MySQL on Windows by using the official mysql.com installer to install the server, shell, and MySQL Workbench, then connect via localhost:3306 in MySQL Workbench.

Install MySQL on mac computers by downloading the macOS dmg from mysql.com, choose the correct OS version, install, set root password, and optionally install MySQL Workbench to connect and test.

Install MySQL server and MySQL Workbench on Linux, using apt and snap, then start the service, secure the installation, and connect to a local database.

Explore the MySQL Workbench interface, including the admin pane, schemas, tables, and the info and output panels. Use the SQL editor to run commands and manage scripts, with customizable preferences.

Create a working dataset for this course by loading data and executing a SQL script in MySQL Workbench, exploring the planetary goods schema and its tables.

Use the use database command to select the database you will query. Verify the current database with select database and handle errors such as unknown database or insufficient permissions.

Explore the sql select statement to retrieve data from tables, return a result set, and understand when to use star for all columns versus selecting specific columns for performance.

Learn how to use the select statement to fetch all columns (or specific column names) with the from clause, and apply the where keyword to filter rows.

Solve a hands-on SQL challenge by querying the products table to fetch a snapshot of all items for the planetary goods inventory department, practicing writing SQL queries.

Learn to retrieve a specific product's details from a MySQL database by writing a targeted query against the products table using the product name as a filter.

Master single-line and multi-line SQL comments to clarify code, leave reminders, and temporarily ignore queries. Use two dashes, hash, and /* */ syntax without affecting execution.

Set up the MySQL workspace with workbench and server, then list tables in the planetary database and fetch product data using select, from, and where.

Learn SQL to extract unique product categories, build customer reports with emails and demographics, identify customers missing addresses, and rank products by price with top five premiums.

Learn how the sql select statement works, including selecting specific columns or all with *, using from and where, and how query order affects results while column names are case-insensitive.

Explore SQL data types and how each column stores numeric, date time, or string values, including int, float, date, varchar, and timestamp.

Learn how to eliminate duplicate rows using the distinct keyword, fetch unique cities and country-state combinations, and understand performance considerations on large datasets.

Explore how to retrieve unique category and subcategory pairs from the product table using distinct, helping marketers gauge product diversity without duplicates.

Use column aliases in SQL to create readable headers with the as keyword, temporary for the query; enclose spaces in quotes and concatenate first and last names with concat.

Write a MySQL query to generate a customer report with full name, email, and demographic data from the customers table, using concatenation and descriptive column aliases.

Explore arithmetic expressions in SQL select statements, using plus, minus, multiply, divide, and modulo to compute calculated columns such as total worth of stock.

Learn how null values in MySQL indicate unknown or missing data, differ from empty or zero, and how to use is null and is not null to handle them.

Learn to handle null values in SQL select statements using ifnull and coalesce, and build readable addresses by concatenating city, state, and country while gracefully managing missing data.

Learn to sort query results using the order by clause, choose ascending or descending order, and sort by one or more columns.

Solve the pricing review by listing Planetary Goods products sorted by price from highest to lowest using a MySQL query. Learn to use order by price desc.

Learn to limit results with the limit clause, order by total paid for top orders, and use offset for pagination.

Retrieve the top five most expensive premium products from the products table by using a sql query with select star from products, order by price descending, and limit 5.

Review core SQL techniques for business problems: distinct categories, concatenated names with aliases, handling missing addresses with COALESCE, sorting by price, and listing top five premium items.

Develop SQL proficiency with the where clause to filter inventory under 20, fetch US, Canada, and Mexico customer data, spotlight star products, price adjustments, and tax checks.

Learn how the where clause filters query results by specifying conditions that records must meet. See examples using select star or specific column lists to improve efficiency on large tables.

Explore the three categories of SQL operators—arithmetic, comparison, and logical—and learn how they perform operations on data, produce boolean results, and refine select statements.

Explore arithmetic operators in SQL for calculations in select and where clauses, including addition, subtraction, multiplication, division, and modulo, illustrated with a 10% discount example.

Solve a challenge using a where clause to identify orders where tax exceeds 5% of the final total in the order details table.

Explore SQL comparison operators that return boolean results and filter data using equal to, not equal to, less than, greater than, and their inclusive forms in where and having clauses.

Learn how the and operator filters records by requiring all conditions to be true, with examples using country USA and city New York City, and chaining conditions for clarity.

Apply the and operator to count products in the accessories category priced under $30, practicing hands-on SQL queries in this 10-day MySQL bootcamp lesson.

Learn how the or operator in sql filters records when any condition is true, with examples like USA, Canada, and Mexico, and use parentheses to improve readability.

Solve an inventory check using the products table to find items in accessories or optic categories with stock below 20. Learn how parentheses control operator precedence to produce correct results.

Learn how the not operator in SQL negates conditions to reverse queries, with examples like selecting non-USA customers and guidance to test queries with limits to avoid broader results.

Learn how to simplify sql where clauses by using the in operator instead of multiple or conditions, with examples like country in ('USA','Canada','UK') and not in.

Filter customers by location using SQL operators, showing OR and the easier IN clause. Retrieve names and emails for USA, Canada, and Mexico.

Tackle this challenge by fetching popular products from the accessories and optics categories using MySQL, selecting specific columns and validating data to guide promotional decisions.

Master the between operator in SQL to filter data within a range with inclusive boundaries, using numeric prices, dates, and text ranges with practical examples.

Solve a price adjustment challenge by querying the products table for home decor items priced between 20 and 100, using sql with between and and operators.

Learn to search data patterns with the like operator and wildcards % and _, matching starts with, ends with, or specific positions, plus escaping and performance tips.

Write a sql query to list products whose names start with star using the like operator star% in the products table, solving the challenge and returning matching results.

Master the where clause to filter null values using is null and is not null, count records, and avoid the common pitfall of comparing null with equals.

Master nested where clauses in MySQL by combining conditions with and or operators and using parentheses to control precedence for precise queries.

Review day teaches using where clauses and operators in MySQL to solve business problems, from inventory checks and customer targeting in US, Canada, and Mexico, to promoting accessories and optics.

Learn to use aggregation and grouping in SQL to analyze sales data, generate reports on sales volume, bestsellers, customer loyalty, premium customers, frequent shoppers, and average order value.

Group and count data by manufacturer in a toy shop example to illustrate data grouping and aggregation using the group by clause and aggregate functions for actionable insights.

Master aggregate functions in SQL, including count, sum, average, min, and max, and apply them to data with or without distinct values, where clauses, and null handling.

Master the group by clause, grouping data with aggregate functions such as count, sum, average, max, and min. See how where filters before grouping and order by sorts after grouping.

Generate a product-wise sales report by grouping by product, summing final total from order details, and ordering results by total in descending order.

Combine aggregate functions with group by to derive per customer insights, calculating count, sum, average, min, and max in a single query.

Identify city-wise user distributions to inform targeted marketing campaigns, logistics planning, and local partnerships. Use a group by city query with count(customer_id) to produce the city and its user count.

learn to identify the top three best-selling products by total sales volume using SQL with order_details, summing quantity and final total, then sort and limit to three.

Master grouping by multiple columns in SQL to analyze demographics by city and country. Count customers, apply aggregate functions, and order by number of customers to reveal hierarchical patterns.

Learn how the having clause filters grouped results and aggregate functions with group by, differs from where, and counts customers by country to find cities with more than five.

Identify premium customers by calculating each customer's total order value from the orders table using sum(total_paid) and group by customer_id, having total order value greater than 1000.

Identify frequent shoppers by counting orders per customer with the orders table, grouping by customer_id, and filtering with having total_orders > 5, noting no customers exceed five orders.

Solve a business problem by computing the average total order cost per customer using the orders table, grouping by customer_id and applying an average aggregate.

Explore nested aggregation and grouping in sql by using subqueries in from, where, and select clauses with having to filter totals by the average.

learn common MySQL pitfalls in grouping and aggregation, including group by vs select order, null handling with coalesce, and proper use of having and order by.

Explore practical applications of grouping and aggregation in sql to build business reports, summarize sales by year and month, and identify highest and lowest selling products.

Review how to solve business problems with group by and aggregation in SQL, including total sales by product, top products by quantity sold, customer loyalty, premium customers, and average spend.

Learn to use SQL joins to combine data from multiple tables and solve business problems, from customer orders and loyalty programs to product sales, retention, and city-level supplier insights.

Explore how SQL joins combine data from multiple tables using related keys, covering inner, left, right, full outer, self, cross, and natural joins, with MySQL not supporting full outer join.

Learn how inner join in SQL combines related rows from customers, orders, and products using join predicates and on clauses, with table and column aliases, to produce matched results.

Learn to write an inner join across four tables: customers, orders, order details, and products, to show each customer's total quantity per product, using group by and aliases.

Generate a sales volume report that shows product id, product name, and total order amount by joining order details to products, using sum and group by.

Learn how left join in SQL combines two tables on a related column, returning all rows from the left table and matched rows from the right, with nulls for nonmatches.

Design an inventory analysis by computing total quantity ordered per product using a left join between products and order details, handling nulls as zero and ordering by quantity.

Apply right join in sql to pull all rows from the right table and matched rows from the left, such as orders with customers, with nulls for unmatched data.

Learn how to use on and using in SQL joins to specify join conditions, compare their syntax, when column names match, and how each approach affects flexibility and readability.

Learn self joins in sql using table aliases to compare a table with itself, find products with the same price, and identify similar names with the left function.

Apply a self join on the customers table with aliases to find city-based customer pairs, excluding the same customer by id.

Execute cross joins in SQL to produce the Cartesian product of two tables without a condition, pairing every customer with every product. Use this sparingly to avoid huge result sets.

Explore natural joins in SQL, where the engine implicitly uses common column names as the on condition to join tables, with caveats about unintended matches.

Craft a sql query using inner joins across customers, orders, order details, and products to produce a customer analysis report with customer name, order date, product name, and final total.

Analyze customer purchases to show which products and suppliers are popular for each customer, using multi-table joins across customers, orders, order details, products, and suppliers to compute final totals.

Explore common SQL join pitfalls and best practices for MySQL, including indexing join columns, avoiding cartesian products, disambiguating column names, and using explicit joins, aliases, limits, and null handling.

Day 5 review covers inner, left, right, self, and cross joins with practical business problems, showing how to analyze customers, orders, products, and city relations.

This day introduces designing a robust, efficient database for quantum tutors, a new online education platform, to streamline interactions among students, tutors, courses, and admins.

Design a structured database by defining tables, relationships, and constraints to ensure data integrity, efficient retrieval, scalability, and secure centralized storage through an iterative design process.

Gather requirements for Quantum Tutors by interviewing stakeholders, understanding end users and business needs, and mapping to database design, creating tables for customers, courses, enrollments, payments, reviews, and admin.

Explore the core concepts of the entity relationship model by defining entities and attributes. Identify one-to-one, one-to-many, and many-to-many relationships in a university database.

Learn to visualize and document databases with entity relationship diagrams, using MySQL Workbench reverse engineering and draw.io to plan, export as png or pdf.

Learn one to one relationships in database design by linking a users table to a credentials table via user_id, using primary and foreign keys, with data separation and performance benefits.

Explore one to many relationships in database design, linking a single record to multiple records with author id and crowfoot notation, and how this reduces redundancy and improves data integrity.

Understand many to many relationships by using a junction table between students and courses to track enrollments, reduce redundancy, and enable joins for data retrieval.

Explore normalization, a database design process that organizes data into smaller, nonredundant tables, reducing redundancy, eliminating anomalies, simplifying queries, and enforcing referential integrity with primary and foreign keys.

Apply first normal form by ensuring a primary key and atomic, single-valued attributes, producing unique records and, when needed, separating multi-valued topics into a dedicated course topics table.

Explore second normal form by ensuring every non-key attribute fully depends on the entire primary key, shown with a lessons table and a separate courses table to remove partial dependencies.

Master the third normal form (3nf) by ensuring every non-key attribute depends only on the primary key, avoiding transitive dependencies, with instructor data in a separate table linked by id.

Identify core relationships for the database design, including customers enrolling in multiple courses, courses linked to an instructor and multiple lessons, and reviews and payments associated with customers and courses.

Design a database for the quantum tutors system by applying database design principles, diagrams, entities, normalization, and relationships, culminating in a complete diagram for quantum tutors.

Materialize the quantum tutors design with DDL to create and alter tables for customers, instructors, courses, enrollments, and payments, enforcing not nulls, durations, and archival workflows.

Explore data definition language, ddl, a subset of sql that manages database structure, enabling you to create, alter, drop, truncate, and rename database objects with hands-on practice.

Learn to create a new MySQL database with the create database statement and switch to it using the use database command, then verify with show tables while following naming rules.

Set up the quantum tutors database by running the create database command, name it quantum_tutors_db for readability, and prepare it for all subsequent sql statements.

Create tables in MySQL using create table syntax, define columns with data types and constraints (not null, primary key, auto increment) for a customers table with fields customer_id and first_name.

Learn how to describe SQL tables to inspect structure and metadata using describe or the shorthand desc; understand fields, data types, nullability, primary keys, defaults, and auto increment.

Defines a primary key as a unique, not null identifier for a table row, using column-level or table-level syntax, including single and composite keys, with examples and duplicate entry errors.

Learn how foreign keys link tables through a primary key, enforcing referential integrity by preventing invalid child rows and improving data retrieval across related tables.

Define and link the remaining tables for quantum tutors, including customers, instructors, courses, enrollments, lessons, payments, reviews, and admins, using primary keys and foreign keys per the schema.

Explore ready-made sample data for the quantum tutors schema to practice MySQL queries, including customers, instructors, courses, and enrollments, with a drop database load script.

Learn to copy tables in SQL with create table as select, duplicating data or just the structure, using LIKE and WHERE clauses, and cross-database reference with database.table.

Learn how to use the alter statement in SQL to add, delete, drop, or rename columns and tables, apply constraints, and adapt database design.

Learn how to use the alter table add column statement, specify data types and constraints, and control column order with after and first options.

Add a course duration column in hours to the courses table using an alter table statement with integer data type, initializing values as null.

Use alter table to drop columns from an existing table, dropping single or multiple columns with comma separated statements and verifying via describe.

Apply the alter table drop column command to remove the subcategory field from the courses table as the business model changes. Verify the change by describing the table.

Use alter table to modify a column’s data type and constraints, such as not null or varchar, perform multi-column changes, and understand compatibility and truncation risks.

Enforce data integrity by ensuring every course has an instructor, alter the courses table to set instructor_id as not null and avoid orphan records.

Extend the courses table to support bilingual titles and more categories by altering the category and title columns to varchar(500), and verify the updated schema.

Learn how the SQL drop statement deletes databases and tables, including syntax, use cases, and the crucial need for backups and the if exist option to handle warnings or errors.

Learn how drop statements interact with foreign key constraints and why dropping a referenced table, such as customers, fails until all foreign keys are removed to preserve referential integrity.

Learn how the truncate statement deletes all rows while preserving table structure, and why foreign keys can block truncation to protect referential integrity.

Demonstrate data pruning for a development environment in a MySQL bootcamp by truncating the payments table to prevent financial data exposure, using a truncate statement instead of drop.

learn how to truncate or drop data with foreign key references by temporarily disabling foreign key checks or dropping and recreating constraints, and understand risks of referential integrity.

learn to rename SQL tables with the rename statement, changing a table name without altering data. rename single and multiple tables using comma-separated pairs.

Review day seven by showing how we design and modify the quantum tutors schema—adding course duration, removing subcategory, enforcing instructor_id not null, extending category and title lengths, and truncating payments.

On day eight, learn to manage data growth and accuracy with data manipulation language (DML) and SQL queries for onboarding admins and instructors, updating profiles, course titles, discounts, and resets.

Explore how data manipulation language (dml) in sql lets you insert, update, and delete data stored in database tables.

Explore how to use insert into with and without a column list in SQL, control column order, handle nulls and autoincrement IDs, and understand not null constraints.

Practice inserting new admin records into the admins table using insert statements to onboard a new admin, including first name, last name, contact number, email, and password.

Insert multiple records into a MySQL table using insert into with a column list and multiple value rows, and learn how to handle missing columns and nulls.

Practice inserting multiple records into the instructors table by onboarding three new instructors—Hannah, Chris, and Diana—with copy-paste details from an attachment and a multi-row insert. Verify results with a select.

Learn to insert data from another table using insert into select, ensure column counts match, and use subqueries to identify customers who enrolled in courses worth more than 250 dollars.

Explore the SQL update statement to change one or more columns using a set clause with a where clause, illustrated by increasing a specific course price by 10%.

Learn to update course titles in MySQL with the update statement, resolve safe update mode issues, and apply new titles like basics of quantum mechanics.

Learn to update multiple columns in a single SQL update statement, append text to descriptions with concat, and increase prices by 10% for physics courses using a where clause.

Update John Doe's city and state from New York, New York to San Francisco, California in the customers table using an update query with a where clause, and verify.

Use a where clause to update multiple rows in SQL, applying a 10% discount to physics and computer science courses priced above $120, preview a select to verify before applying.

Write an update query to discount physics courses priced above 100 by 20% for the next month, updating multiple rows in the courses table by multiplying price by 0.80.

Learn how to use the delete from statement with a where clause to remove records matching specific criteria, while preserving data integrity when foreign keys are involved.

Execute delete statements to retire the course 'Quantum Computing Basics' by removing dependent records in enrollments, lessons, and reviews, then delete the course using select statements to verify integrity.

Learn to reset a MySQL database by writing delete statements to remove data from all tables while preserving admins, and practice deleting in the right order to maintain referential integrity.

Discover the differences between drop, delete, and truncate statements: drop removes the table; delete clears rows with an optional where clause, and truncate clears data while preserving structure.

Review day covers insert, update, and delete statements through admin, instructor, and course scenarios. Practice multi-record inserts, multi-column updates with where clauses, and referential cleanup for retirement and resets.

Use subqueries to gain day nine insights on course category pricing and highest price. Identify instructors with no courses, top performers above four stars, and remove reviews for discontinued courses.

Explore subqueries as queries within another sql query, used in where, from, and select clauses, and learn how to fetch enrollments for United States customers via inner queries.

Identify customers enrolled in programming category courses by joining enrollments with courses, then calculate the total amount paid from payments using a subquery with an in clause.

Master correlated and non-correlated subqueries in SQL by computing the average price with an inner query to filter outer results, including category-specific comparisons and performance considerations.

learn to identify the most expensive course overall and the top-priced course per category using non correlated and correlated subqueries, with practical SQL query demonstrations.

Explore exists and not exists in subqueries and sub query to identify customers with at least one enrollment and those with none, using outer and inner queries.

Identify instructors without courses by using a not exists subquery to return instructors whose id does not match any course's instructor id.

Learn how to use any and all with subqueries in sql to compare course prices across categories, finding items priced higher than any or all physics courses.

Identify top performing instructors by ensuring every course has an average rating above four stars using the all operator, joining courses and reviews to compute per-instructor averages.

Learn how single row subqueries return a single value and drive comparisons in a where clause, such as filtering courses by prices above the average.

Learn how multi row subqueries return multiple rows and work with in, any, or all to filter results. See how a United States customer list feeds the enrollments table.

Learn to use subqueries in insert and delete statements, with not in and a subquery of distinct course id from enrollments to target courses never enrolled.

Practice writing a delete statement with a subquery in the where clause to remove reviews for non-existing courses, using delete from reviews where course_id not in (select course_id from courses).

Day 9 review reinforces MySQL subqueries through business problems like category insights and total payments, and demonstrates price comparisons, not exists, correlated, non-correlated, and delete with subquery patterns.