The SQL MicroDegree 2022: From SQL Basics To MySQL Mastery

The lecture explains the select statement, using from and order by to query the customers table, selecting all with the asterisk, and ending statements with a semicolon.

Explore the select clause to retrieve explicit columns from multiple tables, alias results with as, and use distinct with clear column naming using underscores or quotes.

Extract product names and unit prices from the products table, then create a new price column by multiplying unit price by two and display both columns.

Explore the where clause and comparison operators in SQL to filter data by numeric thresholds, strings, and dates, with proper quoting and exact column names.

Query the orders table to retrieve records with an order date on or after 2080-01-01, selecting all fields and applying a date filter in sql.

Combine multiple conditions using and, or, and not to filter customers by birth date, points, and state. Learn operator precedence, how parentheses alter evaluation, and how not negates a condition.

Explore querying the items table to compute total price by multiplying quantity by unit_price, then filter orders with a total greater than 30 to examine the results.

Learn how the in operator selects customers by state from a list of values, such as Virginia, Georgia, and Florida, and how not in excludes others.

Explore sql basics to mysql mastery by querying the products table and using the in operator to filter records with quantity in stock matching 49, 38, or 72.

Filter customers by a shopping points range using the between operator, showing that it yields the same result as separate range checks while improving readability.

Practice using the between operator to filter birth dates for all customers between January 1991 and January 2000 after selecting all customers, demonstrating date formatting in SQL.

Learn how the like operator filters customers by string patterns, using starts with, contains, and ends with patterns with the % and _ symbols, with case-insensitive matching.

Practice two SQL tasks: filter customers by address patterns using the like operator to find avenue occurrences, and select customers with phone numbers ending in nine.

Master the REGEXP operator to search last names using patterns beyond like, with beginning ^, end $, the pipe for or, and brackets and ranges for flexible matching.

Learn to use the regular expression operator to filter customers by last name patterns, such as starts with, contains, and ends with, and by first name options.

Identify missing data with the NULL operator in SQL and filter customers by phone number using where is null or not, highlighting those without a phone number.

Examine the orders table to identify not shipped records by inspecting ship date and ship variety, then write a filter using an sql operator to select missing values.

Master the order by clause to sort customers by first name and state in ascending or descending order, including birth date sorting regardless of selected columns.

Discover how the limit clause confines results to a specific count, use offsetting for pagination, and display three users per page across multiple pages.

Query the customers table, sort by shopping points in descending order, and apply a limit of three to identify the top three loyal customers.

Learn to join the orders and customers tables using inner join, on the customer id, alias tables for concise output, and display only order id, first name, and last name.

Learn how to join the items table with the products table using the product key and display each product’s name with the price the customer paid.

Join data from two databases by linking the items table in the school store with the products table from the school inventory, and use keyword with a semicolon correctly.

Explore self joins by linking the employees table to its own manager rows, returning employee id, first name, last name, and the manager's first name.

Join data from orders, customers, and order status tables to extract order id, date, customer names, and the status name (processed, shipped, delivered) in a consolidated view.

Learn to join the payments, clients, and payment method tables in the obscure invoicing database to display date, invoice, amount, client name, and payment mode.

Demonstrates using composite primary keys to identify records and build a compound join that matches order and product columns across the order items table and the other items table.

Demonstrates implicit join syntax and its equivalence to join on, and cautions that omitting the condition can yield unintended results, so always use join with on.

Understand how outer joins work in MySQL by comparing left and right joins to inner joins, showing how outer joins include nonmatching rows while preserving left-side data.

master outer joins to combine multiple tables, display all customers regardless of orders, and match ship names using a left join and join condition.

Practice building multi-table queries by joining customers, ships, and order statuses across three tables using inner and left joins to produce the required output.

Learn how the using clause simplifies join conditions when tables share identical column names, replacing verbose table.column comparisons with concise using syntax for clearer sql.

Perform multi-table joins on payments, clients, and payment_method to produce a concise result showing date, customer name, amount, and payment method name, using explicit join syntax and aliases.

Discover why natural joins in SQL and MySQL offer no control over join conditions and can yield incorrect results unless column names exactly match.

Learn how cross join combines every row from two tables to produce all possible product and customer combinations, with explicit and implicit syntax and practical examples like sizes and colors.

