Listen to this Post
These SQL queries are essential for acing technical interviews. Mastering them will give you a strong foundation in database operations.
You Should Know:
1️⃣ Find the 2nd Highest Salary
SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);
Alternative (Using Window Functions):
SELECT DISTINCT salary FROM ( SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rank FROM employees ) WHERE rank = 2;
2️⃣ Count of Employees in Each Department
SELECT department, COUNT() AS employee_count FROM employees GROUP BY department;
Linux Alternative (Using CSV & `awk`):
awk -F, '{print $2}' employees.csv | sort | uniq -c
3️⃣ Find Duplicate Records
SELECT column_name, COUNT() FROM table_name GROUP BY column_name HAVING COUNT() > 1;
Bash Alternative (Using `uniq -d`):
sort data.txt | uniq -d
4️⃣ Find Employees Who Joined in 2024
SELECT FROM employees WHERE YEAR(joining_date) = 2024;
Linux Date Filtering:
awk -F, '$3 ~ /2024/' employees.csv
5️⃣ Retrieve Top 5 Records
SELECT FROM employees ORDER BY salary DESC LIMIT 5;
Linux Equivalent (`head`):
sort -k4 -nr employees.csv | head -5
6️⃣ Find Customers Without Orders
SELECT c.customer_id, c.name FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE o.customer_id IS NULL;
Using `comm` in Linux:
comm -23 <(sort customers.txt) <(sort orders.txt)
7️⃣ Get Total Salary by Department
SELECT department, SUM(salary) AS total_salary FROM employees GROUP BY department;
Bash Alternative (`awk`):
awk -F, '{sum[$2]+=$4} END {for (dept in sum) print dept, sum[bash]}' employees.csv
8️⃣ Find Nth Highest Salary (Nth = 3)
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 2;
Using `sed` for Extraction:
sort -k4 -nr employees.csv | awk '{print $4}' | uniq | sed -n '3p'
9️⃣ Get Employee Details with Department Names
SELECT e.name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id;
Joining Files in Linux (`join`):
join -1 2 -2 1 <(sort -k2 employees.csv) <(sort departments.csv)
What Undercode Say:
SQL remains a critical skill for data professionals, but integrating command-line tools (awk, sort, join) can enhance efficiency. Automation through scripting (bash, Python) reduces manual SQL execution. Future-proof your skills by combining SQL with cloud-based data solutions (BigQuery, Snowflake).
Prediction:
SQL will evolve with AI-assisted query optimization, but core logic (joins, aggregations) remains timeless.
Expected Output:
department,employee_count IT,15 HR,8 Sales,22
🔗 Relevant Course: Advanced SQL for Data Engineering
IT/Security Reporter URL:
Reported By: Aishwarya Pani – Hackers Feeds
Extra Hub: Undercode MoN
Basic Verification: Pass ✅
