100 Must-Know SQL
Commands with Examples
for Every Developer
VISHNU VARDHAN
1. SELECT
Retrieves data from a table.
Syntax
Example
Vishnu
2. SELECT DISTINCT
Returns unique values from a column.
Syntax
Example
Vishnu
3. WHERE
Filters records based on a specified condition.
Syntax
Example
Vishnu
4. AND
Combines multiple conditions in a query where all conditions must be
true.
Syntax
Example
Vishnu
5. OR
Combines multiple conditions in a query where at least one condition
must be true.
Syntax
Example
Vishnu
6. ORDER BY
Sorts results in ascending or descending order.
Syntax
Example
Vishnu
7. LIMIT
Restricts the number of records returned.
Syntax
Example
Vishnu
8. OFFSET
Skips a specified number of rows before returning the result.
Syntax
Example
Vishnu
9. INSERT INTO
Adds new records to a table.
Syntax
Example
Vishnu
10. UPDATE
Modifies existing records.
Syntax
Example
Vishnu
11. DELETE
Removes records from a table.
Syntax
Example
Vishnu
12. CREATE TABLE
Creates a new table in the database.
Syntax
Example
Vishnu
13. ALTER TABLE
Modifies an existing table structure.
Syntax
Example
Vishnu
14. DROP TABLE
Removes a table and its data permanently.
Syntax
Example
Vishnu
15. TRUNCATE TABLE
Removes all records from a table but keeps the structure intact.
Syntax
Example
Vishnu
16. JOIN
Combines records from two or more tables based on a related column.
Syntax
Example
Vishnu
17. INNER JOIN
Returns records with matching values in both tables.
Syntax
Example
Vishnu
18. LEFT JOIN
Returns all records from the left table and matching records from the
right table.
Syntax
Example
Vishnu
19. RIGHT JOIN
Returns all records from the right table and matching records from the
left table.
Syntax
Example
Vishnu
20. FULL OUTER JOIN
Returns all records when there is a match in either table.
Syntax
Example
Vishnu
21. CROSS JOIN
Returns the Cartesian product of two tables (all possible
combinations).
Syntax
Example
Vishnu
22. SELF JOIN
Joins a table to itself.
Syntax
Example
Vishnu
23. GROUP BY
Groups records based on one or more columns.
Syntax
Example
Vishnu
24. HAVING
Filters grouped records after GROUP BY.
Syntax
Example
Vishnu
25. UNION
Combines results from multiple SELECT statements, removing
duplicates.
Syntax
Example
Vishnu
26. UNION ALL
Combines results from multiple SELECT statements, including
duplicates.
Syntax
Example
Vishnu
27. EXISTS
Checks whether a subquery returns any records.
Syntax
Example
Vishnu
28. NOT EXISTS
Checks whether a subquery returns no records.
Syntax
Example
Vishnu
29. CASE
Performs conditional logic in a query.
Syntax
Example
Vishnu
30. COALESCE
Returns the first non-null value in a list.
Syntax
Example
Vishnu
31. NULLIF
Returns NULL if two expressions are equal; otherwise, returns the first
expression.
Syntax
Example
Vishnu
32. IFNULL
Returns the first value if not null; otherwise, returns the second value.
Syntax
Example
Vishnu
33. IS NULL
Checks if a column contains NULL values.
Syntax
Example
Vishnu
34. IS NOT NULL
Checks if a column contains non-null values.
Syntax
Example
Vishnu
35. SUBSTRING
Extracts a substring from a string.
Syntax
Example
Vishnu
36. LENGTH
Returns the number of characters in a string.
Syntax
Example
Vishnu
37. TRIM
Removes spaces or specified characters from both sides of a string.
Syntax
Example
Vishnu
38. UPPER
Converts a string to uppercase.
Syntax
Example
Vishnu
39. LOWER
Converts a string to lowercase.
Syntax
Example
Vishnu
40. REPLACE
Replaces occurrences of a substring in a string.
Syntax
Example
Vishnu
41. ROUND
Rounds a number to a specified decimal place.
Syntax
Example
Vishnu
42. CEIL
Rounds a number up to the nearest integer.
Syntax
Example
Vishnu
43. FLOOR
Rounds a number down to the nearest integer.
Syntax
Example
Vishnu
44. ABS
Returns the absolute value of a number.
Syntax
Example
Vishnu
45. MOD
Returns the remainder of a division.
Syntax
Example
Vishnu
46. POWER
Returns the value of a number raised to a power.
Syntax
Example
Vishnu
47. SQRT
Returns the square root of a number.
Syntax
Example
Vishnu
48. RAND
Generates a random number.
Syntax
Example
Vishnu
49. NOW
Returns the current date and time.
Syntax
Example
Vishnu
50. CURDATE
Returns the current date.
Syntax
Example
Vishnu
51. CURTIME
Returns the current time.
Syntax
Example
Vishnu
52. DATE
Extracts the date part of a datetime value.
Syntax
Example
Vishnu
53. YEAR
Extracts the year from a date.
Syntax
Example
Vishnu
54. MONTH
Extracts the month from a date.
Syntax
Example
Vishnu
55. DAY
Extracts the day from a date.
Syntax
Example
Vishnu
56. DATEDIFF
Calculates the difference between two dates.
Syntax
Example
Vishnu
57. ADDDATE
Adds a specified number of days to a date.
Syntax
Example
Vishnu
58. SUBDATE
Subtracts a specified number of days from a date.
Syntax
Example
Vishnu
59. EXTRACT
Extracts a part of a date/time.
Syntax
Example
Vishnu
60. CAST
Converts one data type into another.
Syntax
Example
Vishnu
61. CONVERT
Similar to CAST, used for type conversion.
Syntax
Example
Vishnu
62. IF
Performs conditional checks in a query.
Syntax
Example
Vishnu
63. GREATEST
Returns the largest value among given values.
Syntax
Example
Vishnu
64. LEAST
Returns the smallest value among given values.
Syntax
Example
Vishnu
65. CHAR_LENGTH
Returns the number of characters in a string.
Syntax
Example
Vishnu
66. POSITION
Finds the position of a substring within a string.
Syntax
Example
Vishnu
67. LEFT
Extracts a specified number of characters from the beginning of a
string.
Syntax
Example
Vishnu
68. RIGHT
Extracts a specified number of characters from the end of a string.
Syntax
Example
Vishnu
69. LPAD
Pads the left side of a string with a specified character to a certain
length.
Syntax
Example
Vishnu
70. RPAD
Pads the right side of a string with a specified character to a certain
length.
Syntax
Example
Vishnu
71. CONCAT
Combines two or more strings into one.
Syntax
Example
Vishnu
72. CONCAT_WS
Concatenates strings with a specified separator.
Syntax
Example
Vishnu
73. REVERSE
Reverses a string.
Syntax
Example
Vishnu
74. LOCATE
Finds the position of a substring in a string (similar to POSITION).
Syntax
Example
Vishnu
75. ASCII
Returns the ASCII code of the first character of a string.
Syntax
Example
Vishnu
76. CHAR
Returns the character for a given ASCII code.
Syntax
Example
Vishnu
77. SIGN
Returns the sign of a number (-1, 0, or 1).
Syntax
Example
Vishnu
78. LOG
Returns the natural logarithm of a number.
Syntax
Example
Vishnu
79. EXP
Returns e raised to a power.
Syntax
Example
Vishnu
80. PI
Returns the value of π.
Syntax
Example
Vishnu
81. RADIANS
Converts degrees to radians.
Syntax
Example
Vishnu
82. DEGREES
Converts radians to degrees.
Syntax
Example
Vishnu
83. COS
Returns the cosine of an angle.
Syntax
Example
Vishnu
84. SIN
Returns the sine of an angle.
Syntax
Example
Vishnu
85. TAN
Returns the tangent of an angle.
Syntax
Example
Vishnu
86. COT
Returns the cotangent of an angle.
Syntax
Example
Vishnu
87. ATAN
Returns the arctangent of a number.
Syntax
Example
Vishnu
88. LOG10
Returns the base-10 logarithm of a number.
Syntax
Example
Vishnu
89. LN
Returns the natural logarithm (base e).
Syntax
Example
Vishnu
90. GREATEST
Returns the largest value among given values.
Syntax
Example
Vishnu
91. LEAST
Returns the smallest value among given values.
Syntax
Example
Vishnu
92. BIT_LENGTH
Returns the length of a string in bits.
Syntax
Example
Vishnu
93. BINARY
Performs a binary comparison between two values.
Syntax
Example
Vishnu
94. UUID
Generates a universally unique identifier.
Syntax
Example
Vishnu
95. ROW_NUMBER
Assigns a unique row number to each row in the result set.
Syntax
Example
Vishnu
96. RANK
Assigns a ranking to each row based on a specified column, with
identical values receiving the same rank while skipping numbers for
duplicate values.
Syntax
Example
Vishnu
97. DENSE_RANK
Similar to RANK, but does not skip numbers when duplicate values
exist.
Syntax
Example
Vishnu
98. NTILE
Divides the result set into a specified number of equal-sized groups
and assigns a group number to each row.
Syntax
Example
Vishnu
99. LAG
Retrieves the value of a column from the previous row in the result
set.
Syntax
Example
Vishnu
100. LEAD
Retrieves the value of a column from the next row in the result set.
Syntax
Example
Vishnu