Dat Le lenguyenthedat

## groupby_sql_styleguide.sql
-- Bad use of aggregation
SELECT
  users.id,
  users.email,
  users.name,
  MAX(users.created_at) AS created_at, -- not necessary
  COUNT(orders.id) AS orders_count
FROM orders
LEFT JOIN users
       ON users.id = orders.user_id

## joins_sql_styleguide.sql
-- Bad: multiple type of joins unecessarily used, start from `orders` as base table is not ideal
SELECT
  DATE(orders.created_at) AS date,
  users.email
FROM orders
LEFT JOIN users
       ON users.id = orders.user_id
JOIN order_items
  ON order_items.order_id = orders.id
GROUP BY 1, 2

## syntax_sql_styleguide.sql
-- Bad: no capitalization
select
  products.id,
  case
    when products.sold_by = 0 then 'item'
    when products.sold_by = 1 then 'weight'
    when products.sold_by = 2 then 'volume'
  end as sold_by,
  product_categories.category_name
from products

## SEA Games Results.csv

          
            No
            Year
            Host
            Rank
            Nation
            Gold
            Silver
            Bronze
            Total

            
              1
              1959
              Thailand
              1
              Thailand
              35
              26
              16
              77

            
              1
              1959
              Thailand
              2
              Myanmar
              11
              15
              14
              40

            
              1
              1959
              Thailand
              3
              Malaysia
              8
              15
              11
              34

            
              1
              1959
              Thailand
              4
              Singapore
              8
              7
              18
              33

            
              1
              1959
              Thailand
              5
              Vietnam
              5
              5
              6
              16

            
              1
              1959
              Thailand
              6
              Laos
              0
              0
              2
              2

            
              2
              1961
              Myanmar
              1
              Myanmar
              35
              25
              22
              82

            
              2
              1961
              Myanmar
              2
              Thailand
              21
              18
              22
              61

            
              2
              1961
              Myanmar
              3
              Malaysia
              16
              24
              39
              79

## set-bits-int16.hs
import System.Environment (getArgs)
import System.Random (StdGen, newStdGen, random)
import Data.Bits (popCount)
import Data.List (unfoldr)
import Data.Int (Int16)

main = do
  seed <- newStdGen
  args <- getArgs
  let size  = read $ head args

## Countries.csv
"continent_code", "country_code","continent_name","country_name","country_iso3","country_number","country_full_name
"AS","AF","Asia","Afghanistan","AFG","004","Islamic Republic of Afghanistan"
"EU","AX","Europe","Åland Islands","ALA","248","Åland Islands"
"EU","AL","Europe","Albania","ALB","008","Republic of Albania"
"AF","DZ","Africa","Algeria","DZA","012","People"s Democratic Republic of Algeria"
"OC","AS","Oceania","American Samoa","ASM","016","American Samoa"
"EU","AD","Europe","Andorra","AND","020","Principality of Andorra"
"AF","AO","Africa","Angola","AGO","024","Republic of Angola"
"NA","AI","North America","Anguilla","AIA","660","Anguilla"
"AN","AQ","Antarctica","Antarctica","ATA","010","Antarctica the territory South of 60 deg S"

## Dexter.csv

          
            Episode
            Name
            Score
            Votes

            
              1.12
              Born Free
              9.6
              4268

            
              1.11
              Truth Be Told
              9.2
              2629

            
              4.12
              The Getaway
              9.2
              6440

            
              1.10
              Seeing Red
              9.2
              2557

            
              4.11
              Hello, Dexter Morgan
              9.1
              2927

            
              4.09
              Hungry Man
              9.1
              2622

            
              6.12
              This Is the Way the World Ends
              9.1
              3346

            
              7.01
              Are You...?
              9.1
              5696

            
              2.09
              Resistance Is Futile
              9.1
              2366

