Query field vs Code field

What is the difference between radio/droplist/multicheck code field and radio/droplist/multicheck query field?

When would be the best to use which?

Query field is database queries. Code field is PHP code. Two entirely different languages and usages. The query select fieldtypes generate their options from your database query. The code select fieldtypes generate their options based off the return value of your custom PHP code.

When would be the best to use which?

I don't know. They're tools. There's no clear cut "where do I use this" scenario. It's 100% dependent on your needs. Maybe you need a dropdown with options that come from a 3rd party extensions API so you'd need PHP and in that case a code select field would do that.

Technically with the code field plugin, i would be able to query from Oracle using some php codes to connect to Oracle?

Yup, you can execute whatever PHP you want. Substitutions are supported Joomla/CB APIs can also be used. In addition to that you've access to the $field and $user objects.