SQL Essentials with MariaDB

Module 1: Basic select

• the SQL select statement
• case sensitivity
• quotes
• statement terminator
• syntax conventions
• the select clause
• the from clause
• conditions and where clause
• logical operators
• the order by clause
• column aliases
• arithmetic expressions
• precedence of operators
• exercises: querying the sample database
• limit, union and aggregate functions
• the limit clause
• union
• union all
• aggregate functions
• the group by clause
• using rollup with group by
• the having clause
• exercises: using limit, union and aggregate functions

Module 2: Limit, union and aggregate functions

• the limit clause
• union
• union all
• aggregate functions
• the group by clause
• using rollup with group by
• the having clause
• exercises: using limit, union and aggregate functions

Module 3: Subqueries and joins

• subqueries
• cartesian products
• joins with original syntax
• table aliases
• natural joins
• join using
• join on
• multi-table joins
• exercises: using subqueries and join

Module 4: Numeric and character functions

• function types
• testing functions
• numeric functions
• character functions
• exercise: using numeric and character functions

Module 5: Date, time and other functions

• date and time column types
• date and time formats
• the date format function
• functions to return date time
• functions to extract components from date time
• date time arithmetic
• miscellaneous functions
• exercise: using date, time and other functions

Module 6: Databases and tables

• creating a database
• selecting a database
• creating tables
• auto_increment
• show create table
• column operations
• constraint operations
• copying tables
• renaming tables
• changing engine for tables
• dropping tables
• temporary tables
• exercises: maintaining databases and tables

Module 7: Indexes and views

• What is an index?
• creating an index
• reviewing indexes
• dropping indexes
• What is a view?
• creating views
• view restrictions
• dropping views
• exercise: maintaining indexes and views

Module 8: Managing data

• inserting rows
• replacing rows
• updating rows
• deleting rows
• the truncate statement
• COMMIT and ROLLBACK commands
• savepoints
• implicit commits
• exercise: managing data