Interview Q&A

• Use EXPLAIN or QUERY PLAN to analyze query execution times and

identify slow queries.

• Track metrics like response time, execution time, and query throughput.

GROUP BY clauses are good candidates for indexing.

• Create indexes on columns that are used in WHERE clauses, JOIN

conditions, and ORDER BY clauses.

• Use covering indexes to avoid full table scans and improve performance.

• Sharding involves splitting data into smaller, more manageable pieces called

shards. Each shard is stored on a different server (or cluster) to distribute the

load.

• Typically, horizontal partitioning is used in sharding, where rows (rather

than columns) of a database table are distributed across multiple databases.

• Example: A user table could be sharded by user ID, where all users with IDs

1-1000 are stored in one database, 1001-2000 in another, and so on.

Inventory, Reviews.

• A common pattern where one record in a table can be associated with

multiple records in another table.

• Example: A Customer can have many Orders, but each Order can belong

to only one Customer.

redundancy by eliminating unnecessary duplication.

• Vertical scaling means upgrading the resources (CPU, RAM, disk space) of a

single database server.

• Advantages: Simple to implement since you only need to upgrade the

existing server.

• Disadvantages: There is a physical limit to how much you can scale up. It

also increases the risk of downtime during upgrades.

• All tenants share the same database and tables. A tenant identifier (e.g.,

tenant_id) is used to segregate data.

• Pros: Easier to maintain and scale.
• Cons: Can lead to security and data isolation issues.

• Use tools like Liquibase, Flyway, or Alembic to manage and version

schema changes. These tools allow you to write migrations in SQL or as

scripts that can be version-controlled.

• These tools can apply, track, and rollback schema changes.

• One database (master) handles all writes, while one or more secondary

databases (slaves) replicate the data for read queries and redundancy.

• Advantages: Load balancing for read-heavy applications, fault tolerance.
• Disadvantages: Writes are still a single point of failure.

record has a unique identifier.

• Understand the source and target databases, and map the data models.
• Identify data transformations, such as type conversions or renaming columns.

• SQL Server Agent: Use SQL Server Agent to schedule backup jobs.
• Example: Create a backup job in SQL Server Management Studio (SSMS) to

run daily, weekly, or at specific intervals.

Script for automated backups:

BACKUP DATABASE MyDatabase TO DISK = 'C:\Backups\MyDatabase.bak';

manager, user).

Review the concept and prepare a concise verbal explanation with a real project example.

• SQL Databases (Relational Databases): These are structured databases that use

Structured Query Language (SQL) for defining and manipulating data. They store

data in tables with rows and columns. SQL databases enforce a strict schema,

meaning the structure of the data must be defined beforehand.

Examples: MySQL, PostgreSQL, SQL Server, Oracle

• NoSQL Databases (Non-relational Databases): These databases are more flexible

and allow storage of unstructured or semi-structured data. They don't require a fixed

schema and are often used for large-scale applications where flexibility, scalability,

and speed are more important than the strict relational model.

Examples: MongoDB, Cassandra, CouchDB, Firebase

pages, slowing down query performance.

data.

• Use tools like gpg, or built-in encryption features (e.g., SQL Server Backup

Encryption, PostgreSQL using pg_dump with -Fc and encrypting the output

file).

none are.

Review the concept and prepare a concise verbal explanation with a real project example.

• Use case: When you only want matching records.

table scan.

• A full backup includes all the data in the database at the time the backup is

taken. It is a complete snapshot of the database.

• Advantages: Easy to restore; ensures a full copy of the database.
• Disadvantages: Larger in size and takes more time compared to other

backup types.

• Example: This backup is typically taken on a regular schedule, like daily or

weekly.

Review the concept and prepare a concise verbal explanation with a real project example.

Review the concept and prepare a concise verbal explanation with a real project example.

• Two or more databases act as both masters and slaves, allowing for both

read and write operations on any node.

• Advantages: High availability and load balancing for both reads and writes.
• Disadvantages: Potential for data conflicts when writes happen

simultaneously on multiple nodes.

• A Customer can have multiple Orders.
• An Order can contain multiple Products (many-to-many relationship).
• A Product can belong to multiple Categories.
• Payments are linked to Orders.

• A differential backup includes all the changes made since the last full backup.

It does not depend on other differential backups.

• Advantages: Smaller than a full backup, and quicker to take compared to full

backups.

• Disadvantages: Can become larger over time as more changes accumulate

between full backups.

• Example: If the last full backup was taken on Monday, a differential backup

taken on Wednesday would include all changes since Monday.

access-controlled locations, ideally offsite or in the cloud (e.g., AWS S3, Azure Blob

Storage).

• Use encrypted cloud storage options.

Review the concept and prepare a concise verbal explanation with a real project example.

• Use cron jobs to automate pg_dump commands.

Example (Linux):

0 3 * * * pg_dump -U postgres mydatabase >

/path/to/backup/mydatabase_$(date +\%Y\%m\%d).sql

• Use pgBackRest or Barman for more advanced backup automation.

Review the concept and prepare a concise verbal explanation with a real project example.

SELECT, INSERT, UPDATE, DELETE).

• Partition large tables into smaller, more manageable subsets, which can

make queries faster.

• Example: Split a sales table by year to avoid scanning years of data in a

single query.

BY are faster.

use foreign keys to link them.

• Structured Data: SQL is best suited for structured data where relationships between

data are important.

• ACID Compliance: SQL databases generally support ACID (Atomicity, Consistency,

Isolation, Durability) properties, making them ideal for applications where data

integrity is crucial, such as banking systems.

• Complex Queries: SQL databases provide powerful query capabilities with joins,

subqueries, and aggregations.

• Maturity and Ecosystem: SQL databases have been around for decades, offering a

robust set of features, tools, and community support.

usage.

integrity.

query.

matched rows from the right table. If there’s no match, NULL is returned for the right

table.

• Use case: When you want all records from the left table.

Review the concept and prepare a concise verbal explanation with a real project example.

Review the concept and prepare a concise verbal explanation with a real project example.

WHERE, JOIN, ORDER BY, and GROUP BY clauses to choose the most appropriate

index.

• Extract the data from the source database using export tools or direct queries.

• Store migration scripts in version control (e.g., Git) and ensure that each

change is applied in a sequential, ordered fashion.

on the primary key. Remove partial dependencies (when a non-key attribute depends

on part of a composite key).

• Each tenant has its schema in the same database. The schema contains the

same tables but is isolated per tenant.

• Pros: Better data isolation compared to the first approach.
• Cons: More complex to manage schema changes across tenants.

• A pattern used when two entities are related in a way where both can have

multiple associations with each other.

• Example: A Student can be enrolled in many Courses, and a Course can

have many Students. This would typically be modeled using a junction table

like Student_Course.

frequently.

• Monitor CPU, memory usage, disk I/O, and network latency using tools like

Prometheus, Grafana, or cloud-specific monitoring tools like AWS

CloudWatch or Azure Monitor.

the database can use the index to quickly find matching rows.

• Customers: Contains customer details (name, address, etc.).
• Orders: Contains order details and references customers.
• Order_Items: A join table between Orders and Products.
• Products: Product details (name, description, price, etc.).
• Inventory: Tracks stock levels.

I/O, CPU, etc.) and chooses the one with the lowest cost.

on another non-key attribute). Ensure that no non-key attribute is dependent on

another non-key attribute.

For example:

• Original Table:

Employee_ID Employee_Nam

Department Department_Manager

1 Alice HR John

2 Bob IT Sarah

• 1NF: Remove repeating groups and ensure atomic values.
• 2NF: Remove partial dependencies. In this case, split into separate tables for

employees and departments.

• 3NF: Remove transitive dependencies. The Department_Manager is a dependency

on Department, not directly on Employee, so it should be moved to a separate table

for Departments.

Review the concept and prepare a concise verbal explanation with a real project example.

likely to improve performance when indexed.

same type of data (e.g., storing multiple phone numbers in separate columns).

to retrieve data.

To resolve this, indexes can be reorganized or rebuilt to improve their performance.

matched rows from the left table. If there’s no match, NULL is returned for the left

table.

• Use case: When you want all records from the right table.

• Set up continuous integration (CI) pipelines to test schema migrations on

different environments to ensure smooth deployment.

• Each tenant gets its own database instance.
• Pros: Complete isolation, better security, and individual scaling.
• Cons: Higher overhead for database management and maintenance.

Example: SaaS applications often use multi-tenancy to serve multiple clients with the same

infrastructure while keeping data separated.

• Use ETL (Extract, Transform, Load) tools to clean, transform, and shape the

data as required.

• A flexible schema design used for scenarios where attributes of entities are

dynamic and can vary over time.

• Example: A Product might have a variety of attributes like size, color, and

weight, but some products may not have all attributes, so you store them as

key-value pairs.

• Use pagination techniques (LIMIT, OFFSET, or ROWNUM) to retrieve data in

chunks rather than all at once.

• Avoid fetching unnecessary columns or rows.

