Separate non-db-tests (and others) to providers/non-providers/task_sdk

Currently `non-db-tests` execute all test type specified via `--test-types` in a single process using xdist (`-n processors`) parallelism in order to make efficient use of parallelisation of the tests. This means that both "core" and "providers" types of tests are run in a single `pytest` command. For example:

```bash
breeze testing non-db-tests \
--parallel-test-types "API Providers[google]"
```

Where PARALLELL_TEST_TYPES are produced by the "selective check" to determine which tests should be run.

However the #42505 splits out providers to a separate directory and we cannot run providers and non-provider tests any longer in the same process, because pytest has a limitation where test module names should not overlap (and tests/conftest.py is different for providers and non-providers, so they cannot be run in the same process as it will result in

```
_pytest.pathlib.ImportPathMismatchError: ('tests.conftest', '/opt/airflow/tests/conftest.py', PosixPath('/opt/airflow/providers/tests/conftest.py'))
```

One solution to that (probably best approach) is to introduce a required flag (`--providers/--no-providers`) for `non-db-tests` and filter out / core provider test folders when respective flag is used, and run two separate jobs in our CI - one for providers and one for core.

That would require to have to separate jobs in `ci.yml`. "Non-db Core tests", "Non-db Providers tests" - each of them passing the flag down to `run-unit-tests.yml` composite workflow.