Open Acadmey

Database Course

RDBMS/SQL

MySQL Course

Part – I

Introduction

Course Introduction
Why MySQL?
DBA vs Developer Course

READ MORE READ LESS

Part -II

MySQL Storage Engines

Storage Engines
FEDERATED Storage Engine
MEMORY Storage Engine
BLACKHOLE Storage Engine
CSV Storage Engine
MyISAM Storage Engine
ARCHIVE Storage Engine

READ MORE READ LESS

Part – III

MySQL Backup & Restore

MySQL Backups
Physical/Cold Backup
Files needed for Cold Backup
Logical Backups
MySQLDUMP Backup Program
Restoring from MySQLDUMP
MySQLPUMP Backup Program
Compressing MySQL Backups
Creating Consistent Data Dump
CREATE TABLE LIKE SQL Statement
MySQL Hot Backup
MySQL Hot Backup Tools
XtraBackup Hot Backup Tool
Preparing Hot Backup Restore

PostgreSQL Course

Part – I

Introduction

PostgreSQL Introduction
Installing PostgreSQL v15 on Windows and Linux
PostgreSQL System Requirement
Installing PostgreSQL v15 on Windows
Setup PostgreSQL Environment Variable on Windows
Installing PostgreSQL v15 on Linux
Setup PostgreSQL Environment Variable on Linux

PostgreSQL Architecture

Process and Memory Architecture
Postmaster Process
Utility Processes
Memory Segments
Physical Files in PostgreSQL

Database Cluster

PostgreSQL Cluster (Initdb, Start/Stop/Restart/Reload)
PostgreSQL Cluster Demo on Windows
PostgreSQL Cluster Demo on Linux

PostgreSQL Directory Layout and Configuration Files

Database Directory Layout
Base Directory
PostgreSQL.conf
PostgreSQL.Auto.conf
Pg_Ident.conf
Pg_Hba.conf

Create Objects(Database/User/Schema) and Privileges & CURD Operations

Create/Drop Database
Create/Drop User
Create/Drop Schema and Search Schema Path
Grant/Revoke Privileges on Database Objects
CRUD Introduction
Create Table, Data Types, Constraints, Functions and Operators
Retrieve Data in PostgreSQL
Update Records in a Table
Delete Records in a Table
Table Inheritance
Table Partitioning using Inheritance
Copy Table

Using Psql Command Line and PG system Catalog

Psql Commands
Pg_Catalog & Time zones in PostgreSQL
Tablespace

Tablespace

Introduction
Create Tablespace on Windows and Linux
Move Objects from Tablespace and Drop a Tablespace
Temporary Tablespace in PostgreSQL

Backup and Restore

Introduction to PostgreSQL Backups
Logical Backups on Windows and Linux
How to Compress and Split Dump Files
Restore Database using Logical Backups through Psql Interface
Restore Database using Logical Backups through Pg_Restore Utility
Offline Backups
Introduction to Online Backups
Setup Continuous Archiving In Windows
Setup Continuous Archiving In Linux
Online Low Level API Backup
Pg_Basebackup Utility
Point-In-Time-Recovery(PITR)

READ MORE READ LESS

Part – II

Maintenance in PostgreSQL

Introduction to Maintenance
How to Calculate Query Cost and Explain Plan
Updating Planner Statistics\Analyze
Vacuum Freeze
Data Fragmentation
Introduction to Vacuum and Vacuum Full

READ MORE READ LESS

NoSQL

MongoDB Course

Part – I

Introduction

What is MongoDB
Why should we learn MongoDB
important Features of MongoDB

READ MORE READ LESS

Part – II

Server Administration

Starting Mongod Server with command line options
Binding Mongod Server to an IP address
Starting mongodb using a configuration file

READ MORE READ LESS

Cassandra Course

Introduction: Cassandra as a distributed, decentralized, columnar store

A Column-Oriented Database
Requirements For A Product Catalog System
What Is Cassandra?
Cassandra Vs HBase

READ MORE READ LESS

Redis Course

Part – I

Introduction

Course Introduction
Introduction To Redis

Getting Started

