Other Resources

• LinkedIn SQL interview questions booklet
• Interview Questions: YT video, blog
• Practice Questions: YT video, blog

1. Working with dates (Docs)

• There are four major date/time data types:
  • Date (date '2001-09-28’)
  • Time (time '01:00’): Two variants → with & without timezone
  • Timestamp (timestamp '2001-09-28 01:00’): Two variants → with & w/o timezone
  • Interval (interval '1 day’)
• Use operators when working with above data types, refer.

<aside> 💡 Intervals stand in contrast to SQL's treatment of DATE types. Dates don't use intervals - instead, subtracting two dates will return an integer representing the number of days between the two dates. You can also add integer values to dates. This is sometimes more convenient, depending on how much intelligence you require in the handling of your dates!

</aside>

• Data/time Functions, here are some important ones:

  • CURRENT_TIME and CURRENT_TIMESTAMP / now() deliver values with timezone; LOCALTIME and LOCALTIMESTAMP  deliver values without time zone.

    CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, and LOCALTIMESTAMP can optionally take a precision parameter, which causes the result to be rounded to that many fractional digits in the seconds field. Without a precision parameter, the result is given to the full available precision.

    CURRENT_DATE
    CURRENT_TIME
    CURRENT_TIMESTAMP
    CURRENT_TIME(precision)
    CURRENT_TIMESTAMP(precision)
    LOCALTIME
    LOCALTIMESTAMP
    LOCALTIME(precision)
    LOCALTIMESTAMP(precision)
    

  • date_trunc: same as date_part but truncates to specified precision.

    SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
    Result:2001-02-16 20:00:00
    
    SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
    Result:2001-01-01 00:00:00
    
    SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00');
    Result:2001-02-16 00:00:00-05
    
    SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00', 'Australia/Sydney');
    Result:2001-02-16 08:00:00-05
    
    SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');
    Result:3 days 02:00:00
    

    Definition: date_trunc(***field***, ***source*** [, ***time_zone*** ])

    *source* is a value expression of type timestamp, timestamp with time zone, or interval. (Values of type date and time are cast automatically to timestamp or interval, respectively.) *field* selects to which precision to truncate the input value. The return value is likewise of type timestamp, timestamp with time zone, or interval, and it has all fields that are less significant than the selected one set to zero (or one, for day and month).

    Valid values for *field* are:

    • microseconds, milliseconds, second, minute, hour
    • day, week, month, quarter, year, decade, century, millennium

  • date_part / extract: ex: date_part('hour', timestamp '2001-02-16 20:38:40') → 20. Also used to extract parts from interval. Read more, here!

    Some fields present only in date_part (not in date_trunc):

    • dow: The day of the week as Sunday (0) to Saturday (6)
    • isodow: ISO format, the day of the week as Monday (1) to Sunday (7)
    • doy: The day of the year (1–365/366)
    • epoch: For timestamp with time zone values, the number of seconds since 1970-01-01 00:00:00 UTC

2. Working with strings

Functions and Operators (Docs)

• || : String concatenate, ex: 'Post' || 'greSQL' → PostgreSQL. Note: Can also be used to concatenate any non-array type to string.

• char_length / length: number of characters in a string. Ex: char_length('SQL') → 3

• Common (lower, upper, reverse, repeat)

• Trimming (ltrim, rtrim, trim): Removes the longest string containing only characters in *characters* (a space by default) from the end of *string*. Ex: rtrim('testxxzx', 'xyz') → test and ltrim('zzzytest', 'xyz') → test.

  • Definition: trim ( [ LEADING | TRAILING | BOTH ] [ *characters* text ] FROM *string* text ) → text

    Removes the longest string containing only characters in *characters* (a space by default) from the start, end, or both ends (BOTH is the default) of *string*. Ex: trim(both 'xyz' from 'yxTomxx') → Tom

• Padding (lpad, rpad): Extends the *string* to length *length* by appending the characters *fill* (a space by default). If the *string* is already longer than *length* then it is truncated. Ex: rpad('hi', 5, 'xy') → hixyx and lpad('hi', 5, 'xy') → xyxhi

• Substring:

  • Definition: substring ( *string* text [ FROM *start* integer ] [ FOR *count* integer ] ) → text

    Extracts the substring of *string* starting at the *start*'th character if that is specified, and stopping after *count* characters if that is specified. Provide at least one of *start* and *count*. Examples:

    • substring('Thomas' from 2 for 3) → hom
    • substring('Thomas' from 3) → omas
    • substring('Thomas' for 2) → Th

  • Definition: substr ( *string* text, *start* integer [, *count* integer ] ) → text

    Extracts the substring of *string* starting at the *start*'th character, and extending for *count* characters if that is specified. (Same as substring(*string* from *start* for *count*).) Ex:

    • substr('alphabet', 3) → phabet
    • substr('alphabet', 3, 2) → ph

  • Definition: left ( *string* text, *n* integer ) → text

    Returns first *n* characters in the string, or when *n* is negative, returns all but last |*n*| characters. Ex: left('abcde', 2) → ab. Similarly right.

  • Definition: split_part ( *string* text, *delimiter* text, *n* integer ) → text

    Splits *string* at occurrences of *delimiter* and returns the *n*'th field (counting from one), or when *n* is negative, returns the |*n*|'th from last field. Ex:

    • split_part('abc~@~def~@~ghi', '~@~', 2) → def
    • split_part('abc,def,ghi,jkl', ',', -2) → ghi

  • string_to_array and string_to_table for splitting text based on delimiter.

  • regex_string_to_array and regex_string_to_table for splitting text using regex as delimiter.

• Substitution:

  • Definition: replace ( *string* text, *from* text, *to* text ) → text

    Replaces all occurrences in *string* of substring *from* with substring *to*. Ex: replace('abcdefabcdef', 'cd', 'XX') → abXXefabXXef

  • Definition: regexp_replace ( *string* text, *pattern* text, *replacement* text, *start* integer, *N* integer [, *flags* text ] ) → text

    Replaces the substring that is the *N*'th match to the POSIX regular expression *pattern*, or all such matches if *N* is zero; see Section 9.7.3. Ex, regexp_replace('Thomas', '.', 'X', 3, 2) → ThoXas