100 SQL Commands — Simple & Unique Explanations

1–10: Basic Data & Structure Commands

1. SELECT – Pulls data out of a table and shows it to you.
2. INSERT – Adds a new row of information into a table.
3. UPDATE – Changes existing data inside a table.
4. DELETE – Removes rows from a table.
5. CREATE DATABASE – Makes a brand-new database.
6. CREATE TABLE – Creates a new table inside a database.
7. ALTER TABLE – Edits a table’s structure (add/remove/change columns).
8. DROP TABLE – Completely deletes a table and its data.
9. TRUNCATE TABLE – Wipes all rows but keeps the table structure.
10. CREATE INDEX – Builds an index to speed up searches.

11–20: Joins & Grouping

11. DROP INDEX – Removes an index from a table.
12. JOIN – Combines data from other tables based on a matching column.
13. INNER JOIN – Shows only the records matched in both tables.
14. LEFT JOIN – Shows all records from the left table + matched rows from the right.
15. RIGHT JOIN – Shows all rows from the right + matched ones from the left.
16. FULL JOIN – Shows rows from both tables, matched or unmatched.
17. UNION – Combines results of two queries but removes duplicates.
18. UNION ALL – Combines results of two queries, keeps duplicates.
19. GROUP BY – Groups rows that share the same value(s).
20. HAVING – Filters groups created by GROUP BY.

21–28: Sorting & Basic Functions

21. ORDER BY – Sorts the output (ASC/DESC).
22. COUNT – Counts number of rows.
23. SUM – Adds up a numeric column.
24. AVG – Finds the average of a numeric column.
25. MIN – Returns the smallest value.
26. MAX – Returns the biggest value.
27. DISTINCT – Shows only unique values.
28. WHERE – Filters rows based on conditions.

29–41: Conditions & Logical Tools

29. AND – All conditions must be true.
30. OR – At least one condition must be true.
31. NOT – Reverses a condition.
32. BETWEEN – Selects values inside a specific range.
33. IN – Matches any value in a given list.
34. LIKE – Searches using patterns (%, _).
35. IS NULL – Checks for empty/NULL values.
36. IS NOT NULL – Checks for non-null values.
37. EXISTS – Returns true if a subquery has any results.
38. CASE – Adds conditional logic (if/else) inside a query.
39. WHEN – Specifies conditions inside CASE.
40. THEN – Defines what to return when the condition is true.
41. ELSE – Defines what to return when no conditions match.

42–55: Keys, Constraints & Table Rules

42. END – Ends the CASE block.
43. PRIMARY KEY – A column that uniquely identifies each row.
44. FOREIGN KEY – Connects one table to another using a reference.
45. CONSTRAINT – A rule applied on a column or table.
46. DEFAULT – Sets a fallback value if none is provided.
47. NOT NULL – Makes sure a column never stays empty.
48. UNIQUE – Ensures all values in a column are different.
49. CHECK – Applies a condition to limit allowed values.
50. CASCADE – Automatically applies delete/update to related records.
51. SET NULL – Turns foreign key values to NULL when parent is deleted.
52. SET DEFAULT – Replaces foreign key with its default value.
53. NO ACTION – Prevents delete/update unless dependent rows are handled manually.
54. RESTRICT – Blocks deletion if related rows exist.
55. CASE WHEN – Short form of conditional logic inside SELECT.

56–77: Query Enhancers, Pagination & Date Functions

56. WITH – Creates a temporary result set (CTE).
57. INTO – Saves SELECT result into a new table.
58. TOP – Limits number of rows returned.
59. LIMIT – Restricts the number of rows returned.
60. OFFSET – Skips a number of rows before starting to return results.
61. FETCH – Retrieves a specific number of rows.
62. ROW_NUMBER() – Assigns a unique number to each row.
63. RANK() – Gives ranking with gaps if ties exist.
64. DENSE_RANK() – Similar to RANK but without gaps.
65. NTILE() – Splits rows into N equal-sized groups.
66. LEAD() – Accesses the next row’s value.
67. LAG() – Accesses the previous row’s value.
68. PARTITION BY – Divides data into groups for window functions.
69. ORDER BY (in windows) – Defines row order within partitions.
70. ROWS – Sets window frame using row counts.
71. RANGE – Sets window frame based on value ranges.
72. CURRENT_TIMESTAMP – Returns current date & time.
73. CURRENT_DATE – Returns today’s date.
74. CURRENT_TIME – Returns current time.
75. DATEADD – Adds time (days, hours, etc.) to a date.
76. DATEDIFF – Returns difference between two dates.
77. DATEPART – Extracts a specific part of a date (year, month, etc.).

78–100: Advanced Aggregation, String Functions & Misc

78. GETDATE – Returns current date & time (like CURRENT_TIMESTAMP).
79. GROUPING SETS – Lets you group data in multiple ways in one query.
80. CUBE – Produces every possible combination of groupings.
81. ROLLUP – Creates subtotals & grand totals automatically.
82. INTERSECT – Shows common rows in two result sets.
83. EXCEPT – Shows rows from first query that aren’t in second.
84. MERGE – Insert, update, or delete based on matching records.
85. CROSS APPLY – Runs a subquery for each row of a table.
86. OUTER APPLY – Like CROSS APPLY but includes unmatched rows too.
87. PIVOT – Converts row values into columns.
88. UNPIVOT – Converts columns into rows.
89. COALESCE – Returns first non-null value from a list.
90. NULLIF – Returns NULL if two values are equal.
91. IIF – Short form of IF logic (true/false).
92. CONCAT – Joins two or more strings.
93. SUBSTRING – Extracts part of a string using positions.
94. CHARINDEX – Finds position of one string inside another.
95. REPLACE – Swaps one substring with another inside a string.
96. LEN – Returns the length of a string.
97. UPPER – Converts text to uppercase.
98. LOWER – Converts text to lowercase.
99. TRIM – Removes spaces from both ends of a string.
100. ROUND – Rounds a number to desired decimal places.