DSA Companion

All About SQL

Collection of all the important SQL queries & LeetCode problems

LeetCode SQL problems coming soon!

Tap on the topic name to expand

Keywords

Keywords are the reserved words in SQL. These are the words that cannot be used as identifiers. These are the words that are used to define the syntax and structure of the SQL statements. These are the words that are used to define the data types, functions, and other elements in SQL.

ADD

The ADD keyword is used to add a column to an existing table.

ADD column_name datatype

ADD CONSTRAINT

The ADD CONSTRAINT keyword is used to add a constraint to an existing table.

ADD CONSTRAINT constraint_name constraint_type

ALL

The ALL keyword is used to return all the rows from a table that satisfy a condition.

SELECT column_name(s) FROM table_name WHERE column_name operator ALL (SELECT column_name FROM table_name WHERE condition);

ALTER TABLE

The ALTER TABLE keyword is used to add, delete, or modify columns in an existing table.

ALTER TABLE table_name ADD column_name datatype

AND

The AND keyword is used to combine two or more conditions in a WHERE clause, a HAVING clause, or a Boolean expression.

SELECT column_name(s) FROM table_name WHERE condition1 AND condition2 AND condition3 ...

ANY

The ANY keyword is used to return all the rows from a table that satisfy a condition.

SELECT column_name(s) FROM table_name WHERE column_name operator ANY (SELECT column_name FROM table_name WHERE condition);

The AS keyword is used to assign an alias to a table or a column.

SELECT column_name AS alias_name FROM table_name

ASC

The ASC keyword is used to sort the result-set in ascending order.

SELECT column_name(s) FROM table_name ORDER BY column_name ASC

BETWEEN

The BETWEEN keyword is used to filter the result-set within a certain range.

SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2

The BY keyword is used to sort the result-set in ascending or descending order.

SELECT column_name(s) FROM table_name ORDER BY column_name ASC|DESC

CASE

The CASE keyword is used to create different outputs (usually in the SELECT statement). It is SQL's way of handling if-then-else logic.

SELECT column_name, CASE WHEN condition THEN result END FROM table_name

CHECK

The CHECK keyword is used to limit the value range that can be placed in a column.

CREATE TABLE table_name (column_name datatype CHECK (condition));

COLUMN

The COLUMN keyword is used to specify the column name in a table.

SELECT column_name(s) FROM table_name

CONSTRAINT

The CONSTRAINT keyword is used to specify a constraint on a table.

CREATE TABLE table_name (column_name datatype CONSTRAINT constraint_name);

CREATE

The CREATE keyword is used to create a database, a table, a view, a stored procedure, a function, an index, or a trigger.

CREATE DATABASE database_name

DATABASE

The DATABASE keyword is used to create a database.

CREATE DATABASE database_name

DEFAULT

The DEFAULT keyword is used to provide a default value for a column.

CREATE TABLE table_name (column_name datatype DEFAULT default_value);

DELETE

The DELETE keyword is used to delete records from a table.

DELETE FROM table_name WHERE condition

DESC

The DESC keyword is used to sort the result-set in descending order.

SELECT column_name(s) FROM table_name ORDER BY column_name DESC

DISTINCT

The DISTINCT keyword is used to return only distinct (different) values.

SELECT DISTINCT column_name(s) FROM table_name

DROP

The DROP keyword is used to delete objects from the database.

DROP DATABASE database_name

ELSE

The ELSE keyword is used with the CASE statement to specify a different result if none of the conditions are true.

SELECT column_name, CASE WHEN condition THEN result ELSE result END FROM table_name

END

The END keyword is used to mark the end of a CASE statement.

SELECT column_name, CASE WHEN condition THEN result ELSE result END FROM table_name

EXISTS

The EXISTS keyword is used to test for the existence of any record in a subquery.

SELECT column_name(s) FROM table_name WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);

FOREIGN KEY

The FOREIGN KEY keyword is used to prevent actions that would destroy links between tables.

CREATE TABLE table_name (column_name datatype, FOREIGN KEY (column_name) REFERENCES table_name);

FROM

The FROM keyword is used to specify the list of tables to be used in the query.

SELECT column_name(s) FROM table_name