Installing Redis on Windows
Installing Redis on Linux
Starting and Shutting Down Redis Server
Getting Server Information
The Redis CLI

Redis Data Management

Redis Keys and Values
Deleting Keys
How to check if a key exists or not
How to define keys with expiration
How to remove expiration from a key
How Redis handle keys expirations
Key Spaces
Keys naming conventions
Keys pattern matching - KEYS
Saving keys information on server
Rename a Key - RENAME
Rename a key with caution using RENAMENX
Deleting Keys Asynchronously via UNLINK
How to find data type of a key value - TYPE

Redis Data Structures - Strings

Introduction to data types
Redis Strings and their use cases
String Key definition for an app, and a shop
Counting numbers using INCR and DECR
Counting floating point numbers
Using APPEND as list of timeseries data
Setting and getting multiple keys via MSET, MGET, MSETNX
Using GETSET for an atomic reset
Extract string value via GETRANGE
Replace string value using SETRANGE
Set Key and expiration using SETEX and PSETEX
Set key if not exists using SETNX
String Encoding types
Using serialized JSON data
Scanning Keys with SCAN

Redis Lists

Introduction to lists
Creating a list using LPUSH, RPUSH
View latest stock prices via LINDEX
Insert an element via LINSERT
Remove elements via LPOP and RPOP
Trim a list using LTRIM
Update an element value via LSET
Find length of a list via LLEN
Find matching elements via LPOS
Remove element via LREM
Move elements between lists via LMOVE

Redis Hashes

Introduction to Hashes
Using HSET, HGET and HGETALL for hashes
Get multiple fields values via HMGET
Find length of a hash via HLEN
Delete fields via HDEL
Use HEXISTS to check if a field exist
Get all fields name via HKEYS
Get all fields values via HVALS
Counting via HINCRBY and HINCRBYFLOAT
Using HSETNX to add a new field
Get random fields via HRANDFIELD

Redis Sets

Introduction to Sets
Are sets are unique, and un-ordered
Using SETS for unique data
Sets Maintenance - SADD, SREM, SPOP
Checking a membership existence
Get random elements via SRANDMEMBER
Moving elements within sets via SMOVE
Sets Operations - Union
Sets Operations - Union and store results with SUNIONSTORE
Sets Operations - Intersection
Sets Operations – Difference

Redis Sorted Sets

Introduction to Sorted Sets
Using ZADD and ZRANGE
Display data in reverse score order
Atomic operations by ZINCRBY
Lexicographical order
Rank stocks with ZRANK

Redis HyperLogLog

Introduction to HyperLogLog
Unique Website visitors via HyperLogLog

Publish/Subscribe Model

Introduction to Publish/Subscribe Model
Setup a Publish/Subscribe communication
Patterned Subscription
Channel management via PUBSUB
Unsubscribe from a channel or channels

READ MORE READ LESS

Part – II

Designing Databases in Redis

From a RDBMS Table to Redis Data Structure
From multi primary keys to Redis Structure

Redis Protocol

Introduction to Redis Protocol
Turning strings into Redis Protocol Specifications
Generating Redis Protocol commands using python

READ MORE READ LESS

Part – III

RediSearch

Introduction to RediSearch
Using RediSearch on Movies Database
Adding sample movies data via hashes
Creating an index
Query data with RediSearch
fuzzy logic and search by value
Value range searches

READ MORE READ LESS

Elasticsearch Course

Introduction

Introduction to Elasticsearch
Understanding of the Elastic Stack

Getting Started

Overview of installation options
Setting up Elasticsearch & Kibana on Linux
Setting up Elasticsearch & Kibana on Windows
Understanding the basic architecture

Inspecting the cluster
Sending queries with CURL
Sharding and scalability
Sharding
Understanding
replication
Replication
Adding more nodes to the cluster
Overview of node roles

Managing Documents

Creating & deleting indices
Indexing documents
Retrieving documents by ID
Updating documents
Scripted updates
Upserts
Replacing documents
Deleting documents
Understanding routing
How Elasticsearch reads data
How Elasticsearch writes data
Understanding document versioning
Optimistic concurrency control
Update by query
Delete by query
Batch processing
Importing data with cURL

Mapping & Analysis

