Listen to this Post
1️⃣ How does indexing work in SQL, and what are the differences between clustered and non-clustered indexes?
2️⃣ What is a Common Table Expression (CTE), and how does it differ from a temporary table?
3️⃣ How would you optimize a slow SQL query? List some techniques.
4️⃣ Explain window functions in SQL. How do they differ from GROUP BY?
5️⃣ What is the difference between DELETE, TRUNCATE, and DROP?
6️⃣ How would you find the second highest salary in an employee table?
7️⃣ What is the difference between a correlated subquery and a non-correlated subquery?
8️⃣ How does SQL handle deadlocks, and how can you prevent them?
9️⃣ Explain the difference between ACID properties and BASE properties in databases.
🔟 What is database normalization? Explain the different normal forms (1NF, 2NF, 3NF, BCNF).
You Should Know:
1. Indexing in SQL
- Clustered Index: Determines the physical order of data in a table. Only one clustered index per table.
CREATE CLUSTERED INDEX idx_employee_id ON Employees(EmployeeID);
- Non-Clustered Index: Creates a separate structure to store index data. Multiple non-clustered indexes allowed.
CREATE NONCLUSTERED INDEX idx_employee_name ON Employees(EmployeeName);
2. Common Table Expression (CTE)
- CTEs are temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.
WITH CTE_Employee AS ( SELECT EmployeeID, EmployeeName FROM Employees WHERE Department = 'IT' ) SELECT * FROM CTE_Employee;
3. Optimizing Slow SQL Queries
- Use `EXPLAIN` to analyze query execution plans.
EXPLAIN SELECT * FROM Employees WHERE Salary > 50000;
- Index columns used in WHERE, JOIN, and ORDER BY clauses.
- Avoid
SELECT *; specify only required columns.
4. Window Functions
- Perform calculations across a set of table rows related to the current row.
SELECT EmployeeID, Salary, RANK() OVER (ORDER BY Salary DESC) AS SalaryRank FROM Employees;
5. DELETE, TRUNCATE, and DROP
- DELETE: Removes rows one by one, can be rolled back.
DELETE FROM Employees WHERE EmployeeID = 101;
- TRUNCATE: Removes all rows at once, faster, cannot be rolled back.
TRUNCATE TABLE Employees;
- DROP: Deletes the entire table structure.
DROP TABLE Employees;
6. Second Highest Salary
- Use a subquery with `LIMIT` and
OFFSET.SELECT Salary FROM Employees ORDER BY Salary DESC LIMIT 1 OFFSET 1;
7. Correlated vs Non-Correlated Subqueries
- Correlated: Inner query depends on the outer query.
SELECT EmployeeName FROM Employees e1 WHERE Salary > (SELECT AVG(Salary) FROM Employees e2 WHERE e1.Department = e2.Department);
- Non-Correlated: Independent of the outer query.
SELECT EmployeeName FROM Employees WHERE Salary > (SELECT AVG(Salary) FROM Employees);
8. Deadlocks
- Use `SET DEADLOCK_PRIORITY` to manage deadlock handling.
SET DEADLOCK_PRIORITY LOW;
9. ACID vs BASE
- ACID: Atomicity, Consistency, Isolation, Durability (Traditional RDBMS).
- BASE: Basically Available, Soft state, Eventual consistency (NoSQL).
10. Database Normalization
- 1NF: Eliminate duplicate columns.
- 2NF: Remove partial dependencies.
- 3NF: Remove transitive dependencies.
- BCNF: Every determinant is a candidate key.
What Undercode Say:
SQL is a powerful tool for managing and querying relational databases. Mastering advanced concepts like indexing, CTEs, window functions, and query optimization is essential for database professionals. Practice the provided commands and techniques to enhance your SQL skills and prepare for technical interviews. For further reading, refer to SQL Documentation.
Linux/Windows Commands for Database Management:
- Linux: Use `mysql` command-line tool to interact with MySQL databases.
mysql -u username -p
- Windows: Use `sqlcmd` for SQL Server.
sqlcmd -S servername -U username -P password
- Backup a database in Linux:
mysqldump -u username -p database_name > backup.sql
- Restore a database in Windows:
sqlcmd -S servername -U username -P password -i backup.sql
References:
Reported By: Sumit Yadav – Hackers Feeds
Extra Hub: Undercode MoN
Basic Verification: Pass ✅
