DATABASE MANAGEMENT SYSTEMS

SQL Coding Style Guidelines

As with all programming languages there are guidelines to follow in SQL that make your queries more readable and your code easier to follow.  This document provides a good starting point for writing readable code.

· First and foremost, always comment your queries.  In Oracle, lines may be commented by beginning the line with “REM” for “remark”; inline comments may be begun with “--“;   finally, while “/* … */” is listed as a valid comment structure, I recommend NOT using it as the last slash is often interpreted to mean “RUN”. 

· When writing a query, try to begin a new clause on a new line, such as

            SELECT E#, Dname
            FROM Employees, Departments
            WHERE Employees.Dept#=Departments.Dept# AND Salary > 10000

as opposed to something like

            SELECT E#, Dname FROM Employees, Departments WHERE Employees.Dept#=Departments.Dept# AND Salary > 10000

If  any clause specification exceeds one line, break it  into two at a logical place.  For example, if the WHERE clause above needed two lines:

            SELECT E#, Dname
            FROM Employees, Departments
            WHERE Employees.Dept#=Departments.Dept#
               AND   Salary > 10000

· Present SQL reserve words in upper case and table/column names in title case.  For example, the following is very difficult to read:

            SELECT E#, DNAME FROM EMPLOYEES, DEPARTMENTS WHERE EMPLOYEES.DEPT#=DEPARTMENTS.DEPT# AND SALARY > 10000

· When using table or column name aliases, choose meaningful names.  Consider the readability of the following two queries:

            SELECT a.name, b.name, c.dname
            FROM Employees a, Employees b, Departments c
            WHERE a.SupervisorID#=b.ID# and b.ID#=c.MgrID

            SELECT Worker.Name, Manager.Name, Departments.Dname
            FROM Employees Worker, Employees Manager, Departments
            WHERE Worker.SupervisorID#=Manager.ID#
              AND    Manager.ID#=Departments.MgrID