Tricky SQL Interview Questions And Solutions

2025-02-04

Here are some tricky SQL interview questions along with their solutions:

Find the second-highest salary in a table without using LIMIT or TOP.

SELECT MAX(salary) FROM table WHERE salary NOT IN (SELECT MAX(salary) FROM table);

Write a SQL query to find all employees who earn more than their managers.

SELECT e1.* FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.id WHERE e1.salary > e2.salary;

Find the duplicate rows in a table without using GROUP BY.

SELECT * FROM table WHERE rowid IN (SELECT rowid FROM table GROUP BY column HAVING COUNT(*) > 1);

Write a SQL query to find the top 10% of earners in a table.

SELECT * FROM table WHERE salary > (SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY salary) FROM table);

Find the cumulative sum of a column in a table.

SELECT column, SUM(column) OVER (ORDER BY rowid) FROM table;

Write a SQL query to find all employees who have never taken a leave.

SELECT * FROM employees WHERE id NOT IN (SELECT employee_id FROM leaves);

Find the difference between the current row and the next row in a table.
```
SELECT *, column - LEAD(column) OVER (ORDER BY rowid) FROM table;
```

Write a SQL query to find all departments with more than one employee.

SELECT department FROM employees GROUP BY department HAVING COUNT(*) > 1;

Find the maximum value of a column for each group without using GROUP BY.

SELECT MAX(column) FROM table WHERE column NOT IN (SELECT MAX(column) FROM table GROUP BY group_column);

Write a SQL query to find all employees who have taken more than 3 leaves in a month.

SELECT * FROM employees WHERE id IN (SELECT employee_id FROM leaves GROUP BY employee_id HAVING COUNT(*) > 3);

What Undercode Say

SQL is a powerful language for managing and manipulating relational databases. Mastering SQL requires not only understanding basic queries but also being able to solve complex problems efficiently. The questions and solutions provided above are designed to test and improve your SQL skills, particularly in scenarios that require creative thinking and efficient query writing.

To further enhance your SQL skills, consider practicing the following Linux commands and tools that are often used in conjunction with SQL for database management and data analysis:

1. psql: The PostgreSQL interactive terminal.

psql -U username -d dbname

2. mysql: The MySQL command-line tool.

mysql -u username -p

3. sqlite3: The SQLite command-line interface.

sqlite3 dbname.db

4. pg_dump: Backup a PostgreSQL database.

pg_dump -U username -d dbname -f backup.sql

5. mysqldump: Backup a MySQL database.

mysqldump -u username -p dbname > backup.sql

sqlmap: Automated SQL injection and database takeover tool.
```
sqlmap -u "http://example.com/page?id=1" --dbs
```
csvkit: A suite of command-line tools for working with CSV files.
```
csvsql --query "SELECT * FROM data" data.csv
```

8. jq: Command-line JSON processor.

curl -s http://example.com/api | jq '.data'

awk: A versatile programming language for working with text files.
```
awk -F, '{print $1}' data.csv
```
sed: Stream editor for filtering and transforming text.
```
sed 's/old/new/g' file.txt
```

For more advanced SQL techniques and best practices, consider exploring the following resources:

By combining SQL with these Linux commands and tools, you can significantly enhance your data management and analysis capabilities. Keep practicing and exploring new challenges to become proficient in SQL and related technologies.

References:

Hackers Feeds, Undercode AI

Listen to this Post