A programmer friend is forever telling me that when I begin to use Drupal, Joomla or WordPress that I will only create sites that will crash as soon as the site begins to receive more attention / traffic.
This is very discouraging as my concern is that I'll make sites based on great concepts that will crash as soon as many people begin visiting.
Are you aware of a course that I could purchase to learn to make a site able to handle heavy traffic?
Programming with an eye towards scalability is desirable, but it can also be very limiting.
The truth is that 95% of sites will never need to worry about scalability. Unless you plan to have more than 10,000 pages on your site (such as might be accumulated by collecting lots of user generated content), I wouldn't worry about scalability. Just choose your platform and be happy. If your site is not performing well, there are usually caching plugins that can be installed, or you could upgrade to a more powerful host.
Sites with millions of monthly visitors and lots of pages are the ones that need to start worrying about scalability. If your site gets to that size unexpectedly, it is almost always possible to migrate to a scalable platform.
Worrying about scalability before you need to is a form of premature optimization. It will prevent you from using the easiest tools to get the job done and may prevent you from getting started at all.
Just launch your site and see what happens first. I have sites on Wordpress, and that works fine for them. It works fine for hundreds of thousands of other sites. I also have a site that gets enough visitors that it needs a load balancer, and fancy caching algorithms to keep it running. I started that site as static HTML.
Sorry to say but your friend isn't correct. Wordpress, Drupal and even Joomla can handle millions of visitors a day and is purely down to the hardware you use. Of course some content managements systems are heavier than others but again this can be overcome by optimizing your database and using quality hardware such as a Scalable cloud, Scalable VPS or even a dedicated server.
It's worth mentioning that hundreds of sites that are in the Alexa top 1000 use Joomla, Wordpress, or Drupal. To disprove your friend I recommend a FireFox plugin called Wappalyzer that can detect most websites and tell you what they using, then you can go back to him and tell him your findings that many of the highest received sites in the world use such platforms.
SQL Problems
The major problem with sites that receive 1,000,000's of visitors a day is that the volume of SQL queries on the database - this requires a fair amount of CPU time but mostly is down to the amount of RAM you have dedicated to your processes. Generally sites that receive this high amount of traffic will have 10,000's of thousands of pages that make the database big and can start to slow down but again, this can be overcome using optimisation of the database and increasing of the hardware the site uses.
Cloud to the Rescue
Sites that receive high amount of traffic can use a CDN to ease of server side requests while enabling audiences all over the globe enjoy rich content at super fast speeds. Also you have SQL's that can be hosted on a cloud, Rackspace and Amazon both offer these services as well as many other companies - These are extremely fast for queries as you pay for the amount of queries you receive daily.
While the above answers are good explanations, I think the direct answer you're looking for is called "load balancing".
You may not need to worry about it in most cases, but it's not a complicated concept to implement if needed. Basically you'll look at your database and it's structure, caching, and your hardware.
For example in a standard WordPress or blog site the comments table can grow incredibly fast. You could easily implement a 3rd party service like disqus to offset that traffic.
Smashing Mag wrote a brilliant article where they covered this topic in detail. http://wp.smashingmagazine.com/2012/09/12/secrets-high-traffic-wordpress-blogs/
