SQL

SQL stands for Structured Query Language.

SQL is used to store, manipulate, and retrieve data in databases.

SQL Tutorials

Data Manipulation

TopicDescription
SQL SELECTLearn how to use the SQL SELECT statement in MySQL with syntax, examples, and tips for selecting and filtering data.
SQL WHEREThe SQL WHERE clause filters records from a table based on specified conditions.
SQL INSERTDetailed guide on using SQL INSERT to add data into MySQL tables, including syntax, single/multiple inserts, and inserting specific columns.
SQL UPDATEModify existing records in a table with the SQL UPDATE statement, allowing changes to one or multiple rows at once.
SQL DELETESQL DELETE statement removes one or more records from a table based on specified conditions.
SQL LIKEPattern matching within a WHERE clause using SQL LIKE allows for filtering records based on specified patterns.

Logical Operators

TopicDescription
SQL ANDThe SQL AND operator filters records based on multiple conditions that all must be true.
SQL ORThe SQL OR operator filters records based on multiple conditions, where at least one must be true.
SQL NOTSQL NOT operator reverses the result of a condition, often used with AND, OR, LIKE, or IN operators to negate conditions.

Sorting and Filtering

TopicDescription
SQL ORDER BYThe SQL ORDER BY clause sorts the result set of a query by one or more columns.
SQL INSQL IN operator allows filtering based on a specified list of values in a WHERE clause.
SQL BETWEENSQL BETWEEN operator filters records within a specific range, including both the lower and upper limits.

Aggregation and Grouping

TopicDescription
SQL GROUP BYSQL GROUP BY groups rows with the same values in specified columns into summary rows.
SQL HAVINGSQL HAVING clause is used to filter groups based on aggregate conditions, used with GROUP BY.

Joins

TopicDescription
SQL JoinsCombines records from multiple tables based on related columns.
SQL Inner JoinReturns records with matching values in both tables.
SQL Left JoinReturns all records from the left table and matching records from the right table.
SQL Right JoinReturns all records from the right table and matching records from the left table.
SQL Full JoinReturns records when there is a match in either left or right table.

Subqueries

TopicDescription
SQL EXISTSChecks if a subquery returns any records, returning TRUE or FALSE.
SQL ANYCompares a value to any value in a specified list or subquery, returning TRUE if the comparison is true for at least one value.
SQL ALLCompares a value to all values in a specified list or subquery.

SQL UNION

SQL String Functions

FunctionDescription
ASCII()Returns the ASCII (numeric) value of the first character in a given string. Useful for comparing characters by their ASCII values.
CHAR()Returns the character that corresponds to a given ASCII code, allowing conversion from numeric ASCII codes to readable characters.
CHARINDEX()Returns the starting position of a substring within a main string. Useful for locating specific text within a string.
CONCAT()Combines two or more strings into one string. Useful for merging text fields or creating dynamic strings.
CONCAT_WS()Concatenates strings with a specified separator, which is added between each string. Ideal for creating delimited lists.
DATALENGTH()Returns the number of bytes required to represent a given expression. Useful for evaluating storage size.
DIFFERENCE()Compares the SOUNDEX values of two strings and returns an integer representing their similarity. Higher values indicate greater similarity.
FORMAT()Formats a value based on a specified format pattern, often used for dates or numbers.
LEFT()Extracts a specified number of characters from the beginning (left side) of a string.
LEN()Returns the length (number of characters) of a string, excluding trailing spaces.
LOWER()Converts all characters in a string to lowercase. Often used for case-insensitive comparisons.
LTRIM()Removes all leading (left-side) spaces from a string, useful for cleaning up data.
NCHAR()Returns the Unicode character that corresponds to a given numeric code. Useful for working with special Unicode characters.
PATINDEX()Returns the starting position of a specified pattern within a string, allowing flexible pattern searches.
QUOTENAME()Returns a string enclosed in delimiters, making it a valid SQL Server delimited identifier. Useful for dynamic SQL queries.
REPLACE()Replaces all occurrences of a specified substring within a string with a new substring.
REPLICATE()Repeats a string a specified number of times, creating a longer string with repeated content.
REVERSE()Returns the reverse order of characters in a string. Useful for reversing text data.
RIGHT()Extracts a specified number of characters from the end (right side) of a string.
RTRIM()Removes all trailing (right-side) spaces from a string, useful for cleaning data.
SOUNDEX()Returns a four-character code based on the phonetic sound of a string, used to evaluate similarity in pronunciation between two strings.
SPACE()Generates a string with a specified number of space characters, useful for padding text fields.
STR()Converts a numeric value to a string format, often used for string concatenation with numbers.
STUFF()Deletes a portion of a string and inserts a specified substring at a particular position within the string.
SUBSTRING()Extracts a portion of a string, starting at a specified position, for a given length.
TRANSLATE()Replaces each character in a string that matches any character in a specified set with the corresponding character in another set.
TRIM()Removes leading and trailing spaces (or specified characters) from a string, making it useful for data cleanup.
UNICODE()Returns the Unicode code of the first character in a given string, helpful for evaluating character encodings.
UPPER()Converts all characters in a string to uppercase, often used for case-insensitive operations.

SQL Math or Numerical Functions

