Listen to this Post
Mastering SQL isn’t just about writing queries—it’s about writing them efficiently! Here’s a complete cheat sheet for SQL queries from basic to advanced.
1. Basic SQL Queries
🔹 Select All Data
SELECT FROM employees;
🔹 Select Specific Columns
SELECT name, age FROM employees;
🔹 Filter Data (WHERE Clause)
SELECT FROM employees WHERE age > 30;
🔹 Sort Data (ORDER BY)
SELECT FROM employees ORDER BY salary DESC;
🔹 Limit Number of Results
SELECT FROM employees LIMIT 5;
2. Joins (Combining Tables)
🔹 INNER JOIN (Matching Rows in Both Tables)
SELECT employees.name, departments.department_name FROM employees INNER JOIN departments ON employees.department_id = departments.id;
🔹 LEFT JOIN (All from Left Table + Matching in Right Table)
SELECT employees.name, departments.department_name FROM employees LEFT JOIN departments ON employees.department_id = departments.id;
🔹 RIGHT JOIN (All from Right Table + Matching in Left Table)
SELECT employees.name, departments.department_name FROM employees RIGHT JOIN departments ON employees.department_id = departments.id;
🔹 FULL OUTER JOIN (All from Both Tables)
SELECT employees.name, departments.department_name FROM employees FULL OUTER JOIN departments ON employees.department_id = departments.id;
3. Aggregation & Grouping
🔹 Count Total Rows
SELECT COUNT() FROM employees;
🔹 Sum of a Column
SELECT SUM(salary) FROM employees;
🔹 Find Average Salary
SELECT AVG(salary) FROM employees;
🔹 Find Maximum & Minimum Salary
SELECT MAX(salary) FROM employees; SELECT MIN(salary) FROM employees;
🔹 Group Data (GROUP BY & HAVING)
SELECT department, COUNT() FROM employees GROUP BY department;
4. Subqueries & Advanced SQL
🔹 Subquery in WHERE Clause
SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
🔹 Find Employees Who Earn More Than Their Manager
SELECT emp.name, emp.salary FROM employees emp JOIN employees mgr ON emp.manager_id = mgr.id WHERE emp.salary > mgr.salary;
🔹 Self Join (Find Employees in Same Department)
SELECT e1.name AS Employee, e2.name AS Colleague FROM employees e1 JOIN employees e2 ON e1.department_id = e2.department_id WHERE e1.id <> e2.id;
You Should Know:
SQL Injection Prevention Commands
-- Use parameterized queries PREPARE stmt FROM 'SELECT FROM users WHERE username = ? AND password = ?'; EXECUTE stmt USING @username, @password; -- MySQL secure installation sudo mysql_secure_installation -- PostgreSQL create role with limited privileges CREATE ROLE readonly WITH LOGIN PASSWORD 'securepassword'; GRANT CONNECT ON DATABASE mydb TO readonly; GRANT USAGE ON SCHEMA public TO readonly; GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
Database Backup Commands
MySQL dump mysqldump -u root -p --all-databases > full_backup.sql PostgreSQL dump pg_dump -U postgres -d mydb -f mydb_backup.sql SQLite backup sqlite3 mydb.db ".backup 'mydb_backup.db'"
Performance Monitoring Commands
-- MySQL show process list SHOW PROCESSLIST; -- PostgreSQL active queries SELECT pid, age(clock_timestamp(), query_start), usename, query FROM pg_stat_activity WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' ORDER BY query_start desc; -- SQLite performance tuning PRAGMA journal_mode = WAL; PRAGMA synchronous = NORMAL; PRAGMA cache_size = -10000; -- 10MB cache
Database Security Hardening
MySQL audit plugin installation INSTALL PLUGIN audit_log SONAME 'audit_log.so'; PostgreSQL enable SSL ssl = on ssl_cert_file = '/etc/ssl/certs/server.crt' ssl_key_file = '/etc/ssl/private/server.key' SQLite encryption (using SQLCipher) PRAGMA key = 'encryptionkey';
Database Migration Commands
MySQL to PostgreSQL conversion pgloader mysql://user:password@localhost/source_db postgresql://user:password@localhost/target_db CSV import to MySQL mysqlimport --ignore-lines=1 --fields-terminated-by=, --local -u root -p database_name file.csv SQLite to MySQL conversion sqlite3 mydb.db .dump | mysql -u root -p mysql_database
What Undercode Say:
SQL mastery extends beyond basic queries – it’s about understanding database architecture, optimization, and security. The commands provided here cover essential operations from basic CRUD to advanced security hardening.
For production environments, always:
1. Implement regular backups (automate with cron)
2. Use parameterized queries to prevent SQL injection
3. Monitor performance with built-in database tools
- Apply principle of least privilege for database users
5. Consider connection pooling for web applications
- Enable query logging for debugging (but disable in production)
7. Regularly update your database software
8. Implement proper indexing strategies
9. Consider read replicas for scaling
10. Always test migrations in staging first
Database administration Linux commands to know:
Check database service status sudo systemctl status mysql sudo systemctl status postgresql Start/stop database services sudo systemctl start mysql sudo systemctl stop postgresql Check disk space for databases df -h /var/lib/mysql df -h /var/lib/postgresql Monitor database connections netstat -anp | grep mysql ss -tulpn | grep postgres Check database logs sudo tail -f /var/log/mysql/error.log sudo journalctl -u postgresql -f
Expected Output:
A comprehensive SQL reference guide covering basic to advanced queries with practical database administration commands for MySQL, PostgreSQL and SQLite, including security hardening and performance optimization techniques.
References:
Reported By: Surajdubey Codes – Hackers Feeds
Extra Hub: Undercode MoN
Basic Verification: Pass ✅
