SQL Cheat Sheet – Must-Know Queries!

2025-02-16

🔹 Basic Queries

– `SELECT * FROM table_name;` – Fetch all data
– `SELECT column1, column2 FROM table_name;` – Fetch specific columns
– `WHERE` – Filter results (SELECT * FROM users WHERE age > 25;)
– `ORDER BY column DESC/ASC;` – Sort results

🔹 Joins

– `INNER JOIN` – Matches in both tables
– `LEFT JOIN` – All from left, matching from right
– `RIGHT JOIN` – All from right, matching from left

🔹 Aggregation

– `COUNT(*)` – Total rows
– `SUM(column_name)` – Total sum
– `AVG(column_name)` – Average value
– `GROUP BY column_name;` – Group results

🔹 Advanced

– `LIMIT 5;` – Show only 5 results
– `HAVING COUNT(*) > 10;` – Filter groups
– `INDEX` – Speed up queries (CREATE INDEX idx_name ON table(column);)

💡 Tip: Always use indexes for large datasets & optimize queries for better performance!

Practice Verified Codes and Commands

1. Basic Query Example

SELECT name, age FROM users WHERE age > 25 ORDER BY age DESC; 

2. Join Example

SELECT users.name, orders.order_id 
FROM users 
INNER JOIN orders ON users.user_id = orders.user_id; 

3. Aggregation Example

SELECT department, AVG(salary) AS avg_salary 
FROM employees 
GROUP BY department 
HAVING AVG(salary) > 50000; 

4. Index Creation

CREATE INDEX idx_email ON users(email); 

5. Limit Example

SELECT * FROM products LIMIT 10; 

What Undercode Say

SQL is a powerful tool for managing and querying relational databases. Mastering SQL commands is essential for database administrators, developers, and data analysts. Here are some additional tips and commands to enhance your SQL skills:

• Backup Database:

  mysqldump -u username -p database_name > backup_file.sql 
  

• Restore Database:

  mysql -u username -p database_name < backup_file.sql 
  

• Linux Command to Monitor Database Performance:

  top -p $(pgrep mysqld) 
  

• Windows Command to Check SQL Server Status:
  [cmd]
  sc query MSSQLSERVER
  [/cmd]

• Optimize MySQL Query Performance:

  EXPLAIN SELECT * FROM users WHERE age > 25; 
  

• Create a New User in MySQL:

  CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password'; 
  GRANT ALL PRIVILEGES ON database_name.* TO 'new_user'@'localhost'; 
  FLUSH PRIVILEGES; 
  

• Check Database Size:

  SELECT table_schema AS "Database", 
  ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" 
  FROM information_schema.TABLES 
  GROUP BY table_schema; 
  

For further reading, check out these resources:

• MySQL Documentation
• SQL Tutorial
• PostgreSQL Guide

SQL is a foundational skill in IT and cybersecurity, especially for managing data and securing databases. Practice these commands regularly to become proficient and efficient in handling databases.

References:

Hackers Feeds, Undercode AI