The pros and cons of using Database apex class oposite to SOQL direct queries or DML - in which cases we should use database class?
The inline DML keywords don't allow for partial success, setting headers (e.g. to send emails or bypass duplicate rules), etc. Use them when you don't need any advanced features of DML operations. They are typically best for controllers, unit tests, and other all-or-none DML situations. The Database methods should be used for advanced features (e.g. emptying the recycle bin, bypassing duplicate rules, etc), and generally will be the only way to implement a certain type of DML behavior other than the default. They should almost always be used in triggers and other automated or bulk contexts (e.g. flows).
The inline query statements are well-suited for static queries that change only by the criteria provided to them. This is true for most queries, including triggers, controllers, batch classes, and so on. Rarely, you'll need to select a dynamic list of fields, objects, and/or criteria, in which case you'll need the relevant Database methods. You should avoid using them only because the compiler cannot warn you of typos in dynamic queries, and the system can't protect you from deleting a field that was used in a dynamic query (but Salesforce can warn you about fields and objects used in inline queries).
Ultimately, there's too many edge cases to give a set of rules that will work for 100% of anything you might need to implement, so you'll need to learn how to use all of the methods, then learn which ones are appropriate for your situation. As you gain experience as a developer, you should intuitively start to learn which method is preferable for the task you're working on. Allow experience to be your teacher.