Review the concept and prepare a concise verbal explanation with a real project example.

• Track buffer cache hit ratio, transaction log size, locks and deadlocks,

and cache usage to monitor the internal database performance.

Trade-offs:

• CA (Consistency and Availability): Systems that prioritize consistency and

availability will fail during network partitions.

• CP (Consistency and Partition Tolerance): Systems that prioritize consistency and

partition tolerance may not be available during network issues.

• AP (Availability and Partition Tolerance): Systems that prioritize availability and

partition tolerance may return stale data.

• Horizontal scaling involves adding more database servers to distribute the

load, typically using replication, sharding, or clustering.

• Advantages: It can handle much larger datasets and traffic since the

workload is distributed across multiple servers.

• Disadvantages: More complex to implement and maintain, requires

specialized techniques like load balancing and data partitioning.

• Data is written to both the primary and secondary database simultaneously.
• Advantages: Ensures data consistency between all replicas.
• Disadvantages: May incur performance overhead due to the need to wait for

replication.

visible to other transactions.

• A transaction log backup records all the changes made to the database since

the last transaction log backup. It allows point-in-time recovery.

• Advantages: Enables recovery of the database to any specific point in time

(assuming all previous logs are available).

• Disadvantages: Requires continuous backups of transaction logs to maintain

full recovery.

• Example: After a transaction log backup, you can restore to a specific

moment in time by replaying the logs up to that point.

ACID stands for:

• Atomicity: Ensures that all operations in a transaction are completed successfully, or

none are. If one part of a transaction fails, the whole transaction fails.

• Consistency: Guarantees that the database is always in a valid state, adhering to all

rules, including constraints, after a transaction.

• Isolation: Ensures that transactions are executed in isolation from each other, so

one transaction does not interfere with another.

• Durability: Ensures that once a transaction is committed, it is permanent, even in the

case of system failures.

• PostgreSQL: PostgreSQL doesn’t have a built-in debugger, but you can use RAISE

NOTICE for debugging or use third-party tools like pgAdmin for debugging.

• MySQL: MySQL Workbench provides a simple debugging interface that allows

stepping through the stored procedure and monitoring execution.

Example (PostgreSQL):

RAISE NOTICE 'Debugging variable: %', my_variable;

files.

• Use access controls, roles, and policies to restrict access to backups.

responsibilities.

Example (SQL Server):

• - Create a role

CREATE ROLE Manager;

• - Grant permissions to the role

GRANT SELECT, UPDATE ON Employees TO Manager;

• - Assign the role to a user

EXEC sp_addrolemember 'Manager', 'john_doe';

In PostgreSQL or MySQL, you would follow similar steps using GRANT statements and role

management commands.

permanent.

available. If there’s no match, NULL is returned for the missing side.

• Use case: When you want all records from both tables.

• Load the transformed data into the target database. For SQL databases,

tools like MySQL Workbench or SQL Server Integration Services (SSIS)

can be used. For NoSQL, tools like MongoDB Compass or Data Migration

Tools can be utilized.

often more efficient.

• A pattern used to improve performance by storing redundant data, reducing

the need for complex joins, and speeding up read queries.

• Used in data warehousing and systems requiring fast reads.

• For large inserts or updates, break the task into smaller batches to avoid

overwhelming the system.

• Identify unused indexes or fragmented indexes that could slow down query

performance.

across multiple servers.

with primary keys and referential integrity rather than repeating the data.

• Data is written to the primary database, and changes are replicated to

secondary databases after a delay.

• Advantages: Better performance due to less replication overhead.
• Disadvantages: Potential for data loss or inconsistency in the event of a

failure.

Tools for Replication:

• SQL Server: Use Always On Availability Groups or Transactional Replication.
• PostgreSQL: Use Streaming Replication or Logical Replication.
• MySQL: Use MySQL Replication or Group Replication.
• MongoDB: Use Replica Sets for automatic failover and high availability.

Example (PostgreSQL Streaming Replication):

# On Master Node

wal_level = replica

archive_mode = on

archive_command = 'cp %p /var/lib/postgresql/archive/%f'

# On Standby Node

primary_conninfo = 'host=master_ip port=5432 user=replication_user

password=replication_password'

Database Scaling & Performance

• Use AWS RDS, Azure SQL, or Google Cloud SQL to automate backups in

cloud environments. These platforms allow automatic backup scheduling

without manual intervention.

• Example: AWS RDS automatically performs daily backups and retains

backups for a configurable retention period.

and recovery procedures to verify data integrity.

Example (SQL Server):

BACKUP DATABASE MyDatabase TO DISK = 'D:\Backups\MyDatabase.bak'

WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = MyCert);

Restoration Example:

RESTORE DATABASE MyDatabase FROM DISK = 'D:\Backups\MyDatabase.bak'

WITH FILE = 1, NOUNLOAD, STATS = 10;

Additional Best Practices:

• Use incremental backups: Reduce the backup size and time by only backing up

data that has changed since the last backup.

• Backup Retention Policy: Implement a retention policy to ensure old backups are

properly archived or deleted.

Backup & Recovery

CategoryID, CustomerID).

an index on the filtering columns can improve performance.

of data from disk.

In summary, indexes drastically speed up SELECT queries at the cost of increased overhead

during INSERT, UPDATE, and DELETE operations.

fragmentation, and distribution of data to make its decision.

undone.

• Primary Key: A unique identifier for each record in a database table. No two rows in

a table can have the same primary key value. It ensures entity integrity.

Example: user_id in a users table.

• Foreign Key: A field (or a combination of fields) in one table that uniquely identifies a

row of another table. It establishes a relationship between two tables and enforces

referential integrity.

Example: user_id in an orders table linking to user_id in the users table.

rows).

• Use case: When you need every possible combination of rows.

(e.g., a unique index) is required.

frequently accessed data, and using denormalization where appropriate.

NoSQL (MongoDB - Optional)

• Ensure the integrity and completeness of the data after migration by

performing data checks (e.g., record counts, sampling).

• Indexes are data structures that speed up the retrieval of data from a database.

They work like the index in a book, allowing quick lookup of data without having to

scan the entire table.

Importance:

• Faster Searches: Improves query performance, especially for large datasets.
• Efficient Sorting: Helps in sorting and filtering operations.
• Primary and Foreign Keys: Automatically indexed to ensure quick data

retrieval.

range (e.g., by date or region).

• For distributed systems, monitor the lag between the primary database and

read replicas.

Tools for Monitoring:

• New Relic, Datadog, SolarWinds, pg_stat_activity (for PostgreSQL), SHOW

STATUS (for MySQL), or SQL Server Profiler for SQL Server.

• This pattern helps to normalize the data when you have a set of static values

used repeatedly across the database.

• Example: A Country table that contains a list of country names, which is

then referenced by other tables like Customers or Employees.

• Avoid complex subqueries or nested SELECTs, especially in large tables.

Rewrite queries using joins or CTEs (Common Table Expressions).

Why is it important?

• Normalization is the process of organizing the data in a database to reduce

redundancy and dependency by dividing large tables into smaller ones and linking

them with relationships.

Importance:

• Reduces Data Redundancy: Ensures data is only stored once.
• Improves Data Integrity: Ensures consistency and correctness of data.
• Simplifies Updates: Easier to maintain and modify data without affecting the

entire system.

query to leverage more efficient join algorithms.

• Analyze the query execution plan to identify any inefficient operations (e.g.,

full table scans) and refactor accordingly.

Advanced Topics

Transactions

A database schema is the structure that defines the organization of data in a database. It

includes definitions of tables, relationships, indexes, constraints, and other elements.

Can you give examples?

Database Constraints are rules applied to ensure the integrity and accuracy of the data

within a database. Examples include:

• NOT NULL: Ensures that a column cannot have a NULL value.
• UNIQUE: Ensures all values in a column are unique.
• CHECK: Ensures that all values in a column satisfy a specific condition.
• DEFAULT: Sets a default value for a column if no value is specified.
• FOREIGN KEY: Ensures the value in one table corresponds to a valid value in

another table.

Referential integrity ensures that relationships between tables remain consistent.

Specifically, it guarantees that foreign keys in a table must match primary keys in another

table, or they must be NULL.

A trigger is a special kind of stored procedure that is automatically executed or fired when

certain events occur in a database, such as INSERT, UPDATE, or DELETE.

Example: A trigger that automatically updates a timestamp field every time a row is modified.

• SQL Server: Developed by Microsoft, it's known for its strong integration with other

Microsoft products. It’s commonly used in enterprise environments.

• PostgreSQL: An open-source, object-relational database known for its standards

compliance, extensibility, and advanced features like support for complex queries,

JSONB, and custom data types.

• MySQL: An open-source relational database known for its speed and ease of use.

It's often used in web applications (e.g., with PHP) and is less feature-rich than

PostgreSQL but highly reliable.

A view is a virtual table created by querying data from one or more tables. It does not store

data itself but presents it in a specific format.

Use cases:

