MariaDB TOC

Duration 2 days

 

Course Contents - DAY 1

Course Introduction

Session 1: DATABASE CONCEPTS

• What is a database?
• Database management systems
• Tables, rows and columns
• Indexes, primary keys, unique constraints and foreign keys
• Client-Server architecture
• Supported data types
• Storage engines and table types
• The Information_Schema and MySQL Databases for MariaDB
• Exercise: Using a MariaDB Database

Session 2: USING THE MySQL CLIENT with MariaDB

• What is the MySQL client?
• Getting started and Logging in
• Selecting a database
• Client commands
• Entering and executing SQL statements
• Retrieving previous commands
• Creating, editing and executing SQL files
• Redirecting output into a file
• Command line execution of SQL Scripts
• Exercise: Using the MySQL Client

Session 3: BASIC SELECT

• The SQL SELECT statement
• Case sensitivity
• Quotes
• Statement terminator
• Syntax conventions
• The select clause
• The FROM clause
• Conditions and the WHERE clause
• Logical operators
• The ORDER BY clause
• Column aliases
• Arithmetic expressions
• Precedence of operators
• Exercises: Querying the sample database

Session 4: 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

 

Course Contents - DAY 2

Session 5: 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 Joins

Session 6: NUMERIC AND CHARACTER FUNCTIONS

• Function types
• Testing functions
• Numeric functions
• Character functions
• Exercise: Using Numeric and Character Functions

Session 7: 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

Session 8: DATABASES AND TABLES

• Creating a database
• Selecting a database
• Obtaining database metadata
• Table properties
• Creating tables
• Autoincrement
• Show create table
• Column operations
• Constraint operations
• Copying tables
• Renaming tables
• Changing engine for tables
• Obtaining table metadata
• Altering tables
• Dropping tables
• Temporary tables
• Memory tables
• Exercises: Maintaining databases and tables

Session 9: INDEXES AND VIEWS

• What is an index?
• Structure of an index
• Creating an index
• Reviewing indexes
• Composite indexes
• Altering a table to add an index
• Dropping indexes
• What is a view?
• Creating views
• View restrictions
• Dropping views
• Exercise: Maintaining indexes and views