Top SQL Interview Questions - Undercode Testing

What is the difference between WHERE and HAVING?
– `WHERE` filters rows before grouping, while `HAVING` filters after grouping.

– Example:

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

2. Explain JOINS (INNER, LEFT, RIGHT, FULL).

INNER JOIN: Returns rows with matching values in both tables.

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

LEFT JOIN: Returns all rows from the left table and matched rows from the right table.
RIGHT JOIN: Returns all rows from the right table and matched rows from the left table.
FULL JOIN: Returns all rows when there is a match in either table.

3. What are primary key and foreign key?

PRIMARY KEY: Uniquely identifies each record in a table.
FOREIGN KEY: Ensures referential integrity by linking two tables.

4. Difference between DELETE, TRUNCATE, and DROP?

DELETE: Removes rows one by one and can be rolled back.
```
DELETE FROM employees WHERE id = 10;
```
TRUNCATE: Removes all rows quickly and cannot be rolled back.
```
TRUNCATE TABLE employees;
```
DROP: Deletes the entire table structure.
```
DROP TABLE employees;
```

5. What is a subquery?

A query within another query.

SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

6. How to find duplicate records?

SELECT name, COUNT(<em>) 
FROM employees 
GROUP BY name 
HAVING COUNT(</em>) > 1;

7. What is the use of GROUP BY?

Groups rows with the same values into summary rows.

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

8. How to get the 2nd highest salary?

SELECT MAX(salary) 
FROM employees 
WHERE salary < (SELECT MAX(salary) FROM employees);

What Undercode Say

SQL is a powerful language for managing and manipulating relational databases. Mastering SQL commands like SELECT, JOIN, GROUP BY, and subqueries is essential for database management and data analysis. Understanding the differences between DELETE, TRUNCATE, and `DROP` helps in maintaining database integrity. For Linux users, SQL commands can be executed in terminal using tools like `mysql` or psql. For example, to connect to a MySQL database:

mysql -u username -p -h hostname database_name

For PostgreSQL:

psql -U username -h hostname -d database_name

In Windows, you can use PowerShell to connect to databases using similar commands. For advanced SQL operations, consider using `EXPLAIN` to analyze query performance:

EXPLAIN SELECT * FROM employees WHERE salary > 50000;

For more advanced SQL techniques, refer to SQL Tutorial and PostgreSQL Documentation.

SQL is not just limited to databases; it can be integrated with programming languages like Python using libraries such as `sqlite3` or SQLAlchemy. For example, to connect to a SQLite database in Python:

import sqlite3
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
cursor.execute('SELECT * FROM employees')
rows = cursor.fetchall()
for row in rows:
print(row)
conn.close()

In conclusion, SQL is a versatile tool that is essential for anyone working with data. Whether you’re preparing for an interview or managing a database, understanding these concepts and commands will significantly enhance your skills. For further learning, explore LeetCode SQL Problems and HackerRank SQL Challenges.

References:

initially reported by: https://www.linkedin.com/posts/sumit-yadav-08562422b_sql-activity-7302162598160965632-8myA – Hackers Feeds
Extra Hub:
Undercode AI

Listen to this Post