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. |