1. SQL -
2. Database Modelling -
- ER Diagram
- MySQL Workbench
3. Version control of schema changes -
4. InnoDB - Storage Engine -
5. Types of locking - row level / table level
6. Triggers -
7. ACID properties
8. Isolation Levels -
9. Normalization (1NF, 2NF) -
10. ACID vs BASE
11. Foreign key constraints & different constraints - ?
12. MVCC -
13. Kafka -
14. binlog replication -
1. Physical vs logical replication -
2. active-active / active-passive
3. Master slave, reader writer
4. CDC
5. process of setting up and configuring streaming replication in PostgreSQL
6. HA setup
7. Bi directional replication
15. Connection Pooling / Proxy
16. Master slave / Reader writer configurations
17. Sharding / Partitioning
18. Autovacuum (postgres)
19. Query Optimization and Archival -
- A query is not performing, how will you debug this -
- Aurora and RDS - Parameter Groups
- innodb_buffer_pool_size
- Sql query to find the name of the student, getting 2nd highest marks in his class - ?
- Stored procedures / Functions
- Partitioning and Sharding -
- https://use-the-index-luke.com/3-minute-test/mysql -
20. Indexing - Clustered vs non clustered - Primary, Unique, Composite
- Candidate key - ?
- Hash Index -
- B+ Tree -
- Index Used or not -
- Cost of indexing -
21. Joins -
1. Different types of joins -
2. Inner join vs outer join -
3. Difference between Union and Union ALL
4. Cross Join -
22. Administration -
1. Privileges / Roles - RBAC
2. Configurations - max_connections, buffer pools, buffer_pool_chunk_size?
3. pgAdmin vs MySQLWorkbench
23. Postgres
1. Explain the difference between tablespaces and schemas in PostgreSQL. When would you use each, and what are the implications?
2. What is the purpose of the pg_hba.conf file in PostgreSQL, and how would you configure it to enhance security?
3. Can you explain the role of the autovacuum process in PostgreSQL? How does it impact database performance, and how can it be tuned?
4. Describe your experience with PostgreSQL backup and recovery strategies. How do you handle PITR (Point-in-Time Recovery) in PostgreSQL?
5. What are foreign data wrappers in PostgreSQL, and how can they be used for data integration with external systems?
6. Explain the concept of roles and privileges in PostgreSQL. How do you manage access control for users and applications?
24. Mongodb
1. Opslog -
2. replicaset performance metrics -
3. backup and restore -
4. difference between sharding and replicaset -
5. Optimize a MongoDB query performance -
6. Mongodb TTL index -
7. Mongodb change streams -
8. Mongodb write concern -
9. Mongodb pipeline -
3-Minute Test: How Much do You Know About MySQL Indexing?
Just 5 questions to see how much you know about MySQL performance. Never get these things wrong again.