SQL for Data Analysis: Beginner MySQL Business Intelligence

Introduce SQL and install MySQL and MySQL Workbench for the Maven Movies database. Set up essential tools for beginners before writing SQL queries.

Learn SQL, the standard language for relational databases, to access and process massive data quickly. This course demonstrates how SQL opens doors for data analysis and career opportunities.

Trace the history and flavors of SQL, from standards to extensions, and see how MySQL, MS SQL Server, SQL Lite, T SQL, and Microsoft SQL share a universal syntax.

Install and connect MySQL Community Server and MySQL Workbench, explore the interface, write queries, troubleshoot errors, and create a database using a ready-to-run script.

Download and install MySQL community server on mac, selecting the DMG archive and following the installer steps. Remember the root password for MySQL Workbench connections and verify in system preferences.

Learn how to download, install, and open MySQL Workbench on macOS, including version guidance and preparing for a future connection to community server.

Connect MySQL Workbench to the community server by creating a new root connection, entering the server password, and using the Workbench editor for most course tasks.

Explore the mysql workbench interface on Mac and PC, including the schemas tab, query editor, and action output. Write and run queries, view results, and troubleshoot.

Create and populate the Maven movie's database in workbench by running the Maven movie's SQL file, then verify the tables and schema reflect a DVD rental business.

Explore practical sql querying on the Maven Movies database, using select, from, where, and order by to analyze tables and uncover insights.

Explore basic database fundamentals with the Maven Movies example, examining tables such as inventory, rental, and customers, and how columns, rows, and timestamps organize data.

Identify the big six elements of a SQL statement—select and from, plus optional where, group by, having, and order by—to write simple, structured queries for data analysis.

Use the select clause to name columns in your query results, listing single or multiple columns separated by commas, and always include from, filters belong in a where clause later.

Identify the tables you query with the from clause, paired with select in every SQL statement. Understand that from cannot list multiple columns and that joins enable multi-table queries.

Discover how to run a first select star from query in the Maven Movies database to retrieve all columns and rows, using MySQL Workbench and readable SQL.

Master the use statement to select your database, avoid No database selected errors, and set a default schema like Maven Movies, with semicolon-separated SQL commands.

Select specific columns by naming them in the select statement, separating with commas, use line breaks for readability, and read the server response to fix syntax errors.

Begin your first sql data analysis assignment by drafting a select query to pull customers' first name, last name, and email from the Maven Movies database.

Demonstrates building a select from the customer table to retrieve first name, last name, and email, using workbench and the schemas tab, with export options to CSV or Excel.

Learn how select distinct returns unique values from one or more columns, producing distinct combinations, with a film ratings example and a pro tip to quickly understand a data set.

Write a sql query to pull the distinct rental durations from all films, ensuring only three, five, or seven days appear, and review results before the solution video.

Practice applying select distinct to reveal all rental durations from the film table, uncovering values of 3 through 7 days.

Learn to filter records with the where clause, an optional yet powerful SQL tool that follows from and precedes group by, having, and order by, using and/or for complex criteria.

Explore common where operators in SQL, including equals, not equals, and between, with examples filtering by amount and date. Learn the correct from versus where order to avoid 1064 errors.

Master filtering payment data for the first 100 customers with the where clause, using less than, less than or equal to, or between 1 and 100.

Learn to filter data with a where clause and multiple conditions using and, debug common syntax errors like missing date quotes, and refine results from 1000 to a smaller set.

Craft a SQL query using where and to filter the first 100 customer payments over five dollars and after January 1st, extracting payment information for beginner MySQL business intelligence.

Apply multiple logical conditions with where, combining them with and to filter sql queries, narrowing results to the first 100 customers' payments over five dollars after January 1, 2006.

Combine where and or to flexibly filter data, expanding results when any criterion matches. Use and to narrow, or to include multiple customer IDs, as shown in the example.

Write a SQL query using or in a where clause to fetch all payments from customers 42, 53, 60 and 75, plus all payments over $5 from any customer.

Learn to build multi-condition where clauses with or and and, pulling payments for customers 42, 53, 60, 75 and all payments over 5, to illustrate results and query logic.

Learn to simplify multiple or conditions by using the in operator in the where clause, replacing repeated customer id values with a single in for faster, easier-to-read queries.

Explore how to use the like operator with wildcards to match patterns in descriptions and titles, using multi-character and single-character wildcards and before-and-after patterns.

Learn to use the like operator with wildcards to filter films by features, such as behind the scenes specials, in this beginner MySQL data analysis assignment.

Use a like with wild cards query to pull film titles and special features containing behind the scenes from the film table, practicing pattern matching and a where clause.

Learn how the group by clause defines result grouping and supports multiple columns. Follow where, group by, having, and order by, and think of it as a pivot table.

Group by enables segment-level analysis using aggregate functions like count and sum, pivoting data into dimensions and metrics; troubleshoot common errors in workbench.

Learn to use comments and aliases to make SQL queries human readable, with single-line and multi-line comments and aliases like count of film ID as films with this rating.

Practice grouping by rental duration and counting titles per duration, producing two columns for rental duration and film count, and aliasing columns to match the example.

Apply group by on the film table to segment data by rental duration and count films for each duration, labeled as 'films with this rental duration'.

Group by multiple dimensions in MySQL by listing columns separated with commas to form groups and subgroups, enabling time-based trends like monthly or weekly.

Learn how to use aggregate functions with group by to generate group level summaries, including count, count distinct, min, max, average, and sum, with null handling and aliases.

Explore aggregate functions with group by, counting films and calculating the average, min, and max rental rates, grouped by replacement cost. Use aliases to create readable metric columns.

Learn how to use aggregate functions with group by to analyze replacement cost and rental rate, computing count, min, max, and average rentals.

