What exactly is a "Console"?

In Windows terms, a Console is a textual GUI window that you see when you run "cmd.exe". It allows you to write text to, and read text from, a window without the window having any other UI chrome such as toolbars, menus, tabs, etc,..

To get started you'll want to load Visual Studio, create a new project and choose "Console Application". Change the boilerplate code that Visual Studio produces to:

using System;
using System.Text;

namespace MyConsoleApp
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.Write("Hello, world!");
            Console.ReadKey();
        }
    }
}

When you run your application, a console window will open with the text "Hello, world!" and it'll stay open until you press a key. That is a console application.

Is console window physically a memory area in the video memory? Or something else?

It's not physically a memory area in video memory, it's "something else". The Wikipedia Win32 console page gives a fairly robust descrption of the ins and outs.

A console application has only one window. It does not have window management functions in order to spawn child "consoles".

You can start additional console applications, but these are separate entities.