Hi James,

Here's how I would write that trigger that you specified above.  In triggers, it's important to bulkify your trigger to avoid hitting governor limits, so I put the SOQL query before the loop.

trigger AccountTrigger on Account (before delete) {

    if(trigger.isDelete){
        //check the entire list of accounts to be deleted.
        System.debug(trigger.old);

        //query the accouts that have opportunities
        List<Account> accountsToBlockDeletion = [SELECT Id, Name 
                                                FROM Account
                                                WHERE Id IN (SELECT AccountId 
                                                            FROM Opportunity 
                                                            WHERE AccountId IN :trigger.oldMap.keySet())];//by filtering on the acocuntId the query runs more efficiently
        
        //loop through the results of the query
        for(Account a: accountsToBlockDeletion){
            //display the account that deletion is being blocked on in the debug logs
            System.debug(a);
            //add our error to prevent deletion.
            trigger.oldMap.get(a.Id).addError('Cannot delete an account that has an opportunity');
        }
    }
}

Regarding your question about dynamic SOQL, Here is the developer forum page: https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_dynamic_soql.htm?search_text=soql
In a nutshell, you construct your query as a string (which you can easily display in a debug statement) and then use Datebase.query(yourQueryString) to execute.
Here's a very simple example:

String q = 'SELECT Id, Name FROM Account WHERE Name = \'Acme\'';
System.debug(q);
List<Account> queriedAccounts = Database.query(q);