• Simplify complex queries: A view can encapsulate complex queries for easier

reuse.

• Data security: Views can restrict access to certain columns or rows for users without

giving them full table access.

• One-to-One (1:1): Each row in one table is linked to one row in another table.
• One-to-Many (1:M): A row in one table can be linked to many rows in another table.
• Many-to-Many (M:N): Rows in one table can be linked to many rows in another table

and vice versa. This often requires a junction table.

• Clustered Index: The data is stored in the order of the index. A table can have only

one clustered index because the rows can only be ordered in one way.

• Non-clustered Index: The index is separate from the data, and the index contains

pointers to the data. A table can have multiple non-clustered indexes.

A composite index is an index that involves more than one column in a table. It's used

when queries often filter or sort by multiple columns, optimizing performance for those

specific queries.

• INNER JOIN: Returns only the rows that have matching values in both tables.
• LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and the

matching rows from the right table. If no match is found, NULLs are returned for

columns from the right table.

A subquery is a query within a query. It is used to retrieve data that will be used in the main

query.

Use case:

• Filtering: When the result of a subquery is used to filter data in the outer query.
• Aggregation: When the result of the subquery is used in aggregate functions.

• UNION: Combines the result sets of two or more queries and removes duplicate

rows.

• UNION ALL: Combines the result sets of two or more queries and includes all rows,

even duplicates.

Joins & Queries

A FULL OUTER JOIN returns all rows from both tables, matching rows where possible. If

there's no match, the result will contain NULL values for the columns from the table that

doesn't have a match.

Use case: When you want to combine all records from both tables, regardless of whether

they match, and include NULL where no match exists.

Example:

SELECT * FROM table1

FULL OUTER JOIN table2 ON table1.id = table2.id;

Window functions allow you to perform calculations across a set of table rows that are

related to the current row, without collapsing the result set into a single row.

Example: ROW_NUMBER() generates a sequential integer to each row within the result set.

Example:

SELECT name, salary,

ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num

FROM employees;

This query adds a sequential row number to each employee, ordered by salary.

A JOIN condition specifies the columns that will be used to match rows between two or

more tables. This condition typically uses ON or USING in SQL.

Example:

SELECT *

FROM table1

JOIN table2 ON table1.id = table2.id;

In this example, the condition table1.id = table2.id determines how the rows from

both tables will be joined.

• WHERE: Filters rows before any grouping is done (i.e., filters individual records).
• HAVING: Filters records after grouping is done (i.e., filters grouped results).

Example:

SELECT department, AVG(salary)

FROM employees

GROUP BY department

HAVING AVG(salary) > 50000;

In this query, HAVING is used to filter groups that have an average salary greater than

50,000.

• IN: Checks whether a value is present in a list or a subquery’s result set.
• EXISTS: Checks whether a subquery returns any rows, returning TRUE if the

subquery returns one or more rows, otherwise FALSE.

Example with IN:

SELECT name

FROM employees

WHERE department_id IN (SELECT id FROM departments WHERE name =

'HR');

Example with EXISTS:

SELECT name

FROM employees e

WHERE EXISTS (SELECT 1 FROM departments d WHERE d.id =

e.department_id AND d.name = 'HR');

The BETWEEN operator filters the result set within a given range. It is inclusive, meaning it

includes the boundary values.

Example:

SELECT *

FROM employees

WHERE salary BETWEEN 40000 AND 60000;

This query will return employees whose salaries are between 40,000 and 60,000 (inclusive).

The LIKE operator is used to search for a specified pattern in a column.

• % matches any sequence of characters.
• _ matches a single character.

Example:

SELECT *

FROM employees

WHERE name LIKE 'J%n'; -- Names that start with 'J' and end with

'n'

Aggregate functions perform a calculation on a set of values and return a single value.

Common aggregate functions include:

• COUNT(): Returns the number of rows.
• SUM(): Returns the sum of a column.
• AVG(): Returns the average of a column.
• MAX(): Returns the maximum value.
• MIN(): Returns the minimum value.

Example:

SELECT AVG(salary)

FROM employees;

• COUNT(*): Counts all rows, including rows with NULL values in any column.
• COUNT(column_name): Counts only non-NULL values in the specified column.

Example:

SELECT COUNT(*) FROM employees; -- Counts all rows

SELECT COUNT(salary) FROM employees; -- Counts rows where salary is

NOT NULL

Conditional aggregation allows you to apply aggregate functions to a subset of data that

meets a certain condition. This is usually done with a CASE expression.

Example:

SELECT department,

SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS male_count,

SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS female_count

FROM employees

GROUP BY department;

This query counts the number of male and female employees in each department.

A Common Table Expression (CTE) is a temporary result set that can be referenced within

a SELECT, INSERT, UPDATE, or DELETE statement. CTEs are often used for organizing

complex queries.

Example:

