SQL notes for a data scientist
By Tuo Wang in Data Science
October 28, 2021
These my personal notes for learning SQL.
Some great tutorials
Learning basic SQL:
Advanced SQL:
- SQL Tutorial: How To Write Better Queries
- Performance Tuning SQL Queries
- Order of execution of a Query
- Window Functions
Difference between INNER JOIN
, LEFT JOIN
, RIGHT JOIN
and OUTER JOIN
SQL Date Functions
- ADD days
# MySQL - DATE_ADD, syntax: DATE_ADD(date, INTERVAL value addunit)
SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL 15 MINUTE);
SELECT DATE_ADD("2017-06-15", INTERVAL 10 DAY); # forward 10 days
SELECT DATE_ADD("2017-06-15", INTERVAL -10 DAY); # backward 10 days
# For substraction, we can also use DATE_SUB
SELECT DATE_SUB("2017-06-15", INTERVAL 10 DAY);
# Note that, we can use CAST .. AS DATE to transform timestamp to date
SELECT CAST("2017-06-15 09:34:21" AS DATE);
# This transform "2017-06-15 09:34:21" -> "2017-06-15"
# OR we can use DATE() function
SELECT DATE("2017-06-15 09:34:21"); # "2017-06-15 09:34:21" -> "2017-06-15"
- DATE difference
# MySQL DATEDIFF: Return the number of *days* between two date values:
SELECT DATEDIFF('2017/08/25', '2011/08/25');
SELECT DATEDIFF('2017/08/25', '2011/08/25');
SELECT DATEDIFF('2017/08/25', '2011/08/25');
# TIMEDIFF : Return the difference between two time expressions:
SELECT TIMEDIFF("2017-06-25 13:10:11", "2017-06-15 13:10:10");
- Current Date
# MySQL
SELECT CURRENT_DATE(); # retrun current date: "YYYY-MM-DD"
SELECT CURRENT_TIMESTAMP(); # return current date and time: "YYYY-MM-DD HH-MM-SS"
- Reformat date
# MySQL
SELECT DATE_FORMAT("2017-06-15", "%Y"); # -> 2017
SELECT DATE_FORMAT("2017-06-15", "%Y-%m"); # -> 2017-06
- Get specific infor from date
# MySQL
# (1). EXTRACT()
SELECT EXTRACT(MONTH FROM "2017-06-15"); # -> 6
SELECT EXTRACT(WEEK FROM "2017-06-15"); # -> 24
SELECT EXTRACT(YEAR_MONTH FROM "2017-06-15"); # -> 201706
# (2). WEEKDAY()
# The WEEKDAY() function returns the weekday number for a given date.
# Note: 0 = Monday, 1 = Tuesday, 2 = Wednesday, 3 = Thursday,
# 4 = Friday, 5 = Saturday, 6 = Sunday.
SELECT WEEKDAY("2017-06-15");
# (3). YEARWEEK()
SELECT YEARWEEK("2017-06-15"); # -> 201724
- Past N days
# MySQL
# (1). EXTRACT()
SELECT EXTRACT(MONTH FROM "2017-06-15"); # -> 6
SELECT EXTRACT(WEEK FROM "2017-06-15"); # -> 24
SELECT EXTRACT(YEAR_MONTH FROM "2017-06-15"); # -> 201706
# (2). WEEKDAY()
# The WEEKDAY() function returns the weekday number for a given date.
# Note: 0 = Monday, 1 = Tuesday, 2 = Wednesday, 3 = Thursday,
# 4 = Friday, 5 = Saturday, 6 = Sunday.
SELECT WEEKDAY("2017-06-15");
# (3). YEARWEEK()
SELECT YEARWEEK("2017-06-15"); -> 201724
- Use
LEFT
to reformat date
SELECT LEFT("2017-06-15", 7); # -> "2017-06"
# Sometimes the RIGHT() function can be helpful.
- unixtim
SELECT FROM_UNIXTIME(1255033470); # -> 2009-10-08 20:24:30
SELECT DATE(FROM_UNIXTIME(1255033470)); # -> 2009-10-08