Interview Q&A

Short answer: SQL Server: To back up a database in SQL Server, you can use the BACKUP DATABASE command.

Explain a bit more

- Full Backup BACKUP DATABASE MyDatabase TO DISK = 'C:\Backups\MyDatabase.bak'; - Transaction Log Backup BACKUP LOG MyDatabase TO DISK = 'C:\Backups\MyDatabase_log.trn'; PostgreSQL: In PostgreSQL, you can use the pg_dump command for backing up the database. - Full Backup pg_dump mydatabase > /path/to/backup/mydatabase.sql For backing up with compression or other options: pg_dump -Fc mydatabase > /path/to/backup/mydatabase.dump For transactional backups, you can use WAL (Write-Ahead Logging) archiving, typically managed through archive_mode and archive_command settings in the postgresql.conf. MySQL: In MySQL, you can use the mysqldump command for backing up the database. - Full Backup mysqldump -u username -p mydatabase > /path/to/backup/mydatabase.sql For binary logging (transaction logs), MySQL uses binlog: - Enabling binary log [mysqld] log-bin=mysql-bin

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: SQL Server: To restore a full backup in SQL Server: - Full Restore RESTORE DATABASE MyDatabase FROM DISK = 'C:\Backups\MyDatabase.bak'; - Transaction Log Restore RESTORE LOG MyDatabase FROM DISK = 'C:\Backups\MyDatabase_log.trn'; To restore a database to a specific point in time (using point-in-time recovery), you would restore the full backup and apply transaction logs.

Explain a bit more

RESTORE DATABASE MyDatabase FROM DISK = 'C:\Backups\MyDatabase.bak'; RESTORE LOG MyDatabase FROM DISK = 'C:\Backups\MyDatabase_log.trn' WITH STOPAT = '2023-09-14T15:30:00'; -- Restore to a specific time PostgreSQL: To restore a backup from a pg_dump: - Full Restore from SQL dump psql -U username -d mydatabase < /path/to/backup/mydatabase.sql For binary dump (pg_dump -Fc): pg_restore -U username -d mydatabase /path/to/backup/mydatabase.dump MySQL: To restore a MySQL database: - Full Restore from SQL dump mysql -u username -p mydatabase < /path/to/backup/mydatabase.sql For restoring binary logs: mysqlbinlog /path/to/binlog/mysql-bin.000001 | mysql -u username -p

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Automating backups ensures that backups are performed regularly and without manual intervention. You can use the following methods:

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Database replication is the process of copying and maintaining database objects in multiple databases to ensure high availability and fault tolerance. Types of Replication:

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Sharding and partitioning are techniques used to distribute data across multiple servers or tables to improve performance and scalability.

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Monitoring database performance is crucial for identifying bottlenecks and ensuring optimal functioning. The following methods are commonly used:

Real-world example (ShopNest)

ShopNest adds an index on Orders(CustomerId, CreatedAt) because “my recent orders” is queried constantly.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: A connection pool is a cache of database connections that are maintained so that connections can be reused when future requests to the database are made.

Explain a bit more

Instead of opening and closing a new connection each time a query is executed, the application can reuse existing connections from the pool. How it helps with scaling: Improves efficiency: Reusing connections reduces the overhead of establishing new connections, leading to faster query execution. Reduces resource consumption: By limiting the number of connections to the database, the pool avoids overloading the database with too many open connections. Improves scalability: Connection pools help handle a large number of requests without overwhelming the database. The pool can be scaled by adjusting the maximum number of connections based on the workload. Example: A connection pool size can be set to 100, meaning up to 100 connections to the database can be active at once, with others waiting in line for an available connection.

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Multi-tenancy refers to a single instance of a database serving multiple tenants (clients or organizations) while keeping their data isolated. Approaches:

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: And ensuring consistency across different environments (development, staging, production). pproaches:

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Managing and versioning database schema changes is essential in keeping track of updates and ensuring consistency across different environments (development, staging, production). Approaches:

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Data migration involves transferring data between systems or platforms, often when changing database engines or moving to the cloud. Steps:

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: The ACID properties ensure reliable processing of database transactions:

Real-world example (ShopNest)

Checkout wraps stock decrement + order insert in a transaction so you never sell stock you do not have.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.