LSE SQL

Best Practices

Lunch&Learn S1S5

Why

SQL8

DEMO

Q&A

SQL8

Checklist

  1. Use format tool (PoorSQL)
  2. Upper Case keyword, DB and Tables
  3. Use good alias
  4. Think about the column order
  5. Comment your code
  6. anything else (e.g. ending semicolon ;, SORT BY, etc.)

1. Use format tool - PoorSQL

  • Set Max Width (chars) is 79
  • check Trailing Commas
  • check Break Join ON Sections
  • All other settings keep as is.

1. Use format tool - PoorSQL

  1. SQL keywords
  2. Schema Name
  3. Table Name
  4. Column Datatype

2. Upper or Lower Case

Upper:

Alias -> "shorter name"
  • always an alias when +1 table
  • short but relevant

3. Use good alias

3. Use good alias

  • one-word -> use first 3-4 letter
  • DIM.DIM_ARTICLE AS art
  • DIM.PRODUCT_ARTICLE_DATAMART 
AS pad
  • snake case -> first letter from every word

4. Column order 

Structure in a logical order left to right:
  1. Primary key
  2. Foreign key
  3. Attributes
  4. Metadata

4. Column order 

internally: business logic from the lowest granularity and up

BAD

SELECT 
    store, 
    article, 
    country,   
    corporate brand, 
    department, 
    planning market, 
    product 
...

GOOD

SELECT 
    store, 
    country, 
    planning market, 
    corporate brand, 
    article,   
    product, 
    department, 
...

 

 

 

 

5. Comment your code

  • This code is doing Y
  • Filtering on X due to Y
  • Casting X due to Y

?

DEMO