SQL Commands Cheat Sheet: Learn with Examples

Posted on Sep 23, 2024 in Technology

1. Basic SQL Commands

• SELECT

  SELECT column1, column2, ... FROM table_name WHERE condition;

• INSERT

  INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

• UPDATE

  UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

• DELETE

  DELETE FROM table_name WHERE condition;

2. Table Operations

• CREATE TABLE

  CREATE TABLE table_name ( column1 datatype constraints, column2 datatype constraints, ...);

• ALTER TABLE

  ALTER TABLE table_name ADD column_name datatype; ALTER TABLE table_name DROP COLUMN column_name; ALTER TABLE table_name MODIFY COLUMN column_name datatype;

• DROP TABLE

  DROP TABLE table_name

3. Joins

• INNER JOIN

  SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.column;

• LEFT JOIN

  SELECT columns FROM table1 LEFT JOIN table2 ON table1.column = table2.column;

• RIGHT JOIN

  SELECT columns FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;

• FULL JOIN

  SELECT columns FROM table1 FULL JOIN table2 ON table1.column = table2.column;

4. Aggregation Functions

• COUNT

  SELECT COUNT(column_name) FROM table_name;

• SUM

  SELECT SUM(column_name) FROM table_name;

• AVG

  SELECT AVG(column_name) FROM table_name;

• MIN

  SELECT MIN(column_name) FROM table_name;

• MAX

  SELECT MAX(column_name) FROM table_name;

5. Grouping and Sorting

• GROUP BY

  SELECT column_name, AGGREGATE_FUNCTION(column_name) FROM table_name GROUP BY column_name;

• HAVING

  SELECT column_name, AGGREGATE_FUNCTION(column_name) FROM table_name GROUP BY column_name HAVING AGGREGATE_FUNCTION(column_name) condition;

• ORDER BY

  SELECT column_name FROM table_name ORDER BY column_name [ASC | DESC];

6. Subqueries

• Simple Subquery

  SELECT column_name FROM table_name WHERE column_name = (SELECT column_name FROM table_name WHERE condition);

• Correlated Subquery

  SELECT column_name FROM table_name t1 WHERE EXISTS (SELECT 1 FROM table_name t2 WHERE t2.column_name = t1.column_name);

7. Common Table Expressions (CTE)

• Basic CTE

  WITH CTE_name AS ( SELECT column_name FROM table_name WHERE condition ) SELECT * FROM CTE_name;

• Recursive CTE

  WITH RECURSIVE CTE_name AS ( SELECT column_name FROM table_name WHERE condition UNION ALL SELECT column_name FROM table_name JOIN CTE_name ON table_name.column_name = CTE_name.column_name ) SELECT * FROM CTE_name;

8. Window Functions

• ROW_NUMBER

  SELECT column_name, ROW_NUMBER() OVER (PARTITION BY partition_column ORDER BY order_column) AS row_num FROM table_name;

• RANK

  SELECT column_name, RANK() OVER (PARTITION BY partition_column ORDER BY order_column) AS rank FROM table_name;

• DENSE_RANK

  SELECT column_name, DENSE_RANK() OVER (PARTITION BY partition_column ORDER BY order_column) AS dense_rank FROM table_name;

• SUM, AVG, MIN, MAX with Window Functions

  SELECT column_name, SUM(column_name) OVER (PARTITION BY partition_column ORDER BY order_column) AS cumulative_sum FROM table_name;

9. Stored Procedures

• Create Procedure

  CREATE PROCEDURE procedure_name (parameter_name data_type) AS BEGIN SQL_statement; END;

• Execute Procedure

  EXEC procedure_name parameter_value;

10. Triggers

• Create Trigger

  CREATE TRIGGER trigger_name ON table_name AFTER INSERT | UPDATE | DELETE AS BEGIN SQL_statement; END;

11. Views

• Create View

  CREATE VIEW view_name AS SELECT column_name FROM table_name WHERE condition;

• Drop View

  DROP VIEW view_name;

12. Cursors

• Declare Cursor

  DECLARE cursor_name CURSOR FOR SELECT column_name FROM table_name;

• Open Cursor

  OPEN cursor_name;

• Fetch Data

  FETCH NEXT FROM cursor_name INTO variable;

• Close and Deallocate Cursor

  CLOSE cursor_name; DEALLOCATE cursor_name;

13. Indexes

• Create Index

  CREATE INDEX index_name ON table_name (column_name);

• Drop Index

  DROP INDEX index_name ON table_name;

14. Functions

• Create Function

  CREATE FUNCTION function_name (parameters) RETURNS return_data_type AS BEGIN RETURN value; END;

• Use Function

  SELECT function_name(parameters);

15. Transactions

• Start Transaction

  BEGIN TRANSACTION;

• Commit Transaction

  COMMIT;

• Rollback Transaction

  ROLLBACK;

16. Data Types

• Numeric Types: INT, FLOAT, DECIMAL
• String Types: VARCHAR, CHAR, TEXT
• Date/Time Types: DATE, DATETIME, TIMESTAMP

17. Constraints

• Primary Key

  PRIMARY KEY (column_name);

• Foreign Key

  FOREIGN KEY (column_name) REFERENCES other_table(column_name);

• Unique

  UNIQUE (column_name);

• Check

  CHECK (condition);

18. Data Manipulation

• MERGE (Upsert)

  MERGE INTO target_table AS target USING source_table AS source ON target.key = source.key WHEN MATCHED THEN UPDATE SET target.column = source.column WHEN NOT MATCHED THEN INSERT (columns) VALUES (values);

19. Performance Tuning

• EXPLAIN

  EXPLAIN SELECT column_name FROM table_name WHERE condition;

• Optimization Tips

  • Use indexes judiciously.
  • Avoid using SELECT *.
  • Optimize joins and subqueries.

20. User Management

• Create User

  CREATE USER 'username'@'host' IDENTIFIED BY 'password';

• Grant Privileges

  GRANT ALL PRIVILEGES ON database.* TO 'username'@'host';

• Revoke Privileges

  REVOKE ALL PRIVILEGES ON database.* FROM 'username'@'host';

21. Data Security

• Encrypt Data

  SELECT AES_ENCRYPT('data', 'key');

• Decrypt Data

  SELECT AES_DECRYPT(encrypted_data, 'key');

22. Backup and Restore

• Backup Database

  BACKUP DATABASE database_name TO DISK = 'path_to_backup_file';

• Restore Database

  RESTORE DATABASE database_name FROM DISK = 'path_to_backup_file';