Beginners Data Analysis Bootcamp with SQL

Understand how databases power everyday interactions—from web searches to login and bank statements—and why database management systems safeguard security, reliability, and scalable data handling for millions of concurrent transactions.

Describe the architecture of database management systems, highlighting the user application layer, ODBC connectivity, and the DBMS engine that processes SQL to access data stored on disk.

Explore how MySQL powers production sites for industry leaders, including Facebook, Wal-Mart, and Oubre, highlighting performance, reliability, ease of use, and low total cost of ownership.

Install MySQL on Windows with the official installer, set up server and MySQL Workbench, and connect via a named MySQL Workbench connection to run queries.

Install MySQL on macOS by downloading the MySQL Community Server and MySQL Workbench DMG files, install, remember the database password, and connect with Workbench to write SQL scripts.

Install and connect a MySQL server in the AWS cloud using the AWS console and MySQL Workbench, selecting a free-tier dev/test instance and configuring the endpoint and credentials.

Connect to a preset MySQL connection in MySQL Workbench, then write and execute SQL in the query editor, use the object browser and snippets, and review results.

Download the sample database that sequel provides, load the schema to create actor and address tables, and insert data to populate the Akila database.

Explore how data modeling reveals how datasets relate, creating a communicative data model that adds value, using my sequel workbench to view color-coded sections like customers and inventory.

Explore the building blocks of data modeling by examining tables (entities), their attributes (columns), data types, cardinality and relationships, plus primary and foreign keys.

Learn about database data types, including date formats (year-month-day and timestamp), strings (char, varchar), and numbers (integer, decimal), and use them for practical data analysis.

Explore information engineering notation for entity relationship modeling, and read cardinality and modality together using symbols like line for one, crowfoot for many, and circle for zero.

Explore one-to-one relationships between tables, where a manager can manage only one office and an office is managed by one manager, with primary and foreign keys illustrating the link.

Learn one to many relationships in SQL databases, where a customer can have multiple orders and each order belongs to one customer, using primary and foreign keys.

Examine many-to-many relationships between students and classes, using a junction table called enrollment to capture grades and enrollment dates.

Explain recursive relationships in a single table, using an employees example where manager_id references employee_id; managers can manage multiple employees, and some employees have no manager.

Understand normalization in data modeling by breaking a larger table into smaller ones to reduce duplicates, then apply first, second, and third normal forms to improve storage and maintenance.

Normalize the employee table from first normal form to third normal form by identifying repeating groups, splitting name into first and last names, and ensuring single-valued attributes.

Apply the second normal form by ensuring non-key columns depend on the entire primary key, and split into employee and department tables plus employee_assignment junction table for their many-to-many relationship.

Master third normal form by eliminating transitive dependencies and derived data, using employee table examples, and recognizing when to stop at 3NF in data modeling.

Explore databases and data modeling, then learn SQL, a structured query language for relational databases, with easy to learn syntax and ANSI standardization.

Classify sql commands into ddl, dml, dql, and dcl, and explore examples of create, alter, drop, insert, update, delete, and select statements.

Master ddl statements by creating a students table, defining columns with data types, then altering, dropping, or truncating the table and refreshing to see changes.

Explore how the select statement retrieves data from tables, detailing components like select, from, where, order by, and limit. See aliasing with as, and ordering and limiting results.

Learn to filter data with the where clause using comparison operators, between ranges, like patterns, and not in, then order by a date field.

Practice data manipulation language (dml) by inserting, updating, and deleting records in a country table, and verify results with select queries.

Master inner joins and syntax options, guided by the data model, to connect tables from country to city, language to film, and Barcelona customers.

Demonstrate how left join returns records from the staff table and matches them with the store table by staff id, highlighting managers versus non-managers, and explain left outer join variations.

Acquire skills in the right join, reverse of the left join, using table a and table b on common keys to link A and B with right outer join syntax.

Explore cross join, the cartesian product, which combines two unrelated tables to produce all color and size combinations, using select from tableA cross join tableB.

Perform a self-join by creating aliases and joining the employee table to itself to list employees with their managers, including those with no manager.

Explore sql set operations using union to remove duplicates and union all to keep duplicates; combine query results with matching data types and column counts; understand intersection and minus semantics.

Learn to simulate intersection in SQL by using exists or in to return the common records between two sets, with guidance on performance tradeoffs.

Learn the minus set operation, returning elements in one set but not in another, using not exists or not in keyword in MySQL, with lab scripts to practice minus-like operations.

Learn how data aggregation works using the group by clause, rolled up keyword, and common aggregate functions such as average, count, max, and min, plus standard deviation and variance.

Apply aggregate functions on a payments table to compute min, max, average, sum, and count. The example shows 0 to 11.99 and 16,049 transactions, with no group by yet.

Explore how group by partitions data by customer id to aggregate transactions, and compute total salary and employee count per department, with rollup adding a final summary row.

Use the having clause to filter grouped results after a group by, applying conditions like sum of salaries greater than 18000 or payments greater than 120 to show specific groups.

Explore nested queries in SQL, distinguishing correlated and non-correlated subqueries, using exists, not exists, any, all, and other predicates and operators, with hands-on lab exercises.

Learn how correlated subqueries in SQL use exists and not exists to pull customers who rented films for more than five days and films not in inventory.

Use non correlated subqueries to filter results, such as listing payments above the average and counting stores in Woodridge by address IDs.

Explore how to use string functions to clean data and format it for yearly audits, with category-based examples and step-by-step explanations.

Explore string functions in SQL, including soundex, cast, and ascii, to handle data quality issues, find names that sound like targets, and convert data types for ingestion.

Format string values in SQL using lowercase, trim, right trim, and padding to concatenate first name and last name, convert email to lowercase, trim spaces, and pad to length 15.

Learn to extract and manipulate data with string functions such as instr, left, mid, right, reverse, replace, and repeat, applied in lab exercises to build practical sql skills.

Explore numeric functions in SQL by applying mathematical functions like abs, floor, round, and sign, and arithmetic operators including division, subtraction, and addition to numeric data.

Learn how SQL date and time functions manage date formats, understand the anatomy of date and time fields, and practice with lab exercises on date portion, time portion, and milliseconds.

Explore SQL date and time functions to obtain current date and time, extract year, month, week, and day components, and format date values for reporting and analysis.

Learn date functions to add or subtract days, months, and years with interval, apply date add techniques, and analyze monthly transactions, rental durations, and peak renting days.

Apply conditional logic with the case statement to transform data values using when and then. Use an optional else on the film table to create a movie rating description.