Demonstrate combining data from two tables using implicit and explicit cross join syntax, selecting and ordering by name, aliasing with Schippert and product names, and validating identical outputs.

Learn how to use union to combine records from the same or different tables, label records as active or archive by date, and manage aliases to produce a single result.

Categorize customers by points from the customers table into bronze, silver, and gold using aliases, union, and between, then order the results alphabetically.

Learn how column attributes define a customer table in design mode, including a primary key, data types like integer and varchar(50), and settings for not null, auto increment, and autofill.

Learn how to insert a new row into a table using insert into and values, specifying columns, using default values, and handling auto incremented primary keys.

Insert three products into the products table using insert into with the columns name, quantity_in_stock, and unit_price, while the primary key auto-increments.

Insert hierarchical rows across related tables by using insert into, last_insert_id, and referential integrity between the order and order_items tables in a relational database.

Learn to copy a table with create table as, duplicating all data from orders into a new table, and address the missing primary key and lack of auto increment.

Update a single row in SQL by updating the invoices table, setting payment total and payment date for a specific record, including arithmetic expressions like half of the invoice total.

Update multiple rows at once by applying a general condition, and disable safe updates in MySQL Workbench to permit these updates.

Learn how subqueries power precise data updates by using them to select client IDs, update multiple records safely, and identify null payment dates in invoices to drive targeted changes.

This exercise demonstrates updating the comments of customers with points greater than 3000 to the string gold via a nested query, focusing on customer_id for the update.

Master delete syntax with the delete keyword to remove all rows or target specific records using a where clause. Use a subquery to identify the target row, and proceed cautiously.

Master aggregate functions like max, min, average, sum, and count to summarize data, with examples using invoice totals and payment dates, and notes on null values and syntax.

Explore how aggregate functions handle arithmetic expressions in arguments, apply filters to data, and remove duplicates with distinct in real-world invoice data.

Group data by customer or by city and state to calculate aggregates with sum, then order by total sales and filter by invoice dates, using joins as needed.

apply the having clause to filter outputs after grouping, using aggregates like count and total to show only clients with totals and invoice counts above specified thresholds, including compound conditions.

Explore the rollup operator in SQL to sum values from aggregate results, generating subtotals by state and city and an overall total.

Restore databases to their original condition by running the provided script, then refresh and verify the databases in the navigation panel while practicing complex queries and sub queries.

Learn to write a subquery to find products priced higher than Legos, using an inner select to compare unit_price to the outer query on the products table.

Use the all operator to compare invoice totals against all values from a subquery, filtering invoices that exceed the maximum value for a specific client.

Explore correlated subqueries by computing each office's average salary and listing employees whose pay is below that office average, using the employees table in the school Hattab database.

Learn to write a subquery inside a select statement to calculate an invoice average and a difference column, handling aliases and avoiding repetition.

Explore numeric functions in SQL, including round, trunked, ceiling, floor, abs, and real function, with examples that show rounding, truncation, and generating a random decimal between 0 and 1.

Explore essential string functions in SQL, including length, upper, lower, ltrim, rtrim, trim, left, right, substring, locate, replace, and concat, with practical examples.

Explore sql date and time functions, from the null function printing current date and time to date or time outputs, and use extract and to_char with aliases.

Master SQL date and time formatting using the date formatting function, applying format specifiers to display year, month, and day, with guidance to consult online references for options.

Master date and time operations using date add to append days, months, or years to the date, and use date diff and time to set to compute seconds since midnight.

Ifnull and coalesce replace null values in queries by using replacement strings or values from other columns.

Learn to categorize orders with an if statement in SQL, marking orders after 2018-01-01 as active and earlier ones as archived, reducing code versus union, with a category alias.

Explore how to replace multiple if conditions with the case operator in SQL, labeling orders by year as current, active, or archived, and handling future cases.

Learn to create and use SQL views as reusable virtual tables, saving complex queries, querying and joining them, and understanding that a view stores code, not data.

Update or delete views using drop view and create or replace view, and save view scripts to folders for reuse alongside stored procedures.

Create a get_clients stored procedure in MySQL by wrapping a select from the clients table with a delimiter, then call it from Java or Python.

Create a MySQL stored procedure efficiently with a GUI, rename it, verify the table name, and let the tool manage delimiters while you apply and finish.

Discover how to drop a stored procedure, use if exists checks to delete or recreate it safely, and save or share the new procedure with your team.