100-SQL-Commands.md

Example syntax for 100 SQL commands/functions

Examples use standard SQL where possible (some are vendor-specific and noted).

---

🔹 1. Data Query Language (DQL)

1. SELECT

SELECT name, age FROM users;

2. SELECT DISTINCT

SELECT DISTINCT country FROM customers;

3. FROM

SELECT * FROM orders;

4. WHERE

SELECT * FROM users WHERE age > 18;

5. GROUP BY

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

6. HAVING

SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

7. ORDER BY

SELECT * FROM products ORDER BY price DESC;

8. LIMIT (MySQL/PostgreSQL)

SELECT * FROM users LIMIT 10;

9. OFFSET

SELECT * FROM users LIMIT 10 OFFSET 20;

10. FETCH FIRST (ANSI SQL)

SELECT * FROM users FETCH FIRST 10 ROWS ONLY;

---

🔹 2. Data Manipulation Language (DML)

11. INSERT

INSERT INTO users (name, age) VALUES ('John', 25);

12. INSERT INTO SELECT

INSERT INTO archive_users (name, age)
SELECT name, age FROM users WHERE active = 0;

13. UPDATE

UPDATE users SET age = 26 WHERE name = 'John';

14. DELETE

DELETE FROM users WHERE age < 18;

15. TRUNCATE

TRUNCATE TABLE logs;

16. MERGE (SQL Server/Oracle)

MERGE INTO target t
USING source s
ON (t.id = s.id)
WHEN MATCHED THEN UPDATE SET t.name = s.name
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name);

17. REPLACE (MySQL)

REPLACE INTO users (id, name) VALUES (1, 'Alice');

18. UPSERT (PostgreSQL)

INSERT INTO users (id, name)
VALUES (1, 'Alice')
ON CONFLICT (id)
DO UPDATE SET name = EXCLUDED.name;

19. CALL

CALL GetUserStats();

20. EXPLAIN

EXPLAIN SELECT * FROM users WHERE id = 1;

---

🔹 3. Data Definition Language (DDL)

21. CREATE DATABASE

CREATE DATABASE company_db;

22. DROP DATABASE

DROP DATABASE company_db;

23. ALTER DATABASE

ALTER DATABASE company_db SET READ_ONLY = 1;

24. CREATE TABLE

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  age INT
);

25. DROP TABLE

DROP TABLE users;

26. ALTER TABLE

ALTER TABLE users ADD email VARCHAR(100);

27. RENAME TABLE (MySQL)

RENAME TABLE users TO customers;

28. CREATE INDEX

CREATE INDEX idx_name ON users(name);

29. DROP INDEX

DROP INDEX idx_name ON users;

30. CREATE VIEW

CREATE VIEW active_users AS
SELECT * FROM users WHERE active = 1;

31. DROP VIEW

DROP VIEW active_users;

32. CREATE SEQUENCE

CREATE SEQUENCE order_seq START WITH 1 INCREMENT BY 1;

33. DROP SEQUENCE

DROP SEQUENCE order_seq;

34. CREATE TRIGGER

CREATE TRIGGER before_insert_users
BEFORE INSERT ON users
FOR EACH ROW
SET NEW.created_at = NOW();

35. DROP TRIGGER

DROP TRIGGER before_insert_users;

36. CREATE FUNCTION

CREATE FUNCTION get_age(birthdate DATE)
RETURNS INT
RETURN YEAR(CURDATE()) - YEAR(birthdate);

37. DROP FUNCTION

DROP FUNCTION get_age;

38. CREATE PROCEDURE

CREATE PROCEDURE GetUsers()
BEGIN
  SELECT * FROM users;
END;

39. DROP PROCEDURE

DROP PROCEDURE GetUsers;

40. COMMENT

COMMENT ON TABLE users IS 'Stores user information';

---

🔹 4. Constraints

41. PRIMARY KEY

id INT PRIMARY KEY

42. FOREIGN KEY

FOREIGN KEY (user_id) REFERENCES users(id)

43. UNIQUE

email VARCHAR(100) UNIQUE

44. NOT NULL

name VARCHAR(100) NOT NULL

45. CHECK

age INT CHECK (age >= 18)

46. DEFAULT

status VARCHAR(20) DEFAULT 'active'

47. AUTO_INCREMENT (MySQL)

