11. How do you remove duplicate records? ๐๏ธ
Use DISTINCT or ROW_NUMBER() with a CTE to delete duplicates.
SELECT DISTINCT * FROM table_name;
Or:sql
WITH Ranked AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY id) AS rn
FROM table_name
)
DELETE FROM Ranked WHERE rn > 1;
12. What is normalization? Explain its types. ๐งฑ
Normalization reduces redundancy and improves data integrity.
- 1NF: Atomic columns (no repeating groups)
- 2NF: 1NF + no partial dependency
- 3NF: 2NF + no transitive dependency
- BCNF: Advanced version of 3NF
13. What is denormalization?
The process of combining tables to improve read speed by introducing redundancy. Used for reporting and faster queries. โก
14. What is a stored procedure?
A saved set of SQL statements that can be reused. ๐พ
CREATE PROCEDURE GetUsers AS
BEGIN
SELECT * FROM users;
END;
15. What are indexes and why are they used?
Indexes speed up query performance by allowing quick data lookup. Useful on columns used in WHERE or JOIN clauses. ๐๏ธ
16. What is the difference between clustered and non-clustered index?
- Clustered: Sorts actual table data. Only one per table. (Physical Order)
- Non-clustered: Separate structure that references data. Can have many. (Logical Order)
17. What is a transaction?
A group of operations treated as a single unit. It follows ACID principles to maintain data integrity.
18. ACID properties in SQL
- Atomicity: All or none of the operations run (All-or-Nothing)
- Consistency: Data stays valid before/after transaction โ๏ธ
- Isolation: Transactions donโt interfere ๐ง
- Durability: Changes remain after success โ
19. Difference between DELETE, TRUNCATE, and DROP
- DELETE: Removes rows, can be rolled back (logged). โช
- TRUNCATE: Removes all rows, faster, less logging. ๐๏ธ
- DROP: Deletes table structure and data entirely. ๐ฅ
20. What is a NULL value in SQL?
NULL represents missing or unknown data. It's different from 0 or an empty string. (Unknown, not Zero.)
๐ฌ Double Tap โค๏ธ For Part-3