CHAPTER 1 : INTRODUCTION TO RDBMS
- Features of RDBMS
- Advantages of RDBMS
- F Codd Rules for RDBMS
- Normalization Concepts & Process
- Client Server Communication
- Oracle Corporation Products
- RDBMS from Other Vendors: Microsoft, IBM,etc
- MySql Versions
- Database Types: OLTP and OLAP Databases
- Real-world Advantage of OLTP & OLAP
- Why a database is called as Relational Database Model
- Database Entites and Attributes
|
CHAPTER 9 : VIEWS, SYNONYMS,SEQUENCES
- VIEWS IN ORACLE, Understanding Views & Use
- Relational Views and Standard Views
- SIMPLE VIEWS and COMPLEX VIEWS in MYSQL
- Column Definitions in VIEWS
- Using VIEWS for DML Operations
- Forced Views, CHECK Constraints in Views
- Creation of READ ONLY VIEWS - Realtime Use
- Sequences, with Synonyms
- What is the Use of Synonym in Oracle?
- Difference between View and Synonym in mysql
- When should Table Synonyms be used?
- Why views are used in MySQL?
|
CHAPTER 17 :MYSQL DATABASE SETUP
- MySQL Download and Installation for Macs
- What is MySQL Workbench
- Installing MySQL Workbench for Windows
- Basic Database Concepts
- What is a Schema
- Database Schema
- MySQL Data Types
- What is SQL
- How to setup a MySQL database on Windows?
- How to create MySQL local database?
- How do I start MySQL for the first time?
- How do I find MySQL database schema?
- What are the 3 types of database schema?
|
CHAPTER 2 : MYSQL INSTALLATION
- MySql Installation Guidance
- MySql 8 Installation
- Installation Pre-Requisites and Precautions
- MySql Workbench 8.0
- My Sql Command prompt
- Mysql Versions and Editions Comparisions
- Connect Workbench Developer Tool
- Creating a new User
- Grant permission
- Lock and Unlock User Account
- How to Reset User Account Password
- How to Establish the MySql Connection
- Default System user in MySql
|
CHAPTER 10 : SUB QUERIES, NESTED QUERIES - 1
- Sub Queries in Real-world
- Dynamic Conditions with Sub Queries
- Sub Queries and Nested Sub Queries
- How does oracle Execute Nested Sub Queries?
- Inner Select and Outer Select Queries
- Usage of Sub Queries with WHERE, HAVING
- Impact of Having Clause in Sub Queries
- Select Nth Highest salary
- Select Duplicate Records
- Delete Duplicate Records
- Advantages of MySql Sub Query
- Subquery in the Select Clause Examples
- Subquery in the From Clause Examples
|
CHAPTER 18 : PERCONA SERVER
- Percona Overview; Percona Server Installation
- PerconaXtraBackup Installation;
- PerconaFeatures; Configuration of XtraBackup;
- Taking Full Backup; Taking Incremental
- Backups; Restoring / Recovering Full Backups
- and Incremental; Partial Backups;
- XtraBackup Installation on MySQL Server;
- How to install Percona MySQL server?
- Is percona MySQL free?
- Is percona better than MySQL?
- Is percona faster than MySQL?
- How to install percona xtrabackup in linux?
- What is Xtrabackup in mysql?
|
CHAPTER 3 : SQL LANGUAGE COMMANDS - 1
- Structured Query Language (SQL) Basics
- What is SQL?
- What Can SQL do?
- Possible Operations with SQL
- Data Definition Language (DDL)
- Data Retrieval Language (DRL)
- Data Manipulation Language (DML)
- Transaction Control Language (TCL)
- Database Security and Privileges (DCL)
- Rules of SQL Queries and Statements
- Real-world applicative uses of SQL
- Creating Users and Tables
|
CHAPTER 11 : MYSQL – (CONTROL STRUCTURE)
- Simple If, If..Else. Nested If..Else Statements
- Ladder, Selection, Simple Case Statements
- GOTO Label and EXIT Statements in MYSQL
- Iterations in MYSQL, Simple LOOP, WHILE
- FOR LOOP and NESTED LOOPS in MYSQL
- MYSQL Select statements
- SQL within MYSQL, Composite Data Types
- Cursor Variables and Management in MYSQL
- Implicit & Explicit Cursors and Attributes
- Cursor with Parameters and Nested LOOPs
- Cursors with Sub Queries, Reference Cursors
- Implicit Cursors,Explicit Cursor
|
CHAPTER 19 : PERFORMANCE (QUERY) TUNING
- Indexes - Definition and Architecture
- B Tree Concept in Indexes
- Clustered and Non clustered Indexes
- Primary Key and Clustered Index
- Unique Key and Non Clustered Indexes
- Simple Index, Rebuild Index
- Materialized Views - Indexed Views
- Composite Index, Function Based Index
- User_indexes System Table
- Range Partitions and Hash Partition
- List Partition and Composite Partition
- Parallel Query Process with Partitions
|
CHAPTER 4 : SQL LANGUAGE COMMANDS - 2
- Table Data Inserts and Validations
- Oracle Data Types
- DDL Commands with Examples
- CREATE, ALTER, TRUNCATE,DROP,RENAME
- Learn the DESCRIBE command to display the table structure
- DML Command DRL Commands Operators
- INSERT, UPDATE, DELETE Statements
- Difference between Truncate and Delete commands
- SELECT Statements with Multiplier
- SQL Comments
- Single Line Comments & Multi Line Comments
|
CHAPTER 12: ADVANCED MYSQL
- Procedures in MYSQL: STORED PROCEDURE
- PROCEDURES @ Parameters (IN, OUT, IN OUT)
- POSITIONAL Notation and NAMED Notation
- Procedure with Cursors and Sub Queries
- ALTER and DROP of Stored Procedures
- Functions in MYSQL: Real-time Usage
- User Defined Functions, Nested Functions
- Using Functions in SQL Statements
- Working with Procedures and Functions
- Comparing Stored Procedures and Functions
- Using SPs with Table Value Functions
|
CHAPTER 20:PERFORMANCE TUNING - PARTITION
- Overview of Partitioning in MySQL
- Partitioning Types; RANGE Partitioning;
- LIST Partitioning;
- HASH Partitioning; KEY Partitioning;
- Can we do partitioning in MySQL?
- Does partitioning improve performance MySQL?
- How to create partition in MySQL?
- What is SQL partitioning?
- How to see partitions in MySQL?
- What is list partition in SQL?
- Which is used to list partition?
|
CHAPTER 5.SQL SPECIAL OPERATORS
- SET, AND, OR, NOT, IN
- Special Operators – IN (NOT IN)
- BETWEEN (NOT BETWEEN),
- Arithmetic and Logical Operators
- Understand Operator Precedence
- UNION, UNION ALL, INTERSECT, MINUS
- LIKE (NOT LIKE), IS NULL (IS NOT NULL)
- Understanding DCL and TCL Commands
- Transaction Concepts in Databases with SQL
- Transaction Types and Uses
- Commit and Rollback Operations
- Nested Transactions with Savepoints
- Database Read Consistency with SQL
|
CHAPTER 13 MYSQL ARCHITECTURE AND ADMIN
- Architectural Overview
- Understand how MySQL Transmits Data
- MySQL Processes Requests;
- Understand how MySQL Stores Data;
- MySQL uses Memory; Tablespaces, Redo and Undo Logs;
- Plug-in Interface;
- What does MySQL admin do?
- What are different tasks that can be performed by
- MySQL administration explain each task?
- What type of architecture does MySQL use?
- What are admin privileges in MySQL?
- How to use MySQL in Windows?
- What is MySQL and why do I need it?
|
Chapter 21 : MYSQL WORKBENCH CONFIGURATION
- User Accessibility Options
- Preferences Options
- General Editor Preferences
- SQL Editor Preference
- Administration Preferences
- Modelling Preferences
- Fonts and Colors
- SSH Prefrences
- Other Preferences
- MySQL Workbench Home Screen
- How to change MySQL configuration?
- How do I change the configuration file path in MySQL Workbench?
- How do I add a config file to MySQL Workbench?
|
CHAPTER 6: GROUPING QUERY RESULTS
- Identify Distinct Values in Tables
- Group Function or Aggregate function
- Group By Syntax
- Group By Operations in Queries
- Having Clause of DRL Statements
- Aggregate Functions with Group By
- Order By Clause and Group By
- SQL Group By Statement
- Query Execution Order with Group By
- Arithmetic Functions,Character Function
- Date & Time Functions,String Function
- Conversion Functions,Analytical Function
- Rank(), Dense_Rank(),Row_Number()
- IFNULL() FUNCTION
|
CHAPTER 14: CONFIGURING & MONITORING MYSQL
- Server Configuration Options
- Option Files and System Variables
- Launching Multiple Servers on the Same Host
- Monitoring Tools & Techniques
- MySQL Enterprise Audit & Monitor
- Monitoring User Activity
- What is monitoring in MySQL?
- How do I start MySQL enterprise monitor?
- How do I access MySQL monitor?
- Is MySQL enterprise monitor free?
- How to configure MySQL server?
- Where can I find server configuration in MySQL?
- How to configure MySQL server in Windows 10?
- How to setup MySQL server on localhost?
|
CHAPTER 22: MYSQL DATABASE MODELING
- What is Data Modelling
- Creating a model -part 1
- Creating a model -part 2
- Basic Modelling
- What is data modelling in MySQL?
- Which model is used in MySQL?
- How to create a data model in MySQL?
- How to get database model in MySQL?
- What is data modelling in MySQL?
- What are the basics of MySQL?
- Which model is used in MySQL?
- How to create database from model MySQL Workbench?
- How does MySQL Workbench connect to database?
- How to import schema into MySQL?
|
CHAPTER 7 : CONSTRAINTS & KEYS
- Importance of Data Integrity
- Working with Integrity constraints
- Types of Integrity Constraints
- Domain Integrity Constraint and Usage
- Entity Integrity Constraints and Usage
- Referential Integrity Constraint and Usage
- Check Constraints and Usage
- NOT NULL Constraint, UNIQUE Constraint
- PRIMARY KEY Constraint and Usage
- FOREIGN KEY Constraints and Relations
- Column Level & Table Level Constraints
- Adding Constraints to Tables, User_Constraints
- Enabling - Dropping Constraints, Self Referential Integrity
- Disabling Constraints on Tables
|
CHAPTER 15:USER MANAGEMENT
- Creating and Modifying User Accounts
- Configuring Passwords; Authentication Plug-Ins;
- Granting Permissions & Grant Tables;
- How to view users in MySQL?
- How to set user privileges in MySQL?
- What are different user levels in MySQL?
- How to control access to MySQL database?
- How do I create a user in MySQL 8.0 and grant all privileges?
- How do I create a new user in MySQL?
- How do I change MySQL username and password?
- How to create MySQL user accounts and grant privileges?
- How to change privileges in MySQL?
- How to grant permission to view in MySQL?
- How do I grant permissions in MySQL 8?
|
CHAPTER 23 : BACKUP TOOLS & TECHNIQUE
- Understanding Backups & Backup Techniques
- Creating a Backup Strategy;
- MySQL Backup Tools;
- Raw Backup Methods;
- How do I backup my MySQL database?
- Does MySQL have backup?
- How to restore all databases in MySQL?
- Which tools are available to perform backups in MySQL?
- What is the best way to backup MySQL database?
- Which tool used for DB backup?
- Which backup tool is best?
- What are the types of backup in MySQL?
- Does MySQL have backup?
- How do I backup my entire database?
|
CHAPTER 8 : QUERIES & JOINS
- Need for Joins and Table Comparisons
- SET Operations on Tables and Joins
- How do Joins work in SQL?
- What does (+) mean in SQL joins?
- Join Types : Equi Join, Simple Join
- Inner Join and Query Conditions
- Cross Join (Cartesian Join), Conditions
- Join syntax - Sql syntax and Ansi Syntax
- Non-Equi Join and Self Join
- Outer Joins – Types, Advantages and Usage
- Cross Joins – Advantages and Limitations
- Self Joins, Merge Joins and Sub Queries
- Using Inner Self Joins with HAVING
|
CHAPTER 16 : MYSQL SECURITY
- Understanding Security Risks
- Network Security & Secure Connections Password
- Operating System Security;
- How to make MySQL secure?
- How is the MySQL access security controlled?
- Does MySQL have authentication?
- What are MySQL vulnerabilities?
- Are MySQL databases secure?
- Can MySQL database be hacked?
- What are MySQL vulnerabilities?
- Is MySQL insecure?
- Does MySQL have security?
- Is MySQL secure enough?
|
CHAPTER 24 : MYSQL REPLICATION
- Overview Replication
- Master-Slave Configuration
- Master-Master Configuration;
- Replication trouble shooting / errors;
- Does MySQL support master to slave replication?
- How to set up master master replication in MySQL?
- How to check master-slave replication MySQL?
- How to check master master replication on MySQL?
- Does MySQL support multi master replication?
- How do I stop master master replication in MySQL?
- How to replicate existing MySQL database?
- How do I copy an existing database?
- How do I copy MySQL database to another computer?
|
