Listen to this Post
SQL (Structured Query Language) is essential for database management, data analysis, and backend development. Below are key SQL interview questions along with practical examples, commands, and optimization techniques.
- What is SQL, and what are its different types?
SQL is a domain-specific language for managing relational databases. Types include:
– DDL (Data Definition Language): CREATE, ALTER, `DROP`
– DML (Data Manipulation Language): SELECT, INSERT, UPDATE, `DELETE`
– DCL (Data Control Language): GRANT, `REVOKE`
– TCL (Transaction Control Language): COMMIT, `ROLLBACK`
Example:
CREATE TABLE Employees (ID INT PRIMARY KEY, Name VARCHAR(50));
2. Difference between DELETE, TRUNCATE, and DROP
- DELETE: Removes rows (can be rolled back).
DELETE FROM Employees WHERE ID = 1;
- TRUNCATE: Removes all rows (faster, no rollback).
TRUNCATE TABLE Employees;
- DROP: Deletes the table structure.
DROP TABLE Employees;
3. Joins in SQL
- INNER JOIN: Returns matching rows.
SELECT A.Name, B.Department FROM Employees A INNER JOIN Dept B ON A.ID = B.EmpID;
- LEFT JOIN: All rows from the left table.
- RIGHT JOIN: All rows from the right table.
4. Normalization vs. Denormalization
- Normalization reduces redundancy (1NF, 2NF, 3NF).
- Denormalization improves read performance by adding redundancy.
5. Indexes and Optimization
Create an index:
CREATE INDEX idx_name ON Employees(Name);
Optimize queries: Use `EXPLAIN` to analyze query performance.
EXPLAIN SELECT FROM Employees WHERE Name = 'John';
You Should Know:
ACID Properties in SQL
- Atomicity: Transactions are all-or-nothing.
- Consistency: Ensures valid data transitions.
- Isolation: Concurrent transactions don’t interfere.
- Durability: Committed changes persist.
Stored Procedures vs. Functions
- Stored Procedure:
CREATE PROCEDURE GetEmployee(IN empID INT) BEGIN SELECT FROM Employees WHERE ID = empID; END;
- Function: Returns a value.
CREATE FUNCTION GetSalary(empID INT) RETURNS INT BEGIN RETURN (SELECT Salary FROM Employees WHERE ID = empID); END;
Linux/Windows Commands for DB Admins
- Backup MySQL (Linux):
mysqldump -u user -p database > backup.sql
- Windows SQL Server Backup:
BACKUP DATABASE MyDB TO DISK = 'C:\backup.bak';
What Undercode Say:
Mastering SQL involves understanding theory (joins, normalization) and practical optimization (EXPLAIN, indexing). For DevOps integration, automate backups and monitor slow queries. Practice CTEs, window functions (RANK(), DENSE_RANK()), and transaction management (BEGIN TRANSACTION, COMMIT).
Expected Output:
-- Example: Find the 2nd highest salary SELECT MAX(Salary) FROM Employees WHERE Salary < (SELECT MAX(Salary) FROM Employees);
No URLs were extracted as the original post did not contain relevant links.
References:
Reported By: Vinoth P – Hackers Feeds
Extra Hub: Undercode MoN
Basic Verification: Pass ✅