Explore how the having clause filters grouped results after group by, using counts like rentals per customer to illustrate filtering and metric-based trimming.

Analyze customers with fewer than 15 rentals using the having clause. Retrieve a grid showing customer IDs and total rentals to guide feedback.

Use the having clause with a group by on customer ID and count of rental IDs to identify customers with fewer than 15 lifetime rentals.

Learn how the order by clause sorts results, with optional syntax, multiple columns, and ascending or descending options; use multiple criteria to create meaningful segments and trends.

Sort by amount descending to rank payments, with customer ID as a secondary criterion when amounts tie. Use order by with group by and sum(amount) to surface top customers.

Apply the order by clause to list film titles with their lengths and rental rates, and sort the results from longest to shortest to explore whether longer films cost more.

Learn to extract film titles with their lengths and rental rates from the film table and sort results by length descending using the order by clause, demonstrated in workbench.

Recap the big six elements of a SQL select statement: select, from, where, group by, having, and order by, and apply their order and usage.

Learn the case statement to create data buckets using ordered when then logic. Use an optional else as a catch all, and understand left to right evaluation until a match.

Explore how case statements implement conditional logic to bucket data into mutually exclusive, collectively exhaustive ranges using when-then pairs and a catch-all else, executing top to bottom.

Explore common case operators inside SQL case statements, using equals, not equals, comparisons, between, like, and in with a demo in workbench to bucket data and recategorize films.

Practice using the case statement to assign customers to store one or store two with active or inactive status, returning first name, last name, and status labels.

Demonstrates building a query with a case statement to label store one active, store one inactive, store two active, and store two inactive, returning first name, last name, and status.

Learn to pivot data in MySQL by combining count with case to replicate Excel pivots, counting inventory IDs per film across stores and grouping by film ID.

Learn how to recreate Excel-style pivots in MySQL by combining group by with case pivoting and count, counting items per film across stores.

Pivot data by store id using count and case to create active and inactive columns, then group by store id and count customer ids.

Tackle the midcourse project by extracting and analyzing data from the Maven Movies database to answer eight underwriter questions, using one table per question and the SQL scripts provided.

Explore using MySQL to extract and analyze data for an underwriter, covering staff, inventory by store, active customers, emails, films, categories, replacement costs, payments, and rentals.

Learn how relational databases relate tables and harness joins to query multiple tables, unlocking powerful SQL capabilities for data analysis.

Learn how normalization reduces redundancy and preserves data integrity by organizing data into related tables linked by primary and foreign keys, and explore 1-to-1, 1-to-many, and many-to-many cardinality.

Diagram relationships between tables by identifying the customer table's primary key and the rental table's foreign key, and visualize the connections in workbench or on paper to understand data structure.

Master multi-table querying in SQL by using join statements to connect film and inventory tables, producing a combined view of film titles in each store's inventory.

Explore the Maven movies database by examining film, inventory, rental, payments, customer, and store tables, and practice basic queries such as select distinct and group by across categories and languages.

Master inner, left, right, and full outer joins, plus union, to access data across a relational database. Learn the syntax and when to apply each join type.

Master inner join, which returns only matching records from two tables and excludes unmatched rows. Use the syntax inner join right table on left_table.column = right_table.column to select overlapping data.

Explore how inner join combines records from two tables by matching inventory ID, troubleshoot ambiguous 1052 errors, and enforce best practices by qualifying table names.

Perform an inner join across multiple tables to retrieve each film's title, description, store id, and inventory id from the inventory data.

Learn how to use an inner join to combine inventory and film data, matching on film id to retrieve inventory id, store id, and film title and description.

Use left join to return all records from the left table and any matches from the right table, like inner join, preserving left data even if there is no match.

Explore left join versus inner join through a film-actor example, counting actor film appearances and showing how left joins preserve all actors while inner joins require matches.

Pull a list of all film titles and count the actors associated with each title using a left join. Ensure every title appears, even those with no actors.

Use a left join to list film titles with the count of associated actors, ensuring films without actors still appear in the results.

Learn how the right join returns all records from the right table with matching left records, contrasted with left and inner joins, and why you should favor left joins.

Compare left, inner, and right joins side by side using actor and actor award tables to show how each join handles matches and nulls, and how results differ.

Explore the full outer join, aka full join, which returns all records from both tables, with matches where possible and using the on clause for pairing.

Bridge unrelated tables by identifying a bridging table, such as the address or film category table, within a snowflake schema to connect data through multi-step joins.

Bridge the actor info and title info using a bridge, producing a list of each actor with all titles they appear in.

Pulls the actor's first name, last name, and film titles by building a bridge through the film_actor table. Demonstrates joining actor to film via inner joins and bridging logic.

Explore multi condition joins in sql, comparing join-on criteria with and/or clauses to filter horror films, and considering performance implications.

Complete the multi-condition join assignment by pulling distinct titles and their descriptions currently available in inventory at store two. Use a multi-condition join and avoid using a where clause.

Master multi-condition joins to fetch distinct film titles and descriptions for store two using inner joins, with and without where clauses, and discuss performance.

Learn to use the union operator to stack select statements from advisor and investor tables into columns, first name and last name; union deduplicates by default, union all preserves duplicates.

Learn how union stacks results from multiple selects into one result set, ensuring the same number of columns, illustrated by advisor and investor tables with a type column.

complete the union operator assignment by combining multiple select statements to produce a list of staff or advisor names with first and last names in a type column.

This solution video demonstrates using the union operator to combine advisor and staff names, selecting first name, last name, and a type column to denote advisor or staff.

Begin tackling the final course project in sql for data analysis, preparing multi-table queries on the Maven Movies database to answer an acquirer's questions.

Explore the final project solution for SQL data analysis. Pull and join data to show store managers and addresses, inventory by store and rating.