The Comprehensive SQL Course (MySQL)

Explore data query language basics in SQL with the select statement, learning how to extract information from database tables, filter results, aggregate data, and join across tables.

Learn how to use the select statement to retrieve data from the country schema’s regions table, using dot notation and semicolons, and interpret results in the grid.

Qualify the table references with dot notation, or switch the schema with use schema to access the regions table in the country schema, ensuring queries run in the correct context.

A statement covers all SQL commands, including data manipulation, data deletion, and data definition. A query is a specific type of statement, namely the select statement used to retrieve data.

Learn to use select star to retrieve all columns from a table, and understand why combining star with explicit columns may be less efficient.

Alias column names in sql query results using as or whitespace, with examples from the countries table to create readable aliases like country_name and capital_city, while data remains unchanged.

Alias table names and qualify columns with dot notation to avoid ambiguity. Learn the execution order and how aliasing affects queries, especially with multiple tables.

Learn to add single-line and multi-line comments in SQL using -- and /* */ to document queries, tables, and columns without affecting execution.

Learn to use select distinct in SQL to retrieve unique records and eliminate duplicates. See how single-column and multi-column queries, including region_id, yield distinct values and combinations.

Sort results with the order by clause in MySQL, specifying columns and asc or desc. Learn default ascending order, multi-column sorting, and how data types affect order.

Learn how to use the limit clause in MySQL to restrict query results, including optional offset and row counts, with practical examples of limit, offset, and their effects.

Understand the SQL engine's order of execution from the from clause through the limit clause, and learn to reference aliased columns in order by for correct results.

Compare syntax differences across MySQL, Oracle, MS SQL, and PostgreSQL, focusing on the limit clause and alternatives like top in T-SQL, and fetch first with offset in Oracle.

Explore MySQL data types, including numerical, character string, and date and time types, and learn how assigning a data type to a column ensures data integrity and efficient storage.

Explore numeric data types in MySQL, including exact fixed-point types such as integers and decimal, with precision and scale, plus approximate types like float and double.

Learn how char and varchar work in MySQL, covering fixed-length storage, padding, and truncation, and decide when to use each for fixed vs variable length data like names and descriptions.

Explore MySQL date and time data types, learn the standard formats for date (YYYY-MM-DD), time (HH:MM:SS), and year, and see how MySQL converts nonstandard inputs to these formats.

Discover how to use the describe statement in MySQL to retrieve metadata about a table, including column names and data types, and understand schema qualification and differences across management systems.

Explore how data types differ across database management systems, including MySQL, SQL Server, PostgreSQL, and Oracle, with terminology and syntax variations and practical workarounds for booleans using integers.

Explore data definition language and data manipulation language in MySQL, defining structures with create, alter, drop, truncate, and manipulating data with insert, update, and delete through hands-on practice.

Create and drop tables in SQL with schema-qualified names, using if not exists and if exists to avoid errors, define columns with data types, and terminate statements with a semicolon.

Create schemas and tables with create schema and create table, then drop schemas or databases with drop schema or drop database, noting that schema and database are synonymous and if exists avoids errors.

Learn how to use insert into to add records to MySQL tables, including schema and table creation, column ordering, quotes, date formats, nulls, and data types.

Learn how to insert multiple rows in MySQL by using multiple insert into statements and insert into select across tables with matching data types and column orders.

Learn how the create table as (ctas) statement copies structure and data from a source table or select query to create a new table, with examples and practical variations.

Truncate table deletes all records inside a table while preserving its structure, unlike drop table which removes the table entirely.

Modify and manage MySQL tables with the alter table statement, adding, dropping, and renaming columns, changing data types, and renaming tables using practical examples.

Update and delete rows using update and delete from statements, guided by a where clause. Explore a temp_schema.emp table showing id-based updates and multi-column changes.

Apply constraints to enforce data integrity for tables, including unique constraints, learn to add or modify them with create table and alter table statements, preventing violations of pre-existing data.

Learn how the not null constraint enforces non-null values by creating a temp_schema.constraints_demo table with a not null integer column and a nullable varchar column, inserting data, and handling violations.

Explore how a unique constraint enforces distinct non-null values in a column, demonstrated on col_one in a constraints_demo table, and learn how not null prevents null entries.

