SQL Commands with examples

In this Blog we focused on most important SQL Commands with examples.Listed 100 most important commands with examples.

Also Read : NotebookLm

100 SQL Commands with examples:

1. 1. 1. SELECT: Returns all columns from the employees table
         SELECT * FROM employees;
      2. SELECT DISTINCT: Fetches unique department names
         SELECT DISTINCT department FROM employees;
      3. WHERE: Filters employees with salary over 50,000
         SELECT * FROM employees WHERE salary > 50000;
      4. AND / OR: Combines conditions
         SELECT * FROM employees WHERE salary > 50000 AND department = 'IT';
      5. ORDER BY: Sorts data in descending order of salary
         SELECT * FROM employees ORDER BY salary DESC;
      6. LIMIT: Limits the number of results
         SELECT * FROM employees LIMIT 5;
      7. CREATE TABLE: Creates a new table
         CREATE TABLE students (id INT PRIMARY KEY, name VARCHAR(100), age INT);
      8. DROP TABLE: Deletes a table
         DROP TABLE students;
      9. ALTER TABLE ADD: Adds a new column
         ALTER TABLE students ADD email VARCHAR(100);
      10. ALTER TABLE DROP: Drops a column
          ALTER TABLE students DROP COLUMN email;
      11. RENAME TABLE: Renames a table
          RENAME TABLE students TO learners;
      12. INSERT INTO: Inserts a row
          INSERT INTO students (id, name, age) VALUES (1, 'Amit', 20);
      13. INSERT MULTIPLE ROWS: Inserts many rows
          INSERT INTO students VALUES (2, 'Neha', 22), (3, 'Raj', 23);
      14. UPDATE: Modifies existing data
          UPDATE students SET age = 25 WHERE id = 1;
      15. DELETE: Deletes a specific row
          DELETE FROM students WHERE id = 2;
      16. TRUNCATE: Deletes all rows from a table
          TRUNCATE TABLE students;
      17. INNER JOIN: Combines rows from two tables
          SELECT emp.name, dept.name FROM employees emp INNER JOIN departments dept ON emp.dept_id = dept.id;
      18. LEFT JOIN: Keeps all rows from the left table
          SELECT emp.name, dept.name FROM employees emp LEFT JOIN departments dept ON emp.dept_id = dept.id;
      19. RIGHT JOIN: Keeps all rows from the right table
          SELECT emp.name, dept.name FROM employees emp RIGHT JOIN departments dept ON emp.dept_id = dept.id;
      20. FULL OUTER JOIN: Keeps all data from both tables
          SELECT * FROM employees FULL OUTER JOIN departments ON employees.dept_id = departments.id;
      21. COUNT(): Counts rows
          SELECT COUNT(*) FROM employees;
      22. SUM(): Adds numeric values
          SELECT SUM(salary) FROM employees;
      23. AVG(): Gets the average
          SELECT AVG(age) FROM students;
      24. MAX() / MIN(): Finds the highest or lowest
          SELECT MAX(salary), MIN(salary) FROM employees;
      25. GROUP BY: Groups data
          SELECT department, COUNT(*) FROM employees GROUP BY department;
      26. HAVING: Filters grouped results
          SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5;
      27. IN: Matches values from a list
          SELECT * FROM employees WHERE department IN ('HR', 'Finance');
      28. NOT IN: Excludes values from a list
          SELECT * FROM employees WHERE department NOT IN ('HR');
      29. BETWEEN: Matches a range
          SELECT * FROM employees WHERE salary BETWEEN 30000 AND 60000;
      30. LIKE: Pattern match
          SELECT * FROM students WHERE name LIKE 'A%';
      31. IS NULL: Checks for null values
          SELECT * FROM employees WHERE manager_id IS NULL;
      32. EXISTS: Checks for existence of a subquery
          SELECT name FROM employees WHERE EXISTS (SELECT 1 FROM departments WHERE id = employees.dept_id);
      33. PRIMARY KEY: Unique identifier
          CREATE TABLE departments (id INT PRIMARY KEY, name VARCHAR(50));
      34. FOREIGN KEY: Defines a relationship
          CREATE TABLE employees (id INT, dept_id INT, FOREIGN KEY (dept_id) REFERENCES departments(id));
      35. NOT NULL / UNIQUE / DEFAULT: Enforces data rules
          CREATE TABLE users (id INT PRIMARY KEY, username VARCHAR(50) UNIQUE NOT NULL, status VARCHAR(20) DEFAULT 'active');
      36. CREATE VIEW: Saves a query as a virtual table
          CREATE VIEW active_users AS SELECT * FROM users WHERE status = 'active';
      37. DROP VIEW: Removes a view
          DROP VIEW active_users;
      38. CREATE INDEX: Speeds up lookups
          CREATE INDEX idx_name ON employees(name);
      39. DROP INDEX: Removes an index
          DROP INDEX idx_name;
      40. BEGIN / COMMIT: Starts and saves a transaction
          BEGIN; UPDATE employees SET salary = salary + 1000; COMMIT;
      41. ROLLBACK: Reverts a transaction
          BEGIN; DELETE FROM employees WHERE id = 10; ROLLBACK;
      42. CASE: Conditional logic in queries
          SELECT name, CASE WHEN salary > 50000 THEN 'High' ELSE 'Low' END AS salary_level FROM employees;
      43. COALESCE(): Returns the first non-null value
          SELECT name, COALESCE(phone, 'N/A') AS contact_number FROM users;
      44. NULLIF(): Returns NULL if arguments are equal
          SELECT NULLIF(salary, 0);
      45. GREATEST() / LEAST(): Finds the max/min of multiple values
          SELECT GREATEST(100, 250, 75);
      46. ROUND(), FLOOR(), CEIL(): Rounds numbers
          SELECT ROUND(45.678, 2), FLOOR(45.678), CEIL(45.678);
      47. CAST(): Converts data types
          SELECT CAST('2025-06-22' AS DATE);
      48. DATE_ADD(): Adds interval to date
          SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);
      49. EXTRACT(): Extracts part of date
          SELECT EXTRACT(YEAR FROM CURRENT_DATE);
      50. NOW(): Gets current timestamp
          SELECT NOW();
      51. String Functions: Basic text manipulation
          SELECT UPPER(name), LOWER(name), LENGTH(name) FROM users;
      52. REPLACE(): Replaces substrings
          SELECT REPLACE(name, 'a', '@') FROM users;
      53. CONCAT(): Combines strings
          SELECT CONCAT(first_name, ' ', last_name) FROM users;
      54. REGEXP: Regular expressions
          SELECT * FROM users WHERE name REGEXP '^A[a-z]*$';
      55. UUID(): Generates unique IDs
          SELECT UUID();
      56. ROW_NUMBER(): Assigns unique row numbers
          SELECT name, ROW_NUMBER() OVER (ORDER BY salary DESC) FROM employees;
      57. RANK(): Assigns rank with gaps
          SELECT name, RANK() OVER (ORDER BY salary DESC) FROM employees;
      58. NTILE(): Divides rows into equal groups
          SELECT name, NTILE(4) OVER (ORDER BY salary) FROM employees;
      59. LAG(): Gets previous row’s value
          SELECT name, LAG(salary) OVER (ORDER BY salary) FROM employees;
      60. LEAD(): Gets next row’s value
          SELECT name, LEAD(salary) OVER (ORDER BY salary) FROM employees;
      61. FIRST_VALUE(): Gets first value in group
          SELECT name, FIRST_VALUE(salary) OVER (ORDER BY salary DESC) FROM employees;
      62. SUM() OVER: Window sum
          SELECT name, salary, SUM(salary) OVER () AS total_salary FROM employees;
      63. PARTITION BY: Groups windowed functions
          SELECT department, name, RANK() OVER (PARTITION BY department ORDER BY salary DESC) FROM employees;
      64. WITH (CTE): Common table expression
          WITH top_emps AS (SELECT * FROM employees WHERE salary > 50000) SELECT * FROM top_emps;
      65. Recursive CTE: Builds recursion
          WITH RECURSIVE nums AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM nums WHERE n < 5) SELECT * FROM nums;
      66. Nested CTE: Uses multiple layers
          WITH base AS (SELECT * FROM sales), ranked AS (SELECT *, RANK() OVER (ORDER BY amount DESC) AS rnk FROM base) SELECT * FROM ranked WHERE rnk <= 5;
      67. Stored Procedure: Saves logic
          DELIMITER // CREATE PROCEDURE GetHighEarners() BEGIN SELECT * FROM employees WHERE salary > 60000; END // DELIMITER ;
      68. CALL Procedure: Executes procedure
          CALL GetHighEarners();
      69. BEFORE TRIGGER: Fires before insert/update
          CREATE TRIGGER before_insert BEFORE INSERT ON employees FOR EACH ROW SET NEW.created_at = NOW();
      70. AFTER TRIGGER: Fires after insert
          CREATE TRIGGER after_insert_log AFTER INSERT ON orders FOR EACH ROW INSERT INTO logs(action) VALUES ('Order Inserted');
      71. DISABLE TRIGGER: Temporarily disable
          ALTER TABLE employees DISABLE TRIGGER ALL;
      72. CREATE USER: Adds a DB user
          CREATE USER 'readonly'@'localhost' IDENTIFIED BY 'securepass';
      73. GRANT: Gives privileges
          GRANT SELECT ON company.* TO 'readonly'@'localhost';
      74. REVOKE: Removes privileges
          REVOKE SELECT ON company.* FROM 'readonly'@'localhost';
      75. DROP USER: Deletes user
          DROP USER 'readonly'@'localhost';
      76. SHOW GRANTS: Lists permissions
          SHOW GRANTS FOR 'readonly'@'localhost';
      77. SHOW TABLES: Lists all tables
          SHOW TABLES;
      78. DESCRIBE: Table structure
          DESCRIBE employees;
      79. EXPLAIN: Query plan
          EXPLAIN SELECT * FROM employees WHERE salary > 50000;
      80. INFORMATION_SCHEMA: Metadata access
          SELECT table_name FROM information_schema.tables WHERE table_schema = 'your_db';
      81. SHOW PROCESSLIST: Active connections
          SHOW PROCESSLIST;
      82. CHAR_LENGTH(): Returns the number of characters in a string
          SELECT CHAR_LENGTH('OpenAI');
      83. POSITION(): Finds the position of substring
          SELECT POSITION('e' IN 'OpenAI');
      84. SUBSTRING(): Extracts part of a string
          SELECT SUBSTRING('OpenAI', 2, 3);
      85. LOCATE(): Finds position of substring
          SELECT LOCATE('AI', 'OpenAI Labs');
      86. REVERSE(): Reverses a string
          SELECT REVERSE('SQL');
      87. LTRIM() / RTRIM(): Trims spaces from left/right
          SELECT LTRIM(' SQL'), RTRIM('SQL ');
      88. LPAD() / RPAD(): Pads strings to fixed length
          SELECT LPAD('42', 5, '0'), RPAD('Hi', 5, '!');
      89. ASCII(): Returns ASCII value
          SELECT ASCII('A');
      90. CONVERT(): Changes data type
          SELECT CONVERT('123', UNSIGNED INTEGER);
      91. BIN() / HEX(): Converts number to binary or hex
          SELECT BIN(5), HEX(255);
      92. MOD(): Modulus operator
          SELECT MOD(10, 3);
      93. POWER(): Exponentiation
          SELECT POWER(2, 3);
      94. RAND(): Returns a random number
          SELECT RAND();
      95. IF(): Conditional logic (like ternary operator)
          SELECT IF(salary > 50000, 'High', 'Low') FROM employees;
      96. ISNULL(): Checks if a value is null
          SELECT ISNULL(manager_id) FROM employees;
      97. DATABASE(): Shows current DB
          SELECT DATABASE();
      98. VERSION(): Returns DB version
          SELECT VERSION();
      99. USER(): Returns current DB user
          SELECT USER();
      100. LAST_INSERT_ID(): Gets last auto-incremented ID
           SELECT LAST_INSERT_ID();

Also Read : Best AI Tools To Improve Communication Skills

The top 100 most helpful SQL commands that every developer, analyst, and DBA should be familiar with are hand-picked and explained in this blog post, along with clear explanations and real-world examples. This is your one-stop resource for learning SQL!
Every database administrator, developer, and data enthusiast needs SQL in their toolbox. From basic data retrieval to sophisticated joins, window functions, and performance-tuning tools, these 100 commands have it all.