Modern Data Analyst: SQL, Python & ChatGPT for Data Analysis

Install MySQL Workbench on Windows by downloading the complete version, selecting custom options, configuring MySQL server 8.0.30, and signing in to the local instance.

installing MySQL on macOS by downloading MySQL Community Server from mysql.com, running the installer, setting a root password, and then installing MySQL Workbench to manage a local instance.

Explore how SQL data types define what values a column can store. Understand bit and byte units, integers and floats, money and decimals, char and varchar, date and time formats.

Learn to create a MySQL database and a players_description table, define a primary key with auto_increment, and insert football players data, as shown with Lionel Messi.

Import a CSV into MySQL by creating and using bank_db, then import into a new bank table with the table data import wizard and review 4521 records.

Learn how to use the select command to retrieve specific columns from a table in SQL, including selecting name and weight from players_description and using * to fetch all fields.

Learn to use the insert command in sql to add new rows with insert into the players_description table values, ensuring the value order matches the columns.

Master the min function in SQL to return the minimum value from a column, with examples using age, id, height, and first name in the players description table.

Discover how the max function in MySQL returns the maximum value from a column, with numeric examples for age and weight and a maximum for text based on alphabet order.

learn to group identical data with the group by clause in sql, use select from statements, and apply aggregate functions like max, min, and sum to display tailored results.

Explore the where clause to filter records by conditions using select from, with age, nationality, and birth date examples. Practice group by and max to calculate maximum euros by nationality.

Learn how to use the sql sum function to aggregate numeric values by groups, with examples by nationality and by position, and calculating total wage by club.

Learn the SQL average function to compute mean values, using group by and where, with examples of average age by nationality and average wage by club.

Learn to use the count function in SQL to tally rows and groups with count(*), count(column), and where clauses, including grouping by nationality and wage_EUR greater than 200.

Learn how the and operator filters records by requiring all conditions to be true in a where clause, with practical SQL examples using age, weight, and center back players.

Learn how the or operator in SQL filters records when either condition is true, with syntax, a from clause and where clause structure, and examples using age, weight, and nationality.

Master the SQL in operator to simplify multiple values in a where clause. See examples using nationality, clubs, and ages to replace multiple or conditions with concise syntax.

Master the like operator in SQL, using the % wildcard to search patterns in the where clause, with beginnings, middles, and endings illustrated.

Learn how the between operator selects values within a range, inclusive of endpoints, for numbers, dates, and text, with examples filtering ages, birth dates, and names.

Explore the order by keyword to sort data ascending or descending, using asc or desc, and apply with group by, where, and average functions.

Learn how to use the having clause to filter results after a group by with aggregate functions such as average, and compare it to where through nationality and age examples.

Master the update statement to modify existing records using set and where clauses. See examples updating club and nationality values with verification queries.

Use the distinct statement to find unique values in a column with select from. Practice counting players per club and per nationality by grouping and ordering results.

Master left and right string functions in SQL to extract characters from the left or right, using select from syntax and examples like nationality and date sale column.

Explore the SQL length function to measure string lengths, use select length(column), and apply having and order by to analyze city and payment data in MySQL.

Learn to convert strings to lowercase and uppercase with the lower and upper functions in SQL. Practice applying these to columns like nationality and product line in MySQL.

Explore the repeat function to duplicate strings in a column, specify repetition count, and create a new column with examples from supermarket sales in SQL and MySQL.

Master the sql replace function to substitute strings across columns like nationality, product_line, and customer_type, apply conditions with where and between dates, and alias results.

Explore the trim, ltrim, and rtrim functions in SQL to remove leading, trailing, or both spaces from strings, with practical examples and length verification.

Explore the cast and convert functions to convert values to specified data types, compare syntax and compatibility, and see MySQL examples converting char to date time and date to char.

Explore how the SQL concat function joins strings with a space, demonstrated by name and last name, and by concatenating customer type with payment in MySQL, plus length checks.

Apply core date, day, month, and year functions in MySQL to extract day, month, year, and current date from date columns such as birth date and date sale.

Create temporary tables in SQL by selecting specific columns from a stored bank table; learn to build two temp tables and drop them when the session ends.

Learn how to join tables in SQL, focusing on inner joins and extending to left, right, and full outer joins, using a practical two-table example with a common customer id.

