Cheat Sheet for SQL Functions

SQL Functions

SQL supports a variety of built-in functions that can be categorized into several groups based on their purposes. Here’s a list of commonly used SQL functions, organized by category:

Aggregate Functions

  1. SUM(): Calculates the sum of values in a column.
  2. AVG(): Calculates the average value in a column.
  3. COUNT(): Counts the number of rows or non-null values in a column.
  4. MAX(): Returns the maximum value in a column.
  5. MIN(): Returns the minimum value in a column.
  6. GROUP_CONCAT() (MySQL, SQLite) or STRING_AGG() (SQL Server): Concatenates values into a single string within a group.
  7. ARRAY_AGG() (PostgreSQL): Aggregates values into an array within a group.

Math Functions

  1. +, -, *, /, %: Arithmetic operators.
  2. DIV: Integer division 5 DIV 2.
  3. ABS(): Returns the absolute value of a number.
  4. ROUND(): Rounds a number to a specified number of decimal places.
  5. CEIL() or CEILING(): Rounds a number up to the nearest integer.
  6. FLOOR(): Rounds a number down to the nearest integer.
  7. POWER(): Raises a number to a specified power.
  8. SQRT(): Calculates the square root of a number.
  9. LOG() or LN(): Calculates the natural logarithm of a number.
  10. EXP(): Calculates the exponential value of a number.
  11. PI() (MySQL and PostgreSQL): Returns the mathematical constant π\pi (pi).

Conditional Functions

  1. CASE WHEN ... THEN ... ELSE ... END: Performs conditional logic.
  2. COALESCE() (Database-Specific): Returns the first non-null value in a list.
  3. NULLIF() (Database-Specific): Compares two expressions and returns null if they are equal.

String Functions

  1. CONCAT(): Concatenates two or more strings.
  2. CONCAT_WS(): Concatenates values with a specified separator.
  3. LENGTH() or LEN(): Returns the length (number of characters) of a string.
  4. SUBSTRING() or SUBSTR(): Extracts a substring from a string.
  5. REPLACE(): Replaces all occurrences of a substring within a string.
  6. UPPER(): Converts a string to uppercase.
  7. LOWER(): Converts a string to lowercase.
  8. TRIM(): Removes leading and trailing spaces from a string.
  9. LEFT(): Returns a specified number of characters from the left of a string.
  10. RIGHT(): Returns a specified number of characters from the right of a string.
  11. POSITION() or CHARINDEX(): Finds the position of a substring within a string.
  12. INITCAP() (PostgreSQL): Capitalizes the first letter of each word in a string.
  13. Full-Text Search Functions (Database-Specific) (e.g., CONTAINS, FREETEXT, MATCH()) for performing advanced text search and ranking in text-based columns.

Date and Time Functions

  1. CURRENT_DATE: Returns the current date.
  2. CURRENT_TIME: Returns the current time.
  3. CURRENT_TIMESTAMP: Returns the current timestamp (date and time).
  4. DATE(): Extracts the date portion from a timestamp.
  5. TIME(): Extracts the time portion from a timestamp.
  6. YEAR(): Returns the year from a date or timestamp.
  7. MONTH(): Returns the month from a date or timestamp.
  8. DAY(): Returns the day of the month from a date or timestamp.
  9. HOUR(): Returns the hour from a time or timestamp.
  10. MINUTE(): Returns the minute from a time or timestamp.
  11. SECOND(): Returns the second from a time or timestamp.
  12. DATEADD() (SQL Server) or INTERVAL (PostgreSQL): Adds a specified interval to a date or timestamp.
  13. DATEDIFF() (SQL Server) or DATE_PART() (PostgreSQL): Calculates the difference between two dates or timestamps.
  14. EXTRACT() (PostgreSQL): Extracts components (e.g., year, month, day) from a date or timestamp.
  15. TIMESTAMPDIFF() (MySQL) or EXTRACT() (Oracle): Calculates the difference between two dates or timestamps in a specific unit (e.g., days, hours).

Conversion Functions

  1. TO_CHAR(): Converts a value to a character string with a specified format (typically used for date and time formatting).
  2. TO_DATE(): Converts a character string to a date.
  3. TO_NUMBER(): Converts a character string to a number.
  4. CAST(): Converts one data type to another.
  5. CONVERT(): Converts one data type to another (database-specific).

Other Functions

  1. IFNULL() or ISNULL(): Checks if a value is null and returns an alternative value.

The availability of these functions may vary depending on the specific database system you are using (e.g., MySQL, PostgreSQL, SQL Server, Oracle, etc.). Additionally, some databases provide additional custom functions and extensions. Be sure to refer to your database system’s documentation for a comprehensive list of functions and their usage.

Comments

Post a Comment

Popular posts from this blog

Slang Terms About Money

Term Meaning Example Buck $1 “It costs 20 bucks.” K $1,000 “He makes 100K a year.” Dime $10 (or a tenth of a dollar) “I spent a dime on snacks.” Bill $100 “That’s a couple of bills.” C-note $100 “He handed me a C-note.” Benjamin $100 “I got paid in Benjamins.” Stack/Rack $1,000 “He dropped 5 racks on a new car.” G $1,000 “The job pays 50G a year.” Grand $1,000 “The car costs 20 grand.” Green Money in general “He’s got a lot of green.” Cheddar/Dough/Bread Money in general “Making bread is important.” Smackers/Clams/Bones Dollars (old-fashioned) “It cost me 50 smackers.”
Read more

Workaround for macOS Dictionary All Tab Issue

After installing custom dictionaries on macOS, you may encounter an issue where the All tab in the built-in Dictionary app does not display correctly. The main problem is that the CFBundleIdentifier key in the Info.plist file of the custom dictionaries is either missing or not unique. Here’s a step-by-step workaround I found to resolve the issue: Steps to Fix the Issue: Locate the ~/Library/Dictionaries directory. To do this, open Finder, press Cmd + Shift + G , and enter ~/Library/Dictionaries (the Library folder is hidden by default). Inside the Dictionaries folder, you will find a list of .dictionary files, which represent the custom dictionaries you have installed. To fix the issue, check the Info.plist file of each dictionary. Navigate to the .dictionary folder, then go to the Contents subfolder, where you will find the Info.plist file. Open the Info.plist file using a text editor or a plist editor. Look for the CFBundleIdentifier key in the ...
Read more

Mathematical Objects

A cheat sheet for mathematical objects and structures, useful for quick reference. Foundations of Sets and Relations Set A collection of distinct objects, often written as X = { x ∣ P ( x ) } X = \{x \mid P(x)\} X = { x ∣ P ( x )} , where P ( x ) P(x) P ( x ) is a property. No additional structure. Map / Function A mapping f : X → Y f: X \to Y f : X → Y assigns each x ∈ X x \in X x ∈ X to a unique y ∈ Y y \in Y y ∈ Y . Relation A subset R ⊆ X × X R \subseteq X \times X R ⊆ X × X , such as equivalence relations or partial orders. Basic Algebraic Structures Semigroup A set S S S with a binary operation ⋅ : S × S → S \cdot: S \times S \to S ⋅ : S × S → S such that: Associativity: For all a , b , c ∈ S a, b, c \in S a , b , c ∈ S , ( a ⋅ b ) ⋅ c = a ⋅ ( b ⋅ c ) (a \cdot b) \cdot c = a \cdot (b \cdot c) ( a ⋅ b ) ⋅ c = a ⋅ ( b ⋅ c ) . Monoid A semigroup with an identity element e ∈ M e \in M e ∈ M , satisfying: Identity: a ⋅ e = e ⋅ a = a ...
Read more

Essential Utilities for LaTeX Package and Class Development

Developing LaTeX packages or classes is not a common task for most users, who typically rely on pre-existing packages. According to the CTAN: Comprehensive TeX Archive Network , there are only 3,040 TeX developers worldwide (as of February 2025). As a result, online resources for LaTeX package and class development are relatively scarce. From my perspective, there isn’t currently a comprehensive guide to the utility commands essential for package development. So, I’ve compiled this blog post, gathering a set of useful commands and packages I’ve discovered or developed through experience. I hope it proves helpful for anyone who is developing—or planning to develop—LaTeX packages or classes. This post also serves as a personal reference for me to revisit the next time I work on a package. This post will be a dynamic document, and I’ll update it as I discover new commands. If you have any useful commands that I haven’t mentioned, please feel free to share them in the ...
Read more

Train PyTorch with Checkpoints

Training a PyTorch model with checkpoints is an important technique to ensure that the model is saved periodically during the training process. This helps to avoid losing all of the progress made during the training if there is a power failure, hardware failure or any other unexpected problem. To train a PyTorch model with checkpoints, you can follow these steps: Import the necessary libraries and packages. import torch import torch . nn as nn import torch . optim as optim from torch . utils . data import DataLoader Define your model architecture. class MyModel ( nn . Module ) : def __init__ ( self ) : super ( MyModel , self ) . __init__ ( ) self . fc1 = nn . Linear ( 784 , 128 ) self . fc2 = nn . Linear ( 128 , 64 ) self . fc3 = nn . Linear ( 64 , 10 ) def forward ( self , x ) : x = x . view ( x . size ( 0 ) , - 1 ) x = self . fc1 ( x ) x = nn . functional . relu ...
Read more