WITH EmployeeCTE AS (

SELECT name, salary

FROM employees

WHERE salary > 50000

SELECT * FROM EmployeeCTE;

• LEFT JOIN: Returns all records from the left table, and matched records from the

right table. If there's no match, NULL values are returned for the right table's

columns.

• RIGHT JOIN: Returns all records from the right table, and matched records from the

left table. If there's no match, NULL values are returned for the left table's columns.

The UNION ALL operator combines the result sets of two or more queries, but unlike

UNION, it does not remove duplicate rows.

Example:

SELECT name FROM employees

UNION ALL

SELECT name FROM contractors;

This will return all names from both employees and contractors, including duplicates.

A subquery in the FROM clause allows you to treat the result of a query as a temporary table,

which can then be joined or queried further.

Example:

SELECT avg_salary

FROM (SELECT AVG(salary) AS avg_salary FROM employees) AS avg_table;

This query calculates the average salary using a subquery in the FROM clause.

Views

A view in SQL is a virtual table that is defined by a SELECT query. It does not store data

itself but rather provides a way to view and work with the results of a query as if it were a

table. Views allow users to access specific, formatted data without modifying the underlying

tables directly.

• Purpose: Simplify complex queries, abstract the database schema, and improve

security by exposing only the necessary data.

Example:

CREATE VIEW employee_view AS

SELECT id, name, department

FROM employees;

You can then query the view like a regular table:

SELECT * FROM employee_view;

• Data Storage:
• Table: Stores actual data in the database.
• View: A virtual table that shows data from one or more tables but does not

store data. It derives its data from the underlying tables each time it is

queried.

• Modifications:
• Table: You can insert, update, or delete data directly.
• View: You cannot modify data in a view unless certain conditions are met.

Some views (those based on a single table) are updatable, while others

(especially those with JOINs, aggregations, or complex logic) are not.

• Performance:
• Table: Generally optimized for performance with indexes, data distribution,

and so on.

• View: May have slower performance due to the query being executed each

time the view is accessed, especially if it's a complex view or involves large

datasets.

• Simplifies Complex Queries: Views can encapsulate complex queries, making

them reusable and easier to manage.

• Data Abstraction: Views abstract the underlying table structure and allow for easier

access to the data without revealing the full database schema.

• Security: By granting access to a view instead of a table, you can restrict access to

sensitive data and only expose the columns or rows that are necessary.

• Consistency: A view can standardize how data is accessed across multiple queries

or applications, ensuring consistent results.

• Reduced Redundancy: Rather than writing the same complex query multiple times,

you can create a view to encapsulate it and reuse the view in different queries.

Under what conditions?

Yes, views can be updated in SQL, but there are conditions for when this is possible:

• Updatable Views: A view is updatable if it:
• Is based on a single table.
• Does not contain aggregation functions like COUNT(), AVG(), or SUM().
• Does not include DISTINCT, GROUP BY, JOIN, or other complex operations

that modify the set of rows.

• Non-Updatable Views: Views that use complex JOINs, aggregations, or GROUP BY

clauses are typically non-updatable.

You can still modify data through these views by using triggers, such as INSTEAD

OF triggers, which allow you to define custom behavior for updating or deleting rows.

Example of an updatable view:

CREATE VIEW simple_view AS

SELECT id, name, salary FROM employees;

• - You can now update 'simple_view' directly:

UPDATE simple_view SET salary = 60000 WHERE id = 101;

An indexed view (also called a materialized view) in SQL Server is a view that has a

unique clustered index created on it. This results in the view storing the query results

physically in the database, similar to a table. It is useful for improving performance when you

have complex queries that aggregate data, as the results of the view are precomputed and

stored.

• Benefits:
• Faster read performance for complex aggregations or queries.
• Reduces the need for recomputing the result of a complex query each time

the view is accessed.

• Downside:
• Insert, update, or delete operations on the underlying tables will incur

additional overhead because the indexed view must also be updated.

Example:

CREATE VIEW employee_summary AS

SELECT department, AVG(salary) AS avg_salary

FROM employees

GROUP BY department;

• - Create a clustered index on the view

CREATE UNIQUE CLUSTERED INDEX idx_employee_summary

ON employee_summary(department);

Here’s how you can create a view in each of these DBMS:

SQL Server:

CREATE VIEW view_name AS

SELECT column1, column2

FROM table_name

WHERE condition;

PostgreSQL:

CREATE VIEW view_name AS

SELECT column1, column2

FROM table_name

WHERE condition;

MySQL:

CREATE VIEW view_name AS

SELECT column1, column2

FROM table_name

WHERE condition;

The syntax for creating a view is generally the same across these databases, although there

are certain DBMS-specific features and optimizations (e.g., indexed views in SQL Server).

A materialized view is a database object that stores the result of a query physically, similar

to an indexed view but more generalized. Unlike regular views, which generate their result

dynamically when queried, materialized views store the result set and can be periodically

refreshed.

• Advantages:
• Faster query performance, especially for complex queries or aggregations,

since the data is precomputed and stored.

• Refresh: The data in a materialized view may become outdated over time, so it

needs to be refreshed periodically, either manually or automatically, depending on the

DBMS.

Example:

CREATE MATERIALIZED VIEW sales_summary AS

SELECT product_id, SUM(sales) AS total_sales

FROM sales

GROUP BY product_id;

• - Refresh the materialized view when needed:

REFRESH MATERIALIZED VIEW sales_summary;

Materialized views are supported in systems like PostgreSQL and Oracle, but MySQL does

not have a built-in materialized view feature. You can simulate one using tables and

scheduled jobs.

To delete a view, you use the DROP VIEW statement. This removes the view from the

database entirely.

Example:

DROP VIEW view_name;

Be cautious when dropping a view, as it will no longer be available for use in queries. Ensure

no other objects are dependent on the view before dropping it.

• View:
• A view is a virtual table defined by a SELECT query. It provides a way to view

and query data without modifying the underlying tables directly.

• Views do not support procedural logic, loops, or variables.
• Stored Procedure:
• A stored procedure is a set of SQL statements that can be executed as a

single unit. It can include complex logic like loops, conditionals, and multiple

queries.

• Stored procedures can perform INSERT, UPDATE, DELETE, and other

operations on the database. They can also return results.

Example of a stored procedure:

CREATE PROCEDURE GetEmployeeSalary(IN emp_id INT)

BEGIN

SELECT salary FROM employees WHERE id = emp_id;

END;

Optimizing views is important to ensure that your queries are efficient. Here are some

strategies:

• Avoid Complex Views: Avoid creating views with complex logic (e.g., multiple

JOINs, GROUP BY, or subqueries), as they can result in slower performance. Keep

views simple and focused.

• Indexing: Ensure that the underlying tables have proper indexes, especially on the

columns used in JOIN or WHERE conditions.

• Materialized Views: Use materialized views when querying large datasets or

performing heavy aggregations. These views store the results physically and can be

refreshed periodically.

• Limit the Data: Use WHERE clauses in your views to filter unnecessary rows and

columns. Only select the data that is necessary.

• Optimize the Base Tables: Since views reflect the data from base tables, ensure

those tables are optimized (e.g., with appropriate indexing and normalization).

• Avoid Nested Views: A view based on another view can result in poor performance,

as the inner view’s query needs to be executed each time the outer view is queried.

Stored Procedures & Functions

A stored procedure is a precompiled collection of SQL statements that can be executed as

a single unit. It allows you to encapsulate logic, such as data manipulation, complex queries,

or repetitive tasks, into reusable blocks. Stored procedures can accept parameters, perform

actions like SELECT, INSERT, UPDATE, and DELETE, and return results.

• Benefits: Improved performance (since the procedure is precompiled), code

reusability, centralized logic, and security (by restricting direct access to tables).

Example:

CREATE PROCEDURE GetEmployeeDetails (IN emp_id INT)

BEGIN

SELECT * FROM employees WHERE id = emp_id;

END;

SQL Server:

CREATE PROCEDURE GetEmployeeDetails (@emp_id INT)

BEGIN

SELECT * FROM employees WHERE id = @emp_id;

END;

PostgreSQL:

CREATE OR REPLACE FUNCTION GetEmployeeDetails(emp_id INT)

RETURNS TABLE(id INT, name VARCHAR) AS $$

BEGIN

RETURN QUERY SELECT id, name FROM employees WHERE id = emp_id;

END;

$$ LANGUAGE plpgsql;

MySQL:

DELIMITER //

CREATE PROCEDURE GetEmployeeDetails(IN emp_id INT)

BEGIN

SELECT * FROM employees WHERE id = emp_id;

END //

DELIMITER ;

The syntax varies slightly between the databases, but the core idea remains the same.

• Performance: Stored procedures are precompiled, so execution is faster compared

to running individual SQL queries each time.

• Code Reusability: Once defined, stored procedures can be reused in multiple

places, reducing redundancy.

• Security: You can grant users permission to execute a stored procedure without

giving them direct access to the underlying tables.

• Maintainability: Centralizing business logic in stored procedures makes

maintenance easier, especially when making changes to the logic.

• Error Handling: Stored procedures allow you to include error-handling mechanisms

like TRY...CATCH in SQL Server or EXCEPTION in PostgreSQL.

Stored procedures allow you to pass parameters to them, either input or output

parameters.

• Input Parameters: These allow you to send data to the procedure.
• Output Parameters: These allow the procedure to send data back to the caller.

Example:

SQL Server:

CREATE PROCEDURE GetEmployeeSalary (@emp_id INT)

BEGIN

SELECT salary FROM employees WHERE id = @emp_id;

END;

Calling the procedure:

EXEC GetEmployeeSalary @emp_id = 101;

MySQL:

DELIMITER //

CREATE PROCEDURE GetEmployeeSalary(IN emp_id INT)

BEGIN

SELECT salary FROM employees WHERE id = emp_id;

END //

DELIMITER ;

Calling the procedure:

CALL GetEmployeeSalary(101);

• Function:
• Return Type: Always returns a value (can be scalar or table).
• Used in Queries: Can be used in SELECT, WHERE, and ORDER BY clauses.
• Side Effects: Typically designed to perform calculations or return a result

without modifying the database.

• Stored Procedure:
• No Return Type: May or may not return values (using output parameters or

result sets).

• Used for Actions: Typically used for performing database operations (like

INSERT, UPDATE, DELETE).

• Side Effects: Designed to perform operations that modify data or perform

business logic.

Example:

Function (returns a value):

CREATE FUNCTION GetEmployeeSalary (emp_id INT) RETURNS DECIMAL

BEGIN

RETURN (SELECT salary FROM employees WHERE id = emp_id);

END;

Stored Procedure (performs an action):

CREATE PROCEDURE UpdateEmployeeSalary (IN emp_id INT, IN new_salary

DECIMAL)

BEGIN

UPDATE employees SET salary = new_salary WHERE id = emp_id;

END;

Error handling is an essential part of stored procedures. Here’s how you handle errors in

different databases:

SQL Server: Use TRY...CATCH blocks to handle errors.

BEGIN TRY

• - Some SQL operation

INSERT INTO employees (name) VALUES ('John Doe');

END TRY

BEGIN CATCH

SELECT ERROR_MESSAGE() AS ErrorMessage;

END CATCH;

PostgreSQL: Use EXCEPTION blocks in PL/pgSQL.

BEGIN

• - Some SQL operation

INSERT INTO employees (name) VALUES ('John Doe');

EXCEPTION

WHEN others THEN

RAISE NOTICE 'Error occurred: %', SQLERRM;

END;

MySQL: MySQL doesn't have a built-in TRY...CATCH, but you can use

DECLARE...HANDLER.

DELIMITER //

CREATE PROCEDURE ExampleProcedure()

BEGIN

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

SELECT 'An error occurred';

• - Some SQL operation

INSERT INTO employees (name) VALUES ('John Doe');

END //

DELIMITER ;

You can call stored procedures from programming languages using appropriate database

connectors and drivers.

Example (C# with SQL Server):

using (SqlConnection conn = new SqlConnection(connectionString))

conn.Open();

using (SqlCommand cmd = new SqlCommand("GetEmployeeDetails",

conn))

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new SqlParameter("@emp_id",

SqlDbType.Int)).Value = 101;

using (SqlDataReader reader = cmd.ExecuteReader())

while (reader.Read())

Console.WriteLine(reader["name"]);

Executing stored procedures asynchronously can improve the performance of applications

by allowing other tasks to run while waiting for the procedure to finish.

Example (C#):

using (SqlConnection conn = new SqlConnection(connectionString))

await conn.OpenAsync();

using (SqlCommand cmd = new SqlCommand("GetEmployeeDetails",

conn))

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new SqlParameter("@emp_id",

SqlDbType.Int)).Value = 101;

using (SqlDataReader reader = await

cmd.ExecuteReaderAsync())

while (await reader.ReadAsync())

Console.WriteLine(reader["name"]);

• Vendor Lock-In: Stored procedures use database-specific syntax, making it harder

to migrate to different database platforms.

• Complexity: As business logic grows within stored procedures, they can become

difficult to maintain and debug.

• Performance: While stored procedures can be optimized, poorly written ones can

hurt performance.

• Limited Flexibility: Stored procedures are less flexible compared to application

code, and they cannot easily handle more complex logic that might be easier in a

high-level programming language.

• Testing: Stored procedures are harder to test and debug in isolation compared to

application code.

Debugging stored procedures can be done in the following ways:

• SQL Server: SQL Server Management Studio (SSMS) allows you to set breakpoints,

step through the code, and inspect variable values during execution.

Steps:

• Stored Procedures: The RETURN keyword in a stored procedure is typically used to

exit the procedure and can optionally return an integer value (usually indicating the

success or failure of the procedure). The return value is often used for error handling

or status reporting.

Indexing

An index in SQL is a database object that improves the speed of data retrieval operations

on a table. It works by maintaining a sorted order of column values in a separate structure,

allowing for faster search and retrieval compared to scanning every row in the table. The

index essentially creates a quick lookup table to find specific values.

Example of an index creation:

CREATE INDEX idx_column_name ON table_name(column_name);

Indexes are particularly useful for queries that involve WHERE, JOIN, ORDER BY, and GROUP

BY clauses.

Indexes improve query performance in several ways:

There are several types of indexes in SQL:

Unique Index: Ensures that all values in the indexed column(s) are unique. Automatically

created when a PRIMARY KEY or UNIQUE constraint is defined on a column.

Example:

CREATE UNIQUE INDEX idx_employee_id ON employees(id);

Full-Text Index: Used for indexing large text fields. It allows for more advanced searches

like full-text searches (e.g., MATCH in MySQL).

Example (MySQL):

CREATE FULLTEXT INDEX idx_fulltext_desc ON products(description);

• Clustered Index: Defines the physical order of the data in the table based on the

index. Each table can have only one clustered index.

• Non-Clustered Index: An index that stores a separate structure containing the

indexed columns and pointers to the actual data rows. A table can have multiple

non-clustered indexes.

• Composite Index: An index that includes multiple columns. Useful for queries that

filter based on multiple columns.

• Spatial Index: Used for indexing spatial data types such as points, lines, and

polygons (mostly for geographical data).

Creating an Index:

CREATE INDEX idx_index_name ON table_name(column_name);

Dropping an Index:

DROP INDEX idx_index_name ON table_name;

In SQL Server, dropping an index:

DROP INDEX idx_index_name; -- No need to specify table name.

• Clustered Index:
• The data in the table is physically sorted based on the clustered index.
• Each table can have only one clustered index (usually the primary key).
• Fast for queries that involve range-based retrieval (e.g., BETWEEN or ORDER

BY).

• Non-Clustered Index:
• The data in the table is not physically sorted. The non-clustered index is

stored separately from the actual data.

• A table can have multiple non-clustered indexes.
• Faster for point lookups (single value queries) but less efficient for

range-based searches.

Indexes can slow down data modification operations:

• INSERT: When a new row is inserted, the index must also be updated to include the

new value, adding extra overhead.

• UPDATE: If the indexed column is updated, the index must be modified to reflect the

new value, which can be slower.

• DELETE: When a row is deleted, the corresponding index entries must be removed,

causing additional overhead.

While indexes speed up SELECT queries, they can reduce the performance of INSERT,

UPDATE, and DELETE operations due to the extra work needed to maintain the index.

Consider creating an index when:

A composite index is an index that is created on multiple columns. It is used when a query

filters or sorts based on more than one column. Composite indexes are especially useful

when queries frequently involve conditions on multiple columns.

• Example Use Case: If a query filters by both last_name and first_name,

creating a composite index on (last_name, first_name) will speed up the

query.

Example of creating a composite index:

CREATE INDEX idx_lastname_firstname ON employees(last_name,

first_name);

• Unique Index:
• Ensures that the values in the indexed columns are unique.
• Can be created on any column and allows NULL values (in most DBMS).
• A table can have multiple unique indexes.
• Primary Key Index:
• Enforces the uniqueness of the column(s) and also guarantees NOT NULL

constraint on the column(s).

• A table can only have one primary key.
• Automatically creates a unique index.

In short, both enforce uniqueness, but the primary key also guarantees that the column(s)

cannot be NULL.

The query optimizer in the database engine decides which index to use based on several

factors:

A covering index is an index that contains all the columns needed by a query, meaning the

query can be answered entirely by the index without accessing the actual table data. This

can improve query performance by reducing disk I/O.

Example:

If a query selects id, name, and salary from the employees table and there is an index

on (id, name, salary), the index itself is sufficient to satisfy the query, and the

database doesn't need to access the table at all.

Index fragmentation occurs when data is inserted, updated, or deleted, causing the index

structure to become inefficient. This can negatively impact performance in several ways:

Reorganizing an Index: A lighter operation that compacts the index and defragments it. It is

used when fragmentation is low (less than 30%).

SQL Server:

ALTER INDEX idx_name ON table_name REORGANIZE;

PostgreSQL:

PostgreSQL does not have an explicit REORGANIZE command, but you can run VACUUM to

clean up the database and reduce fragmentation:

VACUUM INDEX idx_name;

MySQL:

OPTIMIZE TABLE table_name;

Rebuilding an Index: A more intensive operation where the index is completely dropped

and recreated. This can reduce fragmentation to nearly zero.

SQL Server:

ALTER INDEX idx_name ON table_name REBUILD;

PostgreSQL:

REINDEX INDEX idx_name;

MySQL:

ALTER TABLE table_name DROP INDEX idx_name, ADD INDEX idx_name

(column_name);

For large datasets, query optimization can be crucial to ensure performance is not impacted.

Here are several tips:

A transaction is a sequence of SQL operations that are treated as a single unit of work. A

transaction must either be fully completed (committed) or fully undone (rolled back) to

ensure the integrity of the database. Transactions provide the foundation for the ACID

properties (Atomicity, Consistency, Isolation, Durability) that ensure reliable and consistent

operations.

The ACID properties are critical to ensuring the reliability of transactions in a database:

• Atomicity: All operations within a transaction are executed completely or not at all.
• Consistency: A transaction takes the database from one valid state to another,

ensuring that all rules (constraints, triggers, etc.) are respected.

• Isolation: Transactions are isolated from one another, meaning intermediate steps of

a transaction are invisible to others until the transaction is committed.

• Durability: Once a transaction is committed, the changes are permanent, even in the

case of a system crash.

The COMMIT statement is used to finalize a transaction by making all the changes made

during the transaction permanent. It ensures that all changes made during the transaction

are saved and visible to other transactions.

• When to use: After the transaction operations have completed successfully and you

want to ensure that the changes are saved to the database.

Example:

BEGIN TRANSACTION;

UPDATE employees SET salary = 5000 WHERE id = 1;

COMMIT;

The ROLLBACK statement is used to undo all changes made during a transaction. If an error

occurs or something goes wrong, ROLLBACK ensures that the database is restored to its

state before the transaction began.

• When to use: If any part of the transaction fails or if you wish to cancel the changes

made during the transaction.

Example:

BEGIN TRANSACTION;

UPDATE employees SET salary = 5000 WHERE id = 1;

• - Something goes wrong

ROLLBACK;

A SAVEPOINT is a way to set a point within a transaction to which you can later roll back if

necessary. It provides more granular control, allowing partial rollback rather than undoing the

entire transaction.

• When to use: When you want to mark certain stages within a transaction and allow

for partial rollback if an error occurs.

Example:

BEGIN TRANSACTION;

SAVEPOINT sp1;

UPDATE employees SET salary = 5000 WHERE id = 1;

• - Something goes wrong

ROLLBACK TO sp1; -- Rolls back to the savepoint, undoing only the

changes after it

COMMIT;

Can you explain the different isolation

levels (e.g., READ UNCOMMITTED, READ COMMITTED, REPEATABLE

READ, SERIALIZABLE)?

The isolation level in SQL defines how transactions interact with each other in terms of

visibility of data. The isolation level affects the balance between data consistency and

transaction concurrency.

• READ UNCOMMITTED: Allows dirty reads. One transaction can see uncommitted

changes made by another transaction. This is the lowest level of isolation.

• READ COMMITTED: Prevents dirty reads, but allows non-repeatable reads (data

can change during the transaction).

• REPEATABLE READ: Prevents dirty reads and non-repeatable reads, but phantom

reads (new rows can appear in a query) are still possible.

• SERIALIZABLE: The highest isolation level. Prevents dirty reads, non-repeatable

reads, and phantom reads by making transactions execute sequentially.

Transactions impact concurrent operations by introducing locking mechanisms to ensure

that multiple transactions don't interfere with each other and cause inconsistent data. This

affects concurrency in several ways:

• Locking: Transactions may lock rows or tables to prevent conflicting changes,

leading to possible delays for other transactions.

• Deadlocks: When two or more transactions are waiting for each other to release

locks, causing a cycle of dependency. This can be automatically detected and

resolved by the DBMS.

• Implicit Transactions: Automatically begin when a database operation is executed.

The DBMS treats each individual statement as a transaction and automatically

commits after each statement, unless explicitly rolled back.

• Explicit Transactions: Transactions that the user manually controls using BEGIN

TRANSACTION, COMMIT, and ROLLBACK. The user decides when the transaction

begins and ends.

Deadlocks occur when two or more transactions are waiting for each other to release locks,

causing a cycle of dependencies. To handle deadlocks:

• Deadlock Detection: DBMS systems (e.g., SQL Server) can automatically detect

deadlocks and terminate one of the transactions to break the deadlock.

• Retry Logic: If a deadlock is detected, you can implement a retry mechanism in your

application to reattempt the transaction after a short delay.

• Optimize Transactions: Keep transactions short and ensure that they acquire locks

in the same order to reduce the likelihood of deadlocks.

In stored procedures, you control transaction behavior using the following:

• BEGIN TRANSACTION: Explicitly starts a transaction in the stored procedure.
• COMMIT: Ends the transaction and commits all changes made during the

transaction.

• ROLLBACK: Undoes all changes made during the transaction if something goes

wrong.

• SAVEPOINT: Sets a point in the transaction to which you can roll back.

Example:

BEGIN TRANSACTION;

• - SQL operations here

IF (some_condition)

BEGIN

COMMIT; -- Commit if condition is true

END

ELSE

BEGIN

ROLLBACK; -- Rollback if condition is false

END

Normalization & Database Design

Normalization is the process of organizing a database to reduce redundancy and

dependency by dividing large tables into smaller, manageable tables and defining

relationships between them. This process aims to improve the structure of the database by

minimizing the chances of data anomalies (insertion, update, and deletion anomalies).

Normal forms are guidelines used to organize a relational database schema. Each form

addresses a different kind of redundancy or dependency.

• 1NF (First Normal Form):
• A table is in 1NF if it contains only atomic (indivisible) values and each record

has a unique identifier (Primary Key).

• No repeating groups or arrays are allowed.
• 2NF (Second Normal Form):
• A table is in 2NF if it is in 1NF and all non-key attributes are fully

functionally dependent on the primary key.

• Eliminates partial dependency (when a non-key attribute depends on only

part of a composite primary key).

• 3NF (Third Normal Form):
• A table is in 3NF if it is in 2NF and there are no transitive dependencies

(non-key attributes depending on other non-key attributes).

• This removes dependencies between non-key attributes.
• BCNF (Boyce-Codd Normal Form):
• A table is in BCNF if it is in 3NF and every determinant is a candidate key.
• This is a stricter version of 3NF.
• 4NF (Fourth Normal Form):
• A table is in 4NF if it is in BCNF and multi-valued dependencies are

removed.

• This ensures that a record doesn’t have two or more independent

multi-valued attributes.

• 5NF (Fifth Normal Form):
• A table is in 5NF if it is in 4NF and cannot be decomposed further without

losing data.

Denormalization is the process of intentionally introducing redundancy into a database by

merging tables or adding redundant data to reduce the complexity of database queries,

improve read performance, and simplify the schema.

• When to use:
• When read performance is more critical than data integrity and

normalization (e.g., in OLAP systems or reporting).

• In cases where complex JOINs are causing performance bottlenecks.
• In high-performance environments where frequently accessed data can be

stored redundantly to reduce query times.

To normalize a database to 3NF:

• 1NF (First Normal Form): Ensures that the table has atomic columns (no

multi-valued or repeating groups) and each row has a unique identifier (Primary Key).

• 2NF (Second Normal Form): The table must be in 1NF and all non-key columns

must depend on the entire primary key (i.e., there should be no partial

dependencies).

Example:

• A table with a composite primary key (e.g., StudentID, CourseID) where non-key

attributes like CourseName only depend on CourseID would violate 2NF because

CourseName is partially dependent on the composite key. You would separate this

into two tables (one for Courses and one for Students).

Referential integrity ensures that relationships between tables are consistent. Specifically,

it ensures that a foreign key in one table must match a primary key or a unique key in

another table. This prevents invalid references from being made.

• Enforcing Referencing Integrity:
• Use foreign keys to create relationships between tables.
• Enforce actions like ON DELETE CASCADE, ON UPDATE CASCADE, or

ON DELETE RESTRICT to define how changes in parent tables affect related

child tables.

Example:

• A Foreign Key in the Orders table references the CustomerID in the Customers

table. If you try to delete a customer that has associated orders, referential integrity

will prevent the delete unless actions like CASCADE are specified.

Data redundancy occurs when the same piece of data is stored in multiple places, which

can lead to inconsistencies and wasted storage. To avoid redundancy:

How does it differ from a primary key?

A composite key is a combination of two or more columns in a table that can uniquely

identify each record in the table. It is used when a single column is not sufficient to uniquely

identify a row.

• Composite Key: Consists of multiple columns.

Example: A table storing student-course enrollments might use a composite key

consisting of StudentID and CourseID to uniquely identify each enrollment record.

• Primary Key: A primary key is a single column (or a composite of columns) that

uniquely identifies each record in a table. It can be a single column or a composite

of multiple columns, but it must be unique for each row.

An Entity-Relationship Diagram (ERD) is a graphical representation of entities and their

relationships to each other within a database. It is used to visualize the structure of a

database and how tables (entities) relate to one another.

• Entities are represented as rectangles.
• Attributes are shown as ovals connected to their entities.
• Relationships are represented as diamonds, showing how entities interact.

ERDs help in the conceptual design phase of a database, outlining entities, relationships,

and keys before implementation.

How is it different from a natural key?

A surrogate key is an artificial, system-generated key used to uniquely identify a record in a

table. It has no business meaning and is typically a number (e.g., auto-incremented ID).

• Surrogate Key:
• No business meaning.
• Commonly used in data warehousing and large-scale systems.
• Example: A column UserID that is generated automatically.
• Natural Key: A key that has real-world business meaning and is used to uniquely

identify records.

• Example: SocialSecurityNumber or EmailAddress could serve as

natural keys.

Designing a database schema for a large e-commerce application involves careful

consideration of data requirements, scalability, and normalization. Key entities and

relationships to consider:

MongoDB is a NoSQL document-oriented database that stores data in flexible,

semi-structured documents rather than in rows and columns like SQL databases. Unlike

SQL databases, which rely on rigid schemas, MongoDB uses a schema-less approach

where each document can have different fields and structures.

Key differences:

• SQL Databases: Store data in tables with a fixed schema and use ACID

transactions for consistency.

• MongoDB: Uses a document model (e.g., JSON-like structures) and offers more

flexibility, scalability, and ease of replication but may sacrifice some consistency in

distributed environments.

Advantages:

• Scalability: MongoDB scales horizontally through sharding, which can handle large

datasets across distributed clusters.

• Flexibility: Schema-less design allows for dynamic data models and easier iteration

during development.

• Performance: It can perform high-throughput reads and writes for large datasets.
• High Availability: Through replica sets, MongoDB ensures data availability and fault

tolerance.

Disadvantages:

• Consistency: By default, MongoDB uses eventual consistency, which may not be

suitable for all applications (though it supports ACID transactions in some cases).

• Complexity in Transactions: While MongoDB now supports multi-document

transactions, working with them is more complex compared to SQL.

• Data Duplication: The flexibility can sometimes lead to data duplication and

inconsistency if not properly managed.

How does it differ from rows in

SQL databases?

A document in MongoDB is a record represented as a JSON-like object (BSON format)

that contains field-value pairs. It can have a flexible structure, meaning each document in a

collection may contain different fields and data types.

Differences from SQL Rows:

• In SQL, a row is a fixed set of columns (defined by the schema), while in MongoDB,

a document is more flexible and can vary in structure.

• A row in SQL is constrained by a schema, whereas a document in MongoDB can

store complex, nested data structures (arrays, sub-documents).

• Database: A container for collections. MongoDB allows you to have multiple

databases within the same instance.

• Collection: A group of MongoDB documents. Collections are similar to tables in SQL

but don’t have a fixed schema.

Create:

db.collection.insertOne({ name: "Alice", age: 25 });

db.collection.insertMany([{ name: "Bob", age: 30 }, { name:

"Charlie", age: 35 }]);

Read:

db.collection.find({ age: { $gt: 30 } });

db.collection.findOne({ name: "Alice" });

Update:

db.collection.updateOne({ name: "Alice" }, { $set: { age: 26 } });

db.collection.updateMany({ age: { $lt: 30 } }, { $set: { status:

"young" } });

Delete:

db.collection.deleteOne({ name: "Alice" });

db.collection.deleteMany({ age: { $lt: 30 } });

How is it different from SQL indexes?

Indexing in MongoDB is a mechanism to improve query performance by reducing the

amount of data the system needs to scan. MongoDB creates indexes on fields that are

queried frequently.

Differences from SQL indexes:

• MongoDB uses B-tree indexes (by default) but also supports other types like

hashed indexes, geospatial indexes, and text indexes.

• SQL indexes are typically built on a fixed schema and are more rigid, while MongoDB

indexes can be dynamic, allowing indexing on any field within a document.

• MongoDB supports compound indexes and array indexes for more complex

queries.

The $in operator is used to match values in a specified array. It checks if the value of a field

is equal to any value in the provided array.

Example:

db.collection.find({ age: { $in: [25, 30, 35] } });

This query finds all documents where the age field is 25, 30, or 35.

• Replica Set: A group of MongoDB servers that maintain the same data. It provides

high availability and data redundancy by replicating data across multiple servers. If

the primary server goes down, one of the secondaries can be promoted to primary

automatically.

• Sharded Cluster: Used for horizontal scaling. It splits data across multiple servers

(shards) based on a shard key. Each shard holds a portion of the data, and a

mongos router directs queries to the correct shard. Sharding helps manage large

datasets by distributing the load.

find(): Returns a cursor to all documents that match the query. You can iterate over the

results using .forEach() or convert them to an array with .toArray().

Example:

db.collection.find({ age: { $gt: 30 } });

findOne(): Returns the first document that matches the query, or null if no document is

found.

Example:

db.collection.findOne({ name: "Alice" });

How do you use

it?

The Aggregation Framework in MongoDB is used to perform complex data processing

tasks such as grouping, filtering, sorting, and reshaping documents.

It uses a pipeline of stages to process documents:

Example:

db.collection.aggregate([

{ $match: { status: "active" } }, // Filter documents

{ $group: { _id: "$age", count: { $sum: 1 } } }, // Group by age

and count occurrences

{ $sort: { count: -1 } } // Sort by count in descending order

]);

You can use the updateOne(), updateMany(), or replaceOne() methods to update data.

Example:

db.collection.updateOne({ name: "Alice" }, { $set: { age: 27 } });

• $set updates the value of a field.
• $inc increments a field's value.

MongoDB provides multi-document transactions starting with version 4.0. This allows you

to execute multiple operations in a transaction, ensuring ACID properties (Atomicity,

Consistency, Isolation, Durability) across multiple documents and collections.

Example:

const session = client.startSession();

try {

session.startTransaction();

db.collection1.updateOne({ _id: 1 }, { $set: { status: "completed"

} }, { session });

db.collection2.updateOne({ _id: 2 }, { $set: { status: "shipped" }

}, { session });

session.commitTransaction();

} catch (error) {

session.abortTransaction();

} finally {

session.endSession();

Data modeling in MongoDB refers to the design of how data will be stored, organized, and

accessed in the database. Since MongoDB is schema-less, data modeling involves deciding

how to structure documents, collections, and their relationships (embedded vs. referenced

documents) to balance performance, scalability, and ease of querying.

In MongoDB, relationships can be handled in two ways:

• Embedded documents: Storing related data within a single document (best for

one-to-many relationships).

Example: A blog post with comments as an embedded array.

• Referenced documents: Storing related data in separate collections and using

references (best for many-to-many or large datasets).

Example: A customer document may reference an order document by storing the

order’s _id in the customer document.

A document schema defines the structure, rules, and validations for documents in a

collection. MongoDB is schema-less by default, but you can enforce schema validation rules

using MongoDB schema validation.

Example:

db.createCollection("users", {

validator: {

$jsonSchema: {

bsonType: "object",

required: ["name", "email"],

properties: {

name: { bsonType: "string" },

email: { bsonType: "string" }

});

Data validation in MongoDB can be implemented using JSON Schema or custom validation

rules to ensure data consistency.

Example (using JSON Schema):

db.createCollection("products", {

validator: {

$jsonSchema: {

bsonType: "object",

required: ["product_name", "price"],

properties: {

product_name: { bsonType: "string" },

price: { bsonType: "double", minimum: 0 }

});

MongoDB Atlas is a fully-managed cloud database service provided by MongoDB Inc. It

offers:

• Automated backups, scaling, and updates.
• Multi-cloud support (AWS, GCP, Azure).
• Advanced security features like encryption and IP whitelisting.
• Monitoring and performance optimization tools.
• Global replication across different regions.

• Indexing: Create appropriate indexes to optimize query performance.
• Sharding: Distribute large datasets across multiple servers.
• Caching: Cache frequently accessed data.
• Aggregation optimization: Use $match early in the aggregation pipeline.
• Avoid joins: MongoDB performs best when data is denormalized or embedded.

Database Security

Database security refers to the practices, tools, and measures used to protect a database

from unauthorized access, misuse, modification, or destruction of its data. It ensures the

confidentiality, integrity, and availability (often referred to as the CIA triad) of the data stored

within the database.

Why it's important:

• Confidentiality: Protects sensitive data from unauthorized access (e.g., personal

information, financial data).

• Integrity: Ensures data remains accurate, consistent, and unaltered by unauthorized

users.

• Availability: Ensures data is available when needed and prevents disruptions from

threats like ransomware or DDoS attacks.

Common database security risks include:

• SQL Injection: Malicious queries that can compromise the database if the input is

not properly validated.

• Unauthorized Access: Users gaining access to sensitive data without permission.
• Data Breaches: Exposing sensitive data due to insufficient encryption or weak

access controls.

• Privilege Escalation: Attackers gaining elevated permissions or access levels.
• Malicious Insiders: Employees or authorized users intentionally or unintentionally

leaking or modifying data.

• Denial of Service (DoS): Attackers overwhelming the database with requests,

making it unavailable to legitimate users.

• Backup Exposure: Unencrypted or improperly secured backup files that are

accessible to unauthorized users.

SQL Injection is a type of attack where an attacker inserts or manipulates malicious SQL

code into a query, which can compromise the database. It usually happens when user input

is improperly sanitized or validated.

Prevention techniques:

Use Prepared Statements/Parameterized Queries: This ensures that user input is treated

as data, not executable code.

• - Example in MySQL (using PDO in PHP)

$stmt = $pdo->prepare('SELECT * FROM users WHERE username =

:username AND password = :password');

$stmt->execute(['username' => $username, 'password' => $password]);

• Input Validation: Always validate user input by checking for expected data types,

lengths, and ranges.

• Escaping User Input: If parameters cannot be parameterized, make sure all user

input is properly escaped.

• Least Privilege Principle: Limit database user permissions to only those necessary

to perform their tasks.

• Web Application Firewalls (WAF): Use WAFs to detect and block SQL injection

attacks.

Role-Based Access Control (RBAC) is a method for restricting system access to

authorized users based on roles. Each role defines a set of permissions (what actions can

be performed on which database objects).

Steps to implement RBAC:

Stored Procedures can help improve database security by encapsulating business logic

and SQL statements in precompiled code, making it harder for attackers to inject malicious

code.

Roles in database security:

• Input Validation: Stored procedures allow you to validate user inputs at the

database level, preventing malicious input from being executed.

• Preventing Direct Access: By using stored procedures, users can be given

permissions to execute specific procedures rather than direct access to the

underlying tables.

• Encapsulation of Business Logic: The logic within stored procedures is not visible

to the end-user, reducing the attack surface.

• Audit Logging: Stored procedures can include logic to log user activity for auditing

and compliance purposes.

Example: Instead of allowing users to execute arbitrary INSERT or UPDATE statements, you

can give them permission to execute a specific stored procedure that does the necessary

validation and modification of data.

CREATE PROCEDURE update_salary(IN emp_id INT, IN new_salary DECIMAL)

BEGIN

IF new_salary > 0 THEN

UPDATE employees SET salary = new_salary WHERE id = emp_id;

END IF;

END;

Encryption is essential for protecting sensitive data both at rest (on disk) and in transit

(during communication).

SQL Server:

Transparent Data Encryption (TDE): Encrypts data at rest without requiring application

changes.

CREATE DATABASE ENCRYPTION KEY;

ALTER DATABASE MyDatabase SET ENCRYPTION ON;

Column-Level Encryption: For encrypting specific columns.

CREATE CERTIFICATE MyCertificate WITH SUBJECT = 'My Certificate';

CREATE SYMMETRIC KEY MySymmetricKey WITH ALGORITHM = AES_256

ENCRYPTION BY CERTIFICATE MyCertificate;

OPEN SYMMETRIC KEY MySymmetricKey DECRYPTION BY CERTIFICATE

MyCertificate;

PostgreSQL:

pgcrypto extension provides functions for encrypting data.

SELECT pgp_sym_encrypt('Sensitive Data', 'encryption_key');

MySQL:

Encryption Functions: MySQL provides functions like AES_ENCRYPT and AES_DECRYPT.

SELECT AES_ENCRYPT('Sensitive Data', 'encryption_key');

SELECT AES_DECRYPT(encrypted_data, 'encryption_key');

For all databases, consider using SSL/TLS for encrypting data in transit.

Backup and restore security involves ensuring that backup files are stored securely and

that access to backup data is tightly controlled.

Steps for Secure Backup:

There are three primary types of database backups:

SQL Server:

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

• - 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

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.

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

Point-in-time recovery (PITR) allows you to restore a database to a specific moment in

time, which can be crucial in scenarios where:

• Data corruption or accidental deletion occurs.
• You want to rollback to a specific moment before a harmful event.

How it works:

• First, a full backup is restored.
• Then, transaction log backups are applied, allowing you to replay changes made

after the full backup until the specified point in time.

SQL Server Example:

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 up to a specific

time

PostgreSQL Example:

To enable point-in-time recovery, you need to restore a base backup, then use WAL

(Write-Ahead Logging) archives to apply changes until the desired point.

• You would also set the restore_command in recovery.conf and specify the

recovery_target_time.

Automating backups ensures that backups are performed regularly and without manual

intervention. You can use the following methods:

Database replication is the process of copying and maintaining database objects in multiple

databases to ensure high availability and fault tolerance.

Types of Replication:

Database scaling refers to increasing a database's ability to handle more workload—either

by increasing the resources available to a single instance or distributing the load across

multiple instances.

Sharding and partitioning are techniques used to distribute data across multiple servers or

tables to improve performance and scalability.

A read replica is a copy of a database that is used to offload read-only queries from the

primary database (master). It is particularly useful for handling high read traffic and

improving database scalability.

• How it helps with scaling:
• By distributing read requests across multiple replicas, the primary database

can focus on write operations, reducing the load on the master.

• It improves performance by providing additional resources dedicated to read

operations.

• Example: In a web application with high traffic, multiple read replicas can

serve read-heavy queries, while writes are only made to the master database.

Note: Read replicas are usually asynchronous, meaning there can be a slight delay in

replication. For critical real-time reads, this might not be suitable.

Database caching involves storing frequently accessed data in a faster storage layer (e.g.,

memory) so that future requests for the same data can be served more quickly, reducing the

need to query the database repeatedly.

• How it improves performance:
• Reduces database load: Instead of querying the database every time data is

requested, the cache can provide instant access to commonly requested

data.

• Improves response time: Caching data in-memory (using technologies like

Redis or Memcached) makes it accessible in nanoseconds, compared to the

millisecond access time of traditional disk-based databases.

• Reduces latency: Caching significantly reduces the time taken to fetch data,

improving the overall user experience.

Example: Frequently accessed user profiles in a web application can be cached using

Redis, so that subsequent requests don't have to query the database again.

Monitoring database performance is crucial for identifying bottlenecks and ensuring optimal

functioning. The following methods are commonly used:

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. 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.

Query optimization is the process of improving the efficiency of SQL queries to reduce

resource consumption and improve response time. The goal is to make sure that queries are

executed using the most efficient execution plan possible.

• Why it's important:
• Optimized queries help reduce CPU load, memory consumption, and I/O

operations, improving overall system performance.

• It prevents slow queries that might degrade the user experience or cause

resource contention in high-traffic applications.

• Common techniques:
• Indexing: Using appropriate indexes can drastically reduce query times.
• **Avoiding SELECT ***: Only select the necessary columns instead of

selecting all columns.

• Query refactoring: Break down complex queries into simpler ones, or

replace subqueries with joins when possible.

• Using appropriate joins: Ensure proper joins are used (e.g., INNER JOIN

vs. OUTER JOIN).

• Limit result set: Use LIMIT or TOP to reduce the number of rows returned.

Review the concept and prepare a concise verbal explanation with a real project example.

Database design patterns are general solutions to recurring problems that arise in

database schema design. Some common patterns include:

Event sourcing is a pattern where the state of an application is determined by a sequence

of events, rather than storing the current state directly in the database.

• How it works: Instead of storing the current state of an entity, each change to that

entity (event) is stored as an immutable event in an event store. The state can be

reconstructed by replaying the events.

• Advantages:
• You have a complete audit trail of changes.
• Enables eventual consistency and can scale well in distributed systems.
• Use cases: CQRS (Command Query Responsibility Segregation), systems

requiring full audit logs, and systems that need to capture historical data changes.

A materialized view is a database object that stores the results of a query physically, unlike

a regular view, which computes its result dynamically.

• Advantages:
• Faster query response times as the results are precomputed and stored.
• Can be refreshed periodically (e.g., every hour or after specific events) to

keep the data up to date.

• Use cases: Reporting, data warehousing, and aggregation-heavy applications.
• Example:
• A materialized view could aggregate sales data by day, allowing a query to

retrieve the precomputed values instead of performing costly aggregation on

the fly.

Multi-tenancy refers to a single instance of a database serving multiple tenants (clients or

organizations) while keeping their data isolated.

Approaches:

A time-series database (TSDB) is optimized for storing, querying, and managing

time-stamped data (data associated with timestamps), often used for tracking events or

metrics over time.

• Use cases: IoT data, server logs, stock prices, sensor data, application monitoring,

etc.

• Characteristics:
• Data is ordered by timestamps.
• Efficient for inserting large volumes of data continuously.
• Optimized for queries that focus on time ranges (e.g., finding data from a

particular hour or day).

• Examples: InfluxDB, TimescaleDB, Prometheus.

Managing and versioning database schema changes is essential in keeping track of updates

and ensuring consistency across different environments (development, staging, production).

Approaches:

Sharding is the process of breaking a large database into smaller, more manageable pieces

(shards), each of which is stored on a different server.

• When to use it:
• When a single database server can no longer handle the volume of data or

queries.

• When scaling vertically (increasing server resources) is no longer

cost-effective or possible.

• How it works:
• The data is divided based on a specific shard key (e.g., user ID, geographic

region, etc.).

• Each shard contains a subset of the data and is stored on a separate server

or cluster.

Example: In an e-commerce platform, you could shard user data based on geographic

region. Users from the US might be stored in one shard, while users from Europe are stored

in another.

Data migration involves transferring data between systems or platforms, often when

changing database engines or moving to the cloud.

Steps:

The CAP theorem (Consistency, Availability, Partition Tolerance) states that in a

distributed database system, it is impossible to simultaneously guarantee all three of the

following:

Eventual consistency is a consistency model used in distributed systems where updates to

data will propagate and eventually become consistent across all nodes, but not necessarily

immediately.

• How it works:
• In an eventually consistent system, updates are made to a node and

eventually, that update will be propagated to all other nodes.

• It allows for temporary inconsistencies, but ensures that the system will

converge to a consistent state over time.

• Use cases:
• Suitable for systems that can tolerate a delay in consistency, like NoSQL

databases (Cassandra, DynamoDB) and systems dealing with high

availability and massive scale.

The DISTINCT keyword is used to return only unique values in the result set, removing any

duplicate rows.

Example:

SELECT DISTINCT Country FROM Customers;

This will return a list of unique countries from the Customers table.

The CASE statement is SQL’s way of handling if-else logic in queries. It allows you to return

different values based on certain conditions.

Example:

SELECT Name,

CASE

WHEN Age < 18 THEN 'Minor'

WHEN Age >= 18 AND Age < 60 THEN 'Adult'

ELSE 'Senior'

END AS AgeGroup

FROM Employees;

The UNION operator combines the result sets of two or more SELECT queries into a single

result set and removes duplicate records. All SELECT queries must have the same number

of columns with compatible data types.

Example:

SELECT Name FROM Employees

UNION

SELECT Name FROM Customers;

Review the concept and prepare a concise verbal explanation with a real project example.

• CHAR: A fixed-length string data type. It always reserves the same amount of space

regardless of the string's length.

• Use case: When the length of the string is known and consistent.
• VARCHAR: A variable-length string data type. It only uses as much space as needed

to store the string.

• Use case: When the string length can vary.

Example:

CREATE TABLE Example (

fixed_char CHAR(10),

variable_char VARCHAR(10)

The LIMIT clause is used to specify the number of records returned by a SELECT query. It is

commonly used to restrict the number of rows, especially for pagination.

Example:

SELECT * FROM Employees LIMIT 5;

This will return only the first 5 rows from the Employees table.

NULL represents the absence of a value in a column. It is not the same as an empty string or

zero.

• Handling NULL:
• To check for NULL, use IS NULL or IS NOT NULL.
• To handle NULL in queries, use COALESCE() (returns the first non-NULL

value) or IFNULL().

Example:

SELECT Name, IFNULL(Salary, 0) FROM Employees;

• TRUNCATE: Deletes all rows in a table, but the table structure remains. It is faster

and does not log individual row deletions.

• Cannot be rolled back (in most databases).
• Resets auto-increment counters.
• DELETE: Deletes rows based on a condition, and can be rolled back (if using

transactions).

• Slower compared to TRUNCATE.

Example:

DELETE FROM Employees WHERE Age < 18; -- Deletes only specific rows

TRUNCATE TABLE Employees; -- Deletes all rows

An Index is a database object that speeds up the retrieval of rows from a table by creating a

data structure (often a B-tree). Indexes improve the performance of SELECT queries but can

slow down INSERT, UPDATE, and DELETE operations.

Example:

CREATE INDEX idx_employee_name ON Employees(Name);

The ACID properties ensure reliable processing of database transactions: