SQL CYBER VAULT

50 Elite Questions • 5 Power Differences • Pure CSS

1. Primary Key vs Unique Key

#	Primary Key	Unique Key
1	Only one per table	Multiple allowed
2	No NULL	One NULL
3	Clustered Index	Non-Clustered
4	Auto-increment	No auto-increment
5	Used in FK	Not in FK

            CREATE TABLE Users (

              ID INT PRIMARY KEY,

              Email VARCHAR(100) UNIQUE

            );

2. DELETE vs TRUNCATE

#	DELETE	TRUNCATE
1	DML	DDL
2	WHERE allowed	No WHERE
3	Triggers fire	No triggers
4	Rollback	No rollback
5	Slower	Faster

3. INNER JOIN vs LEFT JOIN

#	INNER JOIN	LEFT JOIN
1	Matching rows only	All from left
2	No NULLs	NULLs for unmatched
3	Smaller result	Larger result
4	Faster	Slower
5	Intersection	Inclusion

4. WHERE vs HAVING

#	WHERE	HAVING
1	Before GROUP BY	After GROUP BY
2	No aggregates	Uses aggregates
3	Row filter	Group filter
4	Faster	Slower
5	Can use index	Cannot use index

5. Clustered vs Non-Clustered Index

#	Clustered	Non-Clustered
1	Sorts data physically	Separate structure
2	Only one per table	Multiple allowed
3	Fast range queries	Fast exact match
4	Data + Index	Only pointers
5	PK by default	Explicitly created

6. UNION vs UNION ALL

#	UNION	UNION ALL
1	Removes duplicates	Includes duplicates
2	Slower	Faster
3	Sorts data	No sorting
4	Less memory	More memory
5	Distinct result	All rows

7. Subquery vs JOIN

#	Subquery	JOIN
1	Nested query	Horizontal merge
2	Slower	Faster
3	Less readable	More readable
4	One-time use	Reusable
5	IN, EXISTS	ON clause

8. View vs Table

#	View	Table
1	Virtual	Physical
2	No data	Stores data
3	Dynamic	Static
4	Security	Storage
5	Can be indexed	Always indexed

9. RANK vs DENSE_RANK

#	RANK	DENSE_RANK
1	1,2,2,4	1,2,2,3
2	Gaps	No gaps
3	Skips numbers	Sequential
4	For podium	For ranking
5	Less dense	Dense

10. Schema vs Database

#	Schema	Database
1	Logical container	Physical storage
2	Multiple per DB	One per instance
3	Namespace	File system
4	Contains objects	Contains schemas
5	Can be dropped	Contains data files

11. GRANT vs REVOKE

#	GRANT	REVOKE
1	Gives permission	Removes permission
2	SELECT, INSERT	TAKE BACK
3	Adds rights	Reduces rights
4	DCL	DCL
5	Security	Security

12. DDL vs DML

#	DDL	DML
1	Structure	Data
2	CREATE, ALTER	INSERT, UPDATE
3	Auto-commit	Rollback
4	No WHERE	WHERE allowed
5	Schema change	Data change

13. Foreign Key vs Primary Key

#	Foreign Key	Primary Key
1	References PK	Unique identifier
2	Can be NULL	No NULL
3	Multiple per table	One per table
4	Child table	Parent table
5	Enforces RI	Enforces uniqueness

14. Normalization vs Denormalization

#	Normalization	Denormalization
1	Reduces redundancy	Increases redundancy
2	More tables	Fewer tables
3	Slower reads	Faster reads
4	OLTP	OLAP
5	Data integrity	Performance

15. Stored Procedure vs Function

#	Stored Procedure	Function
1	Can return multiple	Returns one value
2	CALL	SELECT
3	Can modify data	Read-only
4	Can use TRY-CATCH	Cannot
5	Business logic	Calculations

16. Trigger vs Constraint

#	Trigger	Constraint
1	Custom logic	Built-in rules
2	Complex actions	Simple rules
3	Slower	Faster
4	Can rollback	Auto-enforced
5	AFTER, BEFORE	CHECK, FK

17. ACID vs BASE

#	ACID	BASE
1	Consistency	Availability
2	RDBMS	NoSQL
3	Strong	Eventual
4	Transactions	Scalability
5	Banking	Social media

18. COMMIT vs ROLLBACK

#	COMMIT	ROLLBACK
1	Saves changes	Undoes changes
2	Permanent	Temporary
3	Ends transaction	Ends transaction
4	Cannot undo	Can redo
5	Success	Failure

19. ROW_NUMBER vs NTILE

#	ROW_NUMBER	NTILE
1	Unique number	Bucket number
2	1,2,3,4	1,1,2,2
3	Sequential	Groups
4	Pagination	Quartiles
5	Single partition	Multiple buckets

20. Temporary Table vs CTE

#	Temporary Table	CTE
1	Physical storage	Virtual
2	Persists session	Single query
3	Indexable	Not indexable
4	Reusable	Not reusable
5	Performance	Readability

50. OLTP vs OLAP

#	OLTP	OLAP
1	Transactions	Analytics
2	Normalized	Denormalized
3	Real-time	Historical
4	Small queries	Large queries
5	ACID	Read-heavy