SQL Concepts

ACID

ACID is a term used in transaction processing to refer to a set of four critical properties that transactions should exhibit. These four properties are atomicity, consistency, isolation, and durability.

• Atomicity ensures that all changes made by a transaction are treated as a single operation, meaning that either all of the changes are made or none of them are.

• Consistency guarantees that the data is in a consistent state before and after the transaction is executed.

• Isolation - Concurrent transactions will not interfere with one another, and each transaction will be executed independently of the others. Even though the requests are occurring simultaneously, But they're being processed one by one.

• durability ensures that once a transaction is successfully completed, the changes made to the data persist and are not lost, even in the event of a system failure.

Overall, these properties help ensure that transactions are reliable, robust, and maintain data integrity, making them critical for the proper functioning of computer systems that process transactions.

CAP Theorem

• A network with numerous nodes (physical or virtual machines) simultaneously storing data is known as a distributed system. Understanding the CAP theorem is essential when creating a cloud application because all cloud apps are distributed systems.
• To ensure that the system continues to provide a reliable service despite these partitions, partition tolerance becomes a necessity.
• This indicates that in a distributed system, achieving high consistency may come at the expense of reduced availability, whereas prioritising high availability may result in poorer consistency.
• The CAP theorem is often referred to as Brewer's theorem.

Joins

Joins in SQL Server allow you to retrieve data from various tables based on logical links.
Joins specify how data from one table should be used to select rows from another table.
Joins can be done logically using the following SQL syntax:

• INNER JOIN - By using the INNER JOIN keyword, only the rows from both tables with matching columns will be selected with condition which we're giving it.
• LEFT JOIN - The LEFT JOIN command retrieves all rows from the left table as well as the matching rows from the right table.If there is no match, the result from the right side is NULL.
• RIGHT JOIN - The RIGHT JOIN also is same as the left join, the only difference is retrieves from right, we can acheive right join by left also by swapping the table name.
• FULL JOIN - When there is a match in either the left or right tables, the FULL OUTER JOIN statement returns all rows.

Aggregations, Filters in queries

• Aggregations - A single output value is computed by SQL aggregate functions using various column input values. These operations are frequently combined with the SELECT statement's GROUP BY and HAVING clauses. The SQL aggregate functions that are used most frequently are

  • Count() - To find the count of data.
  • Sum() - To find sum of the column.
  • Average() - To find avg value.
  • Min() - To find min value.
  • Max () - To find max value.

• Filters - A subset of data items within SQL and internal database types are defined by text strings known as SQL filters.
  
  These filters often take the form of a SQL WHERE clause, which includes a set of comparisons that need to be met in order for a specific data item to be returned.
  
  These comparisons typically involve field names and the values that correspond to those names.

Normalization

Removing redundant data from your tables will increase storage effectiveness, data integrity, and scalability. This procedure is known as database normalisation.There are ways to measure a database's effectiveness in the relational paradigm.Normal forms (or NF) are the name given to these classes, and there are algorithms for converting a given database between them.

• In order to "normalise" an existing table, it is typically split into many ones that need to be "re-joined" or "connected" each time a query is run.

Types of Normal Forms:

• 1NF - According to the first normal form (1NF), a table attribute can only carry one value and cannot hold more than one value. As a result, 1NF forbids the use of composite attributes, multi-valued attributes, or any combination of the two.

• 2NF - A table must comply to the first normal form (1NF) before it may satisfy the second normal form (2NF) (1NF). Furthermore, the main key must be completely functionally dependent on all non-key properties in the database.

• 3NF - A relation is in 3NF if it is in 2NF and does not contain any transitive partial dependencies.To lessen data duplication, 3NF is used.It is also employed to ensure data integrity.The relation must be in third normal form if there is no transitive dependency for non-prime attributes.

• BCNF - Boyce Codd's normal form is an improved definition of 3NF.

• 4NF - Boyce Codd normal form and the absence of any multi-valued dependencies must both be met for a relation to be categorised as being in fourth normal form (4NF).When there are multiple values of B connected to a single value of A for a given dependency A B, this is referred to as a multi-valued dependency.

• 5NF - When all of the tables are divided up into as many different tables as is possible to prevent redundancy, 5NF is satisfied.Project-join normal form, or 5NF, is another name for this format.

Index

In order to speed up data retrieval, the database search engine uses indexes as lookup tables.
They operate similarly to an index at the end of a journal by making references to information in a table.
The purpose of a database index is to speed up database access even though users cannot see it.

In order to find all the pages in a book that are related to a particular topic, for instance, you would first look in the index, which lists the topics in alphabetical order, before moving on to one or more specific page numbers.

In addition to speeding up data retrieval, indexes also stop duplicate entries from being made in the column or group of columns on which they are created.

for example :

• CREATE INDEX index_name ON table_name(column_name); - To create index
• DROP INDEX college_index; - To drop index

Transactions

A transaction in SQL is a group of one or more SQL statements that communicate with a database.
To undo the changes performed, it can be rolled back as a single logical unit or committed to a database as a single unified logical unit.
When numerous concurrent operations or users are interacting with the database at once, transactions are essential to SQL's ability to ensure database integrity.
By ensuring that related operations are carried out simultaneously and committed as a single logical unit, they contribute to integrity preservation.

Locking Mechanism

To maintain data consistency in SQL, it is essential to utilize SQL Locks. A lock is created at the beginning of a transaction and is released when the transaction is completed in SQL . There are several types of locks available for use.

• One type is the Shared (S) Lock, which is used to read an object without making any changes to it.

• Another type is the Exclusive (X) Lock, which blocks other operations like inserting, updating, or deleting data on the locked objects.

• The Update (U) Lock is another type of lock that is similar to the Exclusive lock. However, it allows for a "read phase" and a "write phase," and some transactions may be restricted during the read phase.

• Lastly, the Intent Lock is used to indicate that an object will be locked shortly by another type of lock.

Database Isolation

• Isolation controls how transaction integrity is visible to other users and systems in database systems.
• A lower level of isolation makes it easier for several users to access the same data concurrently, but it also makes it more likely that users will experience concurrency impacts like losting the changes.

Triggers

Storage-based programmes known as triggers are launched or automatically run when specific events take place.
In reality, triggers are written to run in response to any of the following situations:

• A DML statement is used to manipulate databases (DELETE, INSERT, or UPDATE)
• An explanation of the database (DDL) (CREATE, ALTER, or DROP).
• operating a database (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).

Tables, views, schemas, and databases that are connected to an event can all have triggers defined on them.

Reference

• https://www.ibm.com/in-en/topics/
• https://www.geeksforgeeks.org/
• https://www.scaler.com/topics/
• https://www.tutorialspoint.com/plsql/