Comprehensive MySQL Beginner to Advanced Study Guide

Comprehensive Introduction to MySQL Installation and Setup

  • The course is designed to transition a student from beginner to advanced SQL skills in under 44 hours.

  • SQL (Structured Query Language) is identified as one of the most critical skills in the data industry for querying, cleaning, and exploring datasets.

  • Installation Process for Windows:

    • Access the MySQL community downloads at dev.mysql.com/downloads/installer.

    • Choose the MySQL Installer Community (larger file) to avoid web-dependency during installation.

    • Use the Developer Default setup type to include necessary tools like MySQL Server and MySQL Workbench.

    • Requirements: Ensure the Microsoft Visual C++ Redistributable package is installed on the machine.

    • Configuration:

    • Default Port: 33063306.

    • Authentication: Use Strong Password Encryption.

    • Windows Service: Set the server to start automatically at system startup.

    • Sample Databases: The Sakila database is often included as a default sample for practice.

  • MySQL Workbench Interface:

    • Local Instance: The primary connection used for local development.

    • Query Editor: The central window where SQL code is written and executed.

    • Schemas Tab: Located on the left, showing databases, tables, views, and stored procedures.

    • Execution Buttons:

    • Lightning Bolt: Executes the entire script in the window.

    • Lightning Bolt with 'I' Cursor: Executes only the statement where the cursor is currently positioned.

