Course Notes
Use this workspace to take notes, store sample queries, and build your own interactive cheat sheet!
Note that you need to connect your SQL cells to an integration to run a query.
- You can use a sample integration from the dropdown menu. This includes the Course Databases integration, which contains tables you used in our SQL courses.
- You can connect your own integration by following the instructions provided here.
Take Notes
Add notes here about the concepts you've learned and SQL cells with code you want to keep.
Add your notes here
WORKING WITH DATES & TIMES: 2/19/23
Building Dates GETDATE() OR GETUTCDATE() returns a date in local time or UTC time SYSDATETIME() OR SYSUTCDATETIME returns current time as date type 2
Parsing dates with date parts Functions: DATEPART(): returns numeric value of part wanted DATENAME(): gives string value
Parts: Year/Month/ Day Day of year day of week week of year ISO week of year Minute/ Seconds
ADDING AND SUBTRACTING DATES: DATEADD(DAY, 1, @VARIABLENAME) DATEADD(DAY, -1, @VARIABLENAME)
COMPARING DATES DATEDIFF() Returns INT and rounds up can take args HOUR and MINUTE
fORMATTING DATES FOR REPORTING: Cast(): useful for converting one data type to another. eg, INT to DEC cast functions can be nested cast(cast(@CubsWinWorldSeries AS date)AS NVARCHAR(30)) eg, CAST(@SomeDate AS NVARVAHR(30)) AS DateToString
Convert(): For converting Data types, has some control over foramtting. Takes 3 parameters(data type, input, optional style) eg, CONVERT(NVARCHAR(30), @SomeDate, 0) AS DefaultForm OPTIONAL STYLE: 0: prints out just like CAST 1/101: print in US date format 120: print ODBC format to the sec 3/103: Prints British/French date format 4/104: Prints German date format 11/111: Prints Japanese date format 20/120: ODBC standard(121 for ms) 126: iso8601 YYYY-MM-DD HH:MI:SS.MMM
Format(): useful for formatting date or number in a particular way for reporting. Much more flexible formatting dates to strings than CAST() or CONVERT(). Uses .NET framework for conversion can be slower as you process more rows Format takes 3 parameters; input value, foramt code, optional culture eg, FORMAT(@SomeDate, 'd', 'en-US') AS US_d 'D' build long dates 'd' builds short dates
WORKING WITH CALENDAR TABLES: Calendar table: stores date information for easy retrieval. Contents of a calendar table include: General columns, Calendar Year, Fiscal Year, Specialized Columns APPLY(): executes a function for each row in a result set
--Functions
--DATEPART()
SELECT
DATEPART(YEAR, @dt) AS TheYear;
--DATENAME()
SELECT
DATENAME(MONTH, @dt) AS TheMonth;
--DETERMINING THE YEAR MONTH AND DAY
DECLARE
@SomeTime DATETIME2(7) = SYSUTCDATETIME();
-- Retrieve the year, month, and day
SELECT
YEAR(@SomeTime) AS TheYear,
MONTH(@SomeTime) AS TheMonth,
DAY(@SomeTime) AS TheDay;
--Filling in appropriate dateparts
DECLARE
@BerlinWallFalls DATETIME2(7) = '1989-11-09 23:49:36.2294852';
-- Fill in each date part
SELECT
DATEPART(YEAR, @BerlinWallFalls) AS TheYear,
DATEPART(MONTH, @BerlinWallFalls) AS TheMonth,
DATEPART(DAY, @BerlinWallFalls) AS TheDay,
DATEPART(DAYOFYEAR, @BerlinWallFalls) AS TheDayOfYear,
-- Day of week is WEEKDAY
DATEPART(WEEKDAY, @BerlinWallFalls) AS TheDayOfWeek,
DATEPART(WEEK, @BerlinWallFalls) AS TheWeek,
DATEPART(SECOND, @BerlinWallFalls) AS TheSecond,
DATEPART(NANOSECOND, @BerlinWallFalls) AS TheNanosecond;
-- handling leap years
DECLARE
@PostLeapDay DATETIME2(7) = '2012-03-01 18:00:00',
@TwoDaysAgo DATETIME2(7);
SELECT
@TwoDaysAgo = DATEADD(DAY, -2, @PostLeapDay);
SELECT
@TwoDaysAgo AS TwoDaysAgo,
@PostLeapDay AS SomeTime,
-- Fill in the appropriate function and date types
DATEDIFF(DAY, @TwoDaysAgo, @PostLeapDay) AS DaysDifference,
DATEDIFF(HOUR, @TwoDaysAgo, @PostLeapDay) AS HoursDifference,
DATEDIFF(MINUTE, @TwoDaysAgo, @PostLeapDay) AS MinutesDifference;
--NESTING USING CAST()
DECLARE
@CubsWinWorldSeries DATETIME2(3) = '2016-11-03 00:30:29.245';
SELECT
cast(cast(@CubsWinWorldSeries AS date) AS NVARCHAR(30)) AS DateStringForm;
--USING CONVERT
DECLARE
@CubsWinWorldSeries DATETIME2(3) = '2016-11-03 00:30:29.245';
SELECT
CONVERT(NVARCHAR(30), @CubsWinWorldSeries, 0) AS DefaultForm,
CONVERT(NVARCHAR(30), @CubsWinWorldSeries, 3) AS UK_dmy,
CONVERT(NVARCHAR(30), @CubsWinWorldSeries, 1) AS US_mdy,
CONVERT(NVARCHAR(30), @CubsWinWorldSeries, 103) AS UK_dmyyyy,
CONVERT(NVARCHAR(30), @CubsWinWorldSeries, 101) AS US_mdyyyy;
--FORMATTING DATES WITH FORMAT()
DECLARE
@Python3ReleaseDate DATETIME2(3) = '2008-12-03 19:45:00.033';
SELECT
-- Fill in the function call and format parameter
FORMAT(@Python3ReleaseDate, 'd', 'en-US') AS US_d,
FORMAT(@Python3ReleaseDate, 'd', 'de-DE') AS DE_d,
-- Fill in the locale for Japan
FORMAT(@Python3ReleaseDate, 'd', 'jp-JP') AS JP_d,
FORMAT(@Python3ReleaseDate, 'd', 'zh-cn') AS CN_d;
DECLARE
@Python3ReleaseDate DATETIME2(3) = '2008-12-03 19:45:00.033';
SELECT
-- 20081203
FORMAT(@Python3ReleaseDate, 'yyyyMMdd') AS F1,
-- 2008-12-03
FORMAT(@Python3ReleaseDate, 'yyyy-MM-dd') AS F2,
-- Dec 03+2008 (the + is just a "+" character)
FORMAT(@Python3ReleaseDate, 'MMM dd+yyyy') AS F3,
-- 12 08 03 (month, two-digit year, day)
FORMAT(@Python3ReleaseDate, 'MM yy dd') AS F4,
-- 03 07:45 2008.00
-- (day hour:minute year.second)
FORMAT(@Python3ReleaseDate, 'dd hh:mm yyyy.ss') AS F5;
--Using calendar tables
SELECT
c.Date
FROM dbo.calendar c
WHERE
c.MonthName = 'December'
AND c.DayName = 'Tuesday'
AND c.CalendarYear BETWEEN 2008 AND 2010
ORDER BY
c.Date;
-- Find fiscal week 29 of fiscal year 2019
SELECT
c.Date
FROM dbo.Calendar c
WHERE
-- Instead of month, use the fiscal week
c.FiscalWeekOfYear = 29
-- Instead of calendar year, use fiscal year
AND c.FiscalYear = 2019
ORDER BY
c.Date ASC;Building Dates From Parts 2/20/23
SQL server has 6 functions to build dates from components: DATEFROMPARTS(YEAR, MONTH, DAY) TIMEFROMPARTS(hours, minute, second, fraction, precision) DATETIMEFROMPARTS(year, month, day, hour, minute, second, ms) DATETIME2FROMPARTS(year, month, day, hour, minute, second, fraction, precision) SMALLDATETIMEFROMPARTS(year, month, day, hour, minute) DATETIMEOFFSETFROMPARTS(year, month, day, hour, minute, second, fraction, hour_offset, muinute_offset, precision)
Translating Date Strings: use cast() as default. Cast func is language and locale-specific eg. SELECT CAST('09/14/99' AS Date)
Parsse() useful for translating non default locales. use for small datasets or if you are willing to trade speed for flexibility. eg. SELECT PARSE('25 Dezember 2014' AS DATE USING 'de-de') AS Weihnachten;
2/21/23 DATETIMEOFFSET DATETIMEOFFSET made of 3 components: Date Time Utc Offset
Swiwtchoffset used to change time zone of a DateTime or DateTime2 Swiwtchoffset takes 2 parameters: the date string as input & time zone Swiwtchoffset allows for changes to time in input string sys.time_zone_info : to find out time zones
TODATETIMEOFFSET() to turn an existing date into a date type with an offset.
Handling invalid Dates: Safe Functions: try_Cast() Try_Convert() Try_Parse()
Only use Try_Parse when performance is not critical. Try_Cast & Try_Convert are faters than Try_Parse, but Try_Parse is more efficient.
2/23/23 ABasic aggregate funcs: COUNT DETERMINES THE TOTAL AMOUNT OF ROWS Count calls include: COUNT() COUNT(BIG) COUNT(DISTINCT): counts unique elements
Non-null values: COUNT(d.TR): if you specify a column gives you values where that column is not null Count(NULLIF(d.Tr, 1990))
Statistical aggreagte funcs: AVG() calcs mean STDEV() calcs standard deviation STDEVP() calc population standard deviation VAR() variance VARP() population variance
PERCENTILE_CONT() func is used to calc median has a parameter that goes into parentheses
3/23/23
Downsampling and upsampling data:
Downsampling: Upsampling: Aggregate data Disaggregate data sums or counts results need allocation rule Acceptable for most purposes for data generation, calculated averages