SQL
SQL stands for Structured Query Language.
SQL is used to store, manipulate, and retrieve data in databases.
SQL Tutorials
Data Manipulation
Topic | Description |
---|---|
SQL SELECT | Learn how to use the SQL SELECT statement in MySQL with syntax, examples, and tips for selecting and filtering data. |
SQL WHERE | The SQL WHERE clause filters records from a table based on specified conditions. |
SQL INSERT | Detailed guide on using SQL INSERT to add data into MySQL tables, including syntax, single/multiple inserts, and inserting specific columns. |
SQL UPDATE | Modify existing records in a table with the SQL UPDATE statement, allowing changes to one or multiple rows at once. |
SQL DELETE | SQL DELETE statement removes one or more records from a table based on specified conditions. |
SQL LIKE | Pattern matching within a WHERE clause using SQL LIKE allows for filtering records based on specified patterns. |
Logical Operators
Topic | Description |
---|---|
SQL AND | The SQL AND operator filters records based on multiple conditions that all must be true. |
SQL OR | The SQL OR operator filters records based on multiple conditions, where at least one must be true. |
SQL NOT | SQL NOT operator reverses the result of a condition, often used with AND, OR, LIKE, or IN operators to negate conditions. |
Sorting and Filtering
Topic | Description |
---|---|
SQL ORDER BY | The SQL ORDER BY clause sorts the result set of a query by one or more columns. |
SQL IN | SQL IN operator allows filtering based on a specified list of values in a WHERE clause. |
SQL BETWEEN | SQL BETWEEN operator filters records within a specific range, including both the lower and upper limits. |
Aggregation and Grouping
Topic | Description |
---|---|
SQL GROUP BY | SQL GROUP BY groups rows with the same values in specified columns into summary rows. |
SQL HAVING | SQL HAVING clause is used to filter groups based on aggregate conditions, used with GROUP BY. |
Joins
Topic | Description |
---|---|
SQL Joins | Combines records from multiple tables based on related columns. |
SQL Inner Join | Returns records with matching values in both tables. |
SQL Left Join | Returns all records from the left table and matching records from the right table. |
SQL Right Join | Returns all records from the right table and matching records from the left table. |
SQL Full Join | Returns records when there is a match in either left or right table. |
Subqueries
Topic | Description |
---|---|
SQL EXISTS | Checks if a subquery returns any records, returning TRUE or FALSE. |
SQL ANY | Compares a value to any value in a specified list or subquery, returning TRUE if the comparison is true for at least one value. |
SQL ALL | Compares a value to all values in a specified list or subquery. |
SQL String Functions
Function | Description |
---|---|
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
Function | Description |
---|---|
ABS | Returns the absolute (non-negative) value of a number. Useful for removing negative signs from numeric values. |
ACOS | Returns the arc cosine (inverse cosine) of a number, providing the angle in radians whose cosine is the specified number. |
ASIN | Returns the arc sine (inverse sine) of a number, providing the angle in radians whose sine is the specified number. |
ATAN | Returns the arc tangent (inverse tangent) of a number, providing the angle in radians whose tangent is the specified number. |
ATN2 | Returns the arc tangent of the quotient of two numbers, useful for calculating angles in radians for given x and y coordinates. |
AVG | Calculates the average (mean) value of an expression across a set of values. |
CEILING | Returns the smallest integer that is greater than or equal to a given number. Useful for rounding up to the nearest integer. |
COUNT | Counts the number of records in a result set or specified column, often used to determine the size of a dataset. |
COS | Returns the cosine of a specified angle in radians, a trigonometric function commonly used in angle calculations. |
COT | Returns the cotangent of a specified angle in radians, calculated as 1 divided by the tangent. |
DEGREES | Converts an angle from radians to degrees. Useful for converting radian-based trigonometric results into degree format. |
EXP | Returns Euler’s number (e) raised to a given power, commonly used in exponential growth calculations. |
FLOOR | Returns the largest integer that is less than or equal to a given number. Useful for rounding down to the nearest integer. |
LOG | Calculates the natural logarithm (base e) of a number or logarithm to a specified base, used in exponential and logarithmic calculations. |
LOG10 | Returns the base-10 logarithm of a specified number, often used for logarithmic scaling. |
MAX | Returns the maximum (largest) value in a set of values, commonly used to find the upper limit within a dataset. |
MIN | Returns the minimum (smallest) value in a set of values, used to find the lower limit within a dataset. |
PI | Returns the mathematical constant PI (approximately 3.14159), used for calculations involving circles and trigonometry. |
POWER | Raises a number to the power of another specified number, used for exponential calculations. |
RADIANS | Converts an angle from degrees to radians, useful for trigonometric functions requiring radian input. |
RAND | Generates a random floating-point number between 0 and 1, often used in simulations and randomized selections. |
ROUND | Rounds a number to a specified number of decimal places, commonly used for rounding to fixed decimal points. |
SIGN | Returns the sign (-1, 0, or 1) of a given number, indicating whether it is negative, zero, or positive. |
SIN | Returns the sine of a specified angle in radians, a trigonometric function used for angle-based calculations. |
SQRT | Calculates the square root of a specified number, commonly used in various mathematical and statistical calculations. |
SQUARE | Returns the square of a number (value multiplied by itself), useful for geometric and algebraic calculations. |
SUM | Calculates the total sum of a set of values, often used to aggregate data. |
TAN | Returns the tangent of a specified angle in radians, a trigonometric function often used in geometric calculations. |
SQL Date Functions
Function | Description |
---|---|
CURRENT_TIMESTAMP | Returns the current date and time according to the system’s local time zone, often used to timestamp records. |
DATEADD | Adds 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. |
DATEDIFF | Calculates the difference between two dates in terms of a specified date part (e.g., days, months, years), often used to measure duration. |
DATEFROMPARTS | Creates and returns a date value from specified year, month, and day components, useful for constructing specific dates. |
DATENAME | Returns the specified part of a date (e.g., weekday name, month name) as a string, often used for reporting or display purposes. |
DATEPART | Returns the specified part of a date (e.g., year, month, day) as an integer, allowing date components to be extracted for calculations. |
DAY | Extracts the day component from a specified date, returning a value between 1 and 31. |
GETDATE | Returns the current date and time of the database system, based on the system’s local time zone. |
GETUTCDATE | Returns the current date and time in UTC format, useful for applications requiring universal time. |
ISDATE | Checks if an expression is a valid date. Returns 1 for a valid date, otherwise 0, often used for data validation. |
MONTH | Extracts the month component from a specified date, returning a number from 1 to 12. |
SYSDATETIME | Returns the current date and time of the SQL Server with higher precision than GETDATE, including fractions of a second. |
YEAR | Extracts the year component from a specified date, returning a four-digit year. |
SQL Other Functions
Function | Description |
---|---|
CAST | Converts a value of any datatype to a specified datatype, often used for type compatibility in expressions. |
COALESCE | Returns the first non-null value from a list of expressions, useful for handling null values in queries. |
CONVERT | Converts a value of any datatype to a specified datatype, similar to CAST but with additional formatting options. |
CURRENT_USER | Returns the name of the current user in the SQL Server database, useful for tracking user activity. |
IIF | Returns one value if a specified condition is TRUE, and another value if it is FALSE, providing inline conditional logic. |
ISNULL | Replaces NULL with a specified replacement value, useful for managing null values in calculations or displays. |
ISNUMERIC | Checks if an expression is numeric, returning 1 if true and 0 if false, often used for data validation. |
NULLIF | Returns NULL if two expressions are equal, otherwise returns the first expression, useful for avoiding division by zero and other operations. |
SESSION_USER | Returns the name of the current session’s user in the SQL Server database, useful for session-specific operations. |
SESSIONPROPERTY | Retrieves information about the current session’s settings, such as language and isolation level. |
SYSTEM_USER | Returns the login name for the current user, reflecting the credentials used to access the SQL Server instance. |
USER_NAME | Returns the database user name associated with a specified user ID, useful for auditing and security purposes. |