The Basics of the SELECT Statement

  • The SELECT statement is used to specify which columns of data you want to retrieve from a table.

  • Select All: Use the asterisk symbol (*) to select every column in a table (e.g., SELECT * FROM table_name;).

  • Column Specificity: Specify individual column names separated by commas (e.g., SELECT first_name, last_name FROM employee_demographics;).

  • Mathematical Calculations:

    • Calculations can be performed directly within the SELECT statement.

    • MySQL follows the PEMDAS order of operations: Parentheses, Exponents, Multiplication, Division, Addition, Subtraction.

    • Example: SELECT age, (age + 10) * 10 FROM employee_demographics;.

  • DISTINCT Keyword: Used to return only unique values from a column, eliminating duplicates in the output (e.g., SELECT DISTINCT gender FROM employee_demographics;).

  • Comments in SQL:

    • Single-line comments use the hash symbol (#) or double dashes (--).

    • Multi-line comments are enclosed in /* ... */.

Filtering Data with the WHERE Clause

  • The WHERE clause filters rows based on specific conditions, returning only the records that meet the criteria.

  • Comparison Operators:

    • Equals: == (e.g., WHERE first_name = 'Leslie').

    • Greater Than / Less Than: >>, <<, ≥\ge, ≤\le.

    • Not Equal To: !=!= or <><>.

  • Logical Operators:

    • AND: Both conditions must be true.

    • OR: At least one condition must be true.

    • NOT: Negates a condition (e.g., WHERE NOT gender = 'Male').

  • The LIKE Statement: Used for pattern matching with wildcards.

    • Percent Sign (%): Matches any sequence of characters (including zero characters). Example: WHERE first_name LIKE 'A%' finds names starting with 'A'.

    • Underscore (_): Matches exactly one character. Example: WHERE first_name LIKE 'A__' finds three-letter names starting with 'A'.

Aggregating and Sorting Data

  • GROUP BY: Groups rows that have the same values into summary rows. It is frequently used with aggregate functions.

  • Aggregate Functions:

    • AVG()\text{AVG}(): Returns the average value of a numeric column.

    • MIN()\text{MIN}() / MAX()\text{MAX}(): Returns the smallest or largest value.

    • COUNT()\text{COUNT}(): Returns the number of rows.

    • Constraint: Every column in the SELECT statement that is not part of an aggregate function must be included in the GROUP BY clause.

  • ORDER BY: Sorts the resulting dataset.

    • ASC: Ascending order (default).

    • DESC: Descending order.

    • Multi-column sorting: ORDER BY gender, age DESC; sorts by gender first, then by age within those genders.

  • HAVING Clause: Used specifically to filter data after an aggregation has occurred. The WHERE clause cannot be used with aggregated columns.

Advanced Selection Tools: Limit and Aliasing

  • LIMIT: Specifies the maximum number of rows the query should return.

    • Example: LIMIT 3; returns the top 33 rows.

    • Offset example: LIMIT 2, 1; skips the first 22 rows and returns the next 11 row.

  • Aliasing (AS Keyword): Used to temporarily rename a column or table for the duration of the query.

    • Example: SELECT AVG(age) AS average_age FROM employee_demographics;.

    • Aliasing is critical for readability when using joins or complex calculations.

Joins: Combining Data from Multiple Tables

  • Joins allow for the combination of rows from two or more tables based on a related column between them (typically an ID).

  • INNER JOIN: Returns records that have matching values in both tables.

  • OUTER JOINS:

    • LEFT JOIN (or LEFT OUTER JOIN): Returns all records from the left table and matched records from the right table. If no match exists, NULLs are returned for the right table columns.

    • RIGHT JOIN (or RIGHT OUTER JOIN): Returns all records from the right table and matched records from the left table.

  • SELF JOIN: Joining a table to itself. Useful for hierarchical data or comparing rows within the same table (e.g., assigning a Secret Santa where emp1.id + 1 = emp2.id).

  • Joining Multiple Tables: You can chain joins (e.g., Table A to Table B, then Table B to Table C) as long as common keys exist at each step.

UNIONS: Combining Result Sets

  • A UNION operator is used to combine the result sets of two or more SELECT statements.

  • Conditions for Union:

    • Each SELECT statement must have the same number of columns.

    • The columns must have similar data types.

    • The columns in each SELECT statement must be in the same order.

  • UNION vs. UNION ALL:

    • UNION: Returns only distinct values (removes duplicates).

    • UNION ALL: Returns all values, including duplicates.

Comprehensive String Functions

  • Length: LENGTH(column) returns the number of characters in a string.

  • Casing: UPPER(column) and LOWER(column) convert text to all uppercase or lowercase.

  • Trim: TRIM(), LTRIM(), and RTRIM() remove leading and trailing whitespace. Use TRIM(TRAILING '.' FROM column) to remove specific trailing characters.

  • Substring: SUBSTRING(column, start, length) extracts a portion of a string.

    • Example: SUBSTRING('2023-01-01', 6, 2) extracts '01' (the month).

  • Left and Right: LEFT(column, n) and RIGHT(column, n) extract 'n' characters from the start or end.

  • Replace: REPLACE(column, 'target', 'replacement') swaps specific characters.

  • Locate: LOCATE('search_string', column) returns the position of a substring within a string.

  • Concat: CONCAT(col1, ' ', col2) joins two or more strings into one.

Logical Control with CASE Statements

  • The CASE statement acts like IF-THEN-ELSE logic.

  • Syntax:sql SELECT first_name, CASE WHEN age <= 30 THEN 'Young' WHEN age BETWEEN 31 AND 50 THEN 'Old' ELSE 'On Death Door' END AS age_bracket FROM employee_demographics; &nbsp;

  • Use cases include categorizing data, calculating tiered bonuses, or flagging specific records.

Subqueries and Common Table Expressions (CTEs)

  • Subqueries: A query nested within another query.

    • Can be used in SELECT, FROM, or WHERE clauses.

    • Rule: Subqueries in a SELECT or WHERE clause usually must return only one column if using comparison operators.

  • CTEs (Common Table Expressions): Defined using the WITH keyword.

    • CTEs improve readability and allow you to name a subquery block for reference in the main query.

    • CTEs are not stored in memory and must be used immediately after definition.

    • Example: WITH my_cte AS (SELECT ...) SELECT * FROM my_cte;.

  • Multiple CTEs: Multiple blocks can be defined in one WITH statement separated by commas.

Database Objects: Temp Tables and Stored Procedures

  • Temporary Tables:

    • Created using CREATE TEMPORARY TABLE table_name (...).

    • These tables only exist for the duration of the current session.

    • Highly useful for storing intermediate results in complex multi-step transformations.

  • Stored Procedures:

    • Block of code that can be saved and reused.

    • Delimiters: Because procedures contain semicolons, you must change the delimiter (e.g., to ``) to define the procedure, then change it back to a semicolon.\n - **Parameters:** Procedures can accept inputs (`IN` parameters) to make them dynamic. \n - Call a procedure using `CALL procedure_name(parameter_value);`.\n\n# Advanced Automation: Triggers and Events\n\n- **Triggers:** A block of code that automatically executes when specific data changes occur (`INSERT`, `UPDATE`, or `DELETE`) on a table.\n - Keywords: `NEW` (refers to the row being added/updated) and `OLD` (refers to the row being removed/updated).\n- **Events:** A task that runs on a schedule (similar to a cron job).\n - Scheduled frequencies can be `EVERY 1 MONTH`, `EVERY 30 SECOND`, etc.\n - Requires the global `event_scheduler` variable to be set to `ON`.\n\n# Project 1: Data Cleaning Methodology\n\n1. **Data Staging:** Always work on a copy of the raw data (e.g., `layoffs_staging`) to preserve the original source.\n2. **Identify Duplicates:** Use `ROW_NUMBER() OVER(PARTITION BY company, location, industry, ...)`. Any row with a number `> 1` is a duplicate.\n3. **Standardizing Strings:**\n - Use `TRIM()` on companies.\n - Use `LIKE` to consolidate similar industry names (e.g., 'Crypto', 'Cryptocurrency').\n - Remove trailing formatting issues like periods at the end of country names.\n4. **Date Formatting:** Use `STR_TO_DATE(column, '%m/%d/%Y')` to convert text dates into the ISO `YYYY-MM-DD` format, then `ALTER TABLE` to change the column type to `DATE`.\n5. **Null Handling:** \n - Populate missing industry data by joining the table to itself on `company` and `location` to find non-null matches.\n - Delete rows that lack critical data (e.g., both `total_laid_off` and `percentage_laid_off` are NULL) if they are unusable for analysis.\n\n# Project 2: Exploratory Data Analysis (EDA)\n\n- **Initial Exploration:** Finding the `MAX()` and `MIN()` of layoffs and funding.\n- **Aggregated Insights:** Summing layoffs by company, industry, country, and year.\n- **Rolling Totals:** Calculated using `SUM(total_off) OVER(ORDER BY month_column)` to see the cumulative progression of layoffs over time.\n- **Company Rankings by Year:** \n - Step 1: Create a CTE to sum layoffs per company per year.\n - Step 2: Create a second CTE using `DENSE_RANK() OVER(PARTITION BY year ORDER BY sum_laid_off DESC)`.\n - Step 3: Filter for `ranking <= 5` to identify the top 5 companies with the highest layoffs for every year in the dataset.

Questions & Discussion

  • Q: Why use a staging table?

  • A: It is a best practice to avoid modifying original raw data. If a transformation error occurs, the original data remains safe.

  • Q: What is the difference between RANK and DENSE_RANK?

  • A: RANK skips positions if there is a tie (e.g., 1, 2, 2, 4), while DENSE_RANK does not skip positions (e.g., 1, 2, 2, 3).

  • Q: When should I use a CTE vs. a Temp Table?

  • A: Use CTEs for readability in single queries. Use Temp Tables if you need to reference the subset of data multiple times throughout a complex script or stored procedure.


  • The course is designed to transition a student from beginner to advanced SQL skills in under 4hours,equippingthemwiththeessentialknowledgetothriveinadata−drivenenvironment.</p></li><li><p>SQL(StructuredQueryLanguage)isrecognizedasoneofthemostcriticalskillsinthedataindustryforquerying,cleaning,andexploringdatasets,makingproficiencyinSQLinvaluableforcareersindataanalysis,databasemanagement,andsoftwaredevelopment.</p></li><li><p><strong>InstallationProcessforWindows:</strong></p><ul><li><p>AccesstheMySQLcommunitydownloadsat<code>dev.mysql.com/downloads/installer</code>toensureyouhavethelatestversion.</p></li><li><p>Choosethe<strong>MySQLInstallerCommunity</strong>option,whichincludesalargerfilesizetoavoidanyweb−dependencyissuesduringinstallation.Thisensuresasmoothersetupprocess.</p></li><li><p>Usethe<strong>DeveloperDefault</strong>setuptypetoautomaticallyincludeallnecessarytools,suchastheMySQLServerandMySQLWorkbench,streamliningyourinitialenvironmentsetup.</p></li><li><p><strong>Requirements:</strong>Confirmthatthe<strong>MicrosoftVisualC++Redistributable</strong>packageisinstalledonyourmachine,asit′scrucialforrunningMySQLapplicationsproperly.</p></li><li><p><strong>Configuration:</strong></p><ul><li><p>DefaultPort:Settohours, equipping them with the essential knowledge to thrive in a data-driven environment.</p></li><li><p>SQL (Structured Query Language) is recognized as one of the most critical skills in the data industry for querying, cleaning, and exploring datasets, making proficiency in SQL invaluable for careers in data analysis, database management, and software development.</p></li><li><p><strong>Installation Process for Windows:</strong></p><ul><li><p>Access the MySQL community downloads at <code>dev.mysql.com/downloads/installer</code> to ensure you have the latest version.</p></li><li><p>Choose the <strong>MySQL Installer Community</strong> option, which includes a larger file size to avoid any web-dependency issues during installation. This ensures a smoother setup process.</p></li><li><p>Use the <strong>Developer Default</strong> setup type to automatically include all necessary tools, such as the MySQL Server and MySQL Workbench, streamlining your initial environment setup.</p></li><li><p><strong>Requirements:</strong> Confirm that the <strong>Microsoft Visual C++ Redistributable</strong> package is installed on your machine, as it's crucial for running MySQL applications properly.</p></li><li><p><strong>Configuration:</strong> </p><ul><li><p>Default Port: Set to3306, which is standard for MySQL operations.

  • Authentication: Opt for Strong Password Encryption for enhanced security during user authentication.

  • Windows Service: Configure the MySQL server to start automatically at system startup for seamless operation.

  • Sample Databases: The Sakila database is often included as a default sample for practice, providing real-world illustrations of the important concepts.

  • MySQL Workbench Interface:

    • Local Instance: The primary connection utilized for local development, allowing you to manage the database from your machine.

    • Query Editor: The principal window where SQL code is crafted and executed, providing a user-friendly environment for beginners and advanced users alike.

    • Schemas Tab: Located on the left side of the interface, showing databases, tables, views, and stored procedures to help with organization and accessibility of database components.

    • Execution Buttons:

      • Lightning Bolt: Executes the entire script in the window, allowing for batch operations.

      • Lightning Bolt with 'I' Cursor: Executes only the SQL statement where the cursor is currently positioned, giving finer control over query execution.

  • The Basics of the SELECT Statement

    • The SELECT statement is foundational for retrieving data from relational databases, and understanding its syntax and functionality is essential for effective SQL querying.

    • Select All: Use the asterisk symbol (*) to select every column in a table (e.g., SELECT * FROM table_name;) for quick data retrieval.

    • Column Specificity: Specify individual column names separated by commas (e.g., SELECT first_name, last_name FROM employee_demographics;) to retrieve only the necessary information, enhancing performance.

    • Mathematical Calculations: Calculations can be performed directly within the SELECT statement:

      • MySQL adheres to the PEMDAS order of operations: Parentheses, Exponents, Multiplication, Division, Addition, Subtraction, critical for accurate computation.

      • Example: SELECT age, (age + 10) * 10 FROM employee_demographics; to demonstrate practical use.

    • DISTINCT Keyword: Essential for retrieving unique values from a column, eliminating duplicates in the output (e.g., SELECT DISTINCT gender FROM employee_demographics;).

    • Comments in SQL:

      • Single-line comments utilize the hash symbol (#) or double dashes (--).

      • Multi-line comments are designated with /* ... */, allowing for thorough documentation within SQL scripts.

    Filtering Data with the WHERE Clause

    • The WHERE clause is critical for filtering rows based on specific conditions, ensuring the returned dataset only includes records that meet the defined criteria.

    • Comparison Operators: Essential for executing conditions:

      • Equals: =</code>(e.g.,<code>WHEREfirstname=′Leslie′</code>).</p></li><li><p>GreaterThan/LessThan:<code></code> (e.g., <code>WHERE first_name = 'Leslie'</code>).</p></li><li><p>Greater Than / Less Than: <code>></code>,<code></code>, <code><</code>,<code></code>, <code>\text{>=}</code>,<code></code>, <code>\text{<=}</code>.</p></li><li><p>NotEqualTo:<code></code>.</p></li><li><p>Not Equal To: <code>\neq</code>or<code></code> or <code><></code>.</p></li></ul></li><li><p><strong>LogicalOperators:</strong></p><ul><li><p><strong>AND:</strong>Ensuresbothconditionsmustbetrueforamatchtooccur.</p></li><li><p><strong>OR:</strong>Atleastoneoftheconditionsmustbetruetoreturnarecord.</p></li><li><p><strong>NOT:</strong>Negatesacondition(e.g.,<code>WHERENOTgender=′Male′</code>).</p></li></ul></li><li><p><strong>TheLIKEStatement:</strong>Thisisusedforpatternmatchingwithwildcards:</p><ul><li><p>PercentSign(<code></code>.</p></li></ul></li><li><p><strong>Logical Operators:</strong></p><ul><li><p><strong>AND:</strong> Ensures both conditions must be true for a match to occur.</p></li><li><p><strong>OR:</strong> At least one of the conditions must be true to return a record.</p></li><li><p><strong>NOT:</strong> Negates a condition (e.g., <code>WHERE NOT gender = 'Male'</code>).</p></li></ul></li><li><p><strong>The LIKE Statement:</strong> This is used for pattern matching with wildcards:</p><ul><li><p>Percent Sign (<code>%</code>): Matches any sequence of characters (including zero characters). Example: <code>WHERE first_name LIKE 'A%'</code> identifies names beginning with 'A'.</p></li><li><p>Underscore (<code>_</code>): Matches exactly one character. Example: <code>WHERE first_name LIKE 'A__'</code> finds three-letter names starting with 'A'.</p></li></ul></li></ul><h4 id="35245d44-f0b4-49b6-9f41-bea3a8264e7a" data-toc-id="35245d44-f0b4-49b6-9f41-bea3a8264e7a" collapsed="false" seolevelmigrated="true">Aggregating and Sorting Data</h4><ul><li><p><strong>GROUP BY:</strong> A powerful tool that groups rows sharing the same values into summary rows, frequently utilized with aggregate functions for in-depth analysis of datasets.</p></li><li><p><strong>Aggregate Functions:</strong></p><ul><li><p><code>\text{AVG}()</code>:Computestheaveragevaluewithinanumericcolumn,helpingsummarizedatasetsefficiently.</p></li><li><p><code></code>: Computes the average value within a numeric column, helping summarize datasets efficiently.</p></li><li><p><code>\text{MIN}()</code>/<code></code> / <code>\text{MAX}()</code>:Identifythesmallestorlargestvalueswithinaset,respectively.</p></li><li><p><code></code>: Identify the smallest or largest values within a set, respectively.</p></li><li><p><code>\text{COUNT}()</code>:Returnsthetotalnumberofrows,crucialforunderstandingdatasetsize.</p></li><li><p><strong>Constraint:</strong>Everycolumninthe<code>SELECT</code>statementthatisnotpartofanaggregatefunction<strong>must</strong>beincludedinthe<code>GROUPBY</code>clausetoavoiderrors.</p></li></ul></li><li><p><strong>ORDERBY:</strong>Thissortstheresultingdatasettoenhancedatainterpretation:</p><ul><li><p><code>ASC</code>:Ascendingorder(default),organizesvaluesfromsmallesttolargest.</p></li><li><p><code>DESC</code>:Descendingorder,organizesvaluesfromlargesttosmallest.</p></li><li><p>Multi−columnsortingenablescomplexdataarrangements(e.g.,<code>ORDERBYgender,ageDESC;</code>sortsfirstbygender,thenbyagewithinthosegendergroups).</p></li></ul></li><li><p><strong>HAVINGClause:</strong>Uniquetofilteringdata<strong>after</strong>aggregation,allowingtargetedinsightsfromsummarizeddata(unlike<code>WHERE</code>,whichcannotbeusedwithaggregatedcolumns).</p></li></ul><h4id="f9e0d3f2−6a61−4822−98fa−f5a505faf360"data−toc−id="f9e0d3f2−6a61−4822−98fa−f5a505faf360"collapsed="false"seolevelmigrated="true">AdvancedSelectionTools:LimitandAliasing</h4><ul><li><p><strong>LIMIT:</strong>Specifiesthemaximumnumberofrowsthequeryshouldreturn,enhancingefficiencywhenworkingwithlargedatasets:</p><ul><li><p>Example:<code>LIMIT3;</code>returnsthetop</code>: Returns the total number of rows, crucial for understanding dataset size.</p></li><li><p><strong>Constraint:</strong> Every column in the <code>SELECT</code> statement that is not part of an aggregate function <strong>must</strong> be included in the <code>GROUP BY</code> clause to avoid errors.</p></li></ul></li><li><p><strong>ORDER BY:</strong> This sorts the resulting dataset to enhance data interpretation:</p><ul><li><p><code>ASC</code>: Ascending order (default), organizes values from smallest to largest.</p></li><li><p><code>DESC</code>: Descending order, organizes values from largest to smallest.</p></li><li><p>Multi-column sorting enables complex data arrangements (e.g., <code>ORDER BY gender, age DESC;</code> sorts first by gender, then by age within those gender groups).</p></li></ul></li><li><p><strong>HAVING Clause:</strong> Unique to filtering data <strong>after</strong> aggregation, allowing targeted insights from summarized data (unlike <code>WHERE</code>, which cannot be used with aggregated columns).</p></li></ul><h4 id="f9e0d3f2-6a61-4822-98fa-f5a505faf360" data-toc-id="f9e0d3f2-6a61-4822-98fa-f5a505faf360" collapsed="false" seolevelmigrated="true">Advanced Selection Tools: Limit and Aliasing</h4><ul><li><p><strong>LIMIT:</strong> Specifies the maximum number of rows the query should return, enhancing efficiency when working with large datasets:</p><ul><li><p>Example: <code>LIMIT 3;</code> returns the top3rows.</p></li><li><p>Offsetexamplesprovidefurthercontrol:<code>LIMIT2,1;</code>skipsthefirstrows.</p></li><li><p>Offset examples provide further control: <code>LIMIT 2, 1;</code> skips the first2rowsandreturnsthesubsequentrows and returns the subsequent1 row.

    • Aliasing (AS Keyword): Temporarily renames a column or table for improved clarity in query results:

      • Example: SELECT AVG(age) AS average_age FROM employee_demographics;, simplifies interpretation of results for presentation.

    Joins: Combining Data from Multiple Tables

    • Joins are integral to relational databases, enabling the combination of rows from two or more tables based on shared columns (usually IDs).

    • INNER JOIN: Returns records that possess matching values in both tables, making it essential for fetching relevant datasets.

    • OUTER JOINS:

      • LEFT JOIN (or LEFT OUTER JOIN): Returns all records from the left table and matched records from the right; if no match exists, NULLs are returned for non-matching columns from the right table.

      • RIGHT JOIN (or RIGHT OUTER JOIN): Analogous to the left join, but the focus is on all records from the right table instead.

    • SELF JOIN: When a table joins itself, permitting comparisons of rows within a single table. Useful for hierarchical data or generating comparative analyses (e.g., assigning a Secret Santa where emp1.id + 1 = emp2.id).

    • Joining Multiple Tables: You can chain joins (e.g., Table A to Table B, then Table B to Table C) ensuring common keys exist at each step for successful data retrieval.

    UNIONS: Combining Result Sets

    • A UNION operator is used to amalgamate the result sets of two or more SELECT statements, optimizing storage of simultaneous results.

    • Conditions for Union:

      • Each SELECT statement must possess the same number of columns.

      • The columns must maintain similar data types to ensure compatibility.

      • The columns in each SELECT statement must appear in the same order, fostering consistency in data presentation.

    • UNION vs. UNION ALL:

      • UNION: Returns only distinct values, effectively eliminating duplicates, enhancing clarity in results.

      • UNION ALL: Returns all values, including duplicates, which can serve specific analytical needs.

    Comprehensive String Functions

    • Length: LENGTH(column) retrieves the number of characters within a string, critical for data validation.

    • Casing: UPPER(column) and LOWER(column) transform text data to all uppercase or lowercase, aiding in uniformity during comparisons.

    • Trim: TRIM(), LTRIM(), and RTRIM() remove excess whitespace from strings. Use TRIM(TRAILING '.' FROM column) for removing specific unwanted characters.

    • Substring: SUBSTRING(column, start, length) extracts portions of a string, useful for targeted data queries:

      • Example: SUBSTRING('2023-01-01', 6, 2) extracts '01' (the month).

    • Left and Right: LEFT(column, n) and RIGHT(column, n) pull 'n' characters from the beginning or end of a string, enhancing data manipulation capabilities.

    • Replace: REPLACE(column, 'target', 'replacement') effectively exchanges specific characters in string datasets.

    • Locate: LOCATE('search_string', column) returns the position of a substring within a larger string, crucial for pattern recognition.

    • Concat: CONCAT(col1, ' ', col2) merges two or more strings into one, facilitating better data presentation.

    Logical Control with CASE Statements

    • The CASE statement functions similarly to IF-THEN-ELSE logic, enabling complex conditionals within SQL.

    • Syntax:SELECT first_name, CASE WHEN age <= 30 THEN 'Young' WHEN age BETWEEN 31 AND 50 THEN 'Old' ELSE 'On Death Door' END AS age_bracket FROM employee_demographics;

    • Use cases span categorizing data, calculating tiered bonuses, or flagging specific records, contributing significantly to data-driven decision-making.

    Subqueries and Common Table Expressions (CTEs)

    • Subqueries: Embedding a query within another query allows for more advanced data retrieval. They can serve multiple roles in SELECT, FROM, or WHERE clauses.

      • Rule: Subqueries in a SELECT or WHERE clause must typically return only one column when using comparison operators.

    • CTEs (Common Table Expressions): Defined utilizing the WITH keyword, CTEs enhance query readability and allow referencing of subqueries in the main query directly:

      • CTEs are ephemeral and must be used immediately after their definition, improving the structure of complex queries.

      • Example: WITH my_cte AS (SELECT ...) SELECT * FROM my_cte;.

    • Multiple CTEs: Multiple blocks can be defined within a single WITH statement, separated by commas, enabling layered data extraction.

    Database Objects: Temp Tables and Stored Procedures

    • Temporary Tables: Temporary tables can be created with CREATE TEMPORARY TABLE table_name (...), existing only for the duration of the session to facilitate interim data processing.

    • Stored Procedures: Comprising blocks of reusable code, stored procedures enable efficient database organization:

      • Delimiters: Must alter the delimiter (e.g., to ``) to define the procedure and reset it post-definition due to semicolon usage within procedures.\n - **Parameters:** Procedures can accept inputs (`IN` parameters) for dynamic functionality. Call a procedure via `CALL procedure_name(parameter_value);` for execution.\n\n## Advanced Automation: Triggers and Events\n\n- **Triggers:** An automated procedural block that executes upon specific data changes (`INSERT`, `UPDATE`, or `DELETE`) in a table, enhancing reactive database management.\n - Keywords include `NEW` (referring to the row being added/updated) and `OLD` (referring to the row being discarded/updated).\n- **Events:** A task scheduled to run periodically, similar to cron jobs:\n - Frequencies specify execution rates (e.g., `EVERY 1 MONTH`, `EVERY 30 SECOND`, etc.). A global variable, `event_scheduler`, must be set to `ON` for this to function.\n\n## Practical Projects\n\n### Project 1: Data Cleaning Methodology\n\n1. **Data Staging:** Always work on a copy of the raw data (e.g., `layoffs_staging`) to preserve the original source for integrity during processes.\n2. **Identify Duplicates:** Utilize `ROW_NUMBER() OVER(PARTITION BY company, location, industry, ...)`; any row with a number `>1> 1` signals a duplicate for handling.\n3. **Standardizing Strings:**\n - Apply `TRIM()` on company names.\n - Use `LIKE` to consolidate similar industry names (e.g., 'Crypto', 'Cryptocurrency') for uniformity.\n - Address trailing formatting issues like periods at the end of country names.\n4. **Date Formatting:** Apply `STR_TO_DATE(column, '%m/%d/%Y')` to convert string dates to the ISO `YYYY-MM-DD` format, followed by `ALTER TABLE` to adjust the column type.\n5. **Null Handling:**\n - Populate missing industry data by self-joining to find non-null matches based on `company` and `location`.\n - Eliminate rows lacking critical data (e.g., both `total_laid_off` and `percentage_laid_off` as NULL) if deemed unusable for analysis.\n\n### Project 2: Exploratory Data Analysis (EDA)\n\n- **Initial Exploration:** compute the `MAX()` and `MIN()` values of layoffs and funding for a comprehensive overview.\n- **Aggregated Insights:** Summarizing layoffs by company, industry, country, and year for high-level understandings of trends.\n- **Rolling Totals:** Utilize `SUM(total_off) OVER(ORDER BY month_column)` to capture cumulative layoffs over time for trend analysis.\n- **Company Rankings by Year:**\n - Step 1: Create a CTE to calculate total layoffs per company per year.\n - Step 2: Implement a second CTE using `DENSE_RANK() OVER(PARTITION BY year ORDER BY sum_laid_off DESC)` for ranking.\n - Step 3: Filter results to `ranking <= 5` to isolate the top 55 companies having the most significant layoffs each year.

    Questions & Discussion

    • Q: Why is it advised to use a staging table?

    • A: It's a best practice to preserve original raw data. This keeps the original data intact should any errors occur during transformation.

    • Q: What differentiates RANK from DENSE_RANK?

    • A: RANK skips positions when rank ties occur (e.g., 1, 2, 2, 4), while DENSE_RANK does not skip (e.g., 1, 2, 2, 3), each retaining continuous ranking.

    • Q: Under what circumstances should I opt for a CTE over a Temp Table?

    • A: Utilize CTEs for enhancing readability in single queries. Opt for Temp Tables if you need a subset of data referenced multiple times across a complex script or stored procedure.