Syntax:
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
….
);
Example:
CREATE TABLE customers (
customer_id INT,
customer_name VARCHAR(50),
customer_address VARCHAR(255),
customer_email VARCHAR(255)
);
Normalization in MySQL is the process of organizing data into tables in such a way that the data is stored efficiently and redundancies are minimized. Normalization is an important concept in database design, as it helps to ensure data integrity and reduce data storage requirements.
For example, if a database contains information about customers and orders, the data can be normalized by creating separate tables for customers and orders. Each table would contain the relevant information about customers and orders, and the two tables could be linked together using a foreign key. This would ensure that the data is stored efficiently and redundancies are minimized.
The syntax for creating a new database in MongoDB is:
use
For example, to create a new database called “mydatabase”:
use mydatabase
Data consistency in MongoDB can be ensured by using transactions. A transaction is a set of operations that are executed as a single unit and either all of them are applied or none of them are applied.
For example, if a customer wants to transfer money from one account to another, the transaction would include both the debit and credit operations. If either one of the operations fails, the entire transaction should be rolled back.
MongoDB provides the ability to use transactions across multiple documents, collections, and databases. This helps ensure data consistency by ensuring that all operations within the transaction are either all applied or none are applied.
Sharding in MongoDB is a method for distributing data across multiple machines. It is a method for horizontal scaling, which allows a large dataset to be split across multiple servers or shards.
For example, let’s say you have a large dataset that is stored in a single MongoDB collection. To scale this dataset, you can use sharding to split the collection into multiple shards, each residing on a separate server. This would allow you to distribute the data across multiple machines, allowing for improved performance and scalability.
A replica set in MongoDB is a group of MongoDB instances that maintain the same data set. This means that each instance of the replica set contains the same data, and any changes made to the data on one instance will be automatically replicated to the other instances. For example, if you write a document to one instance, that document will be automatically replicated to the other instances in the replica set. Replica sets also provide redundancy and high availability, as they can continue to serve data even if one of the instances fails.
1. Document-oriented Storage: MongoDB stores data in JSON-like documents with dynamic schemas, making the integration of data in applications easier and faster. For example, a product document in MongoDB may look like this:
{
name: “Laptop”,
description: “Lenovo Thinkpad T480”,
price: 800
}
2. Indexing: MongoDB supports indexing on any field in a document which makes data retrieval faster. For example, if you want to find all the products with a price greater than $500, you can create an index on the price field and MongoDB will use it to quickly locate the documents you need.
3. Replication: MongoDB provides high availability with replica sets. A replica set consists of two or more copies of the data. All replica set members are synchronised, and one member is designated as the primary node, which receives all write operations. The other members, known as secondaries, replicate the primary’s data set.
4. Load balancing: MongoDB uses a technique called “sharding” to support deployments with very large data sets and high throughput operations. Sharding splits the data across multiple machines, so that the data can be spread out and accessed in parallel.
5. Aggregation: MongoDB has powerful aggregation capabilities that allow you to process large amounts of data and return computed results. For example, you can use the aggregation framework to calculate the average price of all the products in the collection.
SQL Server handles deadlocks by automatically choosing one of the sessions as a deadlock victim and aborting the transaction. In order to avoid unnecessary deadlocks, SQL Server implements a deadlock detection and resolution process.
For example, consider two sessions, A and B, that are attempting to update the same row in a table. Session A has acquired a shared lock on the row, while Session B has acquired an exclusive lock on the same row. When Session A attempts to acquire an exclusive lock on the same row, a deadlock is detected. SQL Server then chooses one of the sessions as the deadlock victim and aborts the transaction. In this case, Session B is chosen as the deadlock victim and its transaction is aborted.
A trigger is a block of code that is executed automatically when a specific event occurs in a database, such as when a record is inserted, updated, or deleted. Triggers are often used to implement complex business rules, maintain data integrity, or audit changes to data.
Example of a Trigger:
CREATE TRIGGER tr_Employee_Update
ON Employee
AFTER UPDATE
AS
BEGIN
UPDATE Employee
SET LastUpdated = GETDATE()
WHERE EmployeeID =
(SELECT EmployeeID FROM deleted)
END
A stored procedure is a precompiled set of SQL statements that can be executed multiple times with different parameters. Stored procedures are often used to encapsulate complex business logic and are used to improve application performance by reducing the amount of code that needs to be executed.
Example of a Stored Procedure:
CREATE PROCEDURE GetEmployeeInfo
@EmployeeID int
AS
BEGIN
SELECT *
FROM Employee
WHERE EmployeeID = @EmployeeID
END
The MySQL Query Browser is a graphical tool designed to provide a user-friendly environment in which to construct and execute SQL queries. It enables users to easily create, edit, and execute SQL scripts, as well as browse and modify database objects.
For example, a user can use the MySQL Query Browser to connect to a database and view all the tables within it. They can then select a table and view the data within it, or open the SQL editor to write and execute queries. They can also create, alter, or drop tables, and view the structure of the table.
Vaibhav Kothia