FULL OUTER JOIN

The FULL OUTER JOIN keyword returns all records when there is a match in either left (table1) or right (table2) table records.

SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name=table2.column_name

GROUP BY

The GROUP BY keyword is used to group the result-set by one or more columns.

SELECT column_name, aggregate_function(column_name) FROM table_name WHERE condition GROUP BY column_name

HAVING

The HAVING keyword is used to filter records based on a condition after the GROUP BY clause.

SELECT column_name, aggregate_function(column_name) FROM table_name WHERE condition GROUP BY column_name HAVING condition

The IN keyword is used to filter the result-set within a list of values.

SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...)

INDEX

The INDEX keyword is used to create and retrieve index information from the database.

CREATE INDEX index_name ON table_name (column_name);

INNER JOIN

The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables.

SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name

INSERT INTO

The INSERT INTO keyword is used to insert new records in a table.

INSERT INTO table_name VALUES (value1, value2, value3, ...)

INSERT INTO SELECT

The INSERT INTO SELECT statement copies data from one table and inserts it into another table.

INSERT INTO table2 SELECT column_name(s) FROM table1 WHERE condition

IS NULL

The IS NULL keyword is used to test for an empty value (NULL).

SELECT column_name(s) FROM table_name WHERE column_name IS NULL

IS NOT NULL

The IS NOT NULL keyword is used to test for any value other than NULL.

SELECT column_name(s) FROM table_name WHERE column_name IS NOT NULL

JOIN

The JOIN keyword is used to combine rows from two or more tables, based on a related column between them.

SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name

LEFT JOIN

The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.

SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name=table2.column_name

The LIKE keyword is used in a WHERE clause to search for a specified pattern in a column.

SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern

LIMIT

The LIMIT keyword is used to specify the maximum number of records to be returned by the query.

SELECT column_name(s) FROM table_name LIMIT number

NOT

The NOT keyword is used to reverse the result of a condition.

SELECT column_name(s) FROM table_name WHERE NOT condition

NOT NULL

The NOT NULL constraint ensures that a column cannot have a NULL value.

CREATE TABLE table_name (column_name datatype NOT NULL);

The OR keyword is used to filter records where any of the conditions is true.

SELECT column_name(s) FROM table_name WHERE condition1 OR condition2

ORDER BY

The ORDER BY keyword is used to sort the result-set in ascending or descending order.

SELECT column_name(s) FROM table_name ORDER BY column_name ASC|DESC

OUTER JOIN

The OUTER JOIN keyword returns all records when there is a match in either left (table1) or right (table2) table records.

SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name=table2.column_name

PRIMARY KEY

The PRIMARY KEY constraint uniquely identifies each record in a table.

CREATE TABLE table_name (column_name datatype PRIMARY KEY);

RIGHT JOIN

The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match.

SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name=table2.column_name

SELECT

The SELECT statement is used to select data from a database.

SELECT column_name(s) FROM table_name

SELECT DISTINCT

The SELECT DISTINCT statement is used to return only distinct (different) values.

SELECT DISTINCT column_name(s) FROM table_name

SELECT TOP

The SELECT TOP statement is used to specify the number of records to return.

SELECT TOP number|percent column_name(s) FROM table_name

SELECT INTO

The SELECT INTO statement copies data from one table into a new table.

SELECT column_name(s) INTO new_table_name [IN external_database] FROM existing_table_name [WHERE condition]

SET

The SET statement is used to modify the data that already exists in a database.

UPDATE table_name SET column1=value, column2=value2,... WHERE some_column=some_value

TABLE

The TABLE keyword is used to create a new table in a database.

CREATE TABLE table_name (column_name datatype);

UNION

The UNION operator is used to combine the result-set of two or more SELECT statements.

SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2

UNION ALL

The UNION ALL operator is used to combine the result-set of two or more SELECT statements.

SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2

UPDATE

The UPDATE statement is used to modify the existing records in a table.

UPDATE table_name SET column1=value, column2=value2,... WHERE some_column=some_value

VALUES

The VALUES keyword is used along with the INSERT INTO statement to insert one or more specific values into a table.

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...)

WHERE

The WHERE clause is used to filter records.

SELECT column_name(s) FROM table_name WHERE condition

String Data Types

String data types are used to store character strings.

CHAR

The CHAR data type is used to store a fixed-length string (can contain letters, numbers, and special characters). The fixed length is specified in the column definition.

CREATE TABLE table_name (column_name CHAR(20));

VARCHAR

The VARCHAR data type is used to store a variable-length string (can contain letters, numbers, and special characters). The maximum length is specified in the column definition.

CREATE TABLE table_name (column_name VARCHAR(20));

TEXT

The TEXT data type is used to store a large amount of text (can contain letters, numbers, and special characters).

CREATE TABLE table_name (column_name TEXT);

BINARY

The BINARY data type is used to store a fixed-length string of binary values. The fixed length is specified in the column definition.

CREATE TABLE table_name (column_name BINARY(20));

VARBINARY

The VARBINARY data type is used to store a variable-length string of binary values. The maximum length is specified in the column definition.

CREATE TABLE table_name (column_name VARBINARY(20));

BLOB

The BLOB data type is used to store a large amount of binary values.

CREATE TABLE table_name (column_name BLOB);

ENUM

The ENUM data type is used to store a list of possible values. You can assign a default value. If no default value is assigned, the first value in the list is used as the default value.

CREATE TABLE table_name (column_name ENUM('value1', 'value2', 'value3') DEFAULT 'value2');

SET

The SET data type is used to store a list of possible values. You can assign a default value. If no default value is assigned, the first value in the list is used as the default value.

CREATE TABLE table_name (column_name SET('value1', 'value2', 'value3') DEFAULT 'value2');

Numeric Data Types

Numeric data types are used to store numeric values.

INT

The INT data type is used to store integers (whole numbers).

CREATE TABLE table_name (column_name INT);

TINYINT

The TINYINT data type is used to store very small integers. The signed range is -128 to 127. The unsigned range is 0 to 255.

CREATE TABLE table_name (column_name TINYINT);

SMALLINT

The SMALLINT data type is used to store small integers. The signed range is -32768 to 32767. The unsigned range is 0 to 65535.

CREATE TABLE table_name (column_name SMALLINT);

MEDIUMINT

The MEDIUMINT data type is used to store medium-sized integers. The signed range is -8388608 to 8388607. The unsigned range is 0 to 16777215.

CREATE TABLE table_name (column_name MEDIUMINT);

BIGINT

The BIGINT data type is used to store large integers. The signed range is -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615.

CREATE TABLE table_name (column_name BIGINT);

DECIMAL

The DECIMAL data type is used to store fixed-point numbers. The fixed-point number has a fixed decimal point. The maximum number of digits is specified in the column definition.

CREATE TABLE table_name (column_name DECIMAL(5,2));

FLOAT

The FLOAT data type is used to store floating-point numbers. The floating-point number has a floating decimal point. The maximum number of digits is specified in the column definition.

CREATE TABLE table_name (column_name FLOAT(5,2));

DOUBLE

The DOUBLE data type is used to store large floating-point numbers. The floating-point number has a floating decimal point. The maximum number of digits is specified in the column definition.

CREATE TABLE table_name (column_name DOUBLE(5,2));

REAL

The REAL data type is used to store large floating-point numbers. The floating-point number has a floating decimal point. The maximum number of digits is specified in the column definition.

CREATE TABLE table_name (column_name REAL(5,2));

BIT

The BIT data type is used to store bit values. The maximum number of bits is specified in the column definition.

CREATE TABLE table_name (column_name BIT(5));

BOOLEAN

The BOOLEAN data type is used to store a logical value (TRUE or FALSE).

CREATE TABLE table_name (column_name BOOLEAN);

SERIAL

The SERIAL data type is used to store a unique sequential number. The number is automatically incremented by 1 whenever a new record is inserted.

CREATE TABLE table_name (column_name SERIAL);

Date/Time Data Types

Date/Time data types are used to store date and time values.

DATE

The DATE data type is used to store a date value (year, month, day).

CREATE TABLE table_name (column_name DATE);

DATETIME

The DATETIME data type is used to store a date and time value (year, month, day, hour, minute, second).

CREATE TABLE table_name (column_name DATETIME);

TIMESTAMP

The TIMESTAMP data type is used to store a date and time value (year, month, day, hour, minute, second). The TIMESTAMP value is automatically updated whenever a record is updated.

CREATE TABLE table_name (column_name TIMESTAMP);

TIME

The TIME data type is used to store a time value (hour, minute, second).

CREATE TABLE table_name (column_name TIME);

YEAR

The YEAR data type is used to store a year value (year).

CREATE TABLE table_name (column_name YEAR);

Arithmetic Operators

Arithmetic operators are used to perform arithmetic operations on numeric values.

The + operator adds two numeric values.

SELECT 5 + 3 AS Result;

The - operator subtracts two numeric values.

SELECT 5 - 3 AS Result;

The * operator multiplies two numeric values.

SELECT 5 * 3 AS Result;

The / operator divides two numeric values.

SELECT 5 / 3 AS Result;

The % operator returns the remainder of two numeric values.

SELECT 5 % 3 AS Result;

The ++ operator increments a numeric value by 1.

SELECT 5++ AS Result;

The -- operator decrements a numeric value by 1.

SELECT 5-- AS Result;

Comparison Operators

Comparison operators are used to compare two values.

The = operator checks if the values of two operands are equal or not, if yes then condition becomes true.

SELECT 5 = 3 AS Result;

The <> operator checks if the values of two operands are equal or not, if values are not equal then condition becomes true.

SELECT 5 <> 3 AS Result;

The > operator checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true.

SELECT 5 > 3 AS Result;

The < operator checks if the value of left operand is less than the value of right operand, if yes then condition becomes true.

SELECT 5 < 3 AS Result;

The >= operator checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true.

SELECT 5 >= 3 AS Result;

The <= operator checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true.

SELECT 5 <= 3 AS Result;

Bitwise Operators

Bitwise operators are used to perform bitwise operations on numeric values.

The & operator performs a bitwise AND operation on two numeric values.

SELECT 5 & 3 AS Result;

The | operator performs a bitwise OR operation on two numeric values.

SELECT 5 | 3 AS Result;

The ^ operator performs a bitwise XOR operation on two numeric values.

SELECT 5 ^ 3 AS Result;

Compound Operators

Compound operators are used to combine two or more operators.

The += operator adds the right operand to the left operand and assigns the result to the left operand.

SELECT 5 += 3 AS Result;

The -= operator subtracts the right operand from the left operand and assigns the result to the left operand.

SELECT 5 -= 3 AS Result;

The *= operator multiplies the right operand with the left operand and assigns the result to the left operand.

SELECT 5 *= 3 AS Result;

The /= operator divides the left operand with the right operand and assigns the result to the left operand.

SELECT 5 /= 3 AS Result;

The %= operator takes modulus using two operands and assigns the result to the left operand.

SELECT 5 %= 3 AS Result;

String Functions

String functions are used to perform operations on string values.

ASCII

The ASCII function returns the ASCII value of the leftmost character of the string.

SELECT ASCII('Hello') AS Result;

CHAR_LENGTH

The CHAR_LENGTH function returns the length of a string.

SELECT CHAR_LENGTH('Hello') AS Result;

CONCAT

The CONCAT function concatenates two or more strings.

SELECT CONCAT('Hello', ' ', 'World') AS Result;

FIELD

The FIELD function returns the index (position) of the first occurrence of the string in a list of strings.

SELECT FIELD('World', 'Hello', 'World', 'Goodbye') AS Result;

FORMAT

The FORMAT function formats a number to a specified number of decimal places.

SELECT FORMAT(123.456, 2) AS Result;

INSERT

The INSERT function inserts a substring into a string.

SELECT INSERT('Hello World', 7, 5, 'SQL') AS Result;

INSTR

The INSTR function returns the position of the first occurrence of a substring in a string.

SELECT INSTR('Hello World', 'World') AS Result;

LCASE

The LCASE function converts a string to lowercase.

SELECT LCASE('Hello World') AS Result;

LEFT

The LEFT function returns the leftmost characters from a string.

SELECT LEFT('Hello World', 5) AS Result;

LENGTH

The LENGTH function returns the length of a string.

SELECT LENGTH('Hello World') AS Result;

LOCATE

The LOCATE function returns the position of the first occurrence of a substring in a string.

SELECT LOCATE('World', 'Hello World') AS Result;

LOWER

The LOWER function converts a string to lowercase.

SELECT LOWER('Hello World') AS Result;

LTRIM

The LTRIM function removes the leftmost spaces from a string.

SELECT LTRIM(' Hello World') AS Result;

MID

The MID function returns a substring from a string.

SELECT MID('Hello World', 7, 5) AS Result;

POSITION

The POSITION function returns the position of the first occurrence of a substring in a string.

SELECT POSITION('World' IN 'Hello World') AS Result;

REPEAT

The REPEAT function repeats a string a specified number of times.

SELECT REPEAT('Hello', 3) AS Result;

REPLACE

The REPLACE function replaces all occurrences of a substring in a string with another substring.

SELECT REPLACE('Hello World', 'World', 'SQL') AS Result;

REVERSE

The REVERSE function reverses a string.

SELECT REVERSE('Hello World') AS Result;

RIGHT

The RIGHT function returns the rightmost characters from a string.

SELECT RIGHT('Hello World', 5) AS Result;

RTRIM

The RTRIM function removes the rightmost spaces from a string.

SELECT RTRIM('Hello World ') AS Result;

SPACE

The SPACE function returns a string with the specified number of spaces.

SELECT SPACE(5) AS Result;

STRCMP

The STRCMP function compares two strings.

SELECT STRCMP('Hello', 'Hello') AS Result;

SUBSTRING

The SUBSTRING function returns a substring from a string.

SELECT SUBSTRING('Hello World', 7, 5) AS Result;

TRIM

The TRIM function removes the spaces from both ends of a string.

SELECT TRIM(' Hello World ') AS Result;

UCASE

The UCASE function converts a string to uppercase.

SELECT UCASE('Hello World') AS Result;

Numeric Functions

Numeric functions are used to perform operations on numeric values.

ABS

The ABS function returns the absolute value of a number.

SELECT ABS(-5) AS Result;

ACOS

The ACOS function returns the arccosine of a number.

SELECT ACOS(0.5) AS Result;

ASIN

The ASIN function returns the arcsine of a number.

SELECT ASIN(0.5) AS Result;

ATAN

The ATAN function returns the arctangent of a number.

SELECT ATAN(0.5) AS Result;

ATAN2

The ATAN2 function returns the arctangent of Y / X in the range of -PI to PI.

SELECT ATAN2(0.5, 0.5) AS Result;

AVG

The AVG function returns the average value of a numeric column.

SELECT AVG(Quantity) AS Result FROM OrderDetails;

CEILING

The CEILING function returns the smallest integer value that is greater than or equal to the number.

SELECT CEILING(5.5) AS Result;

COS

The COS function returns the cosine of a number.

SELECT COS(0.5) AS Result;

COT

The COT function returns the cotangent of a number.

SELECT COT(0.5) AS Result;

COUNT

The COUNT function returns the number of rows that matches a specified criteria.

SELECT COUNT(*) AS Result FROM Customers;

DEGREES

The DEGREES function converts a radian value to degrees.

SELECT DEGREES(0.5) AS Result;

EXP

The EXP function returns e raised to the power of a number.

SELECT EXP(0.5) AS Result;

FLOOR

The FLOOR function returns the largest integer value that is less than or equal to the number.

SELECT FLOOR(5.5) AS Result;

GREATEST

The GREATEST function returns the largest value of the selected columns.

SELECT GREATEST(Quantity, UnitPrice) AS Result FROM OrderDetails;

LEAST

The LEAST function returns the smallest value of the selected columns.

SELECT LEAST(Quantity, UnitPrice) AS Result FROM OrderDetails;

LOG

The LOG function returns the natural logarithm of a number.

SELECT LOG(0.5) AS Result;

LOG10

The LOG10 function returns the base-10 logarithm of a number.

SELECT LOG10(0.5) AS Result;

LOG2

The LOG2 function returns the base-2 logarithm of a number.

SELECT LOG2(0.5) AS Result;

MAX

The MAX function returns the largest value of the selected column.

SELECT MAX(Quantity) AS Result FROM OrderDetails;

MIN

The MIN function returns the smallest value of the selected column.

SELECT MIN(Quantity) AS Result FROM OrderDetails;

MOD

The MOD function returns the remainder of a division.

SELECT MOD(5, 2) AS Result;

The PI function returns the value of PI.

SELECT PI() AS Result;

POWER

The POWER function returns the value of a number raised to a power.

SELECT POWER(5, 2) AS Result;

RADIANS

The RADIANS function converts a degree value to radians.

SELECT RADIANS(0.5) AS Result;

RAND

The RAND function returns a random floating-point number between 0 and 1.

SELECT RAND() AS Result;

ROUND

The ROUND function rounds a number to a specified number of decimal places.

SELECT ROUND(5.5, 0) AS Result;

SIGN

The SIGN function returns the sign of a number.

SELECT SIGN(5) AS Result;

SIN

The SIN function returns the sine of a number.

SELECT SIN(0.5) AS Result;

SQRT

The SQRT function returns the square root of a number.

SELECT SQRT(0.5) AS Result;

SUM

The SUM function returns the sum of a numeric column.

SELECT SUM(Quantity) AS Result FROM OrderDetails;

TAN

The TAN function returns the tangent of a number.

SELECT TAN(0.5) AS Result;

TRUNCATE

The TRUNCATE function truncates a number to a specified number of decimal places.

SELECT TRUNCATE(5.5, 0) AS Result;

ADDDATE

The ADDDATE function adds a specified number of days to a date.

SELECT ADDDATE('2018-01-01', 1) AS Result;

ADDTIME

The ADDTIME function adds a specified time interval to a time.

SELECT ADDTIME('12:00:00', '01:00:00') AS Result;

CURDATE

The CURDATE function returns the current date.

SELECT CURDATE() AS Result;

CURRENT_DATE

The CURRENT_DATE function returns the current date.

SELECT CURRENT_DATE() AS Result;

CURRENT_TIME

The CURRENT_TIME function returns the current time.

SELECT CURRENT_TIME() AS Result;

CURRENT_TIMESTAMP

The CURRENT_TIMESTAMP function returns the current date and time.

SELECT CURRENT_TIMESTAMP() AS Result;

CURTIME

The CURTIME function returns the current time.

SELECT CURTIME() AS Result;

DATE

The DATE function returns the date part of a date or datetime expression.

SELECT DATE('2018-01-01 12:00:00') AS Result;

DATEDIFF

The DATEDIFF function returns the number of days between two dates.

SELECT DATEDIFF('2018-01-01', '2018-01-02') AS Result;

DATE_ADD

The DATE_ADD function adds a specified number of days to a date.

SELECT DATE_ADD('2018-01-01', INTERVAL 1 DAY) AS Result;

DATE_FORMAT

The DATE_FORMAT function formats a date or datetime expression using the specified format.

SELECT DATE_FORMAT('2018-01-01 12:00:00', '%Y-%m-%d') AS Result;

DATE_SUB

The DATE_SUB function subtracts a specified number of days from a date.

SELECT DATE_SUB('2018-01-01', INTERVAL 1 DAY) AS Result;

DAY

The DAY function returns the day of the month for a date.

SELECT DAY('2018-01-01') AS Result;

DAYNAME

The DAYNAME function returns the name of the day of the week for a date.

SELECT DAYNAME('2018-01-01') AS Result;

DAYOFMONTH

The DAYOFMONTH function returns the day of the month for a date.

SELECT DAYOFMONTH('2018-01-01') AS Result;

DAYOFWEEK

The DAYOFWEEK function returns the day of the week for a date.

SELECT DAYOFWEEK('2018-01-01') AS Result;

DAYOFYEAR

The DAYOFYEAR function returns the day of the year for a date.

SELECT DAYOFYEAR('2018-01-01') AS Result;

EXTRACT

The EXTRACT function returns a part of a date or datetime expression.

SELECT EXTRACT(YEAR FROM '2018-01-01') AS Result;

FROM_DAYS

