SQL Rules

A. Statement Rules

• Cases: SQL statements are not case-sensitive, meaning that "SELECT" is considered the same as "select" or "Select". A common practice is to write SQL keywords in uppercase and use lowercase for column and table names, which makes code easier to read and debug.

• White Spaces: All extra white space within a SQL statement is ignored when that statement is processed. SQL statements can be specified on one long line or broken up over many lines. So, the following three statements are functionally identical:

• Termination: If running multiple SQL statements, those statements must be separated by a "; (semicolon)" , at the end of each statment.MO

SELECT title FROM Movies;

select title
from Movies;

Select
title
From
movies;

B. Comments

• For multiline comments, use /* your comment */ (though it is rare, it can also be used inline commenting see examples below)

• For inline/entire-line comments, use -- (two hyphens). It is a good practice (and a style requirement for certain DBMS) for readibility to have at least one whitespace after the second dash.

/* MULTILINE COMMENT
comment line 1
comment line 2
comment line 3 
*/
SELECT title FROM Movies;


-- INLINE COMMENT
SELECT title -- inline comment
FROM Movies;
-- OR
SELECT 
    title, 
--    release_year, 
    director
FROM Movies;
-- OR
SELECT title, /*release_year,*/ director
FROM Movies;


-- ENTIRE LINE COMMENT
-- SELECT title, release_year, director
SELECT title, release_year, director
FROM Movies;

C. Aliases

Aliases can be used to specify columns (or combination of columns), tables, and queries and are of great help when:

• Column names are either too long or not readable

• Calculations are performed on columns or functions are used to combine columns

• There are more than one table involved in a query

In contrast to column aliases, table aliases are never returned to the client.

Use of AS Keyword:

Though the use of AS keyword is optional, it helps for easy reading.

-- aliases for column names
SELECT actor_date_of_birth AS dob
FROM Actors;

-- aliases for calculated fields
SELECT AVG(budget) avg_budget -- without AS keyword
FROM Movies;

-- aliases for combined columns
SELECT CONCAT(first_name,' ',last_name) AS full_name
FROM Actor;

-- aliases for table names 
SELECT 
    a.first_name, 
    a.last_name, 
    m.title, 
    m.release_year
FROM Actors AS a, Movies AS m
WHERE a.movie_id = m.id
    AND m.release_year > 2000;

Notice the use of table aliases with column names. This is particularly useful to easily track the sources of fields and mandatory when there are columns with the same name in tables that are joined.

D. Naming of the Columns/Tables/Queries

Do not use Keywords or Clauses to name columns, tables, or queries

When naming columns (or tables) try to use explicit words and avoid shortening words that can only be understood by a handful of people (if not only by you) as much as possible.

It is also good practice to create column names as a chain of words and separate them with underscore "_", as opposed to using whitespaces. However, if you must/prefer to use spaces to separate the words then you need to use double quotes ' " ' around the column name

-- aliases for calculated fields
SELECT AVG(budget) AS "Avearage Budget"
FROM Movies;

Note: This is really the only place in which you'll ever want to use double quotes in SQL. Single quotes for everything else.

E. Query Clause order

The order in which the clauses are executed

1. SELECT

2. FROM

3. WHERE

4. GROUP BY

5. HAVING

6. ORDER BY