## BreakingBad.csv

          
            Episode
            Name
            Score
            Votes

            
              5.14
              Ozymandias
              9.9
              6400

            
              5.16
              Felina
              9.9
              4510

            
              4.13
              Face Off
              9.6
              1783

            
              5.15
              Granite State
              9.3
              1679

            
              5.13
              To'hajiilee
              9.5
              1675

            
              5.11
              Confessions
              9.1
              1103

            
              5.05
              Dead Freight
              9.2
              1087

            
              5.09
              Blood Money
              8.9
              1062

            
              1.01
              Pilot
              8.6
              1037

## The look of disapproval.
alias disapprove="/Applications/Firefox.app/Contents/MacOS/firefox-bin 'data:text/html;base64,PGRpdiBzdHlsZT0idGV4dC1hbGlnbjpjZW50ZXI7Zm9udC1zaXplOjU1dm1pbiI+JiMzMjMyO18mIzMyMzI7PC9kaXY+Cg=='"
	-- Bad use of aggregation
	SELECT
	users.id,
	users.email,
	users.name,
	MAX(users.created_at) AS created_at, -- not necessary
	COUNT(orders.id) AS orders_count
	FROM orders
	LEFT JOIN users
	ON users.id = orders.user_id
	-- Bad: multiple type of joins unecessarily used, start from `orders` as base table is not ideal
	SELECT
	DATE(orders.created_at) AS date,
	users.email
	FROM orders
	LEFT JOIN users
	ON users.id = orders.user_id
	JOIN order_items
	ON order_items.order_id = orders.id
	GROUP BY 1, 2
	-- Bad: no capitalization
	select
	products.id,
	case
	when products.sold_by = 0 then 'item'
	when products.sold_by = 1 then 'weight'
	when products.sold_by = 2 then 'volume'
	end as sold_by,
	product_categories.category_name
	from products
No	Year	Host	Rank	Nation	Gold	Silver	Bronze	Total
1	1959	Thailand	1	Thailand	35	26	16	77
1	1959	Thailand	2	Myanmar	11	15	14	40
1	1959	Thailand	3	Malaysia	8	15	11	34
1	1959	Thailand	4	Singapore	8	7	18	33
1	1959	Thailand	5	Vietnam	5	5	6	16
1	1959	Thailand	6	Laos	0	0	2	2
2	1961	Myanmar	1	Myanmar	35	25	22	82
2	1961	Myanmar	2	Thailand	21	18	22	61
2	1961	Myanmar	3	Malaysia	16	24	39	79
	import System.Environment (getArgs)
	import System.Random (StdGen, newStdGen, random)
	import Data.Bits (popCount)
	import Data.List (unfoldr)
	import Data.Int (Int16)

	main = do
	seed <- newStdGen
	args <- getArgs
	let size = read $ head args
	"continent_code", "country_code","continent_name","country_name","country_iso3","country_number","country_full_name
	"AS","AF","Asia","Afghanistan","AFG","004","Islamic Republic of Afghanistan"
	"EU","AX","Europe","Åland Islands","ALA","248","Åland Islands"
	"EU","AL","Europe","Albania","ALB","008","Republic of Albania"
	"AF","DZ","Africa","Algeria","DZA","012","People"s Democratic Republic of Algeria"
	"OC","AS","Oceania","American Samoa","ASM","016","American Samoa"
	"EU","AD","Europe","Andorra","AND","020","Principality of Andorra"
	"AF","AO","Africa","Angola","AGO","024","Republic of Angola"
	"NA","AI","North America","Anguilla","AIA","660","Anguilla"
	"AN","AQ","Antarctica","Antarctica","ATA","010","Antarctica the territory South of 60 deg S"
Episode	Name	Score	Votes
1.12	Born Free	9.6	4268
1.11	Truth Be Told	9.2	2629
4.12	The Getaway	9.2	6440
1.10	Seeing Red	9.2	2557
4.11	Hello, Dexter Morgan	9.1	2927
4.09	Hungry Man	9.1	2622
6.12	This Is the Way the World Ends	9.1	3346
7.01	Are You...?	9.1	5696
2.09	Resistance Is Futile	9.1	2366