Optimizing RDBMS performance requires a holistic approach because it involves many moving factors: network performance, server performance, software tune-up, and so on.
Before attempting to optimize logically and syntactically correct SQL statements, make sure that other parts of the system are on solid ground: client application, network, server, RDBMS software installation, and your database model. Any SQL query tune-up should start with examining the query execution plan to pinpoint bottlenecks, and focus on optimizing these first; the 80/20 rule will help you to stay focused.
Proper indexing of the table is absolutely crucial for the SQL performance in your database. Indexes can improve some queries and negatively affect others. Selecting an indexing strategy will ultimately depend on your business needs and intended usage scenarios.
Each RDBMS comes with a number of proprietary, database-specific, optimization tools and techniques. Using them might improve performance; the tradeoff is potential vendor lock-in as it will reduce portability of your code.
DBAs perform an important role, being an expert in particular technologies, and having a firm grasp of the issues surrounding relational database setup and operations. Any optimization quest must involve consulting a DBA at some point, the earlier the better.