Data Analysis | SQL,Tableau,Power BI & Excel | Real Projects

Explore the data engineer, data scientist, and data analyst roles and the data collection, cleaning, exploration, analysis, visualization, and reporting process, using SQL, Excel, Tableau, and Power BI.

Insert data into the customers table, adjust data types from char to varchar, run selects, add multiple rows, and drop the table and schema, illustrating DDL and DML basics.

Paste the classic models schema into a new MySQL query, run the script, refresh schemas, and verify the tables are ready for querying.

Turn raw Netflix data into a relational database in Excel by creating tables with primary keys, defining one-to-many relationships, and applying normalization to reduce repetition and improve data integrity.

Explore an optional Netflix titles project at the end of the course to practice relational databases with normalization, multiple tables, and primary and foreign keys visualized in an air diagram.

Explore how to build SQL queries using select, from, and where clauses, apply conditions with equals, not equals, and or, and handle strings enclosed in quotes.

Learn how to perform case-insensitive searches in SQL using upper and lower in where clauses, ensuring Leslie or D Murphy match regardless of data case, with examples in MySQL Workbench.

Learn to use in and not in to filter queries, apply upper for case-insensitive matching, and simplify multi-value filters instead of multiple or conditions.

Learn to use distinct to obtain unique values, apply like with wildcards for pattern matching, and order by to sort results ascending or descending, including multi-column ordering.

Learn how to use distinct to list unique order dates, customers, and employees, and apply like with wildcards to find negotiate in comments, noting case sensitivity and lower/upper normalization.

Learn how to combine customers and payments tables using inner and left joins, handle duplicates, and identify customers with no payments through is null filters.

Apply aggregate functions to compute total payments by date from the payments table using sum, group by, and having; round results to one decimal place and order by date.

Master count, max, min, and avg to summarize sql data. Count distinct order numbers and group by product code, then compute daily max, min, and average payments.

Join the orders and customers tables to identify the top customer by order count, then compute each customer's first and last order dates using min and max order date.

Learn how to write common table expressions (CTEs) as an alternative to subqueries, name CTE tables, use as, and chain multiple CTEs for clean, modular SQL queries.

Learn to use the case statement to categorize data into ranges and create flags in SQL, with examples of credit limit groups and end-to-end select syntax.

Create a flag with a case statement to mark orders of more than 40 motorcycles, joining orders, order details, and products, then sum the flag using a cte.

Learn how to use case statements to flag orders with negotiate or dispute in the comments, creating flags and strings for reporting and analysis in SQL queries.

Explore the row number window function, assigning a sequential number to rows by partition and order, with SQL examples, joins, and CTEs.

Explore lead and lag window functions to access next or previous payments within each customer's ordered payments, using over partition by and order by, and compute differences with a CTE.

Learn to extract year, month, and day from date fields, compute days between order and required dates with date diff, and use date add and date sub to adjust dates.

Master descriptive statistics and central tendency to summarize data using the mean, median, and mode. Identify outliers and data spread to guide deeper analysis and decision making.

Explore variance and standard deviation as measures of data dispersion and risk, illustrated with stock prices, mean, squared deviations, and the interpretation of volatility.

Master frequency distributions to organize data by counting frequencies and choosing between ungrouped and grouped formats. Visualize patterns with bar charts using grade or age buckets.

Explore the distinction between quantitative and qualitative variables, including discrete and continuous, nominal and ordinal types, and see how these classifications guide data analysis.

Master essential excel shortcuts for navigating and editing a Netflix spreadsheet, including ctrl+home, ctrl+end, shift+arrows, and page up/down sheet navigation.

Split text to columns to separate director, country, and listed in fields using comma as the delimiter. Rename and tidy the resulting columns, and next, explore the trim function.

Trim and separate data across sheets using the trim function, create show ID relationships, and paste values to prepare cast, directors, and countries for import.

Learn to sort and filter data by splitting the duration into minutes and seasons with text to columns, then filter to minutes and sort by duration from largest to smallest.

Learn to use the Excel if function to test conditions, combine with and/or logic, and nest tests with examples like release year after 2010 and duration over 120 minutes.

Learn to use sumifs and countifs to sum or count a range with multiple criteria, such as movie type and release year, including 2020 and 2021.

Explore using unique, left, and right to surface distinct values and extract starting or ending characters, handle messy data, and preview durations and years before learning Vlookup.

Master Vlookup in Excel to retrieve a value from a table using a lookup value, table array, column index, and a false range lookup. See IDs map to titles.

Learn to use Xlookup in Excel to retrieve a description by show ID from another table, replacing Vlookup, and apply the formula across rows.

Split each sheet into its own workbook and save as csv files for import into MySQL Workbench. Replace all blank cells with the word null to ensure clean data imports.

He demonstrates importing six spreadsheets into MySQL Workbench by creating a Netflix data schema, using the table data import wizard, addressing encoding issues with notepad, and refreshing tables after import.

Download Power BI and connect to data, loading Netflix data from MySQL. Build a titles table with seven columns and learn the data connection workflow.

Create an area chart from Netflix data by counting show IDs from the date added field, convert it to year, and format colors, legend, and borders.

