Listen to this Post
SQL is the backbone of databases, and mastering it is crucial for every developer, data analyst, and engineer. This cheatsheet covers basic to advanced SQL concepts at a glance!
Basic SQL Commands
✅ SELECT – Retrieves data from a table
SELECT column1, column2 FROM table_name;
✅ WHERE – Filters data based on a condition
SELECT * FROM employees WHERE age > 30;
✅ ORDER BY – Sorts data in ascending or descending order
SELECT * FROM employees ORDER BY salary DESC;
✅ LIMIT – Limits the number of rows returned
SELECT * FROM products LIMIT 10;
✅ DISTINCT – Retrieves unique values
SELECT DISTINCT department FROM employees;
SQL Joins (Combining Tables)
✅ INNER JOIN – Returns matching records from both tables
SELECT employees.name, department.dept_name FROM employees INNER JOIN department ON employees.dept_id = department.id;
✅ LEFT JOIN – Returns all records from the left table & matching records from the right
SELECT customers.name, orders.order_id FROM customers LEFT JOIN orders ON customers.id = orders.customer_id;
✅ RIGHT JOIN – Returns all records from the right table & matching records from the left
SELECT employees.name, projects.project_name FROM employees RIGHT JOIN projects ON employees.id = projects.employee_id;
✅ FULL OUTER JOIN – Returns all records from both tables
SELECT students.name, courses.course_name FROM students FULL OUTER JOIN courses ON students.course_id = courses.id;
Filtering & Grouping Data
✅ GROUP BY – Groups records based on a column
SELECT department, COUNT(*) FROM employees GROUP BY department;
✅ HAVING – Filters grouped data
SELECT department, COUNT(<em>) FROM employees GROUP BY department HAVING COUNT(</em>) > 5;
Aggregation Functions
✅ COUNT() – Returns the number of rows
SELECT COUNT(*) FROM employees;
✅ SUM() – Returns the sum of values
SELECT SUM(salary) FROM employees;
✅ AVG() – Returns the average value
SELECT AVG(salary) FROM employees;
✅ MAX() / MIN() – Returns the highest or lowest value
SELECT MAX(salary), MIN(salary) FROM employees;
Modifying Data
✅ INSERT INTO – Adds a new record
INSERT INTO employees (name, age, department)
VALUES ('John Doe', 30, 'IT');
✅ UPDATE – Updates existing records
UPDATE employees SET salary = 50000 WHERE id = 1;
✅ DELETE – Removes records
DELETE FROM employees WHERE age < 25;
You Should Know:
1. Practice SQL Commands in Linux Terminal
Use `sqlite3` to practice SQL commands directly in your Linux terminal:
sudo apt install sqlite3 sqlite3 test.db
2. Export SQL Query Results to a File
sqlite3 test.db "SELECT * FROM employees;" > output.txt
3. Automate SQL Scripts
Save your SQL commands in a `.sql` file and run them using:
sqlite3 test.db < script.sql
4. Backup Your Database
Use the `.dump` command to create a backup:
sqlite3 test.db ".dump" > backup.sql
5. Import Data from CSV
Import CSV data into SQLite:
sqlite3 test.db ".mode csv" ".import data.csv table_name"
What Undercode Say:
Mastering SQL is essential for anyone working with databases, whether you’re a developer, data analyst, or backend engineer. The commands and examples provided here are just the beginning. Practice regularly, experiment with different datasets, and explore advanced SQL features like window functions, subqueries, and indexing to enhance your database management skills.
For further learning, check out these resources:
Keep practicing, and you’ll soon be writing efficient, optimized SQL queries like a pro!
References:
Reported By: Deepasajjanshetty The – Hackers Feeds
Extra Hub: Undercode MoN
Basic Verification: Pass ✅
