The Complete SQL Commands Guide: 100+ Essential Commands for Database Mastery

SQL (Structured Query Language) is the backbone of database management and data manipulation. Whether you're a beginner starting your data journey or an experienced developer looking for a comprehensive reference, this guide covers 100+ essential SQL commands that every database professional should master.

Table of Contents

• Basic Data Query Commands

• Filtering and Conditional Commands

• Aggregate Functions

• Join Operations

• Data Modification Commands

• Table Structure Commands

• Index Operations

• Constraint Operations

• Database Operations

• Advanced Query Operations

• String Functions

• Date and Time Functions

• Mathematical Functions

• Window Functions

• Control Flow and Conditional

• Transaction Control

Basic Data Query Commands

1. SELECT

The foundation of SQL querying - retrieves data from one or more tables.

2. SELECT DISTINCT

Returns unique values only, eliminating duplicate records from results.

3. WHERE

Filters records based on specified conditions, essential for targeted data retrieval.

4. ORDER BY

Sorts result set in ascending (ASC) or descending (DESC) order.

5. LIMIT

Restricts the number of rows returned in the result set.

6. OFFSET

Skips a specified number of rows before returning results, useful for pagination.

7. TOP

Returns a specified number of records from the top of the result set (SQL Server).

8. FETCH FIRST

Standard SQL way to limit the number of returned rows.

Filtering and Conditional Commands

9. AND

Combines multiple conditions where all must be true for a record to be selected.

10. OR

Combines conditions where at least one must be true for record selection.

11. NOT

Negates a condition, returning the opposite boolean result.

12. IN

Checks if a value matches any value in a specified list.

13. NOT IN

Checks if a value doesn't match any value in the specified list.

14. BETWEEN

Filters values within a specified range (inclusive of boundary values).

15. LIKE

Searches for a specified pattern in a column using wildcards (% and _).

16. NOT LIKE

Searches for records that don't match the specified pattern.

17. IS NULL

Checks for empty or null values in a column.

18. IS NOT NULL

Checks for non-empty or non-null values in a column.

Aggregate Functions

19. COUNT

Counts the number of rows or non-null values in a result set.

20. SUM

Calculates the total sum of numeric column values.

21. AVG

Calculates the average value of a numeric column.

22. MIN

Returns the smallest value in a specified column.

23. MAX

Returns the largest value in a specified column.

24. GROUP BY

Groups rows that have the same values into summary rows.

25. HAVING

Filters groups created by the GROUP BY clause based on conditions.

Join Operations

26. INNER JOIN

Returns records that have matching values in both tables.

27. LEFT JOIN

Returns all records from the left table and matching records from the right table.

28. RIGHT JOIN

Returns all records from the right table and matching records from the left table.

29. FULL OUTER JOIN

Returns all records when there's a match in either left or right table.

30. CROSS JOIN

Returns the Cartesian product of two tables (all possible combinations).

31. SELF JOIN

Joins a table with itself using table aliases.

Data Modification Commands

32. INSERT

Adds new records to a table with specified column values.

33. INSERT INTO SELECT

Inserts data from one table into another table based on a query.

34. UPDATE

Modifies existing records in a table based on specified conditions.

35. DELETE

Removes records from a table based on specified conditions.

36. TRUNCATE

Removes all records from a table and resets identity columns.

37. MERGE

Performs insert, update, or delete operations based on matching conditions.

Table Structure Commands

38. CREATE TABLE

Creates a new table with specified columns, data types, and constraints.

39. ALTER TABLE

Modifies the structure of an existing table (add/drop/modify columns).

40. DROP TABLE

Permanently deletes a table and all its data from the database.

41. RENAME TABLE

Changes the name of an existing table.

42. ADD COLUMN

Adds a new column to an existing table structure.

43. DROP COLUMN

Removes a column from an existing table structure.

44. MODIFY COLUMN

Changes the data type or constraints of an existing column.

Index Operations

45. CREATE INDEX

Creates an index on table columns for faster query performance.

46. DROP INDEX

Removes an existing index from a table.

47. CREATE UNIQUE INDEX

Creates an index that enforces uniqueness constraint on column values.

48. SHOW INDEX

Displays all indexes defined on a specified table.

Constraint Operations

49. PRIMARY KEY

Defines column(s) that uniquely identify each row in a table.

50. FOREIGN KEY

Creates a relationship between tables and ensures referential integrity.

51. UNIQUE

Ensures all values in a column or combination of columns are different.

52. NOT NULL

Prevents null values from being inserted into a column.

53. CHECK

Validates data based on a specified condition before insertion or update.

54. DEFAULT

Sets a default value for a column when no value is specified.

Database Operations

55. CREATE DATABASE

Creates a new database with a specified name.

56. DROP DATABASE

Permanently deletes a database and all its contents.

57. USE

Switches to a specified database for subsequent operations.

58. SHOW DATABASES

Lists all available databases on the server.

59. SHOW TABLES

Lists all tables in the current database.

60. DESCRIBE

Shows the structure of a table including columns and data types.

Advanced Query Operations

61. SUBQUERY

A query nested inside another query for complex data filtering and retrieval.

62. EXISTS

Checks if a subquery returns any rows, useful for conditional logic.

63. NOT EXISTS

Checks if a subquery returns no rows.

64. ANY

Compares a value to any value returned by a subquery.

65. ALL

Compares a value to all values returned by a subquery.

66. UNION

Combines results from two or more queries, removing duplicate rows.

67. UNION ALL

Combines results from two or more queries, keeping all duplicate rows.

68. INTERSECT

Returns common rows that appear in both query results.

69. EXCEPT

Returns rows from the first query that don't appear in the second query.

String Functions

70. CONCAT

Joins two or more strings together into a single string.

71. LENGTH

Returns the character count of a string value.

72. UPPER

Converts all characters in a string to uppercase.

73. LOWER

Converts all characters in a string to lowercase.

74. SUBSTRING

Extracts a portion of a string from a specified starting position.

75. TRIM

Removes leading and trailing whitespace from a string.

76. REPLACE

Replaces all occurrences of a substring with another string.

Date and Time Functions

77. NOW

Returns the current date and time.

78. CURDATE

Returns the current date without the time component.

79. CURTIME

Returns the current time without the date component.

80. DATE

Extracts the date part from a datetime expression.

81. YEAR

Extracts the year from a date value.

82. MONTH

Extracts the month from a date value.

83. DAY

Extracts the day from a date value.

84. DATEDIFF

Calculates the difference between two dates in days.

85. DATE_ADD

Adds a specified time interval to a date.

Mathematical Functions

86. ROUND

Rounds a numeric value to a specified number of decimal places.

87. CEIL

Rounds a number up to the nearest integer.

88. FLOOR

Rounds a number down to the nearest integer.

89. ABS

Returns the absolute (positive) value of a number.

90. POWER

Raises a number to a specified power or exponent.

Window Functions

91. ROW_NUMBER

Assigns a unique sequential number to each row within a result set.

92. RANK

Assigns a rank to each row, with gaps for tied values.

93. DENSE_RANK

Assigns a rank to each row without gaps for tied values.

94. LAG

Accesses data from a previous row in the result set.

95. LEAD

Accesses data from a subsequent row in the result set.

Control Flow and Conditional

96. CASE

Performs conditional logic similar to if-then-else statements.

97. COALESCE

Returns the first non-null value from a list of expressions.

98. NULLIF

Returns null if two expressions are equal, otherwise returns the first expression.

Transaction Control

99. BEGIN TRANSACTION

Starts a new transaction for grouping multiple database operations.

100. COMMIT

Saves all changes made in the current transaction permanently to the database.

101. ROLLBACK

Undoes all changes made in the current transaction, reverting to the previous state.

102. SAVEPOINT

Creates a point within a transaction to which you can later rollback.

Best Practices and Tips

Performance Optimization

• Use indexes wisely: Create indexes on frequently queried columns

• Limit result sets: Always use WHERE clauses to filter unnecessary data

• Avoid SELECT *: Specify only the columns you need

• Use EXPLAIN: Analyze query execution plans for optimization opportunities

Security Considerations

• Parameterized queries: Prevent SQL injection attacks

• Principle of least privilege: Grant minimum necessary permissions

• Regular backups: Implement automated backup strategies

• Data encryption: Encrypt sensitive data at rest and in transit

Code Quality

• Consistent naming: Use clear, descriptive names for tables and columns

• Comments: Document complex queries and business logic

• Formatting: Use proper indentation and line breaks for readability

• Version control: Track changes to database schemas and queries

Conclusion

This comprehensive guide covers 100+ essential SQL commands that form the foundation of database management and data manipulation. From basic queries to advanced operations, these commands provide the tools needed to effectively work with relational databases.

Remember that SQL is a powerful language, and mastery comes through practice. Start with the basic commands and gradually work your way up to more complex operations. Each database system (MySQL, PostgreSQL, SQL Server, Oracle) may have slight variations in syntax, so always refer to the specific documentation for your platform.

Whether you're analyzing data, building applications, or managing databases, these SQL commands will serve as your reliable toolkit for database success.

Ready to level up your SQL skills? Practice these commands with real datasets and explore advanced topics like stored procedures, triggers, and database optimization. The journey to SQL mastery is ongoing, but with these fundamentals, you're well-equipped to tackle any database challenge.

Share this guide with fellow developers and data enthusiasts who are looking to strengthen their SQL knowledge!