Listen to this Post
Want to crack your SQL interview? Here are the most commonly asked SQL questions with answers! 👇
1️⃣ What is the difference between DELETE, TRUNCATE, and DROP?
✅ DELETE – Removes specific rows but keeps the table structure (Rollback possible)
DELETE FROM employees WHERE age < 25;
✅ TRUNCATE – Removes all rows but keeps the table structure (Faster, No Rollback)
TRUNCATE TABLE employees;
✅ DROP – Deletes the entire table including structure (Cannot be undone)
DROP TABLE employees;
2️⃣ What is the difference between HAVING and WHERE?
✅ WHERE filters data before aggregation.
✅ HAVING filters data after aggregation.
SELECT department, COUNT(<em>) FROM employees WHERE age > 25 -- Filters before COUNT() GROUP BY department HAVING COUNT(</em>) > 5; -- Filters after COUNT()
3️⃣ What is an Index in SQL?
✅ Indexes speed up data retrieval by creating a lookup table.
✅ Think of it as a table of contents for faster searching.
CREATE INDEX idx_salary ON employees(salary);
4️⃣ What is Normalization in SQL?
✅ Normalization organizes data to reduce redundancy & improve efficiency.
✅ 1NF, 2NF, 3NF, BCNF are common forms.
6️⃣ How to find the second highest salary in SQL?
SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);
7️⃣ What is a Stored Procedure in SQL?
✅ A Stored Procedure is a saved query that can be executed later.
8️⃣ What is the difference between UNION and UNION ALL?
✅ UNION removes duplicates.
✅ UNION ALL keeps duplicates.
9️⃣ What is a Primary Key vs Foreign Key?
✅ Primary Key – Uniquely identifies a record in a table.
✅ Foreign Key – References a primary key from another table.
🔟 How does SQL Handle NULL Values?
✅ Checking for NULL using IS NULL
SELECT * FROM employees WHERE salary IS NULL;
✅ Replacing NULL values using COALESCE
SELECT name, COALESCE(salary, 0) AS salary FROM employees;
You Should Know:
Practical SQL Commands for Database Management:
1. Backup a Database:
mysqldump -u username -p database_name > backup.sql
2. Restore a Database:
mysql -u username -p database_name < backup.sql
3. Check Running Queries:
SHOW PROCESSLIST;
4. Kill a Process:
KILL process_id;
5. Create User and Grant Permissions:
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON database_name.* TO 'newuser'@'localhost'; FLUSH PRIVILEGES;
6. Find Table Size:
SELECT table_name AS "Table", ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)" FROM information_schema.TABLES WHERE table_schema = "database_name" ORDER BY (data_length + index_length) DESC;
7. Optimize a Table:
OPTIMIZE TABLE table_name;
8. Check Database Version:
SELECT VERSION();
9. Find Duplicate Records:
SELECT column_name, COUNT(<em>) FROM table_name GROUP BY column_name HAVING COUNT(</em>) > 1;
10. Export Query Results to CSV:
SELECT * INTO OUTFILE '/tmp/results.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM table_name;
Linux Commands for Database Administrators:
1. Monitor MySQL Processes:
watch -n 1 "mysqladmin -u root -p processlist"
2. Check Disk Space for Databases:
df -h /var/lib/mysql
3. Check MySQL Error Logs:
tail -f /var/log/mysql/error.log
4. Benchmark MySQL Server:
mysqlslap -u root -p --concurrency=50 --iterations=10 --auto-generate-sql
5. Secure MySQL Installation:
mysql_secure_installation
Windows SQL Server Commands:
1. Start SQL Server Service:
[cmd]
net start MSSQLSERVER
[/cmd]
2. Stop SQL Server Service:
[cmd]
net stop MSSQLSERVER
[/cmd]
3. Import Data with bcp:
[cmd]
bcp database_name.schema_name.table_name in C:\data.csv -c -t, -S server_name -U username -P password
[/cmd]
4. Export Data with bcp:
[cmd]
bcp database_name.schema_name.table_name out C:\data.csv -c -t, -S server_name -U username -P password
[/cmd]
What Undercode Say:
Mastering SQL is essential for any data professional, and understanding these fundamental concepts will give you a strong foundation. The practical commands provided will help you manage databases more effectively in real-world scenarios. Remember that database optimization is an ongoing process – regularly monitor performance, maintain indexes, and clean up unused data. For production environments, always test your queries and backup your data before making structural changes. The combination of theoretical knowledge and practical skills demonstrated here will make you a valuable asset in any data-driven organization.
Expected Output:
DELETE FROM employees WHERE age < 25; TRUNCATE TABLE employees; DROP TABLE employees; SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); CREATE INDEX idx_salary ON employees(salary); SELECT * FROM employees WHERE salary IS NULL; SELECT name, COALESCE(salary, 0) AS salary FROM employees;
References:
Reported By: Surajdubey Codes – Hackers Feeds
Extra Hub: Undercode MoN
Basic Verification: Pass ✅