The FROM_DAYS function converts a day number to a date.

SELECT FROM_DAYS(737424) AS Result;

HOUR

The HOUR function returns the hour for a time.

SELECT HOUR('12:00:00') AS Result;

LAST_DAY

The LAST_DAY function returns the last day of the month for a date.

SELECT LAST_DAY('2018-01-01') AS Result;

LOCALTIME

The LOCALTIME function returns the current time.

SELECT LOCALTIME() AS Result;

MAKEDATE

The MAKEDATE function creates a date from a year and day of year.

SELECT MAKEDATE(2018, 1) AS Result;

MICROSECOND

The MICROSECOND function returns the microsecond for a time.

SELECT MICROSECOND('12:00:00.123456') AS Result;

MINUTE

The MINUTE function returns the minute for a time.

SELECT MINUTE('12:00:00') AS Result;

MONTH

The MONTH function returns the month for a date.

SELECT MONTH('2018-01-01') AS Result;

MONTHNAME

The MONTHNAME function returns the name of the month for a date.

SELECT MONTHNAME('2018-01-01') AS Result;

NOW

The NOW function returns the current date and time.

SELECT NOW() AS Result;

PERIOD_ADD

The PERIOD_ADD function adds a specified number of periods to a period.

SELECT PERIOD_ADD(201801, 1) AS Result;

PERIOD_DIFF

The PERIOD_DIFF function returns the number of periods between two periods.

SELECT PERIOD_DIFF(201801, 201802) AS Result;

QUARTER

The QUARTER function returns the quarter for a date.

SELECT QUARTER('2018-01-01') AS Result;

SECOND

The SECOND function returns the second for a time.

SELECT SECOND('12:00:00') AS Result;

SEC_TO_TIME

The SEC_TO_TIME function converts a time in seconds to a time.

SELECT SEC_TO_TIME(43200) AS Result;

STR_TO_DATE

The STR_TO_DATE function converts a string to a date.

SELECT STR_TO_DATE('2018-01-01', '%Y-%m-%d') AS Result;

SUBDATE

The SUBDATE function subtracts a specified number of days from a date.

SELECT SUBDATE('2018-01-01', 1) AS Result;

SUBTIME

The SUBTIME function subtracts a time from another time.

SELECT SUBTIME('12:00:00', '01:00:00') AS Result;

SYSDATE

The SYSDATE function returns the current date.

SELECT SYSDATE() AS Result;

TIME

The TIME function returns the time part of a time or datetime expression.

SELECT TIME('12:00:00') AS Result;

TIME_FORMAT

The TIME_FORMAT function formats a time or datetime expression using the specified format.

SELECT TIME_FORMAT('12:00:00', '%H:%i:%s') AS Result;

TIME_TO_SEC

The TIME_TO_SEC function converts a time to seconds.

SELECT TIME_TO_SEC('12:00:00') AS Result;

TIMEDIFF

The TIMEDIFF function returns the time difference between two times.

SELECT TIMEDIFF('12:00:00', '01:00:00') AS Result;

TIMESTAMP

The TIMESTAMP function returns the date and time part of a datetime expression.

SELECT TIMESTAMP('2018-01-01 12:00:00') AS Result;

TO_DAYS

The TO_DAYS function converts a date to a day number.

SELECT TO_DAYS('2018-01-01') AS Result;

TO_SECONDS

The TO_SECONDS function converts a datetime to a Unix timestamp.

SELECT TO_SECONDS('2018-01-01 12:00:00') AS Result;

WEEK

The WEEK function returns the week number for a date.

SELECT WEEK('2018-01-01') AS Result;

WEEKDAY

The WEEKDAY function returns the weekday index for a date.

SELECT WEEKDAY('2018-01-01') AS Result;

WEEKOFYEAR

The WEEKOFYEAR function returns the week number for a date.

SELECT WEEKOFYEAR('2018-01-01') AS Result;

YEAR

The YEAR function returns the year for a date.

SELECT YEAR('2018-01-01') AS Result;

YEARWEEK

The YEARWEEK function returns the year and week number for a date.

SELECT YEARWEEK('2018-01-01') AS Result;

Created with by Mayank Patel