Learn how subqueries, select statements embedded in a main select and enclosed in parentheses, drive queries with examples like finding max salary in France.

Use a case expression to create a new column based on conditions. Classify birth years into generations using year extraction and multiple when-then else rules.

Explore the dense_rank window function, how it ranks rows within partitions with ties sharing the same rank, using partition by and order by in SQL examples, ranking products by rating in Yangon.

Explore SQL analysis of an Olympic games dataset by loading a CSV into MySQL, inspecting the athlete_events table, and solving exercises with count distinct, select distinct, group by, and having.

Identify sports that appeared in every Summer Olympic Games using distinct, having, and temporary tables. Learn to count sports per game and find athletics and swimming as the performing sports.

Learn to analyze Olympic data with SQL, computing yearly male and female participation using case and sums, and ranking medalists with dense_rank; identify US medal leaders by sport and event.

Install and set up Anaconda to access Python, Jupyter Notebook, and pandas, launch Jupyter Lab and Jupyter Notebook, and manage libraries and environments with Anaconda Navigator.

Explore the Jupyter Notebook interface, an open source web app for live code, visualizations, and text, widely used for data cleaning, transformation, and analysis in Python.

Explore jupyter notebook cell types and modes, including code, markdown, and raw nbconvert, and switch between command and edit modes with shortcuts like b, h, y, m, and ctrl-enter.

Master popular jupyter notebook shortcuts in command mode, including find and replace with f, run cells with ctrl-enter or shift-enter, and switch between code and markdown with y and m.

Learn how to write your first Python program by printing 'Hello world' using the print function, run code in Jupyter Notebook code cells, and explore strings, numbers, and comments.

Learn the common Python data types, including integers, floats, booleans, and strings, using the type function, and explore string methods such as upper, lower, title, count, and replace.

Explore how variables in Python store data values, assign strings with the equal sign, and combine messages using concatenation, spaces, and f-strings with embedded variables.

Explore Python lists in data analysis: create, index, slice, modify, and copy lists, use append, insert, remove, pop, del, sort (with reverse), and work with nested lists and heterogeneous elements.

Explore how Python dictionaries store key-value pairs, create and access items, add and update entries with update, copy dictionaries with copy, and remove items using pop, del, or clear.

Master the if statement in Python, including if, elif, and else blocks with proper indentation. Explore age-based examples to see how conditions choose adult, teenager, or kid messages.

Learn how to define and use Python functions with def, parameters, and return, and explore built-in functions like len, max, min, type, and range, including calling and basic loops.

Learn how Python modules work, import and access the os module, and use getcwd, listdir, and mkdir to manage directories and files in a notebook environment.

Explore pandas as a Python tool for real-world data analysis, and compare it with Excel to understand dataframes, series, indices, and data types.

Learn how to create a pandas data frame using numpy arrays, lists, dictionaries, and csv files, with code examples in a Jupyter Notebook.

Display data frames in pandas using head and tail to view rows, inspect shape, and show all rows by using pd.set_option display.max_rows after reading a csv into a df.

Learn how to access pandas dataframe attributes with dot notation, and use built-in functions, plus methods like head, info, and describe for data analysis.

Learn how to select a single column from a pandas data frame using square brackets and dot notation, handle multiword column names, and inspect the result as a series.

Learn to select two or more columns in a Pandas data frame using double square brackets, choosing columns like gender and math score, and preserve the order over single-bracket selections.

Add a new column to a pandas data frame using scalar values and numpy arrays. Create random integer scores that match the frame length.

Learn to add new columns to a pandas dataframe using assign and insert, create series from random numbers for scores, and understand when to overwrite the dataframe.

Explore how to perform operations on data frames in pandas, including column-wise sums, counts, means, std, and describe, and how to create a new average column with rounding.

Explore the value_counts method to count elements by category, calculate percentages with normalize, and round results to two decimals for clear, categorized data insights.

Sort a data frame with sort_values by a numeric column to see ascending order, use multiple columns with lambdas for text like race ethnicity, and control ascending and inplace updates.

Learn to use set_index to set a new column as the dataframe index and sort_index to order it, with NumPy shuffle and in-place updates in Pandas.

Learn how to rename columns and indexes in pandas using the rename method, including in-place updates, the columns and index arguments, and applying dictionary mappings.