Course Notes
Use this workspace to take notes, store sample queries, and build your own interactive cheat sheet!
You will 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.
Note: When using sample integrations such as those that contain course data, you have read-only access. You can run queries, but cannot make any changes such as adding, deleting, or modifying the data (e.g., creating tables, views, etc.).
Take Notes
Add notes here about the concepts you've learned and SQL cells with code you want to keep.
Add your notes here
FUNCTIONS FOR MANIPULATING DATA IN SQL SERVER
MANIPULATING TIME
Common mistakes when working with dates and times
Inconsistent date formats Arithmetic operationos Issues with time zones
Time zones in SQL Local time zone UTC time zone
Functions that return date and time of OS Higherr-precision: SYSDATETIME(): RETURNS DATE AND TIME WITHOUT TIMEZONE SYSUTCDATETIME(): RETURNS COMPUTERS DATE AND TIME AS UTC SYSDATETIMEOFFSET():RETURNS DATE AND TIME WITH TIMEZONE OFFSET
lower-precision: GETDATE(): RETURNS DATE GETUTCDATE(): RETURNS DATE AS UTC
fUNCTIONS FOR RETURNING DATE AND TIME YEAR(date): Returns the year from the specifies date Month(date): Reuslts are ints same as year func DATENAME(DATEPART, DATE): RETURNS CHARACTER STRING REPRESENTING THE SPECIFIED DATE PART OF SAID DATE. HAS (DAYOFYEAR) AND (WEEKDAY) DATEPARTS DATEPART(DATEPART, DATE): SIMILAR TO DATENAME BUT RETURNS INT DATEFROMPARTS(YEAR, MONTH, DAY)
Performing arithmetic operations on dates
Types of operations with dates: operations using arithmetic operators (+, -) modify value of date w DATEADD(datepart, number, date) return the difference between 2 dates w DATEDIFF(datepart, startdate, enddate)
Validating if an expression is a date ISDATE(expression): determines if an expression is a valid data type SET DATEFORMAT {FORMAT}: sets order of date parts for interpreting strings as dates. Valid formats include: mdy, dmy, ymd, myd, dym SET LANGUAGE {LANGUAGE}: sets language for session, also changes SET DATEFORMAT, valid langauges include: English, italian, spanish.
WORKING WITH STRINGS
funcs for positions gonna be manipulating strings. used for data cleansing and validation funcs returning posiotion of an expression within a string LEN(character_expression): returns number of characters in a string, excluding blanks CHARINDEX(expression_to_find, expression_to_search, [start_location]) looks for character position in string, returns its starting position, 2 mandatory parameters and 1 optional. PATINDEX('%pattern%', expression, [location]): similar to CHARINDEX(), Returns starting position of a pattern in an expression. PATINDEX('%a%w%', first_name) > 0 : to find name containing "a" followed by other letters, then "w" follwed by other letters.
WHERE PATINDEX('%[xwq]%', first_name) > 0: to find whose first name contains one of these letters: "x", "w" or "q"
Functions for string transformation:
2 simplest funcs for string transformation: LOWER(expression): converts string to lower case UPPER(expression): converts string to uppercase
LEFT(expression, number_of_characters): specified number of characters from beginning. RIGHT(expression, number_of_characters): specified number of characters from beginning.
LTRIM(expression): returns string after removing the leading blanks RTRIM(expression): returns string after removing the trailing blanks TRIM([characters FROM], character_expression): returns string after removing blanks or other specified characters
REPLACE(EXPRESSION, SEARCHED_EXPRESSION, REPLACEMENT_EXPRESSION): returns string where all occurences of an expression are replaced with another one
SUBSTRING(expression, start, number_of_characters): returns part of string
Functions for manipulating groups of strings:
CONCAT(string1, string2 [, stringN]): joins value tg CONCAT_WS(separator, string1, string2 [, stringN]): means concatenate with seperator, receives charatcter value as 1st parameter
STRING_AGG(expression, seperator) [<order_clause>]: concatenates value of string expressions and places separator values between them. when u want to use order clause after STRING_AGG func use; WITHIN GROUP (ORDER BY expression)
STRING_SPLIT(string, separator): divides string into samller pieces, based on a separator. Returns single column table
Recognizing Numeric Data Properties
Aggregate arithemetic funcs: COUNT(): RETURNS # of items in group SUM(): returns sum of all values MAX(): returns max value in expression MIN(): returns min value in expression AVG(): returns avg of values in group
For applying aggregate functions on groups of data, you need to use the GROUP BY statement. aggregate funcs work great with group data and can be used in ORDER BY clause.
Analytic funcs: works on each row instead of group data, applied to first row by default until current row
FIRST_VALUE(numeric expression): returns first value in an ordered set, used in combinatin with over clause. LAST_VALUE(): returns last value in ordered set. If you want funcs to be applied on all rows in a partition, you can add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to the OVER() clause.
LAG(): Accesses data from a previous row in the same result set. use PARTITION BY column syntax in the OVER() clause to apply the funcs on subsets. LEAD(): Access values from subsequent row in the same result set. only works with ordered set of results and you nedd to use it in combination with OVER() clause.
Mathematical funcs:
ABS(numeric_expression): Returns the absolute value of an expression Is non negative value of expression
SIGN(numeric_expression): Returns the sign of an expression, as an integer: -1 (negative numbers) 0 +1 (positive numbers
Rounding funcs : used to create approximations of an expression Ceiling(): returns the smallest integer greater than or equal to the expression
Floor(): returns largest integer less than or equal to the expression
Round(numerric_expression, length): Returns a numeric value, rounded to specified length.
Exponential funcs: POWER(): returns expression raised to specified power.
SQUARE(): returns square
SQRT(): returns square root