Understand how the default constraint assigns values when inserts omit columns, ensuring a value exists, as shown on a constraints_demo table with defaults for int and varchar.

Explore how a check constraint restricts a column to values greater than ten using the check expression, with a demo table and inserts that reject five but accept twelve.

Learn how to drop not null and default constraints in MySQL by using alter table to modify the column, omitting not null or default specification, and verify with describe table.

Master the MySQL syntax to drop unique and check constraints using alter table, drop constraint, and constraint names, with guidance from information_schema and show create table.

Explore the fundamental principles of designing relational databases, including normalization to minimize data redundancy, and the role of primary and foreign keys in linking tables.

Understand how normalization reduces redundancy in relational databases by splitting large tables into fact and dimension tables, using star and snowflake schemas in MySQL.

Explore how relational databases model data relationships, including 1-to-1, 1-to-many, and many-to-many, using common columns and junction tables to join information across tables.

discover how primary keys uniquely identify records and enforce not null constraints. understand how foreign keys enforce referential integrity by linking child and parent tables.

Demonstrate creating and enforcing primary and foreign keys in MySQL with practical table examples, constraint syntax, and insert validations using create, alter, and drop operations.

Explore entity relationship diagrams (ERDs) to visualize database tables, attributes, and relationships using crow's foot notation, including 1-to-1, 1-to-many, and 0-or-1 or 0-or-more relationships.

Prepare for upcoming sections by dropping the temp_underscore_schema. Use right-click to drop the temp_underscore_schema or type drop schema temp_underscore_schema to remove it.

Compare syntax for constraints across MySQL, SQL Server, Postgres, and Oracle, including primary keys, foreign keys, unique, not null, check, and default. Subtle differences exist; consult official docs.

Learn to build a two-table hr schema in MySQL with emp and dept, defining a one-to-many relationship between dept id and dept no, load hr data sql, and review results.

Explore the customer orders schema in MySQL, featuring five related tables—orders, order_items, customers, products, and stores—linked by key relationships, with the downloadable Customer Orders.sql file.

Create a new schema assignment_schema and a table movie_ratings with movie, genre, and rating columns. Add a unique constraint on the movie column to block duplicates.

Learn to enforce positive ratings in the assignment schema by adding a check constraint on the movie_ratings table, test it with a negative value, and drop the table when finished.

Copy emp and dept from hr schema to assignment schema using create table as select. Add primary key on dept_id and foreign key on deptno referencing dept_id; drop assignment schema.

Learn to use the where clause to filter your SQL queries and statements. Explore arithmetic, logical, and comparison operators to handle data filtering and expression evaluation in SQL.

Explore how the where clause filters rows by a condition across select, update, and delete statements, using operators to combine criteria like job equals salesman and salary over 1400.

Explore the where clause with comparison operators, comparing strings, numbers, and dates using quotes and proper formats, and learn how collation and data types affect results.

Explore how null values signal missing data in a countries table, distinguish null from empty or zero, and filter with where subregion_id is null to retrieve missing data records.

Apply arithmetic operators in MySQL expressions and follow the correct order of execution, including the where clause handling unaliased expressions and aliasing details like unit price and line item amount.

Explore logical operators in SQL, including and, or, between, in, and not, learn operator precedence and how to compose where clauses with salary ranges and job filters.

apply the like operator in the where clause of SQL to match patterns with % and _ wildcards, enabling substring searches and length-based filtering, as shown with country names.

Reinforces using the where clause with delete and update statements, demonstrates create table as select, and enforces a check constraint with in operator to restrict values.

Compare syntax differences across database management systems. Focus on case sensitivity and null and empty strings handling, noting MySQL is case-insensitive while PostgreSQL is case-sensitive by default.

Practice MySQL querying with assignment 1. Filter countries by population over 200 million and order by population; find commission earners in hr and order items by quantity and unit price.

Build a MySQL query on the countries table to filter by population between 100 and 200 million or area over 5,000,000 km², and require single-word country names.

Create a table in air schema named sales_people mirroring emp with total_pay as sal plus com; insert salesman records, update to senior salesman when total_pay exceeds 2000, drop table.