Database : SQL & NO-SQL
How would you manage a distributed database?
Ans: By dividing data across multiple servers. For example, in an e-commerce site, I can split customer information among servers based on location, with backups kept for safety.
What is indexing in database?
Indexing in a database is like creating a shortcut to find data faster. Imagine you have a huge book with no table of contents or index. Finding a specific topic would take a long time because you would have to flip through every page.
In a database, an index works like a book's index or table of contents. It helps the database quickly find specific rows of data without having to search through every single row. This makes searching and retrieving data much faster.
What is the order of appearance of the common statements in the SELECT query?
SELECT – FROM – JOIN – ON – WHERE – GROUP BY – HAVING – ORDER BY – LIMIT
Why did you switch to MySQL to MongoDB?
In a recent project, I switched from MySQL to MongoDB because
We needed a more flexible and scalable solution for managing unstructured data.
We were working on different features where each data is connected with another. Something like cloud MongoDB was much easier for us to work concurrently
Our application required storing diverse data types that frequently changed
We need to spend more time on development rather than more database design
Additionally, MongoDB's horizontal scalability means more scalable in future (in Final Round)
Query in MongoDB was much faster
Difference between SQL and NO SQL?
1
Relational databases (RDBMS) store data in rows and tables. These systems connect information from various tables with keys — unique identifiers that the database assigns to rows of data in tables. Primary keys and foreign keys facilitate this process.
Non-relational databases store data just like relational databases. However, they don't contain any rows, tables, or keys. This type of database utilizes a storage model based on the type of data it stores.
2
SQL databases use structured query language. You have to use predefined schemas to determine your data structure before you can work with it.
You can create documents without having to first define their structure.
3
In general, SQL databases are suitable for structured data, where data is consistent, and relationships between tables are well-defined. SQL databases are typically used in applications that require complex queries and transaction management
NoSQL databases have dynamic schemas for unstructured data and store data in many ways. You can use column-oriented, document-oriented, graph-based, or Key Value stores for your data.
4
It’s perfect for complex queries.
It’s not perfect for complex queries.
5
SQL databases are better for multi-row transactions.
NoSQL is better for unstructured data like documents or JSON.
6
SQL databases are vertically scalable in most situations. That means you can increase the load on a single server by adding more CPU, RAM, or SSD capacity.
NoSQL databases are horizontally scalable. You can handle higher traffic via a process called sharding, which adds more servers to your NoSQL database.
7
Example: MySQL, PostgreSQL, Oracle, Microsoft SQL Server
Example: MongoDB, Cassandra
NO SQL
NoSQL databases the preferred choice for large and frequently changing data sets because of horizontal Scaling. For example, you might use a NoSQL database if you have large data objects like images and videos. An SQL database wouldn't be able to handle these objects as effectively, making it difficult to fulfill your data requirements.
In contrast, NoSQL databases are suitable for semi-structured or unstructured data, where the data does not conform to a predefined schema, and relationships between data elements are not well-defined. NoSQL databases are used in applications that require high performance and scalability, such as web applications and mobile apps.
Why NoSQL is Used Over SQL?
Ans: NoSQL is preferred over SQL in many cases because it offers more flexibility and scalability. The primary benefit of using a NoSQL system is that it provides developers with the ability to store and access data quickly and easily, without the overhead of a traditional relational database. As a result, development teams can focus on delivering features and core business logic faster, without worrying about the underlying data storage implementation.
Example: Use Eco-sync Story. Mainly we need to focus on developing rather than database design.
Which is better SQL or NoSQL?
Ans: The decision of which type of database to use - SQL or NoSQL - will depend on the particular needs and requirements of the project. For example, if you need a fast, scalable, and reliable database for web applications then a NoSQL system may be preferable. On the other hand, if your application requires complex data queries and transactional support then an SQL system may be the better choice.
Example: Use Sust Autorickshaw Managment Story. Mainly we need to focus on databased design and complex data queries.
When designing a financial tech software, what kind of database you will use SQL and NO SQL?
Ans: For handling a massive volume of data or user interactions, I'll prefer NOSQL. If data consistency and complex query is more preferred, I'll use SQL.
What is the difference between horizontal and vertical scaling?
Primary and unique key মাঝে difference কি ?
1
The primary key will not accept NULL values
Unique key can accept NULL values.
2
A table can have only one primary key
There can be multiple unique keys on a table.
3
A Primary Key can be a Unique Key
A Unique Key cannot be a primary key.
4
A Clustered index is automatically created when a primary key is defined
a Unique key generates the non-clustered index.
What’s the difference between primary key and foreign key?
1
Identifies each record in a table.
Links two tables together.
2
Ensures each record is unique.
Points to the primary key of another table.
3
Used for quick access within the same table.
Ensures data consistency between related tables.
What is the “join” of DB? Describe briefly.
Ans: It is an operation that combines rows from two or more tables based on a related column between them. It allows you to retrieve data from multiple tables simultaneously by specifying how the tables are related. There are different types of joins, including:
Inner Join: Returns rows where there is a match in both tables.
Left Join: Returns all rows from the left table and the matching rows from the right table.
Right Join: Returns all rows from the right table and the matching rows from the left table.
Full Outer Join: Returns all rows when there is a match in either table
Explain Inner Join, Left Join and Right Join with proper examples.
Inner Join:
Definition: An inner join returns only the rows where there is a match in both tables.
Example: If Table A has 5 rows and Table B has 5 rows, but only 3 rows have matching values in the join condition, the result will have only those 3 matching rows.
Left Join:
Returns all rows from the left table (first table mentioned in the query) and the matching rows from the right table.
If there's no match in the right table, it still returns all rows from the left table, filling in NULL values for the columns from the right table.
Right Join:
Opposite of left join.
Returns all rows from the right table and the matching rows from the left table.
If there's no match in the left table, it still returns all rows from the right table, filling in NULL values for the columns from the left table.
Give a real-life example of INNER JOIN / Outer JOIN in SQL?
Hint: outer join means left/right join
Give a real-life example of many to many relationships.
Ans: Consider the relationship between doctors and patients in a healthcare system:
Doctors: A doctor can have many patients.
Patients: A patient can visit many doctors.
This creates a many-to-many relationship:
A doctor (e.g., Dr. Smith) can have multiple patients (e.g., Alice, Bob, Charlie).
A patient (e.g., Alice) can visit multiple doctors (e.g., Dr. Smith, Dr. Johnson).
In a database design:
You might have a table for doctors with doctor IDs and doctor information.
You might have a table for patients with patient IDs and patient information.
You would need a third table, often called a "visits" or "appointments" table, to represent the many-to-many relationship. This table would typically contain the doctor ID, patient ID, and other information related to the visit, such as date and reason for the visit.
What is RDBMS?
Ans: RDBMS stands for Relational Database Management System. It organizes data into tables with rows and columns, using structured query language (SQL) for querying and managing the data.
What does the “relation” mean in RDBMS?
Ans: In RDBMS, "relation" simply means "table. It means a logical connection between different tables, established on the basis of interaction among these tables.
Imagine you have a simple spreadsheet:
Here, each row represents a person, and each column represents a piece of information about them (ID, Name, Age). This spreadsheet can be thought of as a "relation" in RDBMS - it's a table where data is organized into rows and columns.
What is a Primary key?
Ans: A unique identifier for each record in a table.
What is MongoDB?
Ans: MongoDB is a NoSQL database in which data is stored in documents that consist of key-value pairs, sharing a lot of resemblance to JSON. The data is not stored in the form of tables and that’s how it differs from other database programs.
Type of Database Relation?
One-to-One Relationship:
Person and Passport: Each person has one unique passport, and each passport is issued to one unique person
One-to-Many Relationship:
Customer and Orders: Each customer can have multiple orders, but each order is placed by one customer.
Many-to-Many Relationship:
Students and Courses: Each student can enroll in multiple courses, and each course can have multiple students.
What is a constraint, and why use constraints? What SQL constraints do you know?
A set of conditions defining the type of data that can be input into each column of a table. Constraints ensure data integrity in a table and block undesired actions.
DEFAULT– provides a default value for a column.UNIQUE– allows only unique values.NOT NULL– allows only non-null values.PRIMARY KEY– allows only unique and strictly non-null values (NOT NULLandUNIQUE).FOREIGN KEY– provides shared keys between two and more tables.
What is the difference between the DELETE and TRUNCATE and DROP statements?
DELETE and TRUNCATE and DROP statements?DELETE: Removes specific rows based on a condition, logs each row deletion, can activate triggers, slower for large datasets.
DELETE FROM employees WHERE department = 'HR';TRUNCATE: Removes all rows without conditions, minimal logging, does not activate triggers, faster for large datasets.
Removes all rows from a table, but the table structure (schema) and its columns, constraints, indexes, etc., remain intact.
TRUNCATE TABLE employees;DROP: Completely removes a table (or other database objects) from the database, including the table structure and all data.
DROP TABLE employees;ACID Principle
ACID is an acronym for Atomicity, Consistency, Isolation, Durability, and it is a set of properties that guarantee reliable processing in a database system.
Atomicity: Ensures that all operations within a transaction are completed; if not, the transaction is aborted. This means that transactions are all-or-nothing.
Consistency: Ensures that the database transitions from one valid state to another valid state. This ensures data integrity.
Isolation: Ensures that the intermediate state of a transaction is invisible to other transactions. This means concurrent transactions do not affect each other.
Durability: Ensures that once a transaction is committed, it remains so, even in the case of a system failure. This ensures that the results of the transaction are permanent.
Consider a bank transfer transaction:
Atomicity: The transfer from account A to B happens entirely or not at all.
Consistency: The total amount in the system remains constant, maintaining the balance.
Isolation: Concurrent transfers don't interfere with each other.
Durability: Once the transfer is completed and acknowledged, it remains in the system even if the system crashes immediately afterward.
Last updated