learning SQL

Learning SQL FTW

I’ve been handling my data in R solely. Since sql is highly recommended by experts I followed on twitter, I think it’s worth a shot.

So I went to datacamp and started the intro tutorial and finished in about 4 hrs.

---

Notes

After learning about the basic grammars. It seems that dplyr offers all the features for data manipulation.

Here is what I learned:

• A database is a list of data.frames
  
• basic usage
  • construct a query to the database
    
  • return as a data.frame
    
  • manipulations equivalent to dplyr functions:
    • SELECT XXX from YYY = YYY %>% select(XXX)
      
    • WHERE condition = %>% filter(condition)
      
    • SELECT operation(XXX) FROM YYY GROUP BY ZZZ = YYY %>% group_by(ZZZ) %>% summarise(operation(XXX))
      
    • SELECT op(XXX) AS NNN = mutate(NNN = op(XXX))
• strangely the tutorial in datacamp put join in another full tutorial… don’t know why.

The full grammar is sth like this: pretty simple and straightforward

SELECT column
FROM table
WHERE condition
GROUP BY group condition
HAVING grouped condition

Summary

At least next time I saw some sql query, I could understand what’s going on.