Introduction to analysis
Using the Analyze API
Understanding inverted indices
Introduction to mapping
Overview of data types
How the "keyword" data type works
Understanding type coercion
Understanding arrays
Adding explicit mappings
Retrieving mappings
Using dot notation in field names
Adding mappings to existing indices
How dates work in Elasticsearch
How missing fields are handled
Overview of mapping parameters
Updating existing mappings
Reindexing documents with the Reindex API
Defining field aliases
Multi-field mappings
Index templates
Introduction to dynamic mapping
Combining explicit and dynamic mapping
Configuring dynamic mapping
Dynamic templates
Mapping recommendations
Stemming & stop words
Analyzers and search queries
Built-in analyzers
Creating custom analyzers
Adding analyzers to existing indices
Updating analyzers

Searching for Data

Introduction to searching
Introduction to term level queries
Searching for terms
Retrieving documents by IDs
Range searches
Prefixes, wildcards & regular expressions
Querying by field existence
Term level queries
Introduction to full text queries
The match query
Introduction to relevance scoring
Searching multiple fields
Phrase searches
Full text queries
Leaf and compound queries
Querying with boolean logic
Query execution contexts
Boosting query
Disjunction max (dis_max)
Querying nested objects
Nested inner hits
Nested fields limitations
Compound & nested queries

Joining Queries & Controlling Query Results

Introduction to this section
Add departments test data
Mapping document relationships
Adding documents
Querying by parent ID
Querying child documents by parent
Querying parent by child documents
Multi-level relations
Parent/child inner hits
Terms lookup mechanism
Join limitations
Join field performance considerations
A word on document types
Specifying the result format
Source filtering
Specifying the result size
Specifying an offset
Pagination
Sorting results
Sorting by multi-value fields
Filters

Aggregations

Introduction to aggregations
Metric aggregations
Introduction to bucket aggregations
Document counts are approximate
Nested aggregations
Filtering out documents
Defining bucket rules with filters
Range aggregations
Histograms
Global aggregation
Missing field values
Aggregating nested objects

Improving Search Results

Introduction to this section
Proximity searches
Affecting relevance scoring with proximity
Fuzzy match query (handling typos)
Fuzzy query
Adding synonyms
Adding synonyms from file
Highlighting matches in fields
Stemming

READ MORE READ LESS

Vector Course

Introduction

Introduction to Vector Database
Vectors and Embeddings
Explain vector database like I’m 5
How vector database store data
How do vector database works?
Vectors in 2D

READ MORE READ LESS

Server Course

Linux Administration

Part – I

Overview
Course Overview

Installing and Connecting to a Linux System

Linux Distributions
Installing Linux using WSL on Windows (Windows Subsystem for Linux)
Installing VirtualBox on Windows
When to Install Linux from Scratch or Manually
Logging In Directly to a Linux System

READ MORE READ LESS

Part – II

Disk Management
Creating Partitions with fdisk
File Systems

LVM – The Logical Volume Manager

Introduction to the Logical Volume Manager (LVM)
Creating Physical Volumes (PVs), Volume Groups (VGs), and Logical Volumes (LVs) Extending Volume Groups and Logical Volumes
Mirroring Logical Volumes
Removing Logical Volumes, Physical Volumes, and Volume Groups
Migrating Data from One Storage Device to Another

READ MORE READ LESS

Network Course

Networking

Part – I

Introduction
Course Introduction

Understanding, Implementing, and Troubleshooting IPv4

Introduction
What is an IPv4 Address
What is a MAC Address
Understanding ARP
OSI Model

READ MORE READ LESS

Part – II

Understanding Active Directory Domain Services and DHCP (Dynamic Host Configuration Protocol)

Understanding Active Directory
Introduction to DHCP
How DHCP Works
Benefits of using DHCP
How DHCP Lease Generation Works
How DHCP Lease Renewal Works

READ MORE READ LESS

Storage Technology

Storage

Introduction

Introduction to Storage Concepts
Types of Storage and Terminologies
RAID Concepts
Backup Fundamentals
Storage Protocols
Introduction to Storage
Understanding NAS Concepts
Understanding SAN Concepts

READ MORE READ LESS