[Link].
com/in/ram-kinkar-singh-498865362
SQL
INTERVIEW
QUESTIONS
𝐒𝐐𝐋 𝐢𝐧𝐭𝐞𝐫𝐯𝐢𝐞𝐰 𝐪𝐮𝐞𝐬𝐭𝐢𝐨𝐧𝐬!
1. 𝐅𝐢𝐧𝐝 𝐭𝐡𝐞 𝐬𝐞𝐜𝐨𝐧𝐝-𝐡𝐢𝐠𝐡𝐞𝐬𝐭 𝐬𝐚𝐥𝐚𝐫𝐲 𝐢𝐧 𝐚 𝐭𝐚𝐛𝐥𝐞 𝐰𝐢𝐭𝐡𝐨𝐮𝐭 𝐮𝐬𝐢𝐧𝐠 𝐋𝐈𝐌𝐈𝐓 𝐨𝐫
𝐓𝐎𝐏.
2. 𝐖𝐫𝐢𝐭𝐞 𝐚 𝐒𝐐𝐋 𝐪𝐮𝐞𝐫𝐲 𝐭𝐨 𝐟𝐢𝐧𝐝 𝐚𝐥𝐥 𝐞𝐦𝐩𝐥𝐨𝐲𝐞𝐞𝐬 𝐰𝐡𝐨 𝐞𝐚𝐫𝐧 𝐦𝐨𝐫𝐞 𝐭𝐡𝐚𝐧 𝐭𝐡𝐞𝐢𝐫
𝐦𝐚𝐧𝐚𝐠𝐞𝐫𝐬.
3. 𝐅𝐢𝐧𝐝 𝐭𝐡𝐞 𝐝𝐮𝐩𝐥𝐢𝐜𝐚𝐭𝐞 𝐫𝐨𝐰𝐬 𝐢𝐧 𝐚 𝐭𝐚𝐛𝐥𝐞 𝐰𝐢𝐭𝐡𝐨𝐮𝐭 𝐮𝐬𝐢𝐧𝐠 𝐆𝐑𝐎𝐔𝐏 𝐁𝐘.
4. 𝐖𝐫𝐢𝐭𝐞 𝐚 𝐒𝐐𝐋 𝐪𝐮𝐞𝐫𝐲 𝐭𝐨 𝐟𝐢𝐧𝐝 𝐭𝐡𝐞 𝐭𝐨𝐩 10% 𝐨𝐟 𝐞𝐚𝐫𝐧𝐞𝐫𝐬 𝐢𝐧 𝐚 𝐭𝐚𝐛𝐥𝐞.
5. 𝐅𝐢𝐧𝐝 𝐭𝐡𝐞 𝐜𝐮𝐦𝐮𝐥𝐚𝐭𝐢𝐯𝐞 𝐬𝐮𝐦 𝐨𝐟 𝐚 𝐜𝐨𝐥𝐮𝐦𝐧 𝐢𝐧 𝐚 𝐭𝐚𝐛𝐥𝐞.
6. 𝐖𝐫𝐢𝐭𝐞 𝐚 𝐒𝐐𝐋 𝐪𝐮𝐞𝐫𝐲 𝐭𝐨 𝐟𝐢𝐧𝐝 𝐚𝐥𝐥 𝐞𝐦𝐩𝐥𝐨𝐲𝐞𝐞𝐬 𝐰𝐡𝐨 𝐡𝐚𝐯𝐞 𝐧𝐞𝐯𝐞𝐫 𝐭𝐚𝐤𝐞𝐧 𝐚
𝐥𝐞𝐚𝐯𝐞.
[Link]/in/ram-kinkar-singh-498865362
7. 𝐅𝐢𝐧𝐝 𝐭𝐡𝐞 𝐝𝐢𝐟𝐟𝐞𝐫𝐞𝐧𝐜𝐞 𝐛𝐞𝐭𝐰𝐞𝐞𝐧 𝐭𝐡𝐞 𝐜𝐮𝐫𝐫𝐞𝐧𝐭 𝐫𝐨𝐰 𝐚𝐧𝐝 𝐭𝐡𝐞 𝐧𝐞𝐱𝐭 𝐫𝐨𝐰 𝐢𝐧 𝐚
𝐭𝐚𝐛𝐥𝐞.
8. 𝐖𝐫𝐢𝐭𝐞 𝐚 𝐒𝐐𝐋 𝐪𝐮𝐞𝐫𝐲 𝐭𝐨 𝐟𝐢𝐧𝐝 𝐚𝐥𝐥 𝐝𝐞𝐩𝐚𝐫𝐭𝐦𝐞𝐧𝐭𝐬 𝐰𝐢𝐭𝐡 𝐦𝐨𝐫𝐞 𝐭𝐡𝐚𝐧 𝐨𝐧𝐞
𝐞𝐦𝐩𝐥𝐨𝐲𝐞𝐞.
9. 𝐅𝐢𝐧𝐝 𝐭𝐡𝐞 𝐦𝐚𝐱𝐢𝐦𝐮𝐦 𝐯𝐚𝐥𝐮𝐞 𝐨𝐟 𝐚 𝐜𝐨𝐥𝐮𝐦𝐧 𝐟𝐨𝐫 𝐞𝐚𝐜𝐡 𝐠𝐫𝐨𝐮𝐩 𝐰𝐢𝐭𝐡𝐨𝐮𝐭
𝐮𝐬𝐢𝐧𝐠 𝐆𝐑𝐎𝐔𝐏 𝐁𝐘.
10. 𝐖𝐫𝐢𝐭𝐞 𝐚 𝐒𝐐𝐋 𝐪𝐮𝐞𝐫𝐲 𝐭𝐨 𝐟𝐢𝐧𝐝 𝐚𝐥𝐥 𝐞𝐦𝐩𝐥𝐨𝐲𝐞𝐞𝐬 𝐰𝐡𝐨 𝐡𝐚𝐯𝐞 𝐭𝐚𝐤𝐞𝐧 𝐦𝐨𝐫𝐞
𝐭𝐡𝐚𝐧 3 𝐥𝐞𝐚𝐯𝐞𝐬 𝐢𝐧 𝐚 𝐦𝐨𝐧𝐭𝐡.
11.𝐖𝐫𝐢𝐭𝐞 𝐚 𝐪𝐮𝐞𝐫𝐲 𝐭𝐨 𝐟𝐢𝐧𝐝 𝐭𝐡𝐞 𝐬𝐞𝐜𝐨𝐧𝐝 𝐡𝐢𝐠𝐡𝐞𝐬𝐭 𝐬𝐚𝐥𝐚𝐫𝐲 𝐢𝐧 𝐚𝐧 𝐞𝐦𝐩𝐥𝐨𝐲𝐞𝐞
𝐭𝐚𝐛𝐥𝐞.
12. 𝐇𝐨𝐰 𝐝𝐨 𝐲𝐨𝐮 𝐢𝐝𝐞𝐧𝐭𝐢𝐟𝐲 𝐝𝐮𝐩𝐥𝐢𝐜𝐚𝐭𝐞 𝐫𝐨𝐰𝐬 𝐢𝐧 𝐚 𝐭𝐚𝐛𝐥𝐞 𝐚𝐧𝐝 𝐝𝐞𝐥𝐞𝐭𝐞 𝐭𝐡𝐞𝐦?
13.𝐖𝐫𝐢𝐭𝐞 𝐚 𝐪𝐮𝐞𝐫𝐲 𝐭𝐨 𝐜𝐚𝐥𝐜𝐮𝐥𝐚𝐭𝐞 𝐭𝐡𝐞 𝐩𝐞𝐫𝐜𝐞𝐧𝐭𝐚𝐠𝐞 𝐨𝐟 𝐬𝐚𝐥𝐞𝐬 𝐟𝐨𝐫 𝐞𝐚𝐜𝐡
𝐩𝐫𝐨𝐝𝐮𝐜𝐭.
14. 𝐇𝐨𝐰 𝐝𝐨 𝐲𝐨𝐮 𝐫𝐞𝐭𝐫𝐢𝐞𝐯𝐞 𝐭𝐡𝐞 𝐭𝐨𝐩 𝐍 𝐫𝐞𝐜𝐨𝐫𝐝𝐬 𝐟𝐨𝐫 𝐞𝐚𝐜𝐡 𝐜𝐚𝐭𝐞𝐠𝐨𝐫𝐲 𝐢𝐧 𝐚
𝐝𝐚𝐭𝐚𝐬𝐞𝐭?
15. 𝐖𝐫𝐢𝐭𝐞 𝐚 𝐪𝐮𝐞𝐫𝐲 𝐭𝐨 𝐣𝐨𝐢𝐧 𝐭𝐰𝐨 𝐭𝐚𝐛𝐥𝐞𝐬 𝐚𝐧𝐝 𝐟𝐞𝐭𝐜𝐡 𝐫𝐞𝐜𝐨𝐫𝐝𝐬 𝐭𝐡𝐚𝐭 𝐞𝐱𝐢𝐬𝐭 𝐢𝐧
𝐨𝐧𝐞 𝐭𝐚𝐛𝐥𝐞 𝐛𝐮𝐭 𝐧𝐨𝐭 𝐭𝐡𝐞 𝐨𝐭𝐡𝐞𝐫.
16. 𝐖𝐫𝐢𝐭𝐞 𝐚 𝐒𝐐𝐋 𝐪𝐮𝐞𝐫𝐲 𝐭𝐨 𝐟𝐢𝐧𝐝 𝐭𝐡𝐞 𝐭𝐡𝐢𝐫𝐝 𝐡𝐢𝐠𝐡𝐞𝐬𝐭 𝐬𝐚𝐥𝐚𝐫𝐲 𝐟𝐫𝐨𝐦 𝐚𝐧
𝐞𝐦𝐩𝐥𝐨𝐲𝐞𝐞 𝐭𝐚𝐛𝐥𝐞 𝐰𝐢𝐭𝐡 𝐭𝐡𝐞 𝐟𝐨𝐥𝐥𝐨𝐰𝐢𝐧𝐠 𝐜𝐨𝐥𝐮𝐦𝐧𝐬: 𝐄𝐈𝐃, 𝐄𝐒𝐚𝐥𝐚𝐫𝐲.
17. 𝐂𝐫𝐞𝐚𝐭𝐞 𝐚 𝐒𝐐𝐋 𝐩𝐫𝐨𝐜𝐞𝐝𝐮𝐫𝐞 𝐮𝐬𝐢𝐧𝐠 𝐄𝐒𝐚𝐥𝐚𝐫𝐲 𝐚𝐬 𝐚 𝐩𝐚𝐫𝐚𝐦𝐞𝐭𝐞𝐫 𝐭𝐡𝐚𝐭
𝐬𝐞𝐥𝐞𝐜𝐭𝐬 𝐚𝐥𝐥 𝐄𝐈𝐃𝐬 𝐟𝐫𝐨𝐦 𝐭𝐡𝐞 𝐄𝐦𝐩𝐥𝐨𝐲𝐞𝐞 𝐭𝐚𝐛𝐥𝐞 𝐰𝐡𝐞𝐫𝐞 𝐄𝐒𝐚𝐥𝐚𝐫𝐲 𝐢𝐬 𝐥𝐞𝐬𝐬 𝐭𝐡𝐚𝐧
50,000.
18. 𝐅𝐨𝐫 𝐭𝐡𝐞 𝐄𝐦𝐩𝐥𝐨𝐲𝐞𝐞 𝐭𝐚𝐛𝐥𝐞 (𝐰𝐢𝐭𝐡 𝐜𝐨𝐥𝐮𝐦𝐧𝐬 𝐄𝐈𝐃 𝐚𝐧𝐝 𝐄𝐒𝐚𝐥𝐚𝐫𝐲),
𝐫𝐞𝐭𝐫𝐢𝐞𝐯𝐞 𝐚𝐥𝐥 𝐄𝐈𝐃𝐬 𝐰𝐢𝐭𝐡 𝐨𝐝𝐝 𝐬𝐚𝐥𝐚𝐫𝐢𝐞𝐬 𝐚𝐧𝐝 𝐣𝐨𝐢𝐧 𝐭𝐡𝐢𝐬 𝐰𝐢𝐭𝐡 𝐚𝐧𝐨𝐭𝐡𝐞𝐫 𝐭𝐚𝐛𝐥𝐞,
𝐞𝐦𝐩𝐝𝐞𝐭𝐚𝐢𝐥𝐬 (𝐰𝐢𝐭𝐡 𝐜𝐨𝐥𝐮𝐦𝐧𝐬 𝐄𝐈𝐃 𝐚𝐧𝐝 𝐄𝐃𝐎𝐁), 𝐭𝐨 𝐨𝐛𝐭𝐚𝐢𝐧 𝐄𝐃𝐎𝐁.
19. 𝐇𝐨𝐰 𝐰𝐨𝐮𝐥𝐝 𝐲𝐨𝐮 𝐮𝐬𝐞 𝐭𝐡𝐞 𝐋𝐄𝐀𝐃 𝐨𝐫 𝐋𝐀𝐆 𝐟𝐮𝐧𝐜𝐭𝐢𝐨𝐧 𝐢𝐧 𝐒𝐐𝐋 𝐭𝐨
𝐜𝐨𝐦𝐩𝐚𝐫𝐞 𝐰𝐞𝐞𝐤-𝐨𝐯𝐞𝐫-𝐰𝐞𝐞𝐤 𝐝𝐚𝐭𝐚?
20.𝐓𝐲𝐩𝐞𝐬 𝐨𝐟 𝐉𝐨𝐢𝐧𝐬 𝐢𝐧 𝐒𝐐𝐋, 𝐞𝐱𝐩𝐥𝐚𝐢𝐧 𝐞𝐚𝐜𝐡 𝐨𝐧𝐞 𝐨𝐟 𝐭𝐡𝐞𝐦 𝐰𝐢𝐭𝐡 𝐞𝐱𝐚𝐦𝐩𝐥𝐞𝐬.
[Link]/in/ram-kinkar-singh-498865362
21.𝐒𝐭𝐚𝐭𝐞 𝐝𝐢𝐟𝐟𝐞𝐫𝐞𝐧𝐜𝐞 𝐛𝐞𝐭𝐰𝐞𝐞𝐧 𝐔𝐍𝐈𝐎𝐍 𝐚𝐧𝐝 𝐔𝐍𝐈𝐎𝐍 𝐀𝐋𝐋 𝐟𝐮𝐧𝐜𝐭𝐢𝐨𝐧𝐬 𝐢𝐧 𝐒𝐐𝐋.
22.𝐖𝐡𝐚𝐭 𝐢𝐬 𝐂𝐓𝐄𝐬 𝐚𝐧𝐝 𝐬𝐭𝐚𝐭𝐞 𝐭𝐡𝐞 𝐮𝐬𝐞-𝐜𝐚𝐬𝐞 𝐨𝐟 𝐢𝐭.
23.𝐖𝐡𝐚𝐭 𝐢𝐬 𝐓𝐄𝐌𝐏𝐎𝐑𝐀𝐑𝐘 𝐓𝐀𝐁𝐋𝐄 𝐚𝐧𝐝 𝐡𝐨𝐰 𝐢𝐭 𝐢𝐬 𝐝𝐢𝐟𝐟𝐞𝐫𝐞𝐧𝐭 𝐟𝐫𝐨𝐦 𝐂𝐓𝐄𝐬?
24.𝐖𝐡𝐚𝐭'𝐬 𝐭𝐡𝐞 𝐝𝐢𝐟𝐟𝐞𝐫𝐞𝐧𝐜𝐞 𝐛𝐞𝐭𝐰𝐞𝐞𝐧 𝐃𝐄𝐋𝐄𝐓𝐄, 𝐃𝐑𝐎𝐏 𝐚𝐧𝐝 𝐓𝐑𝐔𝐍𝐂𝐀𝐓𝐄.
25. 𝐖𝐡𝐚𝐭 𝐚𝐫𝐞 𝐰𝐢𝐧𝐝𝐨𝐰 𝐟𝐮𝐧𝐜𝐭𝐢𝐨𝐧𝐬, 𝐚𝐧𝐝 𝐡𝐨𝐰 𝐝𝐨 𝐭𝐡𝐞𝐲 𝐝𝐢𝐟𝐟𝐞𝐫 𝐟𝐫𝐨𝐦
𝐚𝐠𝐠𝐫𝐞𝐠𝐚𝐭𝐞 𝐟𝐮𝐧𝐜𝐭𝐢𝐨𝐧𝐬? 𝐂𝐚𝐧 𝐲𝐨𝐮 𝐠𝐢𝐯𝐞 𝐚 𝐮𝐬𝐞 𝐜𝐚𝐬𝐞?
26. 𝐄𝐱𝐩𝐥𝐚𝐢𝐧 𝐢𝐧𝐝𝐞𝐱𝐢𝐧𝐠. 𝐖𝐡𝐞𝐧 𝐰𝐨𝐮𝐥𝐝 𝐚𝐧 𝐢𝐧𝐝𝐞𝐱 𝐩𝐨𝐭𝐞𝐧𝐭𝐢𝐚𝐥𝐥𝐲 𝐫𝐞𝐝𝐮𝐜𝐞
𝐩𝐞𝐫𝐟𝐨𝐫𝐦𝐚𝐧𝐜𝐞, 𝐚𝐧𝐝 𝐡𝐨𝐰 𝐰𝐨𝐮𝐥𝐝 𝐲𝐨𝐮 𝐚𝐩𝐩𝐫𝐨𝐚𝐜𝐡 𝐢𝐧𝐝𝐞𝐱𝐢𝐧𝐠 𝐬𝐭𝐫𝐚𝐭𝐞𝐠𝐲 𝐟𝐨𝐫
𝐚 𝐥𝐚𝐫𝐠𝐞 𝐝𝐚𝐭𝐚𝐬𝐞𝐭?
27. 𝐖𝐫𝐢𝐭𝐞 𝐚 𝐪𝐮𝐞𝐫𝐲 𝐭𝐨 𝐫𝐞𝐭𝐫𝐢𝐞𝐯𝐞 𝐜𝐮𝐬𝐭𝐨𝐦𝐞𝐫𝐬 𝐰𝐡𝐨 𝐡𝐚𝐯𝐞 𝐦𝐚𝐝𝐞 𝐩𝐮𝐫𝐜𝐡𝐚𝐬𝐞𝐬 𝐢𝐧
𝐭𝐡𝐞 𝐥𝐚𝐬𝐭 30 𝐝𝐚𝐲𝐬 𝐛𝐮𝐭 𝐝𝐢𝐝 𝐧𝐨𝐭 𝐩𝐮𝐫𝐜𝐡𝐚𝐬𝐞 𝐚𝐧𝐲𝐭𝐡𝐢𝐧𝐠 𝐢𝐧 𝐭𝐡𝐞 𝐩𝐫𝐞𝐯𝐢𝐨𝐮𝐬 30
𝐝𝐚𝐲𝐬.
28. 𝐆𝐢𝐯𝐞𝐧 𝐚 𝐭𝐚𝐛𝐥𝐞 𝐨𝐟 𝐭𝐫𝐚𝐧𝐬𝐚𝐜𝐭𝐢𝐨𝐧𝐬, 𝐟𝐢𝐧𝐝 𝐭𝐡𝐞 𝐭𝐨𝐩 3 𝐦𝐨𝐬𝐭 𝐩𝐮𝐫𝐜𝐡𝐚𝐬𝐞𝐝
𝐩𝐫𝐨𝐝𝐮𝐜𝐭𝐬 𝐟𝐨𝐫 𝐞𝐚𝐜𝐡 𝐜𝐚𝐭𝐞𝐠𝐨𝐫𝐲.
29. 𝐇𝐨𝐰 𝐰𝐨𝐮𝐥𝐝 𝐲𝐨𝐮 𝐢𝐝𝐞𝐧𝐭𝐢𝐟𝐲 𝐝𝐮𝐩𝐥𝐢𝐜𝐚𝐭𝐞 𝐫𝐞𝐜𝐨𝐫𝐝𝐬 𝐢𝐧 𝐚 𝐥𝐚𝐫𝐠𝐞 𝐝𝐚𝐭𝐚𝐬𝐞𝐭, 𝐚𝐧𝐝
𝐡𝐨𝐰 𝐰𝐨𝐮𝐥𝐝 𝐲𝐨𝐮 𝐫𝐞𝐦𝐨𝐯𝐞 𝐨𝐧𝐥𝐲 𝐭𝐡𝐞 𝐝𝐮𝐩𝐥𝐢𝐜𝐚𝐭𝐞𝐬, 𝐫𝐞𝐭𝐚𝐢𝐧𝐢𝐧𝐠 𝐭𝐡𝐞 𝐟𝐢𝐫𝐬𝐭
𝐨𝐜𝐜𝐮𝐫𝐫𝐞𝐧𝐜𝐞?
1. Find the second-highest salary in a table without using LIMIT or TOP.
[Link]/in/ram-kinkar-singh-498865362
2. Write a SQL query to find all employees who earn more than their
managers.
3. Find the duplicate rows in a table without using GROUP BY.
[Link]/in/ram-kinkar-singh-498865362
4. Write a SQL query to find the top 10% of earners in a table.
5. Find the cumulative sum of a column in a table.
[Link]/in/ram-kinkar-singh-498865362
6. Write a SQL query to find all employees who have never taken a leave.
7. Find the difference between the current row and the next row in a table.
8. Write a SQL query to find all departments with more than one employee.
[Link]/in/ram-kinkar-singh-498865362
9. Find the maximum value of a column for each group without using GROUP
BY.
10. Write a SQL query to find all employees who have taken more than 3
leaves in a month.
[Link]/in/ram-kinkar-singh-498865362
[Link] a query to find the second highest salary in an employee table.
12. How do you identify duplicate rows in a table and delete them?
[Link]/in/ram-kinkar-singh-498865362
[Link] a query to calculate the percentage of sales for each product.
14. How do you retrieve the top N records for each category in a dataset?
[Link]/in/ram-kinkar-singh-498865362
15. Write a query to join two tables and fetch records that exist in one
table but not the other.
16. Write a SQL query to find the third highest salary from an employee
table with the following columns: EID, ESalary.
When to Use?
• Use DENSE_RANK() if you want to consider duplicate salaries.
• Use ROW_NUMBER() if you want strict ranking without duplicates
affecting the position.
[Link]/in/ram-kinkar-singh-498865362
17. Create a SQL procedure using ESalary as a parameter that selects all
EIDs from the Employee table where ESalary is less than 50,000.
[Link]/in/ram-kinkar-singh-498865362
18. For the Employee table (with columns EID and ESalary), retrieve all EIDs
with odd salaries and join this with another table, empdetails (with columns
EID and EDOB), to obtain EDOB.
19. How would you use the LEAD or LAG function in SQL to compare week-
over-week data?
[Link]/in/ram-kinkar-singh-498865362
[Link] of Joins in SQL, explain each one of them with examples.
Types of Joins in SQL
Joins in SQL combine records from two or more tables based on a related column.
1 NNER JOIN
Returns only matching records from both tables.
2 LEFT JOIN (LEFT OUTER JOIN)
Returns all records from the left table and matching records from the right table. If no match, NULL
is returned.
3 RIGHT JOIN (RIGHT OUTER JOIN)
Returns all records from the right table and matching records from the left table.
4 FULL JOIN (FULL OUTER JOIN)
Returns all records when there is a match in either table.
5 CROSS JOIN
Returns Cartesian Product (each row from one table is combined with every row from another
table).
[Link] difference between UNION and UNION ALL functions in SQL.
[Link]/in/ram-kinkar-singh-498865362
[Link] is CTEs and state the use-case of it.
CTE (Common Table Expression) is a temporary result set used within a query.
Use-Case:
• Improves query readability.
• Helps recursive queries (e.g., hierarchical data).
• Used for temporary filtering inside a complex query.
[Link]/in/ram-kinkar-singh-498865362
[Link] is TEMPORARY TABLE and how it is different from CTEs?
Temporary Table: A table that exists only during a session.
[Link]'s the difference between DELETE, DROP and TRUNCATE.
[Link]/in/ram-kinkar-singh-498865362
DELETE FROM Employee WHERE EID = 5; -- Removes specific record
TRUNCATE TABLE Employee; -- Removes all data but keeps structure
DROP TABLE Employee; -- Deletes entire table including structure
25. What are window functions, and how do they differ from aggregate
functions? Can you give a use case?
26. Explain indexing. When would an index potentially reduce
performance, and how would you approach indexing strategy for a large
dataset?
[Link]/in/ram-kinkar-singh-498865362
Indexing in SQL
Indexing improves the speed of data retrieval by creating a lookup table for faster searches.
• Common types: B-Tree, Hash, Full-Text, Composite Index
When Index Reduces Performance
1. Frequent INSERT/UPDATE/DELETE operations → Overhead due to index maintenance.
2. Small tables → Full table scans may be faster.
3. Too many indexes → High storage and query optimization costs.
Indexing Strategy for Large Datasets
• Use indexes on frequently searched columns (e.g., WHERE, JOIN, ORDER BY).
• Avoid indexing columns with many duplicate values (low cardinality).
• Use Composite Index for multiple filters (WHERE col1 = X AND col2 = Y).
• Monitor with EXPLAIN ANALYZE to check index effectiveness.
Types of Indexes in SQL
Indexes in SQL improve the speed of data retrieval operations. However, they also come with storage
and performance trade-offs. Here are the main types of indexes used in SQL:
1 Primary Index
• Automatically created when defining a PRIMARY KEY in a table.
• Ensures each row is uniquely identifiable.
• Uses clustered indexing (depending on the database).
Example:
CREATE TABLE Employee (
EID INT PRIMARY KEY,
[Link]/in/ram-kinkar-singh-498865362
ESalary INT
);
• Here, EID is a primary index.
2 Unique Index
• Ensures all values in a column are unique (but allows NULL values).
• Similar to PRIMARY KEY, but a table can have multiple unique indexes.
Example:
CREATE UNIQUE INDEX idx_emp_email ON Employee (Email); •
This ensures no two employees have the same email.
3 Clustered Index
• Reorders the actual table data to match the index.
• Only one clustered index is allowed per table.
• Improves retrieval speed since related data is stored physically together.
Example:
CREATE CLUSTERED INDEX idx_emp_salary ON Employee (ESalary); •
Now, the table data is physically sorted by ESalary.
4 Non-Clustered Index
• Stores index separately from table data.
• A table can have multiple non-clustered indexes.
• Good for search queries, but slower than clustered indexes.
Example:
CREATE INDEX idx_emp_name ON Employee (EName);
• The database stores the index separately and points to actual data.
5 Composite Index (Multi-Column Index)
• An index on multiple columns (instead of just one).
• Useful for queries filtering by multiple conditions.
Example:
[Link]/in/ram-kinkar-singh-498865362
CREATE INDEX idx_emp_dept_salary ON Employee (Department, ESalary); •
This improves queries like:
SELECT * FROM Employee WHERE Department = 'IT' AND ESalary > 50000;
6 Full-Text Index
• Used for fast text searching (instead of simple LIKE queries).
• Available in databases like MySQL, PostgreSQL, and SQL Server.
Example (MySQL Full-Text Search):
CREATE FULLTEXT INDEX idx_emp_desc ON Employee (Description);
• Enables fast searching like:
SELECT * FROM Employee WHERE MATCH(Description) AGAINST ('developer');
7 Bitmap Index (Used in Data Warehousing)
• Stores indexes as bitmaps (good for low-cardinality data, like Gender: Male/Female).
• Common in OLAP databases (Oracle, PostgreSQL).
Example (Oracle):
CREATE BITMAP INDEX idx_emp_gender ON Employee (Gender);
Summary Table
Index Type Description
Primary Index Auto-created on PRIMARY KEY, unique, and mostly clustered.
Unique Index Ensures unique values in a column but allows NULL.
Clustered Index Rearranges table data to match the index. One per table.
Index Type Description
Non-Clustered Index Index stored separately, multiple allowed per table.
Composite Index Index on multiple columns, speeds up multi-condition queries.
Full-Text Index Optimized for text-based searches.
Bitmap Index Best for low-cardinality values, mainly for OLAP workloads.
[Link]/in/ram-kinkar-singh-498865362
Key Takeaways
✔ Clustered indexes are best for sorting and searching large datasets.
✔ Non-clustered indexes improve searches without affecting table storage order.
✔ Composite indexes optimize queries using multiple columns.
✔ Full-text indexes are best for searching long text fields efficiently.
Let me know if you need more details!
27. Write a query to retrieve customers who have made purchases in the
last 30 days but did not purchase anything in the previous 30 days.
[Link]/in/ram-kinkar-singh-498865362
28. Given a table of transactions, find the top 3 most purchased products
for each category.
[Link]/in/ram-kinkar-singh-498865362
29. How would you identify duplicate records in a large dataset, and how
would you remove only the duplicates, retaining the first occurrence?