Must-Know SQL Queries for Every Developer!

Listen to this Post

🐢▶️ Listen🚀

SQL queries are essential for managing and retrieving data efficiently. Here’s a list of basic to advanced SQL queries you should master!

1. Basic Queries

✅ SELECT – Retrieve all data from a table

SELECT * FROM employees;

✅ SELECT specific columns

SELECT name, salary FROM employees;

✅ DISTINCT – Get unique values

SELECT DISTINCT department FROM employees;

✅ WHERE – Filter results

SELECT * FROM employees WHERE salary > 50000;

✅ ORDER BY – Sort results

SELECT * FROM employees ORDER BY salary DESC;

✅ LIMIT – Fetch specific number of records

SELECT * FROM employees LIMIT 5;

2. Filtering Data

✅ LIKE – Pattern matching

SELECT * FROM employees WHERE name LIKE 'A%'; -- Names starting with A
SELECT * FROM employees WHERE name LIKE '%son'; -- Names ending with "son"

✅ IN – Match multiple values

SELECT * FROM employees WHERE department IN ('IT', 'HR', 'Finance');

✅ BETWEEN – Select a range

SELECT * FROM employees WHERE salary BETWEEN 40000 AND 70000;

✅ IS NULL / IS NOT NULL – Check for null values

SELECT * FROM employees WHERE department IS NULL;

3. Joins (Combining Tables)

✅ INNER JOIN – Returns matching records from both tables

SELECT e.name, d.department_name 
FROM employees e 
INNER JOIN departments d ON e.department_id = d.id;

✅ LEFT JOIN – Returns all records from the left table and matching ones from the right

SELECT e.name, d.department_name 
FROM employees e 
LEFT JOIN departments d ON e.department_id = d.id;

✅ RIGHT JOIN – Returns all records from the right table and matching ones from the left

SELECT e.name, d.department_name 
FROM employees e 
RIGHT JOIN departments d ON e.department_id = d.id;

✅ FULL OUTER JOIN – Returns all records when there is a match in either table

SELECT e.name, d.department_name 
FROM employees e 
FULL OUTER JOIN departments d ON e.department_id = d.id;

4. Aggregate Queries

✅ COUNT – Count rows

SELECT COUNT(*) FROM employees;

✅ SUM – Total salary

SELECT SUM(salary) FROM employees;

✅ AVG – Average salary

SELECT AVG(salary) FROM employees;

✅ MAX & MIN – Highest and lowest salary

SELECT MAX(salary), MIN(salary) FROM employees;

✅ GROUP BY – Group data and use aggregate functions

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

✅ HAVING – Filter groups

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

You Should Know:

1. Practice SQL in Linux Terminal

Use the `mysql` command to connect to your database and practice SQL queries directly in the terminal:

mysql -u username -p

2. Export Query Results to a File

Use the `INTO OUTFILE` clause to export query results to a file:

SELECT * INTO OUTFILE '/tmp/employees.csv' 
FIELDS TERMINATED BY ',' 
FROM employees;

3. Automate SQL Scripts

Save your SQL queries in a `.sql` file and execute them using:

mysql -u username -p database_name < script.sql

4. Check Database Size

Use this query to check the size of your database:

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;

5. Backup Your Database

Use the `mysqldump` command to create a backup:

mysqldump -u username -p database_name > backup.sql

What Undercode Say:

Mastering SQL is a critical skill for developers, data analysts, and IT professionals. The queries and commands provided here are foundational but powerful enough to handle most database operations. Practice these commands in real-world scenarios, automate repetitive tasks, and explore advanced SQL features like window functions and stored procedures. SQL is not just a query language; it’s a gateway to efficient data management and analysis.

Expected Output:

1. Basic Queries: Retrieve, filter, and sort data.

1. Filtering Data: Use LIKE, IN, BETWEEN, and `IS NULL` for precise data extraction.
2. Joins: Combine data from multiple tables using INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.
3. Aggregate Queries: Perform calculations like COUNT, SUM, AVG, MAX, and `MIN` on datasets.
4. Linux Commands: Use mysql, mysqldump, and automation scripts to enhance productivity.

By mastering these SQL queries and commands, you’ll be well-equipped to handle complex database tasks and excel in your IT career.

References:

Reported By: Deepasajjanshetty Sql – Hackers Feeds
Extra Hub: Undercode MoN
Basic Verification: Pass ✅

Join Our Cyber World:

💬 Whatsapp | 💬 Telegram