FunctionDescription
ABSReturns the absolute (non-negative) value of a number. Useful for removing negative signs from numeric values.
ACOSReturns the arc cosine (inverse cosine) of a number, providing the angle in radians whose cosine is the specified number.
ASINReturns the arc sine (inverse sine) of a number, providing the angle in radians whose sine is the specified number.
ATANReturns the arc tangent (inverse tangent) of a number, providing the angle in radians whose tangent is the specified number.
ATN2Returns the arc tangent of the quotient of two numbers, useful for calculating angles in radians for given x and y coordinates.
AVGCalculates the average (mean) value of an expression across a set of values.
CEILINGReturns the smallest integer that is greater than or equal to a given number. Useful for rounding up to the nearest integer.
COUNTCounts the number of records in a result set or specified column, often used to determine the size of a dataset.
COSReturns the cosine of a specified angle in radians, a trigonometric function commonly used in angle calculations.
COTReturns the cotangent of a specified angle in radians, calculated as 1 divided by the tangent.
DEGREESConverts an angle from radians to degrees. Useful for converting radian-based trigonometric results into degree format.
EXPReturns Euler’s number (e) raised to a given power, commonly used in exponential growth calculations.
FLOORReturns the largest integer that is less than or equal to a given number. Useful for rounding down to the nearest integer.
LOGCalculates the natural logarithm (base e) of a number or logarithm to a specified base, used in exponential and logarithmic calculations.
LOG10Returns the base-10 logarithm of a specified number, often used for logarithmic scaling.
MAXReturns the maximum (largest) value in a set of values, commonly used to find the upper limit within a dataset.
MINReturns the minimum (smallest) value in a set of values, used to find the lower limit within a dataset.
PIReturns the mathematical constant PI (approximately 3.14159), used for calculations involving circles and trigonometry.
POWERRaises a number to the power of another specified number, used for exponential calculations.
RADIANSConverts an angle from degrees to radians, useful for trigonometric functions requiring radian input.
RANDGenerates a random floating-point number between 0 and 1, often used in simulations and randomized selections.
ROUNDRounds a number to a specified number of decimal places, commonly used for rounding to fixed decimal points.
SIGNReturns the sign (-1, 0, or 1) of a given number, indicating whether it is negative, zero, or positive.
SINReturns the sine of a specified angle in radians, a trigonometric function used for angle-based calculations.
SQRTCalculates the square root of a specified number, commonly used in various mathematical and statistical calculations.
SQUAREReturns the square of a number (value multiplied by itself), useful for geometric and algebraic calculations.
SUMCalculates the total sum of a set of values, often used to aggregate data.
TANReturns the tangent of a specified angle in radians, a trigonometric function often used in geometric calculations.

SQL Date Functions

FunctionDescription
CURRENT_TIMESTAMPReturns the current date and time according to the system’s local time zone, often used to timestamp records.
DATEADDAdds a specified time or date interval (e.g., days, months, years) to a date and returns the new date, useful for calculating future or past dates.
DATEDIFFCalculates the difference between two dates in terms of a specified date part (e.g., days, months, years), often used to measure duration.
DATEFROMPARTSCreates and returns a date value from specified year, month, and day components, useful for constructing specific dates.
DATENAMEReturns the specified part of a date (e.g., weekday name, month name) as a string, often used for reporting or display purposes.
DATEPARTReturns the specified part of a date (e.g., year, month, day) as an integer, allowing date components to be extracted for calculations.
DAYExtracts the day component from a specified date, returning a value between 1 and 31.
GETDATEReturns the current date and time of the database system, based on the system’s local time zone.
GETUTCDATEReturns the current date and time in UTC format, useful for applications requiring universal time.
ISDATEChecks if an expression is a valid date. Returns 1 for a valid date, otherwise 0, often used for data validation.
MONTHExtracts the month component from a specified date, returning a number from 1 to 12.
SYSDATETIMEReturns the current date and time of the SQL Server with higher precision than GETDATE, including fractions of a second.
YEARExtracts the year component from a specified date, returning a four-digit year.

SQL Other Functions

FunctionDescription
CASTConverts a value of any datatype to a specified datatype, often used for type compatibility in expressions.
COALESCEReturns the first non-null value from a list of expressions, useful for handling null values in queries.
CONVERTConverts a value of any datatype to a specified datatype, similar to CAST but with additional formatting options.
CURRENT_USERReturns the name of the current user in the SQL Server database, useful for tracking user activity.
IIFReturns one value if a specified condition is TRUE, and another value if it is FALSE, providing inline conditional logic.
ISNULLReplaces NULL with a specified replacement value, useful for managing null values in calculations or displays.
ISNUMERICChecks if an expression is numeric, returning 1 if true and 0 if false, often used for data validation.
NULLIFReturns NULL if two expressions are equal, otherwise returns the first expression, useful for avoiding division by zero and other operations.
SESSION_USERReturns the name of the current session’s user in the SQL Server database, useful for session-specific operations.
SESSIONPROPERTYRetrieves information about the current session’s settings, such as language and isolation level.
SYSTEM_USERReturns the login name for the current user, reflecting the credentials used to access the SQL Server instance.
USER_NAMEReturns the database user name associated with a specified user ID, useful for auditing and security purposes.