Without a sound grasp of database internal, you are bound to misuse it.
Writing an SQL which runs, and does it job, but without setting the proper index in the database may work in development, but work very poorly in production, effectively killing you database, and causing bottle-necks.
Identifying a bottle-neck in itself needs a knowledgeable person, since you might (falsely) say - well I've reached the capacity of my DB server, I just need to scale it up... which translates to money lost, and a very short-term solution.
Performance is not the only problem which may arise - security is a main issue which might not be apparent to the naive developer - O/R mapping and other frameworks made SQL-injections and other database attacks less prevalent, but being unaware of their existence may open up your application to very nasty attacks.
Large-scale DB-oriented projects often have a full-time DBA, whose job was to work with the system architect on the database schema, guiding developers and going over all of their queries, fixing them or adding indices where needed.
In smaller projects, a team might depend on its own developer's competence for this. This means that you should be able to understand the major guidelines and caveats for using and misusing your database.
If you work in a team, you might want to depend on a team member who is more knowledgeable in the database arena than you, and let him review your SQL queries, and help you design them.
Developers today are expected to be intimately familiar with many technologies, from SQL, noSQL, OS, Web Engines, Mobile, etc. and it is relatively easy to achieve a working prototype in any of these to give a developer the illusion of proficiency, until the same code is faced with production environment, where the true complexity of technology is revealed... you better be prepared!