Create a map of countries available by setting country as location and using show id as bubble size; apply a dark theme, color bubbles red, and import the Netflix logo.

Build a single title view by converting visuals to rating and release year cards, resizing elements, and adding a single-select year slicer to filter the movie dataset.

Analyze 2004 sales by product using SQL joins across orders, order details, customers, and products, showing sales value, cost of sales, and net profit by city and country.

Create a country sales overview in Excel by building a pivot table, showing sales and net profit per country and the net profit percentage on a secondary axis.

Learn to analyze customer sales by credit limit using MySQL, joining orders, order details, and customers to compute sales value and group by credit limit for pivot table insights.

Explore office sales by customer country through SQL joins of orders, customers, employees, and offices, then aggregate by product line and visualize with Excel pivot tables and charts.

Identify orders affected by late shipping by adding three days to ship dates, create a late flag with a case statement, and filter results to show only delayed shipments.

Compute running totals of payments by customer using CTEs and lead to derive next order dates, then join with sales totals to calculate money owed and identify over limit cases.

Assign ChatGPT a data analyst role to write SQL queries and extract insights. Provide context with customer fields and use few-shot prompting.

Use ChatGPT to extract insights by product, country, city, detailing sales value, cost of sales, and net profit for 2004. Learn prompt design, table schemas, and SQL-based validation of results.

Demonstrate building intermediate and advanced SQL queries with ChatGPT in MySQL, including lag over partition by to show customer sales, previous sale value, differences, and money owed with payments.

Leverage ChatGPT to generate MySQL case statements that group order comments into comment types like shipping or disputes. The video demonstrates prompts, syntax fixes, and mapping to an orders table.

Create a view in MySQL Workbench by joining orders, order details, customers, products, employees, and offices; compute sales value and cost of sales; connect view to Power BI for dashboard.

Connect Power BI to a MySQL data source, load the Salesdata view, and prepare the report canvas with a dark theme, two-page layout, and styled shapes.

Create three Power BI dashboard cards showing total sales, a two-segment line chart by rest of world vs USA, and count of unique orders, with an average order value measure.

Create overview line charts showing monthly sales by office (USA vs rest of world), with three panels for total sales, count of unique orders, and average order value.

Learn to build interactive Power BI dashboards by linking buttons to bookmarks, adding slicers, and displaying a selected metric in a clustered bar chart with currency data labels.

Create and compare charts by duplicating visuals and switching to scatter, column, and donut types to analyze cost of sale, net profit, and country-level performance across orders.

Learn to conditionally format chart titles to show sales or net profit via a control. Set subtitles, axis titles, and styling to build a clear, interactive dashboard.

Create a decomposition tree to analyze net profit by country, product line, and customer name, format the visual to match the dashboard, and place it on the sales overview sheet.

Add a Power BI sales overview table showing year, month, sales value, month-over-month change, and year-to-date total, with quick measures, formatting, and data cleaning steps.

Explore cloud computing and Snowflake, a cloud-based data warehouse for analytics. Set up a Snowflake account, run SQL queries in the browser, and compare cloud databases with local MySQL Workbench.

Plan data ingestion into Snowflake by choosing batch or streaming based on frequency and size. Stage data internally or externally and load with bulk loading or Snowpipe for real-time access.

Create a Snowflake stage for the customer details schema, enable client-side encryption, upload json, parquet, and csv files, and load the csv into a table named customer csv.

Learn how to load json and parquet data into Snowflake from stages, create and modify tables, cast date fields, and ensure correct column mapping with case-insensitive matching.

Set up an Azure blob storage account, create a storage account and a data container, upload CSV files, and prepare a geo-redundant external storage connection to Snowflake.

Explore data manipulation in Snowflake by inserting, updating, deleting, and truncating records in a customers CSV table, and verify changes with sorting and targeted updates.

Analyze a Disneyland review dataset across California, Hong Kong, and Paris to extract sentiment and summarize themes. Load the data into Snowflake and apply Cortex to derive actionable insights.

Translate multilingual reviews to English with Snowflake Cortex translate, auto-detect input language, and store results in a new review English column in an enriched guest reviews table for sentiment scoring.

Understand the Snowflake Cortex sentiment function, which outputs a float between -1 and 1 by analyzing a text column to reveal review sentiment and enable threshold-based filtering.

Discover how ai_agg aggregates a text column to extract insights, returning the six most common categories from review text using a simple prompt, with options to filter by sentiment.

Explore text classification in Snowflake cortex by defining categories, classifying reviews into single or multiple labels, and extracting labeled outputs from JSON results.

Explore Snowflake's ai_sentiment function to obtain both overall and per-category sentiment for reviews, returning positive, negative, neutral, mixed, or unknown, with up to 10 aspects.

Learn to unpack a JSON categories array in Snowflake with lateral flatten, producing one row per category and sentiment per review for easy grouping and counting.

Generate a Streamlit app inside Snowflake using Cortex code to visualize analysis outputs, turning natural language into Python and SQL with governance-aware context.

Build a Streamlit app from a Python script, run it to visualize sentiment insights with adjustable charts, and use Cortex AI to fix code and tune visuals.