id INT AUTO_INCREMENT PRIMARY KEY

48. IDENTITY (SQL Server)

id INT IDENTITY(1,1) PRIMARY KEY

49. ON DELETE CASCADE

FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

50. ON UPDATE CASCADE

FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE

---

🔹 5–10 (Condensed for readability)

Below are concise syntax examples.

---

Joins

51. INNER JOIN

SELECT * FROM orders
INNER JOIN users ON orders.user_id = users.id;

52. LEFT JOIN

SELECT * FROM users
LEFT JOIN orders ON users.id = orders.user_id;

53. RIGHT JOIN

SELECT * FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

54. FULL JOIN

SELECT * FROM users
FULL JOIN orders ON users.id = orders.user_id;

55. CROSS JOIN

SELECT * FROM users CROSS JOIN roles;

---

Aggregate Functions

56. COUNT()

SELECT COUNT(*) FROM users;

57. SUM()

SELECT SUM(price) FROM orders;

58. AVG()

SELECT AVG(price) FROM orders;

59. MIN()

SELECT MIN(price) FROM products;

60. MAX()

SELECT MAX(price) FROM products;

---

String Functions

61. CONCAT()

SELECT CONCAT(first_name, ' ', last_name) FROM users;

62. SUBSTRING()

SELECT SUBSTRING(name, 1, 3) FROM users;

63. UPPER()

SELECT UPPER(name) FROM users;

64. LOWER()

SELECT LOWER(name) FROM users;

65. TRIM()

SELECT TRIM(name) FROM users;

---

Date Functions

66. NOW()

SELECT NOW();

67. CURRENT_DATE

SELECT CURRENT_DATE;

68. DATE_ADD()

SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);

69. DATEDIFF()

SELECT DATEDIFF('2025-12-31', '2025-01-01');

70. EXTRACT()

SELECT EXTRACT(YEAR FROM NOW());

---

Transactions

71. BEGIN TRANSACTION

BEGIN TRANSACTION;

72. COMMIT

COMMIT;

73. ROLLBACK

ROLLBACK;

74. SAVEPOINT

SAVEPOINT before_update;

75. SET TRANSACTION

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

---

Security

76. GRANT

GRANT SELECT ON users TO user1;

77. REVOKE

REVOKE SELECT ON users FROM user1;

78. CREATE USER

CREATE USER user1 IDENTIFIED BY 'password';

79. DROP USER

DROP USER user1;

80. ALTER USER

ALTER USER user1 IDENTIFIED BY 'newpassword';

---

🔹 Additional Date & Time Functions (continued)

81. CURRENT_TIME

SELECT CURRENT_TIME;

82. YEAR()

SELECT YEAR(order_date) FROM orders;

83. MONTH()

SELECT MONTH(order_date) FROM orders;

84. DAY()

SELECT DAY(order_date) FROM orders;

85. DATE_SUB()

SELECT DATE_SUB(NOW(), INTERVAL 30 DAY);

86. TIME()

SELECT TIME(NOW());

87. HOUR()

SELECT HOUR(order_time) FROM orders;

88. MINUTE()

SELECT MINUTE(order_time) FROM orders;

89. SECOND()

SELECT SECOND(order_time) FROM orders;

90. CURRENT_TIMESTAMP

SELECT CURRENT_TIMESTAMP;

---

🔹 Additional Transaction & Control Statements

91. SET AUTOCOMMIT (MySQL)

SET AUTOCOMMIT = 0;

92. LOCK TABLE

LOCK TABLE users WRITE;

93. UNLOCK TABLES

UNLOCK TABLES;

94. SET CONSTRAINTS (PostgreSQL)

SET CONSTRAINTS ALL DEFERRED;

95. ROLLBACK TO SAVEPOINT

ROLLBACK TO SAVEPOINT before_update;

---

🔹 Additional Administrative / Utility Commands

96. SHOW TABLES (MySQL)

SHOW TABLES;

97. SHOW DATABASES (MySQL)

SHOW DATABASES;

98. DESCRIBE (MySQL)

DESCRIBE users;

99. ANALYZE TABLE

ANALYZE TABLE users;

100. VACUUM (PostgreSQL)

VACUUM;

---

⚠️ Note: Some commands vary slightly between MySQL, PostgreSQL, SQL Server, and Oracle.